第二阶段：SQL 进阶查询

2.1 聚合与分组

2.1.1 聚合函数

聚合函数对一组值进行计算，返回单一结果。

sql

-- ==================== 常用聚合函数 ====================

-- COUNT: 计数
SELECT COUNT(*) AS total_books FROM books;              -- 统计总行数（含 NULL）
SELECT COUNT(description) FROM books;                   -- 统计非 NULL 的行数
SELECT COUNT(DISTINCT category_id) FROM books;          -- 去重计数

-- SUM: 求和
SELECT SUM(price) AS total_price FROM books;
SELECT SUM(price * stock) AS total_inventory_value FROM books;  -- 计算总库存价值

-- AVG: 平均值
SELECT AVG(price) AS avg_price FROM books;
SELECT ROUND(AVG(price), 2) AS avg_price FROM books;   -- 保留 2 位小数

-- MAX / MIN: 最大 / 最小值
SELECT MAX(price) AS most_expensive FROM books;
SELECT MIN(price) AS cheapest FROM books;
SELECT MAX(publish_date) AS latest, MIN(publish_date) AS earliest FROM books;

-- ⚠️ 聚合函数自动忽略 NULL 值（除了 COUNT(*)）
-- AVG(column) = SUM(column) / COUNT(column)，不是 SUM / COUNT(*)

2.1.2 GROUP BY 分组查询

sql

-- 按分类统计图书数量和平均价格
SELECT
  c.name AS category,
  COUNT(*) AS book_count,
  ROUND(AVG(b.price), 2) AS avg_price,
  SUM(b.stock) AS total_stock
FROM books b
JOIN categories c ON b.category_id = c.id
GROUP BY c.name;

-- 输出：
-- +---------+------------+-----------+-------------+
-- | category| book_count | avg_price | total_stock |
-- +---------+------------+-----------+-------------+
-- | 文学    |          5 |     38.40 |         380 |
-- | 科幻    |          3 |     31.00 |         470 |
-- +---------+------------+-----------+-------------+

-- 按作者统计
SELECT
  a.name AS author,
  COUNT(*) AS books_written,
  SUM(b.price) AS total_price
FROM books b
JOIN authors a ON b.author_id = a.id
GROUP BY a.name
ORDER BY books_written DESC;

-- 多列分组
SELECT
  a.country,
  c.name AS category,
  COUNT(*) AS book_count
FROM books b
JOIN authors a ON b.author_id = a.id
JOIN categories c ON b.category_id = c.id
GROUP BY a.country, c.name;

2.1.3 HAVING 过滤分组结果

HAVING 用于在分组后过滤，WHERE 用于分组前过滤。

sql

-- WHERE vs HAVING
-- WHERE: 在 GROUP BY 之前过滤行（不能使用聚合函数）
-- HAVING: 在 GROUP BY 之后过滤组（可以使用聚合函数）

-- 查找出版了 2 本及以上图书的作者
SELECT
  a.name AS author,
  COUNT(*) AS book_count
FROM books b
JOIN authors a ON b.author_id = a.id
GROUP BY a.name
HAVING book_count >= 2;            -- ✅ HAVING 可以使用聚合结果

-- 查找平均价格大于 30 的分类
SELECT
  c.name AS category,
  ROUND(AVG(b.price), 2) AS avg_price
FROM books b
JOIN categories c ON b.category_id = c.id
GROUP BY c.name
HAVING avg_price > 30;

-- WHERE + HAVING 组合使用
SELECT
  a.name AS author,
  COUNT(*) AS book_count,
  AVG(b.price) AS avg_price
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE b.publish_date >= '2000-01-01'   -- 先过滤：只看 2000 年后出版的
GROUP BY a.name
HAVING book_count >= 2;                -- 再过滤：至少出版了 2 本

SQL 执行顺序（重要！）：

FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

  ① FROM        确定数据来源
  ② WHERE       行级过滤
  ③ GROUP BY    分组
  ④ HAVING      组级过滤
  ⑤ SELECT      选择列（此时才计算列别名）
  ⑥ DISTINCT    去重
  ⑦ ORDER BY    排序
  ⑧ LIMIT       分页

2.2 多表查询（JOIN）

2.2.1 JOIN 类型概览

假设两张表：

  Table A (books)          Table B (authors)
  ┌────┬──────────┐        ┌────┬──────────┐
  │ id │ author_id│        │ id │ name     │
  ├────┼──────────┤        ├────┼──────────┤
  │ 1  │ 1        │        │ 1  │ Alice    │
  │ 2  │ 2        │        │ 2  │ Bob      │
  │ 3  │ NULL     │        │ 3  │ Charlie  │  ← 没有关联的书
  └────┴──────────┘        └────┴──────────┘
        ↑ 没有作者

  INNER JOIN          LEFT JOIN           RIGHT JOIN          FULL JOIN (MySQL 不支持)
  ┌─────┬─────┐      ┌─────┬─────┐      ┌─────┬─────┐      ┌─────┬─────┐
  │  A  │  B  │      │  A  │  B  │      │  A  │  B  │      │  A  │  B  │
  │  1  │  1  │      │  1  │  1  │      │  1  │  1  │      │  1  │  1  │
  │  2  │  2  │      │  2  │  2  │      │  2  │  2  │      │  2  │  2  │
  └─────┴─────┘      │  3  │ NULL│      │ NULL│  3  │      │  3  │ NULL│
                      └─────┴─────┘      └─────┴─────┘      │ NULL│  3  │
                                                              └─────┴─────┘
  只返回匹配的行      左表全部 +            右表全部 +            两表全部
                      右表匹配的            左表匹配的

2.2.2 INNER JOIN（内连接）

sql

-- 只返回两表都有匹配的行
SELECT
  b.title,
  a.name AS author,
  c.name AS category,
  b.price
FROM books b
INNER JOIN authors a ON b.author_id = a.id
INNER JOIN categories c ON b.category_id = c.id;

-- INNER 可以省略
SELECT b.title, a.name AS author
FROM books b
JOIN authors a ON b.author_id = a.id;

-- 等价的隐式写法（旧语法，不推荐）
SELECT b.title, a.name
FROM books b, authors a
WHERE b.author_id = a.id;

2.2.3 LEFT JOIN（左外连接）

sql

-- 返回左表所有行，右表无匹配则为 NULL
-- 场景：查看所有作者，包括没有出版书籍的作者
SELECT
  a.name AS author,
  b.title,
  b.price
FROM authors a
LEFT JOIN books b ON a.id = b.author_id;

-- 查找没有出版任何书籍的作者
SELECT a.name AS author
FROM authors a
LEFT JOIN books b ON a.id = b.author_id
WHERE b.id IS NULL;                    -- 右表为 NULL → 没有匹配

-- 统计每个作者的图书数量（包含 0 本的作者）
SELECT
  a.name AS author,
  COUNT(b.id) AS book_count            -- COUNT(b.id) 而不是 COUNT(*)
FROM authors a
LEFT JOIN books b ON a.id = b.author_id
GROUP BY a.name
ORDER BY book_count DESC;

2.2.4 RIGHT JOIN（右外连接）

sql

-- 返回右表所有行，左表无匹配则为 NULL
-- 等价于交换表位置后的 LEFT JOIN

SELECT
  b.title,
  a.name AS author
FROM books b
RIGHT JOIN authors a ON b.author_id = a.id;

-- 实际开发中 LEFT JOIN 用得更多，RIGHT JOIN 很少使用
-- 建议：统一使用 LEFT JOIN，通过调整表的顺序来实现需求

2.2.5 自连接

sql

-- 自连接：同一张表与自身做 JOIN
-- 场景：员工表中查找每个员工的上级

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  manager_id INT,                      -- 引用自身的 id
  salary DECIMAL(10, 2)
);

INSERT INTO employees (name, manager_id, salary) VALUES
  ('CEO', NULL, 100000),
  ('VP Engineering', 1, 80000),
  ('VP Sales', 1, 75000),
  ('Senior Dev', 2, 60000),
  ('Junior Dev', 2, 40000),
  ('Sales Manager', 3, 55000);

-- 查询每个员工及其上级名称
SELECT
  e.name AS employee,
  e.salary,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 输出：
-- +----------------+--------+-----------------+
-- | employee       | salary | manager         |
-- +----------------+--------+-----------------+
-- | CEO            | 100000 | NULL            |
-- | VP Engineering |  80000 | CEO             |
-- | VP Sales       |  75000 | CEO             |
-- | Senior Dev     |  60000 | VP Engineering  |
-- | Junior Dev     |  40000 | VP Engineering  |
-- | Sales Manager  |  55000 | VP Sales        |
-- +----------------+--------+-----------------+

-- 查找薪资高于其上级的员工
SELECT
  e.name AS employee,
  e.salary AS emp_salary,
  m.name AS manager,
  m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

2.2.6 UNION 与 UNION ALL

sql

-- UNION: 合并两个查询结果集（自动去重）
-- UNION ALL: 合并但不去重（更快）

-- 查询中国和日本作者的书（两种不同的筛选条件）
SELECT b.title, a.name, '中国作者' AS source
FROM books b JOIN authors a ON b.author_id = a.id
WHERE a.country = '中国'

UNION ALL

SELECT b.title, a.name, '日本作者' AS source
FROM books b JOIN authors a ON b.author_id = a.id
WHERE a.country = '日本';

-- ⚠️ UNION 要求：
-- 1. 两个 SELECT 的列数必须相同
-- 2. 对应列的数据类型要兼容
-- 3. 列名以第一个 SELECT 为准

-- UNION vs UNION ALL
-- UNION: 去重（有排序开销，较慢）
-- UNION ALL: 不去重（更快，优先使用）

2.3 子查询

2.3.1 子查询类型与示例

sql

-- ==================== 标量子查询（返回单行单列）====================
-- 查询价格高于平均价格的图书
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

-- 查询最新出版的图书
SELECT title, publish_date
FROM books
WHERE publish_date = (SELECT MAX(publish_date) FROM books);

-- ==================== 列子查询（返回多行单列）====================
-- 查询有出版书籍的作者（IN）
SELECT name FROM authors
WHERE id IN (SELECT DISTINCT author_id FROM books WHERE author_id IS NOT NULL);

-- 查询没有出版书籍的作者（NOT IN）
SELECT name FROM authors
WHERE id NOT IN (SELECT author_id FROM books WHERE author_id IS NOT NULL);

-- ==================== 行子查询（返回单行多列）====================
-- 查询与"三体"同作者同分类的书
SELECT title, author_id, category_id
FROM books
WHERE (author_id, category_id) = (
  SELECT author_id, category_id FROM books WHERE title = '三体'
)
AND title != '三体';

-- ==================== 表子查询（返回多行多列）====================
-- 查询每个分类中最贵的书
SELECT b.title, b.price, c.name AS category
FROM books b
JOIN (
  SELECT category_id, MAX(price) AS max_price
  FROM books
  GROUP BY category_id
) AS max_books ON b.category_id = max_books.category_id AND b.price = max_books.max_price
JOIN categories c ON b.category_id = c.id;

2.3.2 EXISTS 与 NOT EXISTS

sql

-- EXISTS: 检查子查询是否返回任何行（返回 TRUE/FALSE）
-- 比 IN 更高效（特别是子查询结果集大时）

-- 查询有出版书籍的作者（使用 EXISTS）
SELECT a.name
FROM authors a
WHERE EXISTS (
  SELECT 1 FROM books b WHERE b.author_id = a.id
);

-- 查询没有出版书籍的作者（使用 NOT EXISTS）
SELECT a.name
FROM authors a
WHERE NOT EXISTS (
  SELECT 1 FROM books b WHERE b.author_id = a.id
);

-- IN vs EXISTS 的选择：
-- 外表大、子查询小 → 用 IN
-- 外表小、子查询大 → 用 EXISTS
-- 实际上 MySQL 优化器在很多场景下会自动转换

2.3.3 相关子查询 vs 非相关子查询

sql

-- 非相关子查询：子查询独立执行，不依赖外部查询（执行一次）
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);  -- 子查询只执行一次

-- 相关子查询：子查询引用了外部查询的列（每行执行一次）
-- 查询价格高于同分类平均价格的图书
SELECT b.title, b.price, b.category_id
FROM books b
WHERE b.price > (
  SELECT AVG(b2.price)
  FROM books b2
  WHERE b2.category_id = b.category_id   -- 引用了外部的 b.category_id
);
-- ⚠️ 相关子查询性能较差（每行执行一次子查询），尽量改写为 JOIN

2.4 常用函数

2.4.1 字符串函数

sql

-- 拼接
SELECT CONCAT('Hello', ' ', 'World');           -- → Hello World
SELECT CONCAT_WS('-', '2026', '03', '13');      -- → 2026-03-13（指定分隔符）

-- 截取
SELECT SUBSTRING('Hello World', 1, 5);          -- → Hello（从第 1 位开始取 5 个）
SELECT LEFT('Hello World', 5);                  -- → Hello
SELECT RIGHT('Hello World', 5);                 -- → World

-- 长度
SELECT LENGTH('Hello');                         -- → 5（字节数）
SELECT CHAR_LENGTH('你好');                      -- → 2（字符数）
SELECT LENGTH('你好');                           -- → 6（utf8mb4 每个中文 3 字节）

-- 查找与替换
SELECT LOCATE('World', 'Hello World');           -- → 7（位置，从 1 开始）
SELECT REPLACE('Hello World', 'World', 'MySQL'); -- → Hello MySQL

-- 大小写
SELECT UPPER('hello');                           -- → HELLO
SELECT LOWER('HELLO');                           -- → hello

-- 去空格
SELECT TRIM('  hello  ');                        -- → hello
SELECT LTRIM('  hello  ');                       -- → hello  （左去空格）
SELECT RTRIM('  hello  ');                       -- →   hello（右去空格）

-- 填充
SELECT LPAD('42', 5, '0');                       -- → 00042（左填充）
SELECT RPAD('hi', 5, '!');                       -- → hi!!!（右填充）

-- 反转
SELECT REVERSE('Hello');                         -- → olleH

2.4.2 数值函数

sql

SELECT ROUND(3.1415, 2);     -- → 3.14（四舍五入）
SELECT CEIL(3.14);            -- → 4（向上取整）
SELECT FLOOR(3.99);           -- → 3（向下取整）
SELECT ABS(-42);              -- → 42（绝对值）
SELECT MOD(10, 3);            -- → 1（取模）
SELECT RAND();                -- → 0.123...（随机数 0-1）
SELECT FLOOR(RAND() * 100);  -- → 随机整数 0-99
SELECT TRUNCATE(3.1415, 2);  -- → 3.14（截断，不四舍五入）
SELECT POWER(2, 10);          -- → 1024（幂运算）
SELECT SQRT(144);             -- → 12（平方根）

2.4.3 日期时间函数

sql

-- 获取当前时间
SELECT NOW();                            -- → 2026-03-13 10:30:00
SELECT CURDATE();                        -- → 2026-03-13
SELECT CURTIME();                        -- → 10:30:00
SELECT UNIX_TIMESTAMP();                 -- → 1773590200（时间戳）

-- 提取日期部分
SELECT YEAR('2026-03-13');               -- → 2026
SELECT MONTH('2026-03-13');              -- → 3
SELECT DAY('2026-03-13');                -- → 13
SELECT HOUR('10:30:45');                 -- → 10
SELECT DAYOFWEEK('2026-03-13');          -- → 6（1=周日，6=周五）
SELECT DAYNAME('2026-03-13');            -- → Thursday

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s');
-- → 2026年03月13日 10:30:00

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');   -- → 2026-03-13

-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);         -- 7 天后
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);       -- 1 个月后
SELECT DATE_SUB(NOW(), INTERVAL 30 DAY);        -- 30 天前
SELECT DATEDIFF('2026-12-31', '2026-01-01');    -- → 364（天数差）
SELECT TIMESTAMPDIFF(HOUR, '2026-03-13 08:00', '2026-03-13 17:30');  -- → 9

-- STR_TO_DATE（字符串转日期）
SELECT STR_TO_DATE('2026-03-13', '%Y-%m-%d');
SELECT STR_TO_DATE('13/03/2026', '%d/%m/%Y');

2.4.4 流程控制函数

sql

-- IF 函数
SELECT title, price,
  IF(price > 30, '高价', '低价') AS price_level
FROM books;

-- IFNULL / COALESCE（NULL 处理）
SELECT title, IFNULL(description, '暂无描述') AS desc FROM books;
SELECT COALESCE(description, bio, '默认值') FROM books;  -- 返回第一个非 NULL 值

-- CASE WHEN（最强大的条件判断）
SELECT title, price,
  CASE
    WHEN price >= 50 THEN '昂贵'
    WHEN price >= 30 THEN '中等'
    WHEN price >= 15 THEN '便宜'
    ELSE '特价'
  END AS price_category
FROM books;

-- CASE 简单用法
SELECT title, status,
  CASE status
    WHEN 'active' THEN '活跃'
    WHEN 'inactive' THEN '非活跃'
    WHEN 'banned' THEN '封禁'
    ELSE '未知'
  END AS status_cn
FROM users;

-- CASE WHEN 在聚合中的应用（行转列）
SELECT
  a.name AS author,
  COUNT(CASE WHEN c.name = '文学' THEN 1 END) AS literature_count,
  COUNT(CASE WHEN c.name = '科幻' THEN 1 END) AS scifi_count
FROM books b
JOIN authors a ON b.author_id = a.id
JOIN categories c ON b.category_id = c.id
GROUP BY a.name;

2.4.5 窗口函数（MySQL 8.0+）

窗口函数是 MySQL 8.0 引入的强大特性，可以在不改变行数的情况下进行分组计算。

sql

-- 窗口函数语法：
-- function_name() OVER (
--   [PARTITION BY col]     -- 分组（类似 GROUP BY，但不合并行）
--   [ORDER BY col]         -- 排序
--   [ROWS/RANGE frame]     -- 窗口范围
-- )

-- ==================== ROW_NUMBER: 行号 ====================
SELECT
  title, price, category_id,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank
FROM books;

-- 每个分类内按价格排名
SELECT
  title, price,
  c.name AS category,
  ROW_NUMBER() OVER (PARTITION BY b.category_id ORDER BY b.price DESC) AS rank_in_category
FROM books b
JOIN categories c ON b.category_id = c.id;

-- ==================== RANK / DENSE_RANK: 排名（处理并列）====================
-- RANK: 并列后跳号（1, 2, 2, 4）
-- DENSE_RANK: 并列后不跳号（1, 2, 2, 3）

SELECT
  title, price,
  RANK() OVER (ORDER BY price DESC) AS rank_skip,
  DENSE_RANK() OVER (ORDER BY price DESC) AS rank_no_skip
FROM books;

-- ==================== LAG / LEAD: 获取前/后行的值 ====================
SELECT
  title,
  publish_date,
  LAG(title, 1) OVER (ORDER BY publish_date) AS prev_book,
  LEAD(title, 1) OVER (ORDER BY publish_date) AS next_book
FROM books;

-- ==================== SUM/AVG/COUNT OVER: 累计/移动计算 ====================
-- 累计求和
SELECT
  title, price,
  SUM(price) OVER (ORDER BY publish_date) AS cumulative_price
FROM books;

-- 移动平均（当前行及前 2 行）
SELECT
  title, price,
  ROUND(AVG(price) OVER (
    ORDER BY publish_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_3
FROM books;

-- 每个分类的占比
SELECT
  title, price,
  c.name AS category,
  ROUND(price / SUM(price) OVER (PARTITION BY b.category_id) * 100, 1) AS pct_in_category
FROM books b
JOIN categories c ON b.category_id = c.id;

-- ==================== NTILE: 等分桶 ====================
SELECT
  title, price,
  NTILE(3) OVER (ORDER BY price) AS price_bucket  -- 按价格分为 3 桶
FROM books;

-- ==================== 实用场景：每个分类中最贵的书 ====================
-- 用窗口函数代替复杂的子查询
SELECT * FROM (
  SELECT
    b.title, b.price, c.name AS category,
    ROW_NUMBER() OVER (PARTITION BY b.category_id ORDER BY b.price DESC) AS rn
  FROM books b
  JOIN categories c ON b.category_id = c.id
) ranked
WHERE rn = 1;

2.5 实践 Demo

Demo 1：销售数据分析

sql

-- 创建销售记录表
CREATE TABLE sales (
  id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT UNSIGNED,
  quantity INT UNSIGNED NOT NULL,
  sale_price DECIMAL(10, 2) NOT NULL,
  sale_date DATE NOT NULL,
  FOREIGN KEY (book_id) REFERENCES books(id)
);

-- 插入测试数据
INSERT INTO sales (book_id, quantity, sale_price, sale_date) VALUES
  (1, 3, 25.00, '2026-01-15'), (4, 5, 23.00, '2026-01-20'),
  (2, 2, 36.00, '2026-02-01'), (3, 1, 68.00, '2026-02-10'),
  (4, 3, 23.00, '2026-02-15'), (5, 4, 32.00, '2026-02-20'),
  (1, 2, 25.00, '2026-03-01'), (6, 2, 38.00, '2026-03-05'),
  (4, 6, 23.00, '2026-03-10'), (2, 3, 36.00, '2026-03-12');

-- ===== 分析 1: 月度销售统计 =====
SELECT
  DATE_FORMAT(sale_date, '%Y-%m') AS month,
  COUNT(*) AS order_count,
  SUM(quantity) AS total_quantity,
  ROUND(SUM(quantity * sale_price), 2) AS total_revenue
FROM sales
GROUP BY month
ORDER BY month;

-- ===== 分析 2: 畅销书 TOP 5 =====
SELECT
  b.title,
  SUM(s.quantity) AS total_sold,
  ROUND(SUM(s.quantity * s.sale_price), 2) AS total_revenue
FROM sales s
JOIN books b ON s.book_id = b.id
GROUP BY b.title
ORDER BY total_sold DESC
LIMIT 5;

-- ===== 分析 3: 每月环比增长 =====
WITH monthly_revenue AS (
  SELECT
    DATE_FORMAT(sale_date, '%Y-%m') AS month,
    SUM(quantity * sale_price) AS revenue
  FROM sales
  GROUP BY month
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / LAG(revenue) OVER (ORDER BY month) * 100, 1
  ) AS growth_pct
FROM monthly_revenue;

-- ===== 分析 4: 按分类的销售占比 =====
SELECT
  c.name AS category,
  SUM(s.quantity * s.sale_price) AS revenue,
  ROUND(
    SUM(s.quantity * s.sale_price)
    / (SELECT SUM(quantity * sale_price) FROM sales) * 100, 1
  ) AS revenue_pct
FROM sales s
JOIN books b ON s.book_id = b.id
JOIN categories c ON b.category_id = c.id
GROUP BY c.name
ORDER BY revenue DESC;

Demo 2：窗口函数综合练习

sql

-- ===== 图书价格排名（全局 + 分类内）=====
SELECT
  b.title,
  c.name AS category,
  b.price,
  RANK() OVER (ORDER BY b.price DESC) AS global_rank,
  RANK() OVER (PARTITION BY b.category_id ORDER BY b.price DESC) AS category_rank
FROM books b
JOIN categories c ON b.category_id = c.id;

-- ===== 每个分类的 TOP 1 最贵图书 =====
SELECT title, category, price FROM (
  SELECT
    b.title, c.name AS category, b.price,
    ROW_NUMBER() OVER (PARTITION BY b.category_id ORDER BY b.price DESC) AS rn
  FROM books b
  JOIN categories c ON b.category_id = c.id
) ranked WHERE rn = 1;

-- ===== 累计销售额 =====
SELECT
  sale_date,
  SUM(quantity * sale_price) AS daily_revenue,
  SUM(SUM(quantity * sale_price)) OVER (ORDER BY sale_date) AS cumulative_revenue
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

2.6 常见问题 QA

Q1: `GROUP BY` 报错 — `sql_mode=only_full_group_by`

sql

-- MySQL 8.0 默认启用 ONLY_FULL_GROUP_BY
-- SELECT 中的非聚合列必须出现在 GROUP BY 中

-- ❌ 错误：title 不在 GROUP BY 中
SELECT title, category_id, COUNT(*) FROM books GROUP BY category_id;

-- ✅ 正确
SELECT category_id, COUNT(*) FROM books GROUP BY category_id;

-- ✅ 或把 title 也加入 GROUP BY
SELECT title, category_id, COUNT(*) FROM books GROUP BY title, category_id;

-- ✅ 或使用 ANY_VALUE()（不推荐）
SELECT ANY_VALUE(title), category_id, COUNT(*) FROM books GROUP BY category_id;

Q2: `NULL` 值的注意事项

sql

-- NULL 参与运算的结果都是 NULL
SELECT 1 + NULL;          -- → NULL
SELECT NULL = NULL;       -- → NULL（不是 TRUE！）
SELECT NULL != NULL;      -- → NULL

-- 正确判断 NULL
SELECT * FROM books WHERE description IS NULL;      -- ✅
SELECT * FROM books WHERE description IS NOT NULL;  -- ✅
SELECT * FROM books WHERE description = NULL;       -- ❌ 永远不匹配

-- NULL 在 ORDER BY 中排在最前面（ASC 时）
-- 在聚合函数中被忽略
-- 在 DISTINCT 中多个 NULL 被视为相同值

Q3: JOIN 时出现笛卡尔积（数据量暴增）

sql

-- 原因：缺少 ON 条件或条件写错
-- 笛卡尔积 = A 表行数 × B 表行数

-- ❌ 忘记写 ON 条件
SELECT * FROM books CROSS JOIN authors;  -- 8 × 5 = 40 行！

-- ❌ ON 条件写错（关联错误的列）
SELECT * FROM books b JOIN authors a ON b.id = a.id;  -- 语义上错误

-- ✅ 正确
SELECT * FROM books b JOIN authors a ON b.author_id = a.id;

-- 排查方法：检查结果行数是否远超预期

Q4: 子查询和 JOIN 怎么选？

sql

-- 经验法则：
-- 1. 能用 JOIN 就用 JOIN（通常性能更好，优化器容易优化）
-- 2. 需要复用查询结果 → CTE (WITH ... AS)
-- 3. EXISTS 检查存在性 → 比 IN 更适合大表

-- 子查询版本
SELECT title FROM books
WHERE author_id IN (SELECT id FROM authors WHERE country = '中国');

-- JOIN 版本（推荐）
SELECT b.title
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE a.country = '中国';

-- CTE 版本（可读性好）
WITH chinese_authors AS (
  SELECT id FROM authors WHERE country = '中国'
)
SELECT b.title
FROM books b
JOIN chinese_authors ca ON b.author_id = ca.id;

2.7 SQL 进阶速查表

┌──────────────────────────────────────────────────────────────────┐
│                    SQL 进阶命令速查表                              │
├──────────────────┬───────────────────────────────────────────────┤
│                  │                                               │
│  聚合函数         │  COUNT(*) / COUNT(col)     计数               │
│                  │  SUM(col)                  求和               │
│                  │  AVG(col)                  平均值             │
│                  │  MAX(col) / MIN(col)       最大/最小          │
│                  │                                               │
├──────────────────┼───────────────────────────────────────────────┤
│                  │                                               │
│  分组             │  GROUP BY col              分组               │
│                  │  HAVING condition          分组后过滤          │
│                  │                                               │
├──────────────────┼───────────────────────────────────────────────┤
│                  │                                               │
│  连接             │  INNER JOIN ... ON         内连接             │
│                  │  LEFT JOIN ... ON          左外连接            │
│                  │  RIGHT JOIN ... ON         右外连接            │
│                  │  CROSS JOIN                笛卡尔积            │
│                  │  UNION / UNION ALL         合并结果集          │
│                  │                                               │
├──────────────────┼───────────────────────────────────────────────┤
│                  │                                               │
│  子查询           │  WHERE col IN (SELECT ...) 列子查询           │
│                  │  WHERE EXISTS (SELECT ...) 存在性检查          │
│                  │  FROM (SELECT ...) AS t    表子查询            │
│                  │  WITH cte AS (SELECT ...)  CTE 公用表达式      │
│                  │                                               │
├──────────────────┼───────────────────────────────────────────────┤
│                  │                                               │
│  窗口函数         │  ROW_NUMBER() OVER (...)   行号               │
│                  │  RANK() / DENSE_RANK()     排名               │
│                  │  LAG() / LEAD()            前/后行值           │
│                  │  SUM() OVER (ORDER BY ...) 累计求和            │
│                  │  NTILE(n) OVER (...)       等分桶             │
│                  │  PARTITION BY              窗口分组            │
│                  │                                               │
└──────────────────┴───────────────────────────────────────────────┘

📝 学习建议：本阶段的核心是 JOIN 多表查询和窗口函数。建议先熟练掌握 INNER JOIN 和 LEFT JOIN，然后重点攻克窗口函数（ROW_NUMBER、RANK、LAG 是面试高频考点）。推荐在 LeetCode 上刷 20-30 道数据库题来巩固。

第二阶段：SQL 进阶查询 ​

2.1 聚合与分组 ​

2.1.1 聚合函数 ​

2.1.2 GROUP BY 分组查询 ​

2.1.3 HAVING 过滤分组结果 ​

2.2 多表查询（JOIN） ​

2.2.1 JOIN 类型概览 ​

2.2.2 INNER JOIN（内连接） ​

2.2.3 LEFT JOIN（左外连接） ​

2.2.4 RIGHT JOIN（右外连接） ​

2.2.5 自连接 ​

2.2.6 UNION 与 UNION ALL ​

2.3 子查询 ​

2.3.1 子查询类型与示例 ​

2.3.2 EXISTS 与 NOT EXISTS ​

2.3.3 相关子查询 vs 非相关子查询 ​

2.4 常用函数 ​

2.4.1 字符串函数 ​

2.4.2 数值函数 ​

2.4.3 日期时间函数 ​

2.4.4 流程控制函数 ​

2.4.5 窗口函数（MySQL 8.0+） ​

2.5 实践 Demo ​

Demo 1：销售数据分析 ​

Demo 2：窗口函数综合练习 ​

2.6 常见问题 QA ​

Q1: GROUP BY 报错 — sql_mode=only_full_group_by ​

Q2: NULL 值的注意事项 ​

Q3: JOIN 时出现笛卡尔积（数据量暴增） ​

Q4: 子查询和 JOIN 怎么选？ ​

2.7 SQL 进阶速查表 ​

第二阶段：SQL 进阶查询

2.1 聚合与分组

2.1.1 聚合函数

2.1.2 GROUP BY 分组查询

2.1.3 HAVING 过滤分组结果

2.2 多表查询（JOIN）

2.2.1 JOIN 类型概览

2.2.2 INNER JOIN（内连接）

2.2.3 LEFT JOIN（左外连接）

2.2.4 RIGHT JOIN（右外连接）

2.2.5 自连接

2.2.6 UNION 与 UNION ALL

2.3 子查询

2.3.1 子查询类型与示例

2.3.2 EXISTS 与 NOT EXISTS

2.3.3 相关子查询 vs 非相关子查询

2.4 常用函数

2.4.1 字符串函数

2.4.2 数值函数

2.4.3 日期时间函数

2.4.4 流程控制函数

2.4.5 窗口函数（MySQL 8.0+）

2.5 实践 Demo

Demo 1：销售数据分析

Demo 2：窗口函数综合练习

2.6 常见问题 QA

Q1: `GROUP BY` 报错 — `sql_mode=only_full_group_by`

Q2: `NULL` 值的注意事项

Q3: JOIN 时出现笛卡尔积（数据量暴增）

Q4: 子查询和 JOIN 怎么选？

2.7 SQL 进阶速查表