第8讲：复杂查询

今天药忘吃喽~ 2021-12-21 14:07 504阅读 0赞

一、子查询概述

1. 子查询定义

①出现在where子句中的select语句被称为子查询，即在where子句中嵌套一个select-from-where

②子查询返回一个集合，可以通过与这个集合比较来确定另一个查询集合

2. 三种类型的子查询

*  (not) in子查询
 *  θ some/θ all子查询
 *  (not) exists子查询

3. 带有子查询的select语句区分为内层查询和外层查询（内层查询被嵌入到外层查询中）

![1200887-20190420161407086-107936087.jpg][]

①非相关子查询：内层查询独立进行，没有涉及任何外层查询相关信息的子查询

②相关子查询：内层查询需要依靠外层查询的某些参量作为限定条件才能进行

*  外层向内层传递的参量需要使用外层的表名或表别名来限定
 *  相关子查询只能由外层向内层传递参数，这也称为变量的作用域规则

【示例】求学过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\#));

转载于:https://www.cnblogs.com/xzxl/p/10742111.html

[1200887-20190420161407086-107936087.jpg]: /images/20211220/c9aa3cd9b9894582998cafa283118948.png