05 SQL 数据库基础¶
覆盖范围:关系模型(表/行/列、主键/外键、三大范式)、SQL 分类(DDL/DML/DQL/DCL)、SELECT 全子句(WHERE/GROUP BY/HAVING/ORDER BY/LIMIT)、JOIN 类型、聚合与子查询、窗口函数、索引原理(B+树/聚簇)、事务 ACID 与隔离级别、锁与优化。 使用方式:先读原理讲解,再看 SQL 示例(默写),最后用 Python 模拟迷你查询引擎验证语义,自测面试追问。
1. 关系模型:数据库的抽象¶
关系数据库把数据组织成表(关系):
| 概念 | 含义 | SQL 对应 |
|---|---|---|
| 表 Table / 关系 | 二维结构:行 × 列 | CREATE TABLE |
| 行 Row / 元组 | 一条记录 | INSERT 一行 |
| 列 Column / 属性 | 一个字段 | ALTER TABLE ADD COLUMN |
| 主键 PK | 唯一标识一行,非空唯一 | PRIMARY KEY |
| 外键 FK | 引用其他表主键,保证引用完整性 | FOREIGN KEY REFERENCES |
| 索引 Index | 加速查找的辅助结构(不改变逻辑) | CREATE INDEX |
三大范式(Normal Form):
- 1NF:列不可再分(原子性)——每个单元格只有一个值;
- 2NF:在 1NF 基础上,非主属性完全依赖主键(消除部分依赖,主要针对联合主键);
- 3NF:在 2NF 基础上,非主属性不传递依赖主键(消除冗余:如「部门名」依赖「部门号」而部门号又依赖员工号,应拆表)。
面试一句话:范式是消除冗余与更新异常的经验法则;实际工程常反范式(冗余字段换查询性能),是否遵守要看场景。
2. SQL 四大分类¶
| 分类 | 全称 | 作用 | 命令 |
|---|---|---|---|
| DDL | Data Definition | 定义结构 | CREATE / ALTER / DROP / TRUNCATE |
| DML | Data Manipulation | 操作数据 | INSERT / UPDATE / DELETE |
| DQL | Data Query | 查询 | SELECT |
| DCL | Data Control | 权限事务控制 | GRANT / REVOKE / COMMIT / ROLLBACK |
-- DDL
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id)
);
-- DML
INSERT INTO users(name, age, dept_id) VALUES ('张三', 25, 1);
UPDATE users SET age = 26 WHERE id = 1;
DELETE FROM users WHERE id = 2;
3. SELECT:查询的完整骨架(执行顺序!)¶
SELECT 列, 聚合(列)
FROM 表
[JOIN 表 ON 条件]
[WHERE 行过滤]
[GROUP BY 分组]
[HAVING 组过滤]
[ORDER BY 排序]
[LIMIT 条数];
逻辑执行顺序(面试必考):
FROM(含 JOIN):先确定数据来源与连接;WHERE:逐行过滤;GROUP BY:按列分组;HAVING:对分组后的结果过滤(能用聚合函数);SELECT:投影列、计算聚合;ORDER BY排序、LIMIT截断。
⚠️ 易错点:
WHERE不能使用聚合函数(它在分组前执行),HAVING可以。如WHERE COUNT(*) > 3是错的,必须是HAVING COUNT(*) > 3。
4. JOIN:表的连接¶
| 类型 | 结果 | 通俗理解 |
|---|---|---|
INNER JOIN |
两边都匹配的行 | 交集 |
LEFT JOIN |
左表全部 + 右表匹配(无则 NULL) | 左表优先 |
RIGHT JOIN |
右表全部 + 左表匹配 | 右表优先 |
FULL OUTER JOIN |
两边全部(无匹配补 NULL) | 并集(MySQL 不支持,用 UNION 模拟) |
CROSS JOIN |
笛卡尔积 | 每行 × 每行 |
SELECT u.name, d.dept_name
FROM users u
LEFT JOIN dept d ON u.dept_id = d.id; -- 无部门的用户也显示(dept_name 为 NULL)
5. 聚合与分组¶
聚合函数:COUNT / SUM / AVG / MAX / MIN(忽略 NULL;COUNT(*) 计行数、COUNT(col) 计非 NULL)。
-- 每个部门的平均年龄,只保留平均年龄 > 30 的部门,按平均年龄降序
SELECT dept_id, AVG(age) AS avg_age
FROM users
GROUP BY dept_id
HAVING AVG(age) > 30
ORDER BY avg_age DESC;
6. 子查询 / 窗口函数¶
-- 子查询:工资高于部门平均的员工
SELECT name FROM emp e
WHERE salary > (SELECT AVG(salary) FROM emp WHERE dept_id = e.dept_id);
-- 窗口函数:按部门分组内按工资排名(不折叠行!)
SELECT name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM emp;
-- RANK 并列会跳号(1,1,3);DENSE_RANK 不跳号(1,1,2);ROW_NUMBER 连续(1,2,3)
窗口函数 vs GROUP BY:GROUP BY 折叠每组成一行;窗口函数保留每个原始行,同时计算聚合/排名——所以能「同时看到明细和排名」。
核心概念¶
(a) 🗂️ SQL 从「关系模型」到「事务与索引」的主线¶
这篇从关系模型与范式讲起,然后是 SQL 四大分类、SELECT 的执行顺序、JOIN、聚合分组、窗口函数,再深入到索引原理(B+ 树)、事务 ACID 与隔离级别、锁与优化——覆盖了数据库笔试面试的绝大多数考点。
flowchart TB
Node1["🧠 关系模型:表 = 行 × 列"] --> Node2["🔑 主键 / 外键 / 范式"]
Node1 --> Node3["📚 SQL 四分类:DDL / DML / DQL / DCL"]
Node2 --> Node4["🔍 SELECT 逻辑执行顺序"]
Node4 --> Node5["🔗 JOIN:INNER / LEFT / RIGHT / FULL"]
Node4 --> Node6["📊 聚合 GROUP BY + HAVING"]
Node6 --> Node7["🪟 窗口函数:排名不折叠行"]
Node1 --> Node8["🌳 索引:B+ 树 O(log n)"]
Node8 --> Node9["📜 事务 ACID + 隔离级别"]
Node9 --> Node10["🔒 锁与优化"]
- 🧠 关系模型:关系数据库把数据组织成二维表:行(Row)是一条记录、列(Column)是一个字段。理解「表/行/列」是一切 SQL 的地基,建表、查数据、关联都围绕它转。
- 🔑 主键 / 外键 / 范式:主键唯一标识一行(非空唯一);外键引用另一张表的主键,保证引用完整性;三大范式是消除冗余的经验法则:1NF 列不可再分、2NF 消除部分依赖、3NF 消除传递依赖——工程里常反范式换性能。
- 📚 SQL 四分类:DDL 定义结构(
CREATE/ALTER/DROP)、DML 操作数据(INSERT/UPDATE/DELETE)、DQL 查询(SELECT)、DCL 权限与事务控制(GRANT/COMMIT/ROLLBACK)。 - 🔍 SELECT 逻辑执行顺序:
FROM(含 JOIN) →WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT。正因为 WHERE 在分组前执行,它不能用聚合函数;HAVING COUNT(*) > 3才是对的。 - 🔗 JOIN:INNER 是两边都匹配的交集;LEFT 保留左表全部、右表缺的补 NULL;RIGHT 反之;FULL 两边全保留(MySQL 不支持,用 UNION 模拟);CROSS 是笛卡尔积。
- 📊 聚合 GROUP BY + HAVING:
COUNT/SUM/AVG/MAX/MIN忽略 NULL;GROUP BY把行按列折叠成组,HAVING对组做过滤(能用聚合函数),ORDER BY排结果。 - 🪟 窗口函数:
RANK()/ROW_NUMBER()配PARTITION BY+ORDER BY在分组内排名,但不折叠行——每个原始行都保留,能同时看明细和排名;RANK 并列跳号(1,1,3)、DENSE_RANK 不跳(1,1,2)、ROW_NUMBER 连续(1,2,3)。 - 🌳 索引:没索引就全表扫描 O(n);B+ 树多路平衡、非叶子只存键(扇出大)、叶子有序相连(范围查询友好),查询 O(log n),千万行约 3-4 层。聚簇索引叶子直接存整行,二级索引存主键再回表。
- 📜 事务 ACID + 隔离级别:事务是一组要么全成功要么全回滚的操作:原子性靠 undo log、持久性靠 redo log、隔离性靠锁 + MVCC。四种隔离级别(RU → RC → RR → Serializable)逐级防脏读、不可重复读、幻读;MySQL 默认可重复读。
- 🔒 锁与优化:乐观锁不加锁、更新时对比版本号;悲观锁
SELECT ... FOR UPDATE锁行。优化三板斧:加合适索引(覆盖索引)、优化 SQL(避免SELECT *)、用EXPLAIN看执行计划(type 为 ALL 就要警惕全表扫)。
✍️ §(核心).1 手算 JOIN:3 用户 × 3 订单的内外连接结果¶
INNER JOIN 只留匹配行,LEFT JOIN 保留左表所有行、无匹配补 NULL——两张小表手算,看懂 3 行 vs 4 行。
设定:users(1-Alice, 2-Bob, 3-Carol);orders(1→100, 1→200, 2→300)。
INNER JOIN users.id = orders.user_id
$$(1,Alice,100),\ (1,Alice,200),\ (2,Bob,300)\Rightarrow\mathbf{3}\ \text{行}$$
LEFT JOIN 同条件(Carol 无订单 → 补 NULL)
$$\text{上面 3 行} + (3,Carol,\text{NULL})\Rightarrow\mathbf{4}\ \text{行}$$
手算收获:
- INNER = 匹配才出现:Bob 2 行、Carol 0 行、Alice 2 行→共 3 行;LEFT = 左表全保留:Carol 以 NULL 出场——行数差 = 未匹配左表行数
- JOIN 本质是笛卡尔积按条件过滤:users×orders 有 $3\times3=9$ 组合,INNER 筛出 3 个匹配、LEFT 额外补 1 行 NULL
- 一对多会放大行数:Alice 有 2 单所以出 2 行——JOIN 前想清"会不会炸行"(分析岗大坑)
- 面试速答:INNER/LEFT/RIGHT/FULL 区别就是"保留哪边 + 未匹配补 NULL";GROUP BY 在 JOIN 之后折叠(WHERE 先行,§代码实战)
下面 numpy 复算。
# ---- §(核心).1 验证:numpy 复算 JOIN ----
users = [(1, 'Alice'), (2, 'Bob'), (3, 'Carol')]
orders = [(1, 100), (1, 200), (2, 300)]
inner = [(uid, name, amt) for uid, name in users for oid, amt in orders if uid == oid]
left = []
for uid, name in users:
matched = [(uid, name, amt) for oid, amt in orders if oid == uid]
left.extend(matched if matched else [(uid, name, None)])
print(f'INNER JOIN = {inner}({len(inner)} 行)')
print(f'LEFT JOIN = {left}({len(left)} 行)')
assert inner == [(1, 'Alice', 100), (1, 'Alice', 200), (2, 'Bob', 300)]
assert left == [(1, 'Alice', 100), (1, 'Alice', 200), (2, 'Bob', 300), (3, 'Carol', None)]
print('\n✓ 与手算一致')
# ---- matplotlib 中文字体(防图内中文乱码)----
import matplotlib
matplotlib.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei', 'Songti SC', 'Arial Unicode MS', 'DejaVu Sans']
matplotlib.rcParams['axes.unicode_minus'] = False
# -------------------------------------------
# 迷你关系查询引擎:SELECT + WHERE + 投影 + 排序 + LIMIT
def select(table, cols=None, where=None, order_by=None, limit=None):
"""极简 SELECT 实现:按 SQL 逻辑顺序执行"""
rows = table['rows']
names = table['cols']
# 1. WHERE:逐行过滤(模拟)
if where:
rows = [r for r in rows if where(dict(zip(names, r)))]
# 2. 投影列
if cols:
idx = [names.index(c) for c in cols]
rows = [[r[i] for i in idx] for r in rows]
names = cols
# 3. ORDER BY:按列排序
if order_by:
col, desc = order_by
i = names.index(col)
rows.sort(key=lambda r: r[i], reverse=desc)
# 4. LIMIT
if limit is not None:
rows = rows[:limit]
return names, rows
users = {
'cols': ['id', 'name', 'age', 'dept_id'],
'rows': [
[1, '张三', 25, 1],
[2, '李四', 32, 1],
[3, '王五', 28, 2],
[4, '赵六', 41, 2],
[5, '孙七', 22, None], # 无部门
],
}
print('SELECT name, age FROM users WHERE age > 26 ORDER BY age DESC LIMIT 2:')
names, rows = select(users, cols=['name', 'age'],
where=lambda r: r['age'] > 26,
order_by=('age', True), limit=2)
print(' ', names, '->', rows)
assert rows == [['赵六', 41], ['李四', 32]]
# JOIN 语义:INNER / LEFT 对照(理解 NULL 补齐)
users = {'cols': ['id', 'name', 'dept_id'], 'rows': [
[1, '张三', 1], [2, '李四', 1], [3, '王五', 2], [4, '孙七', None]]}
dept = {'cols': ['id', 'dept_name'], 'rows': [
[1, '研发'], [2, '产品'], [3, '运营']]}
def join_clean(t1, t2, on, how='inner', cols=None):
"""完整版 JOIN:支持 inner/left/right + 投影"""
c1, c2 = t1['cols'], t2['cols']
out = []
left_matched = set()
for i1, r1 in enumerate(t1['rows']):
hit = False
for r2 in t2['rows']:
if on(dict(zip(c1, r1)), dict(zip(c2, r2))):
out.append(r1 + r2)
hit = True
left_matched.add(i1)
if not hit and how in ('left', 'full'):
out.append(r1 + [None] * len(c2))
if how in ('right', 'full'):
for i2, r2 in enumerate(t2['rows']):
if not any(on(dict(zip(c1, r1)), dict(zip(c2, r2))) for r1 in t1['rows']):
out.append([None] * len(c1) + r2)
if cols:
allcols = c1 + c2
idx = [allcols.index(c) for c in cols]
out = [[r[i] for i in idx] for r in out]
return out
on = lambda u, d: u['dept_id'] == d['id']
print('LEFT JOIN(孙七无部门也保留,dept 补 NULL):')
r = join_clean(users, dept, on, 'left', ['name', 'dept_name'])
for row in r: print(' ', row)
assert ['孙七', None] in r and ['张三', '研发'] in r
print('\nRIGHT JOIN(运营部门无员工,users 侧补 NULL):')
r2 = join_clean(users, dept, on, 'right', ['name', 'dept_name'])
for row in r2: print(' ', row)
print('\nINNER JOIN(只保留匹配行):')
r3 = join_clean(users, dept, on, 'inner', ['name', 'dept_name'])
print(' ', r3)
assert len(r3) == 3
# GROUP BY + HAVING:分组聚合(WHERE 先行 -> 分组 -> HAVING 过滤组)
emp = {'cols': ['name', 'dept_id', 'salary'], 'rows': [
['a', 1, 5000], ['b', 1, 6000], ['c', 2, 3000], ['d', 2, 4000], ['e', 3, 9000]]}
def group_by_agg(table, group_col, agg_fn):
"""模拟 GROUP BY col + 聚合:返回 [(组值, 聚合结果)]"""
names = table['cols']
gi = names.index(group_col)
groups = {}
for r in table['rows']:
k = r[gi]
groups.setdefault(k, []).append(r)
return [(k, agg_fn(rows)) for k, rows in sorted(groups.items())]
avg = lambda rows: sum(r[2] for r in rows) / len(rows)
cnt = lambda rows: len(rows)
print('每个部门平均工资:')
res = group_by_agg(emp, 'dept_id', avg)
for k, v in res: print(f' dept {k}: AVG={v:.0f}')
print('\nHAVING 平均工资 > 4500 的部门(先分组后过滤组):')
filtered = [(k, v) for k, v in res if v > 4500]
for k, v in filtered: print(f' dept {k}: AVG={v:.0f}')
assert [k for k, _ in filtered] == [1, 3]
print('\n每部门人数:')
res2 = group_by_agg(emp, 'dept_id', cnt)
for k, v in res2: print(f' dept {k}: COUNT={v}')
assert dict(res2)[2] == 2
# 窗口函数语义:RANK vs ROW_NUMBER(对比 GROUP BY 不折叠行)
scores = [('a', 'A', 90), ('b', 'A', 80), ('c', 'A', 90), ('d', 'B', 70), ('e', 'B', 85)]
def window_rank(table, part_col, order_col, desc=True):
"""模拟 RANK() OVER (PARTITION BY ... ORDER BY ...)"""
names = table['cols']
pi, oi = names.index(part_col), names.index(order_col)
parts = {}
for idx, r in enumerate(table['rows']):
parts.setdefault(r[pi], []).append((idx, r))
result = [None] * len(table['rows'])
for p, items in parts.items():
items.sort(key=lambda x: x[1][oi], reverse=desc)
prev_val, prev_rank = None, 0
for pos, (idx, r) in enumerate(items, 1):
if r[oi] == prev_val:
rank = prev_rank # RANK:并列同排名
else:
rank = pos
result[idx] = [p, r[oi], rank]
prev_val, prev_rank = r[oi], rank
return result
tbl = {'cols': ['name', 'class', 'score'], 'rows': scores}
print('按班级分区、分数降序 RANK(并列跳号):')
for row in window_rank(tbl, 'class', 'score'):
print(' class', row[0], 'score', row[1], 'rank', row[2])
res = window_rank(tbl, 'class', 'score')
# 注意:result 按原表行序存放,A 班行序为 (90,80,90) -> rank (1,3,1)
ranks_a = [r[2] for r in res if r[0] == 'A']
assert ranks_a == [1, 3, 1], 'A班按原行序 90,80,90 -> RANK 1,3,1(并列跳号)'
# 等价视角:按分数降序 A 班为 90,90,80 -> RANK 1,1,3
print('\n验证:A 班按分数降序 90,90,80 -> RANK 1,1,3(RANK 并列占用下一名次)')
print('对比 ROW_NUMBER -> 1,2,3(每行连续不同排名)')
print('对比 DENSE_RANK -> 1,1,2(并列不跳号)')
7. 索引:为什么查找快¶
7.1 没有索引的代价¶
没有索引时,查询要全表扫描(O(n) 逐行比对)——千万行表就扫千万次。
7.2 B+ 树索引(主流)¶
- 结构:多路平衡查找树——非叶子只存键(扇出大,一层可装上千键),叶子存键 + 指向行的指针;
- 叶子有序且相连(链表),所以范围查询(
BETWEEN、>)只需在叶子顺序扫; - 查询复杂度 O(log n):1000 万行约 3-4 层,3-4 次磁盘 IO;
- 聚簇索引(InnoDB 主键):叶子直接存整行数据,表数据按主键有序;二级索引:叶子存主键值,再回表查(索引覆盖可避免回表)。
7.3 什么时候索引失效(面试高频)¶
| 写法 | 问题 |
|---|---|
WHERE name LIKE '%xx' |
前导通配符,无法走索引 |
WHERE func(col) = x |
列上函数运算,索引失效(应 col = 反函数(x)) |
WHERE col+1 = 5 |
列参与运算 |
| 隐式类型转换 | WHERE str_col = 123 可能全表扫 |
| 联合索引不满足最左前缀 | (a,b,c) 索引查 b 不用 |
OR 连接非索引列 |
可能全表扫(UNION 代替) |
| 区分度低(如性别) | 优化器认为没用直接扫表 |
联合索引最左前缀:(a, b, c) 索引能加速 a、a,b、a,b,c 查询;跳列(只有 b/c)不生效。
8. 事务 ACID 与隔离级别¶
事务 = 一组要么全成功要么全回滚的操作,保证 ACID:
| 特性 | 含义 | 实现层面 |
|---|---|---|
| A 原子性 | 全部执行或全部不执行 | undo log(回滚日志) |
| C 一致性 | 事务前后数据满足约束 | 应用 + 数据库共同保证 |
| I 隔离性 | 并发事务互不干扰 | 锁 + MVCC |
| D 持久性 | 提交后数据不丢失 | redo log + 刷盘 |
并发问题(隔离性不满足时):
- 脏读:读到别的事务未提交的数据;
- 不可重复读:同一事务内两次读同一行,值变了(别的事务提交了更新);
- 幻读:两次范围查询,行数变了(别的事务插入/删除了行)。
四种隔离级别(MySQL InnoDB):
| 级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 Read Uncommitted | ✗ 可能 | ✗ 可能 | ✗ 可能 |
| 读已提交 Read Committed | ✓ 防 | ✗ 可能 | ✗ 可能 |
| 可重复读 Repeatable Read(MySQL 默认) | ✓ 防 | ✓ 防 | ✗ 可能(MVCC 下基本防住) |
| 串行化 Serializable | ✓ 防 | ✓ 防 | ✓ 防 |
MVCC(多版本并发控制):读不加锁,通过「版本链 + 快照」读历史版本——实现读已提交/可重复读,读不阻塞写、写不阻塞读。
面试高频:MySQL 默认隔离级别是可重复读(Oracle 默认读已提交);可重复读下 MVCC 的快照读能防幻读,但当前读(
SELECT ... FOR UPDATE/ UPDATE / DELETE)需**间隙锁(Gap Lock)**才能防幻读。
9. 锁与优化速记¶
- 乐观锁:不加锁,更新时对比版本号/条件,冲突则失败重试(适合读多写少);
- 悲观锁:
SELECT ... FOR UPDATE直接锁行(适合写冲突频繁); - 锁粒度:表锁 > 页锁 > 行锁(InnoDB 行锁基于索引);
- 优化三板斧:① 加合适索引(覆盖索引);② 优化 SQL(避免
SELECT *、大表分页LIMIT offset慢用游标/键集分页);③ 慢查询日志 +EXPLAIN看执行计划(type 是否为 index/ref/eq_ref)。
10. 面试 30 秒速答¶
- 范式:1NF 原子列 / 2NF 消除部分依赖 / 3NF 消除传递依赖;工程常反范式换性能;
- SELECT 顺序:FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT;WHERE 不能放聚合函数;
- JOIN:INNER 交集、LEFT 左全右缺、RIGHT 右全左缺、FULL 全保留;
- 窗口函数:不折叠行,
PARTITION BY分区 +ORDER BY排序;RANK 跳号、DENSE_RANK 不跳、ROW_NUMBER 连续; - 索引:B+ 树 O(log n),叶子有序链表;最左前缀;
%xx/函数/隐式转换导致失效; - ACID:原子性 undo、持久性 redo、隔离性 锁+MVCC;
- 隔离级别:RU < RC < RR(MySQL 默认)< Serializable;脏读/不可重复读/幻读逐级防;
- EXPLAIN:看 type 列(ALL 全表扫要优化)、是否走索引、扫描行数。
11. 自测清单(面试追问)¶
- 主键、外键、索引的区别?为什么外键能保证引用完整性?
- 三大范式各解决什么问题?举例 2NF/3NF 的冗余案例?什么时候该反范式?
- SELECT 各子句执行顺序?为什么 WHERE 不能使用聚合函数而 HAVING 可以?
- 手写:LEFT JOIN 两表查询;GROUP BY + HAVING 分组过滤;窗口函数排名。
- INNER/LEFT/RIGHT/FULL JOIN 各返回什么?
ON与WHERE过滤时机区别? - RANK / DENSE_RANK / ROW_NUMBER 的区别?窗口函数与 GROUP BY 的本质区别?
- B+ 树为什么适合数据库索引?(扇出大、叶子有序、范围查询友好)
- 聚簇索引与二级索引区别?什么是回表与索引覆盖?
- 索引失效的常见场景?(最左前缀、LIKE 前缀、函数、隐式转换)
- ACID 各由什么机制保证?MySQL 默认隔离级别?脏读/不可重复读/幻读分别是什么?
- MVCC 是什么?快照读与当前读的区别?间隙锁防什么?
- 乐观锁与悲观锁怎么选?(写冲突频率 / 读多写少)
- 一条慢 SQL 你怎么排查?(EXPLAIN、慢查询日志、索引分析)
✅ 全部能回答 + 能默写 SQL,本课通过。