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

最新推荐文章于 2024-08-07 11:06:05 发布

Files_Stone

最新推荐文章于 2024-08-07 11:06:05 发布

阅读量847

点赞数

本文链接：https://blog.csdn.net/Files_Stone/article/details/71124939

版权

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