🗄️ 第04章 · 查询 SQL

SELECT WHERE JOIN GROUP BY

查询 SQL¶

说一千道一万，数据库的“读”才是业务命脉——排行榜、列表页、报表、接口，背后全是 SELECT。本章从单表查询讲到多表联结，把最常用的取数姿势一次说清，全程用服务器上真实的 bands / stories / characters 表演示。

SELECT 的执行顺序¶

别看 SELECT 写起来是“先 select 再 from”，它实际解析顺序恰好相反，记住这个顺序能帮你理解为什么有的列不能用在 WHERE 里、为什么别名不能用在 WHERE ：

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

GROUP BY 之后才能用聚合，HAVING 在分组后过滤，WHERE 在分组前过滤。这就是“为什么 WHERE 里不能写聚合函数”。

基础查询：选列、筛选、排序、分页¶

-- 查询全部乐队
SELECT * FROM bands;

-- 只选需要的列，并起别名
SELECT id AS 编号, name AS 乐队名 FROM bands;

-- WHERE 条件筛选（band_id=1，即 Poppin'Party 的剧情）
SELECT id, title FROM stories WHERE band_id = 1;

-- 多个条件：逻辑组合
SELECT id, name FROM bands WHERE id BETWEEN 1 AND 5 AND cls LIKE 'poppin%';

-- 排序 + 分页：按 id 倒序，取前 3 条
SELECT id, name FROM bands ORDER BY id DESC LIMIT 3;

-- 跳过前 5 条、取 2 条（分页第 2 页）
SELECT id, name FROM bands ORDER BY id LIMIT 5, 2;
-- LIMIT 偏移, 数量 —— 前面是跳过多少行，后面是取多少行

LIMIT 记法：LIMIT 数量 或 LIMIT 偏移, 数量（第二种是先跳过多再取多少）。分页时偏移越大越慢，深分页是老生常谈的性能坑。

聚合与分组¶

GROUP BY 把相同的值归成一组，配合聚合函数算汇总。注意：SELECT 里出现的非聚合列，必须都在 GROUP BY 里，否则在 MySQL 严格模式（ONLY_FULL_GROUP_BY）下会直接报错。

-- 每个乐队有多少剧情（按 band_id 分组计数）
SELECT band_id, COUNT(*) AS 剧情数
  FROM stories GROUP BY band_id;

-- 分组后再过滤：只看剧情数 ≥ 2 的乐队（HAVING 在分组后，WHERE 做不到）
SELECT band_id, COUNT(*) AS 剧情数
  FROM stories GROUP BY band_id HAVING 剧情数 >= 2;

-- 统计全库概况：总数、最大、最小、平均
SELECT COUNT(*)      AS 总数,
       MAX(band_id)  AS 最大乐队号,
       MIN(band_id)  AS 最小乐队号,
       AVG(band_id)  AS 平均乐队号
  FROM stories;

易错点：HAVING 和 WHERE 别混用。WHERE 从一开始就排除行、速度更快；HAVING 是分组之后才过滤。能用 WHERE 的过滤条件，绝不要丢到 HAVING 里。

多表联结 JOIN¶

真实业务的数据分散在多个表里，靠外键把它们串起来。JOIN 就是把两张表的记录按关联条件“拼接”成一行。最常见的是 INNER JOIN（只留两边都匹配的）和 LEFT JOIN（保留左边全部）。

-- INNER JOIN：剧情 + 乐队名，两边都匹配才会出现
SELECT s.title AS 剧情标题, b.name AS 乐队名
  FROM stories s
  JOIN bands b ON s.band_id = b.id
 WHERE b.id <= 5;

-- LEFT JOIN：列出所有乐队，即使没有剧情也出现（剧情列为 NULL）
SELECT b.name AS 乐队名, COUNT(s.id) AS 剧情数
  FROM bands b
  LEFT JOIN stories s ON s.band_id = b.id
 GROUP BY b.id, b.name
 ORDER BY 剧情数 DESC;

易错点：ON 的条件（关联键）一定要写对，否则会得到“笛卡尔积”——两张表行数相乘的爆炸结果。JOIN 时顺手比对一下 band_id 是否两表都建了索引，否则大数据量下会全表扫描、慢到飞起。

子查询¶

一个查询的结果作为另一个查询的输入，就是子查询。它在需要“二次过滤”时很好用：

-- 找出“有剧情的乐队”，用 IN 子查询
SELECT id, name FROM bands
 WHERE id IN (SELECT DISTINCT band_id FROM stories);

-- 派生表：把聚合结果当成一张临时表再查
SELECT band_id, 剧情数 FROM (
    SELECT band_id, COUNT(*) AS 剧情数 FROM stories GROUP BY band_id
) AS t WHERE 剧情数 > 1;

案例场景：真实业务报表¶

假设要为 bangdream 库出一份“每支乐队角色数”的报表，把 bands 与 characters 联结、分组、排序、取前几：

SELECT b.name  AS 乐队名,
       COUNT(c.id) AS 角色数
  FROM bands b
  LEFT JOIN characters c ON c.band_id = b.id
 GROUP BY b.id, b.name
 ORDER BY 角色数 DESC;

查询结果要拿到服务器外使用时，用客户端导出成文件：

# 导出查询结果到 CSV（不打印列标题）
mysql --defaults-extra-file=<(printf '[client]\npassword=%s\n' \
  "$(grep bangdream_password /root/.deploy/db_passwords | cut -d= -f2)") \
  -u bangdream bangdream -N -e \
  "SELECT b.name, COUNT(c.id) FROM bands b LEFT JOIN characters c ON c.band_id=b.id GROUP BY b.id,b.name;" \
  > /tmp/band_roster.tsv

查询子句速查¶

子句	作用	示例
`SELECT ...`	选择列、聚合	`SELECT name, COUNT(*)`
`FROM ...`	指定数据源（可 JOIN）	`FROM bands b JOIN stories s ...`
`WHERE ...`	分组/聚合前过滤	`WHERE band_id = 1`
`GROUP BY ...`	分组	`GROUP BY band_id`
`HAVING ...`	分组后过滤	`HAVING COUNT(*) >= 2`
`ORDER BY ...`	排序	`ORDER BY id DESC`
`LIMIT ...`	分页限制	`LIMIT 0, 20`