关系数据库标准语言 SQL

发表于 2025-03-20 更新于 2025-03-27 Waline：阅读次数：本文字数： 6.5k 阅读时长 ≈ 24 分钟

SQL 概述

SQL (Structured Query Language，结构化查询语言) 是关系数据库的标准语言，具有以下特点：

综合统一：集数据定义语言（DDL）、数据操纵语言（DML）、数据控制语言（DCL）于一体。
高度非过程化：只需指定「做什么」，无需说明「怎么做」。
面向集合：操作对象和结果都是元组集合。
两种使用方式：既可作为独立语言交互使用，也可嵌入到宿主语言（如 C, Java）中。
简洁易学：核心功能仅需少量动词。

SQL 与关系模型概念的对应关系：

关系模型	SQL
关系	基本表
子模式	视图
属性	列（column）
元组	行（row）

基本表和视图统称为表（table），视图也被称为虚表（virtual table）。

SQL 基本语言成分

符号：包括 26 个英文字母、阿拉伯数字、括号、四则运算符等。
保留字：具有特定含义的单词或缩写，如 CREATE, SELECT, TABLE, UNIQUE 等。
标识符：用于命名数据库对象，如表、视图、属性、存储过程等。
常量：包括数值常量、字符串常量、日期/时间常量。

SQL 基本表示规范（交互式 SQL）

每条 SQL 语句通常以动词开头，以分号 ; 结尾。
在批处理模式下，分号既是语句结束符，也是语句分隔符。
除常量外，SQL 语句不区分大小写（字母）。
数值常量无需定界符，字符串和日期/时间常量需使用单引号 ' 作为定界符。

SQL 与关系数据库三级模式

graph TB
    %% 定义 SQL 作为顶层节点
    I[SQL\n（应用层）]:::sql-node

    %% 外模式
    subgraph 外模式[外模式（用户层）]
        direction LR
        A[视图 1]:::view-node
        B[视图 2]:::view-node
    end

    %% 模式
    subgraph 模式 [模式（逻辑层）]
        direction LR
        C[基本表 1]:::table-node
        D[基本表 2]:::table-node
        E[基本表 3]:::table-node
        F[基本表 4]:::table-node
    end

    %% 内模式
    subgraph 内模式 [内模式（物理层）]
        direction LR
        G[存储文件 1]:::storage-node
        H[存储文件 2]:::storage-node
    end

    %% 外模式到模式的关系
    A <--> D
    B <--> E
    B <--> F

    %% 模式到内模式的关系
    C <--> G
    D <--> G
    E <--> G
    F <--> H

    %% SQL 到各层次的关系
    I <--> A
    I <--> B
    I <--> C

    %% 美化样式定义
    classDef sql-node fill:#4CAF50,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;
    classDef view-node fill:#FF9800,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;
    classDef table-node fill:#2196F3,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;
    classDef storage-node fill:#9C27B0,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;

    %% 子图样式定义
    classDef subgraphTitle fill:#333,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold,text-align:center;

基本表（Base Table）：实际存储数据的表，一个关系对应一个基本表。
存储文件：组织数据库内模式的逻辑结构，对用户透明。
视图（View）：从基本表导出的虚表，数据库中只存储视图的定义。

学生-课程数据库

本篇将使用一个「学生-课程」数据库作为示例，包含以下三个基本关系：

学生关系（Student）：记录学生信息
| 学号（Sno） | 姓名（Sname） | 性别（Ssex） | 年龄（Sage） | 所在系（Sdept） |
| :-- | :-- | :-- | :-- | :-- |
| 201215121 | 李勇 | 男 | 20 | CS |
| 201215122 | 刘晨 | 女 | 19 | CS |
| 201215123 | 王敏 | 女 | 18 | MA |
| 201215125 | 张立 | 男 | 19 | IS |
课程关系（Course）：记录课程信息
| 课程号（Cno） | 课程名（Cname） | 先行课（Cpno） | 学分（Ccredit） |
| :-- | :-- | :-- | :-- |
| 1 | 数据库 | 5 | 4 |
| 2 | 数学 | NULL | 2 |
| 3 | 信息系统 | 1 | 4 |
| 4 | 操作系统 | 6 | 3 |
| 5 | 数据结构 | 7 | 4 |
| 6 | 数据处理 | NULL | 2 |
| 7 | PASCAL 语言 | 6 | 4 |
选修关系（SC）：记录学生选课信息
| 学号（Sno） | 课程号（Cno） | 成绩（Grade） |
| :-- | :-- | :-- |
| 201215121 | 1 | 92 |
| 201215121 | 2 | 85 |
| 201215121 | 3 | 88 |
| 201215122 | 2 | 90 |
| 201215122 | 3 | 80 |

SQL 数据定义

SQL 数据定义功能包括：

模式定义：创建、删除模式（CREATE SCHEMA, DROP SCHEMA）
表定义：创建、删除、修改基本表（CREATE TABLE, DROP TABLE, ALTER TABLE）
视图和索引定义：创建、删除视图和索引（CREATE VIEW, DROP VIEW, CREATE INDEX, DROP INDEX, ALTER INDEX）

数据库对象命名机制

一个关系数据库管理系统实例可包含多个数据库。
一个数据库可包含多个模式。
一个模式通常包含多个表、视图、索引等数据库对象。
- 表：存储数据的网格结构（类似 Excel 表格）
- 视图：保存查询结果的虚拟表
- 索引：加速数据检索的目录结构

1
2
3

数据库（有的系统称为目录）
└── 模式
    └── 表、视图、索引等

MySQL 中数据库与模式基本是等同的。

模式定义与删除

定义模式：实际上是定义了一个命名空间，可在其中定义数据库对象。
1
2
CREATE SCHEMA [<模式名>] AUTHORIZATION <用户名>
[<表定义子句> | <视图定义子句> | <授权定义子句>];
- 若未指定 <模式名>，则 <模式名> 隐含为 <用户名>。
- 可在 CREATE SCHEMA 中直接创建表、视图或授权。
删除模式：
DROP SCHEMA <模式名> <CASCADE | RESTRICT>;
- CASCADE（级联）：删除模式的同时删除所有下属对象。
- RESTRICT（限制）：若模式中存在下属对象，则拒绝删除。

基本表定义、删除与修改

定义基本表

CREATE TABLE <表名> (
    <列名> <数据类型> [<列级完整性约束条件>]
    [, <列名> <数据类型> [<列级完整性约束条件>]]
    ...
    [, <表级完整性约束条件>]
);

<表名>：要定义的基本表名称。
<列名>：组成表的各个属性（列）。
<列级完整性约束条件>：涉及相应属性列的约束。
<表级完整性约束条件>：涉及一个或多个属性列的约束。

示例：

-- 学生表 Student
CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY, -- 学号，主码
    Sname CHAR(20) UNIQUE,   -- 姓名，唯一（吗）
    Ssex CHAR(2),            -- 性别
    Sage SMALLINT,           -- 年龄
    Sdept CHAR(20)           -- 所在系
);

-- 课程表 Course
CREATE TABLE Course (
    Cno CHAR(4) PRIMARY KEY,                  -- 课程号，主码
    Cname CHAR(40) NOT NULL,                  -- 课程名，非空
    Cpno CHAR(4),                             -- 先行课
    Ccredit SMALLINT,                         -- 学分
    FOREIGN KEY (Cpno) REFERENCES Course(Cno) -- 外码，参照 Course 表的 Cno
);

-- 选课表 SC
CREATE TABLE SC (
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY (Sno, Cno),                    -- 主码由 Sno 和 Cno 组成
    FOREIGN KEY (Sno) REFERENCES Student(Sno), -- 外码，参照 Student 表的 Sno
    FOREIGN KEY (Cno) REFERENCES Course(Cno)   -- 外码，参照 Course 表的 Cno
);

SQL 数据类型

SQL 使用数据类型来实现域的概念，常用数据类型包括：

数据类型	含义
`CHAR(n)`, `CHARACTER(n)`	长度为 `n` 的定长字符串
`VARCHAR(n)`, `CHARACTER VARYING(n)`	最大长度为 `n` 的变长字符串
`CLOB`	字符串大对象（Character Large OBject）
`BLOB`	二进制大对象（Binary Large OBject）
`INT`, `INTEGER`	长整数（4 字节）
`SMALLINT`	短整数（2 字节）
`BIGINT`	大整数（8 字节）
`NUMERIC(p, d)`	定点数，`p` 位数字（不包括符号、小数点），小数后 `d` 位
`DECIMAL(p, d)`, `DEC(p, d)`	同 `NUMERIC`
`REAL`	取决于机器精度的单精度浮点数
`DOUBLE PRECISION`	取决于机器精度的双精度浮点数
`FLOAT(n)`	可选精度的浮点数，精度至少为 `n` 位数字
`BOOLEAN`	逻辑布尔量
`DATE`	日期，包含年、月、日，格式为 `YYYY-MM-DD`
`TIME`	时间，包含一日的时、分、秒，格式为 `HH:MM:SS`
`TIMESTAMP`	时间戳类型
`INTERVAL`	时间间隔类型

修改基本表

ALTER TABLE <表名>
[ADD [COLUMN] <新列名> <数据类型> [<完整性约束>]]
[ADD <表级完整性约束>]
[DROP [COLUMN] <列名> [CASCADE | RESTRICT]]
[DROP CONSTRAINT <完整性约束名> [RESTRICT | CASCADE]]
[ALTER COLUMN <列名> <数据类型>];

ADD 子句：用于增加新列、新的列级或表级完整性约束。
DROP COLUMN 子句：用于删除表中的列。
- CASCADE：自动删除引用该列的其他对象。
- RESTRICT：若该列被其他对象引用，则拒绝删除。
DROP CONSTRAINT 子句：用于删除指定的完整性约束。
ALTER COLUMN 子句：用于修改原有的列定义。

示例：

-- 向 Student 表增加「入学时间」列，数据类型为日期型
ALTER TABLE Student ADD S_entrance DATE;

-- 将年龄的数据类型由字符型改为整数
ALTER TABLE Student ALTER COLUMN Sage INT;

-- 增加课程名称必须取唯一值的约束条件
ALTER TABLE Course ADD UNIQUE (Cname);

删除基本表

DROP TABLE <表名> [RESTRICT | CASCADE];

RESTRICT：删除表是有限制的，若表被其他对象引用，则不能删除。
CASCADE：删除表没有限制，同时删除相关的依赖对象（如视图、索引等）。

索引

目的：加快查询速度。
建立：由数据库管理员或表的拥有者建立。
维护：由关系数据库管理系统自动完成。
使用：关系数据库管理系统自动选择合适的索引作为存取路径。

常见索引类型：

顺序文件上的索引
B+ 树索引
散列索引
位图索引

建立索引

1 2	CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名> [<次序>][, <列名> [<次序>]]...);

<表名>：要建索引的基本表名称。
<列名>：索引可以建立在一列或多列上。
<次序>：指定索引值排列次序，升序（ASC，默认）或降序（DESC）。
UNIQUE：索引的每一个值只对应唯一的数据记录。
CLUSTER：表示要建立的索引是聚簇索引。

示例：

-- Student 表按学号升序建唯一索引
CREATE UNIQUE INDEX Stusno ON Student (Sno);

-- Course 表按课程号升序建唯一索引
CREATE UNIQUE INDEX Coucno ON Course (Cno);

-- SC 表按学号升序和课程号降序建唯一索引
CREATE UNIQUE INDEX SCno ON SC (Sno ASC, Cno DESC);

修改/删除索引

-- 修改索引名
ALTER INDEX <旧索引名> RENAME TO <新索引名>;

-- 删除索引
DROP INDEX <索引名>;

数据字典

数据字典是关系数据库管理系统内部的一组系统表，记录了数据库中所有定义信息，包括：

关系模式定义
视图定义
索引定义
完整性约束定义
各类用户对数据库的操作权限
统计信息等

关系数据库管理系统在执行 SQL 数据定义语句时，实际上就是在更新数据字典表中的相应信息。

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 关键字指定一个转义字符。

-- 查询所有姓 '刘' 的学生
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'）职位的员工的平均工资（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 的同义词。

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

-- 查询非 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';

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

EXISTS：如果子查询返回至少一行，则 EXISTS 返回 TRUE；否则返回 FALSE。
NOT EXISTS：如果子查询返回空集，则 NOT EXISTS 返回 TRUE；否则返回 FALSE。
子查询的 SELECT 列表通常用 *，因为不关心返回的具体值，只关心是否有行返回。
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 的威力：实现全称量词 (∀)

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 优化）。

模式匹配的多样性……又让我见到一种全新的…… ↩︎

SQL 概述

SQL 基本语言成分

SQL 基本表示规范（交互式 SQL）

SQL 与关系数据库三级模式

学生-课程数据库

SQL 数据定义

数据库对象命名机制

模式定义与删除

基本表定义、删除与修改

定义基本表

SQL 数据类型

修改基本表

删除基本表

索引

建立索引

修改/删除索引

数据字典

SQL 数据查询

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

基本结构

逻辑执行顺序

SELECT 子句详解

FROM 子句详解

WHERE 子句详解

ORDER BY 子句详解

聚合函数

GROUP BY 子句与 HAVING 子句

SQL 数据查询（进阶）

连接查询

等值连接/自然连接

自连接

外连接

多表连接

嵌套查询

集合查询 (Set Query)

基于派生表的查询 (Derived Table)

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

`SELECT` 子句详解

`FROM` 子句详解

`WHERE` 子句详解

`ORDER BY` 子句详解

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