今天学了SqlServer模糊查询、聚合函数 和分组查询

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

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
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值