Software Systems Atlas

深色模式

第1章：关系型数据库与 SQL ​

元数据卡

维度值
难度（入门）
前置Vol 4 第1章（网络分层模型），任意编程基础
关键词数据库、SQL、DDL、DML、SELECT、JOIN、事务
代码语言SQL (主) / Java (差异窗口)

你的进度

你站在数据堡垒的门厅里。老陈递给你一本厚厚的账本，封面上写着三个字：关系型数据库。

"你一路走下来攒了不少东西。"老陈说，"代码、配置、日志、用户数据、交易记录——全都塞在随身的口袋里。CSV 文件、JSON 文件夹、内存里的 ArrayList。就像一只装满零碎的口袋。"

"口袋不够用了，对吧？"他翻开账本的第一页，上面画着一张表格——行和列，整整齐齐。

"这叫关系。不是人和人的关系——是数据之间的关系。你把所有数据放进一张张表里，每张表有固定的结构（列），每行是一条记录。然后你用一种叫 SQL 的语言跟它对话。说你要什么，不用管怎么拿。"

你翻了两页，看到几条奇怪的指令：

sql
SELECT * FROM adventurers WHERE level > 18;

"先试试再说。"老陈敲了敲桌上一块发光的石板——SQLite。"来，从建第一张表开始。"

这就是这一章要干的事：装一个数据库，建一张表，往里插数据，再用 SQL 把它查出来。你不需要理解 B+树、事务日志、查询优化——那是后面的事。今天你只需要学会用 SQL 和数据库对话。

你的任务

这一章的目标很简单：装一个数据库，建一张表，往里插数据，再用 SQL 把它查出来。你不需要理解 B+树、事务日志、查询优化——那是后面十几章的事。今天你只需要学会用 SQL 和数据库对话，就像在 bash 里和操作系统对话一样。

本章分层

  • 必读：什么是数据库、建表（CREATE TABLE）、增删改查（INSERT/SELECT/UPDATE/DELETE）、WHERE 条件筛选
  • 选读：JOIN 多表连接、GROUP BY 聚合分组
  • 进阶：子查询优化、窗口函数、执行计划分析

本章不会要求你掌握

  • B+树、LSM-Tree 等存储结构
  • 事务隔离级别
  • 数据库索引的原理与选择

破局 · 溯源

为什么不能只用文件？ ​

先看一个具体场景。假设你在管理一座数据堡垒的宝库库房，用 CSV 文件记物品清单：

csv
item_idnametypevalue
1炎之剑武器1200
2治愈药水药水80
3冰霜法杖武器1800
4隐身斗篷防具950
5火焰卷轴卷轴150

当只有一个用户读写时，一切正常。但一旦出现以下情况，文件方案就开始崩塌：

问题 1——并发写入：你和另一位管理员打开宝库目录文件。你改了炎之剑的价值，他删了隐身斗篷的记录。谁最后保存，谁的决定生效——另一个人的修改直接丢失。

问题 2——崩溃恢复：程序正在写第 1000 条记录时断电了。重新启动后发现文件只有一半数据，甚至文件头都坏了。

问题 3——查询效率：想查"所有价值高于 200 的武器类物品"，你得写一段 Java 代码，把整个文件读到内存、逐行解析、过滤、输出。20 万件时这段代码跑几分钟。

问题 4——数据一致性：你要把冰霜法杖从"武器"类移到"传说物品"类，它的价值翻倍。在文件系统里，你得写两次文件——改类型、改价值。如果第一次写完断电了，冰霜法杖就成了"传说物品但价值还是普通价"。

这些问题不是文件格式的问题，而是文件缺乏数据管理系统的体现。数据库就是专门来解决这些的：并发控制、崩溃恢复、声明式查询、事务保证。

认识 SQL ​

SQL 的全称是 Structured Query Language（结构化查询语言），但它不是一个编程语言——更像是一种声明式 DSL：你说你要什么，不说怎么拿到。

sql
-- 文件方案：你要自己写循环、排序、过滤
-- 数据库方案：你只要说你要什么
SELECT name value FROM vault_items WHERE item_type = '武器' AND value > 200;

引擎会自己决定用不用索引、走什么连接方式。你不用关心它怎么找到这些行的。

装一个数据库 ​

本卷所有示例使用 SQLite 作为入门数据库。零配置、无需服务器、文件即数据库。 生产环境会用 PostgreSQL 或 MySQL，但学习阶段 SQLite 让你专注在 SQL 本身。

bash
# 检查是否已安装
sqlite3 --version

# 如果没有，安装：
# macOS: brew install sqlite3
# Ubuntu/Debian: sudo apt install sqlite3
# Windows: 从 https://sqlite.org/download.html 下载

Java/Python 中连接 SQLite 会在章末实验部分演示。本章先用命令行操作。

CREATE — 建表 ​

启动 SQLite 并创建你的第一张表：

bash
sqlite3 fortress.db
sql
-- 数据堡垒的第一张宝库目录
CREATE TABLE vault_items (
 item_id INTEGER PRIMARY KEY
 name TEXT NOT NULL
 item_type TEXT NOT NULL
 value INTEGER NOT NULL
 discovered_date TEXT
);

-- 查看创建的表
.tables

SQLite 是动态类型的，但生产数据库（PostgreSQL、MySQL）在列上强制类型。建议始终写严格类型，养成好习惯。

数据类型都是什么意思？INTEGER 是整数，TEXT 是字符串，REAL 是浮点数。NOT NULL 的意思是这一列不能为空。

SQL 不区分大小写，但惯例是关键词大写（SELECT、FROM、WHERE），表名和列名小写。这不是规则，纯粹是方便阅读。

INSERT — 插入数据 ​

sql
INSERT INTO vault_items VALUES (1 '炎之剑' '武器' 1200 '2024-01-15');
INSERT INTO vault_items VALUES (2 '治愈药水' '药水' 80 '2024-03-01');
INSERT INTO vault_items VALUES (3 '冰霜法杖' '武器' 1800 '2024-02-20');
INSERT INTO vault_items VALUES (4 '隐身斗篷' '防具' 950 '2024-05-10');
INSERT INTO vault_items (item_id name item_type value discovered_date)
VALUES (5 '火焰卷轴' '卷轴' 150 '2023-11-01');

-- 更规范：指定列名
-- 这样即使表结构变了，INSERT 也不会错位
INSERT INTO vault_items (item_id name item_type value discovered_date)
VALUES (6 '秘银铠甲' '防具' 2200 '2024-06-01');

为什么指定列名更好？ 因为如果以后给表加了列（比如 email），不指定列名的 INSERT 就会报错。

SELECT — 查询数据 ​

sql
-- 全部数据
SELECT * FROM vault_items;

-- 只查特定列
SELECT name value FROM vault_items;

-- 带条件（WHERE）
SELECT name value FROM vault_items
WHERE item_type = '武器';

-- 多条件
SELECT name value FROM vault_items
WHERE item_type = '武器' AND value > 1000;

-- 排序
SELECT name value FROM vault_items
ORDER BY value DESC;

-- 限制数量
SELECT name value FROM vault_items
ORDER BY value DESC
LIMIT 3;

-- 模糊匹配
SELECT name FROM vault_items
WHERE name LIKE '%剑%';

WHERE 子句支持 AND、OR、NOT、IN、BETWEEN、LIKE、IS NULL 等：

sql
SELECT * FROM vault_items
WHERE value BETWEEN 100 AND 1000;

SELECT * FROM vault_items
WHERE item_type IN ('武器' '防具');

SELECT * FROM vault_items
WHERE item_type IS NULL;

UPDATE — 修改数据 ​

sql
-- 给所有武器类物品涨价 10%
UPDATE vault_items
SET value = value * 1.1
WHERE item_type = '武器';

-- 查看结果
SELECT name item_type value FROM vault_items
WHERE item_type = '武器';

忘记 WHERE 的后果：

sql
UPDATE vault_items SET value = 0; -- 所有物品价值变 0！

在 SQLite 里可以用 .selftest 提前检查，生产环境建议在 UPDATE 前先 SELECT 确认范围。

DELETE — 删除数据 ​

sql
-- 删除特定物品
DELETE FROM vault_items WHERE item_id = 4;

-- 删除所有药水类物品
DELETE FROM vault_items WHERE item_type = '药水';

-- 不带 WHERE = 清空表
-- DELETE FROM vault_items;

DELETE vs DROP 的区别：DELETE 删行，DROP 删整张表（包括表结构）。DROP TABLE vault_items; 之后整个宝库目录就没了。

JOIN — 连接多张表 ​

单表查询能解决很多问题，但真实系统很少只有一张表。想想看：如果你把冒险者的信息（名字、等级、公会）也塞进 vault_items 表里，每次有冒险者领走任务就要重复登记——数据冗余、容易不一致。

所以数据库设计里有一个核心原则：数据只存一份，通过关联来组合。

sql
-- 建第二张表：冒险者登记簿
CREATE TABLE adventurers (
 adventurer_id INTEGER PRIMARY KEY
 name TEXT NOT NULL
 class TEXT NOT NULL
 level INTEGER DEFAULT 1
 guild TEXT
);

INSERT INTO adventurers VALUES (1 '艾琳' '战士' 15 '龙鳞公会');
INSERT INTO adventurers VALUES (2 '马尔科' '法师' 22 '星辉议会');

-- 再建第三张表：任务记录
CREATE TABLE quests (
 quest_id INTEGER PRIMARY KEY
 adventurer_id INTEGER
 item_id INTEGER
 status TEXT DEFAULT 'active'
 reward INTEGER
);

INSERT INTO quests VALUES (1 1 3 'completed' 500);
INSERT INTO quests VALUES (2 2 1 'active' 300);
INSERT INTO quests VALUES (3 1 5 'active' 150);

-- JOIN 三张表：查每个冒险者领了什么任务、任务目标是什么物品
SELECT a.name AS adventurer q.status v.name AS target_item
FROM adventurers a
JOIN quests q ON a.adventurer_id = q.adventurer_id
JOIN vault_items v ON q.item_id = v.item_id;

看明白了吗？quests.adventurer_id 和 adventurers.adventurer_id 是连接条件。数据库用这个条件把两张表的行配对起来。

JOIN 的几种类型：

类型含义用在哪
INNER JOIN只返回两表都匹配的行最常见的连接
LEFT JOIN左表全返回，右表不匹配时补 NULL查"没有领过任务的冒险者"
RIGHT JOIN跟 LEFT JOIN 相反SQLite 不支持，用 LEFT 调换顺序代替
FULL JOIN两表所有行都返回合并数据集
sql
-- LEFT JOIN 示例：查出所有冒险者及其任务
-- 如果某个冒险者没领过任务，q.status 显示 NULL
SELECT a.name q.status v.name AS item
FROM adventurers a
LEFT JOIN quests q ON a.adventurer_id = q.adventurer_id
LEFT JOIN vault_items v ON q.item_id = v.item_id;

聚合与分组 ​

想统计"工程部有多少人、平均工资多少"？不用写循环，SQL 给你聚合函数：

sql
SELECT
 item_type
 COUNT(*) AS 数量
 AVG(value) AS 平均价值
 MAX(value) AS 最高价值
 MIN(value) AS 最低价值
FROM vault_items
GROUP BY item_type;

GROUP BY 把数据按物品类型分组，然后每组上执行聚合函数。

HAVING 是对分组结果的过滤——类似 WHERE 但作用于分组之后：

sql
SELECT item_type AVG(value) AS avg_value
FROM vault_items
GROUP BY item_type
HAVING avg_value > 500;

为什么不能用 WHERE AVG(value) > 500？因为 WHERE 在 GROUP BY 之前执行。HAVING 在 GROUP BY 之后执行，是用来过滤聚合结果的。

深入冒险

子查询 ​

子查询就是 SQL 里的嵌套查询——一个 SELECT 的结果作为另一个 SELECT 的输入。

sql
-- 查出价值高于平均值的物品
SELECT name value
FROM vault_items
WHERE value > (SELECT AVG(value) FROM vault_items);

有趣的地方是子查询可以出现在 SELECT 的各个位置：

sql
-- FROM 子句里
SELECT type_stats.item_type type_stats.avg_value
FROM (
 SELECT item_type AVG(value) AS avg_value
 FROM vault_items
 GROUP BY item_type
) type_stats
WHERE type_stats.avg_value > 500;

-- SELECT 子句里（标量子查询）
SELECT name value
 (SELECT AVG(value) FROM vault_items) AS vault_avg
FROM vault_items;

创建索引 ​

当表变大后，全表扫描变慢。这时你需要索引，原理类似书的目录：

sql
CREATE INDEX idx_vault_items_type ON vault_items(item_type);

加了索引后，WHERE item_type = '武器' 就不再扫整张表了。但注意索引不是免费的——每次写入都要更新索引。

常见陷阱

陷阱 1：SELECT * 是万恶之源

生产代码里 SELECT * 会导致：

  • 返回不需要的列，浪费网络带宽
  • 无法利用覆盖索引
  • 表结构变化后结果集的列顺序可能变化

开发阶段快速查看用 SELECT * 没问题，但应用代码里永远指定列名。

陷阱 2：NULL 的诡异行为

sql
-- 假设 value 列允许 NULL
SELECT * FROM vault_items WHERE value = NULL; -- 永远返回 0 行
SELECT * FROM vault_items WHERE value IS NULL; -- 正确

NULL 不等于任何值，包括另一个 NULL。只能用 IS NULL / IS NOT NULL。

陷阱 3：别名的 SQLite 限制

sql
SELECT value * 1.1 AS taxed_value
FROM vault_items
WHERE taxed_value > 1000; -- SQLite 不支持 WHERE 里用列别名

WHERE 子句里不能直接用 SELECT 中的别名。正确做法是：SELECT value * 1.1 AS taxed_value FROM vault_items WHERE value * 1.1 > 1000; 或者用子查询。

通关挑战

** 热身（5 分钟）**

用 SQLite 完成以下操作：

  1. 创建一张 armory_items 表（item_id、name、power、category、stock）
  2. 插入 5 条商品数据
  3. 查询价格高于 50 的商品
  4. 按类别统计商品数量和平均价格
参考解答
sql
CREATE TABLE armory_items (
 item_id INTEGER PRIMARY KEY
 name TEXT NOT NULL
 power INTEGER NOT NULL
 category TEXT NOT NULL
 stock INTEGER DEFAULT 0
);

INSERT INTO armory_items VALUES (1 '铁剑' 15 '武器' 10);
INSERT INTO armory_items VALUES (2 '木盾' 8 '防具' 20);
INSERT INTO armory_items VALUES (3 '治疗法杖' 25 '法器' 5);
INSERT INTO armory_items VALUES (4 '皮靴' 5 '防具' 30);
INSERT INTO armory_items VALUES (5 '火焰卷轴' 40 '消耗品' 8);

SELECT name power FROM armory_items WHERE power > 10;

SELECT category COUNT(*) AS count AVG(power) AS avg_power
FROM armory_items
GROUP BY category;

** 挑战（30 分钟）**

从上一题的数据出发：

  1. 创建 delivery_logs 表（log_id、item_id、quantity、delivery_date、recipient）
  2. 插入 8-10 条交付记录
  3. 用 JOIN 查出每次交付的物品名称和单件威力
  4. 统计每个冒险者领取的总威力，按威力值降序排列
提示

你需要的 SQL 技能：

  • JOIN 连接 armory_items 和 delivery_logs
  • GROUP BY 按 recipient 分组
  • SUM(power * quantity) 计算总威力
  • ORDER BY ... DESC 排序

** 观察（15 分钟）**

sql
-- 打开 SQLite 的执行计划
.expert
EXPLAIN QUERY PLAN
SELECT a.name q.status
FROM adventurers a
JOIN quests q ON a.adventurer_id = q.adventurer_id;

观察有无索引时执行计划的差异。

验收标准

到这一步你应该能：

  • 用 CREATE TABLE 定义一张表，解释每列的含义
  • 用 INSERT 插入数据，写出有意义的 WHERE 筛选条件
  • 用 UPDATE 和 DELETE 修改删除数据，知道不带 WHERE 的后果
  • 用 INNER JOIN 连接两张表
  • 用 GROUP BY + 聚合函数做统计查询
  • 解释为什么数据库比文件方案更适合管理结构化数据

常见卡点

卡点原因解决
"表建好了提示 no such table"忘了先用 .open 或 .tables 确认数据库文件SQLite 会创建新文件，但表却在另一个数据库文件里
"JOIN 结果比预期多"连接条件写错导致笛卡尔积检查 ON 子句的条件是否正确——常见错误是 ON a.adventurer_id = q.adventurer_id 写反
"GROUP BY 报错"SELECT 中有不在 GROUP BY 里的列如果 SELECT item_type name AVG(value) 但只 GROUP BY item_type，那么 name 是不确定的。要么加进 GROUP BY，要么用聚合函数包起来
"大小写不敏感时查询不到"SQLite 默认 LIKE 不区分大小写，但 'Smith' 和 'smith' 在 = 比较时区分用 LOWER() 或 UPPER() 统一大小写，或者用不区分大小写的 COLLATE

现在不需要理解

  • B+树：索引为什么快？数据库内部用什么结构存数据？那是第 4 章的深度内容
  • 事务日志：崩溃后数据库怎么恢复的？第 4 章的 WAL（Write-Ahead Logging）会讲
  • 查询优化器：你的 JOIN 查询为什么走了某个执行计划？第 8 章详细讲
  • MVCC：为什么很多人读写时数据不会乱？第 12 章 MVCC 部分

旅人笔记

数据库就是"专门管数据的操作系统"。SQL 是你和它对话的语言——你说要什么，不用管怎么拿到。一张表、一条 SELECT、一个 JOIN——看着简单，背后是几十年的工程智慧。

→ 下一站预告 ​

Built with VitePress | Software Systems Atlas

Copyright 2026 Steven Yang