第八单元 嵌套查询
学生信息表:
学生编号 | 姓名 | 班级Id | 电话 | 性别 | 生日 |
---|---|---|---|---|---|
180325011 | 任我行 | 5 | 13823204456 | 男 | 1999-09-09 |
180325012 | 张三 | 4 | 13823204452 | 女 | 1998-08-08 |
180325013 | 李四 | 2 | 18899251152 | 男 | 1997-07-07 |
180325014 | 王五 | 1 | 13597445645 | 女 | 1998-08-08 |
180325015 | 帅天行 | 5 | 13814204456 | 男 | 1998-06-06 |
180325016 | 叶星辰 | 5 | 17623204936 | 男 | 1998-05-05 |
180325017 | 赵日天 | 0 | 13922044932 | 男 | 1999-07-15 |
班级表:
班级Id | 班级名称 | 学院(系) |
---|---|---|
1 | 软件技术1班 | 计算机系 |
2 | 会计1班 | 经济管理系 |
3 | 会计2班 | 经济管理系 |
4 | 欧美软件外包班 | 计算机系 |
5 | 会计3班 | 经济管理系 |
成绩表:
Id | 学生编号 | 课程名称 | 理论成绩 | 技术成绩 |
---|---|---|---|---|
1 | 180325011 | 会计从业 | 80 | 90 |
2 | 180325011 | C# 入门编程 | 99 | 100 |
3 | 180325012 | SQLServer编程 |
70 | 75 |
4 | 180325013 | 会计从业 | 93 | 80 |
5 | 180325014 | C# 高级编程 | 99 | 99 |
6 | 180325015 | 会计从业 | 74 | 40 |
7 | 180325015 | C# 入门编程 | 80 | 90 |
请问:叶星辰属于哪个班级?
select * from 班级表 where 班级Id= ( Select 班级Id from 学生表 where 姓名 = '叶星辰' )
2.嵌套查询的格式是什么?
select 列名 from 表名 where 字段名 运算符 -- 外层主查询,也叫父查询 ( select 列名 from 表名 where 条件 -- 内层查询,也叫子查询 )
子查询的结果作为主查询的查询条件
--4.--查询软件技术1班的所有学生信息 -- 1.找表:学生表,班级表,外键:ClassId -- 2.根据已经条件查询外键的值 -- 3.根据外键的值查询出题目的要求结果 select * from StudentInfo where ClassId = ( select Id from ClassInfo where Name='软件技术1班' ) --5.--查询任我行同学的所有成绩 -- 5.1 StudentInfo,StudentScore,找外键 stuId -- 5.2 先写已知条件 ,将外键查询出来 -- 5.3 根据外键的值查询题目要求的成绩信息 select * from StudentScore where stuId in ( select stuId from StudentInfo where stuName='任我行' ) --6.--查询“张三”同学所在班级信息 -- 1 StudentInfo,ClassInfo,找外键 classId -- 2 先写已知条件 ,将外键查询出来 -- 3 根据外键的值查询题目要求的班级信息 select * from ClassInfo where Id in ( select classId from StudentInfo where stuName='张三' ) --7.-- 查询学号为“180325011”的同学所在班级所有男生的信息 -- 1 StudentInfo,StudentInfo,关联字段 classId -- 2 先写已知条件 ,将关联字段查询出来 -- 3 根据关联字段的值查询题目要求的男生的信息 select * from StudentInfo where ClassId= ( select ClassId from StudentInfo where stuId='180325011' ) and stuSex='男' --8.-- 查询班级名为“软件技术1班”一共有多少个女生信息 -- 1 ClassInfo,StudentInfo,关联字段 classId -- 2 先写已知条件 ,将关联字段查询出来 -- 3 根据关联字段的值查询题目要求的女生信息 select * from StudentInfo where stuSex='女' and ClassId= ( select Id from ClassInfo where Name='软件技术1班' ) --9.-- 查询电话号为“18899251152”同学所在的班级信息 -- 1 ClassInfo,StudentInfo,关联字段 classId -- 2 先写已知条件 ,将关联字段查询出来 -- 3 根据关联字段的值查询题目要求的女生信息 select * from ClassInfo where Id= ( select classId from StudentInfo where stuPhone='18899251152' ) --10.-- 查询所有成绩高于平均分的学生信息 -- 1,StudentScore, StudentInfo, 关联字段:StuId -- 已知条件是:平均分 select * from StudentInfo where stuId in ( -- 查询出高于平均分的Stuid select stuId from StudentScore where skillScore> ( select avg(skillScore) from StudentScore ) ) --11.查询所有年龄小于平均年龄的学生信息 -- 计算小于平均年龄的学生信息 select * from StudentInfo where (year(getdate())-year(stuBirthday))< ( -- 计算平均年龄 select avg(year(getdate())-year(stuBirthday)) from StudentInfo ) --12.查询不是软件技术1班级的学生信息 -- 关联字段:ClassId select * from StudentInfo where ClassId not in -- 用in一定不会错,如果子查询的结果只有一条记录时才可以写= ( select Id from ClassInfo where Name='软件技术1班' ) select * from StudentInfo where ClassId != ( select Id from ClassInfo where Name='软件技术1班' ) --13.查询所有班级人数高于平均人数的班级信息 -- 每个班有多少人 select * from ClassInfo where Id in ( select ClassId from StudentInfo group by ClassId having count(stuId)> ( -- 求平均人数 select avg(人数)from ( select count(stuId) as 人数 from StudentInfo group by ClassId ) aa ) ) --14.查询成绩最高的学生信息 select * from StudentInfo where stuId in ( select stuId from StudentScore where skillScore = ( select MAX(skillScore) from StudentScore ) ) --16.查询班级名是“会计1班”所有学生(使用in 关键字查询) select * from StudentInfo where ClassId in ( select Id from ClassInfo where Name='会计1班' ) --17.查询年龄是16、18、21岁的学生信息 select * from StudentInfo where (year(getdate())-year(stuBirthday)) in (16,18,21) --18.查询所有17-20岁且成绩高于平均分的女生信息 select * from StudentInfo where (year(getdate())-year(stuBirthday)) between 17 and 20 and stuSex='女' and stuId in ( select stuId from StudentScore where skillScore> ( select avg(skillScore) from StudentScore ) ) --19.查询不包括'张三'、'王明'、'肖义'的所有学生信息(not in 关键字查询) select * from StudentInfo where stuName not in('张三','王明','肖义') --20.查询不是“计算机系”学院的所有学生(not in 关键字查询) select * from StudentInfo where ClassId not in ( select Id from ClassInfo where College='计算机系' ) --查询成绩比学生编号为'180325011','180325012'其中一位高的同学 -- any,some:某一个,其中一个 select * from StudentInfo where stuId in ( select stuId from StudentScore where skillScore> some ( select skillScore from StudentScore where stuId in('180325011','180325012') ) ) --查询成绩比学生编号为'180325011','180325012'都高的同学(all) -- all:所有 select * from StudentInfo where stuId in ( select stuId from StudentScore where skillScore> all ( select skillScore from StudentScore where stuId in('180325011','180325012') ) ) --Row_Number() Over(Order by 字段):按某个字段进行编号排名 -- 以stuId进行升序排名 select Row_Number() Over(Order by stuId) ,* from StudentInfo -- 按总成绩降序排序并给每一位同学进行编号 select Row_Number() Over(Order by skillScore desc) as 排名, * from StudentScore -- 按总成绩降序排序后查询4-8名的学生信息 select * from( select Row_Number() Over(Order by (skillScore+theoryScore) desc) as 排名, * from StudentScore ) aa where aa.排名 between 4 and 8 -- sqlserver 2012以后,offset rows fetch next rows only -- offset:在。。。位置 select * from StudentScore order by (skillScore+theoryScore) desc offset 3 rows fetch next 5 rows only -- 获取按Id排序后的第3-5位同学信息 select * from( select Row_Number() Over(Order by StuId) as 排名, * from StudentScore ) aa where aa.排名 between 3 and 5 -- select * from StudentScore order by Id offset 2 rows fetch next 3 rows only
配套视频链接:【阶段二】 - SQLServer 基础(超级详细,口碑爆盆)_哔哩哔哩_bilibili
海阔平鱼跃,天高任我行,给我一片蓝天,让我自由翱翔。