第八单元 嵌套查询

什么时候要使用嵌套查询?

学生信息表:

学生编号姓名班级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

热门相关:离婚合约:前妻的秘密   厨道仙途   全天下都知道太子爱她   全天下都知道太子爱她   觅仙道