3.2 数据库的构建
知识点
1.为什么要用数据库
用表格文件（如 Excel）管数据，规模一大就出问题：
| 问题 | 说明 |
|---|---|
| 数据冗余 | 同一个学生信息在几十张表里重复填写 |
| 数据不一致 | 一处改了，别处没改，同一事实出现两个版本 |
| 难以共享 | 文件传给别人，谁改的、哪个是最新都说不清 |
| 难以并发 | 两人同时改，后保存的覆盖前面的 |
| 难以查询 | 数据量大时靠筛选、透视，效率极低 |
| 不安全 | 谁拿到文件就能看全部内容 |
数据库解决的正是这六个问题：集中存储、统一管理、减少冗余、支持并发、高效查询、权限控制。
2.三个必须分清的概念
| 概念 | 英文 | 含义 | 类比 |
|---|---|---|---|
| 数据 | Data | 实际存储的记录 | 仓库里的货物 |
| 数据库 | DB | 按一定结构组织、长期存放在计算机内的数据集合 | 仓库 |
| 数据库管理系统 | DBMS | 管理数据库的软件，负责建库、查询、更新、权限、并发、恢复 | 仓库管理员 |
| 数据库系统 | DBS | 数据库＋DBMS＋应用程序＋人＋硬件 | 整个物流体系 |
关系（必考）：数据库系统包含数据库和数据库管理系统；用户通过 DBMS 操作数据库，不直接碰数据文件。
常见 DBMS：
| 类型 | 代表产品 | 特点 |
|---|---|---|
| 大型商用 | Oracle、SQL Server、DB2 | 功能强、成本高，适合企业 |
| 开源 | MySQL、PostgreSQL | 免费、生态好，Web 系统主流 |
| 轻量嵌入式 | SQLite | 单文件、零配置，适合小型系统与教学 |
| 桌面型 | Access | 图形界面、易上手，适合教学入门 |
3.数据模型：数据怎么"组织"
| 模型 | 结构 | 特点 | 现状 |
|---|---|---|---|
| 层次模型 | 树形 | 一对多，结构僵化 | 已少用 |
| 网状模型 | 图状 | 多对多，复杂难维护 | 已少用 |
| 关系模型 | 二维表 | 简单、直观、有数学基础 | 绝对主流 |
| 面向对象模型 | 对象 | 适合复杂数据类型 | 特定领域 |
关系模型的核心思想：用二维表表示实体与实体之间的联系，"关系"就是"表"。
4.关系模型的术语（必背）
以学生表为例：
| 学号 | 姓名 | 性别 | 出生日期 | 班级 |
|---|---|---|---|---|
| 20240101 | 张明 | 男 | 2008-05-12 | 计算机1班 |
| 20240102 | 李红 | 女 | 2008-09-03 | 计算机1班 |
| 术语 | 含义 | 对应 |
|---|---|---|
| 关系 | 一张二维表 | 整张学生表 |
| 元组（记录／行） | 表中的一行 | 张明那一行 |
| 属性（字段／列） | 表中的一列 | "姓名"这一列 |
| 域 | 某列的取值范围 | 性别只能取"男"或"女" |
| 关系模式 | 表的结构定义 | 学生（学号，姓名，性别，出生日期，班级） |
关系的三条性质（考判断题）：
- 每列不可再分（同一列不能又拆成两列）；
- 行、列的顺序无关（先哪行后哪行不影响结果）；
- 不能有完全相同的两行（靠主键保证）。
5.主键与外键：表的"身份证"与"纽带"
主键（Primary Key）：能唯一标识一条记录的字段（或字段组合）。
主键的三个要求：
- 唯一——不能有重复值；
- 非空——不能为
NULL； - 稳定——最好不随业务变化（所以常用"学号"而不是"姓名"）。
候选键：能当主键的字段可能有多个（学号、身份证号都可唯一标识学生），选定其中一个作主键，其余为候选键。
外键（Foreign Key）：一张表中引用另一张表主键的字段，用来建立表间联系。
例：借阅表中的"学号"就是外键，它引用学生表的"学号"主键。
| 表 | 主键 | 外键 | 说明 |
|---|---|---|---|
| 学生表 | 学号 | 班级编号 → 班级表 | 一个学生属于一个班 |
| 图书表 | 图书编号 | — | — |
| 借阅表 | 借阅编号 | 学号、图书编号 | 一条借阅记录关联一个学生、一本书 |
为什么要外键：保证"借阅表里的学号，在学生表里一定存在"——防止出现"给不存在的学生借书"这种脏数据。
6.常用数据类型
| 类型 | 用途 | 说明 |
|---|---|---|
INT／INTEGER | 整数 | 学号、数量、编号 |
DECIMAL(p,s) | 定点小数 | 金额（DECIMAL(8,2) 表示共 8 位、2 位小数） |
FLOAT／DOUBLE | 浮点数 | 温度、比例（有精度误差，不建议存金额） |
CHAR(n) | 定长字符串 | 性别、固定长度编码 |
VARCHAR(n) | 变长字符串 | 姓名、书名、地址 |
DATE | 日期 | 2026-11-07 |
DATETIME | 日期时间 | 2026-11-07 21:30:00 |
TEXT | 长文本 | 备注、文章内容 |
BOOLEAN | 布尔值 | 是否归还 |
选型三条经验：
- 金额用
DECIMAL，不用FLOAT（浮点有精度误差，0.1+0.2 不等于 0.3）； - 长度不固定的用
VARCHAR，定长的用CHAR（如身份证号 18 位）； - 能用小类型不用大类型（如年龄用
TINYINT而非BIGINT），节省空间、提升速度。
7.从 E-R 图到关系模式（关键转换）
转换规则（三条，必背）：
E-R 元素 | 转换结果 |
|---|---|
| 实体 | 转成一张表，属性变字段，选定主键 |
| 1:1 联系 | 可以合并到任一方表中（加对方主键作外键） |
| 1:n 联系 | 在"多"的一方加"一"方的主键作外键 |
| m:n 联系 | 必须单独建一张中间表，两个主键作联合主键 |
例：学生（m）—借阅—（n）图书
- 实体"学生"→ 学生表（学号，姓名，性别，班级）
- 实体"图书"→ 图书表（图书编号，书名，作者，在架数）
- 联系"借阅"是多对多 → 借阅表（借阅编号，学号，图书编号，借出日期，应还日期，归还日期）
为什么多对多必须建中间表：一个学生借多本书、一本书被多个学生借过，无论把外键放在哪一边，都只能存"一个"关系，必须另开一张表来存"这一对"。
这是本节最常考、也最容易做错的一步，务必让学生动手画一遍。
8.SQL：操作数据库的语言
SQL（结构化查询语言） 是关系数据库的标准语言，按功能分四类：
| 类别 | 全称 | 主要语句 | 作用 |
|---|---|---|---|
DDL | 数据定义语言 | CREATE、ALTER、DROP | 建库、建表、改结构、删表 |
DML | 数据操纵语言 | INSERT、UPDATE、DELETE | 增、改、删数据 |
DQL | 数据查询语言 | SELECT | 查数据（用得最多） |
DCL | 数据控制语言 | GRANT、REVOKE | 授权与回收权限 |
记忆要点：DDL 动"结构"，DML 动"数据"，DQL 只"看"，DCL 管"权限"。
9.建库与建表
（1）建库并使用
CREATE DATABASE library;
USE library;
（2）建表
CREATE TABLE student (
stu_id CHAR(8) PRIMARY KEY, -- 学号，主键
name VARCHAR(20) NOT NULL, -- 姓名，不能为空
gender CHAR(2), -- 性别
birthday DATE, -- 出生日期
class VARCHAR(20) -- 班级
);
CREATE TABLE book (
book_id CHAR(6) PRIMARY KEY,
title VARCHAR(60) NOT NULL,
author VARCHAR(30),
total INT DEFAULT 1, -- 总册数
in_shelf INT DEFAULT 1 -- 在架数
);
CREATE TABLE borrow (
borrow_id INT PRIMARY KEY AUTO_INCREMENT,
stu_id CHAR(8) NOT NULL,
book_id CHAR(6) NOT NULL,
out_date DATE NOT NULL,
due_date DATE NOT NULL,
back_date DATE,
FOREIGN KEY (stu_id) REFERENCES student(stu_id),
FOREIGN KEY (book_id) REFERENCES book(book_id)
);
建表时的五个常用约束：
| 约束 | 作用 |
|---|---|
PRIMARY KEY | 主键，唯一且非空 |
NOT NULL | 该列必须有值 |
UNIQUE | 该列值不能重复（如身份证号） |
DEFAULT | 默认值（如 total DEFAULT 1） |
FOREIGN KEY | 外键，引用另一表主键 |
AUTO_INCREMENT | 自动递增（SQLite 用 AUTOINCREMENT） |
10.增删改查（本节核心，必须会写）
（1）插入数据 INSERT
INSERT INTO student (stu_id, name, gender, birthday, class)
VALUES ('20240101', '张明', '男', '2008-05-12', '计算机1班');
要点：字段顺序与值的顺序必须一一对应；字符串与日期要用单引号。
（2）查询数据 SELECT
-- 查全部
SELECT * FROM student;
-- 查指定列＋条件
SELECT stu_id, name FROM student WHERE class = '计算机1班';
-- 模糊查询（% 代表任意多个字符）
SELECT title, author FROM book WHERE title LIKE '%Python%';
-- 排序（DESC 降序，ASC 升序默认）
SELECT title, in_shelf FROM book ORDER BY in_shelf DESC;
-- 去重
SELECT DISTINCT class FROM student;
-- 统计与分组
SELECT class, COUNT(*) AS 人数 FROM student GROUP BY class;
-- 分组后筛选（WHERE 之后、GROUP BY 之前不能用聚合函数，要用 HAVING）
SELECT class, COUNT(*) AS 人数 FROM student
GROUP BY class HAVING COUNT(*) > 40;
-- 多表连接查询（借阅记录＋书名＋姓名）
SELECT b.title, s.name, r.out_date
FROM borrow r
JOIN student s ON r.stu_id = s.stu_id
JOIN book b ON r.book_id = b.book_id
WHERE r.back_date IS NULL;
SELECT 的书写顺序（背下来）：
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
五个聚合函数：COUNT（计数）、SUM（求和）、AVG（平均）、MAX（最大）、MIN（最小）。
WHERE 与 HAVING 的区别（高频易错）：
WHERE在分组前过滤行，不能用聚合函数；HAVING在分组后过滤组，可以用聚合函数。
（3）更新数据 UPDATE
UPDATE book SET in_shelf = in_shelf - 1 WHERE book_id = 'B00001';
警告：UPDATE 不写 WHERE 会改全表！这是最危险的误操作，务必养成"先 SELECT 验证、再 UPDATE"的习惯。
（4）删除数据 DELETE
DELETE FROM borrow WHERE back_date IS NOT NULL AND due_date < '2024-01-01';
DELETE 与 DROP 的区别（必考）：
| 语句 | 删除什么 | 能否恢复 |
|---|---|---|
DELETE | 删除表中的行（数据） | 可从备份恢复，表结构还在 |
DROP TABLE | 删除整张表（结构＋数据） | 表不存在了 |
TRUNCATE | 清空所有行，保留表结构 | 不可回滚（比 DELETE 快） |
11.数据完整性：让脏数据进不来
| 完整性 | 约束什么 | 手段 |
|---|---|---|
| 实体完整性 | 每条记录唯一可标识 | 主键非空且唯一 |
| 参照完整性 | 外键值必须存在于被引用表 | 外键约束 |
| 域完整性 | 字段值在合法范围内 | 数据类型、CHECK、NOT NULL、DEFAULT |
| 用户定义完整性 | 业务规则 | 如"在架数不得超过总册数" |
为什么要约束：数据库层面拦住的错误，比程序里逐个判断更可靠。程序可能有十个入口，约束只有一处。
删除时的外键问题：如果学生表里有"张明"，借阅表里也有他的记录，直接删学生会失败（参照完整性被破坏）。正确做法是先处理关联记录，或设置级联删除（ON DELETE CASCADE，慎用）。
12.三大范式：表怎么设计才"合理"
| 范式 | 要求 | 违反的例子 | 后果 |
|---|---|---|---|
| 第一范式（1NF） | 每个字段不可再分 | "联系方式"里塞了电话和邮箱 | 无法按邮箱查询 |
| 第二范式（2NF） | 满足 1NF，且非主键字段完全依赖于主键 | 借阅表里存了"书名"，而书名只依赖图书编号 | 一本书改名要改几十条记录 |
| 第三范式（3NF） | 满足 2NF，且非主键字段之间无依赖 | 学生表里存"班级名称"和"班主任"，班主任依赖班级 | 换班主任要改全班记录 |
通俗理解：
- 1NF：一格只放一个值；
- 2NF：一张表只说一件事（别把"书的信息"混进"借阅记录"）；
- 3NF：别让非主键字段互相"带出来"（班主任跟着班级走，就该放班级表）。
范式的代价：范式越高，表越多，查询要连接的表也越多。实际项目中常做"适度反范式"——为了查询性能，允许少量冗余（如订单表里存一份"下单时的商品价格"，因为价格会变，历史订单必须留当时的价）。
考试口径：题目问"设计是否合理、如何改进"，按 1NF→2NF→3NF 逐条检查即可。
13.索引：让查询变快
索引是为加快检索而建立的数据结构（类似书的目录）。
| 项 | 说明 |
|---|---|
| 作用 | 加快 WHERE、ORDER BY、JOIN 的检索速度 |
| 代价 | 占存储空间；增删改时要维护索引，会变慢 |
| 适用 | 经常作为查询条件的列（学号、日期、外键） |
| 不适用 | 值很少的列（如性别只有两三种值） |
CREATE INDEX idx_borrow_stu ON borrow(stu_id);
建索引的经验：主键自动有索引；外键和常用查询条件建索引；不要给每一列都建。
14.建库的实操顺序（课程项目可用）
| 步骤 | 做什么 |
|---|---|
| 1 | 分析需求，画出 E-R 图 |
| 2 | 按转换规则写出关系模式，标出主键与外键 |
| 3 | 检查是否满足 1NF～3NF，必要时调整 |
| 4 | 确定字段类型、长度、约束、默认值 |
| 5 | 写 CREATE TABLE 建表 |
| 6 | 录入少量测试数据（INSERT） |
| 7 | 写几条查询验证（SELECT），检查能否查对 |
| 8 | 补建索引，测试大数据量下的性能 |
| 9 | 导出建库脚本，作为交付文档的一部分 |
"先画图、再建表"是铁律——直接打开工具就开始建表，几乎一定会在中途推倒重来。
15.速查表
| 序号 | 考点 | 结论 |
|---|---|---|
| 1 | 数据库解决的核心问题 | 冗余、不一致、共享、并发、查询、安全 |
| 2 | DB／DBMS／DBS | DBS 包含 DB 与 DBMS |
| 3 | 关系模型的结构 | 二维表 |
| 4 | 元组／属性 | 行／列 |
| 5 | 主键三要求 | 唯一、非空、稳定 |
| 6 | 外键作用 | 建立表间联系，保证参照完整性 |
| 7 | 金额用什么类型 | DECIMAL（不是 FLOAT） |
| 8 | 定长／变长字符串 | CHAR／VARCHAR |
| 9 | 1:n 转换 | 在"多"方加外键 |
| 10 | m:n 转换 | 必须建中间表 |
| 11 | DDL 语句 | CREATE、ALTER、DROP |
| 12 | DML 语句 | INSERT、UPDATE、DELETE |
| 13 | SELECT 子句顺序 | SELECT→FROM→WHERE→GROUP BY→HAVING→ORDER BY |
| 14 | 五个聚合函数 | COUNT、SUM、AVG、MAX、MIN |
| 15 | WHERE 与 HAVING | WHERE 分组前过滤行，HAVING 分组后过滤组 |
| 16 | 模糊查询 | LIKE '%关键字%' |
| 17 | DELETE 与 DROP | 前者删行，后者删表 |
| 18 | 最危险的误操作 | UPDATE／DELETE 不写 WHERE |
| 19 | 三类完整性 | 实体、参照、域 |
| 20 | 三大范式 | 1NF 不可再分、2NF 完全依赖、3NF 无传递依赖 |
| 21 | 索引代价 | 占空间、拖慢增删改 |
| 22 | 建库第一步 | 画 E-R 图，不是打开工具 |
16.课堂提示（讲课参考）
- 先用 Excel 制造"痛"：给一份 200 行的借阅登记表，里面故意有重复学生、错别字姓名、同一本书两个书名。让学生统计"借了几本"，他们会崩溃——这时再讲数据库解决的六个问题，动机就成立了。
E-R转换要动手做，不能只看：给出"学生—课程—成绩"三张图，让学生在纸上写出三张关系模式并标出主键外键。多对多必须建中间表这一点，必须让学生自己写错一次再纠正，否则记不住。SQL教学建议"先抄后改"：给学生 10 条现成语句，只改WHERE条件看结果变化，比从零写更有效。重点反复练SELECT，INSERT／UPDATE／DELETE各练一条即可。UPDATE不带WHERE的后果要演示：在测试库里故意执行一次，全表数据被改，然后从备份恢复。这个"惊险时刻"能让学生记一辈子。- 三大范式用"一张错表"讲最有效：给一张"借阅表"里塞了书名、作者、出版社、班主任的表，让学生找问题（一本书改名要改几十行、换班主任要改全班），再一步步拆成三张表。拆完再问"拆了之后查询变麻烦了，怎么办"——引出反范式与连接查询，认知就完整了。