MySQL升级打怪手册

从零基础到精通的MySQL学习路径

使用说明

本手册采用四个W学习法，帮助你系统化、高效地掌握MySQL知识：

📚 四个W学习法

每个关卡的知识点都遵循以下四个维度：

Why（为什么学？）
- 理解学习这个知识点的原因和重要性
- 明确它在实际工作中的应用场景
- 了解不掌握它会带来的问题
What（学什么？）
- 核心概念和语法
- 关键知识点详解
- 相关技术细节
How（怎么学？）
- 最佳实践和学习方法
- 常见错误和避坑指南
- 实用技巧和注意事项
What's next（接下来做什么？）
- 巩固练习的方向
- 下一步学习建议
- 进阶学习路径

🎯 使用建议

循序渐进：按关卡顺序学习，每关掌握后再进入下一关
动手实践：完成每个关卡的所有练习题，实践是最好的老师
理解原理：不要死记硬背，理解每个概念背后的原理
建立联系：将新知识与已学内容关联，构建完整的知识体系
定期复习：定期回顾前面的内容，温故而知新

第一关：基础入门

知识点

Why（为什么学？）

为什么要学习MySQL基础？

数据库是所有应用系统的核心，掌握数据库是成为合格开发者的必经之路
MySQL是世界上最流行的开源关系型数据库，被广泛应用于各类互联网项目
理解数据库基本概念是学习所有高级特性的基石，没有扎实的基础就无法深入学习
掌握基本SQL语句可以让你立即开始处理数据，解决实际问题

实际应用场景：

用户注册登录系统需要存储用户信息
电商网站需要管理商品、订单数据
内容管理系统需要存储文章、评论
任何需要持久化数据的应用都离不开数据库

What（学什么？）

1.1 MySQL基本概念

数据库（Database）: 存储数据的容器，类似文件夹
表（Table）: 数据库中的数据结构，由行和列组成，类似Excel表格
字段（Column）: 表中的列，每列有特定的数据类型，定义数据的属性
记录（Row）: 表中的一行数据，代表一个完整的数据实体
主键（Primary Key）: 唯一标识每条记录的字段，确保数据不重复
外键（Foreign Key）: 关联其他表的字段，建立表与表之间的关系

1.2 基本数据类型

数值类型:

INT - 整数（如年龄、数量）
DECIMAL(M,D) - 定点数（精确，用于金额计算）
FLOAT, DOUBLE - 浮点数（用于科学计算）

字符串类型:

CHAR(N) - 定长字符串（固定长度，如性别、邮编）
VARCHAR(N) - 变长字符串（灵活长度，如姓名、地址）
TEXT - 长文本（如文章内容、描述）

日期时间类型:

DATE - 日期 (YYYY-MM-DD，如生日)
DATETIME - 日期时间 (YYYY-MM-DD HH:MM:SS，如创建时间)
TIMESTAMP - 时间戳（自动记录时间）

1.3 基本SQL语句结构

-- 查询语句（读取数据）
SELECT 字段列表 FROM 表名 WHERE 条件;

-- 插入语句（添加数据）
INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2);

-- 更新语句（修改数据）
UPDATE 表名 SET 字段=值 WHERE 条件;

-- 删除语句（删除数据）
DELETE FROM 表名 WHERE 条件;

How（怎么学？）

学习方法：

边学边练：每学一个概念，立即在数据库中实践
理解类比：将数据库概念与现实生活对应（表=Excel表格，记录=一行数据）
手写SQL：不要复制粘贴，手动输入SQL语句加深记忆
看错误信息：出错时仔细阅读错误提示，理解问题所在

常见错误：

❌ 忘记在字符串值两边加引号：INSERT INTO users (name) VALUES (张三)
✅ 正确：INSERT INTO users (name) VALUES ('张三')
❌ SQL关键字拼写错误：SELCT * FROM users
✅ 正确：SELECT * FROM users
❌ 忘记WHERE条件导致全表更新/删除
✅ 更新前先用SELECT验证条件是否正确

最佳实践：

养成使用小写或大写统一书写SQL关键字的习惯（推荐大写关键字）
选择合适的数据类型可以节省存储空间和提高查询效率
每个表必须有主键，确保数据唯一性
执行UPDATE/DELETE前先用SELECT验证条件

What's next（接下来做什么？）

巩固练习：

完成下方的5道基础练习题
尝试创建一个自己熟悉场景的表（如图书、电影、课程等）
练习增删改查的基本操作，每种操作至少执行10次

进阶准备：

理解了基础概念后，下一关将学习如何查询和筛选数据
思考问题：如何从大量数据中找到我需要的信息？
预习：了解WHERE条件、排序、分页的概念

练习题

题目1: 创建一个学生表（students），包含以下字段：

id（整数，主键，自动递增）
name（姓名，最多50字符）
age（年龄，整数）
gender（性别，1字符）
email（邮箱，最多100字符）

题目2: 查看 students 表的结构

题目3: 插入3条学生记录：

张三，20岁，男，[email protected]
李四，22岁，女，[email protected]
王五，21岁，男，[email protected]

题目4: 查询所有学生的姓名和年龄

题目5: 删除表 students

第二关：单表查询

知识点

Why（为什么学？）

为什么要学习单表查询？

查询是数据库最常用的操作，占日常工作的80%以上
从海量数据中精确找到需要的信息是数据库的核心价值
掌握条件筛选、排序、分页是处理实际业务需求的基础
单表查询是多表联结、子查询等高级技术的基础

实际应用场景：

电商网站：查找价格在某个范围内的商品、按销量排序
用户系统：搜索用户名包含特定关键词的用户
内容平台：获取最新发布的10篇文章
数据分析：筛选出满足特定条件的记录进行统计

What（学什么？）

2.1 SELECT基础语法

SELECT 字段1, 字段2, ...    -- 选择要查询的字段
FROM 表名                   -- 从哪个表查询
WHERE 条件                  -- 筛选条件
ORDER BY 字段 [ASC|DESC]   -- 排序方式
LIMIT 数量;                 -- 限制返回数量

2.2 WHERE条件运算符

比较运算符：

= 等于
!= 或 <> 不等于
>, <, >=, <= 大于、小于、大于等于、小于等于

范围和列表：

BETWEEN ... AND ... 在某个范围内（包含边界值）
IN (值1, 值2, ...) 在指定列表中

模糊匹配：

LIKE 模糊匹配
- % 匹配任意多个字符（包括0个）
- _ 匹配单个字符

空值判断：

IS NULL 是空值
IS NOT NULL 不是空值

逻辑运算符：

AND 并且（所有条件都满足）
OR 或者（任一条件满足）
NOT 取反

2.3 排序与限制

-- 排序
ORDER BY 字段 ASC   -- 升序（从小到大，默认）
ORDER BY 字段 DESC  -- 降序（从大到小）

-- 限制结果数量
LIMIT 10           -- 返回前10条
LIMIT 5, 10        -- 从第6条开始返回10条（偏移5，用于分页）

2.4 去重与别名

-- 去重（去除重复记录）
SELECT DISTINCT 字段 FROM 表名;

-- 字段别名（让输出更易读）
SELECT 字段 AS 别名 FROM 表名;

-- 表别名（简化书写）
SELECT t.字段 FROM 表名 AS t;

How（怎么学？）

学习方法：

从简到繁：先掌握简单的SELECT，再逐步添加WHERE、ORDER BY等子句
对比学习：对比不同运算符的效果（如 = vs LIKE，AND vs OR）
组合练习：尝试多个条件组合，理解优先级和执行顺序
查看结果：每次查询都要观察返回的数据，验证是否符合预期

常见错误：

❌ WHERE name = '%张%'（等号不能用于模糊匹配）
✅ WHERE name LIKE '%张%'
❌ WHERE age = NULL（NULL不能用等号判断）
✅ WHERE age IS NULL
❌ WHERE salary > 5000 AND salary < 10000 OR dept = '销售部'（逻辑混乱）
✅ WHERE (salary > 5000 AND salary < 10000) OR dept = '销售部'（用括号明确优先级）

最佳实践：

只查询需要的字段，避免 SELECT *（提高性能）
WHERE条件中使用索引字段（查询更快）
LIKE模糊查询时，尽量避免前置%（如'%abc'会导致索引失效）
使用LIMIT限制返回结果，避免一次性返回大量数据

执行顺序理解：

FROM → WHERE → SELECT → ORDER BY → LIMIT
1.先确定查哪个表 → 2.筛选数据 → 3.选择字段 → 4.排序 → 5.限制数量

What's next（接下来做什么？）

巩固练习：

完成下方的10道单表查询练习题
尝试各种WHERE条件的组合，观察结果差异
练习编写复杂的多条件查询（至少包含3个以上条件）

进阶准备：

单表查询掌握后，下一关将学习多表联结
思考问题：如何查询分散在多个表中的关联数据？
预习：了解JOIN的概念和作用

练习题

准备数据：使用以下员工表（employees）

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE,
    manager_id INT
);

题目6: 查询工资大于5000的员工姓名和工资

题目7: 查询部门为'销售部'或'技术部'的所有员工

题目8: 查询姓名中包含'张'的员工信息

题目9: 查询工资在8000到15000之间的员工，按工资降序排列

题目10: 查询入职日期在2020年之后的员工，显示前5名

题目11: 查询邮箱为空的员工

题目12: 查询所有不同的部门名称（去重）

题目13: 查询工资最高的3名员工

题目14: 查询姓名以'李'开头，且工资大于6000的员工

题目15: 查询部门不是'财务部'的员工信息

第三关：多表联结

知识点

Why（为什么学？）

为什么要学习多表联结？

实际业务中，数据往往分散在多个相关联的表中（遵循数据库设计规范）
单表查询无法满足复杂的业务需求，需要关联多个表才能获取完整信息
JOIN是关系型数据库最核心的特性之一，是SQL的精髓所在
掌握JOIN可以解决90%以上的复杂查询场景

实际应用场景：

电商系统：查询订单详情需要关联用户表、商品表、订单表
人力资源：查询员工信息需要关联部门表、职位表、考勤表
社交网络：查询用户动态需要关联用户表、帖子表、评论表
报表统计：综合多个表的数据生成分析报告

What（学什么？）

3.1 JOIN类型

INNER JOIN（内连接） - 交集：

SELECT * FROM 表A
INNER JOIN 表B ON 表A.字段 = 表B.字段;

只返回两表中都有匹配的记录（最常用）

LEFT JOIN（左连接） - 保留左表：

SELECT * FROM 表A
LEFT JOIN 表B ON 表A.字段 = 表B.字段;

返回左表所有记录，右表没有匹配的显示NULL

RIGHT JOIN（右连接） - 保留右表：

SELECT * FROM 表A
RIGHT JOIN 表B ON 表A.字段 = 表B.字段;

返回右表所有记录，左表没有匹配的显示NULL

CROSS JOIN（交叉连接） - 笛卡尔积：

SELECT * FROM 表A CROSS JOIN 表B;

返回两表的所有组合（慎用，结果数量=表A行数×表B行数）

3.2 多表连接

-- 连接3个或更多表
SELECT *
FROM 表A
INNER JOIN 表B ON 表A.id = 表B.a_id
INNER JOIN 表C ON 表B.id = 表C.b_id;

3.3 自连接

-- 同一个表与自己连接（处理层级关系）
SELECT e.name AS 员工, m.name AS 经理
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

How（怎么学？）

学习方法：

画图理解：用韦恩图理解不同JOIN的区别（交集、并集）
小数据验证：先用少量数据测试JOIN结果，理解匹配逻辑
对比实践：同一需求用INNER JOIN和LEFT JOIN对比，观察差异
逐步增加：从2表连接开始，逐步增加到3表、4表

常见错误：

❌ 忘记ON条件导致笛卡尔积：SELECT * FROM A JOIN B
✅ SELECT * FROM A JOIN B ON A.id = B.a_id
❌ 混淆LEFT和INNER JOIN导致数据缺失
✅ 需要所有记录时用LEFT JOIN，只需匹配记录时用INNER JOIN
❌ 字段名重复不加表前缀：SELECT id FROM A JOIN B（id在两表都有）
✅ SELECT A.id, B.id AS b_id FROM A JOIN B ON A.id = B.a_id

最佳实践：

使用表别名简化SQL（FROM employees e代替FROM employees）
明确指定字段的表名，避免歧义（SELECT e.name, d.name）
优先使用INNER JOIN（性能更好），确需保留所有记录时才用LEFT JOIN
多表连接时注意顺序，一般从主表开始连接关联表

JOIN选择决策树：

需要两表的交集吗？
├─ 是 → 使用 INNER JOIN
└─ 否 → 需要保留左表所有记录吗？
    ├─ 是 → 使用 LEFT JOIN
    └─ 否 → 需要保留右表所有记录吗？
        └─ 是 → 使用 RIGHT JOIN

What's next（接下来做什么？）

巩固练习：

完成下方的10道多表联结练习题
尝试将之前的单表查询改写成多表关联查询
练习自连接处理树形结构数据（如组织架构、分类层级）

进阶准备：

多表联结掌握后，下一关将学习聚合函数与分组
思考问题：如何统计每个分组的数据？如何计算总数、平均值？
预习：了解GROUP BY、COUNT、SUM、AVG等概念

练习题

准备数据：使用以下表结构

-- 部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

-- 员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10,2),
    manager_id INT
);

-- 项目表
CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100),
    start_date DATE
);

-- 员工项目关联表
CREATE TABLE emp_projects (
    emp_id INT,
    project_id INT,
    role VARCHAR(50)
);

题目16: 查询所有员工及其所在部门名称

题目17: 查询所有部门及其员工数量（包括没有员工的部门）

题目18: 查询每个员工及其直属上级的姓名

题目19: 查询参与项目的员工姓名、部门名称和项目名称

题目20: 查询没有参与任何项目的员工

题目21: 查询同时参与2个以上项目的员工姓名

题目22: 查询'技术部'员工参与的所有项目名称

题目23: 查询与'张三'在同一部门的其他员工

题目24: 查询每个部门工资最高的员工信息

题目25: 查询没有下属的员工（不是任何人的经理）

第四关：聚合函数与分组

知识点

Why（为什么学？）

为什么要学习聚合函数与分组？

数据分析的核心是统计和汇总，而聚合函数是实现这一目标的关键工具
分组查询能将数据按类别汇总，是生成报表和统计数据的基础
掌握GROUP BY可以回答"每个类别有多少"、"各组平均值是多少"等问题
聚合分析是数据驱动决策的重要依据

实际应用场景：

销售报表：统计每个月/每个产品的销售额、销量
用户分析：计算每个地区的用户数、活跃度
财务统计：汇总每个部门的预算、支出
性能监控：分析每个服务的平均响应时间、错误率

What（学什么？）

4.1 常用聚合函数

COUNT(*)        -- 统计行数（包括NULL）
COUNT(字段)     -- 统计非空值数量
SUM(字段)       -- 求和（只能用于数值）
AVG(字段)       -- 平均值（自动忽略NULL）
MAX(字段)       -- 最大值
MIN(字段)       -- 最小值

4.2 GROUP BY分组

SELECT 字段, 聚合函数(字段)
FROM 表名
GROUP BY 字段;
-- 按指定字段分组，每组返回一行统计结果

4.3 HAVING过滤

SELECT 字段, COUNT(*)
FROM 表名
GROUP BY 字段
HAVING COUNT(*) > 10;  -- 过滤分组结果

WHERE vs HAVING 关键区别:

WHERE: 在分组前过滤原始数据行
HAVING: 在分组后过滤聚合结果

4.4 分组查询执行顺序

SELECT 字段, 聚合函数
FROM 表名
WHERE 条件              -- 1. 先过滤原始数据
GROUP BY 字段           -- 2. 对过滤后的数据分组
HAVING 聚合条件         -- 3. 过滤分组结果
ORDER BY 字段           -- 4. 对最终结果排序
LIMIT 数量              -- 5. 限制返回数量

How（怎么学？）

学习方法：

先单独练习：先掌握单个聚合函数，再组合使用
对比理解：对比WHERE和HAVING的区别，理解执行顺序
多维度分组：尝试按单个字段分组，再按多个字段分组
结合JOIN：将分组与多表联结结合，解决复杂统计需求

常见错误：

❌ SELECT中包含非分组字段：SELECT dept, name, COUNT(*) GROUP BY dept
✅ SELECT dept, COUNT(*) GROUP BY dept（只能选择分组字段和聚合函数）
❌ 在HAVING中使用非聚合条件：HAVING dept = '技术部'
✅ 用WHERE：WHERE dept = '技术部' GROUP BY ...（先过滤再分组性能更好）
❌ COUNT(*)和COUNT(字段)混淆
✅ COUNT(*)统计所有行，COUNT(字段)只统计非NULL值

最佳实践：

能用WHERE过滤的不用HAVING（WHERE性能更好）
SELECT的字段必须是GROUP BY的字段或聚合函数
使用有意义的别名让结果更易读（COUNT(*) AS total_count）
多字段分组时注意顺序（虽然结果相同，但影响可读性）

记忆口诀：

WHERE过滤行，HAVING过滤组
COUNT数行数，SUM求总和
AVG算平均，MAX和MIN找极值
GROUP BY分组，记得要聚合

What's next（接下来做什么？）

巩固练习：

完成下方的10道聚合与分组练习题
尝试编写复杂的分组统计（多字段分组、多个聚合函数）
练习WHERE和HAVING的组合使用

进阶准备：

聚合分组掌握后，下一关将学习子查询
思考问题：如何在WHERE中使用聚合结果？如何进行嵌套查询？
预习：了解子查询、IN、EXISTS等概念

练习题

题目26: 统计员工总数

题目27: 计算所有员工的平均工资

题目28: 查询每个部门的员工数量

题目29: 查询每个部门的平均工资，按平均工资降序排列

题目30: 查询员工数量大于5的部门

题目31: 查询每个部门工资最高和最低的差值

题目32: 统计每个月入职的员工数量

题目33: 查询平均工资超过8000的部门名称和平均工资

题目34: 查询每个经理管理的员工数量（不包括没有下属的）

题目35: 查询参与项目数量最多的前3名员工

第五关：子查询

知识点

Why（为什么学？）

为什么要学习子查询？

子查询是解决复杂业务逻辑的强大工具，可以将多个步骤的查询合并成一条SQL
许多查询需求无法用简单的JOIN实现，必须借助子查询才能完成
掌握子查询可以让你的SQL更加灵活，应对各种复杂的数据筛选场景
子查询是理解SQL执行逻辑的关键，是从初级到高级的必经之路

实际应用场景：

数据对比：查询高于平均值的记录（如工资高于平均工资的员工）
动态筛选：根据其他表的查询结果来筛选数据
复杂统计：查询每个部门工资高于本部门平均工资的员工
存在性判断：判断某些数据是否存在（如查询有订单的客户）

What（学什么？）

5.1 子查询类型

标量子查询（返回单个值）:

-- 查询工资高于平均工资的员工
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

列子查询（返回一列）:

-- 查询北京地区部门的所有员工
SELECT * FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location='北京');

行子查询（返回一行）:

-- 查询工资和年龄都达到最大值的员工
SELECT * FROM employees
WHERE (salary, age) = (SELECT MAX(salary), MAX(age) FROM employees);

表子查询（返回多行多列）:

-- 将子查询结果作为临时表使用
SELECT * FROM (
    SELECT name, salary FROM employees WHERE salary > 5000
) AS high_salary_emp;

5.2 EXISTS子查询

-- 查询有订单的员工（EXISTS判断存在性）
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.emp_id = e.id
);

-- NOT EXISTS判断不存在
SELECT * FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.emp_id = e.id
);

5.3 子查询位置

SELECT子句中: SELECT name, (SELECT COUNT(*) FROM orders WHERE emp_id = e.id) FROM employees e
FROM子句中: SELECT * FROM (子查询) AS 别名
WHERE子句中: WHERE salary > (子查询)
HAVING子句中: HAVING COUNT(*) > (子查询)

5.4 关键运算符

IN - 在子查询结果列表中
NOT IN - 不在子查询结果列表中
EXISTS - 子查询返回结果则为真
ANY/SOME - 满足子查询中任意一个值
ALL - 满足子查询中所有值

How（怎么学？）

学习方法：

分步验证：先单独运行子查询，查看返回结果，再组合成完整SQL
由内而外：从最内层的子查询开始理解，逐层向外推导
对比改写：尝试将JOIN改写成子查询，或将子查询改写成JOIN，理解两者的异同
控制变量：使用小数据集测试，观察不同类型子查询的返回结果

常见错误：

❌ 标量子查询返回多行：WHERE salary > (SELECT salary FROM employees)
✅ 确保返回单值：WHERE salary > (SELECT AVG(salary) FROM employees)
❌ IN子查询中包含NULL导致意外结果：WHERE dept_id NOT IN (SELECT manager_id FROM ...)
✅ 过滤NULL：WHERE dept_id NOT IN (SELECT manager_id FROM ... WHERE manager_id IS NOT NULL)
❌ 忘记为FROM子句的子查询起别名
✅ 必须起别名：FROM (SELECT ...) AS temp_table

最佳实践：

优先考虑JOIN，子查询可读性较差且性能可能不如JOIN
能用EXISTS就不用IN（性能更好，尤其是大数据集）
避免子查询嵌套过深（超过3层会很难维护）
在WHERE中的子查询要注意返回值类型（标量/列/行）

性能对比：

EXISTS vs IN:
- EXISTS: 找到第一条匹配就停止，适合大表
- IN: 会遍历所有结果，适合小数据集

子查询 vs JOIN:
- 子查询: 逻辑清晰，但可能被执行多次
- JOIN: 性能通常更好，但复杂查询可读性差

What's next（接下来做什么？）

巩固练习：

完成下方的10道子查询练习题
尝试将之前的JOIN查询改写成子查询实现
练习复杂的相关子查询（引用外层查询的子查询）

进阶准备：

子查询掌握后，下一关将学习高级查询技巧
思考问题：如何合并多个查询结果？如何进行条件分支？如何排名？
预习：了解UNION、CASE WHEN、窗口函数等概念

练习题

题目36: 查询工资高于平均工资的员工信息

题目37: 查询工资最高的员工姓名和工资

题目38: 查询与'张三'同一部门的其他员工（使用子查询）

题目39: 查询没有员工的部门名称

题目40: 查询工资高于本部门平均工资的员工

题目41: 查询至少参与过一个项目的员工姓名

题目42: 查询工资排名前10%的员工

题目43: 查询每个部门工资第二高的员工

题目44: 查询比所有'销售部'员工工资都高的员工

题目45: 查询参与项目数量超过平均值的员工

第六关：高级查询技巧

知识点

Why（为什么学？）

为什么要学习高级查询技巧？

实际业务中经常需要合并多个查询结果、进行条件分支判断、数据排名等复杂操作
窗口函数和CASE表达式可以优雅地解决很多用传统SQL难以实现的需求
掌握字符串和日期函数可以灵活处理各种数据格式和转换需求
这些技巧是高级SQL开发者的必备技能，能够显著提升SQL的表达能力

实际应用场景：

报表生成：合并多个数据源的查询结果，计算同比环比
数据分类：根据不同条件将数据分级、打标签
排名统计：计算销售排名、成绩排名、分组排名
数据清洗：格式化日期、拼接字符串、提取关键信息

What（学什么？）

6.1 UNION合并查询

-- UNION：合并结果并去重
SELECT 字段 FROM 表1
UNION
SELECT 字段 FROM 表2;

-- UNION ALL：合并结果不去重（性能更好）
SELECT 字段 FROM 表1
UNION ALL
SELECT 字段 FROM 表2;

注意事项：

两个查询的字段数量必须相同
对应字段的数据类型要兼容
最终结果的列名以第一个查询为准

6.2 CASE WHEN条件表达式

-- 简单CASE：等值判断
SELECT name,
    CASE dept_id
        WHEN 1 THEN '技术部'
        WHEN 2 THEN '销售部'
        ELSE '其他'
    END AS dept_name
FROM employees;

-- 搜索CASE：条件判断（更常用）
SELECT name,
    CASE
        WHEN salary < 5000 THEN '低'
        WHEN salary < 10000 THEN '中'
        ELSE '高'
    END AS salary_level
FROM employees;

6.3 窗口函数（MySQL 8.0+）

-- 排名函数
ROW_NUMBER()    -- 连续排名（1,2,3,4）
RANK()          -- 相同值同排名，跳号（1,2,2,4）
DENSE_RANK()    -- 相同值同排名，不跳号（1,2,2,3）

-- 全局排名
SELECT name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- 分区排名（每个部门内排名）
SELECT name, dept_id, salary,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 聚合窗口函数
SELECT name, salary,
    AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg_salary,
    SUM(salary) OVER (ORDER BY hire_date) AS cumulative_salary
FROM employees;

6.4 常用字符串函数

CONCAT(str1, str2, ...)      -- 连接字符串
SUBSTRING(str, pos, len)     -- 截取字符串（pos从1开始）
LENGTH(str)                  -- 字符串长度（字节）
CHAR_LENGTH(str)             -- 字符数
UPPER(str), LOWER(str)       -- 大小写转换
TRIM(str)                    -- 去除首尾空格
LTRIM(str), RTRIM(str)       -- 去除左/右空格
REPLACE(str, from, to)       -- 替换字符串
LEFT(str, len), RIGHT(str, len) -- 从左/右取n个字符
SUBSTRING_INDEX(str, delim, count) -- 按分隔符截取

6.5 常用日期函数

NOW()                        -- 当前日期时间
CURDATE()                    -- 当前日期
CURTIME()                    -- 当前时间
YEAR(date), MONTH(date), DAY(date) -- 提取年月日
DATE_ADD(date, INTERVAL n DAY)  -- 日期加法
DATE_SUB(date, INTERVAL n DAY)  -- 日期减法
DATEDIFF(date1, date2)       -- 日期差（天数）
DATE_FORMAT(date, format)    -- 格式化日期
  -- 常用格式：'%Y-%m-%d', '%Y年%m月%d日', '%Y-%m-%d %H:%i:%s'
TIMESTAMPDIFF(unit, start, end) -- 时间差（可指定单位）

How（怎么学？）

学习方法：

分类练习：将这些技巧分类掌握（合并、条件、排名、字符串、日期）
场景关联：遇到实际问题时思考应该用哪个技巧解决
组合使用：尝试将多个技巧组合使用，解决复杂问题
查文档：记住常用的，不常用的学会查官方文档

常见错误：

❌ UNION的字段数量不匹配：SELECT id, name FROM t1 UNION SELECT id FROM t2
✅ 确保字段数量相同：SELECT id, name FROM t1 UNION SELECT id, NULL FROM t2
❌ CASE忘记END关键字
✅ 必须以END结尾：CASE ... END AS column_name
❌ 窗口函数在WHERE中使用：WHERE ROW_NUMBER() OVER (...) = 1
✅ 用子查询：SELECT * FROM (SELECT *, ROW_NUMBER() OVER (...) AS rn FROM t) WHERE rn = 1
❌ 日期函数导致索引失效：WHERE YEAR(create_time) = 2023
✅ 使用范围条件：WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'

最佳实践：

UNION ALL性能优于UNION，确定无重复时优先使用UNION ALL
CASE WHEN按照从具体到一般的顺序编写条件
窗口函数的PARTITION BY相当于GROUP BY，但不会减少行数
字符串拼接时注意NULL值（NULL与任何值拼接都是NULL）
日期计算优先使用DATE_ADD/DATE_SUB，避免直接加减

技巧对比：

UNION vs UNION ALL:
- UNION: 去重，性能较慢
- UNION ALL: 不去重，性能更好

ROW_NUMBER vs RANK vs DENSE_RANK:
- ROW_NUMBER: 1,2,3,4（永远连续）
- RANK: 1,2,2,4（跳号）
- DENSE_RANK: 1,2,2,3（不跳号）

What's next（接下来做什么？）

巩固练习：

完成下方的10道高级查询练习题
尝试用CASE WHEN改写复杂的IF-ELSE逻辑
练习窗口函数解决排名、累计、移动平均等问题

进阶准备：

高级查询技巧掌握后，下一关将学习数据操作
思考问题：如何安全地插入、更新、删除数据？如何保证数据一致性？
预习：了解INSERT、UPDATE、DELETE、事务的概念

练习题

题目46: 查询本月入职的员工和上月入职的员工，合并显示

题目47: 根据工资将员工分为'高薪'(>10000)、'中薪'(5000-10000)、'低薪'(<5000)三个等级

题目48: 查询每个部门工资排名前3的员工

题目49: 计算每个员工的工作年限（到当前日期）

题目50: 将员工姓名和部门名称连接成一个字段显示

题目51: 查询每个员工的工资排名（全公司范围）

题目52: 提取每个员工邮箱的域名部分

题目53: 查询入职满1年的员工数量

题目54: 按季度统计员工入职人数

题目55: 查询每个部门内工资的累计和

第七关：数据操作

知识点

Why（为什么学？）

为什么要学习数据操作？

数据库不仅要查询，更重要的是要能够增加、修改、删除数据
数据操作是应用程序与数据库交互的核心，用户的每个操作都可能涉及数据变更
错误的数据操作可能导致数据丢失、损坏，必须掌握安全的操作方式
事务控制是保证数据一致性和完整性的关键，是企业级应用的基础

实际应用场景：

用户注册：插入新用户数据
订单处理：更新订单状态、库存数量
数据维护：删除过期数据、清理测试数据
批量操作：导入外部数据、数据迁移、批量更新

What（学什么？）

7.1 INSERT插入数据

-- 插入单条记录
INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2);

-- 插入多条记录（推荐，性能更好）
INSERT INTO 表名 (字段1, 字段2)
VALUES (值1, 值2), (值3, 值4), (值5, 值6);

-- 从查询结果插入
INSERT INTO 表名 (字段1, 字段2)
SELECT 字段1, 字段2 FROM 其他表 WHERE 条件;

-- 插入或更新（存在则更新，不存在则插入）
INSERT INTO 表名 (id, name, count)
VALUES (1, '张三', 10)
ON DUPLICATE KEY UPDATE count = count + 10;

-- 忽略重复（存在则跳过）
INSERT IGNORE INTO 表名 (字段1, 字段2) VALUES (值1, 值2);

7.2 UPDATE更新数据

-- 基本更新
UPDATE 表名 SET 字段=值 WHERE 条件;

-- 多字段更新
UPDATE 表名
SET 字段1=值1, 字段2=值2, 更新时间=NOW()
WHERE 条件;

-- 根据计算更新
UPDATE 表名
SET salary = salary * 1.1
WHERE dept = '技术部';

-- 根据其他表更新（关联更新）
UPDATE 表A
INNER JOIN 表B ON 表A.id = 表B.a_id
SET 表A.字段 = 表B.字段
WHERE 条件;

7.3 DELETE删除数据

-- 删除符合条件的记录
DELETE FROM 表名 WHERE 条件;

-- 删除所有记录（保留表结构）
DELETE FROM 表名;

-- 清空表（更快，重置自增ID，不能回滚）
TRUNCATE TABLE 表名;

-- 关联删除
DELETE 表A FROM 表A
INNER JOIN 表B ON 表A.id = 表B.a_id
WHERE 表B.条件;

DELETE vs TRUNCATE：

DELETE：逐行删除，可以回滚，可以加WHERE条件，不重置自增ID
TRUNCATE：直接删除表再重建，速度快，重置自增ID，不能回滚

7.4 事务控制

-- 开始事务
START TRANSACTION;  -- 或 BEGIN;

-- 执行SQL操作
INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 提交事务（永久保存）
COMMIT;

-- 回滚事务（撤销所有操作）
ROLLBACK;

事务的ACID特性：

原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败
一致性（Consistency）：事务前后数据保持一致状态
隔离性（Isolation）：并发事务互不干扰
持久性（Durability）：提交后的数据永久保存

How（怎么学？）

学习方法：

先查后改：执行UPDATE/DELETE前先用SELECT验证条件是否正确
小范围测试：在测试数据上练习，避免误操作生产数据
事务练习：理解事务的原子性，练习COMMIT和ROLLBACK
备份习惯：重要操作前先备份数据

常见错误：

❌ UPDATE/DELETE忘记WHERE条件导致全表更新：UPDATE employees SET salary = 10000
✅ 务必加WHERE条件：UPDATE employees SET salary = 10000 WHERE id = 1
❌ INSERT时字段和值数量不匹配
✅ 确保一一对应：INSERT INTO t (a, b) VALUES (1, 2)
❌ 在事务中混用DDL语句（CREATE/DROP等会自动提交）
✅ 事务中只使用DML语句（INSERT/UPDATE/DELETE）
❌ 批量INSERT时一次插入过多数据导致超时
✅ 分批插入，每次500-1000条

最佳实践：

批量插入：使用多值INSERT而非多次单条INSERT（性能提升10倍以上）
条件验证：UPDATE/DELETE前用SELECT验证WHERE条件
事务使用：多个相关操作必须放在事务中，保证一致性
避免全表操作：大表UPDATE/DELETE要分批执行，避免锁表
记录日志：重要操作前记录旧值，方便回滚

安全操作流程：

1. 开启事务：START TRANSACTION;
2. 执行操作：UPDATE/DELETE/INSERT
3. 验证结果：SELECT检查是否符合预期
4. 确认提交：COMMIT（或ROLLBACK撤销）

What's next（接下来做什么？）

巩固练习：

完成下方的10道数据操作练习题
练习事务的使用，故意制造错误并回滚
尝试批量插入大量数据，体会性能差异

进阶准备：

数据操作掌握后，下一关将学习表设计与索引
思考问题：如何设计高效的表结构？如何通过索引提升查询性能？
预习：了解主键、外键、索引、数据类型选择等概念

练习题

题目56: 向员工表插入一条新记录

题目57: 批量插入5条部门记录

题目58: 将所有'技术部'员工的工资增加10%

题目59: 删除工资低于3000的员工记录

题目60: 将2019年之前入职的员工工资统一增加500元

题目61: 复制一张员工表的结构和数据到新表

题目62: 删除重复的部门记录，只保留ID最小的

题目63: 更新员工表，将没有部门的员工分配到'未分配部门'

题目64: 使用事务完成：插入新员工，同时更新部门人数

题目65: 删除没有任何员工的部门

第八关：表设计与索引

知识点

Why（为什么学？）

为什么要学习表设计与索引？

良好的表设计是高性能数据库的基础，设计不当会导致后期难以维护和优化
索引是提升查询性能的关键手段，合理的索引可以让查询速度提升百倍甚至千倍
约束确保数据的完整性和一致性，防止脏数据进入数据库
掌握表结构修改是应对需求变更的必备技能

实际应用场景：

新项目启动：设计数据库表结构，选择合适的数据类型和约束
性能优化：为慢查询添加索引，提升系统响应速度
需求变更：修改表结构，添加新字段或调整字段属性
数据规范：通过约束保证数据质量（如唯一性、非空、外键关联）

What（学什么？）

8.1 创建表

CREATE TABLE 表名 (
    字段1 数据类型 [约束],
    字段2 数据类型 [约束],
    PRIMARY KEY (字段),
    FOREIGN KEY (字段) REFERENCES 其他表(字段),
    INDEX 索引名 (字段)  -- 创建表时同时建立索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

完整示例：

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    dept_id INT,
    salary DECIMAL(10,2) DEFAULT 0,
    hire_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (dept_id) REFERENCES departments(id),
    INDEX idx_dept_salary (dept_id, salary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

8.2 约束

NOT NULL           -- 非空约束（该字段不能为NULL）
UNIQUE             -- 唯一约束（该字段值不能重复）
PRIMARY KEY        -- 主键（非空且唯一，一个表只能有一个主键）
FOREIGN KEY        -- 外键（关联其他表，保证引用完整性）
DEFAULT 值         -- 默认值（插入时未指定则使用默认值）
AUTO_INCREMENT     -- 自动递增（通常用于主键，插入时自动+1）
CHECK (条件)       -- 检查约束（MySQL 8.0.16+，确保数据满足条件）

约束的作用：

保证数据完整性：防止无效数据进入数据库
提高数据质量：在数据库层面进行校验，不依赖应用程序
简化开发：很多验证逻辑由数据库自动处理

8.3 修改表结构

-- 添加字段
ALTER TABLE 表名 ADD 字段 数据类型 [约束];
ALTER TABLE 表名 ADD COLUMN age INT DEFAULT 0 AFTER name;

-- 删除字段
ALTER TABLE 表名 DROP COLUMN 字段;

-- 修改字段类型
ALTER TABLE 表名 MODIFY 字段 新数据类型;

-- 修改字段名和类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;

-- 添加/删除约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段);
ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

-- 重命名表
RENAME TABLE 旧表名 TO 新表名;
ALTER TABLE 旧表名 RENAME TO 新表名;

8.4 索引

-- 创建普通索引
CREATE INDEX 索引名 ON 表名(字段);

-- 唯一索引（字段值必须唯一）
CREATE UNIQUE INDEX 索引名 ON 表名(字段);

-- 复合索引（多个字段组合）
CREATE INDEX 索引名 ON 表名(字段1, 字段2, 字段3);

-- 全文索引（用于全文搜索）
CREATE FULLTEXT INDEX 索引名 ON 表名(字段);

-- 删除索引
DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;

-- 查看索引
SHOW INDEX FROM 表名;

索引类型：

普通索引（INDEX）：最基本的索引，无限制
唯一索引（UNIQUE）：索引列值必须唯一，但允许NULL
主键索引（PRIMARY KEY）：特殊的唯一索引，不允许NULL
复合索引：多个字段组合成一个索引，遵循最左前缀原则
全文索引（FULLTEXT）：用于全文搜索，支持中英文检索

索引设计原则：

为WHERE、ORDER BY、GROUP BY、JOIN中频繁使用的字段建索引
选择区分度高的字段（重复值少的字段）
复合索引把区分度高的字段放在前面
不要为小表建索引（全表扫描可能更快）
索引不是越多越好（影响INSERT/UPDATE/DELETE性能）

How（怎么学？）

学习方法：

先设计后建表：在纸上或工具中先设计表结构，考虑清楚再创建
理解范式：学习数据库三范式，理解如何避免数据冗余
索引实验：对比有索引和无索引的查询速度差异
查看执行计划：使用EXPLAIN查看索引是否被使用

常见错误：

❌ 为所有字段都建索引（浪费空间，降低写入性能）
✅ 只为需要的字段建索引，遵循索引设计原则
❌ 选择错误的数据类型（如用VARCHAR存储数字）
✅ 选择合适的数据类型，节省空间提高性能
❌ 主键使用有业务含义的字段（如身份证号）
✅ 主键使用无业务含义的自增ID
❌ 在生产环境直接修改表结构导致锁表
✅ 在业务低峰期修改，或使用在线DDL工具

最佳实践：

数据类型选择：够用就好，VARCHAR优于CHAR，DECIMAL用于金额
主键设计：使用自增INT或BIGINT，不用UUID（占用空间大，无序）
索引命名：使用有意义的名称（如idx_dept_salary）
外键使用：开发环境可以用，生产环境慎用（影响性能和灵活性）
字符集选择：使用utf8mb4支持完整的Unicode字符（包括emoji）

表设计检查清单：

✓ 每个表都有主键
✓ 字段名清晰易懂
✓ 选择合适的数据类型
✓ 设置必要的约束（NOT NULL、UNIQUE等）
✓ 为常用查询字段建立索引
✓ 添加创建时间和更新时间字段

What's next（接下来做什么？）

巩固练习：

完成下方的10道表设计与索引练习题
设计一个完整的业务系统表结构（如博客系统、商城系统）
练习为慢查询添加合适的索引，观察性能提升

进阶准备：

表设计与索引掌握后，下一关将学习性能优化
思考问题：如何分析慢查询？如何优化SQL？如何进行系统性能调优？
预习：了解EXPLAIN、慢查询日志、查询优化技巧等概念

练习题

题目66: 创建一个订单表，包含订单号、客户ID、订单日期、总金额

题目67: 为员工表的name字段添加索引

题目68: 为员工表添加一个'手机号'字段，要求唯一且非空

题目69: 将员工表的salary字段类型改为DECIMAL(12,2)

题目70: 创建一个复合索引(dept_id, salary)

题目71: 查看employees表的所有索引

题目72: 删除employees表中的name索引

题目73: 创建一个商品表，包含自动递增的ID和默认值

题目74: 为订单表和订单详情表建立外键关系

题目75: 修改部门表，添加部门描述字段，默认值为'暂无描述'

第九关：性能优化

知识点

Why（为什么学？）

为什么要学习性能优化？

性能问题是系统上线后最常见的问题，慢查询会严重影响用户体验
随着数据量增长，未优化的SQL可能从毫秒级变成秒级甚至分钟级
掌握性能优化技能可以显著降低服务器成本，提升系统容量
性能优化是高级DBA和后端工程师的核心能力之一

实际应用场景：

系统响应慢：用户抱怨页面加载时间长，需要定位和优化慢查询
数据库压力大：高并发场景下数据库CPU飙升，需要优化查询
报表生成慢：大数据量统计报表耗时过长，影响业务决策
成本控制：通过优化减少数据库资源消耗，降低服务器成本

What（学什么？）

9.1 EXPLAIN分析查询

EXPLAIN SELECT * FROM employees WHERE salary > 5000;

关键字段解读：

id: 查询的执行顺序（id越大越先执行）
select_type: 查询类型（SIMPLE、SUBQUERY、UNION等）
table: 查询的表名
type: 连接类型（性能从好到差）
- system - 表中只有一行（系统表）
- const - 主键或唯一索引查询，最多返回一行
- eq_ref - 主键或唯一索引，用于JOIN
- ref - 非唯一索引查询
- range - 范围查询（>、<、BETWEEN、IN）
- index - 全索引扫描
- ALL - 全表扫描（最差，需优化）
key: 实际使用的索引
rows: 预计扫描的行数（越少越好）
Extra: 额外信息
- Using index - 覆盖索引（好）
- Using where - 使用WHERE过滤（正常）
- Using filesort - 需要额外排序（需优化）
- Using temporary - 使用临时表（需优化）

9.2 查询优化技巧

避免索引失效：

-- ❌ 不好：在索引字段上使用函数导致索引失效
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

-- ✅ 好：使用范围查询，索引生效
SELECT * FROM employees
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

-- ❌ 不好：对索引字段进行运算
WHERE salary * 1.1 > 10000;

-- ✅ 好：将运算放在右侧
WHERE salary > 10000 / 1.1;

**避免SELECT ***：

-- ❌ 不好：查询所有字段，浪费IO
SELECT * FROM employees;

-- ✅ 好：只查询需要的字段
SELECT id, name, salary FROM employees;

合理使用LIMIT：

-- 分页查询时使用LIMIT
SELECT * FROM employees ORDER BY id LIMIT 100;

-- 只需判断是否存在时使用LIMIT 1
SELECT 1 FROM employees WHERE email = '[email protected]' LIMIT 1;

避免在WHERE中使用函数：

-- ❌ 不好：函数导致索引失效
WHERE UPPER(name) = 'ZHANGSAN'

-- ✅ 好：直接比较
WHERE name = 'zhangsan'

优化OR条件：

-- ❌ 不好：OR可能导致索引失效
WHERE dept_id = 1 OR dept_id = 2;

-- ✅ 好：使用IN
WHERE dept_id IN (1, 2);

-- 对于不同字段的OR，考虑用UNION
-- ❌ WHERE dept_id = 1 OR salary > 10000
-- ✅
SELECT * FROM employees WHERE dept_id = 1
UNION
SELECT * FROM employees WHERE salary > 10000;

9.3 索引优化

最左前缀原则：

-- 索引: (dept_id, salary, age)
WHERE dept_id = 1 AND salary > 5000 AND age > 25  -- ✅ 完全使用索引
WHERE dept_id = 1 AND salary > 5000                -- ✅ 使用索引前两列
WHERE dept_id = 1                                  -- ✅ 使用索引第一列
WHERE salary > 5000 AND age > 25                   -- ❌ 不使用索引（跳过第一列）
WHERE dept_id = 1 AND age > 25                     -- ⚠️ 只使用第一列（跳过第二列）

覆盖索引：

-- 创建复合索引
CREATE INDEX idx_name_salary ON employees(name, salary);

-- ✅ 覆盖索引：查询的字段都在索引中，无需回表
SELECT name, salary FROM employees WHERE name = '张三';

-- ❌ 非覆盖索引：需要回表获取age
SELECT name, salary, age FROM employees WHERE name = '张三';

避免索引失效的情况：

LIKE以%开头：WHERE name LIKE '%张三'
不等于操作：WHERE status != 1（考虑改为IN或其他条件）
IS NULL / IS NOT NULL（根据数据分布可能不走索引）
字符串不加引号：WHERE phone = 13800138000（类型转换导致失效）

9.4 其他优化技巧

使用JOIN代替子查询：

-- ❌ 子查询可能被多次执行
SELECT * FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE location = '北京');

-- ✅ JOIN通常性能更好
SELECT e.* FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE d.location = '北京';

分批处理大量数据：

-- ❌ 一次性更新大量数据可能导致锁表
UPDATE employees SET status = 1 WHERE dept_id = 1;

-- ✅ 分批更新，每次1000条
UPDATE employees SET status = 1
WHERE dept_id = 1 AND id BETWEEN 1 AND 1000;
-- 重复执行，更新不同的id范围

定期维护：

-- 分析表，更新统计信息
ANALYZE TABLE employees;

-- 优化表，整理碎片（适用于频繁删除/更新的表）
OPTIMIZE TABLE employees;

使用慢查询日志：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 记录超过2秒的查询

How（怎么学？）

学习方法：

对比实验：准备大量测试数据，对比优化前后的执行时间
看执行计划：养成使用EXPLAIN的习惯，理解查询的执行过程
监控慢查询：开启慢查询日志，分析实际生产环境的慢SQL
基准测试：使用工具（如sysbench）进行性能基准测试

常见错误：

❌ 盲目添加索引，索引越多越好
✅ 根据查询需求合理添加索引，定期清理无用索引
❌ 只看执行时间不看执行计划
✅ 通过EXPLAIN分析，理解性能瓶颈的根本原因
❌ 在生产环境直接执行未经测试的优化
✅ 在测试环境验证优化效果，再应用到生产环境

最佳实践：

建立监控：监控慢查询、数据库CPU、内存、磁盘IO
定期审查：定期审查慢查询日志，持续优化
索引策略：为高频查询建立合适的索引，清理无用索引
分库分表：数据量特别大时考虑分库分表
读写分离：读多写少的场景使用主从复制和读写分离

性能优化思路：

1. 发现问题：通过监控、日志、用户反馈发现慢查询
2. 分析原因：使用EXPLAIN分析执行计划，找出瓶颈
3. 制定方案：根据分析结果制定优化方案（索引、SQL改写、表结构调整）
4. 测试验证：在测试环境验证优化效果
5. 上线观察：上线后持续观察性能指标
6. 总结归档：记录优化过程和效果，积累经验

What's next（接下来做什么？）

巩固练习：

完成下方的5道性能优化练习题
创建大数据量表（百万级），对比不同查询的性能差异
练习使用EXPLAIN分析各种类型的查询

进阶准备：

性能优化掌握后，下一关将进行实战综合练习
思考问题：如何设计一个完整的业务系统？如何应用所学知识解决实际问题？
准备：回顾前面九关的所有知识点，准备综合运用

练习题

题目76: 使用EXPLAIN分析一个复杂查询的执行计划

题目77: 优化查询：查找姓名以'张'开头的员工（考虑索引）

题目78: 改写以下查询以提高性能：

SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

题目79: 创建合适的索引来优化以下查询：

SELECT * FROM employees WHERE dept_id = 5 AND salary > 8000;

题目80: 分析并优化：为什么以下查询很慢？

SELECT * FROM employees WHERE salary * 1.1 > 10000;

第十关：实战综合

知识点

Why（为什么学？）

为什么要进行实战综合练习？

单个知识点的掌握不等于能够解决实际问题，需要综合运用所有技能
实战项目可以帮助你建立完整的知识体系，理解各个知识点之间的关联
通过实际场景的练习，可以发现自己的薄弱环节，有针对性地强化
实战经验是面试和工作中最有价值的资本

实际应用场景：

项目开发：从零开始设计和实现一个完整的数据库系统
系统优化：分析现有系统的性能瓶颈，进行全面优化
数据分析：使用SQL进行复杂的业务数据分析和报表生成
技术面试：应对各种综合性的SQL面试题

What（学什么？）

10.1 综合项目设计思路

电商系统数据库设计示例：

-- 1. 用户表
CREATE TABLE users (
    user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 商品分类表
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(50) NOT NULL,
    parent_id INT DEFAULT 0,
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 商品表
CREATE TABLE products (
    product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    category_id INT,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    INDEX idx_category (category_id),
    INDEX idx_price (price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. 订单表
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status TINYINT DEFAULT 0,  -- 0待支付 1已支付 2已发货 3已完成 4已取消
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_time (order_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. 订单详情表
CREATE TABLE order_items (
    item_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    INDEX idx_order (order_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

10.2 复杂查询场景

场景1：销量排名（窗口函数+分组）：

-- 查询每个类别下销量最高的3个商品
SELECT * FROM (
    SELECT
        p.product_name,
        c.category_name,
        SUM(oi.quantity) AS total_sales,
        RANK() OVER (PARTITION BY p.category_id ORDER BY SUM(oi.quantity) DESC) AS rank
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    JOIN categories c ON p.category_id = c.category_id
    GROUP BY p.product_id, p.product_name, c.category_name, p.category_id
) AS ranked
WHERE rank <= 3;

场景2：用户消费分析（聚合+排名）：

-- 计算每个用户的累计消费金额并排名
SELECT
    u.username,
    SUM(o.total_amount) AS total_spent,
    COUNT(o.order_id) AS order_count,
    RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS spending_rank
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 3  -- 已完成订单
GROUP BY u.user_id, u.username
ORDER BY total_spent DESC;

场景3：推荐系统（自关联+子查询）：

-- 购买过商品A的用户还购买过哪些商品
SELECT
    p.product_name,
    COUNT(DISTINCT oi.order_id) AS purchase_count
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE oi.order_id IN (
    -- 购买过商品A的订单
    SELECT order_id
    FROM order_items
    WHERE product_id = 100
)
AND oi.product_id != 100  -- 排除商品A本身
GROUP BY p.product_id, p.product_name
ORDER BY purchase_count DESC
LIMIT 10;

场景4：复购率分析（CASE WHEN+聚合）：

-- 计算用户复购率（购买2次以上的用户比例）
SELECT
    COUNT(DISTINCT CASE WHEN order_count >= 2 THEN user_id END) * 100.0 /
    COUNT(DISTINCT user_id) AS repurchase_rate
FROM (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE status = 3
    GROUP BY user_id
) AS user_orders;

10.3 性能优化实战

场景：大数据量分页优化：

-- ❌ 传统分页（深度分页时很慢）
SELECT * FROM orders
ORDER BY order_id
LIMIT 100000, 20;

-- ✅ 优化：使用上次查询的最后一条记录的ID
SELECT * FROM orders
WHERE order_id > 100000  -- 上次最后一条的ID
ORDER BY order_id
LIMIT 20;

场景：统计查询优化：

-- ❌ 多次查询
SELECT COUNT(*) FROM orders WHERE status = 0;  -- 待支付
SELECT COUNT(*) FROM orders WHERE status = 1;  -- 已支付
SELECT COUNT(*) FROM orders WHERE status = 2;  -- 已发货

-- ✅ 一次查询完成
SELECT
    status,
    COUNT(*) AS count
FROM orders
GROUP BY status;

-- ✅ 更友好的展示
SELECT
    SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS paid,
    SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS shipped,
    SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) AS completed
FROM orders;

How（怎么学？）

学习方法：

完整项目实践：选择一个熟悉的业务场景（电商、社交、博客等），从零设计数据库
问题驱动：针对每个练习题，先思考解决思路，再动手实现
多方案对比：同一个问题尝试多种实现方式，对比性能和可读性
代码审查：将自己的SQL与参考答案对比，学习更优的写法

常见错误：

❌ 过度设计：一开始就考虑所有可能的场景，导致表结构过于复杂
✅ 敏捷设计：从核心功能开始，逐步迭代和完善
❌ 忽视性能：只关注功能实现，不考虑性能
✅ 性能意识：设计阶段就考虑索引、查询优化
❌ 孤立学习：每个知识点单独练习，不知道如何组合使用
✅ 综合运用：刻意练习多个知识点的组合应用

最佳实践：

设计阶段：先画ER图，明确表之间的关系，再编写DDL
开发阶段：遵循命名规范，添加必要的注释和文档
测试阶段：准备充足的测试数据，测试各种边界情况
优化阶段：使用EXPLAIN分析查询，添加必要的索引
维护阶段：定期备份数据，监控慢查询，持续优化

实战项目建议：

初级项目：
- 图书管理系统（用户、图书、借阅记录）
- 学生成绩管理系统（学生、课程、成绩）

中级项目：
- 博客系统（用户、文章、评论、标签、分类）
- 电商系统（用户、商品、订单、购物车、评价）

高级项目：
- 社交网络（用户、好友关系、动态、点赞、评论）
- 在线教育平台（用户、课程、章节、学习记录、作业）

What's next（接下来做什么？）

巩固练习：

完成下方的10道实战综合练习题
选择一个完整的业务场景，独立设计并实现数据库
为你设计的系统编写至少20个常见业务查询

进阶学习：

进阶主题：
- 事务隔离级别和并发控制
- 分库分表和数据库中间件
- MySQL主从复制和高可用
- 数据库备份与恢复策略
- NoSQL数据库（Redis、MongoDB等）
实战项目：
- 参与开源项目，学习优秀的数据库设计
- 在真实项目中应用所学知识
- 持续优化和改进现有系统
认证与深造：
- 考取MySQL认证（如MySQL DBA认证）
- 学习《高性能MySQL》等经典书籍
- 关注MySQL官方博客和技术社区

恭喜你完成MySQL升级打怪手册的学习！

记住：学习数据库是一个持续的过程，要在实践中不断积累经验。保持好奇心，遇到问题多思考、多实验、多总结。祝你在MySQL的道路上越走越远！

综合练习题

题目81: 设计一个电商系统的数据库，包含用户、商品、订单、订单详情表，并建立适当的关联关系

题目82: 查询每个类别下销量最高的3个商品

题目83: 计算每个用户的累计消费金额，并按消费金额排名

题目84: 查询连续3天都有订单的日期

题目85: 实现一个简单的推荐系统：查询购买过商品A的用户还购买过哪些商品

题目86: 统计每月的销售额环比增长率

题目87: 查询购买过所有类别商品的用户

题目88: 实现分页查询（第3页，每页20条）

题目89: 查询用户的复购率（购买2次以上的用户比例）

题目90: 设计并实现一个简单的权限管理系统（用户-角色-权限）

附录

常用命令速查

-- 数据库操作
SHOW DATABASES;
CREATE DATABASE 数据库名;
USE 数据库名;
DROP DATABASE 数据库名;

-- 表操作
SHOW TABLES;
DESC 表名;
SHOW CREATE TABLE 表名;

-- 用户权限
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
GRANT 权限 ON 数据库.表 TO '用户名'@'主机';
FLUSH PRIVILEGES;

学习建议

循序渐进: 从第一关开始，逐关攻克
动手实践: 每道题都要自己写SQL并执行
理解原理: 不只是记语法，要理解为什么这样写
性能意识: 从一开始就培养性能优化的意识
总结归纳: 遇到问题及时记录和总结

理想赵雷

MySQL升级打怪手册

MySQL升级打怪手册

使用说明

📚 四个W学习法

🎯 使用建议

目录

第一关：基础入门

知识点

Why（为什么学？）

What（学什么？）

1.1 MySQL基本概念

1.2 基本数据类型

1.3 基本SQL语句结构

How（怎么学？）

What's next（接下来做什么？）

练习题

第二关：单表查询

知识点

Why（为什么学？）

What（学什么？）

2.1 SELECT基础语法

2.2 WHERE条件运算符

2.3 排序与限制

2.4 去重与别名

How（怎么学？）

What's next（接下来做什么？）

练习题

第三关：多表联结

知识点

Why（为什么学？）

What（学什么？）

3.1 JOIN类型

3.2 多表连接

3.3 自连接

How（怎么学？）

What's next（接下来做什么？）

练习题

第四关：聚合函数与分组

知识点

Why（为什么学？）

What（学什么？）

4.1 常用聚合函数

4.2 GROUP BY分组

4.3 HAVING过滤

4.4 分组查询执行顺序

How（怎么学？）

What's next（接下来做什么？）

练习题

第五关：子查询

知识点

Why（为什么学？）

What（学什么？）

5.1 子查询类型

5.2 EXISTS子查询

5.3 子查询位置

5.4 关键运算符

How（怎么学？）

What's next（接下来做什么？）

练习题

第六关：高级查询技巧

知识点

Why（为什么学？）

What（学什么？）

6.1 UNION合并查询

6.2 CASE WHEN条件表达式

6.3 窗口函数（MySQL 8.0+）

6.4 常用字符串函数

6.5 常用日期函数

How（怎么学？）

What's next（接下来做什么？）

练习题

第七关：数据操作

知识点

Why（为什么学？）

What（学什么？）

7.1 INSERT插入数据

7.2 UPDATE更新数据

7.3 DELETE删除数据

7.4 事务控制