数据库作业6——嵌套查询

3.4.3 嵌套查询

嵌套查询：将一个查询块嵌套在另一个查询块的where子句或having短句的条件中的查询称为嵌套查询。
分为父查询（外层查询）与子查询（内层查询）
【注】子查询的select语句中不能使用order by 子句
不相关子查询：子查询的查询条件不依赖于父查询（例如 例3.55和例3.56）
相关子查询：子查询的条件依赖于父查询（例如 例3.57）

1. 带有in谓词的子查询

【例3.55】查询与“刘晨”在同一个系学习的学生。

select Sno,Sname,Sdept
from Student
where Sdept in
(select Sdept
from Student
where Sname='刘晨');

它的执行顺序如下列语句：

select Sdept
from Student
where Sname='刘晨';--查询刘晨的系名
select Sno,Sname,Sdept
from Student
where Sdept='CS';--在CS系的学生信息

此外，这个例题也可以通过自身连接来实现：

select S1.Sno,S1.Sname,S1.Sdept
from Student S1,Student S2
where S1.Sdept=S2.Sdept and S2.Sname='刘晨';

例3.55的四种解法：

1.嵌套查询
select Sno,Sname,Sdept
from Student
where Sdept in
(select Sdept
from Student
where Sname='刘晨');

2.连接查询
select S1.Sno,S1.Sname,S1.Sdept
from Student S1,Student S2
where S1.Sdept=S2.Sdept and S2.Sname='刘晨';

3.带比较运算符的查询
select Sno,Sname,Sdept
from Student
where Sdept=
(select Sdept
from Student 
where Sname='刘晨');

4.使用exists
select Sno,Sname,Sdept
from Student x
where exists
(select *
from Student y
where y.Sdept=x.Sdept and y.Sname='刘晨');

【例3.56】查询了选修了课程名为“信息系统”的学生学号和姓名。
课程名在Course表中，选课信息在SC表中，学生姓名在Student表中，所以此题涉及三个表。

select Sno,Sname
from Student
where Sno in
(select Sno 
from SC 
where Cno in
(select Cno
from Course
where Cname='信息系统'));
select * from SC;

用连接查询来做的话：

select Student.Sno,Sname
from Student,Course,SC
where Student.Sno=SC.Sno and
SC.Cno=Course.Cno and 
Course.Cname='信息系统';

2. 带有比较运算符的子查询

确切内层查询返回的是单个值时可以用比较运算符。
【例3.57】找出每个学生超过他自己选修课程平均成绩的课程号。

select Sno,Cno
from SC x
where Grade>=
(select avg(Grade)
from SC y
where y.Sno=x.Sno);

我理解的执行过程是：
先在外层查询中取出一个元组，再在内层查询中计算出该元组学生的平均成绩，再在外层查询中依次检查该学生的每门课程的成绩，大于等于平均分就输出；然后继续下一个学生执行相同的步骤。

3. 带有any（some）或all谓词的子查询

any-某一个，任意一个
all-所有的，全部
【例3.58】查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。
任意一个，所以用any

select Sname,Sage
from Student
where Sage<any
(select Sage
from Student
where Sdept='CS')
and Sdept<>'CS';

小于任意一个，相当于小于最大的一个，所以还可以用聚集函数来实现

select Sname,Sage
from Student
where Sage<
(select max(Sage)
from Student
where Sdept='CS')
and Sdept<>'CS';

两种方法的答案是相同的。
【例3.59】查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

select Sname,Sage
from Student
where Sage<=all
(select Sage
from Student
where Sdept='CS')
and Sdept<>'CS';
--聚集函数实现
select Sname,Sage
from Student
where Sage<
(select min(Sage)
from Student
where Sdept='CS')
and Sdept<>'CS';

因为CS系年龄最小为18，所有学生的最小年龄也为18，所以输出没有结果，若是查找小于等于最小年龄的，结果如下：

4. 带有exists谓词的子查询

带有exists谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
【例3.60】查询所有选修了1号课程的学生姓名。

select Sname
from Student
where exists
(select *
from SC
where Sno=Student.Sno and Cno=1);

【例3.61】查询没有选修1号课程的学生姓名。

select Sname
from Student
where not exists
(select *
from SC
where Sno=Student.Sno and Cno=1);

【例3.62】查询选修了全部课程的学生姓名。
将此语义转换查询一个学生，没有一门课是他不选的。

select Sname
from Student
where not exists
(select *
from Course
where not exists
(select *
from SC
where Sno=Student.Sno and
Cno=Course.Cno));

因为没有学生把所有的课都选了，所以就没有截图了。
过程：首先是在Student表中选出第一个元组进入第二层，在第二层中依次选择每个元组在第三层中进行判断，将返回的结果在第二层中做并运算，将运算的结果在返回到第一层中，就结束了对Student表中第一个元组的判断。然后继续判断剩下的元组。
【例3.63】查询至少选修了学生201215122选修夫人全部课程的学生号码
转换语义为：不存在这样的课程y，学生201215122选修了y，而学生x没有选。

select distinct Sno
from SC SCX
where not exists
(select *
from SC SCY
where SCY.Sno='201215122'and not exists
(select *
from SC SCZ
where SCZ.Sno=SCX.Sno and
SCZ.Cno=SCY.Cno));

过程：首先在X中取出一个元组进入第二层，在第二层中选择出学号相同的元组，将每个元组进入第三层判断Z的学号等于X的学号，Z的课程号等于Y的课程号的元组，将逻辑值返回到第二层再进行判断，将所有的返回到第二层的结果进行并运算，最后返回到第一层。之后取X中的下一个值进行相同的操作。

【个人总结】使用exists 还是挺难的，最后两个例题想了很长的时间才稍微理解了语句的含义，但换道题可能又不会了。