一、核心考点清单
1. GROUP BY 与聚合函数（⭐⭐⭐⭐⭐）
必考知识点：
-- 五大聚合函数 COUNT(*) -- 统计所有行 COUNT(column) -- 统计非NULL值 COUNT(DISTINCT column) -- 统计不重复的非NULL值 AVG(column) -- 平均值，忽略NULL SUM(column) -- 求和，忽略NULL MAX(column) -- 最大值 MIN(column) -- 最小值高频考点：
- COUNT(*) vs COUNT(列) vs COUNT(DISTINCT 列) 的区别
- 聚合函数对NULL的处理方式
- GROUP BY 后 SELECT 只能包含分组列和聚合函数
- 多字段分组
示例：
-- ✅ 正确 SELECT department, AVG(salary) FROM employees GROUP BY department; -- ❌ 错误：name既不在GROUP BY中，也不是聚合函数 SELECT department, name, AVG(salary) FROM employees GROUP BY department;2. WHERE vs HAVING（⭐⭐⭐⭐⭐）
核心区别：
| 对比项 | WHERE | HAVING |
|---|---|---|
| 执行时机 | 分组前过滤 | 分组后过滤 |
| 能否使用聚合函数 | ❌ 不能 | ✅ 能 |
| 过滤对象 | 原始数据行 | 分组结果 |
| 性能 | 更优（先过滤再分组） | 较慢（先分组再过滤） |
示例：
-- ✅ 正确：先用WHERE过滤，再用HAVING筛选分组 SELECT department, AVG(salary) as avg_sal FROM employees WHERE hire_date >= '2020-01-01' -- WHERE不能用聚合函数 GROUP BY department HAVING AVG(salary) > 8000; -- HAVING可以用聚合函数 -- ❌ 错误：WHERE不能用聚合函数 SELECT department, AVG(salary) FROM employees WHERE AVG(salary) > 8000 -- 错误！ GROUP BY department; ``` **记忆口诀：WHERE先筛人，HAVING后选组** --- ### 3. SQL 执行顺序（⭐⭐⭐⭐⭐） **标准执行顺序：** ``` FROM -- 1. 确定数据来源 WHERE -- 2. 过滤原始数据 GROUP BY -- 3. 分组 HAVING -- 4. 过滤分组结果 SELECT -- 5. 选择列（计算表达式） DISTINCT -- 6. 去重 ORDER BY -- 7. 排序 LIMIT -- 8. 限制结果数量为什么要记住执行顺序？
-- 理解为什么这样写会报错 SELECT salary * 1.1 as new_salary FROM employees WHERE new_salary > 5000; -- ❌ 错误！WHERE在SELECT前执行，还没有new_salary -- 正确写法1：在WHERE中重复表达式 WHERE salary * 1.1 > 5000 -- 正确写法2：用子查询 SELECT * FROM ( SELECT salary * 1.1 as new_salary FROM employees ) t WHERE new_salary > 5000;4. 条件聚合（⭐⭐⭐⭐）
核心技巧：SUM/AVG + CASE WHEN
sql
-- 统计不同状态的数量 SELECT department, SUM(CASE WHEN status = '在职' THEN 1 ELSE 0 END) as active_count, SUM(CASE WHEN status = '离职' THEN 1 ELSE 0 END) as left_count, -- 计算占比 ROUND(SUM(CASE WHEN status = '在职' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as active_rate FROM employees GROUP BY department; -- 条件平均值 SELECT category, AVG(CASE WHEN price > 100 THEN price END) as avg_high_price, AVG(CASE WHEN price <= 100 THEN price END) as avg_low_price FROM products GROUP BY category;等价写法（MySQL特有）：
sql
-- 方法1: SUM + CASE SUM(CASE WHEN status = '在职' THEN 1 ELSE 0 END) -- 方法2: COUNT + IF COUNT(IF(status = '在职', 1, NULL)) -- 方法3: SUM + IF SUM(IF(status = '在职', 1, 0))5. 子查询与JOIN（⭐⭐⭐⭐）
常见场景：
场景1：找出每组的最大值对应的记录
-- 需求：找出每个部门工资最高的员工 SELECT e.* FROM employees e INNER JOIN ( SELECT department, MAX(salary) as max_sal FROM employees GROUP BY department ) t ON e.department = t.department AND e.salary = t.max_sal;场景2：与总体比较
-- 需求：找出销量高于平均销量的产品 SELECT product_name, sales_quantity FROM products WHERE sales_quantity > (SELECT AVG(sales_quantity) FROM products);场景3：计算百分比
-- 使用子查询计算占比 SELECT category, COUNT(*) as count, ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM products), 2) as percentage FROM products GROUP BY category;6. 窗口函数（⭐⭐⭐⭐）
MySQL 8.0+ 必考：
-- RANK() - 排名，有并列会跳号 (1,2,2,4) SELECT product_name, sales, RANK() OVER (ORDER BY sales DESC) as ranking FROM products; -- DENSE_RANK() - 排名，有并列不跳号 (1,2,2,3) SELECT product_name, sales, DENSE_RANK() OVER (ORDER BY sales DESC) as ranking FROM products; -- ROW_NUMBER() - 行号，不重复 (1,2,3,4) SELECT product_name, sales, ROW_NUMBER() OVER (ORDER BY sales DESC) as row_num FROM products; -- PARTITION BY - 分组排名 SELECT category, product_name, sales, RANK() OVER (PARTITION BY category ORDER BY sales DESC) as category_rank FROM products; -- LAG/LEAD - 访问前后行 SELECT date, sales, LAG(sales, 1) OVER (ORDER BY date) as prev_sales, LEAD(sales, 1) OVER (ORDER BY date) as next_sales FROM daily_sales;窗口函数 vs GROUP BY：
-- GROUP BY：结果行数减少 SELECT category, AVG(price) FROM products GROUP BY category; -- 结果：每个类别一行 -- 窗口函数：保留所有行 SELECT category, product_name, price, AVG(price) OVER (PARTITION BY category) as category_avg FROM products; -- 结果：每个产品一行，带有类别平均价格7. 日期函数（⭐⭐⭐⭐）
常用日期函数：
-- 日期提取 YEAR(date) -- 2023 MONTH(date) -- 1-12 DAY(date) -- 1-31 QUARTER(date) -- 1-4（季度） DAYOFWEEK(date) -- 1-7（1=周日） WEEK(date) -- 0-53（周数） -- 日期格式化 DATE_FORMAT(date, '%Y-%m') -- 2023-01 DATE_FORMAT(date, '%Y-%m-%d') -- 2023-01-15 DATE_FORMAT(date, '%Y-Q') -- 配合QUARTER使用 -- 日期计算 DATEDIFF(date1, date2) -- 天数差 DATE_ADD(date, INTERVAL 1 DAY) -- 加1天 DATE_SUB(date, INTERVAL 1 MONTH) -- 减1月 CURDATE() -- 当前日期 NOW() -- 当前日期时间 -- 常见用法 -- 按月分组 SELECT DATE_FORMAT(order_date, '%Y-%m') as month, COUNT(*) as order_count FROM orders GROUP BY DATE_FORMAT(order_date, '%Y-%m'); -- 计算天数 SELECT product_name, DATEDIFF(CURDATE(), shelf_date) as days_online FROM products;8. 字符串函数（⭐⭐⭐）
-- 常用字符串函数 CONCAT(str1, str2) -- 连接字符串 CONCAT_WS('-', str1, str2) -- 用分隔符连接 SUBSTRING(str, start, len) -- 截取子串 LENGTH(str) -- 字符串长度 UPPER(str) / LOWER(str) -- 大小写转换 TRIM(str) -- 去除首尾空格 REPLACE(str, old, new) -- 替换 -- 示例 SELECT CONCAT(first_name, ' ', last_name) as full_name, UPPER(email) as email_upper FROM users;二、常见陷阱与易错点
1. NULL值处理（⭐⭐⭐⭐⭐）
-- ❌ 常见错误 SELECT * FROM products WHERE price = NULL; -- 错误！永远不会匹配 SELECT * FROM products WHERE price != NULL; -- 错误！ -- ✅ 正确写法 SELECT * FROM products WHERE price IS NULL; SELECT * FROM products WHERE price IS NOT NULL; -- 聚合函数对NULL的处理 COUNT(*) -- 统计所有行，包括NULL COUNT(column) -- 统计非NULL值 AVG(column) -- 忽略NULL值计算平均 SUM(column) -- 忽略NULL值求和 -- 除零错误处理 -- ❌ 错误：可能除以0 SELECT sales / stock FROM products; -- ✅ 正确：使用NULLIF SELECT sales / NULLIF(stock, 0) FROM products; -- 0变成NULL，结果为NULL而不是错误 -- ✅ 使用IFNULL/COALESCE SELECT IFNULL(sales / NULLIF(stock, 0), 0) FROM products; -- NULL转02. COUNT的区别（⭐⭐⭐⭐⭐）
CREATE TABLE test ( id INT, name VARCHAR(50), score INT ); INSERT INTO test VALUES (1, 'Alice', 90), (2, 'Bob', NULL), (3, 'Charlie', 85), (4, NULL, 80); -- 对比不同COUNT的结果 SELECT COUNT(*) as count_all, -- 4（所有行） COUNT(name) as count_name, -- 3（name非NULL的行） COUNT(score) as count_score, -- 3（score非NULL的行） COUNT(DISTINCT name) as count_distinct -- 3（不重复的name） FROM test;面试常问：COUNT(*) vs COUNT(1) vs COUNT(字段)的性能差异？
- COUNT() 和 COUNT(1) 性能相同，推荐用 COUNT()（更直观）
- COUNT(字段) 需要判断NULL，略慢
- 在有NOT NULL约束的字段上，COUNT(字段) ≈ COUNT(*)
3. GROUP BY 的严格模式（⭐⭐⭐⭐）
MySQL 5.7+ 默认开启 ONLY_FULL_GROUP_BY 模
-- ❌ 错误（严格模式下） SELECT department, employee_name, AVG(salary) FROM employees GROUP BY department; -- 错误：employee_name既不在GROUP BY中，也不是聚合函数 -- ✅ 正确写法1：只选择分组列和聚合函数 SELECT department, AVG(salary) FROM employees GROUP BY department; -- ✅ 正确写法2：都加入GROUP BY SELECT department, employee_name, AVG(salary) FROM employees GROUP BY department, employee_name; -- ✅ 正确写法3：使用ANY_VALUE（MySQL 5.7+） SELECT department, ANY_VALUE(employee_name), AVG(salary) FROM employees GROUP BY department;4. 字符串与数字比较（⭐⭐⭐）
-- 隐式类型转换可能导致意外结果 SELECT * FROM products WHERE product_id = '123'; -- 字符串 '123' 转为数字 SELECT * FROM products WHERE product_id = '123abc'; -- '123abc' 转为 123 -- ⚠️ 索引失效：对列进行函数操作 SELECT * FROM orders WHERE YEAR(order_date) = 2023; -- 索引失效 -- ✅ 更好的写法 SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';5. LIMIT 与分页（⭐⭐⭐）
-- 基本分页 SELECT * FROM products ORDER BY product_id LIMIT 10 OFFSET 20; -- 跳过20条，取10条 -- 等价写法 LIMIT 20, 10; -- offset, count -- ⚠️ 深分页性能问题 SELECT * FROM products LIMIT 1000000, 10; -- 非常慢！需要扫描100万+10行 -- ✅ 优化：使用WHERE + 主键 SELECT * FROM products WHERE product_id > 1000000 ORDER BY product_id LIMIT 10; -- ✅ 使用子查询优化 SELECT * FROM products WHERE product_id >= ( SELECT product_id FROM products ORDER BY product_id LIMIT 1000000, 1 ) LIMIT 10;6. JOIN的陷阱（⭐⭐⭐⭐）
-- ⚠️ 一对多关联导致数据重复 SELECT o.order_id, SUM(o.amount) -- 错误！金额会重复累加 FROM orders o JOIN order_details od ON o.order_id = od.order_id GROUP BY o.order_id; -- ✅ 正确：使用DISTINCT或先聚合 SELECT o.order_id, o.amount -- 不要在JOIN后直接SUM原表金额 FROM orders o JOIN order_details od ON o.order_id = od.order_id GROUP BY o.order_id, o.amount; -- 或者 SELECT o.order_id, o.amount, COUNT(od.detail_id) as item_count FROM orders o LEFT JOIN order_details od ON o.order_id = od.order_id GROUP BY o.order_id, o.amount;三、性能优化考虑
1. WHERE vs HAVING 性能
-- ❌ 性能差：先分组再过滤 SELECT category, COUNT(*) FROM products GROUP BY category HAVING category = '电子产品'; -- ✅ 性能好：先过滤再分组 SELECT category, COUNT(*) FROM products WHERE category = '电子产品' GROUP BY category;原则：能用WHERE就不用HAVING
2. 索引友好的查询
-- ❌ 索引失效 WHERE YEAR(order_date) = 2023 WHERE salary * 1.2 > 10000 WHERE SUBSTRING(name, 1, 3) = 'ABC' -- ✅ 索引友好 WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01' WHERE salary > 10000 / 1.2 WHERE name LIKE 'ABC%'原则：不要在WHERE条件的列上使用函数或计算
3. SELECT 优化
-- ❌ 避免 SELECT * SELECT * FROM large_table; -- 传输大量不需要的数据 -- ✅ 只选择需要的列 SELECT id, name, price FROM large_table; -- ❌ 避免重复的子查询 SELECT name, (SELECT AVG(price) FROM products) as avg1, (SELECT AVG(price) FROM products) as avg2 -- 重复查询 FROM products; -- ✅ 使用JOIN或变量 SELECT p.name, t.avg_price, t.avg_price FROM products p CROSS JOIN (SELECT AVG(price) as avg_price FROM products) t;4. LIMIT优化
-- 大数据量时，使用覆盖索引 SELECT id, name FROM products ORDER BY id LIMIT 1000000, 10; -- 使用延迟关联 SELECT p.* FROM products p INNER JOIN ( SELECT id FROM products ORDER BY id LIMIT 1000000, 10 ) t ON p.id = t.id; ``` --- ## 四、面试答题技巧 ### 1. 解题步骤（5步法） ``` 步骤1：理解需求 - 要查询什么数据？ - 需要哪些表？ - 结果应该是什么样的？ 步骤2：确定数据源 - FROM 哪些表？ - 需要JOIN吗？ 步骤3：确定过滤条件 - WHERE 过滤什么？（分组前） - HAVING 过滤什么？（分组后） 步骤4：确定分组和聚合 - 需要GROUP BY吗？ - 用什么聚合函数？ 步骤5：确定排序和限制 - ORDER BY排序 - LIMIT限制数量 ``` --- ### 2. 口头表达技巧 **回答问题时的完整流程：** ``` 1. 复述需求： "这道题要求统计每个部门的平均工资..." 2. 说明思路： "首先我会用GROUP BY按部门分组，然后用AVG函数计算平均工资..." 3. 注意特殊情况： "需要注意NULL值的处理，以及是否需要过滤某些部门..." 4. 写SQL： （边写边解释关键点） 5. 验证结果： "这个查询会返回每个部门一行，包含部门名和平均工资..." ``` --- ### 3. 常见面试问题 **Q1: WHERE和HAVING的区别？** ``` A: - WHERE在分组前过滤，不能使用聚合函数 - HAVING在分组后过滤，可以使用聚合函数 - 性能上WHERE更优，因为减少了参与分组的数据量 - 执行顺序：WHERE → GROUP BY → HAVING ``` **Q2: COUNT(*)、COUNT(1)、COUNT(列)的区别？** ``` A: - COUNT(*)：统计所有行，包括NULL - COUNT(1)：与COUNT(*)效果相同，性能也相同 - COUNT(列)：只统计该列非NULL的行 - COUNT(DISTINCT 列)：统计不重复的非NULL值 ``` **Q3: GROUP BY后SELECT的列有什么限制？** ``` A: - 在MySQL 5.7+的严格模式（ONLY_FULL_GROUP_BY）下 - SELECT只能包含：GROUP BY的列 + 聚合函数 - 如果包含其他列会报错 - 可以用ANY_VALUE()函数临时解决，但不推荐 ``` **Q4: 如何优化GROUP BY的性能？** ``` A: 1. 在分组列上建立索引 2. 先用WHERE过滤，减少分组数据量 3. 避免在GROUP BY列上使用函数 4. 考虑使用覆盖索引 5. 必要时可以考虑使用分区表五、实用技巧速查表
1. 百分比计算
-- 计算占比 ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM table), 2) as percentage -- 增长率 ROUND((new_value - old_value) / old_value * 100, 2) as growth_rate2. 排名相关
-- Top N ORDER BY xxx DESC LIMIT N -- 每组Top N（窗口函数） RANK() OVER (PARTITION BY category ORDER BY sales DESC) -- 百分位数 PERCENT_RANK() OVER (ORDER BY score)3. 时间相关
-- 本月数据 WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) -- 最近30天 WHERE date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) -- 本年数据 WHERE YEAR(date) = YEAR(CURDATE())4. 条件统计
-- 统计满足条件的数量 SUM(CASE WHEN condition THEN 1 ELSE 0 END) -- 条件平均 AVG(CASE WHEN condition THEN value END) -- 条件求和 SUM(CASE WHEN condition THEN value ELSE 0 END) ``` --- ## 六、考前检查清单 **写完SQL后检查：** ``` □ SELECT的列是否都在GROUP BY中或是聚合函数？ □ WHERE是否错用了聚合函数？ □ HAVING是否能改用WHERE？ □ 是否处理了NULL值？ □ 是否处理了除零错误？ □ COUNT是否用对了（*、列、DISTINCT）？ □ JOIN是否导致数据重复？ □ 日期函数是否使用正确？ □ 是否需要ORDER BY？ □ 是否需要ROUND保留小数？七、高频考题模板
模板1：每组求最值
-- 找出每个部门工资最高的员工 SELECT e.* FROM employees e INNER JOIN ( SELECT department, MAX(salary) as max_sal FROM employees GROUP BY department ) t ON e.department = t.department AND e.salary = t.max_sal;模板2：与总体比较
-- 找出高于平均值的记录 SELECT * FROM table WHERE value > (SELECT AVG(value) FROM table);模板3：累计计算
-- 计算累计和（窗口函数） SELECT date, amount, SUM(amount) OVER (ORDER BY date) as cumulative_sum FROM sales;模板4：同比/环比
-- 计算环比增长 SELECT month, sales, LAG(sales) OVER (ORDER BY month) as prev_month_sales, ROUND((sales - LAG(sales) OVER (ORDER BY month)) / LAG(sales) OVER (ORDER BY month) * 100, 2) as growth_rate FROM monthly_sales;模板5：条件分组统计
-- 统计不同条件下的数量 SELECT category, SUM(CASE WHEN price < 100 THEN 1 ELSE 0 END) as low_price_count, SUM(CASE WHEN price BETWEEN 100 AND 500 THEN 1 ELSE 0 END) as mid_price_count, SUM(CASE WHEN price > 500 THEN 1 ELSE 0 END) as high_price_count FROM products GROUP BY category; ``` --- ## 八、最后的建议 ### 面试前准备 1. **手写练习**：在纸上或白板上写SQL，不依赖IDE提示 2. **背诵顺序**：牢记SQL执行顺序 3. **积累模板**：整理常见题型的SQL模板 4. **理解业务**：不只是写SQL，要理解业务含义 ### 面试中表现 1. **不要急于写代码**：先理清思路，口述解题思路 2. **边写边说**：解释每一步的目的 3. **考虑边界**：主动提及NULL、除零等特殊情况 4. **主动优化**：提出性能优化建议 ### 常见加分项 1. 主动讨论索引优化 2. 提出多种解法并比较优劣 3. 考虑数据量大时的处理方案 4. 提及实际业务中的注意事项 ### 常见扣分项 1. WHERE中使用聚合函数 2. 忘记处理NULL值 3. GROUP BY后SELECT非法列 4. 忘记排序和限制数量 5. 不考虑性能，写出低效SQL --- **核心记忆口诀：** ``` FROM开头定来源 WHERE过滤先行人 GROUP分组看类别 HAVING筛组有条件 SELECT选出所需列 ORDER排序分先后 LIMIT最后定数量
