1. 嵌套查询（课件 3.4.3）
一个SELECT里再套一个SELECT，外层的叫父查询，里层的叫子查询。写嵌套查询先问两件事。
第一件：内层的返回形状——形状决定外层能用哪个谓词。
| 内层返回什么 | 形状 | 外层能用的谓词 |
|---|---|---|
| 一个值 | 一行一列（标量） | 比较运算符=、>、<、>=、<=、!= |
| 一列值 | 一列多行（一个集合） | IN、ANY、ALL |
| 有没有结果 | 只回答"有 / 没有"，不返回数据 | EXISTS/NOT EXISTS |
| 一整张表 | 多行多列 | 放进FROM里当表用（见 §3） |
第二件：内层要不要靠外层才能算——这决定它什么时候被执行。
- 不相关子查询：内层不引用外层的列，自己就能跑。DBMS 先把它算一次，把结果当常量用。
- 相关子查询：内层引用了外层的列（写法上内外用到同一张表，就得起别名区分，如
SC x和SC y，靠y.Sno = x.Sno把两层连起来）。内层自己跑不了——外层每取出一行，就把这一行的值代进内层算一次，效果等于两层嵌套循环。
别名
x、y叫相关名。起别名的原因很实际：内外层是同一张表，不区分就没法说清"我这个 Sno 指的是外层那一行的，还是内层这一行的"。
1.1 带 IN 谓词的子查询
-- [例] 查询选修了课程名为"信息系统"的学生学号和姓名SELECTSno,Sname-- ③ 最后在 Student 关系中取出 Sno 和 SnameFROMStudentWHERESnoIN(SELECTSno-- ② 然后在 SC 关系中找出选修了 3 号课程的学生学号FROMSCWHERECnoIN(SELECTCno-- ① 首先在 Course 关系中找出"信息系统"的课程号，假设为 3FROMCourseWHERECname='信息系统'));- 内层返回什么：一列值（多行）——② 返回的是"选了 3 号课的所有学号"这一列，可能有几十行。
- 外层怎么用它：对
Student的每一行，问一句"我的Sno等于这一列里的某一个吗"，是就留下。所以IN不要求内层只返回一行，返回多少行都行。 - 这两层都是不相关子查询：内层没有引用外层的列，可以先算 ①、再算 ②、最后算 ③。写法上就是从最里层往外写，先把最明确的条件（
Cname = '信息系统'）定下来，再一层层往外套。 - 两条硬规则：内层的列数必须是一列（写成
SELECT Cno, Cname会报ERROR 1241 Operand should contain 1 column(s)）；内层返回 0 行时IN的结果是假，外层这一行不留下，不报错。
1.2 带比较运算符的子查询
-- [例] 查询与刘晨同在一系的学生学号和姓名SELECTSno,Sname,SmajorFROMStudentWHERESmajor=(SELECTSmajorFROMStudentWHERESname='刘晨');- 内层返回什么：一个值（一行一列）。这里
Sname = '刘晨'只会有一个人，所以内层只返回一个Smajor。 - 外层怎么用它：这个值就当成一个常量用，
Smajor = (子查询)和Smajor = 'CS'是同一种比较，只不过那个'CS'是临时查出来的。 - 所以能不能用比较运算符，取决于内层是不是只返回一个值：
| 内层返回 | 结果 |
|---|---|
| 恰好一行一列 | 正常比较 |
| 两行以上 | 报错ERROR 1242 Subquery returns more than 1 row（DBMS 不知道拿哪一个来比） |
| 零行 | 子查询的值当作NULL，=的结果是 UNKNOWN，这一行不满足条件（不报错） |
最后一行是常考的点：查"与不存在的人同系的学生"，结果不是报错，而是一行都查不出来。
相关子查询：内层每行重算一次
-- [例] 查询每个学生超过自己选修课程平均成绩的课程号SELECTSno,CnoFROMSC xWHEREGrade>=(SELECTAVG(Grade)FROMSC yWHEREy.Sno=x.Sno);把内层看成一个带参数的查询，参数就是外层的当前行：
SELECTAVG(Grade)FROMSC yWHEREy.Sno=?-- ? 由外层当前行的 x.Sno 提供执行一次完整的流程：
| 外层取到的当前行 x | ? | 内层实际执行的语句 | 内层返回 | 外层判断 |
|---|---|---|---|---|
| (2001, 1, 80) | 2001 | SELECT AVG(Grade) FROM SC y WHERE y.Sno = 2001 | 85 | 80 >= 85假，丢掉这一行 |
| (2001, 2, 90) | 2001 | 同上，还是这个学生 | 85 | 90 >= 85真，留下 |
| (2002, 2, 70) | 2002 | SELECT AVG(Grade) FROM SC y WHERE y.Sno = 2002 | 70 | 70 >= 70真，留下 |
要分清这里面有两处比较，各在各的位置：
- 内层自己的过滤
WHERE y.Sno = x.Sno：内层把y表（SC）逐行拿来，和自己的x.Sno当前值比，只留下属于这个学生的行——它决定"拿哪些行去算平均值"。这个比较的结果不出内层，也不返回给外层。 - 外层的比较
Grade >= (…)：等内层算出一个数之后，外层拿自己当前行的 Grade和这个数比——它决定这一行留不留。
内层向外返回的就是一个数：因为用了聚集函数AVG又没有GROUP BY，内层永远恰好返回一行一列（空集时返回一行 NULL）。这也是它能直接放在比较运算符右边的唯一原因。
其他要注意的：
- 为什么必须起别名：内外层都是
SC，不写x、y就没法说清是外层那一行还是内层这一行。 - 外层每换一行就重算一次，是 DBMS 的做法；语义上内层的结果只跟
x.Sno有关，同一个学生的多行算出来是同一个平均值。 - 成绩里如果有 NULL，
AVG会跳过它；如果某个学生所有成绩都是 NULL，AVG返回 NULL，比较结果就是 UNKNOWN，这个学生一行都不会留下。
1.3 带 ANY（SOME）或 ALL 谓词的子查询
内层返回**一列值（多行）**时，不能直接写比较运算符（会撞上 1242），要在比较运算符后面接ANY或ALL，表示"跟这一列里的值逐个比"。
| 谓词 | 含义 |
|---|---|
> ANY | 大于子查询结果中的某个值——只要比得过其中一个就算真 |
< ANY/<= ANY/>= ANY/= ANY/!= ANY | 同理：与结果中的某个值比较成立即可 |
> ALL | 大于子查询结果中的所有值——每一个都要比得过才算真 |
< ALL/<= ALL/>= ALL/= ALL | 同理：与结果中的每个值比较都要成立 |
!= ALL | 不等于子查询结果中的任何一个值 |
SOME和ANY是同一个意思（SOME是标准里的拼法）。
-- [例] 查询非 CS 专业中比 CS 专业任意一个学生年龄小的学生姓名和出生日期SELECTSname,SbirthdateFROMStudentWHERESbirthdate>ANY(SELECTSbirthdateFROMStudentWHERESMajor='CS')ANDSMajor<>'CS';- 内层返回的是"CS 专业所有学生的出生日期"这一列多行。
Sbirthdate > ANY (这一列)读作：我的出生日期比这一列里的某一个晚（= 年龄比某一个小）。- “比某一个晚"等价于"比其中最早的那个晚”，最早的那个就是
MIN，所以它能用聚集函数改写成单值比较，子查询只算一次就行：
SELECTSname,SbirthdateFROMStudentWHERESbirthdate>(SELECTMIN(Sbirthdate)FROMStudentWHERESMajor='CS')ANDSMajor<>'CS';ANY、ALL 与聚集函数、IN 的等价转换关系（课件表格）：
= | <> | < | <= | > | >= | |
|---|---|---|---|---|---|---|
| ANY | IN | — | < MAX | <= MAX | > MIN | >= MIN |
| ALL | — | NOT IN | < MIN | <= MIN | > MAX | >= MAX |
- 这张表要会两个方向用：看到
> ALL就换成> MAX（比所有人都大 = 比最大的还大），看到<> ALL就换成NOT IN。 - 内层返回空集时有个反直觉的结果：
> ANY为假（没有一个比得过），> ALL为真（找不到反例）。
1.4 带 EXISTS 谓词的子查询
EXISTS代表存在量词 ∃。它的规则和前面几种根本不同：
- 内层不返回任何数据，只产生逻辑真值 true / false：内层结果非空为 true，为空为 false。
- 所以内层的
SELECT列表写*就行——反正返回什么列都不看，只看"有没有行"。
-- [例] 查询所有选修了 1 号课程的学生姓名SELECTSnameFROMStudentWHEREEXISTS(SELECT*-- 目标列写 * 即可FROMSCWHERESno=Student.Sno-- 相关条件①：内层的 Sno 对上外层当前学生的 SnoANDCno='1');-- 内层自己的过滤条件这里的Sno = Student.Sno到底是什么，一次说清：
- 它是内层的 WHERE 条件，不是"子查询的返回值和外层比较"。子查询的返回值（那些行）压根不出内层，外层拿到的只有一个 true / false。
- 它比的是两个列：内层
SC的Sno和外层当前行的Student.Sno。写法上Student.Sno前必须带外层表名（或别名），不带的话会被当成内层SC的列。 - 它是逐行的：外层扫到刘晨那一行时，内层就变成
SELECT * FROM SC WHERE Sno = '2001' AND Cno = '1'；扫到王勇时Sno换成'2002'再来一遍。 AND Cno = '1'是内层自己的条件，跟外层无关。
执行过程：取外层的第一个元组 → 用它的Sno去跑内层查询 →WHERE为真就把这一行放进结果 → 取外层下一个元组，直到外层表检查完。
-- [例] 查询没有选修 1 号课程的学生姓名：NOT EXISTS 把真假反过来SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1');改写规则（课件）：所有带 IN、比较运算符、ANY、ALL 谓词的子查询都能等价改写成带 EXISTS 的子查询；反过来不行——有些 EXISTS / NOT EXISTS 的子查询无法用其他形式等价替换。
-- [例] 查询与"刘晨"在同一个主修专业学习的学生（把 1.2 的例子改写成 EXISTS 形式）SELECTSno,Sname,SmajorFROMStudent S1WHEREEXISTS(SELECT*FROMStudent S2WHERES2.Smajor=S1.SmajorANDS2.Sname='刘晨');- 同一个需求，
IN版是不相关子查询（内层只算一次），EXISTS版是相关子查询（外层每行算一次）。两者结果相同，MySQL 优化器一般会把它们转成同一种执行计划，别去背"哪个更快"，用EXPLAIN看。
1.5 比较的层次：单值、列、行、集合
前面几节都是"列和子查询比"。把镜头拉远一点，SQL 里能参与比较的操作数一共四种形态：
| 比什么 | 写法 | 语义 |
|---|---|---|
| 值和值 | WHERE Sage > 18 | 每行拿Sage和常量比 |
| 列和列（同一行内的两个列） | WHERE height > weight | 每行拿这一行的height和这一行的weight比 |
| 列和子查询返回的一个值 | WHERE Sage > (SELECT AVG(Sage) FROM Student) | 内层必须一行一列（§1.2） |
| 列和子查询返回的一列多行 | Sage > ALL (…)、Sage > ANY (…)、Sage IN (…) | 与集合里的每一个 / 至少一个比（§1.1、§1.3） |
| 一行和一行 | (Sno, Cno) = (SELECT Sno, Cno FROM SC WHERE …) | 列数一致，按位置一一对应（不看列名），内层必须恰好一行 |
| 一行和一列多行 | (Sno, Cno) IN (SELECT Sno, Cno FROM SC …) | 多行也行，等于"两列同时对上一行" |
| 有没有结果 | EXISTS (…) | 不比值，只看内层空不空（§1.4） |
-- 行构造器（整行比较）的两种写法WHERE(Sno,Cno)=(SELECTSno,CnoFROMSCWHERE...)-- 行子查询：内层必须恰好一行，多行报 1242WHERE(Sno,Cno)IN(SELECTSno,CnoFROMSC)-- 多行也行，等于"两列同时对上一行"列和列比，本质还是逐行、单值比单值，只不过右边从常量换成了另一列；它和
WHERE height > 180在结构上没有区别。没有"整列对整列"的运算符（写不出
A.col1 > B.col2就表示"A 的每个值都大于 B 的每个值"）。要表达"全部大于"，只能用聚集函数或双重否定：WHERE(SELECTMIN(col1)FROMA)>(SELECTMAX(col2)FROMB)-- 最小的 A 也大于最大的 BWHERENOTEXISTS(SELECT1FROMA,BWHEREA.col1<=B.col2)-- 不存在"不大于"的一对后一种就是 §1.6 里 ∀ 的写法（两处用的是同一个双重否定）。前一种在 B 为空表或取值全为 NULL 时会取到 NULL，比较结果不成立。
三层嵌套时相关条件里那种
Sno = Student.Sno AND Cno = Course.Cno，就是把"两列同时对上一行"用AND摊开写的，每个比较仍然是单值比单值；三层里最内层能看见中层和外层的所有列（Student.Sno来自最外层，Course.Cno来自中层），这正是"相关"能一层层套下去的原因。
跨表的列对列比较（补充）
-- ① 同一行内的两个列比：不需要连接，逐行比就完了SELECT*FROMuserWHEREheight>weight;-- ② 跨表的列对列比：必须先把两张表的行配成对SELECT*FROMStudent,SCWHEREStudent.Sno=SC.Sno-- 连接条件：把 SC 的行配到它所属的学生上ANDStudent.Sage>SC.Grade;-- 比较条件：配好对之后再逐对筛-- ③ 只写比较、不写连接条件会怎样SELECT*FROMStudent,SCWHEREStudent.Sage>SC.Grade;- ③ 的前提要说清：两张表之间没有连接条件时，DBMS 先把 A 的每一行和 B 的每一行全组合（笛卡尔积，1000 行 × 1000 行 = 100 万行），再一对一对地判断比较条件。语法合法，但结果通常没有业务意义，而且很慢。
A.col1 > B.col2本身不是连接条件。ON A.id = B.a_id那样的等值连接能把两边的行一一配上；>做不到，它只回答"这一对组合留不留"，配出来的可以是多对多。- 它是逐行筛选，不是"所有 A 都大于所有 B"：设 A = {10, 20}、B = {5, 15}，四对组合里留下 (10,5)、(20,5)、(20,15) 三对——既不是全留也不是全丢。要"每个 A 都大于每个 B"，得用上面那两种写法之一。
- 最后回到子查询：
SELECT *里的*不表示"整行参与比较"，只表示"返回哪些列无所谓"。子查询的返回值永远只在上面那几种形态里挑一种（一个值 / 一列值 / 有没有），不会拿一整行去和外层比。
1.6 全称量词怎么用 NOT EXISTS 写（本节难点）
SQL 里没有全称量词 ∀，靠这个转换：∀x P(x) ≡ ¬ ∃x(¬P(x))（“全都满足” = “不存在一个不满足的”）。
-- [例] 查询选修了全部课程的学生姓名-- 含义：没有一门课是他不选的SELECTSnameFROMStudentWHERENOTEXISTS-- 不存在这样的课程：(SELECT*FROMCourseWHERENOTEXISTS-- 这个学生没有选它(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno));三层要一层层读，注意哪一层的列是谁的：
| 层 | 遍历什么 | 条件里的列来自 | 这一层在问 |
|---|---|---|---|
| 最外层 | 每个学生 | Student.Sno是当前学生 | 对每个学生往下问 |
| 中层 | 每门课程 | Course.Cno是当前课程 | 有没有"这个学生没选这门课"的记录 |
| 最内层 | 选课记录 SC | Student.Sno来自最外层，Course.Cno来自中层 | 这个学生选没选这门课 |
- 只要有一门课没选，中层结果非空，
NOT EXISTS为假，该生被排除；所有课都选了，中层每门课的结果都为空，NOT EXISTS为真，该生留下。
-- [例] 查询选修了全部课程的学生学号（第二种写法：除法转成"计数相等"）SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(Cno)=(SELECTCOUNT(*)FROMCourse);- 第二种写法的前提是
SC的主码为(Sno, Cno)，一个学生一门课只有一条记录，所以"选课门数 = 课程总数"才等价于"全选了"。
-- [例] 查询至少选修了学生 95002 选修的全部课程的学生学号-- 记 p：95002 选了课程 y；q：学生 s 也选了课程 y，要的是 ∀y(p → q)-- 用 p → q ≡ ¬p ∨ q 化成 ¬∃y(p ∧ ¬q)：不存在"95002 选了而 s 没选"的课程SELECTDISTINCTSnoFROMSC scxWHERENOTEXISTS(SELECT*FROMSC scyWHEREscy.Sno='95002'ANDNOTEXISTS(SELECT*FROMSC sczWHEREscz.Sno=scx.SnoANDscz.Cno=scy.Cno));-- 方法二：计数SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMSCWHERESno='95002')GROUPBYSnoHAVINGCOUNT(Cno)=(SELECTCOUNT(Cno)FROMSCWHERESno='95002');方法二里
WHERE Cno IN (…)已经把范围限制在"95002 选过的课"内，所以后面COUNT(Cno)数出来的就是"该生选了其中几门"。练习（课件 19 页）：查询全部学生都选修的课程课号：
SELECTCnoFROMSCGROUPBYCnoHAVINGCOUNT(DISTINCTSno)=(SELECTCOUNT(*)FROMStudent);
2. 集合查询（课件 3.4.4）
-- 并：查询选修了课程 1 或课程 2 的学生SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2';-- 等价写法SELECTDISTINCTSnoFROMSCWHERECno='1'ORCno='2';-- 交：查询选修了课程 1 又选修了课程 2 的学生SELECTSnoFROMSCWHERECno='1'INTERSECTSELECTSnoFROMSCWHERECno='2';-- MySQL 没有 INTERSECT，用 IN 绕过去SELECTSnoFROMSCWHERECno='1'ANDSnoIN(SELECTSnoFROMSCWHERECno='2');- 参加集合运算的各结果表列数必须相同，对应项的数据类型也必须相同。
- 标准 SQL 直接支持的只有
UNION；INTERSECT、EXCEPT是一般商用库扩展的，MySQL 不支持。 UNION会去重（排序后合并），要保留重复行得写UNION ALL。
3. 基于派生表查询（课件 3.4.5）
子查询放在FROM子句里，得到的就是一张临时派生表，必须给它起别名：
-- [例] 查询选修了 1 号课程的学生姓名SELECTSnameFROMStudent,(SELECTSnoFROMSCWHERECno='1')ASsc1WHEREStudent.Sno=sc1.Sno;-- 也可以先把子查询定义成 WITH（公共表表达式），再当表用WITHsc1(Sno)AS(SELECTSnoFROMSCWHERECno='1')SELECTSnameFROMStudent,sc1WHEREStudent.Sno=sc1.Sno;子查询还可以放在SELECT子句里，返回标量子查询（一个具体值）：
-- [例] 查询每门课程的选课人数信息SELECTCno,Cname,(SELECTCOUNT(*)FROMSCWHERESC.Cno=Course.Cno)ASnum_studFROMCourse;把它和"连接 + 分组"对照——下面两条结果并不相同（课件 28~29 页问的就是这件事）：
-- ① 内连接 + 分组：没有人选的课不会出现在结果里SELECTSC.Cno,Cname,COUNT(Sno)ASnum_studFROMSC,CourseWHERESC.Cno=Course.CnoGROUPBYSC.Cno,Cname;-- ② 左外连接 + 分组：没有人选的课也留下来，人数记 0SELECTCourse.Cno,Cname,COUNT(Sno)ASnum_studFROMCourseLEFTJOINSCONSC.Cno=Course.CnoGROUPBYCourse.Cno,CnameORDERBYCourse.Cno;- 差别来自连接方式：内连接只保留两边都匹配上的行，没人选的课被丢掉；左外连接保留左表（Course）的全部行，这些行的
SC侧全是NULL，COUNT(Sno)数出 0。 - 课件那段代码把
GROUP BY写成了sc.cno——没人选的课在SC侧是NULL，会和别的课分不到一组、课号也取不出来，要正确显示课号得按course.cno分组。
4. 综合应用的注意点（课件 30 页小结）
-- ① 聚集函数只能出现在 SELECT 子句和 HAVING 短语中，不能出现在 WHERE 子句里SELECTSnoFROMSCWHEREAVG(Grade)>80GROUPBYSno;-- ✗ 错SELECTSnoFROMSCGROUPBYSnoHAVINGAVG(Grade)>80;-- ✓ 对-- ② 聚集函数不能嵌套：要"平均成绩最高的"不能写 MAX(AVG(Grade))- 结果输出列只能取自最外层查询所用的表，子查询里的属性不能作为最终输出；输出涉及多个表的列时，最外层必须写成连接查询。
ORDER BY只对最终结果排序，所以通常写在最后。
