Appearance
SQL 基础
概念
上一章说"关系是元组的集合"，那是数学。要把这套数学交给机器去算，需要一门语言——SQL（Structured Query Language，结构化查询语言）。
SQL 最特别的地方在两个字：声明式。你写的是"我要什么"，不是"怎么取"。
sql
SELECT sname FROM S WHERE city = '北京';这句话没有告诉数据库"先读哪一页、再比较哪个字节、用什么算法"。怎么取是数据库自己的事——它可以选择全表扫描，也可以选择走索引，可以单线程，也可以并行。你只负责把要求说清楚。
这条分工是整章的地基：SQL 是描述结果的，不是描述过程的。理解了这一条，就能解释后面几乎所有"为什么 SQL 这么规定"。
原理
一、SQL 的四类子语言
SQL 不是一整块，按用途分四类：
| 类别 | 全称 | 管什么 | 代表语句 |
|---|---|---|---|
| DDL | 数据定义语言 | 结构：建表、改表、删表 | CREATE / ALTER / DROP |
| DML | 数据操纵语言 | 数据：增、删、改 | INSERT / UPDATE / DELETE |
| DQL | 数据查询语言 | 查询 | SELECT |
| DCL | 数据控制语言 | 权限与事务 | GRANT / REVOKE / COMMIT / ROLLBACK |
有的教材把 COMMIT/ROLLBACK 单独划成 TCL（事务控制语言），把 SELECT 并进 DML 变三类。分类法不重要，重要的是能一眼看出某条语句属于哪类——DDL 一动就是结构变更，DML 一动就是数据变更，这两件事的风险完全不同。
二、书写顺序 ≠ 执行顺序（本章最重要的一条）
我们敲键盘的顺序是这样的：
SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT但数据库实际执行的顺序是另一个样子：
| 步骤 | 执行的动作 | 说明 |
|---|---|---|
| 1 | FROM | 先把表（以及 JOIN 出来的中间结果）准备好 |
| 2 | ON | 连接条件在这一步筛 |
| 3 | WHERE | 逐行筛 |
| 4 | GROUP BY | 分组 |
| 5 | HAVING | 逐组筛 |
| 6 | SELECT | 这时候才算表达式、才起列别名 |
| 7 | DISTINCT | 去重 |
| 8 | ORDER BY | 排序 |
| 9 | LIMIT | 截断 |
这张表能直接回答三个高频疑问：
- 为什么
WHERE里不能用SELECT里起的别名？ 因为WHERE（第 3 步）跑在SELECT（第 6 步）之前，那时别名还不存在。 - 为什么
ORDER BY里可以用别名？ 因为它排在第 8 步，前面SELECT已经算完了。 - 为什么聚合函数不能写在
WHERE里？ 因为WHERE在分组之前跑，那时候"一组"这个概念还不存在——所以需要HAVING这个专门在分组之后筛的条件。
三、五种连接
| 连接 | 关键字 | 结果 |
|---|---|---|
| 内连接 | INNER JOIN（可省 INNER） | 只保留两边都配得上的行 |
| 左外连接 | LEFT JOIN | 保留左表全部行，右表配不上的填 NULL |
| 右外连接 | RIGHT JOIN | 保留右表全部行 |
| 全外连接 | FULL JOIN | 两边都保留 |
| 交叉连接 | CROSS JOIN | 笛卡尔积，行数相乘 |
INNER JOIN ... ON 与上一章的关系代数一一对应：ON 是连接条件，WHERE 是连接之后再筛行。写外连接时把筛选条件放错位置是经典陷阱：
| 写法 | 效果 |
|---|---|
LEFT JOIN SC ON ... WHERE SC.grade > 80 | 外连接先补出 NULL 行，WHERE 再把它筛掉 → 外连接白做了 |
LEFT JOIN SC ON ... AND SC.grade > 80 | 条件写进 ON，配不上的行仍保留（值填 NULL） |
记法：ON 决定"怎么配"，WHERE 决定"配完留谁"。
四、分组与聚合：WHERE 与 HAVING 分工
五个聚合函数：COUNT / SUM / AVG / MAX / MIN。
GROUP BY 把行切成若干组，聚合函数每组算一个值。分组之后，SELECT 列表里只能出现两类东西：分组列和聚合函数（别的列没有唯一值，取哪一个都不对）。
WHERE 与 HAVING 的分工是本章第二大考点：
| 筛什么 | 能不能用聚合函数 | 执行时机 | |
|---|---|---|---|
WHERE | 行 | 不能 | 分组之前 |
HAVING | 组 | 能 | 分组之后 |
性能上有个顺手的结论：能在 WHERE 里筛掉的，就别留到 HAVING。因为 WHERE 减少的是参与分组的行数，HAVING 减少的只是最终输出的组数——前者省的工作多得多。
五、子查询
| 形态 | 出现在哪 | 返回什么 |
|---|---|---|
| 标量子查询 | WHERE x = (SELECT ...) | 一个值 |
| 行子查询 | WHERE (a,b) = (SELECT ...) | 一行 |
| 表子查询 | FROM (SELECT ...) AS t | 一张表 |
IN / NOT IN | WHERE x IN (SELECT ...) | 一列值 |
EXISTS / NOT EXISTS | WHERE EXISTS (SELECT ...) | 真/假 |
| 相关子查询 | 内层引用了外层的列 | 每行都要重算 |
IN 与 EXISTS 的选择常常被考：IN 先算子查询再逐值比对，EXISTS 是外层每来一行就去内层探一次、一探到就返回。子查询结果小的时候 IN 更顺手，外表小、内表大的时候 EXISTS 往往更快。
NOT IN 上有个必须记住的坑：如果子查询结果里带 NULL，x NOT IN (1, 2, NULL) 的结果既不是真也不是假，而是 UNKNOWN，于是一行都筛不出来。这也是下面要说的三值逻辑的直接后果。
六、NULL 与三值逻辑
NULL 不是 0，也不是空字符串，它的意思是"不知道"。所以：
NULL = NULL结果是 UNKNOWN，不是 TRUE。- 判断空值只能用
IS NULL/IS NOT NULL。 - 聚合函数一律忽略
NULL：SUM/AVG/MAX/MIN都不算它，AVG的分母是COUNT(列名)而不是COUNT(*)。 COUNT(*)数行，COUNT(列名)数该列非空的值——这两个数经常不一样，这是最省事的一道送分题。
WHERE 只保留结果为 TRUE 的行。于是"是"与"否"两类条件加起来往往凑不齐总行数，差的就是那些 NULL。
示例
例 1：手写一个 GROUP BY（C）
数据库把 GROUP BY 做得很顺手，但它的内核并不神秘：先按分组键归堆，再对每一堆算聚合。
#include <stdio.h>
/* SC(sno, cno, grade)：grade = -1 表示 NULL（C 里没有真正的 NULL 值） */
static const char *SC_SNO[5] = {"S1", "S1", "S2", "S3", "S4"};
static const char *SC_CNO[5] = {"C1", "C2", "C1", "C2", "C1"};
static const int SC_GRADE[5] = {90, 85, 70, 95, -1};
/* 按 cno 分组做聚合：分组键只有两个，用线性查找代替哈希表 */
static int find(char *g[], int n, const char *k) {
for (int i = 0; i < n; i++)
if (g[i][0] == k[0] && g[i][1] == k[1]) return i; /* "C1"/"C2" 两个字符一并比 */
return -1;
}
int main(void) {
char *group[5];
int n_group = 0;
int cnt_all[5] = {0}; /* COUNT(*) */
int cnt_g[5] = {0}; /* COUNT(grade)*/
int sum_g[5] = {0}; /* SUM(grade) */
for (int i = 0; i < 5; i++) {
int k = find(group, n_group, SC_CNO[i]);
if (k < 0) { group[n_group++] = (char *)SC_CNO[i]; k = n_group - 1; }
cnt_all[k]++; /* COUNT(*) 把 NULL 行也算进去 */
if (SC_GRADE[i] != -1) { /* 聚合函数跳过 NULL */
cnt_g[k]++;
sum_g[k] += SC_GRADE[i];
}
}
printf("%-6s %-10s %-10s %-10s\n", "CNO", "COUNT(*)", "COUNT(G)", "SUM(G)");
for (int k = 0; k < n_group; k++)
printf("%-6s %-10d %-10d %-10d\n", group[k], cnt_all[k], cnt_g[k], sum_g[k]);
/* 三值逻辑：NULL 既不满足 > 80，也不满足 <= 80 */
int gt = 0, le = 0;
for (int i = 0; i < 5; i++) {
if (SC_GRADE[i] == -1) continue; /* NULL 两条路都不走 */
if (SC_GRADE[i] > 80) gt++;
if (SC_GRADE[i] <= 80) le++;
}
printf("grade > 80 : %d\n", gt);
printf("grade <= 80: %d\n", le);
printf("3 + 1 = %d, 总行数 5, 差的那 1 行就是 NULL\n", gt + le);
return 0;
}
c 本站为静态站，不提供在线运行；可复制到本地用 gcc / python 执行
预期输出：
CNO COUNT(*) COUNT(G) SUM(G)
C1 3 2 160
C2 2 2 180
grade > 80 : 3
grade <= 80: 1
3 + 1 = 4, 总行数 5, 差的那 1 行就是 NULL三点：
- C1 的
COUNT(*)是 3，COUNT(G)是 2 —— 差的正是 S4 那行成绩为NULL的记录。 SUM(C1)是 160 = 90 + 70：NULL被直接跳过，不是当 0 加。所以AVG = 160 / 2 = 80.0，不是 160 / 3。3 + 1 = 4，可总行数是 5：那第 5 行既不属于> 80，也不属于<= 80。它落在三值逻辑的第三个格子里。
例 2：交给真的 SQL 引擎跑一遍（Python）
上一段是"手搓内核"，这一段直接用 Python 自带的 SQLite 跑真 SQL，看它的行为与手写版是否一致。
import sqlite3
def dw(s):
"""显示宽度：中文算 2 列"""
return sum(2 if ord(c) > 0x2000 else 1 for c in str(s))
def pad(s, w):
return str(s) + " " * max(0, w - dw(str(s)))
def show(title, cur):
rows = cur.fetchall()
cols = [d[0] for d in cur.description]
widths = [max([dw(c)] + [dw(r[i]) for r in rows]) + 2 for i, c in enumerate(cols)]
print(" " + title)
print(" " + " | ".join(pad(c, widths[i]) for i, c in enumerate(cols)))
for r in rows:
print(" " + " | ".join(pad(r[i], widths[i]) for i in range(len(cols))))
print(" " + pad("共 %d 行" % len(rows), 12))
print()
return len(rows)
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
CREATE TABLE S (sno TEXT PRIMARY KEY, sname TEXT, city TEXT);
CREATE TABLE C (cno TEXT PRIMARY KEY, cname TEXT);
CREATE TABLE SC (sno TEXT, cno TEXT, grade INT, PRIMARY KEY (sno, cno));
INSERT INTO S VALUES ('S1','张三','北京'),('S2','李四','上海'),
('S3','王五','北京'),('S4','赵六','广州');
INSERT INTO C VALUES ('C1','数据库'),('C2','操作系统');
INSERT INTO SC VALUES ('S1','C1',90),('S1','C2',85),('S2','C1',70),
('S3','C2',95),('S4','C1',NULL);
""")
print("=== (1) WHERE 选行：北京的学生 ===")
show("SELECT * FROM S WHERE city = '北京'", cur.execute(
"SELECT * FROM S WHERE city = '北京'"))
print("=== (2) DISTINCT：投影天生去重 ===")
show("SELECT DISTINCT city FROM S", cur.execute("SELECT DISTINCT city FROM S"))
show("SELECT city FROM S（不去重）", cur.execute("SELECT city FROM S"))
print("=== (3) GROUP BY：COUNT(*) 与 COUNT(grade) 不一样 ===")
show("SELECT cno, COUNT(*), COUNT(grade), SUM(grade) FROM SC GROUP BY cno",
cur.execute("SELECT cno, COUNT(*), COUNT(grade), SUM(grade) "
"FROM SC GROUP BY cno"))
print("=== (4) AVG 忽略 NULL，分母是 COUNT(grade) ===")
show("SELECT cno, AVG(grade), MAX(grade), MIN(grade) FROM SC GROUP BY cno",
cur.execute("SELECT cno, AVG(grade), MAX(grade), MIN(grade) "
"FROM SC GROUP BY cno"))
print("=== (5) HAVING 筛分组，WHERE 筛行 ===")
show("SELECT cno, AVG(grade) FROM SC GROUP BY cno HAVING AVG(grade) > 85",
cur.execute("SELECT cno, AVG(grade) FROM SC GROUP BY cno "
"HAVING AVG(grade) > 85"))
print("=== (6) 左外连接：没选课的 S4 也留下 ===")
show("SELECT S.sno, S.sname, SC.cno, SC.grade FROM S LEFT JOIN SC "
"ON S.sno = SC.sno",
cur.execute("SELECT S.sno, S.sname, SC.cno, SC.grade FROM S "
"LEFT JOIN SC ON S.sno = SC.sno"))
print("=== (7) IN 子查询：选过 C1 的学生 ===")
show("SELECT sname FROM S WHERE sno IN (SELECT sno FROM SC WHERE cno = 'C1')",
cur.execute("SELECT sname FROM S WHERE sno IN "
"(SELECT sno FROM SC WHERE cno = 'C1')"))
n_gt = show("SELECT sno, grade FROM SC WHERE grade > 80",
cur.execute("SELECT sno, grade FROM SC WHERE grade > 80"))
n_le = show("SELECT sno, grade FROM SC WHERE grade <= 80",
cur.execute("SELECT sno, grade FROM SC WHERE grade <= 80"))
n_not = show("SELECT sno, grade FROM SC WHERE NOT (grade > 80)",
cur.execute("SELECT sno, grade FROM SC WHERE NOT (grade > 80)"))
print("=== (8) 三值逻辑的账 ===")
print(" " + pad("grade > 80 为 TRUE 的行", 26) + "%d" % n_gt)
print(" " + pad("grade <= 80 为 TRUE 的行", 26) + "%d" % n_le)
print(" " + pad("NOT (grade > 80) 的行", 26) + "%d" % n_not)
print(" " + pad("SC 总行数", 26) + "%d" % cur.execute(
"SELECT COUNT(*) FROM SC").fetchone()[0])
print()
print(" 3 + 1 = %d，而总行数是 5 —— 差的那 1 行就是 grade 为 NULL 的那行：" % (n_gt + n_le))
print(" 它既不满足 > 80，也不满足 <= 80，NOT 也救不回来（UNKNOWN 取反还是 UNKNOWN）。")
python 本站为静态站，不提供在线运行；可复制到本地用 gcc / python 执行
预期输出：
=== (1) WHERE 选行：北京的学生 ===
SELECT * FROM S WHERE city = '北京'
sno | sname | city
S1 | 张三 | 北京
S3 | 王五 | 北京
共 2 行
=== (2) DISTINCT：投影天生去重 ===
SELECT DISTINCT city FROM S
city
北京
上海
广州
共 3 行
SELECT city FROM S（不去重）
city
北京
上海
北京
广州
共 4 行
=== (3) GROUP BY：COUNT(*) 与 COUNT(grade) 不一样 ===
SELECT cno, COUNT(*), COUNT(grade), SUM(grade) FROM SC GROUP BY cno
cno | COUNT(*) | COUNT(grade) | SUM(grade)
C1 | 3 | 2 | 160
C2 | 2 | 2 | 180
共 2 行
=== (4) AVG 忽略 NULL，分母是 COUNT(grade) ===
SELECT cno, AVG(grade), MAX(grade), MIN(grade) FROM SC GROUP BY cno
cno | AVG(grade) | MAX(grade) | MIN(grade)
C1 | 80.0 | 90 | 70
C2 | 90.0 | 95 | 85
共 2 行
=== (5) HAVING 筛分组，WHERE 筛行 ===
SELECT cno, AVG(grade) FROM SC GROUP BY cno HAVING AVG(grade) > 85
cno | AVG(grade)
C2 | 90.0
共 1 行
=== (6) 左外连接：没选课的 S4 也留下 ===
SELECT S.sno, S.sname, SC.cno, SC.grade FROM S LEFT JOIN SC ON S.sno = SC.sno
sno | sname | cno | grade
S1 | 张三 | C1 | 90
S1 | 张三 | C2 | 85
S2 | 李四 | C1 | 70
S3 | 王五 | C2 | 95
S4 | 赵六 | C1 | None
共 5 行
=== (7) IN 子查询：选过 C1 的学生 ===
SELECT sname FROM S WHERE sno IN (SELECT sno FROM SC WHERE cno = 'C1')
sname
张三
李四
赵六
共 3 行
SELECT sno, grade FROM SC WHERE grade > 80
sno | grade
S1 | 90
S1 | 85
S3 | 95
共 3 行
SELECT sno, grade FROM SC WHERE grade <= 80
sno | grade
S2 | 70
共 1 行
SELECT sno, grade FROM SC WHERE NOT (grade > 80)
sno | grade
S2 | 70
共 1 行
=== (8) 三值逻辑的账 ===
grade > 80 为 TRUE 的行 3
grade <= 80 为 TRUE 的行 1
NOT (grade > 80) 的行 1
SC 总行数 5
3 + 1 = 4，而总行数是 5 —— 差的那 1 行就是 grade 为 NULL 的那行：
它既不满足 > 80，也不满足 <= 80，NOT 也救不回来（UNKNOWN 取反还是 UNKNOWN）。四条结论：
- C 段与 Python 段的分组结果完全一致：C1 的
3 / 2 / 160、C2 的2 / 2 / 180。手搓内核与真引擎走的是同一条路。 AVG(C1) = 80.0：160 / 2，分母是COUNT(grade)。如果分母用COUNT(*)就会算成 53.33，那是错的。- 左外连接给出 5 行：
SC只有 4 行，第 5 行是 S4 配不上时补出来的，成绩位置显示None——那是 Python 对 SQLNULL的叫法，数据库里的那个值就叫NULL。 - 三值逻辑那一节是整章的落点：
> 80有 3 行、<= 80有 1 行、NOT (> 80)也只 1 行。"取反"没有把丢失的那一行找回来，因为NOT UNKNOWN仍然是 UNKNOWN。
考点
考点
1. SELECT 的执行顺序（能默写）
FROM → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT。
由此直接推出三个结论：WHERE 不能用 SELECT 的别名；ORDER BY 可以用；聚合函数不能写在 WHERE 里。
2. WHERE 与 HAVING
WHERE筛行、在分组前、不能用聚合函数。HAVING筛组、在分组后、能用聚合函数。- 能写进
WHERE的条件别丢给HAVING——WHERE省掉的是参与分组的行。
3. NULL 的四条行为
| 表达式 | 结果 |
|---|---|
NULL = NULL | UNKNOWN（不是 TRUE） |
x IN (1, 2, NULL) 且 x = 3 | UNKNOWN（不是 FALSE） |
x NOT IN (1, 2, NULL) 且 x = 3 | UNKNOWN → 一行都筛不出来 |
COUNT(*) vs COUNT(列) | 前者数行，后者跳过 NULL |
SUM / AVG / MAX / MIN | 一律跳过 NULL；AVG 的分母是 COUNT(列) |
判空只能用 IS NULL / IS NOT NULL。
4. 外连接的条件放哪
ON 决定怎么配，WHERE 决定配完留谁。LEFT JOIN 之后在 WHERE 里筛右表的列，会把补出来的 NULL 行全部筛掉，外连接等于白写。
5. 五种连接与关系代数的对应
| SQL | 关系代数 |
|---|---|
WHERE | |
SELECT DISTINCT | |
UNION / EXCEPT / INTERSECT | |
CROSS JOIN | |
NATURAL JOIN | |
JOIN ... ON |
投影天生去重，SELECT 默认不去重——所以等价改写时 DISTINCT 不能丢。
6. 易错点清单
- 把
COUNT(*)当成"统计非空值"：它统计的是行数。 - 以为
AVG的分母是行数：是COUNT(列)，NULL不入分母。 - 分组后
SELECT里写了非分组列：标准 SQL 下报错，MySQL 宽松模式下会随便取一行，这个值没有意义。 - 用
NOT IN配子查询：子查询里有NULL就一行都出不来，改用NOT EXISTS或提前把NULL排除。 - 以为
NULL能被=比出来：只能IS NULL。 - 在外连接的
WHERE里筛被连接表：外连接失效。 - 混淆
DISTINCT的作用范围：SELECT DISTINCT a, b是对 (a, b) 组合 去重，不是对a去重。
小结
- SQL 是声明式语言：只描述结果，不描述过程。
- 书写顺序 ≠ 执行顺序。记住执行顺序，
WHERE不能用别名、聚合函数不能进WHERE这两条就不用背了。 - 分组靠
GROUP BY，筛行用WHERE、筛组用HAVING；聚合函数一律跳过NULL。 NULL带来三值逻辑："是"与"否"两边加起来凑不齐总行数，这是最容易丢分的地方。
回到主线：soft 是主线走完之后的向上延伸。上一章把关系代数讲成了一套"集合运算"，本章给它配上了能真正敲出来的语言。可 SQL 只是说，说得再顺，若数据库每次都要把整张表读一遍，说得再好也没用——下一章要解决的，就是"怎么让数据库不必翻遍全表"：索引。
下一篇：索引与 B+ 树
评论（0）
当前浏览器不允许本地存储，评论无法保存。
还没有评论，来说两句。