跳转至

SQL 与关系代数

1. SQL 聚合与执行顺序

统计每个客户在指定月份的交易总额，并筛选总额超过 10,000 元的客户：

1
2
3
4
5
6
SELECT customer_id, SUM(amount) AS total_amount
FROM transactions
WHERE create_time >= '2026-10-01'
  AND create_time < '2026-11-01'
GROUP BY customer_id
HAVING SUM(amount) > 10000;

逻辑执行顺序可理解为：WHERE 先筛行，GROUP BY 分组，聚合函数计算，HAVING 再筛组。WHERE 不能直接筛聚合结果，聚合条件应放在 HAVING。

COUNT(*) 统计行数，COUNT(phone) 不统计 phone 为 NULL 的行。INNER JOIN 只保留匹配记录；LEFT JOIN 保留左表全部记录，右表无匹配时字段为 NULL。

2. 连接与关系代数

关系代数中，SELECT 对应投影 π，WHERE 对应选择 σ，JOIN 对应连接，CROSS JOIN 对应笛卡尔积。连接是从两个关系的笛卡尔积中选取满足条件的元组。

  • θ 连接：满足任意比较条件的连接。
  • 等值连接：连接条件为 =，连接列不要求同名，结果保留两表全部列，因此连接列可能出现两次。
  • 自然连接：在所有同名列上自动做等值连接，并去掉重复连接列。
维度 等值连接 自然连接
条件 显式指定任意列相等 所有同名列自动相等
列名 不要求同名 要求同名
结果列 连接列可能出现两次 重复连接列只保留一次
SQL JOIN ... ON c.id = a.id NATURAL JOIN 
1
2
3
4
5
SELECT * FROM customer c
JOIN account a ON c.customer_id = a.customer_id;

SELECT * FROM customer
NATURAL JOIN account;

自然连接属于内连接，无匹配行会被丢弃；需要保留无匹配行时使用外连接。若两表存在多个同名列，自然连接会把它们全部纳入条件，生产 SQL 通常更适合显式 ON。CROSS JOIN 无连接条件，行数等于两表行数相乘，业务上需谨慎。

3. 视图与索引

视图可封装复杂 JOIN 和聚合，也可只暴露必要的列和行，例如柜员视图只展示账号、户名、余额。视图的定义如下：

1
2
3
CREATE VIEW v_teller_account AS
SELECT account_id, customer_name, balance
FROM account;

索引逻辑上是辅助访问对象，物理上会占用磁盘或 SSD，常见结构包括 B+ 树节点、哈希桶和索引页。B+ 树适合精确查找和范围查询；哈希索引适合精确匹配，通常不适合范围查询。联合索引 (customer_id, create_time) 通常可支持按 customer_id 或按两列查询，只按 create_time 查询不一定能充分利用，具体由优化器和实现决定，待核验。

聚簇索引与非聚簇索引

聚簇索引（clustered index）是索引键的顺序与表中数据行的组织顺序相对应的索引。沿聚簇索引找到键值时，叶层通常直接承载数据行，或指向按该顺序组织的数据页；因此按聚簇键查找，以及读取一段连续键值范围内的多行，通常具有较好的局部性。

例子：账户表按 account_id 聚簇组织，查找 account_id=1008 可以沿索引定位对应数据页；查询 account_id BETWEEN 1000 AND 1100 时，相关记录通常在相近的数据页中，范围读取较顺序。

非聚簇索引（nonclustered index）的索引顺序与表数据行的组织顺序分开。索引叶层保存索引键及行定位信息；命中索引后，如果查询还需要其他列，数据库可能再按行定位信息回表读取数据。

对比 聚簇索引 非聚簇索引
数据行组织 与索引键顺序相关联 与该索引键顺序分开
叶层常见内容 数据行本身或数据页 索引键与行定位信息
范围查询 相关记录通常更有局部性 可能需要较多分散回表
数量限制 同一张表通常只能有一种聚簇组织顺序 可以有多个，但会增加存储和维护成本

表的同一份数据行不能同时按多个不同键值顺序排列，所以通常只能有一个聚簇组织顺序；但表可以建立多个非聚簇索引。具体术语和实现随数据库产品而异，例如有的系统把“聚簇”定义为数据存储方式，有的系统以主键索引组织数据；考试中应以题目采用的数据库模型或教材定义为准。聚簇索引也不等于“主键”的同义词：主键是一种完整性约束，聚簇描述数据的组织方式，二者可以由同一列承担，也可能分开。

选择聚簇键时通常考虑：常用于范围查询或排序、值相对稳定、选择性合适且更新代价可接受。频繁修改聚簇键可能导致数据行移动或页分裂；宽聚簇键还可能使非聚簇索引中的行定位信息变大，具体代价依实现而异。

索引不是越多越好：插入和更新需要维护索引，索引字段上做函数处理也可能使索引难以使用：

1
2
3
4
5
6
-- 可能导致索引难以使用
WHERE YEAR(create_time) = 2026

-- 通常更适合范围索引
WHERE create_time >= '2026-01-01'
  AND create_time < '2027-01-01'

索引既是逻辑对象也是物理存储结构，底层存储或索引结构变化时，应用通常仍使用同一 SQL，这体现了物理数据独立性。

DDL：定义数据库对象。 DDL（数据定义语言）创建或修改库、表、视图、索引等结构，常见 CREATE、ALTER、DROP；执行流程为确认对象和约束→定义列类型/主外键/默认值→建索引或视图→验证结构与权限。示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TABLE customer (
  customer_id BIGINT PRIMARY KEY,
  customer_name VARCHAR(100) NOT NULL,
  status VARCHAR(20) NOT NULL CHECK (status IN ('ACTIVE', 'CLOSED', 'ARCHIVED'))
);

CREATE TABLE account (
  account_id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customer(customer_id),
  balance DECIMAL(14,2) NOT NULL CHECK (balance >= 0)
);
ALTER TABLE account ADD COLUMN opened_at TIMESTAMP;
CREATE INDEX ix_account_customer ON account(customer_id);

场景：从业务实体建客户和账户表，用外键和检查约束把常见错误挡在数据库层。易错边界：DDL 不等同于插入业务记录；不同 DBMS 对 ALTER、事务性 DDL、类型和约束细节不同；删除表或索引前确认依赖和备份，DROP 通常影响对象本身。

DML：操作数据。 DML（数据操作语言）包括 INSERT、UPDATE、DELETE，部分教材把查询 SELECT 单列为 DQL，也有广义口径纳入 DML。机制是根据条件定位行→检查约束/触发器→执行变更→由事务提交或回滚。示例：

1
2
3
4
5
INSERT INTO customer(customer_id, customer_name, status)
VALUES (1001, '李明', 'ACTIVE');
UPDATE account SET balance = balance - 100
WHERE account_id = 501 AND balance >= 100;
DELETE FROM customer WHERE customer_id = 1001;

场景：更新账户前将业务条件纳入 WHERE，并检查受影响行数。易错边界：UPDATE/DELETE 漏掉 WHERE 会作用于全部行；SQL 标准的三值逻辑下条件为 UNKNOWN 的行也不会被 WHERE 选中；约束错误可能使语句失败，不应仅凭应用端提示假定已落库。

子查询。 子查询是嵌套在另一查询中的查询；可返回单值、单列多值或表。常见步骤：先确定内层查询返回形状→选择比较运算符→考虑空结果和 NULL→检查是否应改成连接。标量子查询可用于阈值比较，IN 比较集合，EXISTS 判断是否至少有匹配行，相关子查询引用外层行并按逻辑逐行判断。场景：找出有交易的客户：

1
2
3
4
5
6
7
SELECT c.customer_id
FROM customer c
WHERE EXISTS (
  SELECT 1 FROM transactions t
  JOIN account a ON a.account_id = t.account_id
  WHERE a.customer_id = c.customer_id
);

易错边界：标量子查询若返回多行通常报错；NOT IN (子查询) 的集合若包含 NULL，比较可能变 UNKNOWN 而筛掉意外结果，常改用 NOT EXISTS；EXISTS 只看是否有行，不依赖 SELECT 1 中的列值；相关子查询表达逻辑相关，不保证物理上一定逐行执行。

集合运算。 UNION 合并并去重，UNION ALL 合并但保留重复，INTERSECT 取交集，EXCEPT（部分系统称 MINUS）取差集。机制：两侧查询列数相同、对应列类型兼容→按位置对齐→按运算符决定重复行处理。场景：合并两期客户名单或比较两个分支数据。易错边界：不是按列名配对；默认 UNION 去重会有额外代价；集合操作结果通常需最外层 ORDER BY 排序；运算符支持与优先级有 DBMS 差异。

1
2
3
SELECT customer_id FROM account_2025
UNION
SELECT customer_id FROM account_2026;

授权与回收。 GRANT 将对象权限（如 SELECT、INSERT、UPDATE、DELETE）授给用户或角色，REVOKE 撤销已授权限。步骤：建立角色→按岗位分组→仅授予完成任务所需权限→检查继承/直接授权→离岗或岗位变化时回收。场景：柜员角色只读账户视图，审核角色可更新审批状态。示例（语法需按数据库调整）：

1
2
GRANT SELECT ON v_teller_account TO teller_role;
REVOKE UPDATE ON account FROM teller_role;

易错边界：GRANT 不校验业务数据正确性；授予视图权限不一定消除通过其他角色获得的基表权限；WITH GRANT OPTION 允许被授权者继续授权，需谨慎；所有者、超级用户、角色继承及公共角色行为因 DBMS 而异，不能只看一条 REVOKE 就断言权限已清零。

NULL 边界与三值逻辑。 NULL 表示缺失、未知或不适用，非 0、空串或普通可比较值。比较结果有 TRUE、FALSE、UNKNOWN；WHERE 只保留 TRUE。判断空值用 IS NULL / IS NOT NULL；聚合函数通常忽略 NULL（COUNT(*) 除外），COUNT(col) 只数非 NULL 值。场景：筛选没有登记电话的客户用 phone IS NULL。易错边界：phone = NULL 不成立，应使用 IS NULL；NULL = NULL 仍为 UNKNOWN；算术含 NULL 通常得 NULL；NOT IN 与 NULL 的组合尤易造成结果为空。

SQL 考点练习（含答案解析）。 下列各题使用通用 SQL 概念，具体语法以题目指定 DBMS 为准。

  1. 需建账户表，账号唯一、客户必填、余额不得为负，至少写出三项约束。答案： account_id ... PRIMARY KEY、customer_id ... NOT NULL（并按业务加外键）、balance ... NOT NULL CHECK(balance >= 0)。解析： 主键表达唯一且非空；外键保证被引用客户存在；CHECK 不应被假定可代替 NOT NULL。
  2. 把状态为 CLOSED 的客户改为 ARCHIVED，只改对应客户。答案： UPDATE customer SET status='ARCHIVED' WHERE status='CLOSED';。解析： WHERE 锁定符合条件的行，执行后检查受影响行数；若没有 WHERE 会改全表。
  3. 找出从未拥有账户的客户。答案： SELECT c.customer_id FROM customer c WHERE NOT EXISTS (SELECT 1 FROM account a WHERE a.customer_id=c.customer_id);。解析： 反关联表达“没有匹配行”；若用 NOT IN，子查询结果含 NULL 会引入 UNKNOWN。
  4. SELECT 1 WHERE 2 NOT IN (1, NULL) 为什么可能不返回行？答案： 因为 2 <> 1 为 TRUE、2 <> NULL 为 UNKNOWN，整体 NOT IN 等价比较不能为 TRUE，WHERE 丢弃该行。解析： SQL 采用三值逻辑；避免 NULL 污染可用 NOT EXISTS 或显式排除 NULL。
  5. 合并两年账号名单，重复账号也只保留一次，应选 UNION 还是 UNION ALL？答案： UNION。解析： UNION 默认去重；UNION ALL 保留重复，适合确需保留多次出现或避免去重成本的场景。
  6. 柜员只需查看公开账户视图，最小权限做法是什么？答案： 向柜员角色授予该视图必要的 SELECT，并检查/撤销其不需要的基表和其他角色权限。解析： 视图提供受限接口，授权决定访问能力；只创建视图或只撤销单个角色的权限不能证明没有间接权限。

完整样例：DDL约束与限定更新（最小输入、成功和失败分支）。 本例独立于前文同名表，以整数分表示金额避免浮点误差。SQL采用通用的主外键、NOT NULL、CHECK和UPDATE语法；SQLite验证时必须先在事务外执行 PRAGMA foreign_keys=ON;，这是SQLite专用开关而非标准SQL。其他DBMS需确认外键已启用和CHECK被执行。建表及最小输入：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
CREATE TABLE demo_customer (
  customer_id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(20) NOT NULL
);
CREATE TABLE demo_account (
  account_id INTEGER NOT NULL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES demo_customer(customer_id),
  balance_cent INTEGER NOT NULL CHECK (balance_cent >= 0)
);
INSERT INTO demo_customer VALUES (1, '甲'), (2, '乙'), (3, '丙');
INSERT INTO demo_account VALUES (11, 1, 1000), (12, 1, 2000), (21, 2, 500);
UPDATE demo_account SET balance_cent = balance_cent - 300
WHERE account_id = 11 AND balance_cent >= 300;
SELECT account_id, customer_id, balance_cent
FROM demo_account ORDER BY account_id;

预期成功更新影响1行；结果依次为 (11,1,700)、(12,1,2000)、(21,2,500)。解析：主键定位唯一账号，外键引用已存在客户，非空与非负分别检查，WHERE只扣余额充足的11号账户，其他行不变。以下每条错误操作均从上述成功结果独立测试；捕获错误后回滚该次测试事务，再进行下一条，不能在处于失败状态的事务中继续提交。

独立失败/未命中操作 预期结果 解析
INSERT INTO demo_account VALUES (11,1,100); 主键冲突，拒绝 账号11已存在
INSERT INTO demo_account VALUES (31,9,100); 外键冲突，拒绝 客户9不存在
INSERT INTO demo_account VALUES (31,1,NULL); 非空冲突，拒绝 CHECK不能代替NOT NULL
UPDATE demo_account SET balance_cent=-1 WHERE account_id=11; CHECK冲突，拒绝 不允许负余额
UPDATE demo_account SET balance_cent=balance_cent-800 WHERE account_id=11 AND balance_cent>=800; 不报约束错，但影响0行 700不足800，业务应判失败

每次独立测试结束后，表仍为700、2000、500分；错误信息文本和失败后事务状态按DBMS/驱动而异。实际转账还须把收款更新、行数检查、流水及提交/回滚置于同一事务，不能把本例单次扣减当作完整转账。

完整样例：连接与子查询（共用最小数据）。 使用上一完整样例成功后的三客户、三账户。甲有11和12号账户，乙有21号账户，丙当前无账户。题目语义限定为“当前快照无账户”，不推断历史上从未拥有；前文旧练习中的“从未拥有”只有在账户历史完整保留且按历史查验时才可成立。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT c.customer_id, c.name, a.account_id
FROM demo_customer c
LEFT JOIN demo_account a ON a.customer_id = c.customer_id
ORDER BY c.customer_id, a.account_id;

SELECT c.customer_id, c.name
FROM demo_customer c
WHERE NOT EXISTS (
  SELECT 1 FROM demo_account a WHERE a.customer_id = c.customer_id
)
ORDER BY c.customer_id;

连接预期结果：(1,'甲',11)、(1,'甲',12)、(2,'乙',21)、(3,'丙',NULL)；子查询预期仅 (3,'丙')。解析：LEFT JOIN按账户逐一匹配，甲产生两行，丙保留一行并补右侧NULL；NOT EXISTS逐客户判断是否没有匹配账户，仅保留丙。换INNER JOIN将丢失丙；在WHERE中增加 a.balance_cent>0 也会过滤掉丙的补空行。EXISTS表达逻辑存在性，不保证DBMS按物理逐行循环实现；这两条SQL不依赖NULL排序差异。

完整样例：NULL、三值逻辑与分组聚合。 独立最小输入中NULL表示未知金额，0表示已知零金额，单位为分：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE demo_payment (customer_id INTEGER NOT NULL, amount_cent INTEGER);
INSERT INTO demo_payment VALUES (1,100), (1,NULL), (1,0), (2,NULL);
SELECT customer_id, COUNT(*) AS rows_n, COUNT(amount_cent) AS known_n,
       SUM(amount_cent) AS total_cent, AVG(amount_cent) AS avg_cent
FROM demo_payment GROUP BY customer_id ORDER BY customer_id;
SELECT customer_id FROM demo_payment WHERE amount_cent = NULL;
SELECT customer_id FROM demo_payment WHERE amount_cent IS NULL
ORDER BY customer_id;
SELECT COUNT(*) AS rows_n, COUNT(amount_cent) AS known_n,
       SUM(amount_cent) AS total_cent
FROM demo_payment WHERE customer_id=9;
分组输出customer_id rows_n known_n total_cent avg_cent
1 3 2 100 50
2 1 0 NULL NULL

其余预期：= NULL查询为空集；IS NULL查询返回客户号1、2各一行；空输入上的无GROUP BY聚合仍返回一行 (0,0,NULL)。解析：COUNT(*)计所有行，COUNT(列)、SUM、AVG忽略NULL但不忽略0；客户1的均值是100/2而非100/3，客户2没有非空金额故SUM/AVG为NULL。NULL比较产生UNKNOWN，WHERE只保留TRUE。若有GROUP BY且过滤后没有行，则连分组也没有，输出为空集。COALESCE(SUM(amount_cent),0)可将输出空值显示为0，但这是业务显示约定，不能把未知金额解释成已知0。AVG返回类型和显示小数位依DBMS而异，本例数值50不依赖格式。