3.4.3 嵌套查询
一．带有in谓词 的 子查询
用自身连接完成 .note
select s1.sno, s1.sname, s1.sdept
from student s1, student s2
where s1.sdept=s2.sdept and s2.sname='刘晨'
student 表
39 查询 与 刘晨 在同一个系 学习的学生.note
假设 一个学生 只可能在一个系,,并且 属于一个系
此时 in 可用 =
select sno, sname, sdept from student
where sdept =(
select sdept from student where sname='刘晨' )
40 查询 选修了 课程名为 信息系统 的 学生 学号 和 姓名.note
select sno,sname
from student
where sno in
(
select sno
from sc
where cno in
(
select cno
from course
where cname='信息系统' )
)
1. course 表 '信息系统'的 课程
2 sc 表 选修3号课程 的 学生学号
3 student 表 取出 sno sname
用 连接查询 40.note
select student.sno, sname
from student, sc , course
where
student.sno=sc.sno and
course.cno=sc.cno and
course.cname='信息系统'
查询 选修了 2号课程 的 学生 的 姓名
select sname
from student
where sno
in(
select sno
from sc
where cno='2'
)
二．不相关 子查询
39 查询 与 刘晨 在同一个系 学习的学生 不相关 子查询.note
假设 一个学生 只可能在一个系,,并且 属于一个系
此时 in 可用 =
select sno, sname, sdept from student
where sdept =(
select sdept from student where sname='刘晨' )
p104 查询 选修了 2号课程 的 学生 的 姓名.note
40 查询 选修了 课程名为 信息系统 的 学生 学号 和 姓名.note
select sno,sname
from student
where sno in
(
select sno
from sc
where cno in
(
select cno
from course
where cname='信息系统' )
)
1. course 表 '信息系统'的 课程
2 sc 表 选修3号课程 的 学生学号
3 student 表 取出 sno sname
嵌套 39.note
1.
select sdept
from student
where sname='刘晨'
2.
select sno, sname , sdept
from student
where sdept
in(
'cs'
)
法(二 39.note
1. 先执行 子查询
select sno, sname , sdept
from student
where sdept='cs'
2.
select sno, sname , sdept
from student
where sdept
in(
'cs'
)
三．相关子查询
41 找出每个学生 超过 他 选修课程 平均成绩 的 课程号.note
select sno, cno
from sc x
where grade>=
(
select avg(grade)
from sc y
where y.sno=x.sno
)
四．带有any all谓词的 子查询
42 某一个 学生 .note
select sname, sage
from student
(
select sage
from student
where sdept='cs'
)
and sdept <> 'cs'
43 所有学生.note
select sname, sage
from student
where sage<all
(
select sage
from student
where sdept='cs'
)
and sdept <> 'cs'
用聚集函数实现 42.note
select sname, sage
from student
where sage<
(
select max(sage)
from student
where sdept='cs'
)
and sdept <> 'cs'
聚集函数 43.note
46 查询 选修了全部课程的 学生姓名.note
select sname
from student
where not exists(
select *
from course
where not exists
(
select *
from sc
where sno=student.sno and cno=course.cno
)
)
五．带有exist谓词 的子查询
嵌套查询 44 查询 所有选修了 1号课程的 学生 姓名.note
select sname
from student
where exists
(
select *
from sc
where sc.sno=student.sno and cno='1'
)
连接运算 44.note
select sname
from student,sc
where sc.sno=student.sno and cno='1'
45 查询 没有 选修了 1号课程的 学生 姓名.note
select sname
from student
where not exists
(
select *
from sc
where sc.sno=student.sno and cno='1'
)
39 查询 与 刘晨 在同一个系 学习的学生 不相关 子查询.note
假设 一个学生 只可能在一个系,,并且 属于一个系
此时 in 可用 =
select sno, sname, sdept from student
where sdept =(
select sdept from student where sname='刘晨' )
exist 39 .note
六．集合查询
（一）union 交 or 或
48 .note
select *
from student
where sdept='cs'&nbs***bsp;sage<=19
48 union 并 &nbs***ote
查询 计算机系 的 学生 及 年龄不大于 19岁的 学生
select *
from student
where sdept='cs'
union
select *
from student
where sage<=19
49 .note
查询 选修了 1号课程 或 2号 课程的学生
select sno
from sc
where cno='1'
union
select sno
from sc
where cno='2'
（二）.intersect 并 and 和
MySQL不支持INTERSECT.note
50 查询 计算机系 的 学生 与 年龄不大于 19岁的 学生.note
select *
from student s1
where s1.sdept='cs' and
exists(
select *
from student s2
where s2.sage<=19 and s1.sno=s2.sno
)
exists 51.note
select sc1.sno
from sc sc1
where sc1.cno='1' and exists
( select sno
from sc sc2
where sc1.sno=sc2.sno and sc2.cno='2'
)
in 51 查询 选修了 1号课程 和 2号 课程的学生.note
select sno
from sc
( select sno
from student
where cno='2'
)
（三）except 差 减
52 查询 计算机系中 年龄大于 19岁的 学生.note
查询 计算机系中 年龄大于 19岁的 学生
select *
from student
MySQL不支持 except.note
MySQL不支持 except
52 查询 计算机系中 的学生 与 年龄 不大于 19岁的 学生 的 差集.note
select *
from student s1
where s1.sdept='cs' and not exists
( select *
from student s2
where s2.sage<=19 and s1.sno=s2.sno
)
由里向外
where sage<any
where cno='1' and sno in
where sdept='cs' and sage>19
京公网安备 11010502036488号