综合练习与本站数据模型

这一课你将能做到

1. 独立完成 10 道由易到难的 SQL 题（覆盖 SELECT/WHERE/JOIN/聚合/窗口函数）
2. 读懂本站 backend/src/main/resources/schema.sql 里 cf_track / cf_lesson / cf_progress 的结构与索引设计
3. 把练习库模型映射到本站真实表，对本站库写出安全的只读查询

为什么要学

前 9 课的语法是散装零件，真实开发要的是整车装配：拿到一个页面需求，独立写出「哪几张表、怎么连、怎么筛、怎么聚合、怎么排序」的完整 SQL。这一课先在练习库里过一遍 10 道综合题，再把镜头转向本站——你已经天天在用的 CodeRoadMap，它的数据就躺在 codeforge 库的 cf_* 表里，读得懂它，SQL 才算真的入门。

先建立图像

一条 SQL 的装配流水线（9 课知识的总装图）：

FROM     决定从哪些表拿数据        （第 1、4 课）
  ↓ JOIN     怎么把表拼宽
WHERE    决定保留哪些行            （第 3 课）
  ↓ DISTINCT 去重                   （第 2 课）
GROUP BY 把行分堆                 （第 5 课）
HAVING   决定保留哪些堆
SELECT   决定输出哪些列、叫什么名   （第 2 课）
ORDER BY 决定输出顺序
LIMIT    决定输出几行

第 6-9 课（写数据、表设计、索引、事务）则保证这条流水线跑在正确、快速、安全的表上。

跟着做

步骤 0：复原数据

-- 先回第一课重跑完整建库脚本，把练习库恢复到初始状态
USE sql_practice;

以下 10 题按难度分三关。先自己写，写不出来再看参考答案——答案就在题下，考验定力。

第一关：单表（热身）

Q1. 查所有已发布课程，按价格从高到低。

SELECT title, price
FROM course
WHERE status = 'published'
ORDER BY price DESC;

Q2. 查深圳、杭州两地的 VIP 学生姓名与城市。

SELECT name, city
FROM student
WHERE city IN ('深圳', '杭州') AND vip = 1;
-- 老陈（深圳）1 行

Q3. 统计每个分类的课程数与平均售价，平均价保留两位小数。

SELECT category,
       COUNT(*)             AS course_cnt,
       ROUND(AVG(price), 2) AS avg_price
FROM course
GROUP BY category;

第二关：联表（主力战场）

Q4. 查「SQL 与关系型数据库」的已发布课时，按课内顺序排列。

SELECT l.order_index, l.title
FROM lesson l
JOIN course c ON l.course_id = c.id
WHERE c.slug = 'sql-essentials' AND l.status = 'published'
ORDER BY l.order_index;
-- 3 行（id=9 的 JOIN 课是 draft，被筛掉）

Q5. 查每个学生完成的课时数，没学过的也要出现（计 0）。

SELECT s.name, COUNT(r.id) AS done_cnt
FROM student s
LEFT JOIN study_record r ON r.student_id = s.id AND r.completed = 1
GROUP BY s.id, s.name
ORDER BY done_cnt DESC;
-- 老陈/阿珍/老周 4，小林 2，大鹏/小夏 1，小吴 0

三个考点：LEFT JOIN 保住小吴；右表条件 completed = 1 放 ON 不放 WHERE；COUNT(r.id) 而不是 COUNT(*)。

Q6. 查从未被购买过的课程。

SELECT c.title
FROM course c
LEFT JOIN pay_order o ON o.course_id = c.id
WHERE o.id IS NULL;
-- Java 面向对象进阶 / SQL 与关系型数据库 / Redis 缓存入门 / Git 团队协作基础

反连接套路：LEFT JOIN + 右表主键 IS NULL。

Q7. 查平均分最高、且至少 2 人完成的已发布课时。

SELECT l.id, l.title,
       ROUND(AVG(r.score), 2) AS avg_score,
       COUNT(*)               AS ppl
FROM study_record r
JOIN lesson l ON r.lesson_id = l.id
WHERE r.completed = 1 AND r.score IS NOT NULL
GROUP BY l.id, l.title
HAVING COUNT(*) >= 2
ORDER BY avg_score DESC
LIMIT 1;
-- 关系型数据库与 SQL 是什么，88.75，4 人

第三关：进阶（面试同款）

Q8. 查每个分类售价最高的课程（窗口函数）。

SELECT category, title, price
FROM (
    SELECT c.category, c.title, c.price,
           ROW_NUMBER() OVER (PARTITION BY c.category ORDER BY c.price DESC) AS rn
    FROM course c
) t
WHERE rn = 1;
-- java: Java 面向对象进阶 / database: MySQL 性能优化实战 / frontend: 前端页面基础

GROUP BY 做不了「组内取整行」，这是窗口函数的地盘。内层子查询必须有别名 t（MySQL 强制）。

Q9. 查各课程的实际收入（只算未退款订单），按收入降序。

SELECT c.title,
       SUM(o.amount) AS revenue,
       COUNT(*)      AS paid_orders
FROM pay_order o
JOIN course c ON o.course_id = c.id
WHERE o.status = 'PAID'
GROUP BY c.id, c.title
ORDER BY revenue DESC;

Q10. 查阿珍的全部学习明细：学生名、课程名、课时名、成绩。

SELECT s.name, c.title AS course_title, l.title AS lesson_title, r.score
FROM study_record r
JOIN student s ON r.student_id = s.id
JOIN lesson l  ON r.lesson_id  = l.id
JOIN course c  ON l.course_id  = c.id
WHERE s.name = '阿珍' AND r.completed = 1
ORDER BY r.score DESC;

步骤 1：读本站真实模型

打开 backend/src/main/resources/schema.sql（节选、略去部分列）：

CREATE TABLE IF NOT EXISTS cf_track (            -- 学习路径（≈练习库 course）
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    slug VARCHAR(64) NOT NULL UNIQUE,
    title VARCHAR(128) NOT NULL,
    category VARCHAR(32) NOT NULL,
    level VARCHAR(32) NOT NULL DEFAULT 'beginner',
    is_member TINYINT(1) NOT NULL DEFAULT 0,
    estimated_weeks INT DEFAULT 4,
    sort_order INT DEFAULT 0,
    INDEX idx_slug (slug), INDEX idx_category (category)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cf_lesson (           -- 课时（≈练习库 lesson）
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    track_id BIGINT NOT NULL,
    slug VARCHAR(64) NOT NULL,
    title VARCHAR(256) NOT NULL,
    order_index INT NOT NULL,
    duration VARCHAR(32) DEFAULT NULL,
    content_md MEDIUMTEXT,
    status VARCHAR(32) NOT NULL DEFAULT 'draft',  -- draft/published
    UNIQUE KEY uk_track_slug (track_id, slug),
    INDEX idx_track_order (track_id, order_index),
    FOREIGN KEY (track_id) REFERENCES cf_track(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cf_progress (         -- 学习进度（≈练习库 study_record）
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    track_id BIGINT NOT NULL,
    lesson_id BIGINT NOT NULL,
    completed TINYINT(1) NOT NULL DEFAULT 0,
    score INT DEFAULT NULL,
    completed_at DATETIME DEFAULT NULL,
    UNIQUE KEY uk_user_lesson (user_id, lesson_id),
    FOREIGN KEY (user_id) REFERENCES cf_user(id) ON DELETE CASCADE,
    FOREIGN KEY (lesson_id) REFERENCES cf_lesson(id) ON DELETE CASCADE
) ENGINE=InnoDB;

对照练习库，学到的东西全部命中：

练习库	本站	共同的设计决策
student	cf_user	自增 BIGINT 主键 + username/email UNIQUE
course	cf_track	slug 做 URL 路由（UNIQUE），id 做库内关联
lesson	cf_lesson	外键挂父表 + CASCADE；联合索引 (父id, order_index) 服务课程详情页
study_record	cf_progress	多对多中间表 + 联合唯一键防重复记录
pay_order	（本站会员走 cf_user.member_until）	订单模型按业务形态裁剪

真实表比练习表多的维度（is_member、is_preview、source_type…）是业务长出来的，骨架完全一致。

步骤 2：对本站库写只读查询

纪律先行：对 codeforge 库只跑 SELECT，永远不跑 DML/DDL；加 LIMIT 控制返回量；没有本地库就先 USE codeforge; 试连。

-- ① 语言类课程清单（列表页数据原型）
SELECT slug, title, level, estimated_weeks
FROM cf_track
WHERE category = 'language'
ORDER BY sort_order;

-- ② 某门课的已发布课时目录（课时侧栏数据原型）
SELECT l.order_index, l.title, l.duration
FROM cf_lesson l
JOIN cf_track t ON l.track_id = t.id
WHERE t.slug = 'sql-essentials' AND l.status = 'published'
ORDER BY l.order_index;

-- ③ 某用户在某课程的学习进度（个人中心数据原型）
SELECT u.username, t.title AS track_title,
       l.title AS lesson_title, p.completed, p.score, p.completed_at
FROM cf_progress p
JOIN cf_user u   ON p.user_id   = u.id
JOIN cf_lesson l ON p.lesson_id = l.id
JOIN cf_track t  ON l.track_id  = t.id
WHERE u.username = '你的用户名'
ORDER BY t.sort_order, l.order_index;

这三条查询，就是页面「课程列表 / 课时侧栏 / 学习进度」背后 Spring Boot 真正发出的 SQL 原型。

步骤 3：毕业检查

• 10 道题不看答案独立做对 7 道以上 → 本 track 达标
• 能对着 schema.sql 说出任一张 cf_ 表的主键、外键、索引分别服务什么 → 表设计和索引课达标
• 能把一个页面需求口述成「FROM 谁、怎么 JOIN、WHERE 什么、GROUP BY 什么」→ 全部达标

讲清楚要点

1. 写 SQL 的正确顺序是先想「行从哪来」：先确定 FROM + JOIN 的表和关系，再谈筛选和聚合。直接从 SELECT 抠语法，永远写不出四表联查。
2. 每个业务页面 = 一条（组）SQL：列表页=WHERE+ORDER BY+LIMIT，详情页=主键等值查询，侧栏=外键关联+排序，看板=GROUP BY+聚合。拿到需求先问「这页数据是哪种形状」。
3. 本站表结构是可以直接读的教材：schema.sql 里每一行索引和外键都是前面 9 课概念的实例。读陌生系统的第一步永远是读它的建表语句。
4. 对真实库的纪律：只读、加 LIMIT、认清环境（SELECT DATABASE(); 先确认），写操作永远走代码和迁移脚本，不手敲。
5. 练不出速度感是因为数据太小：练习库 17 行怎么看都快。真实体感要靠第 8 课那种 10 万行实验，或拿公司测试库的慢查询练手。

你可能会懵的地方

• 答案和我的写法不一样，算对吗？ SQL 一题多解太正常了：Q5 换成先在子查询过滤 completed 再 JOIN 也对，Q6 用 NOT EXISTS 也对。判断标准是结果集一致 + 语义清晰 + 不踩 NULL 坑。跑 EXPLAIN 对比计划是进阶判据。

• 练习库模型和本站模型为什么不完全一样？ 练习库是「教学最小完备集」：五张表覆盖全部语法。本站多了会员、来源、多语言内容等业务维度。学的是骨架决策，不是逐列背诵。

• cf_progress 里为什么冗余了 track_id？lesson 表里明明有？ 你抓到了一个反范式案例：进度查询永远带着「某课程」维度，冗余一列让按用户的课程维度统计少一层 JOIN。第 7 课说的「反范式要清醒」就是这种场景。

• 想练真实查询但没有 codeforge 库？ 两个途径：本地起后端让 Flyway 自动建库；或把 schema.sql 导进本地 MySQL 再跑 deploy/sql/courses/ 下的造数脚本。都做到只读访问。

• 学完这 10 课，下一步是什么？ 纵向加深：窗口函数、子查询与 CTE、慢查询调优、锁与 MVCC 深水区，见延伸里的进阶方向。先把手上的项目 SQL 写对写稳，再谈炫技。

错法对照

错法	问题	正解
只看答案不动手	眼会手不会	每题先自己写，卡 5 分钟再看
背答案不总结套路	换个题干就废	归纳每题考的是哪一课哪个套路
对 codeforge 手敲 UPDATE	生产数据事故	真实库只读，写操作走代码
查询不加 LIMIT 拉全表	大表一拉几十万行	探索阶段永远先 LIMIT
读完 schema 不做笔记	下次还要重读	每张表记三行：主键/外键/核心索引

卡点急救

1. 第 8 题子查询报 Every derived table must have its own alias：子查询括号后面必须跟别名（t），这是 MySQL 硬性要求。
2. Q5 小吴的行不见了：LEFT JOIN 写成了 INNER JOIN，或 completed = 1 被你放进了 WHERE。
3. 连不上本站库：codeforge 库只在后端启动（Flyway）或手动导入 schema 后才存在；先 SHOW DATABASES; 确认。
4. 答案能跑但结果对不上：检查有没有按步骤 0 复原数据——做过第 6 课的删除和第 8 课的 10 万行插入，结果必然对不上。

面试常问

• 给你一张陌生表的 DDL，你怎么快速理解它？ 三步：看主键和唯一键（认行逻辑）；看外键和索引（关系与查询模式，索引即「这表被怎么查」的说明书）；看注释和默认值（业务枚举与状态机）。这题考的不是 SQL 语法，是读 schema 的方法论。

• 写一道「每个分类下最高分的学生」类题目的思路？ 先明确口径（组内 Top1 还是 TopN、并列怎么算），再选工具：GROUP BY + JOIN 回原表取整行，或窗口函数 ROW_NUMBER/RANK。说清取舍比写出来更得分。

• 线上一条 SQL 慢，你的排查路径？ EXPLAIN 看 type/key/rows/Extra → 是否缺索引或写法失效 → 数据量与回表情况 → 是否深分页/大事务 → 慢查询日志定位 Top SQL。有序的路径感是面试官想听的。

• 练习时和面试时，SQL 最容易错的点？ NULL 三值逻辑、LEFT JOIN 被 WHERE 吃掉、GROUP BY 口径、隐式类型转换、忘 ORDER BY 假设顺序。本 track 每一课的错法对照表就是考点清单。

自测三问

1. 「课程列表 / 课时侧栏 / 学习进度」三个页面分别对应什么形状的 SQL？
2. cf_lesson 的 idx_track_order(track_id, order_index) 同时服务了哪两类操作？
3. 对本站库做探索时，必须遵守的三条纪律是什么？

小结一句话

10 课零件装配成整车：FROM/JOIN 定关系、WHERE 定行、GROUP BY 定口径、SELECT 定输出；练习库的五张表和本站的 cf_* 表是同一套设计决策——接下来去真实项目里写你的第一条生产 SQL。

延伸

• 本站真实建表脚本：backend/src/main/resources/schema.sql（强烈建议通读一遍）
• MySQL 8.0 参考手册
• 进阶方向：窗口函数与 CTE → 慢查询与锁 → Spring Boot + MyBatis 实战（见本站对应 track）