数据库基础（关系模型与范式）
关系模型里在说什么
关系型数据库把数据看成 二维表：一行是一条 元组，一列是一个 属性，列的取值范围叫 域。
表之间靠 主键 / 外键 表达关联；查询多用关系代数思路（选择、投影、连接）描述，再由 SQL 实现。
键与约束（设计前先想清楚）
| 概念 | 含义 |
|---|---|
| 候选键 | 能唯一标识一行、且去掉任一列就不再唯一的属性集合 |
| 主键 | 选中的一个候选键，表中唯一且通常非空 |
| 外键 | 引用另一张表主键（或唯一键）的列，用来维护参照完整性 |
| 超键 | 能唯一标识一行的属性集合（可能有多余列） |
工程上：主键尽量稳定（少用业务含义易变的字段）；外键约束是否在库里强制执行，要看性能与演进策略，但 语义上应始终清晰。
函数依赖（范式讨论的根基）
若在整张表里，属性列 (Y) 的值可以由属性集合 (X) 唯一决定，记作 (X \rightarrow Y)。
范式就是在问：这张表里有没有不该绑在一起的属性，从而减少冗余与异常。
第一范式（1NF）
每个单元格是不可再分的原子值（不能有「表里套表」、重复组、逗号分隔的多值塞进一格）。
不满足 1NF 时，查询和索引都很难规范化。
第二范式（2NF）
前提：满足 1NF，且存在 候选键（可能是复合主键）。
消除「部分函数依赖」：非主属性不能只依赖候选键的 一部分，而要依赖 整个候选键。
典型反例：(学号, 课程号) → 成绩，同时又 (课程号) → 课程名。课程名只依赖候选键的一部分，应拆表。
第三范式（3NF）
前提：满足 2NF。
消除「传递依赖」：非主属性不能只依赖于 另一个非主属性，而应直接或仅通过候选键决定。
典型反例：学生 → 院系，院系 → 系主任。系主任传递依赖于学生，应把院系信息拆到单独的院系表。
直观理解：表里每一列都应直接描述「这一行所代表的主体」，而不是描述别的列。
Boyce-Codd 范式（BCNF，常称「修正的 3NF」）
比 3NF 更严：每一个决定因素（左边的 (X)）都必须是候选键。
有些特殊情况：满足 3NF 但仍可能存在冗余时，会继续规范化到 BCNF。
范式之外的现实：何时愿意「不那么范式」
规范化降低冗余，但往往 增加表数量与 JOIN。常见折中：
- 适度反范式：为了读性能冗余少量字段（要有刷新策略或接受短暂不一致）。
- 维度建模 / 宽表：分析场景（OLAP）与事务场景（OLTP）目标不同，不必用同一套范式标准硬套。
- 约束下沉：有的在应用层保证一致性，但要意识到失去数据库强制手段后的测试与运维成本。
小结
| 范式 | 一句话 |
|---|---|
| 1NF | 字段原子化 |
| 2NF | 复合键下无「部分依赖」 |
| 3NF | 无「传递依赖」 |
| BCNF | 决定因素必是候选键 |
把范式当成 诊断冗余与更新异常的 checklist，再结合访问量、一致性要求和演进节奏做工程取舍。
ER 建模基础
设计表结构前先画 ER 图（实体-关系图），明确实体、属性与关系：
实体（Entity） → 一张表，如 User、Order
属性（Attribute）→ 列，如 name、created_at
关系（Relation） → 表与表的关联方式
基数（Cardinality）
| 关系类型 | 说明 | 建表方式 |
|---|---|---|
| 一对一（1:1） | 一个用户有一份档案 | 外键放任一方，或合并为一张表 |
| 一对多（1:N） | 一个用户有多个订单 | 外键放「多」的那边（Order.user_id） |
| 多对多（M:N） | 学生 ↔ 课程 | 引入中间表（选课表），各持一方外键 |
弱实体
依赖强实体才有意义的实体（如订单明细依赖订单），其主键需包含强实体的主键。
完整性约束
数据库层面强制的数据质量规则：
| 约束 | 说明 | 示例 |
|---|---|---|
NOT NULL | 列不可为空 | name VARCHAR(100) NOT NULL |
UNIQUE | 列值在表内唯一（允许 NULL） | email VARCHAR(255) UNIQUE |
PRIMARY KEY | 主键 = NOT NULL + UNIQUE | id BIGINT PRIMARY KEY |
FOREIGN KEY | 参照完整性，引用外表主键/唯一键 | FOREIGN KEY (user_id) REFERENCES user(id) |
CHECK | 值必须满足表达式 | CHECK (age >= 0 AND age <= 150) |
DEFAULT | 插入时未给值的默认值 | status VARCHAR(20) DEFAULT 'active' |
外键级联选项
| 选项 | ON DELETE / ON UPDATE 行为 |
|---|---|
CASCADE | 父行删除/更新，子行同步删除/更新 |
SET NULL | 父行删除，子行外键置为 NULL |
RESTRICT | 有子行时拒绝删除/更新父行（默认行为） |
NO ACTION | 与 RESTRICT 类似，延迟检查 |
工程上：OLTP 系统慎用 CASCADE，删除行为应由应用层明确控制，避免意外级联删除大量数据。
SQL 语言分类
| 分类 | 全称 | 代表语句 | 说明 |
|---|---|---|---|
| DDL | Data Definition Language | CREATE / ALTER / DROP / TRUNCATE | 定义/修改表结构，自动提交 |
| DML | Data Manipulation Language | INSERT / UPDATE / DELETE / SELECT | 操作数据，受事务控制 |
| DCL | Data Control Language | GRANT / REVOKE | 权限管理 |
| TCL | Transaction Control Language | BEGIN / COMMIT / ROLLBACK / SAVEPOINT | 事务控制 |
TRUNCATE是 DDL（不可回滚），DELETE是 DML（可回滚），两者都能清空表，但行为根本不同。
关系代数与 SQL 对应
SQL 背后的理论基础是关系代数，理解对应关系有助于写出正确高效的查询：
| 关系代数操作 | SQL 对应 | 说明 |
|---|---|---|
| 选择（σ） | WHERE | 从表中筛选满足条件的行 |
| 投影（π） | SELECT col1, col2 | 只取指定列（去重时加 DISTINCT） |
| 连接（⋈） | JOIN | 合并两张表的行，INNER/LEFT/RIGHT/FULL |
| 笛卡尔积（×） | FROM A, B（无 WHERE 条件） | 所有行的组合，通常是 BUG |
| 并（∪） | UNION | 合并两个结果集，去重 |
| 差（-） | EXCEPT / NOT IN | 在 A 中不在 B 中的行 |
| 交（∩） | INTERSECT / IN | 同时在 A 和 B 中的行 |
JOIN 类型速记
INNER JOIN — 两表都有才返回
LEFT JOIN — 左表全返，右表无匹配则 NULL
RIGHT JOIN — 右表全返，左表无匹配则 NULL
FULL JOIN — 两边都全返（MySQL 不直接支持，用 UNION 模拟）
CROSS JOIN — 笛卡尔积，慎用
事务与 ACID
事务：一组操作，要么全部成功提交，要么全部失败回滚，保证数据从一个一致状态转移到另一个一致状态。
| 特性 | 英文 | 含义 |
|---|---|---|
| 原子性 | Atomicity | 事务中的操作不可分割，全成功或全回滚 |
| 一致性 | Consistency | 事务前后，数据满足所有定义的约束和业务规则 |
| 隔离性 | Isolation | 并发事务互不干扰，隔离级别决定可见程度 |
| 持久性 | Durability | 提交后，即使系统崩溃数据也不丢失（WAL/redo log 保证） |
事务隔离级别
并发事务不加隔离会出现三类问题：
| 问题 | 说明 |
|---|---|
| 脏读 | 读到另一事务未提交的数据 |
| 不可重复读 | 同一事务内两次读同一行，值不同（被他人提交修改） |
| 幻读 | 同一事务内两次按条件查询，行数不同（被他人插入/删除） |
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 说明 |
|---|---|---|---|---|
READ UNCOMMITTED | 可能 | 可能 | 可能 | 最低，几乎不用 |
READ COMMITTED | 不会 | 可能 | 可能 | Oracle/PostgreSQL 默认 |
REPEATABLE READ | 不会 | 不会 | 可能* | MySQL InnoDB 默认（间隙锁消除幻读） |
SERIALIZABLE | 不会 | 不会 | 不会 | 最高，性能最差 |
数据类型选型
整数
| 类型 | 字节 | 范围（有符号） | 适用 |
|---|---|---|---|
TINYINT | 1 | -128 ~ 127 | 状态码、枚举值 |
SMALLINT | 2 | -32768 ~ 32767 | 小范围计数 |
INT | 4 | -21亿 ~ 21亿 | 普通主键、数量 |
BIGINT | 8 | ±9.2×10¹⁸ | 分布式 ID、大流水号 |
浮点与精确小数
| 类型 | 特点 | 适用 |
|---|---|---|
FLOAT / DOUBLE | 二进制浮点，有精度误差 | 科学计算，不适合金额 |
DECIMAL(p, s) | 十进制精确存储 | 金额、税率、汇率（必选） |
DECIMAL(10, 2)表示最多 10 位有效数字，其中 2 位小数。
字符串
| 类型 | 特点 | 适用 |
|---|---|---|
CHAR(n) | 定长，不足补空格，查询快 | 固定长度字段（手机号、身份证号） |
VARCHAR(n) | 变长，节省空间 | 绝大多数文本字段 |
TEXT | 大文本，不能做索引前缀 | 文章正文、备注（超 255 字符） |
VARCHAR(255)是常见选择，但n不是字节数而是字符数，UTF-8 下一个中文占 3 字节。
日期与时间
| 类型 | 范围 | 时区 | 适用 |
|---|---|---|---|
DATE | 1000-01-01 ~ 9999-12-31 | 无 | 生日、日期标签 |
DATETIME | 同上，含时间 | 无（存什么取什么） | 业务时间（明确时区场景） |
TIMESTAMP | 1970 ~ 2038 | 自动转换为 UTC 存储 | 记录行、创建/更新时间 |
TIMESTAMP有 2038 问题（32 位溢出），新系统推荐用DATETIME+ 应用层统一 UTC 处理，或直接存BIGINT（毫秒时间戳）。
OLTP vs OLAP
数据库设计目标因使用场景根本不同：
| 维度 | OLTP（事务处理） | OLAP（分析处理） |
|---|---|---|
| 目标 | 高并发增删改查 | 大数据量聚合分析 |
| 查询特点 | 单行/小范围，精确查找 | 跨表全表扫描、GROUP BY、聚合 |
| 数据量 | 当前热数据，GB 级 | 全量历史数据，TB～PB 级 |
| 范式 | 高度范式化（减冗余） | 反范式宽表（减 JOIN） |
| 代表产品 | MySQL、PostgreSQL | ClickHouse、Hive、BigQuery |
| 更新频率 | 高频小事务 | 批量导入，很少更新 |
| 索引策略 | B+ 树索引（点查） | 列式存储 + 位图索引（范围扫描） |
同一业务通常两套都需要：OLTP 负责在线业务，ETL 同步到 OLAP 做报表与分析（即数仓架构）。