关系模型与 SQL：从 SELECT 到 JOIN

0. 元信息

• 主题路径：docs/topics/db-and-sql/subtopics/relational-model-and-sql/README.md
• 主分类：计算机基础
• 辅助分类：Web 与后端
• 适合对象：完成父主题前置阶段的开发者
• 建议周期：1～3 周，每周 8～12 小时
• 前置知识：Linux 命令行与 PostgreSQL 基础环境
• 最终目标：能为给定结果写出正确 SQL，并解释 bag semantics、NULL 与 JOIN 基数

1. 学习路线

关系定义 → SQL 表达式 → JOIN → 聚合 → 窗口函数

2. 阶段周数分配

本子主题按实验推进，不固定周数。每完成一个概念就留下 SQL、输入数据、输出和解释。

3. 九阶段表（精简）

4. 第一周任务

精简版不固定 Day 1～7。执行约定是 PostgreSQL 16+、psql -v ON_ERROR_STOP=1、脚本可在空库重复运行。

5. 阶段通用验收

以 §3 的可观察标准和 §9.4 题目为验收。

6. 最终验收

独立完成一份最小实验、三组数据、一份解释和一次 PostgreSQL/MySQL 行为对照。

7. 综合项目

并入父主题“小型电商数据库”，本子主题负责 SQL 查询正确性 的设计、实验与报告。

本主题贡献

• 在父主题 db-and-sql 的综合项目「小型电商数据库」中，本主题（关系模型与 SQL）负责 SQL 查询正确性：把 relation / tuple / attribute / key 落到 5 张核心表（users / products / orders / order_items / inventory）的 schema 上，并写出 bag semantics、NULL 三值逻辑、JOIN 基数与窗口函数边界全部正确的查询。
• 把 §1 路线「关系定义 → SQL 表达式 → JOIN → 聚合 → 窗口函数」落成 40+ 条可执行 SQL：用 INNER / LEFT / FULL / CROSS / SELF 区分基数差异；用 WHERE 与 HAVING 分清聚合前/后；用 RANK() / LAG() / NTILE() / SUM() OVER 跑排行榜与累计指标。
• 与 db-and-sql 其它子主题对接：把 DDL + seed + SQL + cleanup 全脚本化（psql -v ON_ERROR_STOP=1 可在空库重跑）作为 index-and-query-plan 的最小 schema 与基准输入；把同一套 SQL 在 RC / RR / Serializable 下复测并交给 transaction-and-isolation 出隔离异常矩阵。

交付物清单：

1. sql/01_schema.sql：5 张核心表 DDL，约束覆盖 PK / UK / FK / CHECK，建索引 ≥ 8 条，附 SELECT version() 输出；
2. sql/02_select_joins.sql：≥ 25 条 SELECT 含 JOIN 的用例，覆盖 INNER / LEFT / CROSS / SELF 与 WHERE / HAVING，含 bag 与 NULL 边界用例（NOT IN (NULL)、count(*) vs count(col)、WHERE col = NULL）；
3. sql/03_window.sql：≥ 8 条窗口函数用例（ROW_NUMBER / RANK / DENSE_RANK / LAG / LEAD / NTILE / SUM() OVER / AVG() OVER）+ 边界用例（PARTITION BY 单分组、frame 子句）；
4. reports/pg_vs_mysql.md：≥ 3 条 SQL 在 PostgreSQL 16+ 与 MySQL 8.4 下的差异（如 INSERT ... ON CONFLICT vs INSERT IGNORE、FILTER (WHERE …) vs 反连接、GROUP BY 隐式列、字符序与 UTF8 默认 collation）。

验收标准：

• 全部脚本在空 PostgreSQL 与空 MySQL 各跑一次成功（ON_ERROR_STOP=1，失败即停），且每次 reset 跑结果相同；
• 每条 SQL 输出（含 NULL 与重复行）与 expected/*.txt 完全一致，无遗漏行；
• 报告至少 3 个差异点，每条差异点附两边的 SQL 与执行结果（含 EXPLAIN cost）。

8. 推荐资料

见 §9.3。先用 PostgreSQL 官方文档和课程实验，书用于补理论。

9. 学习资料汇聚（v0.3 自包含）

9.1 背景与动机

Codd 关系模型、SQL 与关系代数之间的差异。真实系统还要处理数据规模、失败和并发。我们用 PostgreSQL 实验观察语义，再用 MySQL 核对实现差异。

9.2 概念地图

flowchart LR
  A[需求与数据] --> B[关系与约束]
  B --> C[SQL]
  C --> D[Planner / Transaction]
  I[Index / Statistics] --> D
  D --> E[执行与并发]
  E --> F[结果 / 延迟 / 锁等待]
  F --> G[验证与修改]
  G --> C

核心概念：relation、tuple、attribute、key、NULL、JOIN、aggregation、window。图中从业务不变量出发，经 SQL、计划或事务到可观察结果，再用相同数据验证修改。

9.3 基础知识讲解

9.3.1 经典论文

9.3.2 经典书籍

9.3.3 优秀博客与文档

9.3.4 核心人物

9.3.5 开发与学习方法

课程补充：CMU 15-445 适合数据库实现，Berkeley CS186 适合系统课程与实验。Andy Pavlo 的课堂讲解用于连接论文、实现与计划输出。

9.4 经典问题与经典案例

9.5 学习难点

概念难点

思维难点

工程难点

9.6 技术标准与接口

9.6.1 Entity

9.6.2 Scope

本子主题处理 SQL 查询正确性。SQL 标准描述通用语义，产品文档描述 planner、storage、locking 等实现。不要把 PostgreSQL 扩展当成所有数据库的共同保证。

9.6.3 Structure

必须掌握的接口：DDL/DML、约束、SELECT/JOIN、事务控制、EXPLAIN (ANALYZE, BUFFERS)、system catalog 与统计视图。具体重点是：relation、tuple、attribute、key、NULL、JOIN、aggregation、window。

9.6.4 Ecosystem

PostgreSQL 是主实验环境。MySQL 用于 InnoDB 和 optimizer 对照。SQLite 适合单文件实验。Oracle 与 SQL Server 用来了解企业产品的方言、执行计划和并发差异。JDBC、ODBC、DB-API 是常见客户端接口。

9.6.5 Depth Tiers

本计划要求达到 L3。

9.6.6 Source

• PostgreSQL 官方文档：引用快照 2026-07-30。
• MySQL Reference Manual：引用快照 2026-07-30。
• ISO SQL 标准条目：引用快照 2026-07-30。

10. 常见误区

• 用单个正常样例证明理解；
• 混淆逻辑结果与物理执行顺序；
• 忽略 NULL 与三值逻辑；
• 把数据库默认行为当业务规范；
• 不保存版本、DDL 和种子数据；
• 看到索引就断言查询会更快；
• 只看平均耗时，不看计划和数据分布；
• 在生产直接试破坏性 SQL；
• 长事务里做网络调用；
• 跨数据库迁移时只检查语法；
• 用 ORM 隐藏生成 SQL 后不再检查；
• 修改后不重跑相同正确性断言。

11. 所有知识点分类（统一规则）

1. 编程语言
2. 数据结构与算法
3. 计算机基础
4. 工程技术
5. Web 与后端
6. 前端与客户端
7. 数据与人工智能
8. 项目与职业能力
9. 安全与可靠性

本计划归属：计算机基础 主 + Web 与后端 辅。