MySQL升级打怪手册-答案
MySQL升级打怪手册 - 学习指南
引导式学习答案 - 授人以鱼不如授人以渔
使用说明
本学习指南遵循引导式学习原则，通过提示和引导帮助你独立思考和解决问题，而不是直接给出答案。
📖 答案结构说明
每道题目包含以下部分：
- 💡 解题思路 - 分析问题，理解需求
- 🔍 关键提示 - 需要用到的知识点和关键词
- 📝 实现步骤 - 分步骤引导实现
- ✅ 验证方法 - 如何验证答案是否正确
- 🚀 扩展思考 - 其他解法和深入思考
- 💯 参考答案 - 完整答案（建议最后查看）
🎯 学习建议
- 先思考再动手：看完解题思路后，先尝试自己写SQL
- 逐步实现：按照实现步骤一步步完成
- 多种方法：尝试用不同方法解决同一问题
- 验证结果：养成验证结果的习惯
- 扩展延伸：完成后思考扩展问题
第一关：基础入门 - 学习指南
题目1：创建学生表
💡 解题思路
这道题考查的是建表语法。你需要：
- 理解每个字段的含义和数据类型
- 知道如何设置主键和自动递增
- 掌握CREATE TABLE的基本语法
🔍 关键提示
- 主键需要使用
PRIMARY KEY - 自动递增需要使用
AUTO_INCREMENT - 字符串类型使用
VARCHAR(长度) - 整数类型使用
INT - 单字符类型使用
CHAR(1)
📝 实现步骤
- 写出建表语句框架：
CREATE TABLE students ( ... ); - 添加id字段：类型是什么？需要什么约束？
- 添加name字段：应该用什么类型存储最多50字符的姓名？
- 依次添加age、gender、email字段
- 为id字段设置主键和自动递增
✅ 验证方法
-- 创建后用以下命令验证
DESC students;
-- 查看是否有5个字段，每个字段的类型是否正确
🚀 扩展思考
- 如果要让name字段不能为空，应该怎么做？
- 如果要让email字段具有唯一性，应该怎么做？
- 除了VARCHAR，还可以用什么类型存储姓名？各有什么优缺点？
💯 点击查看参考答案
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age INT,
gender CHAR(1),
email VARCHAR(100)
);
要点说明：
INT PRIMARY KEY AUTO_INCREMENT：整型主键，自动递增VARCHAR(50)：可变长字符串，最多50个字符CHAR(1)：固定长度字符串，适合性别这种单字符
题目2：查看表结构
💡 解题思路
这题考查如何查看已创建表的结构。这是调试和验证的重要技能。
🔍 关键提示
- MySQL提供了多个命令可以查看表结构
- 最常用的命令是
DESC（DESCRIBE的缩写） - 还可以使用
SHOW相关命令
📝 实现步骤
- 思考：要查看students表的结构，需要用什么命令？
- 尝试使用
DESC 表名的格式 - 观察输出结果：字段名、类型、是否允许NULL、键类型等
🚀 扩展思考
DESC students和SHOW COLUMNS FROM students有什么区别？- 如何查看建表的完整SQL语句？（提示：
SHOW CREATE TABLE） - 如何查看数据库中所有的表？
💯 点击查看参考答案
-- 方法1：最常用
DESC students;
-- 方法2：更详细
SHOW COLUMNS FROM students;
-- 方法3：查看完整建表语句
SHOW CREATE TABLE students;
输出说明：
- Field：字段名
- Type：数据类型
- Null：是否允许NULL
- Key：是否是键（PRI=主键）
- Default：默认值
- Extra：额外信息（如auto_increment）
题目3：插入学生记录
💡 解题思路
这题考查插入数据的语法。需要注意：
- 一次性插入多条数据比单条插入效率更高
- 字符串值需要用引号
- 字段列表和值列表要一一对应
🔍 关键提示
- 使用
INSERT INTO语句 - 批量插入格式：
VALUES (值1), (值2), (值3) - 字符串需要用单引号包围
- id字段是自动递增的，可以不用填写
📝 实现步骤
- 写出INSERT INTO的基本框架
- 指定要插入的字段（除了id，其他字段都要）
- 使用VALUES添加第一条记录
- 用逗号分隔，继续添加第二、三条记录
- 注意性别应该是'男'还是'女'
✅ 验证方法
-- 插入后查询验证
SELECT * FROM students;
-- 应该看到3条记录
SELECT COUNT(*) FROM students;
-- 应该返回3
🚀 扩展思考
- 如果分3次执行单条INSERT，和一次执行批量INSERT，哪个更快？为什么？
- 如果你想让id从1001开始，应该怎么做？
- 如果插入时某个邮箱重复了会怎样？（如果email有UNIQUE约束）
💯 点击查看参考答案
-- 推荐：批量插入（性能更好）
INSERT INTO students (name, age, gender, email) VALUES
('张三', 20, '男', '[email protected]'),
('李四', 22, '女', '[email protected]'),
('王五', 21, '男', '[email protected]');
-- 或者：单条插入（不推荐，仅作演示）
INSERT INTO students (name, age, gender, email)
VALUES ('张三', 20, '男', '[email protected]');
INSERT INTO students (name, age, gender, email)
VALUES ('李四', 22, '女', '[email protected]');
INSERT INTO students (name, age, gender, email)
VALUES ('王五', 21, '男', '[email protected]');
性能对比：批量插入可以减少与数据库的交互次数，性能提升10倍以上。
题目4：查询姓名和年龄
💡 解题思路
这是最基础的SELECT查询。关键是：
- 只查询需要的字段，不要SELECT *
- 理解字段列表的写法
🔍 关键提示
- 基本语法：
SELECT 字段1, 字段2 FROM 表名; - 多个字段用逗号分隔
- 查询所有记录不需要WHERE条件
📝 实现步骤
- 确定要查询哪些字段？题目要求姓名和年龄
- 确定从哪个表查询？students表
- 组合成完整的SELECT语句
🚀 扩展思考
SELECT name, age和SELECT *有什么区别？- 能否改变查询结果的字段顺序？如
SELECT age, name - 如何给查询结果的列名起别名？（提示：使用AS）
💯 点击查看参考答案
SELECT name, age FROM students;
-- 使用别名让结果更易读
SELECT name AS 姓名, age AS 年龄 FROM students;
最佳实践：
- 只查询需要的字段，避免
SELECT * - 在实际项目中，可以用AS起中文别名让结果更直观
题目5：删除表
💡 解题思路
这题考查删除表的语法。注意：
- 删除表会永久删除所有数据和表结构
- 删除前最好确认一下
🔍 关键提示
- 使用
DROP TABLE语句 - 删除操作是不可逆的，要谨慎
- 可以先用
DESC确认表是否存在
📝 实现步骤
- 回想删除表的关键字是什么？（DROP）
- 写出完整的DROP TABLE语句
- 删除后可以用
SHOW TABLES;验证
✅ 验证方法
-- 删除前查看所有表
SHOW TABLES;
-- 删除
DROP TABLE students;
-- 删除后再次查看，students应该不在列表中
SHOW TABLES;
🚀 扩展思考
DROP TABLE students和DELETE FROM students有什么区别？- 如果想保留表结构只删除数据，应该用什么命令？
- 如何避免误删表？（提示：可以先用IF EXISTS判断）
💯 点击查看参考答案
-- 基本语法
DROP TABLE students;
-- 更安全的写法（如果表存在才删除）
DROP TABLE IF EXISTS students;
对比说明：
DROP TABLE：删除表结构和所有数据，不可恢复DELETE FROM：只删除数据，保留表结构TRUNCATE TABLE：快速清空所有数据，重置自增ID
安全提示：生产环境删除表前一定要备份！
第二关：单表查询 - 学习指南
题目6：查询工资大于5000的员工
💡 解题思路
这题引入了WHERE条件筛选。需要：
- 理解WHERE子句的作用
- 掌握比较运算符的使用
- 明确要查询哪些字段
🔍 关键提示
- 基本结构：
SELECT ... FROM ... WHERE 条件 - 大于号用
> - 题目要求查询"姓名和工资"，不是所有字段
📝 实现步骤
- 确定查询字段：name和salary
- 确定筛选条件：salary > 5000
- 组合成完整SQL语句
- 思考：条件应该放在SELECT后面还是FROM后面？
✅ 验证方法
-- 执行你的查询后，检查结果中是否所有salary都大于5000
-- 可以再查一次看有多少人工资<=5000
SELECT COUNT(*) FROM employees WHERE salary <= 5000;
🚀 扩展思考
- 如果要查询工资大于等于5000，应该怎么改？
- 如果要查询工资在5000到10000之间，有几种写法？
- 查询工资不等于5000的员工，有几种写法？
💯 点击查看参考答案
-- 基本答案
SELECT name, salary FROM employees WHERE salary > 5000;
-- 扩展：使用别名
SELECT name AS 姓名, salary AS 工资
FROM employees
WHERE salary > 5000;
-- 扩展：排序显示（虽然题目没要求，但更实用）
SELECT name, salary
FROM employees
WHERE salary > 5000
ORDER BY salary DESC;
学习要点：
- WHERE子句用于过滤数据
- 比较运算符：
><>=<==!=或<> - 只查询需要的字段可以提升性能
题目7：查询销售部或技术部员工
💡 解题思路
这题考查多条件查询。关键点：
- "或"的关系如何表达？
- 有多种写法，哪种更优雅？
🔍 关键提示
- 逻辑运算符：
OR表示"或"，AND表示"且" IN运算符：字段 IN (值1, 值2, ...)- 两种方法都可以实现，但IN更简洁
📝 实现步骤
- 方法1：使用OR连接两个条件
department = '销售部' OR department = '技术部'
- 方法2：使用IN
department IN ('销售部', '技术部')
- 两种方法都试一试，对比一下哪个更清晰
✅ 验证方法
-- 执行查询后，检查department字段
-- 应该只有'销售部'和'技术部'两种值
SELECT DISTINCT department FROM employees
WHERE department IN ('销售部', '技术部');
🚀 扩展思考
- 如果要查询除了销售部和技术部之外的员工，怎么写？
IN和多个OR在性能上有区别吗？- 如果要查询3个、5个部门，哪种写法更好维护？
💯 点击查看参考答案
-- 方法1：使用IN（推荐，更简洁）
SELECT * FROM employees WHERE department IN ('销售部', '技术部');
-- 方法2：使用OR
SELECT * FROM employees
WHERE department = '销售部' OR department = '技术部';
-- 扩展：排除某些部门
SELECT * FROM employees WHERE department NOT IN ('销售部', '技术部');
-- 扩展：查询特定字段
SELECT name, department, salary
FROM employees
WHERE department IN ('销售部', '技术部');
对比分析：
- IN更简洁，当条件多时优势明显
- OR更直观，但条件多时代码冗长
- 性能上两者基本相同，优先选择可读性好的
题目8：姓名包含'张'的员工
💡 解题思路
这题考查模糊查询。需要掌握：
- LIKE运算符的使用
- 通配符
%的含义 - 与等号的区别
🔍 关键提示
- 使用
LIKE运算符进行模糊匹配 %匹配任意多个字符（包括0个）_匹配单个字符- 题目要求"包含"，所以前后都需要%
📝 实现步骤
- 思考：
name = '张'能满足要求吗？为什么不能？ - 改用LIKE：
name LIKE '张%'是什么意思？ name LIKE '%张'和name LIKE '%张%'有什么区别？- 哪个能满足"包含"的要求？
✅ 验证方法
-- 查询后检查结果
-- '张三'、'小张'、'张'、'王张李'都应该被查出来
🚀 扩展思考
'张%'、'%张'、'%张%'三种写法各匹配什么样的数据？- 如何查询姓张的员工（张在最前面）？
- 如何查询名字是三个字且第二个字是张的员工？（提示：用
_） - 模糊查询对索引有什么影响？
💯 点击查看参考答案
-- 基本答案：包含'张'
SELECT * FROM employees WHERE name LIKE '%张%';
-- 扩展：姓张（以张开头）
SELECT * FROM employees WHERE name LIKE '张%';
-- 扩展：名字最后一个字是张
SELECT * FROM employees WHERE name LIKE '%张';
-- 扩展：三个字且第二个字是张
SELECT * FROM employees WHERE name LIKE '_张_';
通配符说明：
%：匹配0个或多个字符'张%'→ 张三、张小明'%张'→ 小张、老张'%张%'→ 张三、小张、王张李
_：匹配单个字符'_张_'→ 李张三'张__'→ 张小明（张+两个字符）
性能提示：'%张%' 和 '%张' 无法使用索引，会全表扫描
题目9：工资8000-15000，降序排列
💡 解题思路
这题同时考查：
- 范围查询：如何表示"在...之间"
- 排序：如何指定排序字段和顺序
🔍 关键提示
- 范围查询：
BETWEEN ... AND ...（包含边界值） - 排序：
ORDER BY 字段 DESC（DESC表示降序） - 两个子句要按顺序：WHERE → ORDER BY
📝 实现步骤
- 先写WHERE条件：salary在8000到15000之间
- 可以用：
BETWEEN 8000 AND 15000 - 也可以用：
salary >= 8000 AND salary <= 15000
- 可以用：
- 再加排序：
ORDER BY salary DESC - 注意顺序：WHERE在前，ORDER BY在后
✅ 验证方法
-- 执行后检查：
-- 1. 第一条记录的工资应该最高（接近15000）
-- 2. 最后一条记录的工资应该最低（接近8000）
-- 3. 所有记录的工资都在8000-15000之间
🚀 扩展思考
- BETWEEN和两个比较运算符哪个更好？
- 如果要升序排列呢？ASC还是DESC？
- 如果工资相同时，如何按姓名再排序？（提示：ORDER BY多个字段）
- 能否先排序再筛选？顺序能颠倒吗？
💯 点击查看参考答案
-- 方法1：使用BETWEEN（推荐，更简洁）
SELECT * FROM employees
WHERE salary BETWEEN 8000 AND 15000
ORDER BY salary DESC;
-- 方法2：使用比较运算符
SELECT * FROM employees
WHERE salary >= 8000 AND salary <= 15000
ORDER BY salary DESC;
-- 扩展：工资相同时按姓名排序
SELECT * FROM employees
WHERE salary BETWEEN 8000 AND 15000
ORDER BY salary DESC, name ASC;
-- 扩展：只查询需要的字段
SELECT name, salary, department
FROM employees
WHERE salary BETWEEN 8000 AND 15000
ORDER BY salary DESC;
语法顺序（重要）：
SELECT → FROM → WHERE → ORDER BY → LIMIT
排序说明：
ASC：升序（从小到大），默认值DESC：降序（从大到小）- 多字段排序：
ORDER BY 字段1 DESC, 字段2 ASC
题目10：2020年后入职的前5名员工
💡 解题思路
这题综合考查：
- 日期比较
- LIMIT限制结果数量
- 完整的查询语句结构
🔍 关键提示
- 日期比较：
hire_date > '2020-12-31'或使用YEAR函数 - 限制数量：
LIMIT 5 - 可能需要排序：按入职日期排序更合理
📝 实现步骤
- 确定日期条件：2020年之后是指2021-01-01开始
- 方法1：
hire_date > '2020-12-31' - 方法2：
hire_date >= '2021-01-01' - 方法3：
YEAR(hire_date) > 2020
- 方法1：
- 添加LIMIT限制：
LIMIT 5 - 思考是否需要排序？按什么排序？
✅ 验证方法
-- 检查结果的hire_date是否都在2020年之后
-- 检查是否只返回5条记录
SELECT COUNT(*) FROM (...你的查询...) AS result; -- 应该是5
🚀 扩展思考
- 这三种日期比较方法性能有区别吗？
- 如果要查询"2020年（含）之后"，条件应该怎么改？
LIMIT 5和LIMIT 0, 5有区别吗？- 如果要查询第6-10名，LIMIT应该怎么写？
💯 点击查看参考答案
-- 方法1：日期字符串比较（推荐，性能最好）
SELECT * FROM employees
WHERE hire_date > '2020-12-31'
ORDER BY hire_date
LIMIT 5;
-- 或者
SELECT * FROM employees
WHERE hire_date >= '2021-01-01'
ORDER BY hire_date
LIMIT 5;
-- 方法2：使用YEAR函数（可读性好，但性能稍差）
SELECT * FROM employees
WHERE YEAR(hire_date) > 2020
ORDER BY hire_date
LIMIT 5;
-- 扩展：分页查询（第2页，每页5条）
SELECT * FROM employees
WHERE hire_date > '2020-12-31'
ORDER BY hire_date
LIMIT 5 OFFSET 5; -- 等同于 LIMIT 5, 5
日期比较最佳实践：
- ✅ 使用日期字符串：
hire_date > '2020-12-31'（可以使用索引） - ❌ 使用函数：
YEAR(hire_date) > 2020（索引失效）
LIMIT语法：
LIMIT 5：返回前5条LIMIT 5, 10：从第6条开始，返回10条（偏移5）LIMIT 10 OFFSET 5：同上，更清晰的写法
题目11-15：其他单表查询题目
💡 学习提示：后续题目请按照相同的思路：
- 先分析题目要求
- 确定需要用到的知识点
- 尝试自己编写SQL
- 验证结果是否正确
- 查看参考答案对比学习
（由于篇幅限制，这里提供关键提示，完整学习指南将继续补充...）
题目11：邮箱为空 - 关键词：IS NULL
题目12：去重查询 - 关键词：DISTINCT
题目13：工资最高的3名 - 关键词：ORDER BY ... DESC LIMIT 3
题目14：姓李且工资>6000 - 关键词：LIKE '李%' AND
题目15：部门不是财务部 - 关键词：!= 或 <>
第三关：多表联结
题目16：查询所有员工及其所在部门名称
💡 解题思路
这是最基础的INNER JOIN题目。需要理解：
- 两表如何通过共同字段关联
- INNER JOIN只返回两表都有匹配的记录
- 如何使用表别名简化SQL
🔍 关键提示
- 需要关联两个表：
employees和departments - 关联字段：
employees.dept_id = departments.dept_id - 使用表别名：
employees e和departments d - 语法：
FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id
📝 实现步骤
- 先思考：需要哪些表？它们通过什么字段关联？
- 确定SELECT字段：员工姓名来自employees表，部门名称来自departments表
- 写JOIN语句：
FROM employees INNER JOIN departments - 添加ON条件：两表通过dept_id关联
- 使用表别名让SQL更简洁
✅ 验证方法
-- 检查结果
-- 1. 每个员工都应该有对应的部门名称
-- 2. 如果某员工没有部门（dept_id为NULL），INNER JOIN不会显示该员工
-- 3. 可以用COUNT验证返回的记录数
SELECT COUNT(*) FROM employees WHERE dept_id IS NOT NULL;
🚀 扩展思考
- 如果要显示所有员工（包括没有部门的），应该用什么JOIN？
- INNER JOIN 和 WHERE 子句的顺序能颠倒吗？
- 如果两个表的关联字段名称相同，可以用
USING(dept_id)代替ON - 如果不写ON条件会发生什么？（笛卡尔积）
💯 点击查看参考答案
-- 方法1：使用表别名（推荐）
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- 方法2：不使用别名（可读性差）
SELECT employees.emp_name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;
-- 方法3：使用USING（当关联字段名相同时）
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d USING(dept_id);
-- 方法4：隐式JOIN（不推荐，WHERE子句混合了过滤和关联条件）
SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;
要点说明：
INNER JOIN只返回两表都匹配的记录- 使用表别名（
e、d）让SQL更简洁 ON指定关联条件，类似WHERE但专门用于JOIN- 推荐使用显式JOIN语法（方法1），而不是隐式JOIN（方法4）
题目17：查询所有部门及其员工数量（包括没有员工的部门）
💡 解题思路
这题同时考查：
- LEFT JOIN：需要保留左表（departments）所有记录
- 聚合函数COUNT：统计每个部门的员工数
- GROUP BY：按部门分组统计
🔍 关键提示
- 使用
LEFT JOIN保留所有部门 - 使用
COUNT(e.emp_id)统计员工数（不要用COUNT(*)） - 使用
GROUP BY d.dept_id按部门分组 - 没有员工的部门，COUNT应该返回0
📝 实现步骤
- 确定主表：departments（因为要保留所有部门）
- 选择JOIN类型：LEFT JOIN（保留左表所有记录）
- 添加COUNT函数：统计每个部门的员工数
- 添加GROUP BY：按部门分组
- 思考：为什么用COUNT(e.emp_id)而不是COUNT(*)？
✅ 验证方法
-- 验证步骤
-- 1. 检查是否所有部门都出现在结果中
SELECT COUNT(*) FROM departments; -- 与结果行数比较
-- 2. 手动检查某个部门的员工数
SELECT COUNT(*) FROM employees WHERE dept_id = 1;
-- 3. 检查没有员工的部门是否显示为0
🚀 扩展思考
- 为什么要用
COUNT(e.emp_id)而不是COUNT(*)？ - 如果用 INNER JOIN 会有什么问题？
- 如果只想显示有员工的部门，应该怎么改？
HAVING COUNT(e.emp_id) > 0和 WHERE 的区别是什么？
💯 点击查看参考答案
-- 标准答案
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;
-- 或者（如果只需要部门名称分组）
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;
要点说明：
- LEFT JOIN：保留左表（departments）所有记录，右表没有匹配的显示NULL
- COUNT(e.emp_id)：统计具体字段，NULL不计数。没有员工的部门返回0
- COUNT(*)：统计所有行，即使e表字段全是NULL也会计数为1（错误）
- GROUP BY：必须包含SELECT中的非聚合字段
错误示范：
-- ❌ 使用COUNT(*)会导致没有员工的部门也显示为1
SELECT d.dept_name, COUNT(*) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;
-- ❌ 使用INNER JOIN会丢失没有员工的部门
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM departments d
INNER JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;
题目18：查询每个员工及其直属上级的姓名
💡 解题思路
这是自连接（Self Join）的经典应用：
- 同一个表（employees）与自己连接
- 通过 manager_id 和 emp_id 建立关联
- 需要使用不同的表别名区分员工和经理
🔍 关键提示
- 自连接：
FROM employees e LEFT JOIN employees m - 两个别名：
e（员工）和m（经理/manager） - 关联条件：
e.manager_id = m.emp_id - 使用LEFT JOIN：因为最高领导没有上级（manager_id为NULL）
📝 实现步骤
- 理解表结构：employees表中，manager_id 指向另一个员工的 emp_id
- 使用两个别名：e代表员工，m代表经理
- 写JOIN：
FROM employees e LEFT JOIN employees m - 添加ON条件：
ON e.manager_id = m.emp_id - 思考：为什么用LEFT JOIN而不是INNER JOIN？
✅ 验证方法
-- 验证步骤
-- 1. 检查最高领导（没有上级）是否也显示在结果中
SELECT * FROM employees WHERE manager_id IS NULL;
-- 2. 手动验证某个员工的上级是否正确
SELECT e.emp_name, e.manager_id, m.emp_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id
WHERE e.emp_name = '某员工名';
🚀 扩展思考
- 如果要查询每个经理及其所有下属，应该怎么写？
- 如何查询某个员工的所有上级（多级）？需要递归查询
- 如果用INNER JOIN会丢失谁？
- 能否用RIGHT JOIN？效果有何不同？
💯 点击查看参考答案
-- 方法1：使用LEFT JOIN（推荐，显示所有员工）
SELECT
e.emp_name AS employee_name,
m.emp_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
-- 方法2：使用INNER JOIN（会丢失最高领导）
SELECT
e.emp_name AS employee_name,
m.emp_name AS manager_name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.emp_id;
-- 方法3：使用COALESCE处理NULL（优化显示）
SELECT
e.emp_name AS employee_name,
COALESCE(m.emp_name, '无上级') AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
要点说明：
- 自连接：同一张表使用不同别名与自己连接
- LEFT JOIN：保留所有员工，包括没有上级的（最高领导）
- 别名含义：
e表示 employee（员工），m表示 manager（经理） - 关联逻辑：员工的 manager_id 对应经理的 emp_id
场景对比：
LEFT JOIN：显示所有员工，最高领导的上级显示为NULLINNER JOIN：只显示有上级的员工，最高领导不会出现RIGHT JOIN：实际上没有意义（等同于把e和m位置互换后用LEFT JOIN）
题目19：查询参与项目的员工姓名、部门名称和项目名称
💡 解题思路
这是三表连接的经典案例：
- 需要连接3个表：employees、departments、projects
- 还需要中间表 emp_projects 来建立员工和项目的多对多关系
- 总共4个表的连接
🔍 关键提示
- 连接4个表的顺序：employees → departments → emp_projects → projects
- 第一个JOIN：
employees e INNER JOIN departments d ON e.dept_id = d.dept_id - 第二个JOIN：
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id - 第三个JOIN：
INNER JOIN projects p ON ep.project_id = p.project_id
📝 实现步骤
- 确定主表：employees（员工是查询的主体）
- 第一次连接：关联 departments 获取部门名称
- 第二次连接：关联 emp_projects 获取员工参与的项目
- 第三次连接：关联 projects 获取项目名称
- 思考：这里为什么都用INNER JOIN？
✅ 验证方法
-- 验证步骤
-- 1. 检查是否有重复的员工（一个员工参与多个项目会有多行）
SELECT emp_name, COUNT(*)
FROM (...你的查询...) AS result
GROUP BY emp_name;
-- 2. 手动验证某个员工参与的项目数
SELECT COUNT(*) FROM emp_projects WHERE emp_id = 1;
🚀 扩展思考
- 如果要包括没有参与项目的员工，哪个JOIN要改成LEFT JOIN?
- 如果要同时显示员工的角色（role），需要从哪个表获取？
- 四个表的连接顺序可以调整吗？
- 如何优化这个查询的性能？
💯 点击查看参考答案
-- 方法1：标准四表连接
SELECT
e.emp_name,
d.dept_name,
p.project_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
INNER JOIN projects p ON ep.project_id = p.project_id;
-- 方法2：同时显示员工在项目中的角色
SELECT
e.emp_name,
d.dept_name,
p.project_name,
ep.role
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
INNER JOIN projects p ON ep.project_id = p.project_id;
-- 方法3：包括没有参与项目的员工
SELECT
e.emp_name,
d.dept_name,
p.project_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN emp_projects ep ON e.emp_id = ep.emp_id
LEFT JOIN projects p ON ep.project_id = p.project_id;
要点说明：
- 多表连接顺序：从主表开始，逐步连接相关表
- 中间表：emp_projects 是多对多关系的桥梁表
- INNER JOIN：只显示参与了项目的员工
- 一个员工多行：如果员工参与多个项目，会产生多行记录
连接路径：
employees (员工)
→ departments (部门) 通过 dept_id
→ emp_projects (员工项目关联) 通过 emp_id
→ projects (项目) 通过 project_id
题目20：查询没有参与任何项目的员工
💡 解题思路
这题考查LEFT JOIN + IS NULL的经典用法：
- 使用LEFT JOIN连接员工和项目关联表
- 筛选右表为NULL的记录（即没有匹配的）
- 理解"找不存在的"这类问题的通用解法
🔍 关键提示
- 使用LEFT JOIN：
FROM employees e LEFT JOIN emp_projects ep - WHERE条件：
ep.emp_id IS NULL（项目关联表中没有该员工的记录） - LEFT JOIN后，没有匹配的记录右表字段都是NULL
📝 实现步骤
- 思考："没有参与项目"意味着在 emp_projects 表中找不到该员工
- 使用LEFT JOIN保留所有员工
- 添加WHERE条件筛选右表为NULL的记录
- 理解：为什么不能用INNER JOIN？
✅ 验证方法
-- 验证步骤
-- 1. 检查这些员工在emp_projects表中是否真的不存在
SELECT emp_id FROM emp_projects WHERE emp_id IN (
-- 你的查询结果的emp_id列表
); -- 应该返回空
-- 2. 反向验证：查询参与了项目的员工
SELECT DISTINCT e.emp_id, e.emp_name
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id;
🚀 扩展思考
- 能否用子查询 NOT IN 实现？哪种性能更好？
- 能否用 NOT EXISTS 实现？
- 如果要查询"只参与了1个项目"的员工呢？
- LEFT JOIN + IS NULL 这个模式可以用在哪些场景？
💯 点击查看参考答案
-- 方法1：LEFT JOIN + IS NULL（推荐，性能好）
SELECT e.emp_id, e.emp_name
FROM employees e
LEFT JOIN emp_projects ep ON e.emp_id = ep.emp_id
WHERE ep.emp_id IS NULL;
-- 方法2：NOT IN 子查询（注意NULL值陷阱）
SELECT emp_id, emp_name
FROM employees
WHERE emp_id NOT IN (
SELECT emp_id FROM emp_projects WHERE emp_id IS NOT NULL
);
-- 方法3：NOT EXISTS 子查询（性能较好）
SELECT e.emp_id, e.emp_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM emp_projects ep WHERE ep.emp_id = e.emp_id
);
-- 方法4：EXCEPT（MySQL 8.0+支持）
SELECT emp_id, emp_name FROM employees
EXCEPT
SELECT e.emp_id, e.emp_name
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id;
要点说明：
- LEFT JOIN + IS NULL：查找"不存在"的经典模式
- 为什么用LEFT JOIN：保留左表所有记录，右表没匹配的为NULL
- WHERE ep.emp_id IS NULL：筛选出没有参与项目的员工
NOT IN的陷阱：
-- ❌ 危险：如果子查询结果包含NULL，整个查询返回空
WHERE emp_id NOT IN (SELECT emp_id FROM emp_projects)
-- ✅ 安全：过滤掉NULL
WHERE emp_id NOT IN (SELECT emp_id FROM emp_projects WHERE emp_id IS NOT NULL)
性能对比：
- LEFT JOIN + IS NULL ≈ NOT EXISTS > NOT IN（在有索引的情况下）
- NOT IN 在子查询结果包含NULL时有逻辑陷阱
题目21：查询同时参与2个以上项目的员工姓名
💡 解题思路
这题结合了：
- 多表连接：员工表和项目关联表
- 分组统计：COUNT统计每个员工的项目数
- HAVING筛选：对分组后的结果进行过滤
🔍 关键提示
- 连接表：
employees e INNER JOIN emp_projects ep - 分组：
GROUP BY e.emp_id, e.emp_name - 统计：
COUNT(ep.project_id) AS project_count - 筛选：
HAVING COUNT(ep.project_id) > 2
📝 实现步骤
- 连接employees和emp_projects表
- 按员工分组（GROUP BY）
- 使用COUNT统计每个员工参与的项目数
- 使用HAVING筛选项目数>2的员工
- 思考：为什么用HAVING而不是WHERE？
✅ 验证方法
-- 验证某个员工的项目数
SELECT emp_id, COUNT(*) AS project_count
FROM emp_projects
WHERE emp_id = 某个员工id
GROUP BY emp_id;
-- 查看所有员工的项目数分布
SELECT
e.emp_name,
COUNT(ep.project_id) AS project_count
FROM employees e
LEFT JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
ORDER BY project_count DESC;
🚀 扩展思考
- HAVING 和 WHERE 的区别是什么？
- 如果要查询"恰好参与2个项目"的员工呢？
- 如果要同时显示这些员工参与的所有项目名称呢？
- COUNT(ep.project_id) 和 COUNT(*) 在这里有区别吗？
💯 点击查看参考答案
-- 方法1：标准答案
SELECT
e.emp_name,
COUNT(ep.project_id) AS project_count
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
HAVING COUNT(ep.project_id) > 2;
-- 方法2：只显示员工姓名（不显示项目数）
SELECT e.emp_name
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
HAVING COUNT(ep.project_id) > 2;
-- 方法3：同时显示参与的项目名称（需要子查询或窗口函数）
SELECT
e.emp_name,
COUNT(ep.project_id) AS project_count,
GROUP_CONCAT(p.project_name) AS projects
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
INNER JOIN projects p ON ep.project_id = p.project_id
GROUP BY e.emp_id, e.emp_name
HAVING COUNT(ep.project_id) > 2;
要点说明：
- HAVING vs WHERE：
- WHERE：过滤原始数据行（分组前）
- HAVING：过滤分组后的结果（可以使用聚合函数）
- GROUP BY：按员工分组，每个员工一行
- COUNT：统计每个员工参与的项目数
错误示范：
-- ❌ 不能在WHERE中使用聚合函数
SELECT e.emp_name
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
WHERE COUNT(ep.project_id) > 2 -- 错误！
GROUP BY e.emp_id, e.emp_name;
-- ✅ 必须用HAVING
HAVING COUNT(ep.project_id) > 2;
扩展：查询恰好2个项目的员工
HAVING COUNT(ep.project_id) = 2;
题目22：查询'技术部'员工参与的所有项目名称
💡 解题思路
这题需要：
- 多表连接（4个表）
- WHERE条件筛选特定部门
- 可能需要DISTINCT去重
🔍 关键提示
- 连接路径：employees → departments → emp_projects → projects
- WHERE条件：
d.dept_name = '技术部' - 可能需要：
DISTINCT去重（多个技术部员工参与同一项目） - 选择字段：只需要项目名称
p.project_name
📝 实现步骤
- 从employees表开始，依次连接其他表
- 添加WHERE条件筛选部门名称
- SELECT只选择项目名称
- 思考：是否需要DISTINCT？为什么？
✅ 验证方法
-- 先查询技术部有哪些员工
SELECT e.emp_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = '技术部';
-- 再查询这些员工参与的项目
SELECT DISTINCT p.project_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
INNER JOIN projects p ON ep.project_id = p.project_id
WHERE d.dept_name = '技术部';
🚀 扩展思考
- 如果不用DISTINCT会发生什么？
- 如果要同时显示每个项目有多少技术部员工参与呢？
- 能否用子查询实现？
- 如果要查询"只有技术部员工参与"的项目呢？
💯 点击查看参考答案
-- 方法1：使用DISTINCT去重（推荐）
SELECT DISTINCT p.project_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
INNER JOIN projects p ON ep.project_id = p.project_id
WHERE d.dept_name = '技术部';
-- 方法2：使用GROUP BY去重
SELECT p.project_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
INNER JOIN projects p ON ep.project_id = p.project_id
WHERE d.dept_name = '技术部'
GROUP BY p.project_id, p.project_name;
-- 方法3：同时显示每个项目的技术部参与人数
SELECT
p.project_name,
COUNT(DISTINCT e.emp_id) AS tech_emp_count,
GROUP_CONCAT(DISTINCT e.emp_name) AS tech_employees
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
INNER JOIN projects p ON ep.project_id = p.project_id
WHERE d.dept_name = '技术部'
GROUP BY p.project_id, p.project_name;
-- 方法4：使用子查询
SELECT DISTINCT p.project_name
FROM projects p
INNER JOIN emp_projects ep ON p.project_id = ep.project_id
WHERE ep.emp_id IN (
SELECT e.emp_id
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = '技术部'
);
要点说明：
- 为什么需要DISTINCT：如果多个技术部员工参与同一项目，项目名会重复
- DISTINCT vs GROUP BY：都能去重，DISTINCT更简洁，GROUP BY可以配合聚合函数
- 连接顺序：employees → departments（筛选部门）→ emp_projects → projects
场景对比：
-- 不用DISTINCT：可能有重复项目
SELECT p.project_name FROM ... -- 项目A, 项目A, 项目B
-- 用DISTINCT：去重后的结果
SELECT DISTINCT p.project_name FROM ... -- 项目A, 项目B
题目23：查询与'张三'在同一部门的其他员工
💡 解题思路
这题需要：
- 子查询：先查出张三的部门ID
- 自连接或WHERE筛选：查询同部门的其他员工
- 排除自己：WHERE条件排除张三本人
🔍 关键提示
- 方法1：子查询
WHERE dept_id = (SELECT dept_id FROM employees WHERE emp_name = '张三') - 方法2：自连接
FROM employees e1 JOIN employees e2 ON e1.dept_id = e2.dept_id - 排除张三：
WHERE emp_name != '张三'或WHERE e1.emp_id != e2.emp_id
📝 实现步骤
- 先写子查询：查询张三的dept_id
- 在主查询中使用这个dept_id作为条件
- 添加条件排除张三本人
- 思考：这道题有几种不同的实现方法？
✅ 验证方法
-- 先查张三的部门
SELECT dept_id FROM employees WHERE emp_name = '张三';
-- 然后手动查询该部门的所有员工
SELECT * FROM employees WHERE dept_id = (张三的部门ID);
-- 验证结果中不包含张三
🚀 扩展思考
- 子查询和自连接两种方法哪种性能更好？
- 如果有多个叫"张三"的员工怎么办？
- 如果张三不存在，查询会返回什么？
- 能否用EXISTS实现？
💯 点击查看参考答案
-- 方法1：使用子查询（推荐，简洁清晰）
SELECT emp_id, emp_name, dept_id
FROM employees
WHERE dept_id = (
SELECT dept_id FROM employees WHERE emp_name = '张三'
)
AND emp_name != '张三';
-- 方法2：使用自连接
SELECT e1.emp_id, e1.emp_name, e1.dept_id
FROM employees e1
INNER JOIN employees e2 ON e1.dept_id = e2.dept_id
WHERE e2.emp_name = '张三'
AND e1.emp_name != '张三';
-- 或者用emp_id排除（更严谨）
SELECT e1.emp_id, e1.emp_name, e1.dept_id
FROM employees e1
INNER JOIN employees e2 ON e1.dept_id = e2.dept_id
WHERE e2.emp_name = '张三'
AND e1.emp_id != e2.emp_id;
-- 方法3：使用EXISTS（性能较好）
SELECT e1.emp_id, e1.emp_name, e1.dept_id
FROM employees e1
WHERE EXISTS (
SELECT 1 FROM employees e2
WHERE e2.emp_name = '张三'
AND e1.dept_id = e2.dept_id
AND e1.emp_id != e2.emp_id
);
-- 方法4：先查部门ID再查询（分步骤）
-- 第一步
SET @zhang_dept = (SELECT dept_id FROM employees WHERE emp_name = '张三');
-- 第二步
SELECT emp_id, emp_name, dept_id
FROM employees
WHERE dept_id = @zhang_dept
AND emp_name != '张三';
要点说明：
- 子查询：先查出张三的部门ID，再用这个ID查同部门员工
- 排除自己：可以用姓名（
!= '张三'）或ID（!= e2.emp_id） - 多个张三的问题：如果有同名者，用emp_id排除更准确
潜在问题：
-- 如果张三不存在，子查询返回NULL
WHERE dept_id = NULL -- 永远为FALSE，查不到任何结果
-- 解决方案：检查张三是否存在
SELECT emp_id, emp_name, dept_id
FROM employees
WHERE dept_id = (
SELECT dept_id FROM employees WHERE emp_name = '张三'
)
AND emp_name != '张三'
AND EXISTS (SELECT 1 FROM employees WHERE emp_name = '张三');
题目24：查询每个部门工资最高的员工信息
💡 解题思路
这是经典的分组取最大值问题：
- 需要先找出每个部门的最高工资
- 然后根据部门和工资找到对应的员工
- 可以用子查询或窗口函数实现
🔍 关键提示
- 方法1：子查询
WHERE (dept_id, salary) IN (SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id) - 方法2：自连接 + GROUP BY
- 方法3：窗口函数
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC)
📝 实现步骤
- 先思考：需要先找出每个部门的最高工资
- 子查询：
SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id - 用(dept_id, salary)组合作为条件匹配
- 思考：如果一个部门有多个人都是最高工资怎么办？
✅ 验证方法
-- 先查看每个部门的最高工资
SELECT dept_id, MAX(salary) AS max_salary
FROM employees
GROUP BY dept_id;
-- 验证查询结果中每个部门只有一个（或多个同工资的）员工
SELECT dept_id, COUNT(*)
FROM (...你的查询...) AS result
GROUP BY dept_id;
🚀 扩展思考
- 如果一个部门有多个员工工资并列最高，都应该显示吗？
- 子查询和窗口函数两种方法哪种更好？
- 如果要查询每个部门工资前3名呢？
- 能否用LEFT JOIN实现？
💯 点击查看参考答案
-- 方法1：使用子查询（推荐，兼容性好）
SELECT e.emp_id, e.emp_name, e.dept_id, e.salary
FROM employees e
WHERE (e.dept_id, e.salary) IN (
SELECT dept_id, MAX(salary)
FROM employees
GROUP BY dept_id
);
-- 方法2：使用自连接
SELECT e.*
FROM employees e
INNER JOIN (
SELECT dept_id, MAX(salary) AS max_salary
FROM employees
GROUP BY dept_id
) dept_max ON e.dept_id = dept_max.dept_id AND e.salary = dept_max.max_salary;
-- 方法3：使用窗口函数（MySQL 8.0+，推荐）
SELECT emp_id, emp_name, dept_id, salary
FROM (
SELECT
emp_id, emp_name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
FROM employees
) ranked
WHERE rk = 1;
-- 或者使用ROW_NUMBER（只取一个最高工资员工）
SELECT emp_id, emp_name, dept_id, salary
FROM (
SELECT
emp_id, emp_name, dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees
) ranked
WHERE rn = 1;
-- 方法4：同时显示部门名称
SELECT e.emp_id, e.emp_name, d.dept_name, e.salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE (e.dept_id, e.salary) IN (
SELECT dept_id, MAX(salary)
FROM employees
GROUP BY dept_id
);
要点说明：
- (dept_id, salary) IN：组合条件匹配，同时满足部门和工资
- 并列最高工资：
- 子查询方法：会返回所有并列最高的员工
RANK()：会返回所有并列最高的员工ROW_NUMBER()：只返回一个（按ORDER BY的第一个）
窗口函数解释：
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)
-- PARTITION BY dept_id: 按部门分组
-- ORDER BY salary DESC: 每组内按工资降序
-- RANK(): 排名，并列的排名相同
RANK vs ROW_NUMBER：
RANK()：1, 1, 3, 4（有并列，跳号）ROW_NUMBER()：1, 2, 3, 4（没有并列，连续）
扩展：查询每个部门工资前3名
WHERE rk <= 3
题目25：查询没有下属的员工（不是任何人的经理）
💡 解题思路
这又是一个查找不存在的问题：
- 如果员工是经理，其emp_id会出现在其他员工的manager_id中
- 使用LEFT JOIN + IS NULL查找不在manager_id中的员工
- 或者使用NOT IN / NOT EXISTS
🔍 关键提示
- 方法1：LEFT JOIN
FROM employees e LEFT JOIN employees m ON e.emp_id = m.manager_id WHERE m.emp_id IS NULL - 方法2：NOT IN
WHERE emp_id NOT IN (SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL) - 方法3：NOT EXISTS
WHERE NOT EXISTS (SELECT 1 FROM employees e2 WHERE e2.manager_id = e1.emp_id)
📝 实现步骤
- 理解题意：没有下属 = 自己的emp_id不在任何人的manager_id中
- 使用LEFT JOIN将员工表自连接（e.emp_id = m.manager_id）
- 筛选右表为NULL的记录
- 思考：这道题和题目20的思路是否相似？
✅ 验证方法
-- 查看所有经理（有下属的员工）
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
-- 验证查询结果中的员工ID是否都不在上述列表中
SELECT emp_id FROM (...你的查询...) AS result
WHERE emp_id IN (
SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL
); -- 应该返回空
🚀 扩展思考
- 为什么NOT IN子查询中要过滤NULL？
- LEFT JOIN和NOT EXISTS哪个性能更好？
- 如果要查询"下属人数最多的经理"呢？
- 能否同时显示员工的部门信息？
💯 点击查看参考答案
-- 方法1：LEFT JOIN + IS NULL（推荐）
SELECT e.emp_id, e.emp_name
FROM employees e
LEFT JOIN employees subordinate ON e.emp_id = subordinate.manager_id
WHERE subordinate.emp_id IS NULL;
-- 方法2：NOT IN（注意NULL陷阱）
SELECT emp_id, emp_name
FROM employees
WHERE emp_id NOT IN (
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
-- 方法3：NOT EXISTS（性能较好）
SELECT e1.emp_id, e1.emp_name
FROM employees e1
WHERE NOT EXISTS (
SELECT 1
FROM employees e2
WHERE e2.manager_id = e1.emp_id
);
-- 方法4：使用EXCEPT（MySQL 8.0+）
SELECT emp_id, emp_name FROM employees
EXCEPT
SELECT DISTINCT e.emp_id, e.emp_name
FROM employees e
INNER JOIN employees subordinate ON e.emp_id = subordinate.manager_id;
-- 扩展：同时显示部门信息
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN employees subordinate ON e.emp_id = subordinate.manager_id
WHERE subordinate.emp_id IS NULL;
要点说明：
- LEFT JOIN逻辑：将员工表与自己连接，关联条件是 e.emp_id = subordinate.manager_id
- 如果某员工是经理，右表会有匹配记录（他的下属）
- 如果某员工不是经理，右表为NULL
- NOT IN的NULL陷阱：必须过滤NULL，否则查询返回空
NOT IN的NULL问题详解：
-- ❌ 危险写法
WHERE emp_id NOT IN (SELECT manager_id FROM employees)
-- 如果manager_id有NULL值：
-- emp_id NOT IN (1, 2, NULL) 等价于
-- emp_id != 1 AND emp_id != 2 AND emp_id != NULL
-- 而 emp_id != NULL 永远是 NULL（不是TRUE也不是FALSE）
-- 导致整个条件为 NULL，返回空结果
-- ✅ 安全写法
WHERE emp_id NOT IN (
SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
)
扩展：查询每个经理的下属人数
SELECT
e.emp_id,
e.emp_name,
COUNT(subordinate.emp_id) AS subordinate_count
FROM employees e
LEFT JOIN employees subordinate ON e.emp_id = subordinate.manager_id
GROUP BY e.emp_id, e.emp_name
HAVING COUNT(subordinate.emp_id) > 0;
第四关：聚合函数与分组 - 学习指南
题目26：统计员工总数
💡 解题思路
这是最基础的聚合函数应用。需要理解：
- COUNT函数的作用是统计数量
- COUNT(*)和COUNT(字段)的区别
- 聚合函数返回单个值
🔍 关键提示
- 使用
COUNT(*)统计所有行数 - 或使用
COUNT(字段)统计非NULL值的数量 - 不需要GROUP BY，因为是统计全表
📝 实现步骤
- 确定使用COUNT函数
- 决定是用COUNT(*)还是COUNT(字段)
- 写出完整的SELECT语句
- 思考：需要WHERE条件吗？
✅ 验证方法
-- 手动验证：查看所有员工
SELECT * FROM employees;
-- 数一数有多少行，应该与COUNT结果一致
🚀 扩展思考
- COUNT(*)和COUNT(id)有什么区别？
- 如果表中有NULL值，COUNT(*)和COUNT(某字段)结果会不同吗？
- 如何统计去重后的数量？（提示：COUNT(DISTINCT 字段)）
💯 点击查看参考答案
-- 方法1：使用COUNT(*)（推荐）
SELECT COUNT(*) AS total_employees FROM employees;
-- 方法2：使用COUNT(字段)
SELECT COUNT(id) AS total_employees FROM employees;
-- 方法3：使用COUNT(1)
SELECT COUNT(1) AS total_employees FROM employees;
要点说明：
COUNT(*)：统计所有行，包括所有字段都是NULL的行COUNT(字段)：只统计该字段非NULL的行数COUNT(1)：与COUNT(*)效果相同，性能也基本一致- 给结果起别名让输出更易读
区别示例：
-- 假设某些员工的email为NULL
SELECT
COUNT(*) AS all_count, -- 返回所有员工数
COUNT(email) AS email_count -- 返回有邮箱的员工数
FROM employees;
题目27：计算所有员工的平均工资
💡 解题思路
这题考查AVG聚合函数的使用：
- AVG函数自动计算平均值
- 只能用于数值类型字段
- 自动忽略NULL值
🔍 关键提示
- 使用
AVG(salary)计算平均工资 - AVG会自动忽略NULL值
- 可以使用ROUND函数保留小数位数
📝 实现步骤
- 确定要计算平均值的字段：salary
- 使用AVG函数：AVG(salary)
- 考虑是否需要四舍五入
- 思考：如果有员工工资为NULL会怎样？
✅ 验证方法
-- 手动验证：查看所有工资并计算
SELECT salary FROM employees;
-- 用计算器算一下平均值，对比结果
🚀 扩展思考
- AVG和SUM/COUNT的关系是什么？
- 如何计算工资的中位数？
- 如何保留2位小数？（提示：ROUND函数）
- 如果某些员工工资为NULL，AVG如何处理？
💯 点击查看参考答案
-- 方法1：基本用法
SELECT AVG(salary) AS average_salary FROM employees;
-- 方法2：保留2位小数
SELECT ROUND(AVG(salary), 2) AS average_salary FROM employees;
-- 方法3：同时显示其他统计信息
SELECT
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
SUM(salary) AS total_salary,
COUNT(*) AS employee_count
FROM employees;
-- 扩展：验证AVG的计算
SELECT
SUM(salary) / COUNT(salary) AS manual_avg,
AVG(salary) AS auto_avg
FROM employees;
-- 两个结果应该相同
要点说明：
AVG(salary)：自动计算平均值ROUND(AVG(salary), 2)：保留2位小数- AVG会自动忽略NULL值，只计算有值的记录
- 可以同时使用多个聚合函数
NULL值处理：
-- AVG自动忽略NULL
-- 假设5个员工工资为：1000, 2000, 3000, NULL, NULL
-- AVG结果 = (1000+2000+3000)/3 = 2000
-- 而不是 (1000+2000+3000)/5 = 1200
题目28：查询每个部门的员工数量
💡 解题思路
这是GROUP BY分组的入门题目：
- 需要按部门分组
- 对每个部门统计员工数
- 理解GROUP BY的作用
🔍 关键提示
- 使用
GROUP BY department按部门分组 - 使用
COUNT(*)统计每组的数量 - SELECT中只能出现分组字段或聚合函数
📝 实现步骤
- 确定分组字段：department
- 确定聚合函数：COUNT(*)
- 写出SELECT语句：SELECT department, COUNT(*)
- 添加GROUP BY子句
- 思考：如果不加GROUP BY会怎样？
✅ 验证方法
-- 验证某个具体部门的员工数
SELECT COUNT(*) FROM employees WHERE department = '技术部';
-- 对比分组结果中技术部的数量是否一致
🚀 扩展思考
- 如果SELECT中包含非分组字段会怎样？
- 如何按多个字段分组？
- 如何只显示员工数大于5的部门？（提示：HAVING）
- GROUP BY和DISTINCT有什么区别？
💯 点击查看参考答案
-- 方法1：基本答案
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
-- 方法2：添加排序（按数量降序）
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;
-- 方法3：包含NULL值的处理
SELECT
COALESCE(department, '未分配部门') AS department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department;
要点说明：
GROUP BY department：按部门分组，每个部门返回一行COUNT(*)：统计每个分组的行数- SELECT中的字段必须是：
- GROUP BY中的字段，或
- 聚合函数（COUNT、SUM、AVG等）
错误示范：
-- ❌ 错误：SELECT中包含非分组字段
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;
-- name不在GROUP BY中，也不是聚合函数，会报错
-- ✅ 正确：只包含分组字段和聚合函数
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
执行过程理解：
原始数据：
技术部 张三
技术部 李四
销售部 王五
销售部 赵六
技术部 孙七
GROUP BY department后：
技术部 → [张三, 李四, 孙七] → COUNT(*) = 3
销售部 → [王五, 赵六] → COUNT(*) = 2
题目29：查询每个部门的平均工资，按平均工资降序排列
💡 解题思路
这题结合了分组、聚合、排序三个知识点：
- 按部门分组
- 计算每组的平均工资
- 对结果进行排序
🔍 关键提示
GROUP BY department按部门分组AVG(salary)计算平均工资ORDER BY AVG(salary) DESC按平均工资降序- 注意SQL子句的顺序
📝 实现步骤
- 先写分组：GROUP BY department
- 添加聚合函数：AVG(salary)
- 在SELECT中选择需要的字段
- 添加ORDER BY排序
- 注意：ORDER BY必须在GROUP BY之后
✅ 验证方法
-- 验证某个部门的平均工资
SELECT AVG(salary) FROM employees WHERE department = '技术部';
-- 检查排序是否正确：第一行应该是平均工资最高的部门
🚀 扩展思考
- 能否在ORDER BY中使用别名？
- 如果同时按部门和职位分组呢？
- 如何只显示平均工资前3名的部门？
- ORDER BY中能直接写AVG(salary)还是必须用别名？
💯 点击查看参考答案
-- 方法1：基本答案
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;
-- 方法2：直接在ORDER BY中使用聚合函数
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC;
-- 方法3：保留2位小数，更美观
SELECT
department,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;
-- 方法4：同时显示员工数和平均工资
SELECT
department,
COUNT(*) AS employee_count,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;
要点说明：
- 子句顺序（重要）：
SELECT → FROM → GROUP BY → ORDER BY - ORDER BY可以使用：
- 别名：
ORDER BY avg_salary DESC - 原表达式：
ORDER BY AVG(salary) DESC - 位置编号：
ORDER BY 2 DESC（2表示第2列）
- 别名：
DESC表示降序，ASC表示升序（默认）
完整执行顺序：
1. FROM employees -- 确定数据源
2. GROUP BY department -- 按部门分组
3. AVG(salary) -- 计算每组平均工资
4. SELECT -- 选择要显示的列
5. ORDER BY -- 对结果排序
多字段排序：
-- 先按平均工资降序，平均工资相同时按部门名升序
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC, department ASC;
题目30：查询员工数量大于5的部门
💡 解题思路
这题引入了HAVING子句：
- 先按部门分组统计员工数
- 再筛选出员工数>5的部门
- 理解HAVING和WHERE的区别
🔍 关键提示
- 使用
GROUP BY department分组 - 使用
COUNT(*)统计数量 - 使用
HAVING COUNT(*) > 5筛选分组结果 - 不能用WHERE，因为WHERE在分组前执行
📝 实现步骤
- 先写基本的分组统计：SELECT department, COUNT(*) ... GROUP BY department
- 思考：应该用WHERE还是HAVING？
- 添加HAVING子句过滤分组结果
- 理解：为什么不能用WHERE COUNT(*) > 5？
✅ 验证方法
-- 先查看所有部门的员工数
SELECT department, COUNT(*) AS cnt
FROM employees
GROUP BY department;
-- 手动检查哪些部门的员工数>5
🚀 扩展思考
- WHERE和HAVING的本质区别是什么？
- 能否同时使用WHERE和HAVING？
HAVING COUNT(*) > 5和WHERE COUNT(*) > 5为什么后者会报错？- 如果要查询"技术部中员工数量>5的记录"，如何写？
💯 点击查看参考答案
-- 方法1：基本答案
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
-- 方法2：使用别名（注意：有些数据库不支持在HAVING中使用SELECT的别名）
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING employee_count > 5; -- MySQL支持，标准SQL不支持
-- 方法3：同时使用WHERE和HAVING
SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 3000 -- 先过滤：只统计工资>3000的员工
GROUP BY department
HAVING COUNT(*) > 5; -- 再过滤：只显示员工数>5的部门
-- 方法4：添加排序
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY employee_count DESC;
WHERE vs HAVING 关键区别：
| 对比项 | WHERE | HAVING |
|---|---|---|
| 作用时机 | 分组前过滤 | 分组后过滤 |
| 作用对象 | 原始数据行 | 分组结果 |
| 能否使用聚合函数 | ❌ 不能 | ✅ 可以 |
| 执行顺序 | 2（FROM之后） | 4（GROUP BY之后） |
执行顺序示例：
SELECT department, COUNT(*) AS cnt
FROM employees -- 1. 获取数据
WHERE salary > 5000 -- 2. 过滤原始行（分组前）
GROUP BY department -- 3. 按部门分组
HAVING COUNT(*) > 5 -- 4. 过滤分组结果（分组后）
ORDER BY cnt DESC; -- 5. 排序
错误示范：
-- ❌ 错误：WHERE不能使用聚合函数
SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 5 -- 报错！
GROUP BY department;
-- ✅ 正确：用HAVING
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
记忆技巧：
- WHERE = Where are the rows?（筛选行）
- HAVING = Having these groups（筛选组）
题目31：查询每个部门工资最高和最低的差值
💡 解题思路
这题需要在分组中使用多个聚合函数：
- 按部门分组
- 找出每组的最高工资和最低工资
- 计算两者的差值
🔍 关键提示
MAX(salary)获取最高工资MIN(salary)获取最低工资- 可以在SELECT中进行算术运算：
MAX(salary) - MIN(salary) - 给结果起个有意义的别名
📝 实现步骤
- 确定分组字段：department
- 使用MAX获取最高工资
- 使用MIN获取最低工资
- 计算差值：MAX - MIN
- 思考：能否直接在一个表达式中完成？
✅ 验证方法
-- 验证某个部门的最高和最低工资
SELECT MAX(salary), MIN(salary)
FROM employees
WHERE department = '技术部';
-- 手动计算差值，对比结果
🚀 扩展思考
- 如何同时显示最高工资、最低工资和差值？
- 如何找出工资差距最大的部门？
- 能否在WHERE中使用MAX或MIN？
- 差值为0说明什么？
💯 点击查看参考答案
-- 方法1：只显示差值
SELECT
department,
MAX(salary) - MIN(salary) AS salary_gap
FROM employees
GROUP BY department;
-- 方法2：同时显示最高、最低和差值（推荐）
SELECT
department,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
MAX(salary) - MIN(salary) AS salary_gap
FROM employees
GROUP BY department;
-- 方法3：按差值降序排列，找出差距最大的部门
SELECT
department,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
MAX(salary) - MIN(salary) AS salary_gap
FROM employees
GROUP BY department
ORDER BY salary_gap DESC;
-- 方法4：只显示差值大于5000的部门
SELECT
department,
MAX(salary) - MIN(salary) AS salary_gap
FROM employees
GROUP BY department
HAVING MAX(salary) - MIN(salary) > 5000;
要点说明：
- 可以在SELECT中进行算术运算
- 多个聚合函数可以同时使用
- 聚合函数可以在HAVING中作为过滤条件
- 给计算结果起别名提高可读性
常用聚合函数组合：
SELECT
department,
COUNT(*) AS emp_count, -- 员工数
SUM(salary) AS total_salary, -- 工资总和
AVG(salary) AS avg_salary, -- 平均工资
MAX(salary) AS max_salary, -- 最高工资
MIN(salary) AS min_salary, -- 最低工资
MAX(salary) - MIN(salary) AS gap -- 差值
FROM employees
GROUP BY department;
实际意义：
- 工资差值大：说明部门内薪资差距大，可能有资深员工和新员工
- 工资差值小：说明部门内薪资比较平均
- 工资差值为0：说明该部门所有员工工资相同（或只有一个员工）
题目32：统计每个月入职的员工数量
💡 解题思路
这题结合了日期函数和分组：
- 从入职日期中提取月份信息
- 按月份分组
- 统计每个月的员工数
🔍 关键提示
- 使用
YEAR(hire_date)和MONTH(hire_date)提取年月 - 或使用
DATE_FORMAT(hire_date, '%Y-%m')格式化日期 - 按提取的年月分组
- 使用COUNT统计数量
📝 实现步骤
- 思考：如何从日期中提取月份？
- 决定分组方式：按年月还是只按月份？
- 写出GROUP BY子句
- 添加COUNT统计
- 考虑：2020年1月和2021年1月应该分开还是合并？
✅ 验证方法
-- 查看某个月入职的所有员工
SELECT * FROM employees
WHERE YEAR(hire_date) = 2020 AND MONTH(hire_date) = 3;
-- 数一数有多少人
🚀 扩展思考
- 如何按年份统计？按季度统计？
- DATE_FORMAT和YEAR/MONTH哪个更好用？
- 如何让结果按时间顺序排列？
- 如何只统计2020年的数据？
💯 点击查看参考答案
-- 方法1：使用YEAR和MONTH函数（推荐）
SELECT
YEAR(hire_date) AS hire_year,
MONTH(hire_date) AS hire_month,
COUNT(*) AS employee_count
FROM employees
GROUP BY YEAR(hire_date), MONTH(hire_date)
ORDER BY hire_year, hire_month;
-- 方法2：使用DATE_FORMAT（更简洁）
SELECT
DATE_FORMAT(hire_date, '%Y-%m') AS hire_month,
COUNT(*) AS employee_count
FROM employees
GROUP BY DATE_FORMAT(hire_date, '%Y-%m')
ORDER BY hire_month;
-- 方法3：显示为中文格式
SELECT
DATE_FORMAT(hire_date, '%Y年%m月') AS hire_month,
COUNT(*) AS employee_count
FROM employees
GROUP BY YEAR(hire_date), MONTH(hire_date)
ORDER BY hire_month;
-- 方法4：只统计某一年的数据
SELECT
MONTH(hire_date) AS month,
COUNT(*) AS employee_count
FROM employees
WHERE YEAR(hire_date) = 2020
GROUP BY MONTH(hire_date)
ORDER BY month;
-- 扩展：按季度统计
SELECT
YEAR(hire_date) AS year,
QUARTER(hire_date) AS quarter,
COUNT(*) AS employee_count
FROM employees
GROUP BY YEAR(hire_date), QUARTER(hire_date)
ORDER BY year, quarter;
要点说明：
- YEAR(hire_date)：提取年份（如2020）
- MONTH(hire_date)：提取月份（1-12）
- DATE_FORMAT(hire_date, format)：格式化日期
'%Y-%m'→ 2020-03'%Y年%m月'→ 2020年03月'%Y-%m-%d'→ 2020-03-15
- QUARTER(hire_date)：提取季度（1-4）
DATE_FORMAT常用格式：
%Y -- 四位年份（2020）
%y -- 两位年份（20）
%m -- 两位月份（01-12）
%c -- 月份（1-12）
%d -- 两位日期（01-31）
%H -- 24小时制小时（00-23）
%i -- 分钟（00-59）
%s -- 秒（00-59）
分组注意事项：
-- ❌ 错误：只按月份分组，会把不同年份的同一月份合并
SELECT MONTH(hire_date), COUNT(*)
FROM employees
GROUP BY MONTH(hire_date);
-- 结果：2020年3月和2021年3月会合并成一条记录
-- ✅ 正确：按年和月分组
SELECT YEAR(hire_date), MONTH(hire_date), COUNT(*)
FROM employees
GROUP BY YEAR(hire_date), MONTH(hire_date);
-- 结果：2020年3月和2021年3月是两条独立的记录
题目33：查询平均工资超过8000的部门名称和平均工资
💡 解题思路
这题需要多表JOIN + 分组 + HAVING：
- 连接员工表和部门表获取部门名称
- 按部门分组计算平均工资
- 用HAVING筛选平均工资>8000的部门
🔍 关键提示
- 需要JOIN employees和departments表
GROUP BY dept_id或dept_nameHAVING AVG(salary) > 8000筛选分组结果- 注意要显示部门名称，不是部门ID
📝 实现步骤
- 确定需要哪些表：employees（工资）、departments（部门名）
- 写JOIN语句连接两表
- 按部门分组
- 计算平均工资
- 使用HAVING筛选
- 思考：能否不JOIN，只用子查询？
✅ 验证方法
-- 先查看所有部门的平均工资
SELECT d.dept_name, AVG(e.salary)
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name;
-- 检查哪些部门平均工资>8000
🚀 扩展思考
- 如果用LEFT JOIN会有什么问题？
- 能否只用employees表实现（不显示部门名称）？
- 如何同时显示员工数量？
- WHERE和HAVING在这个查询中的区别是什么？
💯 点击查看参考答案
-- 方法1：标准答案（使用INNER JOIN）
SELECT
d.dept_name,
AVG(e.salary) AS avg_salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING AVG(e.salary) > 8000;
-- 方法2：保留2位小数，添加排序
SELECT
d.dept_name,
ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING AVG(e.salary) > 8000
ORDER BY avg_salary DESC;
-- 方法3：同时显示员工数量
SELECT
d.dept_name,
COUNT(e.emp_id) AS employee_count,
ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING AVG(e.salary) > 8000
ORDER BY avg_salary DESC;
-- 方法4：不使用JOIN（只显示部门ID，不显示名称）
SELECT
dept_id,
AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 8000;
要点说明：
- 多表关联 + 分组是常见的组合查询模式
- GROUP BY要包含SELECT中的非聚合字段
- HAVING用于过滤分组后的聚合结果
- 使用INNER JOIN只返回有匹配的记录
执行顺序：
1. FROM employees e
2. INNER JOIN departments d -- 关联表
3. GROUP BY d.dept_id -- 分组
4. AVG(e.salary) -- 计算聚合值
5. HAVING AVG > 8000 -- 过滤分组结果
6. SELECT -- 选择列
7. ORDER BY -- 排序（如果有）
WHERE vs HAVING 在此题中的应用：
-- 组合使用WHERE和HAVING
SELECT
d.dept_name,
AVG(e.salary) AS avg_salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE e.hire_date > '2020-01-01' -- WHERE: 只统计2020年后入职的员工
GROUP BY d.dept_id, d.dept_name
HAVING AVG(e.salary) > 8000; -- HAVING: 筛选平均工资>8000的部门
GROUP BY的字段选择：
-- 推荐：同时包含ID和名称
GROUP BY d.dept_id, d.dept_name
-- 或者只用ID（但MySQL在某些模式下可能报错）
GROUP BY d.dept_id
-- MySQL 5.7+可以设置sql_mode来控制GROUP BY的严格程度
题目34：查询每个经理管理的员工数量（不包括没有下属的）
💡 解题思路
这题需要自连接 + 分组：
- 将员工表与自己连接（员工的manager_id = 经理的emp_id）
- 按经理分组统计下属数量
- 不显示没有下属的员工
🔍 关键提示
- 自连接：
FROM employees e JOIN employees m ON e.manager_id = m.emp_id - 按经理分组：
GROUP BY m.emp_id, m.emp_name - 使用INNER JOIN自动排除没有下属的员工
- 或使用HAVING COUNT(*) > 0
📝 实现步骤
- 理解表结构：manager_id指向另一个员工的emp_id
- 设计自连接：e表示员工，m表示经理
- 关联条件：e.manager_id = m.emp_id
- 按经理分组并统计
- 思考：用INNER JOIN还是LEFT JOIN？
✅ 验证方法
-- 先查看组织结构
SELECT e.emp_name AS 员工, m.emp_name AS 经理
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
-- 手动数某个经理的下属数量
SELECT COUNT(*) FROM employees WHERE manager_id = 某经理ID;
🚀 扩展思考
- 如果要包括没有下属的员工（显示0），应该怎么改？
- 如何找出管理员工最多的经理？
- 如何显示经理自己的职位信息？
- 能否用子查询实现？
💯 点击查看参考答案
-- 方法1：使用INNER JOIN（推荐，自动排除没有下属的）
SELECT
m.emp_id AS manager_id,
m.emp_name AS manager_name,
COUNT(e.emp_id) AS subordinate_count
FROM employees e
INNER JOIN employees m ON e.manager_id = m.emp_id
GROUP BY m.emp_id, m.emp_name;
-- 方法2：按manager_id分组（更简单，但只显示ID）
SELECT
manager_id,
COUNT(*) AS subordinate_count
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;
-- 方法3：使用LEFT JOIN + HAVING（显式排除没有下属的）
SELECT
m.emp_id AS manager_id,
m.emp_name AS manager_name,
COUNT(e.emp_id) AS subordinate_count
FROM employees m
LEFT JOIN employees e ON m.emp_id = e.manager_id
GROUP BY m.emp_id, m.emp_name
HAVING COUNT(e.emp_id) > 0;
-- 方法4：按下属数量降序排列，找出管理最多员工的经理
SELECT
m.emp_name AS manager_name,
COUNT(e.emp_id) AS subordinate_count
FROM employees e
INNER JOIN employees m ON e.manager_id = m.emp_id
GROUP BY m.emp_id, m.emp_name
ORDER BY subordinate_count DESC;
-- 方法5：同时显示经理的部门信息
SELECT
m.emp_name AS manager_name,
d.dept_name AS manager_dept,
COUNT(e.emp_id) AS subordinate_count
FROM employees e
INNER JOIN employees m ON e.manager_id = m.emp_id
LEFT JOIN departments d ON m.dept_id = d.dept_id
GROUP BY m.emp_id, m.emp_name, d.dept_name;
要点说明：
- 自连接：同一张表使用不同别名与自己连接
- INNER JOIN：只返回有下属的经理（因为必须匹配）
- LEFT JOIN：返回所有员工，用HAVING过滤出有下属的
- e表示员工（employee），m表示经理（manager）
自连接的逻辑：
employees表：
emp_id emp_name manager_id
1 张三 NULL
2 李四 1
3 王五 1
4 赵六 2
自连接 e.manager_id = m.emp_id：
e.emp_name e.manager_id → m.emp_id m.emp_name
李四 1 → 1 张三
王五 1 → 1 张三
赵六 2 → 2 李四
按m分组统计：
张三 → [李四, 王五] → COUNT = 2
李四 → [赵六] → COUNT = 1
INNER JOIN vs LEFT JOIN：
-- INNER JOIN：只显示有下属的经理
FROM employees e
INNER JOIN employees m ON e.manager_id = m.emp_id
-- 结果：只有张三和李四（因为他们有下属）
-- LEFT JOIN + HAVING：先包括所有，再过滤
FROM employees m
LEFT JOIN employees e ON m.emp_id = e.manager_id
HAVING COUNT(e.emp_id) > 0
-- 结果：所有员工，但HAVING过滤掉没有下属的
扩展：包括没有下属的员工：
SELECT
m.emp_name AS employee_name,
COUNT(e.emp_id) AS subordinate_count
FROM employees m
LEFT JOIN employees e ON m.emp_id = e.manager_id
GROUP BY m.emp_id, m.emp_name
ORDER BY subordinate_count DESC;
-- 这样会显示所有员工，没有下属的显示0
题目35：查询参与项目数量最多的前3名员工
💡 解题思路
这题综合了多表JOIN + 分组 + 排序 + LIMIT：
- 连接员工表和项目关联表
- 按员工分组统计项目数
- 按项目数降序排列
- 取前3名
🔍 关键提示
- JOIN employees和emp_projects表
GROUP BY emp_id按员工分组COUNT(project_id)统计项目数ORDER BY COUNT(project_id) DESC降序排列LIMIT 3只取前3名
📝 实现步骤
- 确定需要的表：employees、emp_projects
- 写JOIN关联两表
- 按员工分组
- 统计每个员工的项目数
- 排序并限制结果数量
- 思考：如果第3名有并列怎么办？
✅ 验证方法
-- 先查看所有员工的项目数
SELECT e.emp_name, COUNT(ep.project_id) AS project_count
FROM employees e
LEFT JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
ORDER BY project_count DESC;
-- 手动确认前3名
🚀 扩展思考
- 如果要处理并列第3名的情况怎么办？
- 如果员工没有参与任何项目，应该显示吗？
- 如何同时显示参与的项目名称？
- LIMIT和RANK()窗口函数有什么区别？
💯 点击查看参考答案
-- 方法1：基本答案
SELECT
e.emp_name,
COUNT(ep.project_id) AS project_count
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
ORDER BY project_count DESC
LIMIT 3;
-- 方法2：同时显示员工ID
SELECT
e.emp_id,
e.emp_name,
COUNT(ep.project_id) AS project_count
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
ORDER BY project_count DESC
LIMIT 3;
-- 方法3：处理并列情况（使用窗口函数，MySQL 8.0+）
SELECT emp_id, emp_name, project_count
FROM (
SELECT
e.emp_id,
e.emp_name,
COUNT(ep.project_id) AS project_count,
RANK() OVER (ORDER BY COUNT(ep.project_id) DESC) AS ranking
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
) AS ranked
WHERE ranking <= 3;
-- 方法4：同时显示部门信息
SELECT
e.emp_name,
d.dept_name,
COUNT(ep.project_id) AS project_count
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
LEFT JOIN departments d ON e.dept_id = d.dept_id
GROUP BY e.emp_id, e.emp_name, d.dept_name
ORDER BY project_count DESC
LIMIT 3;
-- 方法5：显示参与的项目名称列表
SELECT
e.emp_name,
COUNT(ep.project_id) AS project_count,
GROUP_CONCAT(p.project_name) AS project_list
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
INNER JOIN projects p ON ep.project_id = p.project_id
GROUP BY e.emp_id, e.emp_name
ORDER BY project_count DESC
LIMIT 3;
要点说明：
- 多表JOIN：连接employees和emp_projects
- 分组统计：按员工分组，统计项目数
- ORDER BY + LIMIT：先排序再取前N名
- INNER JOIN：只显示参与过项目的员工
LIMIT的问题：
-- 假设项目数量排名：
-- 张三: 10个项目（第1名）
-- 李四: 8个项目（第2名）
-- 王五: 5个项目（第3名，并列）
-- 赵六: 5个项目（第3名，并列）
-- LIMIT 3 的结果：
-- 张三, 李四, 王五（赵六被排除了，虽然也是第3名）
-- 使用RANK()的结果：
-- 张三(rank=1), 李四(rank=2), 王五(rank=3), 赵六(rank=3)
-- WHERE ranking <= 3 会包括所有第3名
RANK vs LIMIT：
- LIMIT：严格限制返回行数，简单直接
- RANK()：处理并列情况，更符合"前3名"的语义
GROUP_CONCAT用法：
-- GROUP_CONCAT: 将分组内的多个值连接成一个字符串
GROUP_CONCAT(p.project_name) -- 默认逗号分隔
GROUP_CONCAT(p.project_name SEPARATOR '; ') -- 自定义分隔符
GROUP_CONCAT(DISTINCT p.project_name ORDER BY p.project_name) -- 去重并排序
完整查询解析：
1. FROM employees e -- 主表：员工
2. INNER JOIN emp_projects ep -- 关联：项目关联表
3. GROUP BY e.emp_id, e.emp_name -- 分组：按员工
4. COUNT(ep.project_id) -- 聚合：统计项目数
5. ORDER BY project_count DESC -- 排序：按项目数降序
6. LIMIT 3 -- 限制：只取前3条
第五关：子查询 - 学习指南
题目36：查询工资高于平均工资的员工信息
💡 解题思路
这是标量子查询的经典应用：
- 先计算平均工资（子查询）
- 再筛选工资大于平均值的员工（外层查询）
- 理解子查询返回单个值的特点
🔍 关键提示
- 子查询：
(SELECT AVG(salary) FROM employees) - 外层查询：
WHERE salary > (子查询) - 子查询会先执行，返回一个数值
- 这个数值被用于外层查询的比较
📝 实现步骤
- 先单独写子查询：计算平均工资
- 将子查询放入WHERE条件中
- 外层查询筛选符合条件的员工
- 思考：子查询会执行几次？
✅ 验证方法
-- 先查看平均工资是多少
SELECT AVG(salary) FROM employees;
-- 假设是6000
-- 然后验证查询结果
SELECT * FROM employees WHERE salary > 6000;
-- 应该与子查询的结果一致
🚀 扩展思考
- 能否用JOIN实现这个需求？
- 如果要查询低于平均工资的员工呢？
- 如何查询工资在平均值上下10%范围内的员工？
- 子查询和变量哪个性能更好？
💯 点击查看参考答案
-- 方法1：标准子查询
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 方法2：只查询需要的字段
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 方法3：同时显示平均工资（使用CROSS JOIN）
SELECT
e.*,
avg_sal.avg_salary
FROM employees e
CROSS JOIN (SELECT AVG(salary) AS avg_salary FROM employees) AS avg_sal
WHERE e.salary > avg_sal.avg_salary;
-- 方法4：显示高出平均工资的百分比
SELECT
emp_name,
salary,
(SELECT AVG(salary) FROM employees) AS avg_salary,
ROUND((salary - (SELECT AVG(salary) FROM employees)) / (SELECT AVG(salary) FROM employees) * 100, 2) AS percent_above_avg
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 方法5：使用变量（避免重复计算子查询）
SELECT AVG(salary) INTO @avg_salary FROM employees;
SELECT * FROM employees WHERE salary > @avg_salary;
要点说明：
- 标量子查询：返回单个值（一行一列）
- 执行顺序：子查询先执行，结果用于外层查询
- 子查询用括号包围：
(SELECT ...) - 可以在WHERE、SELECT、HAVING等位置使用
子查询类型：
-- 标量子查询：返回单个值
WHERE salary > (SELECT AVG(salary) FROM employees)
-- 列子查询：返回一列多行
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = '北京')
-- 行子查询：返回一行多列
WHERE (salary, age) = (SELECT MAX(salary), MAX(age) FROM employees)
-- 表子查询：返回多行多列
FROM (SELECT * FROM employees WHERE salary > 5000) AS high_earners
性能优化：
-- ❌ 不好：子查询在SELECT中，每行都执行一次
SELECT
emp_name,
salary,
(SELECT AVG(salary) FROM employees) AS avg_sal,
(SELECT AVG(salary) FROM employees) AS avg_sal2
FROM employees;
-- ✅ 好：用变量或JOIN避免重复计算
SELECT
emp_name,
salary,
avg_sal.avg_salary
FROM employees
CROSS JOIN (SELECT AVG(salary) AS avg_salary FROM employees) AS avg_sal
WHERE salary > avg_sal.avg_salary;
常见变体：
-- 查询低于平均工资的员工
WHERE salary < (SELECT AVG(salary) FROM employees)
-- 查询工资在平均值±10%范围内的员工
WHERE salary BETWEEN
(SELECT AVG(salary) * 0.9 FROM employees)
AND (SELECT AVG(salary) * 1.1 FROM employees)
-- 查询工资在中位数以上的员工（更复杂）
WHERE salary >= (
SELECT salary
FROM employees
ORDER BY salary
LIMIT 1 OFFSET (SELECT COUNT(*) FROM employees) / 2
)
题目37：查询工资最高的员工姓名和工资
💡 解题思路
这题有多种实现方式：
- 使用子查询找出最高工资，再查询该员工
- 使用ORDER BY + LIMIT直接取第一名
- 注意：可能有多个员工并列最高工资
🔍 关键提示
- 方法1：子查询
WHERE salary = (SELECT MAX(salary) FROM employees) - 方法2：排序
ORDER BY salary DESC LIMIT 1 - 方法1能处理并列情况，方法2只返回一条
- MAX函数返回最大值
📝 实现步骤
- 思考：最高工资是多少？用MAX查询
- 找出工资等于最高工资的员工
- 或者：直接按工资降序排列，取第一个
- 考虑：如果有多个员工工资都是最高，怎么办？
✅ 验证方法
-- 先查看最高工资
SELECT MAX(salary) FROM employees;
-- 查看有多少人是最高工资
SELECT COUNT(*) FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
🚀 扩展思考
- 两种方法在有并列第一时结果有何不同？
- 如何查询工资第二高的员工？
- 如何查询每个部门工资最高的员工？
- 哪种方法性能更好？
💯 点击查看参考答案
-- 方法1：子查询（推荐，能处理并列情况）
SELECT emp_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
-- 方法2：ORDER BY + LIMIT（只返回一条记录）
SELECT emp_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1;
-- 方法3：使用ALL（等价于方法1）
SELECT emp_name, salary
FROM employees
WHERE salary >= ALL(SELECT salary FROM employees);
-- 方法4：使用窗口函数（MySQL 8.0+，处理并列）
SELECT emp_name, salary
FROM (
SELECT
emp_name,
salary,
RANK() OVER (ORDER BY salary DESC) AS ranking
FROM employees
) AS ranked
WHERE ranking = 1;
-- 方法5：同时显示部门信息
SELECT e.emp_name, e.salary, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary = (SELECT MAX(salary) FROM employees);
要点说明：
- 子查询方式：能返回所有最高工资的员工（处理并列）
- LIMIT方式：只返回一条，无法处理并列情况
- MAX(salary)：返回最大值
- >= ALL：大于等于所有值，即最大值
方法对比：
假设工资情况：
张三: 10000（最高）
李四: 10000（最高，并列）
王五: 9000
方法1（子查询）结果：
张三 10000
李四 10000
✅ 返回所有最高工资的员工
方法2（LIMIT 1）结果：
张三 10000
❌ 只返回一个，李四被遗漏了
查询第二高工资：
-- 方法1：使用子查询排除最高工资
SELECT emp_name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)
);
-- 方法2：使用LIMIT OFFSET
SELECT emp_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
-- 方法3：使用DISTINCT去重（更准确）
SELECT emp_name, salary
FROM employees
WHERE salary = (
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1
);
-- 方法4：使用窗口函数DENSE_RANK
SELECT emp_name, salary
FROM (
SELECT
emp_name,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS ranking
FROM employees
) AS ranked
WHERE ranking = 2;
ALL关键字说明：
-- >= ALL: 大于等于所有值
WHERE salary >= ALL(SELECT salary FROM employees)
-- 等价于
WHERE salary = (SELECT MAX(salary) FROM employees)
-- <= ALL: 小于等于所有值（最小值）
WHERE salary <= ALL(SELECT salary FROM employees)
-- 等价于
WHERE salary = (SELECT MIN(salary) FROM employees)
性能考虑：
- 子查询：需要扫描表两次（一次MAX，一次匹配）
- ORDER BY + LIMIT：只需一次扫描，但无法处理并列
- 窗口函数：一次扫描，能处理并列，推荐使用（MySQL 8.0+）
题目38：查询与'张三'同一部门的其他员工（使用子查询）
💡 解题思路
这题在第三关已经出现过，这里用子查询方式实现：
- 子查询：查询张三的部门ID
- 外层查询：查询该部门的其他员工
- 排除张三本人
🔍 关键提示
- 子查询：
(SELECT dept_id FROM employees WHERE emp_name = '张三') - 外层WHERE：
dept_id = (子查询) AND emp_name != '张三' - 注意排除张三本人
- 考虑如果有多个叫张三的员工
📝 实现步骤
- 先写子查询：查询张三的dept_id
- 在外层查询中使用这个dept_id
- 添加条件排除张三本人
- 思考：用emp_id排除还是用emp_name排除？
✅ 验证方法
-- 先查张三的部门
SELECT dept_id FROM employees WHERE emp_name = '张三';
-- 手动查询该部门的所有员工
SELECT * FROM employees WHERE dept_id = (张三的部门ID);
🚀 扩展思考
- 这题和第三关的题目23有什么区别？
- 子查询和自连接两种方法哪个更好？
- 如果有多个叫"张三"的员工，结果会怎样？
- 如何同时显示部门名称？
💯 点击查看参考答案
-- 方法1：标准子查询（使用姓名排除）
SELECT emp_id, emp_name, dept_id
FROM employees
WHERE dept_id = (
SELECT dept_id FROM employees WHERE emp_name = '张三'
)
AND emp_name != '张三';
-- 方法2：使用emp_id排除（更严谨）
SELECT e1.emp_id, e1.emp_name, e1.dept_id
FROM employees e1
WHERE e1.dept_id = (
SELECT e2.dept_id
FROM employees e2
WHERE e2.emp_name = '张三'
LIMIT 1 -- 如果有多个张三，只取第一个的部门
)
AND e1.emp_id != (
SELECT e2.emp_id
FROM employees e2
WHERE e2.emp_name = '张三'
LIMIT 1
);
-- 方法3：使用IN（如果有多个张三）
SELECT emp_id, emp_name, dept_id
FROM employees
WHERE dept_id IN (
SELECT dept_id FROM employees WHERE emp_name = '张三'
)
AND emp_name != '张三';
-- 方法4：同时显示部门名称
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.dept_id = (
SELECT dept_id FROM employees WHERE emp_name = '张三' LIMIT 1
)
AND e.emp_name != '张三';
-- 对比：使用自连接（第三关的方法）
SELECT e1.emp_id, e1.emp_name, e1.dept_id
FROM employees e1
INNER JOIN employees e2 ON e1.dept_id = e2.dept_id
WHERE e2.emp_name = '张三'
AND e1.emp_name != '张三';
要点说明：
- 标量子查询：返回张三的dept_id
- 排除自己：AND emp_name != '张三' 或 AND emp_id != (子查询)
- 多个张三：用LIMIT 1限制或改用IN
- 题目要求使用子查询，所以不用JOIN
子查询 vs 自连接对比：
| 对比项 | 子查询 | 自连接 |
|---|---|---|
| 可读性 | 更清晰，逻辑分步 | 稍复杂 |
| 性能 | 可能执行多次 | 一次扫描 |
| 灵活性 | 易于修改条件 | 需要调整JOIN |
| 适用场景 | 简单筛选 | 复杂关联 |
处理多个同名员工：
-- 问题：如果有2个张三，在不同部门
emp_id emp_name dept_id
1 张三 1
2 李四 1
3 张三 2
4 王五 2
-- 方法1的结果（有歧义）：
-- 子查询可能返回多个dept_id，导致错误
-- 正确处理：指定要查询哪个张三
WHERE dept_id = (
SELECT dept_id
FROM employees
WHERE emp_name = '张三'
AND emp_id = 1 -- 指定具体的张三
)
-- 或者：查询所有张三的同事
WHERE dept_id IN (
SELECT dept_id FROM employees WHERE emp_name = '张三'
)
AND emp_name != '张三';
子查询位置：
-- WHERE子句中
WHERE dept_id = (SELECT ...)
-- SELECT子句中
SELECT
emp_name,
(SELECT dept_name FROM departments d WHERE d.dept_id = e.dept_id) AS dept_name
FROM employees e;
-- FROM子句中
FROM (SELECT * FROM employees WHERE salary > 5000) AS高薪员工
题目39：查询没有员工的部门名称
💡 解题思路
这是查找不存在的经典问题：
- 方法1：LEFT JOIN + IS NULL
- 方法2：NOT IN 子查询
- 方法3：NOT EXISTS 子查询
- 理解这三种方法的异同
🔍 关键提示
- 方法1：
FROM departments d LEFT JOIN employees e ... WHERE e.dept_id IS NULL - 方法2：
WHERE dept_id NOT IN (SELECT DISTINCT dept_id FROM employees) - 方法3：
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id) - 注意NOT IN的NULL陷阱
📝 实现步骤
- 理解题意：部门表中存在，但员工表中没有人
- 思考：用LEFT JOIN还是子查询？
- 选择一种方法实现
- 验证：确保所有方法结果一致
✅ 验证方法
-- 先查看所有部门
SELECT * FROM departments;
-- 查看每个部门的员工数
SELECT d.dept_name, COUNT(e.emp_id) AS emp_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;
-- 员工数为0的就是没有员工的部门
🚀 扩展思考
- 三种方法的性能有区别吗？
- NOT IN为什么要注意NULL值？
- NOT EXISTS和NOT IN哪个更好？
- 如何查询有员工的部门？
💯 点击查看参考答案
-- 方法1：LEFT JOIN + IS NULL（推荐，直观易懂）
SELECT d.dept_id, d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.dept_id IS NULL;
-- 方法2：NOT IN 子查询（注意NULL陷阱）
SELECT dept_id, dept_name
FROM departments
WHERE dept_id NOT IN (
SELECT DISTINCT dept_id
FROM employees
WHERE dept_id IS NOT NULL -- 必须过滤NULL
);
-- 方法3：NOT EXISTS（推荐，性能好）
SELECT d.dept_id, d.dept_name
FROM departments d
WHERE NOT EXISTS (
SELECT 1
FROM employees e
WHERE e.dept_id = d.dept_id
);
-- 方法4：使用子查询和聚合
SELECT d.dept_id, d.dept_name
FROM departments d
LEFT JOIN (
SELECT DISTINCT dept_id FROM employees
) AS emp_depts ON d.dept_id = emp_depts.dept_id
WHERE emp_depts.dept_id IS NULL;
要点说明：
- LEFT JOIN + IS NULL：保留左表所有记录，右表没匹配的为NULL
- NOT IN：不在子查询结果列表中
- NOT EXISTS：子查询没有返回结果则为真
- 三种方法逻辑等价，但性能和陷阱不同
NOT IN的NULL陷阱（重要！）：
-- ❌ 危险写法
SELECT dept_id, dept_name
FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees);
-- 如果employees表中有dept_id为NULL的记录：
-- NOT IN (1, 2, NULL) 等价于
-- dept_id != 1 AND dept_id != 2 AND dept_id != NULL
-- 而 dept_id != NULL 永远是NULL（不是TRUE也不是FALSE）
-- 导致整个条件为NULL，查询返回空结果！
-- ✅ 安全写法：过滤NULL
WHERE dept_id NOT IN (
SELECT dept_id FROM employees WHERE dept_id IS NOT NULL
)
性能对比：
NOT EXISTS vs NOT IN:
- NOT EXISTS: 找到第一条匹配就停止（短路），性能好
- NOT IN: 需要完全计算子查询结果，性能稍差
- NOT IN有NULL陷阱，NOT EXISTS没有
LEFT JOIN vs NOT EXISTS:
- LEFT JOIN: 需要连接操作，但直观
- NOT EXISTS: 半连接优化，性能通常更好
- 小数据量差别不大，大数据量NOT EXISTS更优
推荐顺序：NOT EXISTS > LEFT JOIN + IS NULL > NOT IN
三种方法的执行逻辑：
-- LEFT JOIN方式：
1. 将departments和employees进行LEFT JOIN
2. 保留所有部门，没有员工的部门右侧为NULL
3. WHERE e.dept_id IS NULL筛选出NULL的记录
-- NOT IN方式：
1. 子查询查出所有有员工的部门ID列表
2. 外层查询筛选不在这个列表中的部门
-- NOT EXISTS方式：
1. 对每个部门，检查employees表中是否存在该部门的员工
2. 如果不存在（NOT EXISTS为真），则返回该部门
相反问题：查询有员工的部门：
-- 方法1：INNER JOIN
SELECT DISTINCT d.dept_id, d.dept_name
FROM departments d
INNER JOIN employees e ON d.dept_id = e.dept_id;
-- 方法2：IN
SELECT dept_id, dept_name
FROM departments
WHERE dept_id IN (
SELECT DISTINCT dept_id FROM employees WHERE dept_id IS NOT NULL
);
-- 方法3：EXISTS
SELECT d.dept_id, d.dept_name
FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);
题目40：查询工资高于本部门平均工资的员工
💡 解题思路
这是相关子查询的经典应用：
- 对每个员工，计算其所在部门的平均工资
- 比较员工工资与部门平均工资
- 子查询引用外层查询的字段（相关子查询）
🔍 关键提示
- 相关子查询：子查询引用外层的字段
WHERE e1.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id)- 子查询会对每行执行一次
- 也可以用窗口函数实现
📝 实现步骤
- 理解题意：每个员工与本部门的平均工资比较，不是全公司
- 写子查询：计算某个部门的平均工资
- 让子查询引用外层的dept_id（相关子查询）
- 思考：这个子查询会执行多少次？
✅ 验证方法
-- 先查看每个部门的平均工资
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id;
-- 手动检查某个员工是否高于本部门平均工资
🚀 扩展思考
- 相关子查询和普通子查询有什么区别？
- 如何用窗口函数实现？
- 如何查询低于本部门平均工资的员工？
- 性能会不会很差？如何优化？
💯 点击查看参考答案
-- 方法1：相关子查询（标准方法）
SELECT e1.emp_id, e1.emp_name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id -- 相关：引用外层的dept_id
);
-- 方法2：使用JOIN和分组子查询
SELECT e.emp_id, e.emp_name, e.dept_id, e.salary
FROM employees e
INNER JOIN (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
) AS dept_avg ON e.dept_id = dept_avg.dept_id
WHERE e.salary > dept_avg.avg_salary;
-- 方法3：使用窗口函数（MySQL 8.0+，性能最好）
SELECT emp_id, emp_name, dept_id, salary
FROM (
SELECT
emp_id,
emp_name,
dept_id,
salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg_salary
FROM employees
) AS with_avg
WHERE salary > dept_avg_salary;
-- 方法4：同时显示部门平均工资和差值
SELECT
e.emp_name,
e.salary,
dept_avg.avg_salary AS dept_avg,
e.salary - dept_avg.avg_salary AS above_avg
FROM employees e
INNER JOIN (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
) AS dept_avg ON e.dept_id = dept_avg.dept_id
WHERE e.salary > dept_avg.avg_salary
ORDER BY above_avg DESC;
要点说明：
- 相关子查询：子查询引用外层查询的字段（e1.dept_id）
- 执行次数：外层每一行都会执行一次子查询
- 性能：相关子查询性能较差，窗口函数性能最好
- 三种方法结果相同，但执行效率不同
相关子查询 vs 普通子查询：
-- 普通子查询：执行一次，返回结果供外层使用
WHERE salary > (SELECT AVG(salary) FROM employees)
-- 执行1次子查询
-- 相关子查询：外层每行执行一次
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
)
-- 如果外层有100行，子查询执行100次
执行过程示例：
假设employees表：
emp_id emp_name dept_id salary
1 张三 1 8000
2 李四 1 6000
3 王五 2 9000
4 赵六 2 7000
1. 处理张三（dept_id=1）：
子查询：AVG(salary) WHERE dept_id=1 → (8000+6000)/2 = 7000
比较：8000 > 7000 → 真，张三被选中
2. 处理李四（dept_id=1）：
子查询：AVG(salary) WHERE dept_id=1 → 7000
比较：6000 > 7000 → 假，李四不被选中
3. 处理王五（dept_id=2）：
子查询：AVG(salary) WHERE dept_id=2 → (9000+7000)/2 = 8000
比较：9000 > 8000 → 真，王五被选中
4. 处理赵六（dept_id=2）：
子查询：AVG(salary) WHERE dept_id=2 → 8000
比较：7000 > 8000 → 假，赵六不被选中
最终结果：张三、王五
性能优化对比：
-- 慢：相关子查询（N次查询）
-- 外层100行 → 子查询执行100次
-- 快：JOIN方式（2次查询）
-- 1. 子查询统计各部门平均工资（扫描一次employees）
-- 2. JOIN和筛选（扫描一次employees）
-- 最快：窗口函数（1次查询）
-- 一次扫描完成所有计算
窗口函数详解：
AVG(salary) OVER (PARTITION BY dept_id)
-- OVER: 窗口函数标志
-- PARTITION BY dept_id: 按部门分组
-- AVG(salary): 计算每组的平均工资
-- 结果：为每行添加一个字段，显示该行所在部门的平均工资
扩展：查询工资是本部门最高的员工：
-- 相关子查询
SELECT emp_name, dept_id, salary
FROM employees e1
WHERE salary = (
SELECT MAX(salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);
-- 窗口函数
SELECT emp_name, dept_id, salary
FROM (
SELECT
emp_name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
FROM employees
) ranked
WHERE rk = 1;
题目41：查询至少参与过一个项目的员工姓名
💡 解题思路
这题可以用多种方法实现：
- EXISTS子查询（推荐）
- IN子查询
- INNER JOIN
- 理解"至少一个"的含义
🔍 关键提示
- EXISTS：
WHERE EXISTS (SELECT 1 FROM emp_projects ep WHERE ep.emp_id = e.emp_id) - IN：
WHERE emp_id IN (SELECT DISTINCT emp_id FROM emp_projects) - JOIN：
SELECT DISTINCT e.emp_name FROM employees e INNER JOIN emp_projects ep - DISTINCT去重很重要
📝 实现步骤
- 理解题意：只要参与过项目（至少1个）
- 思考：用EXISTS、IN还是JOIN？
- 选择一种方法实现
- 注意：可能一个员工参与多个项目，需要去重
✅ 验证方法
-- 先查看有多少员工参与了项目
SELECT COUNT(DISTINCT emp_id) FROM emp_projects;
-- 验证结果数量是否一致
🚀 扩展思考
- EXISTS和IN哪个性能更好？
- 为什么JOIN需要DISTINCT？
- 如何查询没有参与项目的员工？
- 如何查询参与超过3个项目的员工？
💯 点击查看参考答案
-- 方法1：使用EXISTS（推荐，性能好）
SELECT emp_name
FROM employees e
WHERE EXISTS (
SELECT 1
FROM emp_projects ep
WHERE ep.emp_id = e.emp_id
);
-- 方法2：使用IN
SELECT emp_name
FROM employees
WHERE emp_id IN (
SELECT DISTINCT emp_id FROM emp_projects
);
-- 方法3：使用INNER JOIN（需要DISTINCT去重）
SELECT DISTINCT e.emp_name
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id;
-- 方法4：同时显示参与的项目数量
SELECT
e.emp_name,
COUNT(ep.project_id) AS project_count
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name;
-- 方法5：使用窗口函数统计项目数
SELECT DISTINCT emp_name, project_count
FROM (
SELECT
e.emp_name,
COUNT(ep.project_id) OVER (PARTITION BY e.emp_id) AS project_count
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
) AS with_count;
要点说明：
- EXISTS：半连接，找到第一条就停止，性能最好
- IN：先计算子查询，再匹配，需要DISTINCT去重
- JOIN：产生笛卡尔积，需要DISTINCT去重
- "至少一个"意味着>=1，EXISTS和IN都可以
为什么JOIN需要DISTINCT？
假设数据：
employees: 张三(id=1), 李四(id=2)
emp_projects:
emp_id=1, project_id=101
emp_id=1, project_id=102
emp_id=2, project_id=103
不用DISTINCT的结果：
张三 (来自project_id=101)
张三 (来自project_id=102)
李四 (来自project_id=103)
用DISTINCT的结果：
张三
李四
EXISTS vs IN 性能对比：
-- EXISTS: 短路优化
-- 对每个员工，只要找到一条匹配的项目记录就停止
-- 如果员工参与10个项目，只需检查第1个
-- IN: 完全计算
-- 先计算所有参与项目的员工ID列表
-- 再检查每个员工是否在列表中
推荐：EXISTS（尤其是关联表数据量大时）
相反问题：查询没有参与项目的员工：
-- NOT EXISTS
SELECT emp_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM emp_projects ep WHERE ep.emp_id = e.emp_id
);
-- NOT IN
SELECT emp_name
FROM employees
WHERE emp_id NOT IN (
SELECT emp_id FROM emp_projects WHERE emp_id IS NOT NULL
);
-- LEFT JOIN + IS NULL
SELECT e.emp_name
FROM employees e
LEFT JOIN emp_projects ep ON e.emp_id = ep.emp_id
WHERE ep.emp_id IS NULL;
扩展：查询参与超过3个项目的员工：
SELECT e.emp_name
FROM employees e
WHERE (
SELECT COUNT(*) FROM emp_projects ep WHERE ep.emp_id = e.emp_id
) > 3;
-- 或使用JOIN + GROUP BY + HAVING
SELECT e.emp_name
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
HAVING COUNT(ep.project_id) > 3;
题目42：查询工资排名前10%的员工
💡 解题思路
这题考查百分位数计算：
- 先计算总员工数
- 计算前10%的人数
- 按工资降序取前N名
- 或使用窗口函数PERCENT_RANK
🔍 关键提示
- 计算10%人数：
CEIL(COUNT(*) * 0.1) - 使用LIMIT：先排序再LIMIT
- 窗口函数：
PERCENT_RANK() OVER (ORDER BY salary DESC) - 向上取整：CEIL函数
📝 实现步骤
- 方法1：先统计总数，计算10%，再LIMIT
- 方法2：用子查询一次完成
- 方法3：使用窗口函数（MySQL 8.0+）
- 思考：10%如何向上取整？
✅ 验证方法
-- 先查看总员工数
SELECT COUNT(*) FROM employees;
-- 假设100人，10%就是10人
-- 查看第10名和第11名的工资
SELECT salary FROM employees ORDER BY salary DESC LIMIT 9, 2;
🚀 扩展思考
- 如果总数不能被10整除怎么办？
- PERCENT_RANK和NTILE有什么区别？
- 如何查询工资中位数？
- 如何查询每个部门前10%的员工？
💯 点击查看参考答案
-- 方法1：使用变量和LIMIT
SET @total = (SELECT COUNT(*) FROM employees);
SET @top_count = CEIL(@total * 0.1);
SELECT emp_name, salary
FROM employees
ORDER BY salary DESC
LIMIT @top_count;
-- 方法2：使用子查询一次完成
SELECT emp_name, salary
FROM employees
ORDER BY salary DESC
LIMIT (SELECT CEIL(COUNT(*) * 0.1) FROM employees);
-- 方法3：使用窗口函数PERCENT_RANK（MySQL 8.0+）
SELECT emp_name, salary, percentile
FROM (
SELECT
emp_name,
salary,
PERCENT_RANK() OVER (ORDER BY salary DESC) AS percentile
FROM employees
) AS ranked
WHERE percentile <= 0.1;
-- 方法4：使用NTILE分桶（MySQL 8.0+）
SELECT emp_name, salary, bucket
FROM (
SELECT
emp_name,
salary,
NTILE(10) OVER (ORDER BY salary DESC) AS bucket
FROM employees
) AS bucketed
WHERE bucket = 1;
-- 方法5：同时显示排名和百分位
SELECT
emp_name,
salary,
rank_num,
ROUND(rank_num * 100.0 / total, 2) AS percentile
FROM (
SELECT
emp_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_num,
COUNT(*) OVER () AS total
FROM employees
) AS with_rank
WHERE rank_num <= CEIL(total * 0.1);
要点说明：
- CEIL函数：向上取整（100 * 0.1 = 10, CEIL(101 * 0.1) = 11）
- PERCENT_RANK：计算百分位排名（0到1之间）
- NTILE(n)：将数据分成n个桶
- 前10%可以用多种方法实现
PERCENT_RANK 详解：
-- PERCENT_RANK() 返回 (rank - 1) / (total_rows - 1)
假设5个员工，工资降序：
工资 PERCENT_RANK
10000 (1-1)/(5-1) = 0 -- 0%
9000 (2-1)/(5-1) = 0.25 -- 25%
8000 (3-1)/(5-1) = 0.5 -- 50%
7000 (4-1)/(5-1) = 0.75 -- 75%
6000 (5-1)/(5-1) = 1.0 -- 100%
NTILE 详解：
-- NTILE(10) 将数据平均分成10组
-- 第1组就是前10%
NTILE(10) OVER (ORDER BY salary DESC)
-- 100个员工分成10组，每组10人
-- bucket=1 的就是前10名（前10%）
处理小数问题：
-- 假设103个员工
-- 前10% = 103 * 0.1 = 10.3
CEIL(10.3) = 11 -- 向上取整，取11人
FLOOR(10.3) = 10 -- 向下取整，取10人
ROUND(10.3) = 10 -- 四舍五入，取10人
推荐用CEIL，确保至少包含10%
扩展：每个部门前10%：
SELECT dept_id, emp_name, salary, percentile
FROM (
SELECT
dept_id,
emp_name,
salary,
PERCENT_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS percentile
FROM employees
) AS ranked
WHERE percentile <= 0.1;
中位数计算：
-- MySQL 8.0+
SELECT
AVG(salary) AS median_salary
FROM (
SELECT
salary,
ROW_NUMBER() OVER (ORDER BY salary) AS rn,
COUNT(*) OVER () AS total
FROM employees
) AS numbered
WHERE rn IN (FLOOR((total + 1) / 2), CEIL((total + 1) / 2));
题目43：查询每个部门工资第二高的员工
💡 解题思路
这是分组排名的经典问题：
- 按部门分组
- 在每组内按工资排名
- 取每组的第2名
- 需要处理并列情况
🔍 关键提示
- 窗口函数：
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) - 或使用：
DENSE_RANK()、ROW_NUMBER() - 子查询方式较复杂
- 注意：第二高可能有并列
📝 实现步骤
- 理解题意：每个部门内部排名第2
- 使用窗口函数（推荐）或相关子查询
- 选择合适的排名函数（RANK vs DENSE_RANK vs ROW_NUMBER）
- 思考：如果第2名有并列怎么办？
✅ 验证方法
-- 先查看每个部门的工资排名
SELECT
dept_id,
emp_name,
salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
FROM employees
ORDER BY dept_id, rk;
🚀 扩展思考
- RANK、DENSE_RANK、ROW_NUMBER的区别？
- 如果部门只有1个人怎么办？
- 如何查询第二低的工资？
- 相关子查询和窗口函数哪个更好？
💯 点击查看参考答案
-- 方法1：使用DENSE_RANK（推荐，处理并列工资）
SELECT emp_name, dept_id, salary
FROM (
SELECT
emp_name,
dept_id,
salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
FROM employees
) AS ranked
WHERE rk = 2;
-- 方法2：使用RANK（并列时可能跳号）
SELECT emp_name, dept_id, salary
FROM (
SELECT
emp_name,
dept_id,
salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
FROM employees
) AS ranked
WHERE rk = 2;
-- 方法3：使用ROW_NUMBER（不处理并列，只取一个）
SELECT emp_name, dept_id, salary
FROM (
SELECT
emp_name,
dept_id,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees
) AS ranked
WHERE rn = 2;
-- 方法4：使用相关子查询（复杂但通用）
SELECT e1.emp_name, e1.dept_id, e1.salary
FROM employees e1
WHERE (
SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
AND e2.salary > e1.salary
) = 1;
-- 方法5：使用子查询 + LIMIT
SELECT e1.emp_name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
SELECT DISTINCT salary
FROM employees e2
WHERE e2.dept_id = e1.dept_id
ORDER BY salary DESC
LIMIT 1 OFFSET 1
);
要点说明：
- DENSE_RANK：密集排名，1,2,2,3（推荐用于找第N高）
- RANK：跳号排名，1,2,2,4
- ROW_NUMBER：连续排名，1,2,3,4（不处理并列）
- 窗口函数比相关子查询性能好得多
排名函数对比：
假设某部门工资：10000, 9000, 9000, 8000
DENSE_RANK(): 1, 2, 2, 3
RANK(): 1, 2, 2, 4
ROW_NUMBER(): 1, 2, 3, 4
查询第二高（9000）：
- DENSE_RANK WHERE rk=2: 返回两个9000 ✅
- RANK WHERE rk=2: 返回两个9000 ✅
- ROW_NUMBER WHERE rn=2: 只返回一个9000 ⚠️
DENSE_RANK vs RANK 选择：
-- 部门工资：12000, 10000, 10000, 8000
-- 用RANK找第2高:
-- rk=1: 12000
-- rk=2: 10000, 10000
-- rk=4: 8000
WHERE rk = 2 ✅ 返回10000（正确）
-- 用RANK找第3高:
-- WHERE rk = 3 ❌ 没有结果！（因为跳到rk=4了）
-- 用DENSE_RANK找第3高:
-- rk=1: 12000
-- rk=2: 10000, 10000
-- rk=3: 8000
WHERE rk = 3 ✅ 返回8000（正确）
结论：查找第N高工资用DENSE_RANK更准确
相关子查询详解：
-- 逻辑：找出有多少个不同的工资比我高
WHERE (
SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id -- 同部门
AND e2.salary > e1.salary -- 比我高
) = 1 -- 恰好1个工资比我高，说明我是第2高
-- 示例：
-- 部门1工资：10000, 9000, 8000
-- 对于9000这条记录：
-- 比9000高的工资：10000（1个）
-- COUNT = 1，满足条件，9000是第2高 ✅
处理特殊情况：
-- 如果部门没有第2高（只有1个人或所有人工资相同）
-- 窗口函数会返回空结果（WHERE rk = 2没有匹配）
-- 包含部门信息，显示哪些部门没有第2高
SELECT
d.dept_name,
e.emp_name,
e.salary
FROM departments d
LEFT JOIN (
SELECT emp_name, dept_id, salary
FROM (
SELECT
emp_name, dept_id, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
FROM employees
) ranked
WHERE rk = 2
) e ON d.dept_id = e.dept_id;
扩展：第二低工资：
-- 只需将DESC改为ASC
SELECT emp_name, dept_id, salary
FROM (
SELECT
emp_name, dept_id, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary ASC) AS rk
FROM employees
) ranked
WHERE rk = 2;
题目44：查询比所有'销售部'员工工资都高的员工
💡 解题思路
这题考查ALL关键字的使用：
- 先找出销售部的最高工资
- 查询工资高于这个最高值的员工
- 或使用
> ALL子查询 - 理解"所有"的含义
🔍 关键提示
- ALL关键字：
WHERE salary > ALL (SELECT salary FROM employees WHERE dept = '销售部') - 等价于：
WHERE salary > (SELECT MAX(salary) FROM employees WHERE dept = '销售部') - 注意NULL值问题
- 可以用JOIN实现
📝 实现步骤
- 理解题意：比销售部所有人都高，即比最高的还高
- 方法1：找出销售部最高工资，再比较
- 方法2：使用ALL关键字
- 思考：如果销售部没有员工怎么办？
✅ 验证方法
-- 先查看销售部的最高工资
SELECT MAX(salary) FROM employees WHERE department = '销售部';
-- 验证结果中的员工工资是否都大于这个值
🚀 扩展思考
- ALL和ANY/SOME有什么区别？
- 如果要查询比任意一个销售部员工工资高的呢？
- 如果销售部为空，ALL会返回什么？
- 能否用NOT EXISTS实现？
💯 点击查看参考答案
-- 方法1：使用ALL（推荐，语义清晰）
SELECT emp_name, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department = '销售部'
);
-- 方法2：使用MAX（等价，更常用）
SELECT emp_name, salary
FROM employees
WHERE salary > (
SELECT MAX(salary)
FROM employees
WHERE department = '销售部'
);
-- 方法3：使用NOT EXISTS（逻辑等价）
SELECT e1.emp_name, e1.salary
FROM employees e1
WHERE NOT EXISTS (
SELECT 1
FROM employees e2
WHERE e2.department = '销售部'
AND e2.salary >= e1.salary
);
-- 方法4：使用JOIN
SELECT e1.emp_name, e1.salary
FROM employees e1
CROSS JOIN (
SELECT MAX(salary) AS max_sales_salary
FROM employees
WHERE department = '销售部'
) sales_max
WHERE e1.salary > sales_max.max_sales_salary;
-- 方法5：排除销售部，且工资高于销售部最高工资
SELECT emp_name, salary, department
FROM employees
WHERE department != '销售部'
AND salary > (
SELECT MAX(salary)
FROM employees
WHERE department = '销售部'
);
要点说明：
- > ALL：大于所有值，等价于大于最大值
- < ALL：小于所有值，等价于小于最小值
- > ANY/SOME：大于任意一个值，等价于大于最小值
- ALL/ANY主要用于提高SQL可读性
ALL vs ANY 对比：
-- 假设销售部工资：5000, 7000, 9000
-- > ALL: 大于所有值
WHERE salary > ALL (...)
等价于 WHERE salary > 9000 (大于最大值)
-- > ANY: 大于任意一个值
WHERE salary > ANY (...)
等价于 WHERE salary > 5000 (大于最小值)
-- < ALL: 小于所有值
WHERE salary < ALL (...)
等价于 WHERE salary < 5000 (小于最小值)
-- < ANY: 小于任意一个值
WHERE salary < ANY (...)
等价于 WHERE salary < 9000 (小于最大值)
等价关系总结：
> ALL ≈ > MAX
< ALL ≈ < MIN
> ANY ≈ > MIN
< ANY ≈ < MAX
= ANY ≈ IN
<> ALL ≈ NOT IN
NULL值处理：
-- 如果子查询包含NULL
WHERE salary > ALL (SELECT salary FROM ...)
-- salary > NULL 结果是NULL（不是TRUE也不是FALSE）
-- 导致整个条件为NULL，该行被排除
-- 安全写法：过滤NULL
WHERE salary > ALL (
SELECT salary FROM employees
WHERE department = '销售部'
AND salary IS NOT NULL
)
特殊情况：子查询为空：
-- 如果销售部没有员工
SELECT salary FROM employees WHERE department = '销售部'
-- 返回空集
WHERE salary > ALL (空集)
-- 永远为TRUE！所有员工都满足
WHERE salary > (SELECT MAX(salary) FROM ... WHERE department = '销售部')
-- 子查询返回NULL
-- salary > NULL 为NULL，所有行被排除
推荐用MAX方式，逻辑更清晰
实际应用示例：
-- 查询工资比所有技术部员工都低的员工
WHERE salary < ALL (
SELECT salary FROM employees WHERE department = '技术部'
)
-- 查询工资比任意一个管理层员工高的员工
WHERE salary > ANY (
SELECT salary FROM employees WHERE position = '经理'
)
-- 查询部门在北京或上海的员工
WHERE dept_id = ANY (
SELECT dept_id FROM departments WHERE location IN ('北京', '上海')
)
-- 等价于
WHERE dept_id IN (
SELECT dept_id FROM departments WHERE location IN ('北京', '上海')
)
题目45：查询参与项目数量超过平均值的员工
💡 解题思路
这题综合了子查询、分组、聚合：
- 先计算每个员工的项目数
- 再计算平均项目数
- 筛选超过平均值的员工
- 可以用多种方法实现
🔍 关键提示
- 步骤1：计算平均项目数
SELECT AVG(cnt) FROM (每个员工的项目数) - 步骤2：计算每个员工的项目数并比较
- 需要两层子查询或HAVING
- 注意：没参与项目的员工不计入
📝 实现步骤
- 内层：统计每个员工的项目数
- 中层：计算平均项目数
- 外层：筛选超过平均的员工
- 思考：如何在一个查询中完成？
✅ 验证方法
-- 先查看每个员工的项目数
SELECT emp_id, COUNT(*) AS cnt
FROM emp_projects
GROUP BY emp_id;
-- 计算平均项目数
SELECT AVG(cnt) FROM (
SELECT COUNT(*) AS cnt FROM emp_projects GROUP BY emp_id
) AS counts;
🚀 扩展思考
- 能否用窗口函数简化？
- 如何同时显示员工的项目数和平均值？
- 如何查询项目数在平均值以下的员工？
- 没参与项目的员工是否应该算入平均值？
💯 点击查看参考答案
-- 方法1：使用子查询（标准方法）
SELECT e.emp_name, COUNT(ep.project_id) AS project_count
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
HAVING COUNT(ep.project_id) > (
SELECT AVG(cnt)
FROM (
SELECT COUNT(*) AS cnt
FROM emp_projects
GROUP BY emp_id
) AS avg_projects
);
-- 方法2：使用HAVING和子查询
SELECT e.emp_name, COUNT(ep.project_id) AS project_count
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
HAVING project_count > (
SELECT AVG(project_count)
FROM (
SELECT emp_id, COUNT(*) AS project_count
FROM emp_projects
GROUP BY emp_id
) AS emp_project_counts
);
-- 方法3：使用窗口函数（MySQL 8.0+，更优雅）
SELECT emp_name, project_count
FROM (
SELECT
e.emp_name,
COUNT(ep.project_id) AS project_count,
AVG(COUNT(ep.project_id)) OVER () AS avg_project_count
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
) AS with_avg
WHERE project_count > avg_project_count;
-- 方法4：先计算平均值，再筛选
WITH project_stats AS (
SELECT
emp_id,
COUNT(*) AS project_count
FROM emp_projects
GROUP BY emp_id
),
avg_count AS (
SELECT AVG(project_count) AS avg_projects
FROM project_stats
)
SELECT e.emp_name, ps.project_count
FROM employees e
INNER JOIN project_stats ps ON e.emp_id = ps.emp_id
CROSS JOIN avg_count ac
WHERE ps.project_count > ac.avg_projects;
-- 方法5：同时显示项目数、平均值和差值
SELECT
emp_name,
project_count,
avg_count,
project_count - avg_count AS above_avg
FROM (
SELECT
e.emp_name,
COUNT(ep.project_id) AS project_count,
(
SELECT AVG(cnt)
FROM (SELECT COUNT(*) AS cnt FROM emp_projects GROUP BY emp_id) AS t
) AS avg_count
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
) AS with_avg
WHERE project_count > avg_count;
要点说明：
- 两层子查询：内层统计每人项目数，外层计算平均
- HAVING：用于过滤分组后的聚合结果
- 窗口函数：AVG() OVER() 在每行都显示平均值
- 题目隐含：只统计参与了项目的员工
执行逻辑分解：
步骤1：统计每个员工的项目数
emp_id project_count
1 5
2 3
3 8
4 2
步骤2：计算平均项目数
AVG(5, 3, 8, 2) = 4.5
步骤3：筛选 > 4.5 的员工
emp_id project_count
1 5
3 8
窗口函数详解：
AVG(COUNT(ep.project_id)) OVER ()
-- 难点：聚合函数嵌套窗口函数
-- 执行顺序：
-- 1. GROUP BY 先执行，每组计算 COUNT(ep.project_id)
-- 2. AVG() OVER() 对所有分组的COUNT结果求平均
-- 3. 每行都显示这个平均值
示例结果：
emp_name project_count avg_project_count
张三 5 4.5
李四 3 4.5
王五 8 4.5
赵六 2 4.5
然后WHERE过滤 project_count > avg_project_count
WITH CTE 方式（推荐，可读性最好）：
-- Common Table Expression，公用表表达式
WITH project_stats AS (
-- 第一步：统计每人项目数
SELECT emp_id, COUNT(*) AS project_count
FROM emp_projects
GROUP BY emp_id
),
avg_count AS (
-- 第二步：计算平均值
SELECT AVG(project_count) AS avg_projects
FROM project_stats
)
-- 第三步：筛选和展示
SELECT
e.emp_name,
ps.project_count,
ac.avg_projects
FROM employees e
INNER JOIN project_stats ps ON e.emp_id = ps.emp_id
CROSS JOIN avg_count ac
WHERE ps.project_count > ac.avg_projects;
是否包括0项目的员工？
-- 题目："参与项目数量超过平均值"
-- 理解1：只统计参与了项目的员工的平均值
SELECT AVG(cnt) FROM (
SELECT COUNT(*) AS cnt FROM emp_projects GROUP BY emp_id
)
-- 如果10个员工，5个参与项目(数量2,3,4,5,6)，平均=4
-- 理解2：统计所有员工，没参与的算0
SELECT AVG(cnt) FROM (
SELECT e.emp_id, COUNT(ep.project_id) AS cnt
FROM employees e
LEFT JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id
)
-- 10个员工，5个参与(2,3,4,5,6)，5个不参与(0,0,0,0,0)，平均=2
通常题目指理解1（只统计参与的）
扩展：查询项目数前20%的员工：
SELECT emp_name, project_count, percentile
FROM (
SELECT
e.emp_name,
COUNT(ep.project_id) AS project_count,
PERCENT_RANK() OVER (ORDER BY COUNT(ep.project_id) DESC) AS percentile
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_id, e.emp_name
) AS ranked
WHERE percentile <= 0.2;
学习进度建议
本学习指南已完成第一关到第五关的详细引导，共45道题目的完整解析。
已完成关卡
✅ 第一关：基础入门（题目1-5）
- 重点掌握：建表、插入、查询、删除表的基本操作
- 多练习：至少独立完成3次以上
- 核心知识：CREATE TABLE、INSERT、SELECT、DROP TABLE
✅ 第二关：单表查询（题目6-15）
- 重点掌握：WHERE、ORDER BY、LIMIT的组合使用
- 对比练习：每道题尝试2-3种不同写法
- 核心知识：WHERE条件、模糊查询LIKE、排序、分页
✅ 第三关：多表联结（题目16-25）
- 重点掌握：INNER JOIN、LEFT JOIN、自连接
- 难点突破：理解JOIN的执行逻辑和不同JOIN类型的区别
- 核心知识：INNER JOIN、LEFT JOIN、自连接、多表关联
✅ 第四关：聚合函数与分组（题目26-35）
- 重点掌握：GROUP BY、HAVING、聚合函数（COUNT、SUM、AVG、MAX、MIN）
- 难点突破：WHERE与HAVING的区别、复杂分组统计
- 核心知识：分组统计、日期函数、多表JOIN+分组
✅ 第五关：子查询（题目36-45）
- 重点掌握：标量子查询、相关子查询、EXISTS、IN、NOT IN
- 难点突破：子查询性能优化、NULL陷阱、ALL/ANY关键字
- 核心知识：子查询类型、窗口函数、百分位计算、ALL/ANY
学习建议
-
循序渐进学习
- 按关卡顺序学习，不要跳跃
- 每关的练习题至少完成80%再进入下一关
- 遇到困难回头复习前面的基础内容
-
实践为主
- 看完解题思路后先自己尝试编写SQL
- 遇到困难再查看关键提示
- 最后对比参考答案，学习多种解法
-
总结归纳
- 每完成一关做一次总结
- 整理易错点和常用技巧
- 建立自己的SQL知识体系
-
扩展学习
- 完成扩展思考题目
- 尝试用不同方法实现同一需求
- 对比不同方法的性能和可读性
第六关：高级查询技巧 - 学习指南
题目46：查询本月入职的员工和上月入职的员工，合并显示
💡 解题思路
这道题考查UNION合并查询和日期函数的使用。需要：
- 分别查询本月和上月入职的员工
- 使用UNION或UNION ALL合并两个查询结果
- 理解UNION和UNION ALL的区别
🔍 关键提示
- 使用
DATE_FORMAT()格式化日期进行比较 - 使用
YEAR()和MONTH()函数提取年月 UNION会去重，UNION ALL不去重- 可以添加标识字段区分是本月还是上月入职
📝 实现步骤
- 获取当前年月：使用
YEAR(CURDATE())和MONTH(CURDATE()) - 编写本月入职员工查询
- 编写上月入职员工查询（注意跨年情况）
- 使用UNION ALL合并（因为本月和上月不会重复）
- 可选：添加一个字段标识入职月份
✅ 验证方法
-- 插入测试数据
INSERT INTO employees (name, hire_date) VALUES
('本月员工1', CURDATE()),
('上月员工1', DATE_SUB(CURDATE(), INTERVAL 1 MONTH));
-- 执行查询，检查是否同时显示本月和上月员工
🚀 扩展思考
- UNION和UNION ALL的性能差异是什么？
- 如何查询最近3个月入职的员工？
- 如果要按月份分组统计，应该怎么写？
💯 点击查看参考答案
方法1：使用UNION ALL（推荐）
SELECT name, hire_date, '本月入职' AS period
FROM employees
WHERE YEAR(hire_date) = YEAR(CURDATE())
AND MONTH(hire_date) = MONTH(CURDATE())
UNION ALL
SELECT name, hire_date, '上月入职' AS period
FROM employees
WHERE YEAR(hire_date) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
AND MONTH(hire_date) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
ORDER BY hire_date DESC;
方法2：使用日期范围（性能更好）
SELECT name, hire_date,
CASE
WHEN hire_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') THEN '本月入职'
ELSE '上月入职'
END AS period
FROM employees
WHERE hire_date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
AND hire_date < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
ORDER BY hire_date DESC;
方法3：使用DATE_FORMAT
SELECT name, hire_date, '本月入职' AS period
FROM employees
WHERE DATE_FORMAT(hire_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')
UNION ALL
SELECT name, hire_date, '上月入职' AS period
FROM employees
WHERE DATE_FORMAT(hire_date, '%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m');
要点说明：
- UNION ALL vs UNION：这里使用UNION ALL因为本月和上月的数据不会重复，性能更好
- 日期函数：
YEAR()、MONTH()、DATE_FORMAT()都可以提取日期部分 - 性能考虑：方法2使用范围查询，可以利用索引，性能最好
- 可读性：添加period字段清楚标识数据来源
性能对比：
- 方法1：在索引字段上使用函数，可能导致索引失效
- 方法2：使用范围查询，能够充分利用索引（推荐）
- 方法3：使用DATE_FORMAT，代码简洁但可能索引失效
题目47：根据工资将员工分为'高薪'、'中薪'、'低薪'三个等级
💡 解题思路
这道题考查CASE WHEN条件表达式的使用。需要：
- 定义工资等级的划分标准
- 使用CASE WHEN进行条件判断
- 为结果列起一个有意义的别名
🔍 关键提示
- 使用
CASE WHEN ... THEN ... ELSE ... END语法 - 条件按从具体到一般的顺序编写
- 记得给CASE表达式起别名
- 可以使用简单CASE或搜索CASE
📝 实现步骤
- 确定等级划分：高薪(>10000)、中薪(5000-10000)、低薪(<5000)
- 编写SELECT语句选择基本字段
- 添加CASE WHEN表达式判断工资等级
- 测试边界值（5000和10000）
✅ 验证方法
-- 验证不同工资范围的员工是否正确分类
SELECT salary, salary_level
FROM (你的查询)
WHERE salary IN (4999, 5000, 10000, 10001);
🚀 扩展思考
- 如果等级划分标准改变，如何快速调整？
- 如何统计每个等级的人数和平均工资？
- CASE WHEN可以嵌套吗？什么场景下需要嵌套？
💯 点击查看参考答案
方法1：搜索CASE（推荐，更灵活）
SELECT
name,
salary,
CASE
WHEN salary > 10000 THEN '高薪'
WHEN salary >= 5000 THEN '中薪'
ELSE '低薪'
END AS salary_level
FROM employees
ORDER BY salary DESC;
方法2：带统计的版本
SELECT
name,
salary,
CASE
WHEN salary > 10000 THEN '高薪'
WHEN salary >= 5000 AND salary <= 10000 THEN '中薪'
WHEN salary < 5000 THEN '低薪'
ELSE '未知'
END AS salary_level
FROM employees
ORDER BY salary DESC;
方法3：按等级分组统计
SELECT
CASE
WHEN salary > 10000 THEN '高薪'
WHEN salary >= 5000 THEN '中薪'
ELSE '低薪'
END AS salary_level,
COUNT(*) AS emp_count,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY salary_level
ORDER BY avg_salary DESC;
方法4：更细致的等级划分
SELECT
name,
salary,
CASE
WHEN salary >= 20000 THEN '特高薪'
WHEN salary >= 15000 THEN '高薪'
WHEN salary >= 10000 THEN '中高薪'
WHEN salary >= 5000 THEN '中薪'
WHEN salary >= 3000 THEN '低薪'
ELSE '最低薪'
END AS salary_level,
-- 计算与中位数的比例
ROUND(salary / (SELECT AVG(salary) FROM employees) * 100, 2) AS salary_percentage
FROM employees
ORDER BY salary DESC;
要点说明：
- CASE WHEN顺序：从高到低判断，匹配第一个为真的条件就返回
- 边界处理：5000和10000要明确归属哪个等级（这里5000属于中薪）
- 简化条件：第二个条件可以省略
AND salary <= 10000，因为前面已经判断了> 10000 - ELSE子句：建议加上ELSE处理意外情况（如NULL值）
- 性能：CASE WHEN在SELECT中，不影响WHERE索引的使用
实际应用扩展：
-- 结合其他条件：查询技术部的高薪员工
SELECT name, dept, salary
FROM (
SELECT
name,
dept,
salary,
CASE
WHEN salary > 10000 THEN '高薪'
WHEN salary >= 5000 THEN '中薪'
ELSE '低薪'
END AS salary_level
FROM employees
) AS emp_levels
WHERE dept = '技术部' AND salary_level = '高薪';
题目48：查询每个部门工资排名前3的员工
💡 解题思路
这道题考查窗口函数（MySQL 8.0+）的使用。需要：
- 理解RANK()、DENSE_RANK()、ROW_NUMBER()的区别
- 使用PARTITION BY按部门分组
- 使用ORDER BY定义排名规则
- 筛选排名前3的记录
🔍 关键提示
- 窗口函数：
RANK() OVER (PARTITION BY ... ORDER BY ...) - PARTITION BY相当于分组，但不会减少行数
- 需要用子查询或CTE筛选排名结果
- 注意处理工资相同的情况
📝 实现步骤
- 使用ROW_NUMBER()或RANK()为每个部门的员工排名
- 按部门分组（PARTITION BY dept_id）
- 按工资降序排序（ORDER BY salary DESC）
- 用子查询筛选排名<=3的记录
✅ 验证方法
-- 检查每个部门是否都有最多3条记录
SELECT dept_id, COUNT(*)
FROM (你的查询结果)
GROUP BY dept_id;
🚀 扩展思考
- RANK()、DENSE_RANK()、ROW_NUMBER()在工资相同时结果有何不同？
- 如果不用窗口函数，如何实现（MySQL 5.7）？
- 如何查询每个部门工资排名第2的员工？
💯 点击查看参考答案
方法1：使用ROW_NUMBER()（每个排名唯一）
SELECT dept_id, name, salary, rank_num
FROM (
SELECT
dept_id,
name,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_num
FROM employees
) AS ranked_emp
WHERE rank_num <= 3
ORDER BY dept_id, rank_num;
方法2：使用RANK()（工资相同排名相同，会跳号）
SELECT dept_id, name, salary, salary_rank
FROM (
SELECT
dept_id,
name,
salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank
FROM employees
) AS ranked_emp
WHERE salary_rank <= 3
ORDER BY dept_id, salary_rank;
方法3：使用DENSE_RANK()（工资相同排名相同，不跳号）
SELECT dept_id, name, salary, dense_rank
FROM (
SELECT
dept_id,
name,
salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank
FROM employees
) AS ranked_emp
WHERE dense_rank <= 3
ORDER BY dept_id, dense_rank;
方法4：不使用窗口函数（MySQL 5.7兼容）
SELECT e1.dept_id, e1.name, e1.salary
FROM employees e1
WHERE (
SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
AND e2.salary >= e1.salary
) <= 3
ORDER BY e1.dept_id, e1.salary DESC;
方法5：使用变量（MySQL 5.7兼容，但不推荐）
SELECT dept_id, name, salary, rank_num
FROM (
SELECT
dept_id,
name,
salary,
@rank := IF(@dept = dept_id, @rank + 1, 1) AS rank_num,
@dept := dept_id
FROM employees, (SELECT @rank := 0, @dept := NULL) AS vars
ORDER BY dept_id, salary DESC
) AS ranked
WHERE rank_num <= 3;
要点说明：
三种排名函数的区别（假设工资：10000, 9000, 9000, 8000）：
ROW_NUMBER(): 1, 2, 3, 4（连续，工资相同也不同排名）RANK(): 1, 2, 2, 4（跳号，两个第2名后面是第4名）DENSE_RANK(): 1, 2, 2, 3（不跳号，两个第2名后面是第3名）
选择建议：
- 需要唯一排名：用
ROW_NUMBER() - 工资相同应同排名：用
RANK()或DENSE_RANK() - 需要连续排名（不跳号）：用
DENSE_RANK()
性能对比：
- 窗口函数（方法1-3）：性能最好，代码最简洁
- 子查询方式（方法4）：性能较差，但兼容性好
- 变量方式（方法5）：不推荐，MySQL 8.0后可能不稳定
扩展示例：同时显示部门名称
SELECT
d.dept_name,
e.name AS emp_name,
e.salary,
e.salary_rank
FROM (
SELECT
dept_id,
name,
salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank
FROM employees
) AS e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary_rank <= 3
ORDER BY d.dept_name, e.salary_rank;
题目49：计算每个员工的工作年限（到当前日期）
💡 解题思路
这道题考查日期计算函数的使用。需要：
- 计算入职日期到当前日期的时间差
- 选择合适的日期函数
- 处理不同的时间单位（年、月、天）
🔍 关键提示
TIMESTAMPDIFF(unit, start_date, end_date)：计算时间差DATEDIFF(date1, date2)：计算天数差YEAR(CURDATE()) - YEAR(hire_date)：简单计算年份差- 注意闰年和不同月份天数的影响
📝 实现步骤
- 使用CURDATE()获取当前日期
- 使用TIMESTAMPDIFF计算年份差
- 可选：同时显示月数、天数
- 可选：格式化显示为"X年Y月"
✅ 验证方法
-- 插入已知日期的测试数据
INSERT INTO employees (name, hire_date) VALUES
('测试员工', '2020-01-01');
-- 检查计算结果是否正确
🚀 扩展思考
- 如何计算精确到月的工作年限（如2年3个月）？
- 如何找出工龄最长的前10名员工？
- 如何统计各工龄段的员工数量？
💯 点击查看参考答案
方法1：使用TIMESTAMPDIFF（推荐）
SELECT
name,
hire_date,
TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS work_years,
TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS work_months,
TIMESTAMPDIFF(DAY, hire_date, CURDATE()) AS work_days
FROM employees
ORDER BY work_years DESC, work_months DESC;
方法2：精确计算年和月
SELECT
name,
hire_date,
TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years,
TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) % 12 AS months,
CONCAT(
TIMESTAMPDIFF(YEAR, hire_date, CURDATE()), '年',
TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) % 12, '个月'
) AS work_duration
FROM employees
ORDER BY hire_date;
方法3：使用DATEDIFF计算天数再转换
SELECT
name,
hire_date,
DATEDIFF(CURDATE(), hire_date) AS total_days,
FLOOR(DATEDIFF(CURDATE(), hire_date) / 365) AS work_years,
ROUND(DATEDIFF(CURDATE(), hire_date) / 365, 2) AS work_years_decimal
FROM employees
ORDER BY total_days DESC;
方法4：简单年份差（不够精确）
SELECT
name,
hire_date,
YEAR(CURDATE()) - YEAR(hire_date) AS year_diff,
-- 更精确的计算：考虑月和日
YEAR(CURDATE()) - YEAR(hire_date) -
(DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(hire_date, '%m%d')) AS work_years
FROM employees;
方法5：工龄分段统计
SELECT
CASE
WHEN work_years >= 10 THEN '10年以上'
WHEN work_years >= 5 THEN '5-10年'
WHEN work_years >= 3 THEN '3-5年'
WHEN work_years >= 1 THEN '1-3年'
ELSE '1年以内'
END AS seniority_level,
COUNT(*) AS emp_count,
GROUP_CONCAT(name ORDER BY hire_date) AS employees
FROM (
SELECT
name,
hire_date,
TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS work_years
FROM employees
) AS emp_seniority
GROUP BY seniority_level
ORDER BY MIN(work_years) DESC;
要点说明：
- TIMESTAMPDIFF：最精确的方法，可以指定单位（YEAR、MONTH、DAY、HOUR等）
- DATEDIFF：只能计算天数差，需要手动转换为年
- 简单年份差：不考虑具体月日，可能不准确
- CURDATE() vs NOW()：CURDATE()返回日期，NOW()返回日期时间
不同方法的精确度：
-- 假设今天是2024-03-15，员工2023-06-01入职
-- TIMESTAMPDIFF(YEAR, ...) → 0年（不满1年）
-- YEAR(...) - YEAR(...) → 1年（不准确）
-- DATEDIFF(...) / 365 → 0.77年（约9个月）
实际应用扩展：
-- 计算工龄工资（每年增加200元）
SELECT
name,
salary AS base_salary,
TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS work_years,
salary + (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) * 200) AS total_salary
FROM employees
ORDER BY total_salary DESC;
-- 查询即将满整年工龄的员工（还有30天内）
SELECT
name,
hire_date,
TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS current_years,
DATE_ADD(hire_date, INTERVAL (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) YEAR) AS next_anniversary,
DATEDIFF(
DATE_ADD(hire_date, INTERVAL (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) YEAR),
CURDATE()
) AS days_to_anniversary
FROM employees
WHERE DATEDIFF(
DATE_ADD(hire_date, INTERVAL (TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) + 1) YEAR),
CURDATE()
) <= 30
ORDER BY days_to_anniversary;
题目50：将员工姓名和部门名称连接成一个字段显示
💡 解题思路
这道题考查字符串函数和多表联结的组合使用。需要：
- 使用字符串连接函数拼接多个字段
- JOIN关联员工表和部门表
- 格式化输出，增加可读性
🔍 关键提示
CONCAT(str1, str2, ...)：连接字符串CONCAT_WS(separator, str1, str2, ...)：用分隔符连接- 需要JOIN部门表获取部门名称
- 注意NULL值的处理
📝 实现步骤
- JOIN员工表和部门表
- 使用CONCAT或CONCAT_WS连接姓名和部门名
- 添加适当的分隔符或格式化文本
- 起一个有意义的别名
✅ 验证方法
-- 检查输出格式是否正确
-- 检查是否有NULL值影响连接结果
🚀 扩展思考
- CONCAT和CONCAT_WS有什么区别？
- 如果某个字段是NULL，CONCAT的结果是什么？
- 如何连接多个字段并添加不同的分隔符？
💯 点击查看参考答案
方法1：使用CONCAT
SELECT
e.emp_id,
CONCAT(e.name, ' - ', d.dept_name) AS employee_info
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;
方法2：使用CONCAT_WS（更方便）
SELECT
e.emp_id,
CONCAT_WS(' - ', e.name, d.dept_name) AS employee_info
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;
方法3：更详细的格式
SELECT
e.emp_id,
CONCAT(e.name, '（', IFNULL(d.dept_name, '未分配部门'), '）') AS employee_info
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;
方法4：多字段连接
SELECT
e.emp_id,
CONCAT_WS(' | ',
e.name,
d.dept_name,
CONCAT('工资:', e.salary),
CONCAT('入职:', DATE_FORMAT(e.hire_date, '%Y年%m月'))
) AS full_info
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;
方法5：使用COALESCE处理NULL
SELECT
e.emp_id,
CONCAT(
e.name,
' - ',
COALESCE(d.dept_name, '未分配')
) AS employee_info,
-- 或者使用IFNULL
CONCAT(
e.name,
' - ',
IFNULL(d.dept_name, '未分配')
) AS employee_info2
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;
要点说明：
CONCAT vs CONCAT_WS：
-- CONCAT：任意参数为NULL，结果为NULL
SELECT CONCAT('张三', NULL, '技术部'); -- 结果：NULL
-- CONCAT_WS：会忽略NULL值，只用分隔符连接非NULL值
SELECT CONCAT_WS(' - ', '张三', NULL, '技术部'); -- 结果：'张三 - 技术部'
NULL处理的重要性：
-- 错误示例：没有处理NULL
SELECT CONCAT(name, ' - ', dept_name) -- 如果dept_name是NULL，结果是NULL
-- 正确示例：处理NULL
SELECT CONCAT(name, ' - ', IFNULL(dept_name, '未知部门'))
其他字符串函数：
SELECT
-- 连接
CONCAT(name, ' ', dept_name) AS method1,
CONCAT_WS(' ', name, dept_name) AS method2,
-- 大小写转换
UPPER(name) AS upper_name,
LOWER(name) AS lower_name,
-- 截取
SUBSTRING(name, 1, 1) AS first_char,
LEFT(name, 2) AS first_two_chars,
RIGHT(name, 2) AS last_two_chars,
-- 长度
LENGTH(name) AS byte_length,
CHAR_LENGTH(name) AS char_length,
-- 替换
REPLACE(name, '张', 'Zhang') AS replaced,
-- 去空格
TRIM(name) AS trimmed,
-- 重复
REPEAT('-', 10) AS separator
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
实际应用场景：
-- 生成员工卡片信息
SELECT
CONCAT(
'姓名：', e.name, '\n',
'部门：', IFNULL(d.dept_name, '未分配'), '\n',
'工资：¥', FORMAT(e.salary, 2), '\n',
'入职：', DATE_FORMAT(e.hire_date, '%Y年%m月%d日')
) AS employee_card
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- 生成邮件标题
SELECT
CONCAT(
'[', d.dept_name, '] ',
e.name, ' - ',
'工资通知'
) AS email_subject
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
题目51：查询每个员工的工资排名（全公司范围）
💡 解题思路
这道题考查窗口函数的全局排名。与题目48不同，这里不需要分区：
- 使用RANK()、DENSE_RANK()或ROW_NUMBER()
- 不使用PARTITION BY（全公司范围）
- 按工资降序排序
🔍 关键提示
- 窗口函数不带PARTITION BY就是全局排名
RANK() OVER (ORDER BY salary DESC)- 考虑工资相同的情况应该如何排名
- 可以同时显示多种排名方式对比
📝 实现步骤
- 选择合适的排名函数
- 使用ORDER BY salary DESC定义排序规则
- 可选：同时显示三种排名函数的结果进行对比
✅ 验证方法
-- 检查排名是否从1开始
-- 检查工资相同的员工排名是否正确
SELECT salary, COUNT(*) FROM (你的查询) GROUP BY salary HAVING COUNT(*) > 1;
🚀 扩展思考
- 如何查询工资排名前10%的员工？
- 如何计算每个员工的工资百分位？
- 如何查询工资中位数？
💯 点击查看参考答案
方法1：使用RANK()（相同工资同排名，跳号）
SELECT
name,
dept_id,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY salary_rank;
方法2：使用DENSE_RANK()（相同工资同排名，不跳号）
SELECT
name,
dept_id,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY salary_rank;
方法3：使用ROW_NUMBER()（唯一排名）
SELECT
name,
dept_id,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY salary_rank;
方法4：三种排名对比
SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS rank_with_gaps,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_no_gaps,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number,
-- 计算与最高工资的差距
(SELECT MAX(salary) FROM employees) - salary AS diff_from_max,
-- 计算工资百分位
PERCENT_RANK() OVER (ORDER BY salary DESC) AS percentile
FROM employees
ORDER BY salary DESC;
方法5：查询TOP 10%员工
SELECT
name,
salary,
salary_rank,
total_emp,
ROUND(salary_rank * 100.0 / total_emp, 2) AS rank_percentage
FROM (
SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank,
COUNT(*) OVER () AS total_emp
FROM employees
) AS ranked_emp
WHERE salary_rank <= CEIL((SELECT COUNT(*) FROM employees) * 0.1)
ORDER BY salary_rank;
方法6：不使用窗口函数（MySQL 5.7兼容）
SELECT
e1.name,
e1.salary,
COUNT(DISTINCT e2.salary) AS salary_rank
FROM employees e1
LEFT JOIN employees e2 ON e2.salary >= e1.salary
GROUP BY e1.emp_id, e1.name, e1.salary
ORDER BY salary_rank;
要点说明：
三种排名的区别示例（工资：15000, 12000, 12000, 10000）：
RANK(): 1, 2, 2, 4 -- 跳号
DENSE_RANK(): 1, 2, 2, 3 -- 不跳号
ROW_NUMBER(): 1, 2, 3, 4 -- 唯一排名（相同工资也不同排名）
其他有用的窗口函数：
SELECT
name,
salary,
-- 排名相关
RANK() OVER (ORDER BY salary DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile, -- 分成4组
PERCENT_RANK() OVER (ORDER BY salary DESC) AS percent_rank, -- 百分位排名
CUME_DIST() OVER (ORDER BY salary DESC) AS cumulative_dist, -- 累积分布
-- 聚合窗口函数
AVG(salary) OVER () AS company_avg_salary,
MAX(salary) OVER () AS max_salary,
MIN(salary) OVER () AS min_salary,
SUM(salary) OVER (ORDER BY salary DESC) AS cumulative_salary,
-- LAG和LEAD（访问前后行）
LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary,
LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary,
salary - LAG(salary, 1) OVER (ORDER BY salary DESC) AS diff_from_prev
FROM employees
ORDER BY salary DESC;
实际应用示例：
-- 查询工资中位数
SELECT AVG(salary) AS median_salary
FROM (
SELECT
salary,
ROW_NUMBER() OVER (ORDER BY salary) AS row_num,
COUNT(*) OVER () AS total_count
FROM employees
) AS numbered
WHERE row_num IN (FLOOR((total_count + 1) / 2), CEIL((total_count + 1) / 2));
-- 查询每个工资段的人数分布
SELECT
quartile,
COUNT(*) AS emp_count,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
AVG(salary) AS avg_salary
FROM (
SELECT
salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees
) AS quartiles
GROUP BY quartile
ORDER BY quartile;
题目52：提取每个员工邮箱的域名部分
💡 解题思路
这道题考查字符串处理函数的组合使用。需要：
- 找到@符号的位置
- 提取@之后的部分
- 处理可能的NULL值或格式错误
🔍 关键提示
SUBSTRING_INDEX(str, delimiter, count)：按分隔符截取SUBSTRING(str, pos)：从指定位置截取LOCATE('@', email)：查找@的位置- 处理边界情况（NULL、没有@、多个@）
📝 实现步骤
- 使用SUBSTRING_INDEX提取@后面的部分
- 或者使用LOCATE找到@的位置，再用SUBSTRING截取
- 添加NULL值处理
- 可选：同时提取用户名部分
✅ 验证方法
-- 测试各种邮箱格式
INSERT INTO employees (name, email) VALUES
('测试1', '[email protected]'),
('测试2', '[email protected]'),
('测试3', NULL),
('测试4', 'invalid');
🚀 扩展思考
- 如何同时提取用户名部分（@之前）？
- 如何统计每个域名的员工数量？
- 如何验证邮箱格式是否正确？
💯 点击查看参考答案
方法1：使用SUBSTRING_INDEX（最简单）
SELECT
name,
email,
SUBSTRING_INDEX(email, '@', -1) AS domain
FROM employees
WHERE email IS NOT NULL
ORDER BY domain;
方法2：同时提取用户名和域名
SELECT
name,
email,
SUBSTRING_INDEX(email, '@', 1) AS username,
SUBSTRING_INDEX(email, '@', -1) AS domain
FROM employees
WHERE email IS NOT NULL AND email LIKE '%@%'
ORDER BY domain;
方法3：使用LOCATE和SUBSTRING
SELECT
name,
email,
SUBSTRING(email, LOCATE('@', email) + 1) AS domain,
SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username
FROM employees
WHERE email IS NOT NULL
AND LOCATE('@', email) > 0
ORDER BY domain;
方法4：添加格式验证
SELECT
name,
email,
CASE
WHEN email IS NULL THEN '无邮箱'
WHEN email NOT LIKE '%@%.%' THEN '格式错误'
ELSE SUBSTRING_INDEX(email, '@', -1)
END AS domain,
CASE
WHEN email IS NOT NULL AND email LIKE '%@%.%' THEN '有效'
ELSE '无效'
END AS email_status
FROM employees
ORDER BY email_status DESC, domain;
方法5：统计每个域名的员工数
SELECT
SUBSTRING_INDEX(email, '@', -1) AS domain,
COUNT(*) AS emp_count,
GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS employees
FROM employees
WHERE email IS NOT NULL AND email LIKE '%@%'
GROUP BY domain
ORDER BY emp_count DESC, domain;
方法6：提取顶级域名（com、cn、org等）
SELECT
name,
email,
SUBSTRING_INDEX(email, '@', -1) AS full_domain,
SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', -1) AS top_level_domain
FROM employees
WHERE email IS NOT NULL AND email LIKE '%@%'
ORDER BY top_level_domain, email;
要点说明：
SUBSTRING_INDEX参数说明：
SUBSTRING_INDEX(str, delim, count)
-- count > 0: 从左边数第count个分隔符之前的部分
-- count < 0: 从右边数第count个分隔符之后的部分
示例：'[email protected]'
SUBSTRING_INDEX(email, '@', 1) → 'user'
SUBSTRING_INDEX(email, '@', -1) → 'mail.example.com'
SUBSTRING_INDEX(email, '.', 1) → 'user@mail'
SUBSTRING_INDEX(email, '.', -1) → 'com'
SUBSTRING_INDEX(email, '.', -2) → 'example.com'
字符串函数组合技巧：
-- 提取二级域名（如example.com中的example）
SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', 1)
-- 提取完整域名并转小写
LOWER(SUBSTRING_INDEX(email, '@', -1))
-- 检查是否是公司邮箱
SUBSTRING_INDEX(email, '@', -1) LIKE '%.company.com'
实际应用场景：
-- 分类统计不同类型的邮箱
SELECT
CASE
WHEN email LIKE '%@gmail.com' THEN 'Gmail'
WHEN email LIKE '%@qq.com' THEN 'QQ邮箱'
WHEN email LIKE '%@163.com' OR email LIKE '%@126.com' THEN '网易邮箱'
WHEN email LIKE '%@company.com' THEN '公司邮箱'
ELSE '其他'
END AS email_type,
COUNT(*) AS count
FROM employees
WHERE email IS NOT NULL
GROUP BY email_type
ORDER BY count DESC;
-- 验证邮箱格式并分类
SELECT
name,
email,
CASE
WHEN email IS NULL THEN '未填写'
WHEN email NOT LIKE '%@%' THEN '缺少@'
WHEN email NOT LIKE '%@%.%' THEN '缺少域名'
WHEN email LIKE '%@%@%' THEN '多个@'
WHEN LENGTH(SUBSTRING_INDEX(email, '@', 1)) = 0 THEN '用户名为空'
WHEN LENGTH(SUBSTRING_INDEX(email, '@', -1)) < 3 THEN '域名太短'
ELSE '格式正确'
END AS validation_result
FROM employees
ORDER BY
CASE validation_result
WHEN '格式正确' THEN 1
ELSE 2
END,
email;
其他字符串处理示例：
SELECT
email,
-- 提取和转换
SUBSTRING_INDEX(email, '@', -1) AS domain,
UPPER(SUBSTRING_INDEX(email, '@', 1)) AS username_upper,
LOWER(email) AS email_lower,
-- 替换
REPLACE(email, '@', ' [at] ') AS email_safe,
-- 长度和位置
LENGTH(email) AS total_length,
CHAR_LENGTH(email) AS char_count,
LOCATE('@', email) AS at_position,
-- 截取
LEFT(email, 5) AS first_5_chars,
RIGHT(email, 10) AS last_10_chars,
SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username,
-- 填充
LPAD(SUBSTRING_INDEX(email, '@', 1), 10, '*') AS padded_username,
-- 反转（不常用）
REVERSE(email) AS reversed
FROM employees
WHERE email IS NOT NULL;
题目53：查询入职满1年的员工数量
💡 解题思路
这道题考查日期比较和聚合函数的结合使用。需要：
- 计算入职日期与当前日期的时间差
- 判断是否满1年
- 统计符合条件的数量
🔍 关键提示
- 使用DATE_ADD或DATE_SUB计算1年前的日期
- 或使用TIMESTAMPDIFF计算时间差
- 使用COUNT统计数量
- 注意"满1年"的定义（≥365天还是≥1年）
📝 实现步骤
- 计算1年前的日期：
DATE_SUB(CURDATE(), INTERVAL 1 YEAR) - 比较hire_date是否小于等于这个日期
- 使用COUNT(*)统计数量
✅ 验证方法
-- 同时查看明细和汇总
SELECT name, hire_date, TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years
FROM employees
WHERE TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) >= 1;
🚀 扩展思考
- 如何查询入职不满1年的员工？
- 如何查询入职满N年的员工（N可变）？
- 如何统计各工龄段的员工数量？
💯 点击查看参考答案
方法1：使用DATE_SUB（推荐）
SELECT COUNT(*) AS employees_over_1_year
FROM employees
WHERE hire_date <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);
方法2：使用TIMESTAMPDIFF
SELECT COUNT(*) AS employees_over_1_year
FROM employees
WHERE TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) >= 1;
方法3：使用DATEDIFF（按天数计算）
SELECT COUNT(*) AS employees_over_1_year
FROM employees
WHERE DATEDIFF(CURDATE(), hire_date) >= 365;
方法4：详细查询（显示明细）
SELECT
name,
hire_date,
DATEDIFF(CURDATE(), hire_date) AS days_worked,
TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years_worked,
TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS months_worked
FROM employees
WHERE TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) >= 1
ORDER BY hire_date;
方法5：分段统计
SELECT
CASE
WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) < 1 THEN '不满1年'
WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) < 3 THEN '1-3年'
WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) < 5 THEN '3-5年'
WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) < 10 THEN '5-10年'
ELSE '10年以上'
END AS seniority_group,
COUNT(*) AS emp_count,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY seniority_group
ORDER BY MIN(TIMESTAMPDIFF(YEAR, hire_date, CURDATE()));
方法6：使用SUM和CASE
SELECT
SUM(CASE WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) >= 1 THEN 1 ELSE 0 END) AS over_1_year,
SUM(CASE WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) < 1 THEN 1 ELSE 0 END) AS under_1_year,
COUNT(*) AS total_employees,
ROUND(
SUM(CASE WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) >= 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
2
) AS percentage_over_1_year
FROM employees;
要点说明：
不同方法的精确度比较：
-- 假设今天是2024-03-15，员工2023-03-16入职（差1天满1年）
-- 方法1: DATE_SUB
hire_date <= '2023-03-15' → 不满足（3月16日 > 3月15日）
-- 方法2: TIMESTAMPDIFF(YEAR, ...)
TIMESTAMPDIFF(YEAR, '2023-03-16', '2024-03-15') → 0（不满1年）
-- 方法3: DATEDIFF
DATEDIFF('2024-03-15', '2023-03-16') → 364天（不满365天）
结论：三种方法在临界情况下结果一致
性能对比：
-- ✅ 好：范围查询，可以使用索引
WHERE hire_date <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
-- ⚠️ 一般：函数计算，可能导致索引失效
WHERE TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) >= 1
WHERE DATEDIFF(CURDATE(), hire_date) >= 365
实际应用扩展：
-- 查询即将满1年的员工（还有30天内）
SELECT
name,
hire_date,
DATE_ADD(hire_date, INTERVAL 1 YEAR) AS anniversary_date,
DATEDIFF(DATE_ADD(hire_date, INTERVAL 1 YEAR), CURDATE()) AS days_until_anniversary
FROM employees
WHERE hire_date > DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
AND hire_date <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR - 30 DAY)
ORDER BY days_until_anniversary;
-- 按入职年份统计满1年员工数
SELECT
YEAR(hire_date) AS hire_year,
COUNT(*) AS emp_count,
AVG(TIMESTAMPDIFF(YEAR, hire_date, CURDATE())) AS avg_years_worked
FROM employees
WHERE TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) >= 1
GROUP BY hire_year
ORDER BY hire_year DESC;
-- 计算员工留存率（入职满1年的比例）
SELECT
CONCAT(
ROUND(
COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) >= 1 THEN 1 END) * 100.0 /
NULLIF(COUNT(CASE WHEN hire_date <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) THEN 1 END), 0),
2
),
'%'
) AS retention_rate
FROM employees;
题目54：按季度统计员工入职人数
💡 解题思路
这道题考查日期函数和分组统计的组合。需要：
- 提取入职日期的年份和季度
- 按年份和季度分组
- 统计每组的人数
🔍 关键提示
- 使用
QUARTER(date)函数获取季度(1-4) - 使用
YEAR(date)获取年份 - 使用GROUP BY对年份和季度分组
- 可以使用CONCAT格式化输出
📝 实现步骤
- 提取年份：YEAR(hire_date)
- 提取季度：QUARTER(hire_date)
- 按年份和季度分组
- 统计数量并排序
✅ 验证方法
-- 检查每个季度的记录
-- 验证季度值是否在1-4之间
-- 检查是否有遗漏的季度
🚀 扩展思考
- 如何同时显示同比增长？
- 如何可视化季度趋势（如用*表示数量）？
- 如何计算季度平均入职人数？
💯 点击查看参考答案
方法1：基本统计
SELECT
YEAR(hire_date) AS hire_year,
QUARTER(hire_date) AS hire_quarter,
COUNT(*) AS emp_count
FROM employees
GROUP BY hire_year, hire_quarter
ORDER BY hire_year, hire_quarter;
方法2：格式化显示
SELECT
CONCAT(YEAR(hire_date), '-Q', QUARTER(hire_date)) AS year_quarter,
COUNT(*) AS emp_count,
GROUP_CONCAT(name ORDER BY hire_date) AS employees
FROM employees
GROUP BY YEAR(hire_date), QUARTER(hire_date)
ORDER BY YEAR(hire_date), QUARTER(hire_date);
方法3：添加季度日期范围
SELECT
YEAR(hire_date) AS year,
QUARTER(hire_date) AS quarter,
CONCAT(YEAR(hire_date), '-Q', QUARTER(hire_date)) AS period,
MIN(hire_date) AS quarter_start,
MAX(hire_date) AS quarter_end,
COUNT(*) AS emp_count
FROM employees
GROUP BY year, quarter
ORDER BY year DESC, quarter DESC;
方法4：横向展示（行转列）
SELECT
YEAR(hire_date) AS year,
SUM(CASE WHEN QUARTER(hire_date) = 1 THEN 1 ELSE 0 END) AS Q1,
SUM(CASE WHEN QUARTER(hire_date) = 2 THEN 1 ELSE 0 END) AS Q2,
SUM(CASE WHEN QUARTER(hire_date) = 3 THEN 1 ELSE 0 END) AS Q3,
SUM(CASE WHEN QUARTER(hire_date) = 4 THEN 1 ELSE 0 END) AS Q4,
COUNT(*) AS year_total
FROM employees
GROUP BY year
ORDER BY year DESC;
方法5：计算环比增长
SELECT
year_quarter,
emp_count,
prev_quarter_count,
emp_count - prev_quarter_count AS diff,
CASE
WHEN prev_quarter_count > 0 THEN
CONCAT(ROUND((emp_count - prev_quarter_count) * 100.0 / prev_quarter_count, 2), '%')
ELSE 'N/A'
END AS growth_rate
FROM (
SELECT
CONCAT(YEAR(hire_date), '-Q', QUARTER(hire_date)) AS year_quarter,
COUNT(*) AS emp_count,
LAG(COUNT(*), 1) OVER (ORDER BY YEAR(hire_date), QUARTER(hire_date)) AS prev_quarter_count
FROM employees
GROUP BY YEAR(hire_date), QUARTER(hire_date)
) AS quarterly_stats
ORDER BY year_quarter DESC;
方法6：完整的季度分析
SELECT
YEAR(hire_date) AS year,
QUARTER(hire_date) AS quarter,
CONCAT(YEAR(hire_date), 'Q', QUARTER(hire_date)) AS period,
-- 季度起止日期
DATE_FORMAT(
MAKEDATE(YEAR(hire_date), 1) + INTERVAL (QUARTER(hire_date) - 1) QUARTER,
'%Y-%m-%d'
) AS quarter_start_date,
DATE_FORMAT(
MAKEDATE(YEAR(hire_date), 1) + INTERVAL QUARTER(hire_date) QUARTER - INTERVAL 1 DAY,
'%Y-%m-%d'
) AS quarter_end_date,
-- 统计数据
COUNT(*) AS emp_count,
ROUND(AVG(salary), 2) AS avg_salary,
MIN(hire_date) AS first_hire,
MAX(hire_date) AS last_hire,
-- 累计统计
SUM(COUNT(*)) OVER (ORDER BY YEAR(hire_date), QUARTER(hire_date)) AS cumulative_count
FROM employees
GROUP BY year, quarter
ORDER BY year DESC, quarter DESC;
要点说明：
QUARTER函数说明：
QUARTER('2024-01-01') → 1 (1-3月)
QUARTER('2024-04-01') → 2 (4-6月)
QUARTER('2024-07-01') → 3 (7-9月)
QUARTER('2024-10-01') → 4 (10-12月)
计算季度起止日期：
-- Q1: 1月1日 - 3月31日
-- Q2: 4月1日 - 6月30日
-- Q3: 7月1日 - 9月30日
-- Q4: 10月1日 - 12月31日
-- 季度第一天
DATE_FORMAT(
MAKEDATE(YEAR(date), 1) + INTERVAL (QUARTER(date) - 1) QUARTER,
'%Y-%m-%d'
)
-- 季度最后一天
LAST_DAY(
MAKEDATE(YEAR(date), 1) + INTERVAL QUARTER(date) QUARTER - INTERVAL 1 DAY
)
实际应用场景：
-- 按季度统计招聘效果
SELECT
CONCAT(YEAR(hire_date), 'Q', QUARTER(hire_date)) AS quarter,
COUNT(*) AS total_hired,
SUM(CASE WHEN dept_id = 1 THEN 1 ELSE 0 END) AS tech_dept,
SUM(CASE WHEN dept_id = 2 THEN 1 ELSE 0 END) AS sales_dept,
ROUND(AVG(salary), 2) AS avg_starting_salary
FROM employees
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
GROUP BY YEAR(hire_date), QUARTER(hire_date)
ORDER BY quarter DESC;
-- 季度入职趋势可视化（简单文本图表）
SELECT
CONCAT(YEAR(hire_date), 'Q', QUARTER(hire_date)) AS quarter,
COUNT(*) AS emp_count,
REPEAT('█', COUNT(*)) AS bar_chart
FROM employees
GROUP BY YEAR(hire_date), QUARTER(hire_date)
ORDER BY quarter;
-- 查询入职最多的季度
SELECT
CONCAT(YEAR(hire_date), 'Q', QUARTER(hire_date)) AS quarter,
COUNT(*) AS emp_count
FROM employees
GROUP BY YEAR(hire_date), QUARTER(hire_date)
ORDER BY emp_count DESC
LIMIT 5;
其他时间维度统计：
-- 按月统计
SELECT
DATE_FORMAT(hire_date, '%Y-%m') AS month,
COUNT(*) AS emp_count
FROM employees
GROUP BY month
ORDER BY month DESC;
-- 按周统计
SELECT
YEARWEEK(hire_date) AS year_week,
COUNT(*) AS emp_count
FROM employees
GROUP BY year_week
ORDER BY year_week DESC;
-- 按星期几统计
SELECT
DAYNAME(hire_date) AS day_of_week,
DAYOFWEEK(hire_date) AS day_num,
COUNT(*) AS emp_count
FROM employees
GROUP BY day_of_week, day_num
ORDER BY day_num;
题目55：查询每个部门内工资的累计和
💡 解题思路
这道题考查窗口函数的聚合应用。需要：
- 使用SUM窗口函数
- 按部门分区（PARTITION BY）
- 按某个字段排序计算累计和
🔍 关键提示
SUM(salary) OVER (PARTITION BY dept_id ORDER BY ...)- PARTITION BY按部门分组
- ORDER BY定义累计顺序（通常按入职日期或员工ID）
- 理解累计和的概念
📝 实现步骤
- 选择要显示的字段
- 使用SUM窗口函数计算累计和
- PARTITION BY dept_id按部门分区
- ORDER BY定义累计顺序
✅ 验证方法
-- 检查每个部门的最后一条累计和是否等于部门工资总和
SELECT dept_id, SUM(salary) FROM employees GROUP BY dept_id;
🚀 扩展思考
- 如何计算移动平均值？
- 如何计算每个员工的工资占部门总工资的百分比？
- 如何同时显示多个累计指标？
💯 点击查看参考答案
方法1：按入职日期累计（推荐）
SELECT
dept_id,
name,
hire_date,
salary,
SUM(salary) OVER (
PARTITION BY dept_id
ORDER BY hire_date
) AS cumulative_salary
FROM employees
ORDER BY dept_id, hire_date;
方法2：按员工ID累计
SELECT
dept_id,
emp_id,
name,
salary,
SUM(salary) OVER (
PARTITION BY dept_id
ORDER BY emp_id
) AS cumulative_salary,
-- 同时显示部门总工资
SUM(salary) OVER (PARTITION BY dept_id) AS dept_total_salary,
-- 计算百分比
ROUND(
salary * 100.0 / SUM(salary) OVER (PARTITION BY dept_id),
2
) AS salary_percentage
FROM employees
ORDER BY dept_id, emp_id;
方法3：完整的累计分析
SELECT
d.dept_name,
e.name,
e.hire_date,
e.salary,
-- 累计工资
SUM(e.salary) OVER (
PARTITION BY e.dept_id
ORDER BY e.hire_date
) AS cumulative_salary,
-- 累计人数
ROW_NUMBER() OVER (
PARTITION BY e.dept_id
ORDER BY e.hire_date
) AS cumulative_emp_count,
-- 累计平均工资
AVG(e.salary) OVER (
PARTITION BY e.dept_id
ORDER BY e.hire_date
) AS cumulative_avg_salary,
-- 部门总计
SUM(e.salary) OVER (PARTITION BY e.dept_id) AS dept_total,
COUNT(*) OVER (PARTITION BY e.dept_id) AS dept_emp_count
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_name, e.hire_date;
方法4：移动平均（最近3人）
SELECT
dept_id,
name,
hire_date,
salary,
-- 移动平均（当前行及前2行）
AVG(salary) OVER (
PARTITION BY dept_id
ORDER BY hire_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3,
-- 累计和
SUM(salary) OVER (
PARTITION BY dept_id
ORDER BY hire_date
) AS cumulative_sum
FROM employees
ORDER BY dept_id, hire_date;
方法5：不使用窗口函数（MySQL 5.7兼容）
SELECT
e1.dept_id,
e1.name,
e1.hire_date,
e1.salary,
(
SELECT SUM(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
AND e2.hire_date <= e1.hire_date
) AS cumulative_salary
FROM employees e1
ORDER BY e1.dept_id, e1.hire_date;
方法6：多维度累计统计
SELECT
dept_id,
name,
hire_date,
salary,
-- 按时间累计
SUM(salary) OVER (
PARTITION BY dept_id
ORDER BY hire_date
) AS time_cumulative,
-- 按工资排序累计（从高到低）
SUM(salary) OVER (
PARTITION BY dept_id
ORDER BY salary DESC
) AS salary_rank_cumulative,
-- 全部累计（部门总和）
SUM(salary) OVER (PARTITION BY dept_id) AS dept_total,
-- 全局累计（不分区）
SUM(salary) OVER (ORDER BY hire_date) AS company_cumulative
FROM employees
ORDER BY dept_id, hire_date;
要点说明：
窗口函数的帧（Frame）概念：
-- 默认：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- 从分区开始到当前行（累计和的默认行为）
-- 其他选项：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 所有之前的行到当前
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 前2行到当前（移动平均）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- 当前到最后
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING -- 前1行到后1行
累计和的验证：
-- 最后一行的累计和应该等于分组总和
SELECT
dept_id,
MAX(cumulative_salary) AS last_cumulative,
SUM(salary) AS actual_total
FROM (
SELECT
dept_id,
salary,
SUM(salary) OVER (PARTITION BY dept_id ORDER BY emp_id) AS cumulative_salary
FROM employees
) AS cum_data
GROUP BY dept_id;
-- last_cumulative应该等于actual_total
实际应用场景：
-- 查询累计工资达到目标的日期
SELECT
dept_id,
name,
hire_date,
cumulative_salary
FROM (
SELECT
dept_id,
name,
hire_date,
SUM(salary) OVER (
PARTITION BY dept_id
ORDER BY hire_date
) AS cumulative_salary
FROM employees
) AS cum_data
WHERE cumulative_salary >= 100000 -- 累计达到10万
ORDER BY dept_id, hire_date;
-- 计算每个员工加入后的部门平均工资变化
SELECT
dept_id,
name,
hire_date,
salary,
ROUND(
AVG(salary) OVER (
PARTITION BY dept_id
ORDER BY hire_date
),
2
) AS avg_salary_after_join,
ROUND(
AVG(salary) OVER (PARTITION BY dept_id),
2
) AS current_avg_salary
FROM employees
ORDER BY dept_id, hire_date;
-- 识别工资增长异常（与移动平均偏差过大）
SELECT
dept_id,
name,
salary,
moving_avg,
ABS(salary - moving_avg) AS deviation,
CASE
WHEN ABS(salary - moving_avg) > moving_avg * 0.5 THEN '异常'
ELSE '正常'
END AS status
FROM (
SELECT
dept_id,
name,
salary,
AVG(salary) OVER (
PARTITION BY dept_id
ORDER BY hire_date
ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
) AS moving_avg
FROM employees
) AS avg_data
WHERE moving_avg IS NOT NULL
ORDER BY deviation DESC;
第七关：数据操作 - 学习指南
题目56：向员工表插入一条新记录
💡 解题思路
这道题考查INSERT语句的基本用法。需要：
- 了解INSERT语句的基本语法
- 明确要插入哪些字段
- 注意数据类型和引号的使用
🔍 关键提示
INSERT INTO 表名 (字段列表) VALUES (值列表)- 字符串和日期需要用单引号包裹
- 可以省略字段列表（但不推荐）
- 自增字段可以不指定值
📝 实现步骤
- 确定要插入的字段（排除自增ID）
- 准备对应的值
- 编写INSERT语句
- 执行并验证
✅ 验证方法
-- 插入后查询验证
SELECT * FROM employees ORDER BY emp_id DESC LIMIT 1;
-- 或查询总数是否增加
SELECT COUNT(*) FROM employees;
🚀 扩展思考
- 如何插入时忽略某些字段（使用默认值）？
- 如果插入重复的唯一字段会怎样？
- 如何在插入后获取自增ID？
💯 点击查看参考答案
方法1：指定所有字段（推荐）
INSERT INTO employees (name, department, salary, hire_date, manager_id)
VALUES ('张伟', '技术部', 8000.00, '2024-01-15', 1);
方法2：使用DEFAULT关键字
INSERT INTO employees (name, department, salary, hire_date, manager_id)
VALUES ('李娜', '销售部', DEFAULT, CURDATE(), NULL);
方法3：省略字段列表（不推荐）
-- 必须按表结构顺序提供所有字段的值
INSERT INTO employees
VALUES (NULL, '王强', '财务部', 7500.00, '2024-01-20', 2);
方法4：获取插入后的ID
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('赵敏', '人事部', 6500.00, '2024-01-25');
-- 获取刚插入的ID
SELECT LAST_INSERT_ID() AS new_emp_id;
要点说明：
- 指定字段列表：明确哪些字段要插入值，代码更清晰
- 字符串引号：MySQL中字符串用单引号或双引号都可以，推荐单引号
- 日期格式：'YYYY-MM-DD'或使用函数如CURDATE()、NOW()
- NULL值：可以显式插入NULL（如果字段允许）
- DEFAULT：使用字段定义的默认值
常见错误：
-- ❌ 错误：字符串没有引号
INSERT INTO employees (name) VALUES (张三);
-- ✅ 正确：字符串用引号
INSERT INTO employees (name) VALUES ('张三');
-- ❌ 错误：字段数和值数不匹配
INSERT INTO employees (name, salary) VALUES ('李四');
-- ✅ 正确：字段和值一一对应
INSERT INTO employees (name, salary) VALUES ('李四', 5000);
实用技巧：
-- 插入并忽略重复（主键或唯一索引冲突时跳过）
INSERT IGNORE INTO employees (emp_id, name) VALUES (1, '已存在');
-- 插入或更新（存在则更新，不存在则插入）
INSERT INTO employees (emp_id, name, salary)
VALUES (1, '张三', 8000)
ON DUPLICATE KEY UPDATE salary = 8000;
-- 插入并返回完整记录（需要配合查询）
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('新员工', '技术部', 9000, CURDATE());
SELECT * FROM employees WHERE emp_id = LAST_INSERT_ID();
题目57：批量插入5条部门记录
💡 解题思路
这道题考查批量INSERT的用法。需要：
- 掌握一次插入多条记录的语法
- 理解批量插入的性能优势
- 正确组织多条记录的数据
🔍 关键提示
- 多条VALUES用逗号分隔
INSERT INTO 表名 (字段) VALUES (值1), (值2), (值3)- 批量插入比多次单条插入快得多
- 建议每次插入500-1000条为佳
📝 实现步骤
- 确定要插入的字段
- 准备5条记录的数据
- 用逗号连接多个VALUES
- 一次性执行
✅ 验证方法
-- 验证插入的记录数
SELECT COUNT(*) FROM departments;
-- 查看新插入的记录
SELECT * FROM departments ORDER BY dept_id DESC LIMIT 5;
🚀 扩展思考
- 批量插入和单条插入的性能差异有多大？
- 一次性插入太多数据会有什么问题？
- 如何批量插入时处理部分失败的情况？
💯 点击查看参考答案
方法1：标准批量插入（推荐）
INSERT INTO departments (dept_name, location, budget)
VALUES
('技术部', '北京', 500000.00),
('销售部', '上海', 300000.00),
('财务部', '北京', 200000.00),
('人事部', '深圳', 150000.00),
('市场部', '广州', 250000.00);
方法2：使用SET语法（不常用）
INSERT INTO departments
SET dept_name = '技术部', location = '北京', budget = 500000;
INSERT INTO departments
SET dept_name = '销售部', location = '上海', budget = 300000;
-- 需要执行5次，不推荐
方法3：从查询结果批量插入
-- 假设有临时表或其他来源
INSERT INTO departments (dept_name, location, budget)
SELECT dept_name, location, budget
FROM temp_departments
LIMIT 5;
方法4：批量插入并处理冲突
INSERT INTO departments (dept_id, dept_name, location, budget)
VALUES
(1, '技术部', '北京', 500000.00),
(2, '销售部', '上海', 300000.00),
(3, '财务部', '北京', 200000.00),
(4, '人事部', '深圳', 150000.00),
(5, '市场部', '广州', 250000.00)
ON DUPLICATE KEY UPDATE
dept_name = VALUES(dept_name),
location = VALUES(location),
budget = VALUES(budget);
要点说明：
- 性能优势：批量插入比单条插入快10-100倍
- 事务处理：多条记录在一个事务中，要么全部成功，要么全部失败
- 格式规范：每个VALUES一行，便于阅读和维护
- 数量控制：建议每次500-1000条，太多可能导致超时或锁表
性能对比示例：
-- ❌ 低效：单条插入（假设需要1秒/条）
INSERT INTO departments (dept_name) VALUES ('部门1'); -- 1秒
INSERT INTO departments (dept_name) VALUES ('部门2'); -- 1秒
INSERT INTO departments (dept_name) VALUES ('部门3'); -- 1秒
-- 总计：3秒
-- ✅ 高效：批量插入（假设只需0.1秒）
INSERT INTO departments (dept_name)
VALUES ('部门1'), ('部门2'), ('部门3'); -- 0.1秒
-- 性能提升：30倍
大数据量批量插入最佳实践：
-- 如果要插入10000条记录，分批处理
START TRANSACTION;
-- 第1批：1-1000
INSERT INTO departments (dept_name, location)
VALUES
('部门1', '城市1'),
('部门2', '城市2'),
-- ... 共1000条
('部门1000', '城市1000');
-- 第2批：1001-2000
INSERT INTO departments (dept_name, location)
VALUES
('部门1001', '城市1001'),
-- ... 共1000条
('部门2000', '城市2000');
-- ... 继续分批
COMMIT;
错误处理：
-- 使用IGNORE忽略错误（如主键冲突）
INSERT IGNORE INTO departments (dept_id, dept_name)
VALUES
(1, '已存在部门'), -- 如果ID=1存在，这条会跳过
(2, '新部门'), -- 正常插入
(3, '另一个新部门'); -- 正常插入
题目58：将所有'技术部'员工的工资增加10%
💡 解题思路
这道题考查UPDATE语句和计算的使用。需要：
- 使用WHERE筛选特定部门
- 使用表达式计算新工资
- 先验证再执行更新
🔍 关键提示
UPDATE 表名 SET 字段=新值 WHERE 条件- 可以在SET中使用计算表达式
- 务必先用SELECT验证WHERE条件
- 没有WHERE会更新所有记录
📝 实现步骤
- 先用SELECT查询技术部员工
- 验证WHERE条件是否正确
- 编写UPDATE语句：salary = salary * 1.1
- 执行后再次查询验证
✅ 验证方法
-- 更新前：查看技术部员工的工资
SELECT name, department, salary FROM employees WHERE department = '技术部';
-- 执行UPDATE
-- 更新后：再次查询对比
SELECT name, department, salary FROM employees WHERE department = '技术部';
🚀 扩展思考
- 如何同时更新多个字段？
- 如何根据不同条件更新不同的值？
- 如果误操作更新错了，如何回滚？
💯 点击查看参考答案
方法1：基本更新（推荐）
-- 第1步：先验证（非常重要！）
SELECT emp_id, name, department, salary, salary * 1.1 AS new_salary
FROM employees
WHERE department = '技术部';
-- 第2步：确认无误后执行更新
UPDATE employees
SET salary = salary * 1.1
WHERE department = '技术部';
-- 第3步：验证结果
SELECT name, department, salary FROM employees WHERE department = '技术部';
方法2：使用ROUND保留小数位
UPDATE employees
SET salary = ROUND(salary * 1.1, 2)
WHERE department = '技术部';
方法3：同时更新多个字段
UPDATE employees
SET
salary = salary * 1.1,
updated_at = NOW()
WHERE department = '技术部';
方法4：使用安全模式（事务）
-- 开启事务
START TRANSACTION;
-- 执行更新
UPDATE employees
SET salary = salary * 1.1
WHERE department = '技术部';
-- 验证结果
SELECT name, salary FROM employees WHERE department = '技术部';
-- 如果正确则提交，否则回滚
COMMIT; -- 或 ROLLBACK;
方法5：条件更新（不同条件不同涨幅）
UPDATE employees
SET salary = CASE
WHEN department = '技术部' THEN salary * 1.1
WHEN department = '销售部' THEN salary * 1.15
WHEN department = '财务部' THEN salary * 1.05
ELSE salary
END;
方法6：限制更新数量
-- 只更新前10名技术部员工
UPDATE employees
SET salary = salary * 1.1
WHERE department = '技术部'
ORDER BY hire_date
LIMIT 10;
要点说明：
- 安全第一：更新前必须用SELECT验证WHERE条件
- 计算表达式：
salary * 1.1表示原值的110% - 小数处理：使用ROUND()避免过多小数位
- 影响行数：UPDATE执行后会显示影响的行数
常见错误：
-- ❌ 危险：忘记WHERE条件，会更新所有记录！
UPDATE employees SET salary = salary * 1.1;
-- ✅ 正确：加上WHERE条件
UPDATE employees SET salary = salary * 1.1 WHERE department = '技术部';
-- ❌ 错误：直接设置为1.1
UPDATE employees SET salary = 1.1 WHERE department = '技术部';
-- ✅ 正确：乘以1.1
UPDATE employees SET salary = salary * 1.1 WHERE department = '技术部';
最佳实践流程：
-- 步骤1：开启事务
START TRANSACTION;
-- 步骤2：执行更新
UPDATE employees
SET salary = salary * 1.1
WHERE department = '技术部';
-- 步骤3：查看影响的行数和结果
SELECT ROW_COUNT() AS affected_rows; -- 查看影响行数
SELECT * FROM employees WHERE department = '技术部'; -- 验证结果
-- 步骤4：确认无误后提交
COMMIT; -- 如果发现错误，执行 ROLLBACK;
记录修改历史：
-- 先备份到历史表
INSERT INTO salary_history (emp_id, old_salary, new_salary, change_date, reason)
SELECT
emp_id,
salary AS old_salary,
salary * 1.1 AS new_salary,
NOW(),
'技术部年度调薪10%'
FROM employees
WHERE department = '技术部';
-- 然后执行更新
UPDATE employees
SET salary = salary * 1.1
WHERE department = '技术部';
题目59：删除工资低于3000的员工记录
💡 解题思路
这道题考查DELETE语句的使用。需要：
- 使用WHERE条件筛选要删除的记录
- 删除前务必验证
- 理解DELETE的不可逆性
🔍 关键提示
DELETE FROM 表名 WHERE 条件- 删除前必须验证WHERE条件
- DELETE没有WHERE会删除所有记录
- 删除操作通常不可逆，务必谨慎
📝 实现步骤
- 先用SELECT查询符合条件的记录
- 确认是要删除的记录
- 执行DELETE语句
- 验证删除结果
✅ 验证方法
-- 删除前：查看要删除的记录
SELECT * FROM employees WHERE salary < 3000;
-- 执行DELETE
-- 删除后：验证是否还存在
SELECT * FROM employees WHERE salary < 3000; -- 应该返回0条
🚀 扩展思考
- DELETE和TRUNCATE有什么区别？
- 如何安全地删除大量数据？
- 误删除后如何恢复？
💯 点击查看参考答案
方法1：基本删除（推荐使用事务）
-- 第1步：先查询要删除的记录（非常重要！）
SELECT emp_id, name, salary
FROM employees
WHERE salary < 3000;
-- 第2步：开启事务（安全删除）
START TRANSACTION;
-- 第3步：执行删除
DELETE FROM employees
WHERE salary < 3000;
-- 第4步：验证结果
SELECT * FROM employees WHERE salary < 3000; -- 应该返回空
SELECT COUNT(*) FROM employees; -- 查看剩余记录数
-- 第5步：确认无误后提交
COMMIT; -- 如果发现错误，立即执行 ROLLBACK;
方法2：先备份再删除
-- 备份要删除的记录
CREATE TABLE employees_backup AS
SELECT * FROM employees WHERE salary < 3000;
-- 然后删除
DELETE FROM employees WHERE salary < 3000;
-- 如果需要恢复
-- INSERT INTO employees SELECT * FROM employees_backup;
方法3：软删除（推荐用于生产环境）
-- 不真正删除，只标记为已删除
UPDATE employees
SET is_deleted = 1, deleted_at = NOW()
WHERE salary < 3000;
-- 查询时排除已删除记录
SELECT * FROM employees WHERE is_deleted = 0;
方法4：限制删除数量
-- 分批删除（避免锁表）
DELETE FROM employees
WHERE salary < 3000
ORDER BY emp_id
LIMIT 100;
-- 多次执行直到没有记录
方法5：使用子查询
DELETE FROM employees
WHERE emp_id IN (
SELECT emp_id
FROM (
SELECT emp_id FROM employees WHERE salary < 3000
) AS temp
);
要点说明：
- 验证先行：删除前必须用SELECT验证条件
- 事务保护：在事务中删除，发现错误可回滚
- 软删除：生产环境推荐软删除，保留数据可追溯
- 分批删除：大量数据删除时分批进行，避免锁表
DELETE vs TRUNCATE：
-- DELETE：逐行删除，可回滚，可加WHERE条件，不重置自增ID
DELETE FROM employees WHERE salary < 3000;
-- TRUNCATE：直接删除整个表数据并重建，速度快，重置自增ID，不能回滚
TRUNCATE TABLE employees; -- 删除所有数据
-- TRUNCATE特点：
-- ✓ 速度非常快
-- ✓ 重置AUTO_INCREMENT
-- ✗ 不能加WHERE条件（只能清空整个表）
-- ✗ 不能回滚
常见错误：
-- ❌ 致命错误：忘记WHERE条件，删除所有记录！
DELETE FROM employees;
-- ✅ 正确：始终加WHERE条件
DELETE FROM employees WHERE salary < 3000;
-- ❌ 错误：在生产环境直接删除
DELETE FROM employees WHERE salary < 3000;
-- ✅ 正确：在事务中删除，便于回滚
START TRANSACTION;
DELETE FROM employees WHERE salary < 3000;
-- 验证后再 COMMIT 或 ROLLBACK
安全删除最佳实践：
-- 1. 查询要删除的记录数量
SELECT COUNT(*) AS will_delete FROM employees WHERE salary < 3000;
-- 2. 查看具体记录
SELECT * FROM employees WHERE salary < 3000 ORDER BY emp_id;
-- 3. 备份（可选但推荐）
CREATE TABLE employees_low_salary_backup AS
SELECT * FROM employees WHERE salary < 3000;
-- 4. 在事务中执行删除
START TRANSACTION;
DELETE FROM employees WHERE salary < 3000;
-- 5. 验证删除结果
SELECT COUNT(*) FROM employees; -- 查看总数是否正确
SELECT * FROM employees WHERE salary < 3000; -- 应该返回空
-- 6. 确认无误后提交
COMMIT;
-- 如果发现错误，立即回滚
-- ROLLBACK;
关联删除（级联删除）：
-- 删除员工的同时删除其相关记录
START TRANSACTION;
-- 先删除关联表的记录
DELETE FROM employee_projects WHERE emp_id IN (
SELECT emp_id FROM employees WHERE salary < 3000
);
-- 再删除主表记录
DELETE FROM employees WHERE salary < 3000;
COMMIT;
题目60：将2019年之前入职的员工工资统一增加500元
💡 解题思路
这道题考查日期比较和UPDATE的结合使用。需要：
- 使用日期比较筛选员工
- 使用加法更新工资
- 先验证后更新
🔍 关键提示
- 日期比较：
WHERE hire_date < '2019-01-01' - 工资增加：
SET salary = salary + 500 - 可以使用YEAR()函数或直接比较日期
📝 实现步骤
- 确定日期条件（2019年之前）
- 先SELECT验证符合条件的员工
- 执行UPDATE增加工资
- 验证更新结果
✅ 验证方法
-- 更新前
SELECT name, hire_date, salary FROM employees WHERE hire_date < '2019-01-01';
-- 执行UPDATE
-- 更新后对比
SELECT name, hire_date, salary FROM employees WHERE hire_date < '2019-01-01';
🚀 扩展思考
- 如何计算工龄并根据工龄调整工资？
- 如何同时记录调薪历史？
- 不同年份入职的员工如何差异化调整？
💯 点击查看参考答案
方法1：直接日期比较（推荐）
-- 第1步：验证条件
SELECT emp_id, name, hire_date, salary, salary + 500 AS new_salary
FROM employees
WHERE hire_date < '2019-01-01'
ORDER BY hire_date;
-- 第2步：执行更新
UPDATE employees
SET salary = salary + 500
WHERE hire_date < '2019-01-01';
方法2：使用YEAR函数
UPDATE employees
SET salary = salary + 500
WHERE YEAR(hire_date) < 2019;
方法3：使用DATE函数确保日期格式
UPDATE employees
SET salary = salary + 500
WHERE DATE(hire_date) < DATE('2019-01-01');
方法4：同时更新多个字段并记录
UPDATE employees
SET
salary = salary + 500,
updated_at = NOW(),
last_raise_date = CURDATE(),
last_raise_amount = 500
WHERE hire_date < '2019-01-01';
方法5：根据不同入职年份差异化调整
UPDATE employees
SET salary = CASE
WHEN hire_date < '2015-01-01' THEN salary + 1000 -- 2015年前入职+1000
WHEN hire_date < '2017-01-01' THEN salary + 800 -- 2015-2016年入职+800
WHEN hire_date < '2019-01-01' THEN salary + 500 -- 2017-2018年入职+500
ELSE salary
END
WHERE hire_date < '2019-01-01';
方法6：使用事务并记录历史
START TRANSACTION;
-- 记录调薪历史
INSERT INTO salary_changes (emp_id, old_salary, new_salary, change_amount, change_date, reason)
SELECT
emp_id,
salary,
salary + 500,
500,
NOW(),
'2019年前入职员工统一调薪'
FROM employees
WHERE hire_date < '2019-01-01';
-- 执行更新
UPDATE employees
SET salary = salary + 500
WHERE hire_date < '2019-01-01';
-- 验证
SELECT COUNT(*) AS affected_count FROM employees WHERE hire_date < '2019-01-01';
COMMIT;
要点说明：
- 日期格式：MySQL日期格式为'YYYY-MM-DD'
- 比较运算符：
<表示早于指定日期 - 性能考虑：直接日期比较比使用函数性能更好
- 索引利用：
hire_date < '2019-01-01'可以利用索引
不同日期条件写法对比：
-- ✅ 推荐：直接比较（可以利用索引）
WHERE hire_date < '2019-01-01'
-- ⚠️ 一般：使用函数（可能导致索引失效）
WHERE YEAR(hire_date) < 2019
-- ✅ 也可以：使用BETWEEN
WHERE hire_date BETWEEN '1900-01-01' AND '2018-12-31'
-- ✅ 灵活：使用DATE_SUB
WHERE hire_date < DATE_SUB(CURDATE(), INTERVAL 6 YEAR) -- 6年前
根据工龄差异化调薪：
UPDATE employees
SET salary = salary + (
CASE
WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) >= 10 THEN 1000
WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) >= 5 THEN 800
WHEN TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) >= 3 THEN 500
ELSE 300
END
)
WHERE hire_date < '2019-01-01';
完整的安全更新流程：
-- 1. 查看影响范围
SELECT
COUNT(*) AS total_employees,
SUM(salary) AS current_total_salary,
SUM(salary + 500) AS new_total_salary,
SUM(500) AS total_increase
FROM employees
WHERE hire_date < '2019-01-01';
-- 2. 查看具体员工列表
SELECT emp_id, name, hire_date, salary, salary + 500 AS new_salary
FROM employees
WHERE hire_date < '2019-01-01'
ORDER BY hire_date;
-- 3. 在事务中执行
START TRANSACTION;
UPDATE employees
SET salary = salary + 500
WHERE hire_date < '2019-01-01';
-- 4. 验证结果
SELECT COUNT(*) AS updated_count FROM employees WHERE hire_date < '2019-01-01';
-- 5. 确认无误后提交
COMMIT; -- 或 ROLLBACK;
批量更新性能优化：
-- 如果员工数量很大，考虑分批更新
-- 第1批
UPDATE employees
SET salary = salary + 500
WHERE hire_date < '2019-01-01'
AND emp_id BETWEEN 1 AND 1000;
-- 第2批
UPDATE employees
SET salary = salary + 500
WHERE hire_date < '2019-01-01'
AND emp_id BETWEEN 1001 AND 2000;
-- 继续...
题目61：复制一张员工表的结构和数据到新表
💡 解题思路
这道题考查表复制的多种方法。需要：
- 区分只复制结构和同时复制数据
- 了解CREATE TABLE ... LIKE和CREATE TABLE ... AS SELECT的区别
- 掌握INSERT INTO ... SELECT的用法
🔍 关键提示
CREATE TABLE 新表 LIKE 原表- 只复制结构CREATE TABLE 新表 AS SELECT * FROM 原表- 复制结构和数据INSERT INTO 新表 SELECT * FROM 原表- 向已存在的表插入数据
📝 实现步骤
- 确定是只复制结构还是同时复制数据
- 选择合适的SQL语句
- 执行并验证
- 检查索引和约束是否也被复制
✅ 验证方法
-- 验证表结构
DESC employees_copy;
-- 验证数据
SELECT COUNT(*) FROM employees_copy;
SELECT * FROM employees_copy LIMIT 10;
🚀 扩展思考
- 复制表时索引会一起复制吗？
- 如何只复制部分数据？
- 如何复制表到另一个数据库？
💯 点击查看参考答案
方法1：CREATE TABLE ... AS SELECT（最常用）
-- 复制结构和全部数据
CREATE TABLE employees_copy AS
SELECT * FROM employees;
-- 验证
SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees_copy;
-- 两个表记录数应该相同
方法2：CREATE TABLE ... LIKE + INSERT
-- 第1步：只复制表结构（包括索引）
CREATE TABLE employees_copy LIKE employees;
-- 第2步：插入数据
INSERT INTO employees_copy
SELECT * FROM employees;
方法3：复制部分字段和数据
-- 只复制特定字段
CREATE TABLE employees_simple AS
SELECT emp_id, name, department, salary
FROM employees;
方法4：复制部分数据（带条件）
-- 只复制技术部员工
CREATE TABLE tech_employees AS
SELECT * FROM employees
WHERE department = '技术部';
方法5：复制时修改数据
-- 复制并转换数据
CREATE TABLE employees_summary AS
SELECT
emp_id,
name,
department,
salary,
YEAR(hire_date) AS hire_year,
TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years_worked
FROM employees;
方法6：复制到另一个数据库
-- 在目标数据库中执行
CREATE TABLE target_db.employees AS
SELECT * FROM source_db.employees;
要点说明：
LIKE vs AS SELECT 区别：
-- CREATE TABLE ... LIKE
-- ✓ 完整复制表结构（字段类型、默认值、索引、约束）
-- ✗ 不复制数据
CREATE TABLE employees_structure LIKE employees;
-- CREATE TABLE ... AS SELECT
-- ✓ 复制表结构和数据
-- ✗ 不复制索引和约束（只有字段定义）
CREATE TABLE employees_data AS SELECT * FROM employees;
完整对比表：
| 特性 | LIKE | AS SELECT |
|---|---|---|
| 复制字段定义 | ✓ | ✓ |
| 复制数据 | ✗ | ✓ |
| 复制索引 | ✓ | ✗ |
| 复制外键 | ✓ | ✗ |
| 复制AUTO_INCREMENT | ✓ | ✗ |
| 复制默认值 | ✓ | ✗ |
| 复制注释 | ✓ | ✗ |
最完整的复制方法：
-- 1. 复制表结构（包括索引和约束）
CREATE TABLE employees_backup LIKE employees;
-- 2. 复制所有数据
INSERT INTO employees_backup SELECT * FROM employees;
-- 3. 验证
SELECT
(SELECT COUNT(*) FROM employees) AS original_count,
(SELECT COUNT(*) FROM employees_backup) AS backup_count;
-- 4. 比较表结构
SHOW CREATE TABLE employees;
SHOW CREATE TABLE employees_backup;
复制特定范围的数据：
-- 只复制最近一年的数据
CREATE TABLE employees_recent AS
SELECT * FROM employees
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);
-- 只复制前1000条记录
CREATE TABLE employees_sample AS
SELECT * FROM employees
ORDER BY emp_id
LIMIT 1000;
-- 复制特定部门
CREATE TABLE employees_tech AS
SELECT * FROM employees
WHERE department IN ('技术部', '研发部');
定期备份表的实践：
-- 带时间戳的备份表名
SET @backup_table = CONCAT('employees_backup_', DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'));
-- 创建备份（需要使用预处理语句）
SET @sql = CONCAT('CREATE TABLE ', @backup_table, ' AS SELECT * FROM employees');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 或者简单方式（手动替换日期）
CREATE TABLE employees_backup_20240115 AS SELECT * FROM employees;
手动重建索引（如果使用AS SELECT）：
-- 1. 创建表和数据
CREATE TABLE employees_copy AS SELECT * FROM employees;
-- 2. 手动添加主键
ALTER TABLE employees_copy ADD PRIMARY KEY (emp_id);
-- 3. 手动添加索引
CREATE INDEX idx_department ON employees_copy(department);
CREATE INDEX idx_salary ON employees_copy(salary);
-- 4. 查看原表的所有索引
SHOW INDEX FROM employees;
-- 5. 根据原表的索引定义，逐一创建
跨数据库复制：
-- 方法1：在同一MySQL服务器上
CREATE TABLE new_database.employees AS
SELECT * FROM old_database.employees;
-- 方法2：使用mysqldump（命令行）
-- mysqldump -u用户名 -p old_database employees > employees.sql
-- mysql -u用户名 -p new_database < employees.sql
-- 方法3：使用INSERT INTO ... SELECT
INSERT INTO new_database.employees
SELECT * FROM old_database.employees;
题目62：删除重复的部门记录，只保留ID最小的
💡 解题思路
这道题考查去重删除的技巧。需要：
- 识别重复记录（通常基于某些字段）
- 确定保留哪条记录（ID最小的）
- 删除其他重复记录
🔍 关键提示
- 使用GROUP BY和HAVING找出重复记录
- 使用子查询确定要删除的ID
- 可以使用NOT IN或LEFT JOIN实现
📝 实现步骤
- 找出重复的部门名称
- 对每组重复记录，找出ID最小的
- 删除ID不是最小的记录
- 验证结果
✅ 验证方法
-- 检查是否还有重复
SELECT dept_name, COUNT(*) AS cnt
FROM departments
GROUP BY dept_name
HAVING COUNT(*) > 1;
🚀 扩展思考
- 如何基于多个字段判断重复？
- 如何保留最新的记录而不是ID最小的？
- 大数据量时如何高效去重？
💯 点击查看参考答案
方法1：使用NOT IN子查询（推荐）
-- 第1步：查看重复记录
SELECT dept_name, COUNT(*) AS duplicate_count
FROM departments
GROUP BY dept_name
HAVING COUNT(*) > 1;
-- 第2步：查看要删除的记录
SELECT d1.*
FROM departments d1
WHERE d1.dept_id NOT IN (
SELECT MIN(d2.dept_id)
FROM departments d2
GROUP BY d2.dept_name
);
-- 第3步：执行删除
DELETE FROM departments
WHERE dept_id NOT IN (
SELECT * FROM (
SELECT MIN(dept_id)
FROM departments
GROUP BY dept_name
) AS temp
);
方法2：使用LEFT JOIN
DELETE d1
FROM departments d1
LEFT JOIN (
SELECT dept_name, MIN(dept_id) AS min_id
FROM departments
GROUP BY dept_name
) d2 ON d1.dept_id = d2.min_id
WHERE d2.min_id IS NULL;
方法3：使用窗口函数（MySQL 8.0+）
-- 标记要删除的记录
DELETE FROM departments
WHERE dept_id IN (
SELECT dept_id FROM (
SELECT
dept_id,
ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY dept_id) AS rn
FROM departments
) AS ranked
WHERE rn > 1
);
方法4：创建临时表去重
-- 第1步：创建去重后的临时表
CREATE TABLE departments_temp AS
SELECT MIN(dept_id) AS dept_id, dept_name, location, budget
FROM departments
GROUP BY dept_name, location, budget;
-- 第2步：删除原表
DROP TABLE departments;
-- 第3步：重命名临时表
RENAME TABLE departments_temp TO departments;
-- 第4步：重建主键和索引
ALTER TABLE departments ADD PRIMARY KEY (dept_id);
方法5：使用EXISTS
DELETE FROM departments d1
WHERE EXISTS (
SELECT 1
FROM departments d2
WHERE d2.dept_name = d1.dept_name
AND d2.dept_id < d1.dept_id
);
要点说明：
- 去重逻辑：保留每组中dept_id最小的记录
- 子查询嵌套：MySQL限制不能在FROM子句中直接引用要修改的表，需要再嵌套一层
- 性能考虑：大数据量时使用临时表方法更高效
- 数据安全：删除前务必备份或在事务中操作
完整的安全去重流程：
-- 1. 备份原表
CREATE TABLE departments_backup AS SELECT * FROM departments;
-- 2. 查看重复情况
SELECT
dept_name,
COUNT(*) AS count,
GROUP_CONCAT(dept_id ORDER BY dept_id) AS ids
FROM departments
GROUP BY dept_name
HAVING COUNT(*) > 1;
-- 3. 在事务中执行删除
START TRANSACTION;
-- 删除重复记录（保留ID最小的）
DELETE FROM departments
WHERE dept_id NOT IN (
SELECT * FROM (
SELECT MIN(dept_id)
FROM departments
GROUP BY dept_name
) AS temp
);
-- 4. 验证结果
SELECT dept_name, COUNT(*) FROM departments GROUP BY dept_name HAVING COUNT(*) > 1;
-- 应该返回空，说明没有重复了
-- 5. 确认无误后提交
COMMIT;
基于多个字段判断重复：
-- 假设dept_name和location组合唯一
DELETE FROM departments
WHERE dept_id NOT IN (
SELECT * FROM (
SELECT MIN(dept_id)
FROM departments
GROUP BY dept_name, location
) AS temp
);
保留最新记录（ID最大）：
-- 保留ID最大的，删除其他
DELETE FROM departments
WHERE dept_id NOT IN (
SELECT * FROM (
SELECT MAX(dept_id)
FROM departments
GROUP BY dept_name
) AS temp
);
保留最近创建的记录：
-- 假设有created_at字段
DELETE FROM departments
WHERE dept_id NOT IN (
SELECT * FROM (
SELECT dept_id
FROM (
SELECT
dept_id,
ROW_NUMBER() OVER (
PARTITION BY dept_name
ORDER BY created_at DESC
) AS rn
FROM departments
) AS ranked
WHERE rn = 1
) AS temp
);
大数据量优化方案：
-- 方案1：分批删除
SET @batch_size = 1000;
SET @deleted = 1;
WHILE @deleted > 0 DO
DELETE FROM departments
WHERE dept_id IN (
SELECT dept_id FROM (
SELECT d1.dept_id
FROM departments d1
WHERE EXISTS (
SELECT 1
FROM departments d2
WHERE d2.dept_name = d1.dept_name
AND d2.dept_id < d1.dept_id
)
LIMIT @batch_size
) AS temp
);
SET @deleted = ROW_COUNT();
END WHILE;
添加唯一约束防止以后重复：
-- 去重后添加唯一约束
ALTER TABLE departments
ADD UNIQUE INDEX uk_dept_name (dept_name);
-- 或使用UNIQUE约束
ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE (dept_name);
题目63：更新员工表，将没有部门的员工分配到'未分配部门'
💡 解题思路
这道题考查处理NULL值和关联更新。需要：
- 识别没有部门的员工（dept_id IS NULL）
- 找到或创建'未分配部门'
- 更新员工的部门ID
🔍 关键提示
- 使用
IS NULL判断空值（不能用= NULL） - 可能需要先确保'未分配部门'存在
- 可以使用子查询获取部门ID
📝 实现步骤
- 检查是否有没有部门的员工
- 确保'未分配部门'存在，不存在则创建
- 更新员工记录，设置dept_id
- 验证更新结果
✅ 验证方法
-- 更新前
SELECT COUNT(*) FROM employees WHERE dept_id IS NULL;
-- 执行UPDATE
-- 更新后
SELECT COUNT(*) FROM employees WHERE dept_id IS NULL; -- 应该为0
🚀 扩展思考
- 如何同时处理其他默认值？
- 如何避免硬编码部门ID？
- 如果部门表没有'未分配部门'怎么办？
💯 点击查看参考答案
方法1：先创建部门再更新（推荐）
-- 第1步：检查'未分配部门'是否存在，不存在则创建
INSERT INTO departments (dept_name, location, budget)
SELECT '未分配部门', '总部', 0
WHERE NOT EXISTS (
SELECT 1 FROM departments WHERE dept_name = '未分配部门'
);
-- 第2步：更新员工记录
UPDATE employees
SET dept_id = (
SELECT dept_id
FROM departments
WHERE dept_name = '未分配部门'
LIMIT 1
)
WHERE dept_id IS NULL;
方法2：使用INSERT IGNORE
-- 第1步：插入'未分配部门'（如果已存在则忽略）
INSERT IGNORE INTO departments (dept_name, location, budget)
VALUES ('未分配部门', '总部', 0);
-- 第2步：更新员工
UPDATE employees
SET dept_id = (SELECT dept_id FROM departments WHERE dept_name = '未分配部门')
WHERE dept_id IS NULL;
方法3：在事务中完成
START TRANSACTION;
-- 确保'未分配部门'存在
INSERT INTO departments (dept_name, location, budget)
SELECT '未分配部门', '总部', 0
FROM DUAL
WHERE NOT EXISTS (
SELECT 1 FROM departments WHERE dept_name = '未分配部门'
);
-- 更新员工
UPDATE employees e
SET e.dept_id = (
SELECT d.dept_id
FROM departments d
WHERE d.dept_name = '未分配部门'
LIMIT 1
)
WHERE e.dept_id IS NULL OR e.dept_id NOT IN (SELECT dept_id FROM departments);
-- 验证
SELECT COUNT(*) AS unassigned_count FROM employees WHERE dept_id IS NULL;
COMMIT;
方法4：使用变量存储部门ID
-- 第1步：获取或创建'未分配部门'的ID
INSERT INTO departments (dept_name, location, budget)
SELECT '未分配部门', '总部', 0
WHERE NOT EXISTS (SELECT 1 FROM departments WHERE dept_name = '未分配部门');
SET @unassigned_dept_id = (
SELECT dept_id FROM departments WHERE dept_name = '未分配部门' LIMIT 1
);
-- 第2步：更新员工
UPDATE employees
SET dept_id = @unassigned_dept_id
WHERE dept_id IS NULL;
方法5：使用JOIN更新
-- 先确保部门存在
INSERT IGNORE INTO departments (dept_name) VALUES ('未分配部门');
-- 使用JOIN更新
UPDATE employees e
CROSS JOIN (
SELECT dept_id FROM departments WHERE dept_name = '未分配部门' LIMIT 1
) d
SET e.dept_id = d.dept_id
WHERE e.dept_id IS NULL;
方法6：同时处理其他默认值
-- 确保未分配部门存在
INSERT IGNORE INTO departments (dept_name, location, budget)
VALUES ('未分配部门', '总部', 0);
-- 更新员工，同时设置其他默认值
UPDATE employees
SET
dept_id = (SELECT dept_id FROM departments WHERE dept_name = '未分配部门'),
updated_at = NOW(),
status = 'pending_assignment'
WHERE dept_id IS NULL;
要点说明：
- NULL判断：必须使用
IS NULL或IS NOT NULL，不能用= NULL - 先确保存在：更新前先确保'未分配部门'存在
- 事务保护：在事务中执行，保证数据一致性
- 避免硬编码：使用子查询获取dept_id而不是硬编码数字
NULL值判断的正确方式：
-- ❌ 错误：不能用等号判断NULL
WHERE dept_id = NULL -- 永远返回FALSE
WHERE dept_id != NULL -- 永远返回FALSE
-- ✅ 正确：使用IS NULL
WHERE dept_id IS NULL
WHERE dept_id IS NOT NULL
-- 也可以使用IFNULL或COALESCE
WHERE IFNULL(dept_id, 0) = 0
WHERE COALESCE(dept_id, 0) = 0
完整的安全更新流程：
-- 1. 查看当前状况
SELECT
COUNT(*) AS total_employees,
SUM(CASE WHEN dept_id IS NULL THEN 1 ELSE 0 END) AS no_dept_count,
SUM(CASE WHEN dept_id IS NOT NULL THEN 1 ELSE 0 END) AS has_dept_count
FROM employees;
-- 2. 查看没有部门的员工列表
SELECT emp_id, name, dept_id FROM employees WHERE dept_id IS NULL;
-- 3. 开启事务
START TRANSACTION;
-- 4. 确保'未分配部门'存在
INSERT INTO departments (dept_name, location, budget)
SELECT '未分配部门', '总部', 0
WHERE NOT EXISTS (
SELECT 1 FROM departments WHERE dept_name = '未分配部门'
);
-- 5. 更新员工
UPDATE employees
SET dept_id = (
SELECT dept_id FROM departments WHERE dept_name = '未分配部门' LIMIT 1
)
WHERE dept_id IS NULL;
-- 6. 验证结果
SELECT COUNT(*) AS still_null FROM employees WHERE dept_id IS NULL;
-- 应该返回0
SELECT * FROM employees
WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = '未分配部门');
-- 7. 确认无误后提交
COMMIT;
处理部门ID不存在于部门表的情况：
-- 更全面的更新：包括NULL和无效的dept_id
UPDATE employees e
SET e.dept_id = (
SELECT dept_id FROM departments WHERE dept_name = '未分配部门' LIMIT 1
)
WHERE e.dept_id IS NULL
OR e.dept_id NOT IN (SELECT dept_id FROM departments);
添加约束防止NULL（更新后）：
-- 更新完成后，可以添加NOT NULL约束
ALTER TABLE employees
MODIFY dept_id INT NOT NULL;
-- 或添加外键约束
ALTER TABLE employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);
题目64：使用事务完成：插入新员工，同时更新部门人数
💡 解题思路
这道题考查事务的使用。需要：
- 理解事务的ACID特性
- 将多个操作放在一个事务中
- 正确使用BEGIN/START TRANSACTION、COMMIT、ROLLBACK
🔍 关键提示
START TRANSACTION或BEGIN开启事务COMMIT提交事务ROLLBACK回滚事务- 事务中的操作要么全部成功，要么全部失败
📝 实现步骤
- 开启事务
- 插入新员工记录
- 更新对应部门的人数
- 验证结果
- 提交或回滚
✅ 验证方法
-- 检查员工是否插入成功
SELECT * FROM employees WHERE name = '新员工姓名';
-- 检查部门人数是否更新
SELECT dept_id, emp_count FROM departments WHERE dept_id = X;
🚀 扩展思考
- 事务的隔离级别有哪些？
- 什么情况下事务会自动回滚？
- 如何处理事务中的错误？
💯 点击查看参考答案
方法1：基本事务（推荐）
-- 第1步：开启事务
START TRANSACTION;
-- 第2步：插入新员工
INSERT INTO employees (name, department, salary, hire_date, dept_id)
VALUES ('张新', '技术部', 8000.00, CURDATE(), 1);
-- 获取新插入的员工ID（可选）
SET @new_emp_id = LAST_INSERT_ID();
-- 第3步：更新部门人数
UPDATE departments
SET emp_count = emp_count + 1
WHERE dept_id = 1;
-- 第4步：验证结果
SELECT * FROM employees WHERE emp_id = @new_emp_id;
SELECT dept_id, dept_name, emp_count FROM departments WHERE dept_id = 1;
-- 第5步：如果一切正常，提交事务
COMMIT;
-- 如果发现问题，可以执行
-- ROLLBACK;
方法2：使用savepoint（保存点）
START TRANSACTION;
-- 插入员工
INSERT INTO employees (name, dept_id, salary, hire_date)
VALUES ('李新', 2, 7500.00, CURDATE());
SAVEPOINT after_insert;
-- 更新部门人数
UPDATE departments
SET emp_count = emp_count + 1
WHERE dept_id = 2;
-- 如果这步出错，可以回滚到savepoint
-- ROLLBACK TO after_insert;
-- 全部成功则提交
COMMIT;
方法3：使用存储过程封装
DELIMITER //
CREATE PROCEDURE add_employee_and_update_dept(
IN p_name VARCHAR(50),
IN p_dept_id INT,
IN p_salary DECIMAL(10,2),
IN p_hire_date DATE
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Error occurred, transaction rolled back' AS result;
END;
START TRANSACTION;
-- 插入员工
INSERT INTO employees (name, dept_id, salary, hire_date)
VALUES (p_name, p_dept_id, p_salary, p_hire_date);
-- 更新部门人数
UPDATE departments
SET emp_count = emp_count + 1
WHERE dept_id = p_dept_id;
COMMIT;
SELECT 'Transaction committed successfully' AS result;
END //
DELIMITER ;
-- 调用存储过程
CALL add_employee_and_update_dept('王新', 3, 9000.00, '2024-01-15');
方法4：完整的错误处理
START TRANSACTION;
-- 检查部门是否存在
SELECT dept_id INTO @dept_exists
FROM departments
WHERE dept_id = 1;
IF @dept_exists IS NOT NULL THEN
-- 插入员工
INSERT INTO employees (name, dept_id, salary, hire_date)
VALUES ('赵新', 1, 8500.00, CURDATE());
-- 更新部门人数
UPDATE departments
SET emp_count = emp_count + 1
WHERE dept_id = 1;
-- 记录操作日志
INSERT INTO operation_log (operation, table_name, record_id, created_at)
VALUES ('INSERT', 'employees', LAST_INSERT_ID(), NOW());
COMMIT;
ELSE
-- 部门不存在，回滚
ROLLBACK;
SELECT 'Department not found, transaction rolled back' AS error;
END IF;
方法5：自动计算部门人数（触发器方式）
-- 创建触发器（更优雅的方案）
DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
UPDATE departments
SET emp_count = emp_count + 1
WHERE dept_id = NEW.dept_id;
END //
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
UPDATE departments
SET emp_count = emp_count - 1
WHERE dept_id = OLD.dept_id;
END //
DELIMITER ;
-- 之后只需要插入员工即可，部门人数自动更新
START TRANSACTION;
INSERT INTO employees (name, dept_id, salary, hire_date)
VALUES ('孙新', 1, 7000.00, CURDATE());
COMMIT;
要点说明：
- 原子性：事务中的所有操作要么全部成功，要么全部失败
- 一致性：事务执行前后数据保持一致状态
- 隔离性：并发事务互不干扰
- 持久性：提交后的修改永久保存
事务的使用场景：
-- 场景1：转账操作
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
-- 场景2：订单创建
START TRANSACTION;
INSERT INTO orders (user_id, total_amount) VALUES (1, 500);
SET @order_id = LAST_INSERT_ID();
INSERT INTO order_items (order_id, product_id, quantity) VALUES (@order_id, 10, 2);
UPDATE products SET stock = stock - 2 WHERE product_id = 10;
COMMIT;
-- 场景3：批量操作
START TRANSACTION;
DELETE FROM temp_data WHERE processed = 1;
INSERT INTO main_data SELECT * FROM temp_data;
UPDATE temp_data SET processed = 1;
COMMIT;
事务隔离级别：
-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 读已提交
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 可重复读（MySQL默认）
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 串行化
完整的事务最佳实践：
START TRANSACTION;
-- 1. 验证前置条件
SELECT COUNT(*) INTO @dept_exists
FROM departments
WHERE dept_id = 1;
IF @dept_exists = 0 THEN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Department does not exist';
END IF;
-- 2. 执行业务逻辑
INSERT INTO employees (name, dept_id, salary, hire_date)
VALUES ('新员工', 1, 8000.00, CURDATE());
SET @new_emp_id = LAST_INSERT_ID();
UPDATE departments
SET emp_count = emp_count + 1,
updated_at = NOW()
WHERE dept_id = 1;
-- 3. 验证结果
SELECT emp_count INTO @new_count
FROM departments
WHERE dept_id = 1;
-- 4. 记录审计日志
INSERT INTO audit_log (operation, user_id, details, created_at)
VALUES ('ADD_EMPLOYEE', 1, CONCAT('Added employee ID: ', @new_emp_id), NOW());
-- 5. 提交
COMMIT;
-- 返回结果
SELECT
@new_emp_id AS new_employee_id,
@new_count AS department_employee_count;
错误处理示例：
DELIMITER //
CREATE PROCEDURE safe_add_employee(
IN p_name VARCHAR(50),
IN p_dept_id INT,
IN p_salary DECIMAL(10,2)
)
BEGIN
DECLARE v_error INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_error = 1;
START TRANSACTION;
INSERT INTO employees (name, dept_id, salary, hire_date)
VALUES (p_name, p_dept_id, p_salary, CURDATE());
UPDATE departments
SET emp_count = emp_count + 1
WHERE dept_id = p_dept_id;
IF v_error = 1 THEN
ROLLBACK;
SELECT 'Error: Transaction rolled back' AS result;
ELSE
COMMIT;
SELECT 'Success: Employee added and department updated' AS result;
END IF;
END //
DELIMITER ;
题目65：删除没有任何员工的部门
💡 解题思路
这道题考查关联删除和子查询的使用。需要：
- 找出没有员工的部门
- 使用子查询或JOIN实现
- 安全地删除这些部门
🔍 关键提示
- 使用NOT EXISTS或NOT IN子查询
- 或使用LEFT JOIN找出没有匹配的部门
- 删除前务必验证
📝 实现步骤
- 查询没有员工的部门列表
- 验证查询结果是否正确
- 执行删除操作
- 验证删除结果
✅ 验证方法
-- 删除前查看
SELECT d.* FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;
-- 删除后验证
-- 再次执行上面的查询，应该返回空
🚀 扩展思考
- 如何同时删除部门的其他关联数据？
- 如何实现软删除？
- 大量数据时如何优化性能？
💯 点击查看参考答案
方法1：使用NOT EXISTS（推荐）
-- 第1步：查看要删除的部门
SELECT *
FROM departments d
WHERE NOT EXISTS (
SELECT 1
FROM employees e
WHERE e.dept_id = d.dept_id
);
-- 第2步：执行删除
DELETE FROM departments
WHERE NOT EXISTS (
SELECT 1
FROM employees e
WHERE e.dept_id = departments.dept_id
);
方法2：使用NOT IN子查询
-- 查看要删除的部门
SELECT * FROM departments
WHERE dept_id NOT IN (
SELECT DISTINCT dept_id
FROM employees
WHERE dept_id IS NOT NULL
);
-- 执行删除
DELETE FROM departments
WHERE dept_id NOT IN (
SELECT DISTINCT dept_id
FROM employees
WHERE dept_id IS NOT NULL
);
方法3：使用LEFT JOIN
-- 查看要删除的部门
SELECT d.*
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;
-- 执行删除（需要使用子查询）
DELETE FROM departments
WHERE dept_id IN (
SELECT dept_id FROM (
SELECT d.dept_id
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL
) AS temp
);
方法4：使用聚合查询
-- 统计每个部门的员工数，删除员工数为0的
DELETE FROM departments
WHERE dept_id IN (
SELECT dept_id FROM (
SELECT d.dept_id
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id
HAVING COUNT(e.emp_id) = 0
) AS temp
);
方法5：在事务中安全删除
START TRANSACTION;
-- 查看要删除的部门
SELECT
d.dept_id,
d.dept_name,
COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING COUNT(e.emp_id) = 0;
-- 执行删除
DELETE FROM departments
WHERE dept_id IN (
SELECT dept_id FROM (
SELECT d.dept_id
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id
HAVING COUNT(e.emp_id) = 0
) AS temp
);
-- 验证
SELECT COUNT(*) AS deleted_count FROM departments
WHERE NOT EXISTS (
SELECT 1 FROM employees WHERE dept_id = departments.dept_id
);
-- 应该返回0
COMMIT;
方法6：同时删除相关数据
START TRANSACTION;
-- 删除没有员工的部门的其他关联数据
DELETE FROM dept_budgets
WHERE dept_id IN (
SELECT dept_id FROM (
SELECT d.dept_id
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL
) AS temp
);
-- 删除部门本身
DELETE FROM departments
WHERE NOT EXISTS (
SELECT 1 FROM employees WHERE dept_id = departments.dept_id
);
COMMIT;
要点说明：
- EXISTS vs IN：EXISTS性能通常更好，找到第一条匹配即停止
- NULL处理：使用NOT IN时要注意NULL值的影响
- 子查询嵌套：MySQL不允许直接在FROM中引用被修改的表，需要再嵌套一层
- 验证重要：删除前务必验证查询结果
NOT EXISTS vs NOT IN 性能对比：
-- ✅ 推荐：NOT EXISTS（性能更好）
DELETE FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);
-- ⚠️ 注意：NOT IN（要处理NULL）
DELETE FROM departments
WHERE dept_id NOT IN (
SELECT dept_id FROM employees WHERE dept_id IS NOT NULL
);
-- 如果子查询中有NULL，NOT IN可能返回意外结果
NOT IN 的 NULL 陷阱：
-- 假设employees表中有dept_id为NULL的记录
SELECT dept_id FROM employees;
-- 结果：1, 2, NULL
-- 这个查询可能不会删除任何记录！
DELETE FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees);
-- 原因：dept_id NOT IN (1, 2, NULL) 等价于
-- dept_id != 1 AND dept_id != 2 AND dept_id != NULL
-- 而 dept_id != NULL 永远返回 NULL（不是TRUE也不是FALSE）
-- ✅ 正确做法：过滤NULL
DELETE FROM departments
WHERE dept_id NOT IN (
SELECT dept_id FROM employees WHERE dept_id IS NOT NULL
);
完整的安全删除流程：
-- 1. 备份要删除的部门
CREATE TABLE departments_to_delete AS
SELECT d.*
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;
-- 2. 查看要删除的部门列表
SELECT * FROM departments_to_delete;
-- 3. 统计删除前后的数量
SELECT
(SELECT COUNT(*) FROM departments) AS before_count,
(SELECT COUNT(*) FROM departments_to_delete) AS will_delete,
(SELECT COUNT(*) FROM departments) - (SELECT COUNT(*) FROM departments_to_delete) AS after_count;
-- 4. 在事务中执行删除
START TRANSACTION;
DELETE FROM departments
WHERE NOT EXISTS (
SELECT 1 FROM employees WHERE dept_id = departments.dept_id
);
-- 5. 验证结果
SELECT ROW_COUNT() AS deleted_count;
-- 6. 再次检查
SELECT * FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;
-- 应该返回空
-- 7. 确认无误后提交
COMMIT;
软删除实现：
-- 不真正删除，只标记
UPDATE departments d
SET
is_active = 0,
deleted_at = NOW()
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);
-- 查询时过滤掉已删除的部门
SELECT * FROM departments WHERE is_active = 1;
级联删除（如果有外键）：
-- 如果设置了外键的ON DELETE CASCADE
-- 删除部门会自动删除相关的员工（危险！）
-- 更安全的做法：先检查是否有员工，有则不删除
DELETE FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
)
AND NOT EXISTS (
SELECT 1 FROM dept_projects p WHERE p.dept_id = d.dept_id
)
AND NOT EXISTS (
SELECT 1 FROM dept_budgets b WHERE b.dept_id = d.dept_id
);
第七关学习总结
恭喜完成第七关的学习！通过这10道题目，你已经掌握了：
核心知识点回顾
- INSERT - 单条插入、批量插入、性能优化
- UPDATE - 单表更新、关联更新、条件更新
- DELETE - 条件删除、关联删除、去重删除
- 事务 - ACID特性、BEGIN/COMMIT/ROLLBACK、错误处理
- 数据安全 - 验证先行、事务保护、备份恢复
最佳实践要点
- ✅ UPDATE/DELETE前必须先用SELECT验证WHERE条件
- ✅ 重要操作使用事务保护，便于回滚
- ✅ 批量INSERT性能远优于单条INSERT
- ✅ 处理NULL值要用IS NULL，不能用= NULL
- ✅ 大数据量操作要分批进行，避免锁表
后续关卡预告
接下来的关卡将学习：
- 第八关：表设计与索引 - 约束、索引设计、ALTER TABLE
- 第九关：性能优化 - EXPLAIN、慢查询优化、索引优化
- 第十关：实战综合 - 综合项目实战
第八关：表设计与索引 - 学习指南
题目66：创建一个订单表，包含订单号、客户ID、订单日期、总金额
💡 解题思路
这题考查表结构设计 + CREATE TABLE 语法。要点：
- 订单号一般用自增主键或业务单号，这里使用自增整型主键；
- 客户ID通常是与用户表/客户表关联的外键（本题可先不强制外键，只保留字段）；
- 订单日期用
DATE或DATETIME，视业务需要； - 总金额用
DECIMAL(10,2)等定点数表达金额。
🔍 关键提示
- 使用
CREATE TABLE orders (...)； order_id INT PRIMARY KEY AUTO_INCREMENT；customer_id使用INT或BIGINT；order_date可使用DATETIME；total_amount DECIMAL(10,2) NOT NULL。
📝 实现步骤
- 写出建表框架：
CREATE TABLE orders (...); - 添加
order_id字段：整型、自增、主键； - 添加
customer_id字段：整型，可以允许为空或不为空视需求； - 添加
order_date字段：DATETIME或TIMESTAMP； - 添加
total_amount字段：DECIMAL(10,2)并设置非空； - 选择合适的存储引擎和字符集（如 InnoDB + utf8mb4）。
✅ 验证方法
DESC orders;
SHOW CREATE TABLE orders\G
检查：
- 是否有4个字段；
- 主键、自增、类型是否符合预期。
🚀 扩展思考
- 如果系统中已经有
customers表，应该如何增加外键约束？ - 订单状态（待支付/已支付/已发货等）应该放在本表还是单独拆表？
- 订单号是否需要使用有业务含义的字符串（如
20250101-0001），如何与主键关系设计？
💯 点击查看参考答案
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATETIME NOT NULL,
total_amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
要点说明：
- 使用自增
order_id作为主键，避免用有业务含义的字段做主键； DECIMAL(10,2)适合金额计算，避免浮点误差；- 实际项目中通常还会增加
status、created_at、updated_at等字段。
题目67：为员工表的 name 字段添加索引
💡 解题思路
这题考查添加单列索引。要点：
- 确认表名为
employees，字段为name； - 选择索引创建方式：
CREATE INDEX或ALTER TABLE ... ADD INDEX； - 注意索引命名规范（建议加表名前缀，如
idx_employees_name）。
🔍 关键提示
- 语法1：
CREATE INDEX 索引名 ON 表名(字段); - 语法2：
ALTER TABLE 表名 ADD INDEX 索引名 (字段); - 索引名建议：
idx_表名_字段名。
📝 实现步骤
- 写出索引名：
idx_employees_name； - 选择
CREATE INDEX方式； - 在
employees(name)上创建索引； - 使用
SHOW INDEX验证。
✅ 验证方法
SHOW INDEX FROM employees;
确认：
- 存在
idx_employees_name索引； Column_name为name。
🚀 扩展思考
- 对
name建索引是否总是合适？如果表很小或模糊查询很多，会怎样？ - 什么时候应改用前缀索引（如
name(10)）？ - 当同时在
name和email上建索引时，有没有必要建复合索引？
💯 点击查看参考答案
-- 推荐写法：CREATE INDEX
CREATE INDEX idx_employees_name ON employees(name);
-- 等价写法：ALTER TABLE
ALTER TABLE employees ADD INDEX idx_employees_name (name);
要点说明：
- 两种写法效果相同，推荐
CREATE INDEX更直观； - 命名规范可以提升可读性和维护性。
题目68：为员工表添加一个'手机号'字段，要求唯一且非空
💡 解题思路
这题考查 ALTER TABLE 增加字段 + 约束：
- 使用
ALTER TABLE ... ADD COLUMN增加phone字段； NOT NULL保证非空；UNIQUE保证唯一性。
🔍 关键提示
ALTER TABLE employees ADD COLUMN phone VARCHAR(20) NOT NULL UNIQUE;- 可以使用
AFTER 某字段指定插入位置（可选）； - 唯一约束可以在字段级或表级声明。
📝 实现步骤
- 选择合适的数据类型：
VARCHAR(20)； - 决定字段名：
phone； - 使用
ADD COLUMN增加字段，并加上NOT NULL UNIQUE； - 可选：指定放在
email字段后； - 使用
DESC或SHOW CREATE TABLE验证。
✅ 验证方法
DESC employees;
SHOW CREATE TABLE employees\G
再尝试插入两条相同手机号的记录，第二条应报错。
🚀 扩展思考
- 如果线上已有重复手机号的数据，直接加 UNIQUE 会发生什么？
- 如何先清洗重复数据，再添加唯一约束？
- 如果一个员工允许有多个手机号，应如何设计表结构？
💯 点击查看参考答案
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20) NOT NULL UNIQUE
AFTER email;
要点说明：
NOT NULL UNIQUE即可实现“非空且唯一”；AFTER email只影响展示顺序，不影响逻辑。
题目69：将员工表的 salary 字段类型改为 DECIMAL(12,2)
💡 解题思路
这题考查 修改字段类型：
- 使用
ALTER TABLE ... MODIFY； - 保持字段名不变，只修改类型；
- 注意保留原有约束（如
NOT NULL、DEFAULT等）。
🔍 关键提示
ALTER TABLE 表名 MODIFY 字段名 新数据类型 [约束];- 若原字段有
NOT NULL/DEFAULT，需要在 MODFIY 中一并写出。
📝 实现步骤
- 查看当前
salary字段定义（方便保留约束）； - 写出
ALTER TABLE employees MODIFY salary DECIMAL(12,2); - 如有默认值或非空约束，补上相应子句；
- 使用
DESC验证。
✅ 验证方法
DESC employees;
确认 salary 类型为 decimal(12,2)。
🚀 扩展思考
- 金额字段为什么推荐用
DECIMAL而不是FLOAT/DOUBLE？ - 如果已有数据，类型变宽（位数变大）会有什么影响？
- 如果从较大精度改为较小精度，可能造成哪些风险？
💯 点击查看参考答案
ALTER TABLE employees
MODIFY salary DECIMAL(12,2);
如果原字段是非空且默认0，可以写为：
ALTER TABLE employees
MODIFY salary DECIMAL(12,2) NOT NULL DEFAULT 0;
题目70：创建一个复合索引 (dept_id, salary)
💡 解题思路
考查复合索引 + 最左前缀原则：
- 目标表为
employees，字段为dept_id, salary； - 使用
CREATE INDEX或ALTER TABLE ... ADD INDEX； - 理解该索引适用于哪些查询模式。
🔍 关键提示
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);- 该索引可以支持：
WHERE dept_id = ? AND salary > ?WHERE dept_id = ?
- 不适用于
WHERE salary > ?单独条件（因为违反最左前缀）。
📝 实现步骤
- 为
employees创建索引idx_dept_salary； - 使用
SHOW INDEX验证； - 使用
EXPLAIN对比创建前后的执行计划。
✅ 验证方法
SHOW INDEX FROM employees;
EXPLAIN SELECT * FROM employees
WHERE dept_id = 1 AND salary > 8000;
🚀 扩展思考
- 如果查询条件经常是
salary > ?而不带dept_id，这个索引是否合适？ - 多列索引与两个单列索引（
dept_id/salary）有什么区别？ - 如果经常按
dept_id, salary排序，这个索引有什么优势？
💯 点击查看参考答案
CREATE INDEX idx_dept_salary
ON employees(dept_id, salary);
要点说明：
- 复合索引的字段顺序很重要，一般将区分度更高、过滤更频繁的字段放前面；
- 在
WHERE dept_id = ? AND salary > ?场景下效果最好。
题目71：查看 employees 表的所有索引
💡 解题思路
此题考查查看索引信息：
- 使用
SHOW INDEX FROM 表名； - 理解关键字段含义：
Key_name,Column_name,Non_unique等。
🔍 关键提示
SHOW INDEX FROM employees;- 也可用
SHOW INDEXES或SHOW KEYS。
📝 实现步骤
- 直接执行
SHOW INDEX FROM employees; - 观察输出中的索引名和对应字段；
- 判断哪些是主键、哪些是普通索引或唯一索引。
✅ 验证方法
查看结果中：
Key_name = PRIMARY为主键索引；Non_unique = 0表示唯一索引；- 是否包含刚刚创建的
idx_employees_name、idx_dept_salary等。
🚀 扩展思考
- 如何只查看某个索引的定义？
- 如何判断一个索引是否重复/冗余（例如覆盖关系）？
💯 点击查看参考答案
SHOW INDEX FROM employees;
-- 或
SHOW INDEXES FROM employees;
-- 或
SHOW KEYS FROM employees;
输出字段说明（部分）：
Key_name：索引名；Column_name：使用该索引的列；Non_unique：0 表示唯一索引，1 表示非唯一索引。
题目72：删除 employees 表中的 name 索引
💡 解题思路
考查删除索引：
- 先确认索引名称（如
idx_employees_name）； - 使用
DROP INDEX或ALTER TABLE ... DROP INDEX； - 注意不要误删主键索引。
🔍 关键提示
DROP INDEX 索引名 ON 表名;- 或
ALTER TABLE 表名 DROP INDEX 索引名; - 若最初索引名就是
name，则按实际名称删除。
📝 实现步骤
- 通过
SHOW INDEX找到 name 字段的索引名； - 使用
DROP INDEX语句删除； - 再次
SHOW INDEX验证是否已删除。
✅ 验证方法
SHOW INDEX FROM employees;
确认 Key_name = idx_employees_name（或真实索引名）已经不存在。
🚀 扩展思考
- 删除索引会对正在执行的查询产生什么影响？
- 在高并发生产环境中删除大型索引是否会引起阻塞？
💯 点击查看参考答案
假设索引名为 idx_employees_name：
DROP INDEX idx_employees_name ON employees;
-- 或
ALTER TABLE employees DROP INDEX idx_employees_name;
要点说明：
DROP INDEX与ALTER TABLE ... DROP INDEX等价；- 删除索引不会删除数据，但会影响相关查询的性能。
题目73：创建一个商品表，包含自动递增的ID和默认值
💡 解题思路
考查建表 + 默认值：
- 设计商品表字段：
id、name、price、stock等； id自增主键；- 如
stock默认 0，created_at默认当前时间。
🔍 关键提示
- 使用
AUTO_INCREMENT+PRIMARY KEY； - 使用
DEFAULT指定默认值； - 一般会加上
created_at/updated_at字段。
📝 实现步骤
- 确定表名：
products； - 设计字段：
id INT PRIMARY KEY AUTO_INCREMENTname VARCHAR(100) NOT NULLprice DECIMAL(10,2) NOT NULL DEFAULT 0stock INT NOT NULL DEFAULT 0created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- 写
CREATE TABLE语句并执行； DESC验证。
✅ 验证方法
DESC products;
SHOW CREATE TABLE products\G
🚀 扩展思考
- 如果商品有上下架状态，应该如何设计字段及默认值？
- 如果希望在更新时自动刷新
updated_at，应如何定义？
💯 点击查看参考答案
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL DEFAULT 0,
stock INT NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
要点说明：
- 默认值可以减少插入时需要指定的字段；
- 对于库存类字段，
DEFAULT 0是常见做法。
题目74：为订单表和订单详情表建立外键关系
💡 解题思路
考查 外键约束 + ALTER TABLE：
- 假设已有
orders(order_id)和order_items(order_id, product_id, ...)； - 在
order_items上建立外键指向orders.order_id； - 可选择
ON DELETE CASCADE或ON DELETE RESTRICT等策略。
🔍 关键提示
- 外键通常建在“子表”上（这里是
order_items）； - 语法：
ALTER TABLE 子表 ADD CONSTRAINT 名 FOREIGN KEY(字段) REFERENCES 父表(字段) [ON DELETE ...]; - 要求父表字段有索引（主键或唯一）。
📝 实现步骤
- 确认
orders.order_id为主键； - 在
order_items上添加外键：FOREIGN KEY (order_id) REFERENCES orders(order_id)；
- 视需求决定是否加
ON DELETE CASCADE； - 使用
SHOW CREATE TABLE order_items验证。
✅ 验证方法
SHOW CREATE TABLE order_items\G
并尝试：
- 插入一个不存在的
order_id，应报错； - 删除
orders中某条记录，观察order_items的行为（根据 ON DELETE 策略）。
🚀 扩展思考
- 实战中是否总是建议使用外键？为什么很多大型系统选择“逻辑外键”而不用数据库外键？
- 订单与用户、订单与支付记录等关系，外键如何设计？
💯 点击查看参考答案
ALTER TABLE order_items
ADD CONSTRAINT fk_order_items_order
FOREIGN KEY (order_id) REFERENCES orders(order_id)
ON DELETE CASCADE;
要点说明：
ON DELETE CASCADE：删除订单时自动删除对应详情记录（要慎用）；- 也可以使用
ON DELETE RESTRICT（默认）或ON DELETE SET NULL。
题目75：修改部门表，添加部门描述字段，默认值为'暂无描述'
💡 解题思路
考查 ALTER TABLE 添加字段 + 默认值：
- 在
departments表中增加description字段； - 类型可选
VARCHAR(255)； - 设置
DEFAULT '暂无描述'。
🔍 关键提示
ALTER TABLE departments ADD COLUMN description VARCHAR(255) NOT NULL DEFAULT '暂无描述';- 可用
AFTER控制字段位置。
📝 实现步骤
- 确定字段名、类型和默认值；
- 写出
ALTER TABLE语句； - 使用
DESC或SHOW CREATE TABLE验证。
✅ 验证方法
DESC departments;
插入一条未指定 description 的记录，确认其值为 '暂无描述'。
🚀 扩展思考
- 如果以后需要支持多语言描述（中/英），字段设计如何扩展？
- 部门描述是否应该拆到独立表（如多语言、多版本说明）？
💯 点击查看参考答案
ALTER TABLE departments
ADD COLUMN description VARCHAR(255) NOT NULL DEFAULT '暂无描述';
要点说明：
NOT NULL DEFAULT '暂无描述'保证字段始终有值；- 默认值文字要与产品/业务约定一致。
第八关学习总结
恭喜完成第八关的学习！通过这10道题目，你已经掌握了：
核心知识点回顾
- CREATE TABLE - 建表语法、字段类型选择、约束设置
- ALTER TABLE - 添加字段、修改字段、删除字段、添加约束
- 索引设计 - 普通索引、唯一索引、复合索引、索引命名规范
- 约束管理 - 主键、外键、唯一约束、非空约束、默认值
- 表结构优化 - 数据类型选择、索引设计原则、最左前缀原则
最佳实践要点
- ✅ 每个表必须有主键，推荐使用自增整型
- ✅ 金额字段使用DECIMAL，避免浮点数精度问题
- ✅ 为WHERE、ORDER BY、JOIN中频繁使用的字段建索引
- ✅ 复合索引遵循最左前缀原则，区分度高的字段放前面
- ✅ 索引不是越多越好，要平衡查询性能和写入性能
后续关卡预告
接下来的关卡将学习：
- 第九关：性能优化 - EXPLAIN、慢查询优化、索引优化
- 第十关：实战综合 - 综合项目实战
第九关：性能优化 - 学习指南
题目76：使用 EXPLAIN 分析一个复杂查询的执行计划
💡 解题思路
本题不要求写出业务 SQL，只要会用 EXPLAIN 包裹原查询并解读关键字段：
- 在原查询前加上
EXPLAIN； - 关注
type、key、rows、Extra列； - 根据输出判断是否存在全表扫描、临时表、文件排序等问题。
🔍 关键提示
- 语法：
EXPLAIN 原查询; - 常见关注点：
type是否为ALL（全表扫描）；key是否使用了期望的索引；rows预估扫描行数是否过大；Extra中是否有Using filesort、Using temporary。
📝 实现步骤
- 找到你要分析的复杂查询，例如：
SELECT e.name, d.dept_name, e.salary FROM employees e JOIN departments d ON e.dept_id = d.dept_id WHERE e.salary > 8000 ORDER BY e.salary DESC; - 在前面加上
EXPLAIN：EXPLAIN SELECT ...; - 观察结果，对照文档理解各列含义；
- 根据结果判断是否需要加索引或改写 SQL。
✅ 验证方法
执行：
EXPLAIN SELECT e.name, d.dept_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 8000
ORDER BY e.salary DESC;
检查：
- 是否使用了
dept_id或salary相关索引； type是否优于ALL。
🚀 扩展思考
- 尝试在
employees(dept_id, salary)上加索引，再次EXPLAIN，对比前后差异； - 思考：当
EXPLAIN显示Using filesort时，如何通过索引或 SQL 改写消除？
💯 点击查看参考答案
参考写法：
EXPLAIN
SELECT e.name, d.dept_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 8000
ORDER BY e.salary DESC;
解读示例（伪输出）：
type = ALL：说明是全表扫描，需要考虑加索引；key = NULL：没有使用索引；rows = 100000：预估扫描行数较大；Extra = Using filesort：ORDER BY 触发文件排序，可尝试建合适的索引优化。
题目77：优化查询——查找姓名以 '张' 开头的员工（考虑索引）
💡 解题思路
要点：
- 查询语句使用
LIKE '张%'，避免前置%； - 为
name字段创建合适索引（可为整个字段或前缀索引）； - 使用
EXPLAIN验证是否走索引。
🔍 关键提示
- SQL 形式：
SELECT * FROM employees WHERE name LIKE '张%'; - 为
name建索引：CREATE INDEX idx_name ON employees(name);
📝 实现步骤
- 建立索引（如尚未建立）：
CREATE INDEX idx_employees_name ON employees(name); - 执行查询：
SELECT * FROM employees WHERE name LIKE '张%'; - 使用
EXPLAIN查看是否使用idx_employees_name索引。
✅ 验证方法
EXPLAIN SELECT * FROM employees WHERE name LIKE '张%';
检查：
type是否为range；key是否为idx_employees_name；rows是否明显减少。
🚀 扩展思考
- 如果使用
LIKE '%张%'，索引会失效，如何替代？（如全文索引、搜索引擎） - 对中文名字是否有必要使用前缀索引（如
name(2)）？
💯 点击查看参考答案
-- 建索引
CREATE INDEX idx_employees_name ON employees(name);
-- 优化后的查询
SELECT *
FROM employees
WHERE name LIKE '张%';
要点说明：
- 以常量开头的
LIKE '张%'可以使用普通 B+Tree 索引； - 以
%开头的模糊查询无法利用该索引。
题目78：改写查询以提高性能（YEAR(hire_date) = 2020）
💡 解题思路
原查询：
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
问题：
- 在 WHERE 中对索引字段使用函数，会导致索引失效；
- 应改写为日期范围查询。
🔍 关键提示
- 使用：
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01' - 这样可以使用
hire_date上的索引。
📝 实现步骤
- 保留原有业务语义（筛选 2020 年的记录）；
- 将函数过滤改写为区间过滤；
- 若无索引，在
hire_date上创建索引； - 使用
EXPLAIN对比优化前后。
✅ 验证方法
EXPLAIN SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
EXPLAIN SELECT * FROM employees
WHERE hire_date >= '2020-01-01'
AND hire_date < '2021-01-01';
对比 type、key、rows 等字段。
🚀 扩展思考
- 对其他日期函数（如
DATE(hire_time)、MONTH(...)）也要避免在 WHERE 中使用； - 如果经常按年份统计，是否需要冗余一列
hire_year存储年份？
💯 点击查看参考答案
-- 不推荐的写法（函数导致索引失效）
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
-- 推荐的优化写法
SELECT *
FROM employees
WHERE hire_date >= '2020-01-01'
AND hire_date < '2021-01-01';
要点说明：
- WHERE 子句中，对索引列做函数/计算，往往会让优化器放弃索引；
- 改写为区间查询既保持语义又提升性能。
题目79：为查询 dept_id = 5 AND salary > 8000 创建合适的索引
💡 解题思路
目标查询：
SELECT * FROM employees
WHERE dept_id = 5 AND salary > 8000;
要点：
- 为
dept_id和salary建复合索引； - 遵循最左前缀原则，将过滤更严格的字段放前（通常
dept_id）。
🔍 关键提示
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);- 该索引能很好支持上述条件和按工资排序。
📝 实现步骤
- 创建复合索引：
CREATE INDEX idx_dept_salary ON employees(dept_id, salary); - 使用
EXPLAIN查看查询是否使用该索引； - 对比没有索引、只有单列索引与复合索引三种情况的执行计划。
✅ 验证方法
EXPLAIN SELECT * FROM employees
WHERE dept_id = 5 AND salary > 8000;
检查 key 是否为 idx_dept_salary，type 是否为 range。
🚀 扩展思考
- 如果经常只按
salary > 8000查询，这个索引是否合适？ - 若顺序反过来
(salary, dept_id)，对上述查询有何影响？
💯 点击查看参考答案
CREATE INDEX idx_dept_salary
ON employees(dept_id, salary);
然后执行：
EXPLAIN SELECT * FROM employees
WHERE dept_id = 5 AND salary > 8000;
要点说明：
- 针对组合条件，复合索引通常优于多个单列索引；
- 字段顺序根据实际查询模式决定。
题目80：分析并优化 salary * 1.1 > 10000 的慢查询
💡 解题思路
原查询：
SELECT * FROM employees
WHERE salary * 1.1 > 10000;
问题：
- 对索引字段
salary做运算，导致索引失效； - 可以把计算移到常量一侧。
🔍 关键提示
- 将条件改写为：
WHERE salary > 10000 / 1.1 - 确保
salary上有索引。
📝 实现步骤
- 观察当前执行计划（通常是
ALL全表扫描）； - 为
salary建索引（如尚未建）； - 将条件从
salary * 1.1 > 10000改写为salary > 10000 / 1.1； - 使用
EXPLAIN对比优化前后。
✅ 验证方法
EXPLAIN SELECT * FROM employees
WHERE salary * 1.1 > 10000;
EXPLAIN SELECT * FROM employees
WHERE salary > 10000 / 1.1;
对比 type、key、rows。
🚀 扩展思考
- 其他类似写法，如
age + 1 > 30、price * 0.8 < 100，也都应改写； - 在复杂业务逻辑中，是否可以把这些计算前移到应用层完成？
💯 点击查看参考答案
-- 原始写法（不推荐）
SELECT * FROM employees
WHERE salary * 1.1 > 10000;
-- 推荐写法
SELECT *
FROM employees
WHERE salary > 10000 / 1.1;
配合索引：
CREATE INDEX idx_salary ON employees(salary);
第九关学习总结
恭喜完成第九关的学习！通过这5道题目，你已经掌握了：
核心知识点回顾
- EXPLAIN执行计划 - type、key、rows、Extra字段解读，识别性能瓶颈
- 索引优化 - 最左前缀原则、覆盖索引、避免索引失效
- 查询优化 - 避免SELECT *、合理使用LIMIT、避免函数运算
- SQL改写技巧 - JOIN代替子查询、OR改为IN、函数移到右侧
- 性能分析 - 慢查询定位、执行计划分析、优化方案制定
最佳实践要点
- ✅ 养成使用EXPLAIN分析查询的习惯
- ✅ 避免在WHERE中对索引字段使用函数或运算
- ✅ 只查询需要的字段，避免SELECT *
- ✅ 合理使用LIMIT限制结果集大小
- ✅ 定期审查慢查询日志，持续优化
后续关卡预告
接下来的关卡将学习：
- 第十关：实战综合 - 综合项目实战，应用前面所有知识点
第十关：实战综合 - 学习指南
题目81：设计一个电商系统的数据库（用户、商品、订单、订单详情）
💡 解题思路
本题考查完整的表设计与关联关系：
- 识别核心实体：用户（users）、商品（products）、订单（orders）、订单详情（order_items）；
- 为每个实体设计字段及主键；
- 使用外键建立合理的表间关系；
- 考虑索引和常见查询场景。
🔍 关键提示
- 每个表使用自增主键（BIGINT/INT）；
- 订单与用户：
orders.user_id → users.user_id； - 订单详情与订单/商品：
order_items.order_id → orders.order_id，order_items.product_id → products.product_id； - 为常用查询字段建索引（如
orders.user_id、orders.order_time、products.category_id）。
📝 实现步骤
- 先在纸上画出实体及关系（ER 图）；
- 为每个表设计字段（参考主手册中的示例）；
- 编写
CREATE TABLE语句，注意主键、外键、索引； - 依赖顺序：先创建无外键依赖的表，再创建依赖它们的表。
✅ 验证方法
SHOW TABLES;
SHOW CREATE TABLE users\G
SHOW CREATE TABLE products\G
SHOW CREATE TABLE orders\G
SHOW CREATE TABLE order_items\G
检查：
- 主键/外键是否正确；
- 字段类型与业务含义是否合理。
🚀 扩展思考
- 增加更多业务表，如：购物车、评价、优惠券、地址等；
- 考虑分库分表、读写分离时，主键和路由键如何设计。
💯 点击查看参考答案
可参考主手册中的示例（简化版）：
CREATE TABLE users (
user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE products (
product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(200) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
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,
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;
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;
题目82：查询每个类别下销量最高的3个商品
💡 解题思路
考查分组 + 聚合 + 窗口函数/子查询：
- 计算每个商品在所属类别下的总销量（基于
order_items和products/categories）； - 在类别维度内按销量排序并取前 3 名；
- 可使用窗口函数
RANK()/ROW_NUMBER()或相关子查询。
🔍 关键提示
- 使用窗口函数（MySQL 8.0+）：
RANK() OVER (PARTITION BY category_id ORDER BY SUM(quantity) DESC) - 再在外层筛选
rank <= 3。
📝 实现步骤
- 连接表：
products p、order_items oi、categories c； GROUP BY按商品统计销量：SUM(oi.quantity)；- 在子查询中增加窗口函数计算名次；
- 外层过滤
rank <= 3。
✅ 验证方法
-- 先查看某个类别下所有商品销量和排序
SELECT category_name, product_name, total_sales, rank
FROM (...子查询...) t
WHERE category_name = '某类别';
确认每个类别最多 3 条记录。
🚀 扩展思考
- 如果数据库版本不支持窗口函数，可以如何改写（如使用相关子查询）？
- 如何改写为“销量前 10% 的商品”而不是固定前 3 个？
💯 点击查看参考答案
SELECT *
FROM (
SELECT
p.product_id,
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 rnk
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 rnk <= 3;
题目83：计算每个用户的累计消费金额，并按消费金额排名
💡 解题思路
考查聚合 + 排名：
- 按用户聚合订单金额：
SUM(total_amount)； - 可统计订单数等额外指标；
- 使用窗口函数
RANK()或DENSE_RANK()按总消费额排序。
🔍 关键提示
- 只统计已完成订单（如
status = 3）； - 使用：
RANK() OVER (ORDER BY SUM(total_amount) DESC)
📝 实现步骤
- 连接
users u与orders o； WHERE o.status = 3过滤已完成订单；GROUP BY u.user_id, u.username计算每个用户总消费；- 使用窗口函数加上排名列；
- 按总消费降序输出。
✅ 验证方法
SELECT username, total_spent, spending_rank
FROM (...你的查询...)
ORDER BY total_spent DESC;
确认排名与金额排序一致。
🚀 扩展思考
- 如何限制只显示前 100 名高消费用户？
- 如果要按“平均客单价”排名，SQL 应如何调整？
💯 点击查看参考答案
SELECT
u.user_id,
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;
题目84：查询连续 3 天都有订单的日期
💡 解题思路
考查日期处理 + 连续性判断：
- 先统计每天是否有订单（可从
orders.order_time提取日期）； - 通过自连接或窗口函数判断某天与前两天是否都有订单；
- 输出连续区间中的日期。
🔍 关键提示
- 先得到每天一条记录：
SELECT DATE(order_time) AS dt FROM orders GROUP BY DATE(order_time); - 使用自连接：
t1.dt = t2.dt + INTERVAL 1 DAY t2.dt = t3.dt + INTERVAL 1 DAY
📝 实现步骤
- 使用子查询
daily_orders得到所有有订单的日期； - 将该子查询别名为
t1, t2, t3三次自连接； - 使用日期加减 1/2 天的条件判断连续性；
- 选择合适的输出列（如输出这 3 天中最早的一天或中间一天）。
✅ 验证方法
-- 查看中间结果
SELECT DATE(order_time) AS dt, COUNT(*) cnt
FROM orders
GROUP BY DATE(order_time)
ORDER BY dt;
手动检查有无连续 3 天，然后对比 SQL 结果。
🚀 扩展思考
- 如何扩展为连续 N 天都有订单（如 7 天）？
- 使用窗口函数（如
LAG）能否写出更简洁的解法？
💯 点击查看参考答案
自连接解法示例：
WITH daily_orders AS (
SELECT DISTINCT DATE(order_time) AS dt
FROM orders
)
SELECT t1.dt AS day3, t2.dt AS day2, t3.dt AS day1
FROM daily_orders t1
JOIN daily_orders t2 ON t2.dt = t1.dt - INTERVAL 1 DAY
JOIN daily_orders t3 ON t3.dt = t1.dt - INTERVAL 2 DAY
ORDER BY t3.dt;
也可以只输出最早的一天 t3.dt，视需求而定。
题目85：推荐系统——购买过商品 A 的用户还购买过哪些商品
💡 解题思路
考查自关联 + 子查询：
- 找出“购买过商品 A 的所有订单”；
- 在这些订单中，统计除 A 以外被购买的其他商品及购买次数；
- 按购买次数降序输出推荐列表。
🔍 关键提示
- 使用子查询获取
order_id集合； - 主查询在
order_items上统计； - 排除商品 A 本身。
📝 实现步骤
- 用子查询：
SELECT order_id FROM order_items WHERE product_id = ?; - 外层在
order_items中筛选order_id落在上述集合内； - 用
COUNT(DISTINCT order_id)或SUM(quantity)统计关联度； - 排除
product_id = ?自身。
✅ 验证方法
手动选一个 product_id（如 100），执行 SQL：
SELECT p.product_name, purchase_count
FROM (...你的查询...) t
JOIN products p ON t.product_id = p.product_id;
查看结果是否合理。
🚀 扩展思考
- 如何限定推荐“同一类别下的商品”？
- 如何将购买次数转换为推荐分数（如 TF-IDF 之类思想）？
💯 点击查看参考答案
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 (
SELECT order_id
FROM order_items
WHERE product_id = 100 -- 商品A的ID
)
AND oi.product_id <> 100
GROUP BY p.product_id, p.product_name
ORDER BY purchase_count DESC
LIMIT 10;
题目86：统计每月的销售额环比增长率
💡 解题思路
考查按月聚合 + 窗口函数 LAG：
- 先统计每个月的销售总额；
- 使用
LAG获取上个月的销售额； - 计算
(本月 - 上月) / 上月作为环比增长率。
🔍 关键提示
- 按月聚合：
DATE_FORMAT(order_time, '%Y-%m') AS ym - 使用：
LAG(month_total) OVER (ORDER BY ym)
📝 实现步骤
- 子查询
monthly：按月GROUP BY统计SUM(total_amount)； - 在外层对
monthly使用窗口函数计算上月值； - 计算环比：
(this_month - last_month) / last_month； - 处理上个月为 0 或 NULL 的情况。
✅ 验证方法
SELECT * FROM (...你的查询...) ORDER BY ym;
手动核对某几个月的计算是否正确。
🚀 扩展思考
- 如何改写为“同比”（与去年同月相比）增长率？
- 数据量特别大时，是否需要预聚合表来加速统计？
💯 点击查看参考答案
WITH monthly AS (
SELECT
DATE_FORMAT(order_time, '%Y-%m') AS ym,
SUM(total_amount) AS month_total
FROM orders
WHERE status = 3 -- 已完成订单
GROUP BY DATE_FORMAT(order_time, '%Y-%m')
)
SELECT
ym,
month_total,
LAG(month_total) OVER (ORDER BY ym) AS last_month_total,
CASE
WHEN LAG(month_total) OVER (ORDER BY ym) IS NULL
OR LAG(month_total) OVER (ORDER BY ym) = 0
THEN NULL
ELSE (month_total - LAG(month_total) OVER (ORDER BY ym))
/ LAG(month_total) OVER (ORDER BY ym)
END AS mom_growth_rate
FROM monthly
ORDER BY ym;
题目87：查询购买过所有类别商品的用户
💡 解题思路
考查集合覆盖 + 分组计数：
- 先统计系统中类别总数
total_categories； - 对每个用户，统计其购买过的不同类别数；
- 过滤“其类别数 == 总类别数”的用户。
🔍 关键提示
- 类别总数：
SELECT COUNT(*) FROM categories; - 用户-类别统计：
SELECT user_id, COUNT(DISTINCT category_id) AS cat_cnt FROM ... GROUP BY user_id;
📝 实现步骤
- 使用子查询或 CTE 得到
total_categories； - 连接
orders、order_items、products、categories； - 按
user_id分组统计COUNT(DISTINCT category_id)； - 在 HAVING 子句中与总数比较。
✅ 验证方法
对结果用户随机抽查，手动验证其订单中是否覆盖所有类别。
🚀 扩展思考
- 如果只要求“购买过至少 N 个不同类别的用户”，HAVING 条件应如何调整？
- 类别树有父子关系时，可以按“一级类别”或“二级类别”进行统计。
💯 点击查看参考答案
WITH total_cat AS (
SELECT COUNT(*) AS total_categories FROM categories
),
user_cat AS (
SELECT
o.user_id,
COUNT(DISTINCT p.category_id) AS cat_cnt
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY o.user_id
)
SELECT u.user_id
FROM user_cat u
CROSS JOIN total_cat t
WHERE u.cat_cnt = t.total_categories;
题目88：实现分页查询（第 3 页，每页 20 条）
💡 解题思路
考查LIMIT 分页：
- 第 1 页从 0 开始：
LIMIT 0, 20； - 第 3 页的偏移量为
(3-1)*20 = 40； - 标准写法：
LIMIT offset, size。
🔍 关键提示
- 偏移量
offset = (page - 1) * pageSize； - 深度分页性能问题：offset 很大时会变慢。
📝 实现步骤
- 选择分页表（如
orders），按主键排序； - 写出分页 SQL：
SELECT * FROM orders ORDER BY order_id LIMIT 40, 20; - 可只查询必要字段而非
*。
✅ 验证方法
SELECT COUNT(*) FROM orders;
SELECT * FROM orders ORDER BY order_id LIMIT 40, 20;
检查：
- 第 3 页数据与前 2 页不重叠；
- 每页 20 条（除非数据总数不足）。
🚀 扩展思考
- 大数据量下如何优化分页（如基于“游标/上次最大 ID”的方式）？
- 如何写出通用分页 SQL（传入 page/pageSize 参数）？
💯 点击查看参考答案
-- 第3页，每页20条
SELECT *
FROM orders
ORDER BY order_id
LIMIT 40, 20;
延伸：基于ID的高性能分页
-- 假设上一页最后一条记录的ID为 last_id
SELECT *
FROM orders
WHERE order_id > :last_id
ORDER BY order_id
LIMIT 20;
题目89：查询用户的复购率（购买 2 次以上的用户比例）
💡 解题思路
考查分组统计 + 条件聚合：
- 按
user_id分组，统计每个用户的订单数； - 统计订单数 ≥2 的用户数量；
- 用“复购用户数 / 总用户数”得到比例。
🔍 关键提示
- 子查询统计每个用户订单数；
- 外层用
COUNT(DISTINCT ...)或SUM(CASE WHEN ...)计算比例。
📝 实现步骤
- 子查询
user_orders：SELECT user_id, COUNT(*) AS order_count FROM orders WHERE status = 3 GROUP BY user_id; - 外层统计：
- 复购用户数：
COUNT(CASE WHEN order_count >= 2 THEN 1 END)； - 总用户数：
COUNT(*)或COUNT(user_id)；
- 复购用户数：
- 计算百分比。
✅ 验证方法
SELECT * FROM user_orders ORDER BY order_count DESC;
手动核对几位用户的订单数和复购判断是否正确。
🚀 扩展思考
- 如何按月份统计“当月复购率”？
- 如何进一步细分不同用户群体（如新客/老客）的复购率？
💯 点击查看参考答案
WITH user_orders AS (
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE status = 3
GROUP BY user_id
)
SELECT
COUNT(CASE WHEN order_count >= 2 THEN 1 END) * 100.0 /
COUNT(*) AS repurchase_rate
FROM user_orders;
题目90：设计并实现一个简单的权限管理系统（用户-角色-权限）
💡 解题思路
考查多对多关系建模 + 关联查询：
- 用户（users）、角色（roles）、权限（permissions）是三个核心实体；
- 用户与角色是多对多，需要中间表
user_roles； - 角色与权限是多对多，需要中间表
role_permissions； - 通过多表关联查询某个用户拥有哪些权限。
🔍 关键提示
- 表结构示意：
users(user_id, username, ...)roles(role_id, role_name, ...)permissions(perm_id, perm_name, ...)user_roles(user_id, role_id)role_permissions(role_id, perm_id)
📝 实现步骤
- 设计 5 张表结构；
- 为中间表设置联合主键或唯一键，避免重复关系；
- 插入一些测试数据；
- 写查询：给定用户，查询其所有权限。
✅ 验证方法
-- 查询某个用户拥有的所有权限
SELECT DISTINCT p.perm_name
FROM users u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN roles r ON ur.role_id = r.role_id
JOIN role_permissions rp ON r.role_id = rp.role_id
JOIN permissions p ON rp.perm_id = p.perm_id
WHERE u.username = 'alice';
🚀 扩展思考
- 如何增加“菜单权限 / 接口权限 / 数据权限”等不同层级？
- 如何在应用层缓存权限信息以减少数据库压力？
💯 点击查看参考答案
建表示例：
CREATE TABLE roles (
role_id INT PRIMARY KEY AUTO_INCREMENT,
role_name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE permissions (
perm_id INT PRIMARY KEY AUTO_INCREMENT,
perm_name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE user_roles (
user_id BIGINT NOT NULL,
role_id INT NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (role_id) REFERENCES roles(role_id)
);
CREATE TABLE role_permissions (
role_id INT NOT NULL,
perm_id INT NOT NULL,
PRIMARY KEY (role_id, perm_id),
FOREIGN KEY (role_id) REFERENCES roles(role_id),
FOREIGN KEY (perm_id) REFERENCES permissions(perm_id)
);
查询某个用户所有权限：
SELECT DISTINCT p.perm_name
FROM users u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN roles r ON ur.role_id = r.role_id
JOIN role_permissions rp ON r.role_id = rp.role_id
JOIN permissions p ON rp.perm_id = p.perm_id
WHERE u.username = 'alice';
第十关学习总结
恭喜完成第十关的学习！通过这10道综合实战题目，你已经掌握了：
核心知识点回顾
- 数据库设计 - 从需求分析到ER图，再到表结构设计
- 复杂查询 - 窗口函数、子查询、多表关联的综合运用
- 业务分析 - 销量排名、用户消费分析、推荐系统、复购率统计
- 性能优化 - 分页优化、统计查询优化、索引设计
- 系统设计 - 权限管理、电商系统等完整业务场景
最佳实践要点
- ✅ 设计阶段先画ER图，明确表之间的关系
- ✅ 复杂查询分步实现，先实现功能再优化性能
- ✅ 合理使用窗口函数简化排名和累计计算
- ✅ 大数据量分页使用游标方式而非OFFSET
- ✅ 统计查询尽量一次完成，避免多次查询
学习成果
恭喜你完成了MySQL升级打怪手册的全部10关学习！
通过这90道题目的练习，你已经：
- ✅ 掌握了MySQL的基础语法和高级特性
- ✅ 理解了数据库设计和优化的核心原则
- ✅ 具备了解决实际业务问题的能力
- ✅ 建立了完整的SQL知识体系
后续学习建议
完成全部10关后，建议进一步学习：
-
MySQL高级特性
- 存储过程和函数
- 触发器
- 视图和物化视图
- 分区表
-
性能调优深入
- 慢查询日志分析
- Query Profile分析
- InnoDB存储引擎优化
- 读写分离和分库分表
-
运维与架构
- 主从复制
- 高可用方案（MHA、MGR等）
- 备份与恢复策略
- 监控和告警
-
实战项目
- 参与开源项目
- 设计完整的业务系统数据库
- 性能优化实战
- 故障排查和解决
完整学习进度
已完成详细指南
- ✅ 第一关：基础入门（题目1-5）
- ✅ 第二关：单表查询（题目6-15）
- ✅ 第三关：多表联结（题目16-25）
- ✅ 第四关：聚合函数与分组（题目26-35）
- ✅ 第五关：子查询（题目36-45）
- ✅ 第六关：高级查询技巧（题目46-55）
- ✅ 第七关：数据操作（题目56-65）
- ✅ 第八关：表设计与索引（题目66-75）
- ✅ 第九关：性能优化（题目76-80）
- ✅ 第十关：实战综合（题目81-90）
学习建议总结
引导式学习的优势
- 培养思考能力：不是背答案，而是学会分析问题
- 加深理解：通过步骤引导理解SQL执行逻辑
- 扩展视野：每题都提供多种解法和扩展思考
- 建立信心：逐步引导让学习更有成就感
学习方法建议
- 先思考，后查答案：看到题目先自己尝试30分钟
- 对比学习：你的答案和参考答案有什么不同？
- 举一反三：完成一题后，尝试变换条件再练习
- 记录笔记：把易错点和技巧记录下来
- 动手实践：每道题都要自己敲代码，不要只看
进阶学习路径
完成全部10关后，建议进一步学习：
-
MySQL高级特性
- 存储过程和函数
- 触发器
- 视图和物化视图
- 分区表
-
性能调优深入
- 慢查询日志分析
- Query Profile分析
- InnoDB存储引擎优化
- 读写分离和分库分表
-
运维与架构
- 主从复制
- 高可用方案（MHA、MGR等）
- 备份与恢复策略
- 监控和告警
-
实战项目
- 参与开源项目
- 设计完整的业务系统数据库
- 性能优化实战
- 故障排查和解决
🎉 恭喜你完成MySQL升级打怪手册的学习！
记住：学习数据库是一个持续的过程，要在实践中不断积累经验。
保持好奇心，遇到问题多思考、多实验、多总结。
祝你在MySQL的道路上越走越远！
附录：常用SQL速查
基础查询
-- 基本查询
SELECT 字段 FROM 表 WHERE 条件 ORDER BY 字段 LIMIT 数量;
-- 去重
SELECT DISTINCT 字段 FROM 表;
-- 模糊查询
SELECT * FROM 表 WHERE 字段 LIKE '%关键词%';
聚合与分组
-- 聚合函数
SELECT COUNT(*), SUM(字段), AVG(字段), MAX(字段), MIN(字段) FROM 表;
-- 分组统计
SELECT 字段, COUNT(*) FROM 表 GROUP BY 字段 HAVING COUNT(*) > 10;
多表联结
-- 内连接
SELECT * FROM A INNER JOIN B ON A.id = B.a_id;
-- 左连接
SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;
-- 自连接
SELECT e.name, m.name AS manager FROM emp e LEFT JOIN emp m ON e.mgr_id = m.id;
子查询
-- 标量子查询
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);
-- IN子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE location='北京');
-- EXISTS子查询
SELECT * FROM emp e WHERE EXISTS (SELECT 1 FROM orders o WHERE o.emp_id = e.id);
窗口函数
-- 排名
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM emp;
-- 分区排名
SELECT name, dept_id, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank FROM emp;
-- 累计和
SELECT name, SUM(salary) OVER (ORDER BY hire_date) AS cumulative FROM emp;
数据操作
-- 插入
INSERT INTO 表 (字段1, 字段2) VALUES (值1, 值2);
INSERT INTO 表 (字段1, 字段2) VALUES (值1, 值2), (值3, 值4);
-- 更新
UPDATE 表 SET 字段=值 WHERE 条件;
-- 删除
DELETE FROM 表 WHERE 条件;
-- 事务
START TRANSACTION;
-- SQL操作
COMMIT; -- 或 ROLLBACK;
表结构
-- 建表
CREATE TABLE 表名 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 修改表
ALTER TABLE 表 ADD COLUMN 字段 类型;
ALTER TABLE 表 MODIFY COLUMN 字段 新类型;
ALTER TABLE 表 DROP COLUMN 字段;
-- 索引
CREATE INDEX idx_name ON 表(字段);
CREATE INDEX idx_multi ON 表(字段1, 字段2);
DROP INDEX idx_name ON 表;
常用函数
-- 字符串函数
CONCAT(str1, str2), SUBSTRING(str, pos, len), LENGTH(str), UPPER(str), LOWER(str)
-- 日期函数
NOW(), CURDATE(), DATE_ADD(date, INTERVAL 1 DAY), DATEDIFF(date1, date2)
YEAR(date), MONTH(date), DAY(date), DATE_FORMAT(date, '%Y-%m-%d')
-- 数值函数
ROUND(num, 2), CEIL(num), FLOOR(num), ABS(num)
-- 条件函数
IF(condition, true_value, false_value)
IFNULL(value, default_value)
CASE WHEN condition THEN value ELSE default END
Happy Learning! 🚀
浙公网安备 33010602011771号