SQL 数据查询

发表于 2025-04-03 Waline：阅读次数：本文字数： 4.8k 阅读时长 ≈ 18 分钟

基础 SQL 数据查询

数据查询是数据库的核心功能，SQL 通过 SELECT 语句提供强大而灵活的查询能力。其基本结构和执行逻辑是理解 SQL 的关键。

`SELECT` 语句基本结构与执行顺序

基本结构

SELECT 语句由多个子句组成，用于指定查询的不同方面：

SELECT [ALL | DISTINCT] <目标列表达式> [AS <别名>] [, ...]       -- 目标子句（5）
FROM <表名或视图名> [AS <别名>] [, ...] | (<子查询>) [AS <别名>] -- 范围子句（1）
[WHERE <条件表达式>]                                             -- 条件子句（2）
[GROUP BY <列名 1> [, <列名 2> ...]]                             -- 分组子句（3）
[HAVING <分组过滤条件表达式>]                                    -- 分组过滤子句（4）
[ORDER BY <列名 A> [ASC | DESC] [, <列名 B> [ASC | DESC] ...]];  -- 排序子句（6）

SELECT：指定查询结果中包含哪些列（投影）。
FROM：指定查询操作的对象（数据来源，可以是表、视图或子查询结果）。
WHERE：指定行过滤条件（选择），在分组前作用于 FROM 子句产生的中间结果。
GROUP BY：将结果集按一列或多列的值进行分组，值相等的为一组。
HAVING：指定分组过滤条件，在分组后作用于 GROUP BY 产生的组。
ORDER BY：指定查询结果的排序方式（升序 ASC 或降序 DESC）。

逻辑执行顺序

尽管 SQL 语句的书写顺序是 SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY，但数据库管理系统（DBMS）在逻辑上通常按以下顺序处理这些子句（实际物理执行可能因优化而不同）：

FROM：确定查询的数据源，如果是多表，执行笛卡尔积（或根据 JOIN 类型连接）。
WHERE：根据指定的条件筛选 FROM 子句产生的行。
GROUP BY：将 WHERE 子句筛选后的结果按指定列进行分组。
HAVING：筛选 GROUP BY 子句产生的组。
SELECT：计算最终要输出的列，包括列、表达式、聚合函数等。处理 DISTINCT 关键字。
ORDER BY：对最终结果集进行排序。

graph TD
    A[(FROM & JOINs)] --> B(WHERE);
    B --> C(GROUP BY);
    C --> D(HAVING);
    D --> E(SELECT);
    E --> F(DISTINCT);
    F --> G(ORDER BY);
    G --> H{Final Result};

    style A fill:#f9f,stroke:#333,stroke-width:2px
    style B fill:#ccf,stroke:#333,stroke-width:2px
    style C fill:#fcf,stroke:#333,stroke-width:2px
    style D fill:#9cf,stroke:#333,stroke-width:2px
    style E fill:#ff9,stroke:#333,stroke-width:2px
    style F fill:#f99,stroke:#333,stroke-width:2px
    style G fill:#9ff,stroke:#333,stroke-width:2px
    style H fill:#cfc,stroke:#333,stroke-width:4px

逻辑顺序 vs. 物理执行

这只是逻辑处理顺序，DBMS 的查询优化器可能会根据实际情况（如索引、统计信息等）调整物理执行计划以提高效率，但最终结果必须与遵循逻辑顺序得到的结果一致。

`SELECT` 子句详解

SELECT 子句定义了查询结果的结构，即最终输出哪些列。

选择所有列：使用星号 *。

SELECT * FROM Student; -- 查询 Student 表的所有列

选择指定列：列出需要查询的列名，用逗号分隔。
SELECT Sno, Sname FROM Student; -- 查询学号和姓名
使用表达式：目标列表达式可以是列名、常量、函数或算术、字符串表达式。
1
2
3
-- 查询学生姓名及其出生年份
SELECT Sname, 2025 - Sage AS BirthYear
FROM Student;

SELECT 子句不仅限于选择表中已有的列，还可以包含计算或转换后的值，这被称为广义投影。

列别名（Alias）：使用 AS 关键字（或省略 AS 直接跟别名）为结果列指定一个更易读或在后续处理中更方便使用的名称。
SELECT Sname AS 姓名, Sdept Department FROM Student;
消除重复行（Distinct）：使用 DISTINCT 关键字去除结果集中的重复元组。默认行为是 ALL，即保留所有行。
1
2
-- 查询选修了课程的学生学号（去除重复）
SELECT DISTINCT Sno FROM SC;

`FROM` 子句详解

FROM 子句指定查询的数据来源。

单个表：FROM <表名>
多个表：FROM <表名1>, <表名2>, ...
- 这在逻辑上会先生成所有表的笛卡尔积，然后通常由 WHERE 子句中的连接条件进行筛选。
表别名（Alias）：为表指定别名，可以简化查询语句，尤其是在多表连接或自连接时，以及用于区分不同表中可能存在的同名列。
1
2
3
SELECT S.Sname, C.Cname
FROM Student S, Course C, SC
WHERE S.Sno = SC.Sno AND SC.Cno = C.Cno; -- 使用别名 S 和 C

别名的作用

简化表名书写。
区分不同表中的同名列（如 Student.Sno 和 SC.Sno）。
在自连接中区分同一张表的两个实例。

`WHERE` 子句详解

WHERE 子句用于设置行过滤条件，只有满足条件的行才会被包含在后续的处理（如分组、聚合、投影）中。

作用对象：FROM 子句产生的（可能是笛卡尔积或连接后的）中间结果集中的每一行。
条件表达式：可以包含多种谓词，并通过逻辑运算符 AND, OR, NOT 组合。

常用查询谓词：

比较：=, >, <, >=, <=, !=, <> (后两者通常等价，表示不等于)

-- 查询计算机科学系（CS）的学生
SELECT Sname FROM Student WHERE Sdept = 'CS';
-- 查询年龄小于 20 岁的学生
SELECT Sname, Sage FROM Student WHERE Sage < 20;
-- 查询成绩不及格（小于 60）的学生学号
SELECT DISTINCT Sno FROM SC WHERE Grade < 60;

范围：BETWEEN <值1> AND <值2>, NOT BETWEEN <值1> AND <值2>

包含边界值。

1 2	-- 查询年龄在 20 到 23 岁之间的学生 SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23;

集合：IN (<值列表>), NOT IN (<值列表>)

判断列的值是否在给定的集合中。值列表可以是常量，也可以是子查询的结果。

-- 查询 CS, MA, IS 系的学生
SELECT Sname, Ssex FROM Student WHERE Sdept IN ('CS', 'MA', 'IS');
-- 等价于
SELECT Sname, Ssex FROM Student WHERE Sdept = 'CS' OR Sdept = 'MA' OR Sdept = 'IS';

字符匹配：LIKE '<匹配串>', NOT LIKE '<匹配串>'

用于字符串模式匹配^[1]。
通配符：
- %：代表零个或多个任意字符，即 .*
- _：代表一个任意字符，即 .。
ESCAPE 子句：如果匹配串中需要包含通配符本身，可以使用 ESCAPE 关键字指定一个转义字符。
- MySQL 默认使用 \ 作为转义符。

-- 查询所有姓「刘」的学生
SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE '刘%';
-- 查询姓名第二个字是「阳」的学生
SELECT Sname, Sno FROM Student WHERE Sname LIKE '_阳%';
-- 查询课程名包含 'DB_' 的课程 (假设 '_' 是普通字符)
SELECT Cno, Ccredit FROM Course WHERE Cname LIKE 'DB\_%' ESCAPE '\'; -- 使用 '\' 作为转义符

空值：IS NULL, IS NOT NULL
- 用于判断列的值是否为 NULL。
- 不能使用 = 或 != 来判断 NULL，必须使用 IS NULL 或 IS NOT NULL。
1
2
-- 查询缺少成绩的学生学号和课程号
SELECT Sno, Cno FROM SC WHERE Grade IS NULL;
逻辑运算：AND, OR, NOT
- 用于组合多个条件。
- 优先级：NOT > AND > OR。可以使用括号 () 改变优先级。
1
2
-- 查询 CS 系年龄小于 20 的学生
SELECT Sname FROM Student WHERE Sdept = 'CS' AND Sage < 20;

`ORDER BY` 子句详解

ORDER BY 子句用于对最终查询结果进行排序。

排序方式：ASC（升序，默认, Ascending），DESC（降序，Descending）。
多列排序：可以指定多个排序列，优先级从左到右。
空值排序：NULL 值的排序位置（最前或最后）取决于具体的 DBMS 实现。

-- 查询选修了 3 号课程的学生学号和成绩，按成绩降序排列
SELECT Sno, Grade FROM SC WHERE Cno = '3' ORDER BY Grade DESC;

-- 查询所有学生信息，按所在系升序排列，同一系内按年龄降序排列
SELECT * FROM Student ORDER BY Sdept ASC, Sage DESC;

聚合函数

聚合函数（Aggregate Functions）对一组值进行计算，并返回单个值。常与 GROUP BY 子句配合使用。

函数	描述
`COUNT(*)`	统计组内（或整个表）的元组总数
`COUNT([DISTINCT] 列名)`	统计组内（或整个表）指定列的非空值的个数（`DISTINCT` 去重）
`SUM([DISTINCT] 列名)`	计算组内（或整个表）指定列（数值型）的总和（`DISTINCT` 去重后求和）
`AVG([DISTINCT] 列名)`	计算组内（或整个表）指定列（数值型）的平均值（`DISTINCT` 去重后求平均）
`MAX([DISTINCT] 列名)`	计算组内（或整个表）指定列的最大值（`DISTINCT` 通常无意义）
`MIN([DISTINCT] 列名)`	计算组内（或整个表）指定列的最小值（`DISTINCT` 通常无意义）

NULL 值处理：

除 COUNT(*) 外，所有聚合函数在计算前都会忽略 NULL 值。
如果作用于空集（例如，WHERE 条件过滤掉了所有行，或者 HAVING 过滤掉了所有组），COUNT 返回 0，其他聚合函数返回 NULL。

使用 DISTINCT：

DISTINCT 可以在 COUNT, SUM, AVG 中使用，表示在计算前先去除重复值。

示例：

-- 查询学生总人数
SELECT COUNT(*) AS TotalStudents FROM Student;

-- 查询选修了课程的学生总数（去重）
SELECT COUNT(DISTINCT Sno) AS StudentsWithCourses FROM SC;

-- 计算 1 号课程的平均成绩
SELECT AVG(Grade) AS AvgGrade_C1 FROM SC WHERE Cno = '1';

-- 查询选修 1 号课程的最高分
SELECT MAX(Grade) AS MaxGrade_C1 FROM SC WHERE Cno = '1';

-- 查询学生「201215121」选修课程的总学分 (需要连接 Course 表)
SELECT SUM(C.Ccredit) AS TotalCredits
FROM SC, Course C
WHERE SC.Sno = '201215121' AND SC.Cno = C.Cno;

-- 同时查询学生「201215121」选修的课程门数、平均成绩、最高分、最低分（假设成绩 >= 60 为通过）
SELECT COUNT(*) AS NumCourses, AVG(Grade) AS AvgGrade, MAX(Grade) AS MaxGrade, MIN(Grade) AS MinGrade
FROM SC
WHERE Sno = '201215121' AND Grade >= 60;

`GROUP BY` 子句与 `HAVING` 子句

GROUP BY 和 HAVING 子句用于实现分组统计查询。

GROUP BY 子句：
- 将查询结果（经过 WHERE 筛选后）按一列或多列的值进行分组，值完全相同的行会分到同一组。
- 聚合函数会分别作用于每个分组。
- 规则：出现在 SELECT 列表中但未被聚合函数包裹的列，必须出现在 GROUP BY 子句中。
  - 标准 SQL 要求，部分 DBMS 可能有放宽，但不建议依赖。
HAVING 子句：
- 用于对 GROUP BY 产生的分组进行过滤，只有满足 HAVING 条件的组才会被保留。
- 条件表达式通常包含聚合函数。

GROUP BY 规则

如果要显示某列的具体值（而不是统计值），就必须按这列来分类。

因为当使用 GROUP BY 时，数据库会将多行数据合并为一组。对于：

聚合函数：可以计算整个组的统计值（如总数、平均值）
非聚合列：如果不指定分组依据，数据库不知道应该显示组中哪一行的值

正确示例：

– 统计每个部门的员工数量
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;

这里：

department 出现在 SELECT 中且未被聚合，所以必须在 GROUP BY 中
COUNT(*) 是聚合函数，不需要在 GROUP BY 中

错误示例：

– 错误的写法（在某些数据库中会报错）
SELECT department, employee_name, COUNT(*)
FROM employees
GROUP BY department;

这里 employee_name 既没有被聚合，也没有出现在 GROUP BY 中，数据库不知道应该显示哪个员工的名字。

特性	`WHERE` 子句	`HAVING` 子句
作用对象	行（元组）	组（Group）
作用时机	分组（`GROUP BY`）之前	分组（`GROUP BY`）之后
条件内容	不能包含聚合函数	通常包含聚合函数

示例：

-- 求每个课程号及相应的选课人数
SELECT Cno, COUNT(Sno) AS NumStudents
FROM SC
GROUP BY Cno;

-- 查询选修了 3 门以上课程的学生学号
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) > 3;

-- 查询平均成绩大于等于 90 分的学生学号和平均成绩
SELECT Sno, AVG(Grade) AS AvgGrade
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 90;

WHERE 中不能使用聚合函数

以下查询是错误的，因为 WHERE 子句在分组前执行，无法使用聚合函数：

– 错误示例！
SELECT Sno, AVG(Grade)
FROM SC
WHERE AVG(Grade) >= 90 – 不能在 WHERE 中使用 AVG
GROUP BY Sno;

应使用 HAVING 子句，如上一个正确示例所示。

分组统计执行过程示例（逻辑）：考虑查询每个部门（DEP）中非经理（JOB <> 'M'）职位的员工（EMPL）的平均工资（AVG(SAL)），并按平均工资降序排列：

SELECT DEP, JOB, AVG(SAL) AS AvgSalary
FROM EMPL
WHERE JOB <> 'M'
GROUP BY DEP, JOB
ORDER BY AvgSalary DESC;

FROM EMPL：获取 EMPL 表所有数据。
WHERE JOB <> 'M'：筛选出职位不是 'M' 的员工行。
GROUP BY DEP, JOB：将筛选后的行按 DEP 和 JOB 的组合进行分组。
- 例如，所有 DEP='BLU', JOB='S' 的员工分为一组，DEP='GRE', JOB='C' 的分为一组，等等。
SELECT DEP, JOB, AVG(SAL)：对每个分组计算平均工资 AVG(SAL)，并选择分组依据 DEP, JOB 以及计算出的平均工资。
ORDER BY AvgSalary DESC：对最终生成的结果行按 AvgSalary（即 AVG(SAL)）降序排序。

如果再加上 HAVING AVG(SAL) > 28000：

SELECT DEP, JOB, AVG(SAL) AS AvgSalary
FROM EMPL
WHERE JOB <> 'M'
GROUP BY DEP, JOB
HAVING AVG(SAL) > 28000 -- 增加 HAVING 子句
ORDER BY AvgSalary DESC;

在上述步骤 3 和 4 之间会插入一步 HAVING AVG(SAL) > 28000，即对 GROUP BY 产生的每个组计算 AVG(SAL)，只保留平均工资大于 28000 的组。

然后步骤 4 和 5 只对通过 HAVING 筛选的组进行操作。

进阶 SQL 数据查询

连接查询

当查询需要的信息分散在多个表中时，需要使用连接查询。

连接条件：通常在 WHERE 子句中指定，用来关联两个或多个表中的行。连接条件定义了表之间如何匹配。
- 一般格式：[<表1>.]<列1> <比较运算符> [<表2>.]<列2>
- 连接字段：连接条件中使用的列，它们的类型必须可比较，但名称不必相同。

等值连接/自然连接

等值连接：连接运算符为 =。
自然连接：一种特殊的等值连接，要求两个关系中进行比较的分量必须是同名的属性组，并且在结果中去除重复的属性列。

-- 查询每个学生及其选修课程的情况（等值连接）

--- 写法 1：在 WHERE 中指定连接条件
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;

--- 写法 2：使用 JOIN...ON 语法（推荐）
SELECT Student.*, SC.*
FROM Student JOIN SC ON Student.Sno = SC.Sno;

-- 自然连接示例（假设 Student 和 SC 都有 Sno 列）
--- 注意：标准 SQL 有 NATURAL JOIN 关键字，但需谨慎使用，因为它依赖于列名匹配，可能不符合预期。
--- 通常用 JOIN...USING 或 JOIN...ON 实现类似效果。
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student JOIN SC ON Student.Sno = SC.Sno; -- 结果中 Sno 只出现一次（取决于 DBMS 实现和 SELECT 列表）
--- 或者使用 USING（如果列名相同）
SELECT Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student JOIN SC USING(Sno);

连接操作的执行过程（常见方法），DBMS 可能采用多种算法执行连接：

嵌套循环连接（Nested-Loop Join）：对第一个表（外层循环）的每一行，扫描整个第二个表（内层循环），查找满足连接条件的行。简单但可能效率低。
索引连接（Index Join）：如果第二个表的连接字段上有索引，则对于第一个表的每一行，利用索引快速查找第二个表中匹配的行。效率较高。
排序合并连接（Sort-Merge Join）：首先将两个表按连接字段排序，然后像拉链一样合并两个排序好的表，查找匹配的行。适用于等值连接，尤其在表较大时。

自连接

自连接即一个表与其自身进行连接。

必要条件：必须为表使用不同的别名。
列引用：必须使用别名来限定列名。

-- 查询每一门课的直接先修课的先修课（间接先修课）
SELECT F.Cno, S.Cpno AS Indirect_Cpno
FROM Course F, Course S -- 使用别名 F 和 S 代表 Course 表的两个实例
WHERE F.Cpno = S.Cno;   -- F 的先修课号 = S 的课程号

外连接

普通连接（内连接）只返回满足连接条件的行。外连接则会保留一个或两个表中不满足连接条件的行，并在对应的另一个表的列中填入 NULL。

LEFT OUTER JOIN/LEFT JOIN：保留左表中所有的行。如果在右表中找不到匹配行，则右表列填充 NULL。
RIGHT OUTER JOIN/RIGHT JOIN：保留右表中所有的行。如果在左表中找不到匹配行，则左表列填充 NULL。
FULL OUTER JOIN/FULL JOIN：保留左表和右表中所有的行。如果某行在一侧没有匹配，则另一侧列填充 NULL。

-- 查询所有学生及其选修课程的情况，即使学生没有选课也要列出（左外连接）
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT OUTER JOIN SC ON Student.Sno = SC.Sno;
-- 对于没有选课的学生，Cno 和 Grade 列将显示 NULL

多表连接

多表连接即连接两个以上的表。

连接条件：需要足够的连接条件将所有表关联起来。通常，连接 $n$ 个表至少需要 $n-1$ 个连接条件。

-- 查询每个学生的学号、姓名、选修的课程名及成绩
SELECT S.Sno, S.Sname, C.Cname, SC.Grade
FROM Student S, SC, Course C -- 连接三个表
WHERE S.Sno = SC.Sno AND SC.Cno = C.Cno; -- 两个连接条件

-- 使用 JOIN 语法（推荐）
SELECT S.Sno, S.Sname, C.Cname, SC.Grade
FROM Student S
JOIN SC ON S.Sno = SC.Sno
JOIN Course C ON SC.Cno = C.Cno;

连接与选择同时进行

WHERE 子句可以同时包含连接条件和行选择条件。DBMS 通常会优化执行，尽早应用选择条件以减少中间结果的大小。

– 查询选修了 2 号课程且成绩在 90 分以上的学生的学号和姓名
SELECT S.Sno, S.Sname
FROM Student S JOIN SC ON S.Sno = SC.Sno
WHERE SC.Cno = '2' AND SC.Grade > 90; – SC.Cno = '2' 和 SC.Grade > 90 是选择条件

嵌套查询

嵌套查询（Nested Query/Subquery）即一个 SELECT 语句（子查询）嵌套在另一个 SELECT 语句（父查询）的子句（如 WHERE, FROM, SELECT, HAVING）中。

查询块（Query Block）：每个 SELECT-FROM-WHERE 语句构成一个查询块。
父查询（Outer Query）/子查询（Inner Query/Subquery）

子查询的类型：

不相关子查询（独立子查询，Uncorrelated Subquery）：
- 子查询可以独立执行，不依赖于父查询。
- 执行过程：先执行子查询，其结果作为父查询的条件（或数据源等）。子查询只执行一次。
相关子查询（Correlated Subquery）：
- 子查询的执行依赖于父查询当前处理的行的值。
- 执行过程：类似于嵌套循环。对父查询的每一行，将相关列的值传递给子查询，执行子查询，然后根据子查询的结果判断父查询当前行是否满足条件。子查询可能执行多次。

具体例子

不相关子查询（独立子查询）：子查询可以独立运行，不依赖外部查询，只执行一次。

数据库先执行子查询
将子查询结果缓存
用这个结果执行外部查询

示例：找出工资高于公司平均工资的员工

1
2
3

SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

先执行 SELECT AVG(salary) FROM employees 得到公司平均工资（比如 5000）
然后执行 SELECT employee_name, salary FROM employees WHERE salary > 5000

相关子查询：子查询依赖外部查询的当前行数据，会为外部查询的每一行执行一次。

从外部查询取一行
将该行的相关值传递给子查询
执行子查询
根据子查询结果决定是否保留该行
重复上述过程直到处理完所有行

示例：找出每个部门中工资高于该部门平均工资的员工

SELECT e1.employee_name, e1.salary, e1.department
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department -- 子查询依赖外部查询的 department
);

从 employees 表（e1）取第一行员工记录
将该员工的部门传给子查询，计算该部门的平均工资
比较该员工工资是否大于这个部门平均
如果是，保留该行
对 e1 表的每一行重复上述过程

限制：

子查询内部通常不能使用 ORDER BY 子句（除非配合 TOP/LIMIT 等限制结果数量的子句，且在 FROM 子句中）。

带有子查询的谓词：

IN/NOT IN：判断父查询的某个表达式的值是否在（或不在）子查询返回的结果集合中。子查询通常返回单列。

-- 查询与「刘晨」在同一个系学习的学生（不相关子查询）
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname = '刘晨')
  AND Sname <> '刘晨'; -- 排除刘晨自己

-- 查询选修了课程名为「信息系统」的学生学号和姓名（不相关子查询）
SELECT Sno, Sname
FROM Student
WHERE Sno IN (SELECT Sno
              FROM SC
              WHERE Cno IN (SELECT Cno
                            FROM Course
                            WHERE Cname = '信息系统'));

连接 vs. IN 子查询

很多 IN 子查询可以等价地用连接查询实现。通常连接查询的性能更好，因为 DBMS 的优化器对连接的处理更成熟。

– 上一个例子用连接查询实现
SELECT S.Sno, S.Sname
FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON SC.Cno = C.Cno
WHERE C.Cname = '信息系统';

比较运算符（>, <, =, >=, <=, !=, <>）：

当子查询确定只返回单个值时（例如，子查询使用了聚合函数且没有 GROUP BY，或者查询条件基于主键），可以直接使用比较运算符。

-- 查询年龄等于「刘晨」年龄的学生（假设姓名唯一，子查询返回单值）
-- 注意：如果 Sname 不唯一，此查询可能出错或结果不确定
SELECT Sno, Sname, Sage
FROM Student
WHERE Sage = (SELECT Sage FROM Student WHERE Sname = '刘晨');

-- 找出每个学生超过他自己选修课程平均成绩的课程号（相关子查询）
SELECT Sno, Cno
FROM SC x -- 父查询使用别名 x
WHERE Grade >= (SELECT AVG(Grade)
                FROM SC y -- 子查询使用别名 y
                WHERE y.Sno = x.Sno); -- 子查询依赖父查询的 x.Sno

单值子查询

直接使用比较运算符要求子查询返回单行单列。如果子查询可能返回多行或零行，直接比较通常会出错。应谨慎使用，或改用 IN, ANY/ALL, EXISTS。

ANY/SOME/ALL：与比较运算符结合使用，处理子查询返回多值（一列多行）的情况。SOME 是 ANY 的同义词。

-- 查询非 CS 系中，比 CS 系任意一个学生年龄小的学生姓名和年龄
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';

-- 查询非 CS 系中，比 CS 系所有学生年龄都小的学生姓名和年龄
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';

谓词	含义	等价聚合（若适用）
`= ANY`	等于子查询结果中的某个值	`IN`
`!= ANY`	不等于子查询结果中的某个值（很少用）
`> ANY`	大于子查询结果中的某个值（即大于最小值）	`> MIN(...)`
`< ANY`	小于子查询结果中的某个值（即小于最大值）	`< MAX(...)`
`>= ANY`	大于等于子查询结果中的某个值	`>= MIN(...)`
`<= ANY`	小于等于子查询结果中的某个值	`<= MAX(...)`
`= ALL`	等于子查询结果中的所有值（仅当子查询结果为单值时才有意义，很少用）
`!= ALL`	不等于子查询结果中的所有值	`NOT IN`
`> ALL`	大于子查询结果中的所有值（即大于最大值）	`> MAX(...)`
`< ALL`	小于子查询结果中的所有值（即小于最小值）	`< MIN(...)`
`>= ALL`	大于等于子查询结果中的所有值	`>= MAX(...)`
`<= ALL`	小于等于子查询结果中的所有值	`<= MIN(...)`

EXISTS/NOT EXISTS：判断子查询的结果集是否为空。

EXISTS：如果子查询返回至少一行，则 EXISTS 返回 TRUE；否则返回 FALSE。
NOT EXISTS：如果子查询返回空集，则 NOT EXISTS 返回 TRUE；否则返回 FALSE。
子查询的 SELECT 列表通常用 *^[2]，因为不关心返回的具体值，只关心是否有行返回。
EXISTS 通常用于相关子查询。

-- 查询所有选修了 1 号课程的学生姓名（使用 EXISTS）
SELECT Sname
FROM Student S
WHERE EXISTS (SELECT *
              FROM SC
              WHERE SC.Sno = S.Sno AND SC.Cno = '1'); -- 相关子查询

-- 查询没有选修 1 号课程的学生姓名（使用 NOT EXISTS）
SELECT Sname
FROM Student S
WHERE NOT EXISTS (SELECT *
                  FROM SC
                  WHERE SC.Sno = S.Sno AND SC.Cno = '1');

EXISTS 的威力：实现全称量词（ $\forall$ ）

SQL 没有直接的全称量词（如 for all）。但可以通过双重否定 NOT EXISTS ... NOT EXISTS ... 来模拟。

逻辑等价： $\forall x P(x) \equiv \neg \exists x \neg P(x)$
「对于所有的 $x$ ， $P(x)$ 都成立」等价于「不存在 $x$ 使得 $P(x)$ 不成立」
示例：查询选修了学生「201215122」选修的全部课程的学生号码。
语义：查询学生 SCX.Sno，要求不存在这样的课程 SCY.Cno（该课程被 201215122 选修），使得该学生 SCX.Sno 没有选修这门课程 SCY.Cno。

SELECT DISTINCT SCX.Sno – 外层查询学生
FROM SC SCX
WHERE NOT EXISTS ( – 不存在这样的课程 Y…
    SELECT 
    FROM SC SCY
    WHERE SCY.Sno = '201215122' – …是 201215122 选修的课程 Y
      AND NOT EXISTS ( – …使得学生 X 没有选修课程 Y
          SELECT 
          FROM SC SCZ
          WHERE SCZ.Sno = SCX.Sno – 学生 X
            AND SCZ.Cno = SCY.Cno – 选修了课程 Y
      )
);

示例：查询选修了全部课程的学生姓名。
语义：查询学生 S.Sname，要求不存在这样的课程 C.Cno，使得该学生 S.Sno 没有选修这门课程 C.Cno。

SELECT Sname
FROM Student S
WHERE NOT EXISTS ( – 不存在这样的课程 C…
    SELECT 
    FROM Course C
    WHERE NOT EXISTS ( – …使得学生 S 没有选修课程 C
        SELECT 
        FROM SC
        WHERE SC.Sno = S.Sno
          AND SC.Cno = C.Cno
    )
);

这种双重 NOT EXISTS 的结构是实现「查询选修了 A 所选全部课程的 B」或「查询选修了全部课程的 C」这类问题的标准 SQL 模式，也称为关系除法的 SQL 实现。

集合查询（Set Query）

对两个或多个 SELECT 语句的结果集进行集合运算。

要求：参与运算的各个查询结果必须具有相同数量的列，且对应列的数据类型必须兼容（或可隐式转换）。
运算符：
- UNION：并集。合并结果集，并自动去除重复行。
- UNION ALL：并集。合并结果集，保留所有行（包括重复行）。效率通常比 UNION 高。
- INTERSECT：交集。返回同时存在于所有结果集中的行，并自动去除重复行。
- EXCEPT（或 MINUS，取决于 DBMS）：差集。返回存在于第一个结果集中，但不存在于第二个结果集中的行，并自动去除重复行。

-- 查询 CS 系的学生 或 年龄不大于 19 岁的学生（并集, 去重）
SELECT * FROM Student WHERE Sdept = 'CS'
UNION
SELECT * FROM Student WHERE Sage <= 19;

-- 查询既选修了 1 号课程，又选修了 2 号课程的学生（交集）
SELECT Sno FROM SC WHERE Cno = '1'
INTERSECT
SELECT Sno FROM SC WHERE Cno = '2';
-- 这个查询也可以用 IN 或连接实现：
SELECT Sno FROM SC WHERE Cno = '1' AND Sno IN (SELECT Sno FROM SC WHERE Cno = '2');

-- 查询 CS 系的学生，但排除年龄不大于 19 岁的学生（差集）
SELECT * FROM Student WHERE Sdept = 'CS'
EXCEPT
SELECT * FROM Student WHERE Sage <= 19;
-- 等价于：
-- SELECT * FROM Student WHERE Sdept = 'CS' AND Sage > 19;

基于派生表的查询（Derived Table）

子查询不仅可以用在 WHERE 或 HAVING 子句中，还可以用在 FROM 子句中。此时，子查询的结果被当作一个临时的表（称为派生表或内联视图），父查询可以像查询普通表一样查询这个派生表。

语法：FROM (<子查询>) AS <派生表别名> [(<列别名列表>)]
别名：必须为派生表指定一个别名。列别名是可选的，如果省略，则使用子查询 SELECT 列表中的列名（或别名）。

-- 找出每个学生超过他自己选修课程平均成绩的课程号 (使用派生表)
-- 步骤 1：计算每个学生的平均成绩，生成派生表 Avg_sc
-- 步骤 2：将 SC 表与派生表 Avg_sc 连接
SELECT SC.Sno, SC.Cno
FROM SC, (SELECT Sno, AVG(Grade) AS avg_grade -- 子查询计算平均分
          FROM SC
          GROUP BY Sno) AS Avg_sc (avg_sno, avg_grade_val) -- 派生表及其列别名
WHERE SC.Sno = Avg_sc.avg_sno -- 连接条件
  AND SC.Grade >= Avg_sc.avg_grade_val; -- 比较条件

-- 查询所有选修了 1 号课程的学生姓名（使用派生表）
SELECT Sname
FROM Student, (SELECT Sno FROM SC WHERE Cno = '1') AS SC1 -- 派生表 SC1 只包含选修了 1 号课的学生 Sno
WHERE Student.Sno = SC1.Sno;

派生表使得查询结构更清晰，有时也能提供比相关子查询更好的性能（取决于 DBMS 优化）。

模式匹配的多样性……又让我见到一种全新的…… ↩︎
也有一种说法是用 SELECT 1，参考 What does "select 1 from" do? ↩︎

基础 SQL 数据查询

SELECT 语句基本结构与执行顺序

基本结构

逻辑执行顺序

SELECT 子句详解

FROM 子句详解

WHERE 子句详解

ORDER BY 子句详解

聚合函数

GROUP BY 子句与 HAVING 子句

进阶 SQL 数据查询

连接查询

等值连接/自然连接

自连接

外连接

多表连接

嵌套查询

集合查询（Set Query）

基于派生表的查询（Derived Table）

`SELECT` 语句基本结构与执行顺序

`SELECT` 子句详解

`FROM` 子句详解

`WHERE` 子句详解

`ORDER BY` 子句详解

`GROUP BY` 子句与 `HAVING` 子句