SQL进阶

聚合、连接、视图与嵌入式SQL的查询机制

Posted by CloudingYu on March 17, 2026

一、SQL SELECT 完整语法与聚合函数

1.1 SQL查询的完整语法结构

SQL SELECT 语句的完整语法结构为：

1
2
3
4
5
6

SELECT [ALL|DISTINCT] <列名/表达式>
FROM <表名/视图名>
WHERE <条件表达式>
GROUP BY <分组列>
HAVING <分组条件>
ORDER BY <排序列>

SQL 查询的结果本质上是一个集合，所有的中间状态、最终结果都是集合。因此可以在查询结果上做各种面向集合的操作。

1.2 聚合函数（Aggregate Functions）

对查询结果集合进行统计的聚合函数包括：

函数 功能 注意事项
COUNT(*) 统计元组个数 统计行数
COUNT([DISTINCT] 列名) 统计某列的非空值个数 DISTINCT 去重
SUM(列名) 求某列的总和 必须是数值类型
AVG(列名) 求某列的平均值 必须是数值类型
MAX(列名) 求某列的最大值  
MIN(列名) 求某列的最小值  

例子1：统计男生的人数和平均年龄

1
2
3

SELECT COUNT(*), AVG(age)
FROM S
WHERE sex = 'M';

例子2：统计选修了课程的学生人数

1
2

SELECT COUNT(DISTINCT sno)
FROM SC;

注意：因为一个学生可能选多门课，学号列会有重复值，需要用 DISTINCT 去重后统计。

1.3 GROUP BY 分组与 HAVING 筛选

GROUP BY 的作用是将查询结果集合按照某一列的值分成多个组（子集合）。

HAVING 的作用是对分组后的组加约束条件。注意：

  • WHERE 条件是加在行上的
  • HAVING 条件是加在分组上的

SQL 执行顺序（逻辑上的步骤）：

  1. FROM：从基本表取数据，做笛卡尔积
  2. WHERE：筛选满足条件的元组
  3. GROUP BY：对元组进行分组
  4. HAVING：对分组进行筛选
  5. SELECT：计算输出列
  6. ORDER BY：排序

这个执行过程可以对应到关系代数表达式：先做笛卡尔积，然后做选择，再添加分组算子（将元组集合变成多个集合），最后做投影和排序。

例子3：统计每个年龄段选修课程的人数

1
2
3
4

SELECT age, COUNT(*)
FROM S, SC
WHERE S.sno = SC.sno
GROUP BY age;

例子4：统计男生按年龄分组、人数大于50人的分组，并按人数和年龄排序

1
2
3
4
5
6

SELECT age, COUNT(*)
FROM S
WHERE sex = 'M'
GROUP BY age
HAVING COUNT(*) > 50
ORDER BY 2, 1 DESC;

其中 ORDER BY 2, 1 DESC 表示先按第2列（人数）升序，再按第1列（年龄）降序。

1.4 SQL 与国际标准

SQL 语言遵循 SQL2/SQL3 国际标准，基本语法在不同数据库之间一致。但不同数据库在以下方面可能存在差异（称为方言）：

  • 数据类型
  • 字符串处理函数
  • 日期函数等内置函数

在学习 SQL 或做自然语言转 SQL 时，需要注意不同数据库之间的方言差异。

二、SELECT 子句高级特性

2.1 ALL 与 DISTINCT

在 SELECT 子句中，ALL（默认）表示所有行都输出（含重复），DISTINCT 表示去重后输出。

例子：检索所有男生选修的课程号

1
2
3

SELECT DISTINCT cno
FROM S, SC
WHERE S.sno = SC.sno AND sex = 'M';

使用 DISTINCT 是因为多门课程可能有多个男生选择，会产生重复的课程号。

2.2 SELECT 中的计算表达式

可以在 SELECT 中对列值进行运算。例如计算出生年份：

1
2

SELECT sname, 2005 - age AS birth_year
FROM S;

2.3 列别名（AS）

可以给输出列重新命名，使结果更易理解：

1
2

SELECT sname AS student_name, 2005 - age AS birth_year
FROM S;

2.4 BETWEEN 运算符

SQL 设计了更接近自然语言的运算符，例如 BETWEEN：

1
2
3

SELECT sname
FROM S
WHERE age BETWEEN 18 AND 20;

等价于 age >= 18 AND age <= 20。

SQL 在设计时就希望语言尽可能简单、拟人化，方便人们书写和理解。

2.5 字符串模糊匹配：LIKE

两字符串完全相等比较难，所以 SQL 提供了模糊匹配运算符 LIKE，支持两个通配符：

通配符 含义
%（百分号） 匹配零个或多个任意字符
_（下划线） 匹配恰好一个任意字符

例子：查找所有以 B 开头的学生姓名

1
2
3

SELECT sname
FROM S
WHERE sname LIKE 'B%';

B% 表示以 B 开头，后面跟任意长度的任意字符串。

三、空值（NULL）与集合操作

3.1 空值 NULL

在关系表中，除了主键之外，其他属性可以取空值。例如：

  • 学生表中某学生的年龄暂时未知
  • SC 表中某条选课记录的成绩尚未录入

关键注意事项：判断某个列值是否为空必须使用 IS NULL，而不能用等号：

1
2
3
4
5

-- 正确写法
SELECT * FROM S WHERE age IS NULL;

-- 错误写法（不会报错但结果不对）
SELECT * FROM S WHERE age = NULL;

= NULL 的含义是查找字段值等于字符串 “NULL” 的记录，而 IS NULL 才是判断字段值是否为空。这是 SQL 初学者最容易犯的错误之一。

3.2 集合的并、交、差操作

由于 SQL 查询的结果都是集合，因此集合之间可以做并、交、差操作：

操作 SQL 关键字 含义
并 UNION 两个查询结果的并集
交 INTERSECT 两个查询结果的交集
差 EXCEPT 从第一个结果中去掉第二个结果中出现的

3.3 集合成员判断：IN / NOT IN

SQL 查询的结果都是集合，因此大量使用集合操作。

例子：查找至少不学 C2 和 C4 两门课的学生

1
2
3
4
5

SELECT sno
FROM S
WHERE sno NOT IN (
    SELECT sno FROM SC WHERE cno IN ('C2', 'C4')
);

3.4 集合比较：ANY/SOME 与 ALL

关键词 含义
> ANY (集合) 大于集合中的某一个即可
> ALL (集合) 必须大于集合中的所有元素
= ANY (集合) 等价于 IN
<> ALL (集合) 等价于 NOT IN

例子1：检索至少有一门成绩超过 S4 学生某门成绩的学生

1
2
3
4
5

SELECT DISTINCT sno
FROM SC
WHERE grade > ANY (
    SELECT grade FROM SC WHERE sno = 'S4'
);

例子2：检索平均成绩最高的学生的学号

1
2
3
4
5
6

SELECT sno
FROM SC
GROUP BY sno
HAVING AVG(grade) >= ALL (
    SELECT AVG(grade) FROM SC GROUP BY sno
);

3.5 元组比较

SQL 支持对整个元组（多属性值）做大小比较，比较规则与字符串比较类似：按字典序逐属性比较。先比较第一个属性，如果相等则比较第二个，依此类推。

3.6 空关系测试：EXISTS / NOT EXISTS

  • EXISTS (子查询)：判断子查询结果集合是否非空
  • NOT EXISTS (子查询)：判断子查询结果集合是否为空

3.7 重复值判断：UNIQUE

  • UNIQUE (子查询)：判断子查询结果集合中是否有重复元组

例子：查找只开设了一门课程的教师的姓名

1
2
3
4
5

SELECT teacher
FROM C AS X
WHERE UNIQUE (
    SELECT teacher FROM C WHERE teacher = X.teacher
);

如果教师上了两门课，嵌套查询中该教师的名字会出现两次，UNIQUE 判断就不满足。

四、导出表与连接操作

4.1 导出表（Derived Table）

在 FROM 子句中可以使用子查询临时生成一张表（导出表），供后续查询使用。

例子：查询平均成绩超过 80 分的学生学号和姓名

方法一（GROUP BY + HAVING）：

1
2
3
4
5
6
7

SELECT sno, sname
FROM S
WHERE sno IN (
    SELECT sno FROM SC
    GROUP BY sno
    HAVING AVG(grade) > 80
);

方法二（导出表）：

1
2
3
4
5
6
7

SELECT sno, sname
FROM S, (
    SELECT sno, AVG(grade) AS avg_grade
    FROM SC
    GROUP BY sno
) AS result
WHERE S.sno = result.sno AND result.avg_grade > 80;

某些复杂查询使用导出表能简化逻辑，两种写法效果相同。

4.2 连接操作

前面学习的自然连接：如果两个关系有公共属性，在公共属性上取值相同的元组会被连接在一起。连接条件是写在 WHERE 子句中的。

4.3 外连接（Outer Join）

自然连接只会保留能够匹配的元组，无法匹配的元组会被丢弃。而外连接会保留某一边（或两边）未能匹配的元组，缺失的属性值以 NULL 填充。

类型 含义
LEFT OUTER JOIN 保留左表中所有元组
RIGHT OUTER JOIN 保留右表中所有元组
FULL OUTER JOIN 保留两表中所有元组

例子：关系 R(A,B,C) 和 S(B,C,D) 做左外连接

  • 左表中的 A3,B3,C3 在右表中没有匹配（右表为 B4,C4,D4），但左外连接会保留它，D 列以 NULL 填充

4.4 自然连接 vs 非自然连接

  • NATURAL JOIN：自动以同名的公共属性为连接条件，结果中公共列只出现一次
  • 非自然连接：需要显式指定连接条件（ON），两边的同名列都会保留

USING(列名)：指定我们认为哪些列是公共属性，连接时只在该列上匹配且结果中该列只出现一次。

五、递归查询（Recursive Query）

5.1 问题引入：先导课程查询

假设有一张课程表，记录每门课的先导课程：

课程号 课程名 先导课程号
0 程序设计语言 NULL
1 数据结构 0
2 数据库 1
3 数据挖掘 2

如何查出每门课程的所有先导课程（包括间接的）？

困难：要做几次自连接？不知道——因为事先不知道先导链条有多长。写 SQL 时知道表中有哪些列，但看不到表中具体的数据值，因此不知道需要连接多少次。

这个查询用普通的 JOIN 无法完成，因为连接次数取决于数据本身而非表结构。

5.2 递归查询的 Datalog 思路

递归查询源自关系逻辑（Datalog）中的递归运算，由两条规则描述：

  1. 基础规则：若 course(X, Y)（即课程 X 的先导是 Y），则 W(X, Y)（W 是先导关系）
  2. 递归规则：若 W(X, Y) 且 course(Y, Z)，则 W(X, Z)

执行过程：

  1. 先用基础规则从 course 表中填充 W 的初始值（直接先导关系）
  2. 再用递归规则：将已有的 W 与 course 做连接，产生新的间接先导关系
  3. 重复第 2 步，直到不再产生新的元组

5.3 SQL 中的递归查询语法

1
2
3
4
5
6
7
8
9
10

WITH RECURSIVE W(cno, pre_cno) AS (
    -- 基础规则：直接先导关系
    SELECT cno, pre_cno FROM course
    UNION
    -- 递归规则：间接先导关系
    SELECT W.cno, course.pre_cno
    FROM W, course
    WHERE W.pre_cno = course.cno
)
SELECT * FROM W;

执行过程演示：

  • 第1轮：从 course 表得到直接先导关系
  • 第2轮：W 与 course 连接，得到跨两级的先导关系
  • 第3轮：W 继续与 course 连接，得到跨三级的先导关系
  • 继续迭代直到没有新元组产生

5.4 递归查询的终止性问题

在递归查询的研究中，最关心两个问题：第一是如何高效计算，第二是递归会不会终止。

必须保证递归过程是有限穷尽的——W 表必须有限，不能无限循环。在实际数据（如课程先导关系是无环的）中，递归一定会终止。

六、数据的插入、删除与修改

6.1 插入数据（INSERT）

三种插入方式：

方式1：插入单条或多条完整元组

1
2

INSERT INTO SC VALUES ('S6', 'C6', 90);
INSERT INTO SC VALUES ('S5', 'C8', NULL), ('S6', 'C9', 85);

若插入完整元组（所有列都有值），可省略列名。

方式2：插入部分列

1

INSERT INTO SC(sno, cno) VALUES ('S5', 'C8');

必须指明插入了哪些列的值，未指定的列取默认值或 NULL。

方式3：将查询结果插入表

1
2
3
4
5
6

INSERT INTO s_grade(sno, avg_grade)
SELECT sno, AVG(grade)
FROM SC
WHERE sno IN (SELECT sno FROM S WHERE sex = 'M')
GROUP BY sno
HAVING AVG(grade) > 80;

这常用于数据迁移场景：将老系统数据一次性导入新系统的表中。

方式4：将整张表插入

1

INSERT INTO SC SELECT * FROM SC4;

6.2 删除数据（DELETE）

1
2

DELETE FROM <表名>
WHERE <条件表达式>;

例子1：删除所有课程名为 “Math” 的成绩记录

1
2
3
4

DELETE FROM SC
WHERE cno IN (
    SELECT cno FROM C WHERE cname = 'Math'
);

例子2：删除 C4 课程中小于平均成绩的记录

1
2
3
4

DELETE FROM SC
WHERE cno = 'C4' AND grade < (
    SELECT AVG(grade) FROM SC WHERE cno = 'C4'
);

6.3 修改数据（UPDATE）

1
2
3

UPDATE <表名>
SET <列名> = <表达式>
WHERE <条件表达式>;

方式1：修改特定列

1
2
3
4
5
6
7
8
9
10

-- 将 C5 课程的任课教师改为吴教师
UPDATE C SET teacher = '吴教师' WHERE cno = 'C5';

-- 将所有女学生的成绩加 10%
UPDATE SC SET grade = grade * 1.1
WHERE sno IN (SELECT sno FROM S WHERE sex = 'F');

-- 将 C4 课程中低于平均分的成绩加 5%
UPDATE SC SET grade = grade * 1.05
WHERE cno = 'C4' AND grade < (SELECT AVG(grade) FROM SC WHERE cno = 'C4');

方式2：用 ROW 替换整个元组

1
2

UPDATE C SET ROW = ('C5', '数据库', '刘教师')
WHERE cno = 'C5';

七、视图（View）

7.1 什么是视图

在数据库三层结构（外模式-概念模式-内模式）中，视图是最外层的结构，是针对每个用户访问数据库的需求而定制的个性化数据结构。

视图可以为教务人员提供包含学生姓名、课程名的友好界面，避免直接记忆和使用底层学号、课程号。

7.2 创建视图

1
2
3
4

CREATE VIEW student_grade(sno, sname, cname, grade) AS
SELECT S.sno, S.sname, C.cname, SC.grade
FROM S, SC, C
WHERE S.sno = SC.sno AND SC.cno = C.cno;

这个视图让教务人员可以直接按学生姓名或课程名查询成绩，而无需知道学号和课程号。

7.3 视图的工作原理

数据库中存在数据字典（Data Dictionary），其中包含视图的数据字典，记录每个视图的定义（即其对应的 SELECT 语句）。

关键特点：视图不存储任何实际数据。创建视图只是记录了定义，查询视图时数据库会：

  1. 从数据字典中取出视图的定义（SELECT 语句）
  2. 执行该查询生成一张临时表
  3. 在临时表上执行用户的查询操作

因此，创建任意数量的视图不会浪费存储空间。

7.4 视图的更新问题

视图可以像表一样被查询，但并非所有视图都可以进行 INSERT/UPDATE/DELETE 操作。

不能更新的视图：

  1. 多表连接视图：如 student_grade 视图涉及三张表，插入时不知道该往哪张表插入，也缺少关键信息（如课程号）
  2. 含聚合/分组的视图：如统计每个学生选课数和平均成绩的视图，插入时不知道具体每门课的成绩

可以更新的视图：行列子集视图（Row-Column Subset View），需满足三个条件：

  • 基于单张基本表
  • 只使用了选择和投影操作
  • 包含该表的主键

例子：所有男生的学号、姓名和年龄视图

1
2

CREATE VIEW male_student AS
SELECT sno, sname, age FROM S WHERE sex = 'M';

这个视图可更新——向该视图插入数据时，系统会自动补全性别为 ‘M’。

7.5 视图的三大优点

  1. 数据独立性：下层关系表变化时，只需修改对应的 SELECT 语句，视图的名字和列不变，用户感知不到变化
  2. 简化用户视角：为用户定制友好、人性化的数据结构
  3. 安全保护：通过视图限制用户能看到的数据范围。例如财务数据只对领导和财务人员可见；可以建一个不包含敏感列的视图，授权给普通用户

我们可以直接给用户授权（授权到表），但更好的方式是通过视图来精确控制用户能看到哪些列。

7.6 删除视图

1

DROP VIEW <视图名>;

八、嵌入式SQL与游标

8.1 编程语言如何访问数据库

数据库建好后，需要在程序中访问它才有实际价值。嵌入式SQL（Embedded SQL）解决的就是编程语言与数据库的交互问题。

传统做法（早期）：

  1. 在 C/Python 等语言中直接嵌入 SQL 语句
  2. 通过预处理器将嵌入 SQL 的程序拆分为两部分：纯 C 语言代码 + SQL 调用（变成函数调用）
  3. C 编译器编译纯 C 代码，连接时加上数据库函数库即可

8.2 核心问题：查询结果集合的处理

编程语言通常只能处理单个变量值，而 SQL 查询可能返回多条结果。关键问题：

“应用程序把 SQL 语句发到数据库服务器后，服务器执行完了，查询结果（可能有很多条）怎么返回来？”

  • 不知道有多大（该开多大空间接收）
  • 不知道格式是什么样的
  • 不同查询返回的结果结构不同

8.3 游标（Cursor）机制

游标是解决多行查询结果传递的核心机制。

工作流程：

  1. 服务器端执行查询后，将结果放在一个临时表中
  2. 给客户端返回一个指针（游标），指向临时表的第一条元组
  3. 客户端通过游标逐条获取数据（FETCH），指针自动下移
  4. 遍历完所有结果后，关闭游标释放服务器端空间

游标的生命周期：

  • DECLARE：定义游标，关联一条 SELECT 语句
  • OPEN：执行查询，生成临时表，指针指向第一条
  • FETCH：取回当前指针指向的元组数据，指针自动下移
  • CLOSE：释放服务器端的临时表空间

8.4 不涉及游标的操作

以下 SQL 操作不涉及游标，直接执行即可：

  • INSERT、DELETE、UPDATE
  • 已知只返回单行结果的 SELECT（如按主键查询）

对于单行结果，可使用 INTO 直接将结果送到宿主变量：

1
2
3

SELECT sname, age, sex INTO :name, :age, :sex
FROM S
WHERE sno = :sno;

冒号前缀 : 表示宿主变量（Host Variable），是定义在编程语言中的变量，用于程序与 SQL 之间传递数据。

8.5 游标与数据库并发连接

每个数据库连接在服务器端都需要一定的资源（空间）来处理查询结果和游标。

阿里当年抛弃 Oracle 开始自研数据库，最主要的原因就是阿里的并发用户量太大（淘宝海量用户同时在线）。如果要给 Oracle 支付那么高并发用户量的许可费，成本太高了。连接并发数是数据库系统的一个很重要的指标。

8.6 通过游标修改数据

游标指向的元组不仅可读，还可以修改（CURRENT OF 游标名）：

1
2
3
4
5
6

// 遍历成绩，不及格的删除，60-69分的改成70分
if (grade < 60) {
    DELETE FROM SC WHERE CURRENT OF cursor_name;
} else if (grade >= 60 && grade <= 69) {
    UPDATE SC SET grade = 70 WHERE CURRENT OF cursor_name;
}

8.7 游标的两种状态

类型 特点
普通游标 只能向前移动（FETCH 每次取一条并自动下移）
滚动游标（Scroll Cursor） 可向任意方向移动

滚动游标的移动命令：

命令 功能
NEXT 向前推进一行（同 FETCH）
PRIOR 向上回退一行
FIRST 跳到第一行
LAST 跳到最后一行
RELATIVE n 相对当前位置移动 n 行
ABSOLUTE n 跳到绝对第 n 行

8.8 SQLSTATE 状态监测

每条 SQL 执行完后，服务器都会返回一个执行状态。通过 SQLSTATE（在 C 语言中是结构体，其他语言中形态不同）可以判断操作是否成功、游标是否取完了所有数据。

编程访问数据库时，每一条 SQL 执行后都应该检查返回状态，确认是否成功。

九、动态SQL

9.1 什么是动态SQL

普通嵌入式SQL中，SQL 语句在写程序时就已确定。而动态SQL处理的是：写程序时还不知道 SQL 语句长什么样（如命令窗口中的交互式查询），需要在运行时临时构造 SQL 语句。

9.2 查询处理的两步流程

一条 SQL 语句在数据库中执行需要经过两步：

  1. 查询处理与优化（Prepare）：SQL -> 关系代数表达式 -> 查询优化 -> 生成可执行代码（访问路径/Access Path）
  2. 执行（Execute）：运行生成的可执行代码

9.3 PREPARE 与 EXECUTE

对应两条命令：

命令 功能
PREPARE 做查询处理和查询优化，生成可执行代码
EXECUTE 执行已经准备好的代码

这两步可以合并（直接 EXECUTE），也可以分开。

9.4 为什么要分开？

如果一条 SQL 语句会被执行多次，先 PREPARE 后多次 EXECUTE 更高效——因为查询处理和优化只需要做一次。

查询优化本身也需要时间，且优化时间也算在总运行时间里。如果只执行一次，PREPARE 和 EXECUTE 合并即可。如果执行多次，分开可以节省重复优化的开销。

这里有一个悖论——你把优化时间花得越多，越可能找到最优执行方案，但优化时间也算在响应时间里。所以只能花适当的时间找到一个相对较好的方案。

十、ODBC 接口与数据库架构演进

10.1 ODBC（Open Database Connectivity）

ODBC 是几乎所有数据库系统都支持的公共访问接口，是国际标准，类似于 SQL 语言的地位。通过 ODBC 可以用同样的方式访问所有数据库。

数据库之所以用得那么好，就是因为它的主要接口全是公认的国际标准。我从 DB2 转到 Oracle，或从 Sybase 转到 DB2，基本不会有什么问题，因为接口都一样。

10.2 ODBC 的句柄（Handle）结构

ODBC 定义了三种层次的句柄（可理解为指针）：

句柄 作用域 含义
环境句柄（Environment Handle） 整个应用程序 标识当前应用程序
连接句柄（Connection Handle） 一个数据库连接 标识与某个数据库的连接
语句句柄（Statement Handle） 一条 SQL 语句 标识某条 SQL 语句和其游标

在一个程序里可以连接多个数据库，在一个连接里可以同时执行多条查询，每条语句有各自的句柄。

10.3 数据库架构的历史演进

  1. 最早（FoxPro 时代）：单个数据库文件，通过统一接口访问
  2. Client/Server 架构：客户端 + 数据库服务器，配置连接即可
  3. Web Server 三层架构：客户端 <-> Web 服务器 <-> 数据库服务器，Web 服务器增加了对数据库调用的集中管理和资源调度能力
  4. 中间件（Middleware）：在 Web 服务器基础上发展为中间件，对数据库资源的调度能力更强。如今大多数应用都通过中间件来调用数据库

10.4 ODBC 基本程序流程

  1. 分配环境句柄、连接句柄、语句句柄
  2. 连接数据库（指定数据库、用户名、密码）
  3. 执行 SQL（PREPARE + EXECUTE 或直接 EXECUTE）
  4. 对结果进行处理（FETCH 遍历、检查返回状态 SQLSTATE/SQLCA）
  5. 释放所有句柄、断开连接
CATALOG
    FEATURED TAGS
    题解 洛谷 软件设计 面向对象 行为型模式 设计模式 算法
    FRIENDS