第8讲：复杂查询
一、子查询概述
1. 子查询定义
①出现在where子句中的select语句被称为子查询，即在where子句中嵌套一个select-from-where
②子查询返回一个集合，可以通过与这个集合比较来确定另一个查询集合
2. 三种类型的子查询
- (not) in子查询
- θ some/θ all子查询
- (not) exists子查询
3. 带有子查询的select语句区分为内层查询和外层查询（内层查询被嵌入到外层查询中）
①非相关子查询：内层查询独立进行，没有涉及任何外层查询相关信息的子查询
②相关子查询：内层查询需要依靠外层查询的某些参量作为限定条件才能进行
- 外层向内层传递的参量需要使用外层的表名或表别名来限定
- 相关子查询只能由外层向内层传递参数，这也称为变量的作用域规则
【示例】求学过001号课程的学生的姓名：
- select Sname from Student Stud where S# in (select S# from SC where C# = '001' and S# = Stud.S#);
- 参量S#表示内层查询的S#必须在Student表中也出现过
4. 子查询是为了判断下列条件，它们分别对应了in、θ some/θ all、exists子查询
（1）集合成员资格：某一元素是否是某一个集合的成员
（2）集合之间的比较：某一个集合是否包含另一个集合等
（3）集合基数的测试：测试集合是否为空
二、(not) in子查询
1. 基本语法形式：表达式 [not] in (子查询)
①语义：判断表达式的值是否在子查询的结果(一个集合)中
【示例1】列出张三、王三同学的所有信息：
- select * from Student where Sname in ('张三', '王三'); // 该处直接使用了某一子查询的结果(集合)
【示例2】列出选修了001号课程的学生的学号和姓名：
- selsect S#, Sname from Student where S# in (select S# from SC where C# = '001');
- 子查询返回学过001号课程的学生的学号(集合)，如果某学号在该集合中，就表示该学生学过001号课程，也就是要找的
【示例3】求既学过001号课程，又学过002号课程的学生的学号：
- select S# from SC where C# = '001' and S# in (select S# from SC where C# = '002');
- 子查询返回学过002号课程的学生的学号(集合)，如果某学号在该集合中，就表示该学生学过002号课程
【示例4】列出没学过李明老师讲授课程的学生的姓名：
- select Sname from Student where S# not in (selsect S# from SC, Course C, Teacher T where SC.C# = C.C# and C.T# = T.T# and T.Tname = '李明');
- 子查询返回学过李明老师讲授课程的学生的学号(集合)，如果某学号不在该集合中，就表示该学生没学过李明老师的课
三、θ some/θ all子查询
1. 基本语法形式：表达式 θ some/all (子查询)
①θ是比较运算符，可以为<、>、<=、>=、=、<>
②语义：将表达式的值与子查询的结果(一个集合)进行比较
- “表达式 θ some (子查询)”的结果为真：表达式的值至少与子查询结果(集合)中的某一个值相比较满足θ关系
- “表达式 θ all (子查询)”的结果为真：表达式的值与子查询结果(集合)中的所有值相比较都满足θ关系
【示例1】找出工资最低的教师的姓名：
- select Tname from Teacher where Salary <= all (select Salary from Teacher);
- 子查询返回所有教师的工资(集合)，当某工资小于等于该集合中的所有工资时，则表示该教师工资最低
【示例2】找出001号课成绩不是第一的所有学生的学号：
- select S# from SC where C# = '001' and Score < some (select Score from SC where C# = '001');
- 子查询返回学过001号课的学生的成绩(集合)，如果某成绩小于该集合中的某一个工资时，则表示该学生成绩不是第一
【示例3】找出001号课成绩最高的所有学生的学号：
- select S# from SC where C# = '001' and Score >= all (select Score from SC where C# = '001');
【示例4】找出98030101号学生成绩最低的课程号：
- select C# from SC where S# = '98030101' and Score <= all (select Score from SC where S# = '98030101');
【示例5】找出张三同学成绩最低的课程号(相关子查询)：
- select C# from SC, Student Stud where SC.S# = Stud.S# and Sname = '张三' and Score <= all (select Score from SC where S# = Stud.S#);
- 涉及到姓名，即涉及到Student表，子查询返回该学生的所有成绩
- 此处相关子查询中，内层循环需要借助外层循环的Stud来限定S#为张三的S#
四、(not) exists子查询
1. 基本语法形式：[not] exists (子查询)
①语义：判断子查询的结果(一个集合)中是否存在元组
②注：本子查询较难理解，应注意转换题目意思，然后从“不存在...不存在”这一语义进行解题。
【示例1】检索学过001号教师教的所有课程的所有学生的姓名：
- select Sname from Student where not exists (select * from Course where T# = '001' and not exists (select * from SC where S# = Student.S# and C# = Course.C#));
- 题目语句的意思等价于“不存在有一门001号教师教的课程该同学没学过”
【示例2】列出没学过李明老师讲授任何一门课程的所有学生的姓名：
- select Sname from Student where not exists (select * from Course, SC, Teacher where Tname = '李明' and Course.T# = Teacher.T# and Course.C# = SC.C# and S# = Student.S#);
【示例3】列出至少学过98030101号同学学过所有课程的同学的学号：
- select DISTINCT S# from SC SC1 where not exists (select * from SC SC2 where SC2.S# = '98030101' and not exists (select * from SC where C# = SC2.C# and S# = SC1.S#));
