表与 SQL 基础
本合集目录
本章目录
单册说明:本文件是卷 6 的第 2 章,可独立阅读。前置:第 1 章(存储选型)。本章把「读到」的四张表建出来,逐字段解释约束,然后用六条查询把数据问出来。所有 SQL 都在真实库上跑过。
| 项 | 内容 |
|---|---|
| 所属卷 | 卷 6 · 数据与状态(第 2 / 8 章) |
| 前置章节 | 6.1 数据存在哪儿 |
| 本章产出 | 四张表 + 一份测试数据 + 六条能跑的查询 |
| 对应代码 | app/migrations/001_init.sql,本章另附 seed.py |
| 配图 | 无(后续统一制图) |
| 读完的判定 | 能看着一个需求写出带 WHERE、JOIN、GROUP BY 的查询,并说出它为什么不会数错 |
6.2.1 一句话结论:表设计的每个字段都在回答三个问题
一个问题:这一列能不能为空?能不能重复?指向谁? 三个答案写进约束,数据库就替你把关;不写,就靠你每次写代码时记得。
先说失败。你给 users.email 只写了 TEXT 没写 UNIQUE。注册接口里查了一下"这个邮箱存在吗",不存在就插——两个请求同时进来,都查到不存在,都插成功:
id | email | password_hash
1 | [email protected] | scrypt$...(旧密码)
2 | [email protected] | scrypt$...(新密码)
之后这个人改密码、登录,时好时坏。因为 SELECT ... WHERE email = ? 只返回第一行,改密码改的是另一行。这不是"偶发 bug",是约束缺失。加上 UNIQUE 之后,第二个插入会直接失败:
sqlite3.IntegrityError: UNIQUE constraint failed: users.email
一句报错,比三天排查便宜。
这里有个必须讲清的先后关系:应用层的检查是"尽力而为",数据库层的约束是"最终保证"。 注册接口里那句 SELECT ... WHERE email = ? 一定要有,因为它能给用户一句友好的"这个邮箱已注册";但它挡不住两个请求同时通过检查。约束未必会先被触发,可一旦触发,它就是最后一道门。所以两道都要有,指望任何一道单独成立,都会在某个并发时刻翻车。
6.2.2 原理与方法
四张表,逐字段
| 表 | 字段 | 类型 | 约束 | 为什么这么定 |
|---|---|---|---|---|
| users | id | INTEGER | 主键、自增 | 数字比邮箱更适合做引用目标,改邮箱不影响关联 |
| TEXT | NOT NULL、UNIQUE | 登录名,必须唯一;统一小写后存 | ||
| password_hash | TEXT | NOT NULL | 只存哈希,永不存明文;字段名就叫 hash,防止有人往里塞明文 | |
| created_at | TEXT | NOT NULL、默认当前时间 | ISO 8601 字符串,排序即时间序 | |
| notes | id | INTEGER | 主键、自增 | 同上 |
| user_id | INTEGER | NOT NULL、外键 → users.id | 归属。没有它就没有多用户,也没有越权可防 | |
| title | TEXT | NOT NULL | 允许空字符串是业务问题,不允许 NULL 是数据问题 | |
| content | TEXT | NOT NULL | 同上 | |
| source_url | TEXT | 可空 | "手写摘录"本来就没有来源,空是合法状态 | |
| created_at | TEXT | NOT NULL、默认值 | 列表默认按它倒序 | |
| tags | id | INTEGER | 主键 | 标签改名时不影响关联 |
| name | TEXT | NOT NULL、UNIQUE | 同一个标签只应存在一次 | |
| note_tags | note_id | INTEGER | 外键 → notes.id,联合主键之一 | 关系表 |
| tag_id | INTEGER | 外键 → tags.id,联合主键之一 | 联合主键保证同一条摘录不会重复挂同一个标签 |
两个类型上的取舍
SQLite 的类型是"建议"而不是"铁律":你写 INTEGER,它照样接受字符串 'abc'(除非表是 STRICT 表)。真正起作用的是约束。所以有两处需要主动决定:
| 选择 | 这里怎么定 | 理由 |
|---|---|---|
| 时间存 TEXT 还是 INTEGER 时间戳 | 存 TEXT,格式固定为 2026-11-17T09:12:03Z |
人类可读、能直接比大小、导出到 Excel 不用转换;代价是占的空间比整数多一点 |
source_url 存一列还是拆成域名+路径 |
存一列 | 拆开只在"按站点统计"时才值得,本项目的查询用不到;过早拆会把一个字段变成三个字段的维护成本 |
NOT NULL 还是给默认值 |
该必填就 NOT NULL,且不给默认值 |
给了默认值,代码里漏传字段会静默写入 '' 或 0,错误从"插入失败"变成"数据看起来不对" |
一句话:约束不是用来为难代码的,是用来把错误从"运行时才发现"提前到"写入那一刻"。
多对多为什么必须有中间表一条摘录可以有多个标签,一个标签下有多条摘录——这是多对多。多对多没法用"在 notes 表里加一列"解决:
| 方案 | 长什么样 | 问题 |
|---|---|---|
在 notes 里加 tag 列 |
notes(1, '事务', 'sqlite') |
一条摘录只能有一个标签 |
在 notes 里加 tags 列存 "sqlite,python" |
notes(1, '事务', 'sqlite,python') |
查"所有 python 标签"要 LIKE '%python%',会把 python3 也捞出来;改名要全表替换 |
加一张 note_tags |
(1,1)、(1,2) |
每条关系一行,可索引、可约束、可统计 |
中间表的联合主键 PRIMARY KEY (note_id, tag_id) 是关键:它同时保证"不重复"和"两个方向都能查"。缺了它,同一条摘录挂两次 python,标签统计就会变成 2。
增删改查:四个动词
| 动作 | 语句 | 返回什么 | 常见错误 |
|---|---|---|---|
| 增 | INSERT INTO notes(user_id,title,content) VALUES (?,?,?) |
新行的 lastrowid |
忘了 NOT NULL 的列 |
| 查 | SELECT id,title FROM notes WHERE user_id = ? |
0 到多行 | SELECT * 把大字段也拖出来 |
| 改 | UPDATE notes SET title = ? WHERE id = ? AND user_id = ? |
影响行数 | 漏写 WHERE,全表被改 |
| 删 | DELETE FROM notes WHERE id = ? AND user_id = ? |
影响行数 | 漏写 WHERE,全表被删 |
UPDATE 和 DELETE 的 WHERE 值得单独记:漏写不会报错,只会安静地改掉/删掉所有行。执行前先用同样的 WHERE 跑一次 SELECT COUNT(*),是成本最低的保险。
WHERE 的运算符
| 写法 | 含义 | 例子 |
|---|---|---|
= != <> |
相等 / 不等 | user_id = 1 |
> < >= <= |
比较 | id > 100 |
BETWEEN a AND b |
闭区间 | created_at BETWEEN '2026-11-01' AND '2026-11-30' |
IN (...) |
属于集合 | id IN (1,2,3) |
LIKE '%x%' |
模糊匹配,% 任意长度,_ 一个字符 |
title LIKE '%事务%' |
IS NULL / IS NOT NULL |
判空 | source_url IS NULL |
AND / OR / NOT |
组合 | (a OR b) AND c |
AND 的优先级高于 OR,所以 a OR b AND c 等于 a OR (b AND c)。写混了就加括号,别靠记忆。
JOIN:把两张表接起来
| 类型 | 语义 | 用在哪 |
|---|---|---|
INNER JOIN(写 JOIN 就是它) |
两边都匹配上的行才保留 | 查"有标签的摘录的标签名" |
LEFT JOIN |
左表全保留,右表匹配不上就填 NULL | 查"所有标签,没用过的也要显示 0" |
| 笛卡尔积(不带 ON 条件) | 行数相乘 | 几乎总是 bug |
判断该用哪个,问一句:"没有对应记录的那一方,要不要出现?" 要,就用 LEFT JOIN。
聚合:把多行压成一个数
| 函数 | 含义 | 陷阱 |
|---|---|---|
COUNT(*) |
数行数 | JOIN 之后数的是"连接后的行",不是"条目数" |
COUNT(col) |
数该列非 NULL 的行 | NULL 不计入,这条常被用来意外"过滤" |
COUNT(DISTINCT col) |
去重后计数 | 统计"有多少条不同的摘录"要用它 |
SUM / AVG / MAX / MIN |
求和/均值/最大/最小 | AVG 忽略 NULL,分母不是总行数 |
GROUP BY col |
按列分组,每组一行 | SELECT 里出现的非聚合列,必须出现在 GROUP BY 里 |
HAVING |
对分组结果再过滤 | 和 WHERE 的区别:WHERE 过滤行,HAVING 过滤组 |
NULL:不是 0,也不是空字符串
SQL 里的 NULL 意思是"未知"。任何和它比较的结果也是"未知",而"未知"在 WHERE 里不算成立:
| 写法 | 结果 | 说明 |
|---|---|---|
source_url = NULL |
永远不成立 | 未知等于未知?不知道 |
source_url != NULL |
永远不成立 | 同上 |
source_url IS NULL |
正确 | 专用语法 |
NULL = NULL |
结果是 NULL,不是 1 | 三值逻辑:真 / 假 / 未知 |
所以 COUNT(source_url) 会跳过空值,SUM 也会,WHERE x != 'a' 会漏掉所有 x IS NULL 的行。这不是缺陷,是"未知"这个语义的必然结果。
6.2.3 动手:建四张表,灌数据,跑六条查询
写查询有个固定的三步,照这个顺序想,比"先写 SELECT 再说"快得多:
- 要哪几列:列表页只要
id, title, created_at,详情页才要content; - 数据在哪几张表:只涉及 notes 就是单表;要标签名就必然三张表;
- 怎么过滤、怎么排序、要不要分组:过滤写
WHERE,排序写ORDER BY,统计才用GROUP BY。
把下面的 SQL 存成 seed.py 里的字符串,跑一次就有数据了(删掉旧库再跑,结果可复现):
con.executescript("""
CREATE TABLE users (...); -- 见前文逐字段表
CREATE TABLE notes (...);
CREATE TABLE tags (...);
CREATE TABLE note_tags (...);
""")
con.executemany("INSERT INTO users(email, password_hash) VALUES (?,?)", [
("[email protected]", "scrypt$..."), ("[email protected]", "scrypt$...")])
con.executemany("INSERT INTO notes(user_id, title, content, source_url) VALUES (?,?,?,?)", [
(1, "SQLite 的事务", "BEGIN IMMEDIATE 先拿写锁", None),
(1, "Python 的 with", "上下文管理器负责释放资源", "https://example.com/py"),
(2, "别人的摘录", "不该被你看到", None)])
con.executemany("INSERT INTO tags(name) VALUES (?)",
[("sqlite",), ("python",), ("读书",), ("待整理",)])
con.executemany("INSERT INTO note_tags(note_id, tag_id) VALUES (?,?)",
[(1, 1), (1, 2), (2, 2), (3, 3)])
con.commit()
这里用 executescript 只为一次跑完建表语句,它不需要原子性;第 5 章的迁移脚本不能这么写,原因在那一章的坑二。
注意 待整理 这个标签故意不挂任何摘录,它是下面 LEFT JOIN 的对照组。
查询 1:某人的全部摘录,新的在前
SELECT id, title, created_at FROM notes WHERE user_id = 1 ORDER BY id DESC;
(2, 'Python 的 with', '2026-11-16T18:23:45Z')
(1, 'SQLite 的事务', '2026-11-16T18:23:45Z')
查询 2:在标题或正文里搜关键词
SELECT id, title FROM notes
WHERE user_id = 1 AND (title LIKE '%Python%' OR content LIKE '%Python%')
ORDER BY id DESC;
(2, 'Python 的 with')
括号不能省。少了它,AND 会先和 content LIKE 结合,结果变成"user_id=1 且标题含 Python"或者"任何人的正文含 Python"——跨用户的搜索结果就漏出来了。
查询 3:一条摘录的标签(三表 JOIN)
SELECT n.id, n.title, t.name
FROM notes n
JOIN note_tags nt ON nt.note_id = n.id
JOIN tags t ON t.id = nt.tag_id
WHERE n.id = 1
ORDER BY t.name;
(1, 'SQLite 的事务', 'python')
(1, 'SQLite 的事务', 'sqlite')
两行,因为这条摘录挂了两个标签。JOIN 会把一行变成多行,这是后面所有"数错"问题的根源。
查询 3b:一次取出多条摘录的标签(列表页专用)
列表页要显示 20 条摘录、每条带标签。写法有两个:
| 写法 | 请求数 | 结果 |
|---|---|---|
| 先查 20 条,再对每条查一次标签 | 21 次查询 | 能跑,但数据量一大就慢,这叫 N+1 查询 |
先查 20 条,再用 IN 一次性取标签 |
2 次查询 | 推荐 |
SELECT nt.note_id, t.name
FROM note_tags nt
JOIN tags t ON t.id = nt.tag_id
WHERE nt.note_id IN (1, 2)
ORDER BY nt.note_id, t.name;
(1, 'python')
(1, 'sqlite')
(2, 'python')
拿到这些行之后,在 Python 里按 note_id 装进一个字典,再贴回每条摘录。第 4 章的 _rows_with_tags() 做的就是这件事——把"SQL 的多行"变成"JSON 的数组"。
查询 4:每个标签下有几条(只算自己的)
SELECT t.name, COUNT(DISTINCT nt.note_id) AS cnt
FROM tags t
JOIN note_tags nt ON nt.tag_id = t.id
JOIN notes n ON n.id = nt.note_id
WHERE n.user_id = 1
GROUP BY t.id
ORDER BY cnt DESC, t.name;
('python', 2)
('sqlite', 1)
GROUP BY t.id 而不是 t.name:按 id 分组更稳,名字将来允许重名也不会混组。COUNT(DISTINCT nt.note_id) 而不是 COUNT(*):万一中间表出现重复行,COUNT(*) 会把同一条摘录数两次。
查询 5:所有标签,没用过的显示 0
SELECT t.name, COUNT(nt.note_id) AS cnt
FROM tags t
LEFT JOIN note_tags nt ON nt.tag_id = t.id
GROUP BY t.id
ORDER BY cnt DESC, t.name;
('python', 2)
('sqlite', 1)
('读书', 1)
('待整理', 0)
换成 JOIN(即 INNER JOIN),待整理 那一行会消失:
('python', 2)
('sqlite', 1)
('读书', 1)
这就是"标签管理页少了一个标签"这一类 bug 的全部原因。
查询 6:没有来源的摘录
SELECT COUNT(*) FROM notes WHERE source_url IS NULL;
(2,)
把 IS NULL 写成 = NULL,结果是 0。系统会告诉你"所有摘录都有来源",而事实相反。
这条查询故意没加 user_id,所以数的是全库(两个人的两条空来源)。真实接口里一定要带上归属条件——这也是第 7 章那个"少一个条件"的伏笔。
6.2.4 三个坑
坑一:= NULL 永远查不到东西
症状:WHERE source_url = NULL 返回 0 行,但库里明明有一堆空来源的记录。改成 != NULL 还是 0 行。
成因:NULL 表示"未知",未知 = 未知 的结果是"未知",WHERE 只保留结果为"真"的行。所以两个方向都不成立。这个规则不是 SQLite 特有,所有关系型数据库都一样。
修法:判空只用 IS NULL / IS NOT NULL。写查询时如果结果"莫名其妙是空的",第一个怀疑对象就是 NULL。
坑二:GROUP BY 里放了裸列,SQLite 不报错但结果随机
症状:这条查询在 SQLite 里跑得好好的,换到别的数据库直接报错;或者同一份数据,两次执行返回的 n.title 不一样。
SELECT t.name, n.title, COUNT(*)
FROM tags t JOIN note_tags nt ON nt.tag_id = t.id JOIN notes n ON n.id = nt.note_id
GROUP BY t.id;
('sqlite', 'SQLite 的事务', 1)
('python', 'SQLite 的事务', 2) ← 这一组的 title 是随便挑的一行
('读书', '别人的摘录', 1)
成因:python 这一组有两行,n.title 没有聚合,SQLite 允许它"取组内某一行的值"——标准里这是未定义行为,SQLite 为了兼容旧代码保留了它。MySQL 的宽松模式也类似,PostgreSQL 会直接拒绝。
修法:SELECT 里出现的每一列,要么在 GROUP BY 里,要么被聚合函数包住。要"每组取一条代表",就明确写 MAX(n.id) 配合子查询,别指望数据库替你挑。
坑三:写了 REFERENCES,外键却根本没生效
症状:删掉一个用户,他的摘录还在;插入一条 user_id = 999 的摘录,居然成功。表定义里明明写了外键。
成因:SQLite 的外键检查是连接级开关,默认关闭。PRAGMA foreign_keys = ON 只对当前连接有效,新建连接要重新开。很多人只在建表脚本里开了一次。
修法:在 connect() 里每次都执行 PRAGMA foreign_keys = ON(第 1 章的 db.py 就是这么写的)。验证方式:PRAGMA foreign_keys; 返回 1 才算开。开启后再插孤儿数据,会得到 sqlite3.IntegrityError: FOREIGN KEY constraint failed。
本章验收
- 四张表建好,
PRAGMA foreign_keys返回 1 - 六条查询全部跑通,结果与书中一致(条数、顺序都对得上)
- 能解释
note_tags为什么需要联合主键 - 能说出
INNER JOIN与LEFT JOIN在标签统计上的区别,并各跑一次 - 能解释
= NULL、IS NULL、NULL = NULL三者的结果
自测
Q1:为什么 notes 表里不直接存 tags 字符串,而要多一张 note_tags?
看答案
存字符串会把"关系"变成"文本",之后每次查询都要做字符串匹配:搜 `python` 会命中 `python3`,改标签名要全表替换,统计要解析字符串。更根本的是没法加约束——你无法让数据库保证"这条摘录不会重复挂 python"。拆成三张表之后,每条关系是一行数据,可以加索引、加唯一约束、做聚合,代价只是多写一个 JOIN。Q2:SELECT t.name, COUNT(*) FROM tags t JOIN note_tags nt ON nt.tag_id = t.id GROUP BY t.id 数出来的到底是什么?
看答案
数的是"连接之后的行数",也就是 note_tags 里匹配到的关系条数。只要 note_tags 对 (note_id, tag_id) 有唯一约束,它和"有多少条摘录挂了这个标签"数值相同;一旦中间表出现重复行,或者你再 JOIN 了第三张一对多的表,`COUNT(*)` 就会被放大。要表达"多少条不同的摘录",用 `COUNT(DISTINCT nt.note_id)`,语义写在 SQL 里,比靠约束保证更稳。Q3:为什么 UPDATE 和 DELETE 要先跑一次 SELECT COUNT(*)?
看答案
因为漏写 `WHERE` 不会报错。`UPDATE notes SET title = 'x'` 会安静地改掉所有行,`DELETE FROM notes` 会安静地清空整张表,返回的只是"影响行数"。先用同样的条件 `SELECT COUNT(*)`,你能在动手之前看到"这一下会影响几行",数字不对就停下来。这是比任何备份都快的止损手段。下一步
表建好了,但 sqlite3 这个模块还有几个必须记住的细节:连接怎么拿、结果怎么读、参数怎么传。下一章从一次真实的注入开始,让你亲眼看见"拼接字符串"能删掉整张表。