SQL 进阶地图

这一课你将能做到

1. 说清楚基础 SQL（SELECT、JOIN、GROUP BY）与本 track 进阶内容（DDL、DML、窗口函数、约束、事务、调优）的分工边界
2. 在本机搭好 sql_practice 练习库，建出 cf_user、cf_problem、cf_submission、cf_practice_record 四张做题平台业务表并灌入可复现的示例数据
3. 跑通一条聚合自检查询，输出与本文给出的结果表完全一致，确认环境、数据、版本三者都就绪

为什么要学

「会写 SELECT」只是 SQL 的及格线。真正的分水岭在后面：表结构怎么设计才不返工、并发下数据怎么不错不乱、一条慢 SQL 怎么从 2 秒压到 20 毫秒、面试官问「RR 级别下幻读到底防没防」你能不能答出 MVCC 和间隙锁的分工。这些内容全部落在本 track 的 12 课里。

更实际的一点：这套课不是拿抽象的 A 表 B 表练手，而是用本站（CodeForge 刷题平台）的真实业务表贯穿——用户、题目、提交、刷题记录。学完每一课，你手里都有一套可以反复跑、反复改、结果可预期的练手库；以后你进入任何一家公司的项目，看到的表结构大概率就是这几张表的变体（用户表、内容表、行为流水表、配置表）。

人话：基础 SQL 让你「会查数据」，进阶 SQL 让你「设计表、保数据、提速度」。前者是入职门槛，后者是面试加分项和日常排障的看家本领。

先建立图像

整站核心表关系（第 8、10 课还会引入课时表与钱包表）：

cf_user（用户）
   │ 1:N
   ├──> cf_submission（提交）<── N:1 ── cf_problem（题目）
   │ 1:N
   └──> cf_practice_record（刷题记录）<── N:1 ──┘

四张表各自的职责：

表	干什么	关键列
cf_user	平台注册用户	username、email、level、status
cf_problem	题目元数据	slug、difficulty（easy/medium/hard）
cf_submission	每次判题机产生的提交流水	user_id、problem_id、status、runtime_ms
cf_practice_record	用户主动练习的行为记录	user_id、problem_id、is_correct、duration_sec

记住这个区分：submission 是机器写入的流水，practice_record 是用户行为。后面统计 AC 率用前者，统计学习时长用后者，混用是新手常犯的错。

跟着做

步骤 1：确认 MySQL 版本

SELECT VERSION();

输出必须是 8.0.x。窗口函数、CTE、CHECK 约束、角色管理都是 8.0 引入或加固的，5.7 跑本 track 会直接语法报错。

步骤 2：建库

CREATE DATABASE IF NOT EXISTS sql_practice
  DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE sql_practice;

步骤 3：建四张业务表

CREATE TABLE IF NOT EXISTS cf_user (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  username   VARCHAR(50)     NOT NULL COMMENT '用户名',
  email      VARCHAR(255)    NOT NULL COMMENT '邮箱',
  level      TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '等级 1-5',
  status     ENUM('active','banned') NOT NULL DEFAULT 'active' COMMENT '账号状态',
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_username (username),
  UNIQUE KEY uk_email (email)
) ENGINE=InnoDB COMMENT='用户表';

CREATE TABLE IF NOT EXISTS cf_problem (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug       VARCHAR(80)  NOT NULL COMMENT '题目标识',
  title      VARCHAR(200) NOT NULL COMMENT '题目标题',
  difficulty ENUM('easy','medium','hard') NOT NULL DEFAULT 'easy',
  PRIMARY KEY (id),
  UNIQUE KEY uk_slug (slug)
) ENGINE=InnoDB COMMENT='题目表';

CREATE TABLE IF NOT EXISTS cf_submission (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      BIGINT UNSIGNED NOT NULL,
  problem_id   INT UNSIGNED    NOT NULL,
  language     VARCHAR(20)     NOT NULL DEFAULT 'java',
  status       ENUM('accepted','wrong_answer','runtime_error','time_limit') NOT NULL,
  runtime_ms   INT UNSIGNED    NOT NULL DEFAULT 0 COMMENT '运行耗时毫秒',
  submitted_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_user_problem (user_id, problem_id)
) ENGINE=InnoDB COMMENT='提交表';

CREATE TABLE IF NOT EXISTS cf_practice_record (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      BIGINT UNSIGNED NOT NULL,
  problem_id   INT UNSIGNED    NOT NULL,
  is_correct   TINYINT(1)      NOT NULL DEFAULT 0 COMMENT '本次练习是否做对',
  duration_sec INT UNSIGNED    NOT NULL DEFAULT 0 COMMENT '用时秒',
  practiced_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_user_time (user_id, practiced_at)
) ENGINE=InnoDB COMMENT='刷题记录表';

步骤 4：灌入练习数据

显式写 id 和时间，是为了保证后面每一课的查询结果都能和本文完全对上：

INSERT INTO cf_user (id, username, email, level, status, created_at) VALUES
  (1, 'lin',  'lin@example.com',  3, 'active', '2024-11-01 09:00:00'),
  (2, 'zhao', 'zhao@example.com', 5, 'active', '2024-11-05 14:30:00'),
  (3, 'wang', 'wang@example.com', 1, 'active', '2025-01-10 10:00:00'),
  (4, 'sun',  'sun@example.com',  2, 'banned', '2025-02-20 16:45:00'),
  (5, 'zhou', 'zhou@example.com', 4, 'active', '2025-03-15 08:20:00'),
  (6, 'qian', 'qian@example.com', 1, 'active', '2025-05-01 12:00:00');

INSERT INTO cf_problem (id, slug, title, difficulty) VALUES
  (1, 'two-sum',     '两数之和',             'easy'),
  (2, 'lrucache',    'LRU 缓存',             'medium'),
  (3, 'kth-largest', '数组中的第 K 大元素',   'medium'),
  (4, 'graph-bfs',   '图的广度优先遍历',      'hard'),
  (5, 'valid-paren', '有效的括号',            'easy');

INSERT INTO cf_submission (id, user_id, problem_id, language, status, runtime_ms, submitted_at) VALUES
  (1,  1, 1, 'java',   'accepted',       12, '2025-06-01 09:05:00'),
  (2,  1, 2, 'java',   'wrong_answer',   30, '2025-06-01 09:40:00'),
  (3,  1, 2, 'java',   'accepted',       25, '2025-06-02 10:00:00'),
  (4,  2, 4, 'python', 'accepted',       48, '2025-06-03 20:15:00'),
  (5,  2, 3, 'python', 'time_limit',   2000, '2025-06-03 21:00:00'),
  (6,  2, 3, 'python', 'accepted',       60, '2025-06-04 09:00:00'),
  (7,  3, 1, 'java',   'runtime_error',   5, '2025-06-05 11:30:00'),
  (8,  3, 1, 'java',   'accepted',       15, '2025-06-05 11:50:00'),
  (9,  5, 2, 'go',     'accepted',       20, '2025-06-06 15:00:00'),
  (10, 5, 5, 'go',     'wrong_answer',    8, '2025-06-06 15:30:00'),
  (11, 5, 5, 'go',     'accepted',       10, '2025-06-07 09:00:00'),
  (12, 4, 1, 'java',   'wrong_answer',   18, '2025-06-07 10:10:00');

INSERT INTO cf_practice_record (id, user_id, problem_id, is_correct, duration_sec, practiced_at) VALUES
  (1, 1, 1, 1, 300,  '2025-06-01 09:00:00'),
  (2, 1, 2, 0, 900,  '2025-06-01 09:20:00'),
  (3, 1, 2, 1, 600,  '2025-06-02 09:50:00'),
  (4, 2, 4, 1, 1500, '2025-06-03 19:50:00'),
  (5, 2, 3, 1, 800,  '2025-06-04 08:55:00'),
  (6, 3, 1, 1, 450,  '2025-06-05 11:45:00'),
  (7, 5, 2, 1, 500,  '2025-06-06 14:55:00'),
  (8, 5, 5, 0, 200,  '2025-06-06 15:20:00'),
  (9, 5, 5, 1, 250,  '2025-06-07 08:55:00');

注意 qian（id=6）一条提交都没有——这个「空档用户」在第 6 课讲 NOT EXISTS 陷阱时会派上大用场。

步骤 5：跑自检查询

SELECT
  u.username,
  COUNT(s.id)                      AS total_sub,
  SUM(s.status = 'accepted')       AS ac_cnt
FROM cf_user u
LEFT JOIN cf_submission s ON s.user_id = u.id
GROUP BY u.id, u.username
ORDER BY ac_cnt DESC, total_sub DESC, u.id;

预期结果：

username	total_sub	ac_cnt
lin	3	2
zhao	3	2
zhou	3	2
wang	2	1
sun	1	0
qian	0	0

能对上这张表，说明环境没问题。SUM(s.status = 'accepted') 是 MySQL 的布尔求和技巧：条件成立算 1，不成立算 0，SUM 一下就是 AC 条数——这个写法后面会反复出现。

讲清楚要点

1. 基础与本 track 的分工：SELECT/JOIN/GROUP BY 属于基础层，本 track 负责 DDL 建表、DML 写入安全、聚合与函数进阶、子查询与窗口函数、视图与存储过程、约束、事务与权限、性能调优。每一课都建立在前一课的表之上。
2. 为什么用业务表而不是 A/B/C 表：抽象表学完记不住结构、写不出业务语义；围绕用户-题目-提交这条主线，每一条 SQL 都有真实问题的答案（谁 AC 最多、哪天提交最多、谁没做过 easy 题），记忆和理解成本都低得多。
3. MySQL 8 是硬性前提：窗口函数（8.0）、CTE 与递归 CTE（8.0）、CHECK 约束真正生效（8.0.16）、角色（8.0）全在本 track 范围内。用 5.7 会在第 7 课当场卡死。
4. 必须 utf8mb4 而不是 utf8：MySQL 的 utf8 是残缺实现，最长 3 字节，存 emoji 或部分生僻字直接报错；utf8mb4 才是完整 4 字节 UTF-8，也是 8.0 的默认字符集。
5. 只关心 InnoDB：事务、行锁、MVCC、崩溃恢复都依赖 InnoDB。本 track 所有表显式声明 ENGINE=InnoDB，避免默认引擎被改过的环境踩坑。
6. 示例数据显式带 id 插入：这样每一课的查询结果都是确定的，你跑出来的每一行都能和本文核对，学错能立刻发现。

你可能会懵的地方

• 我用 MySQL 5.7 或 MariaDB 行不行？ 5.7 没有窗口函数和 CTE，第 7 课整课无法完成；MariaDB 语法大体兼容但行为有差异（如默认隔离级别）。装 MySQL 8.0+，一次到位。

• 为什么不用客户端里现成的示例库（如 sakila）？ 示例库的电影租赁业务离你太远，且不同教程改来改去数据早就不一致。自建练习库保证每一步可复现。

• 12 课的 SQL 都要背吗？ 不用。跟着敲、把结果对上就是理解；只有每课「面试常问」里的条目值得背成条目化的答案。

• 状态字段用 ENUM 还是 VARCHAR？ 状态少且固定（如四个判题结果）用 ENUM 省_checks 心；会持续增加的用 VARCHAR + 应用层字典。面试答「小规模 ENUM、可扩展场景 VARCHAR/TINYINT 加字典表」即可。

• ORM 都这么强了，为什么还手写 SQL？ CRUD 交给 ORM 没问题，但复杂报表（窗口函数）、性能调优（EXPLAIN）、事务边界控制都绕不开手写 SQL——这恰恰是本 track 的全部内容。

错法对照

错法	问题	正解
建库不指定 utf8mb4	存中文/emoji 乱码或报错	DEFAULT CHARSET utf8mb4
表用 MyISAM 引擎	不支持事务和行锁	显式 ENGINE=InnoDB
INSERT 不写列名 INSERT INTO t VALUES(...)	表结构一变全错	显式列清单
拿系统 test 库当练习库	升级重装时被清空	专用 sql_practice 库
跳过本课直接学后面的课	后续结果对不上无法核对	先跑完本课全部脚本

卡点急救

1. ERROR 1049 Unknown database 'sql_practice'：先执行步骤 2 的 CREATE DATABASE 再 USE；每开一个新客户端窗口都要重新 USE sql_practice。
2. 客户端连 MySQL 8 报认证错误（caching_sha2_password）：旧客户端不支持新认证插件。执行 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码'; 或升级客户端。
3. 自检查询结果为空或对不上：依次执行 SELECT COUNT(*) FROM cf_user;（应为 6）、SELECT COUNT(*) FROM cf_submission;（应为 12）；对不上就重跑步骤 4 的插入脚本（先把表 TRUNCATE）。

面试常问

• InnoDB 和 MyISAM 的区别？ InnoDB 支持事务、行级锁、外键、崩溃自动恢复（redo/undo），适合写多读少的在线业务；MyISAM 只支持表锁、无事务，崩溃后需修表。互联网在线业务一律 InnoDB。

• 为什么字符集要用 utf8mb4？ MySQL 的 utf8 每字符最多 3 字节，无法存 emoji 和部分生僻汉字；utf8mb4 是完整 UTF-8（最多 4 字节）。8.0 起默认已是 utf8mb4，但老库迁移时仍需显式检查。

• 你平时怎么练 SQL？ 答「自建练习库、造可复现数据、每个查询核对预期结果」是明显的加分项——说明你有实验意识，而不是背语法。

自测三问

1. cf_submission 和 cf_practice_record 的职责边界是什么？统计 AC 率应该用哪张？
2. 本 track 哪些特性要求 MySQL 8 以上？至少说出三个。
3. 自检查询里 SUM(s.status = 'accepted') 为什么能得到 AC 条数？换成 qian 那行为什么是 NULL 而不是 0？

小结一句话

进阶 SQL 的核心是「设计表结构、保证数据正确、把查询跑快」，本课建好的 sql_practice 练习库和 6 用户 6 题 12 提交的示例数据将贯穿全部 12 课。

延伸

• MySQL 8.0 Reference Manual — Data Types
• 下一课：DDL 与表结构——亲手把用户表「规范地」建一遍，搞懂类型选择、默认值与 ALTER 的代价。