跳到正文
零到全栈 · 卷 6 · 数据与状态

表与 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 主键、自增 数字比邮箱更适合做引用目标,改邮箱不影响关联
email 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 再说"快得多:

  1. 要哪几列:列表页只要 id, title, created_at,详情页才要 content;
  2. 数据在哪几张表:只涉及 notes 就是单表;要标签名就必然三张表;
  3. 怎么过滤、怎么排序、要不要分组:过滤写 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 这个模块还有几个必须记住的细节:连接怎么拿、结果怎么读、参数怎么传。下一章从一次真实的注入开始,让你亲眼看见"拼接字符串"能删掉整张表。

→ 卷6-03-SQLite实战与注入.md