今天学了SqlServer模糊查询、聚合函数 和分组查询
来源:互联网 发布:软件无线电技术与实现 编辑:程序博客网 时间:2024/05/14 06:03
use Student
create table ClassInfo(
id int identity(1,1) primary key,
name varchar(20) not null
)
create table StudentInfo1(
id int identity(1,1) primary key,
name varchar(20) not null,
gender varchar(5) check(gender in('男','女')),
city varchar(10) default('郑州'),
physics int,
mathmatics int,
classId int foreign key references ClassInfo(id)
)
insert into ClassInfo values('一年一班')
insert into ClassInfo values('一年二班')
insert into ClassInfo values('二年一班')
insert into ClassInfo values('二年二班')
alter table StudentInfo1
add age int check(age>6 and age<130)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('张三',16,'男','开封',76,87,1)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('张四',17,'男','开封',66,85,1)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('刘三',19,'男','新乡',97,81,2)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('刘四',19,'女','新乡',86,87,2)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('刘五',16,'女','开封',76,87,2)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('刘实在',16,'男','许昌',76,87,2)
select * from StudentInfo1 where age>=19 and gender='男'
select * from StudentInfo1 where name like '[^刘%]'
select * from StudentInfo1 where name like '%实%'
select * from StudentInfo1 where name like '_实_'
select * from StudentInfo1 where age between 16 and 18
select * from StudentInfo1 where city in ('开封','许昌')
select SUM(physics) as '物理总成绩',SUM(mathmatics) as '数学总成绩',SUM(physics+mathmatics) as '总成绩' from StudentInfo1
select SUM(mathmatics) as '数学总成绩' from StudentInfo1
select SUM(physics+mathmatics) as '总成绩' from StudentInfo1
select MAX(physics) as '物理最高分' ,min(mathmatics) as '数学最低分' ,AVG(physics) as '物理平均分' from StudentInfo1
select COUNT(*) as '物理及格人数(成绩>80分)' from StudentInfo1 where physics >80
select city as '城市',COUNT(*)as '人数' from StudentInfo1 group by city
select city as '城市',MIN(age) as '最小年龄' from StudentInfo1 group by city
create table ClassInfo(
id int identity(1,1) primary key,
name varchar(20) not null
)
create table StudentInfo1(
id int identity(1,1) primary key,
name varchar(20) not null,
gender varchar(5) check(gender in('男','女')),
city varchar(10) default('郑州'),
physics int,
mathmatics int,
classId int foreign key references ClassInfo(id)
)
insert into ClassInfo values('一年一班')
insert into ClassInfo values('一年二班')
insert into ClassInfo values('二年一班')
insert into ClassInfo values('二年二班')
alter table StudentInfo1
add age int check(age>6 and age<130)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('张三',16,'男','开封',76,87,1)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('张四',17,'男','开封',66,85,1)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('刘三',19,'男','新乡',97,81,2)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('刘四',19,'女','新乡',86,87,2)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('刘五',16,'女','开封',76,87,2)
insert into StudentInfo1 (name,age,gender,city,physics,mathmatics,classId)
values ('刘实在',16,'男','许昌',76,87,2)
select * from StudentInfo1 where age>=19 and gender='男'
select * from StudentInfo1 where name like '[^刘%]'
select * from StudentInfo1 where name like '%实%'
select * from StudentInfo1 where name like '_实_'
select * from StudentInfo1 where age between 16 and 18
select * from StudentInfo1 where city in ('开封','许昌')
select SUM(physics) as '物理总成绩',SUM(mathmatics) as '数学总成绩',SUM(physics+mathmatics) as '总成绩' from StudentInfo1
select SUM(mathmatics) as '数学总成绩' from StudentInfo1
select SUM(physics+mathmatics) as '总成绩' from StudentInfo1
select MAX(physics) as '物理最高分' ,min(mathmatics) as '数学最低分' ,AVG(physics) as '物理平均分' from StudentInfo1
select COUNT(*) as '物理及格人数(成绩>80分)' from StudentInfo1 where physics >80
select city as '城市',COUNT(*)as '人数' from StudentInfo1 group by city
select city as '城市',MIN(age) as '最小年龄' from StudentInfo1 group by city
order by MIN(age) desc
select COUNT(*) as '人数',gender,classid from StudentInfo1 group by classId, gender
select MIN(age) as '最小年龄',city as '城市' from StudentInfo1 group by city
having COUNT(*)>=2
order by MIN(age) asc
0 0
- 今天学了SqlServer模糊查询、聚合函数 和分组查询
- 模糊查询和聚合函数
- 聚合函数和分组查询
- sqlServer 聚集函数 和 分组查询
- mysql分组查询聚合函数
- Hibernate-分组查询、聚合函数
- 数据库学习笔记(五)-模糊查询和聚合函数
- HIbernate聚合函数和分组查询(学习笔记)
- MySQL数据库的聚合函数和分组查询
- 7.排序、聚合函数、分组查询
- Elasticsearch分组聚合-查询
- mysql:day2--复杂查询、模糊查询、创建视图、聚合函数
- 聚合函数,分组查询,连接查询综合例子
- 聚合函数,分组查询,连接查询综合例子
- SQL语句聚合函数、分组、子查询及组合查询
- Hibernate的查询 标准(Criteria)查询 聚合和分组
- Hibernate的查询 标准(Criteria)查询 聚合和分组
- Hibernate的查询 标准(Criteria--Projections)查询----- 聚合和分组
- 用C/C++创建windows服务程序
- 快速排序(递归法与迭代法)
- poj 3495 Bitwise XOR of Arithmetic Progression
- RPG角色生成器(C++)
- [李景山php] ubuntu/centos 搭建 搭建 redis 集群
- 今天学了SqlServer模糊查询、聚合函数 和分组查询
- itoa函数的实现
- UVA 11584 划分回文串
- 使用AsyncTask自定义图片加载类
- Rxjava2源码分析(一):Flowable的创建和基本使用过程分析
- 利用HashSet对数组去重
- 泊松融合(Poisson blend)
- android 自定义相机Camera2
- 优雅自定义轮播图