DevNull 知识库

Appearance

05｜查询进阶：连接、聚合、函数 ​

上一篇的查询都是单表——SELECT ... FROM servers WHERE ...。但真实业务里，数据分散在多张表：服务器在一张表、负责人在另一张表、监控指标又在别的表。查"这台服务器是谁负责的"，得把两张表关联起来。

本篇讲查询进阶：JOIN 连接多表、GROUP BY 分组统计、常用函数、子查询。

一、JOIN：关联多张表 ​

准备两张表。servers 记服务器，owners 记负责人：

sql
-- servers 表
id | hostname | owner_id | env
1  | web-01   | 100      | prod
2  | web-02   | 101      | prod

-- owners 表
owner_id | name   | email
100      | 张三    | zhangsan@xx
101      | 李四    | lisi@xx

servers.owner_id 指向 owners.owner_id。查"每台服务器是谁负责的"：

sql
SELECT s.hostname, o.name AS owner, o.email
FROM servers s
JOIN owners o ON s.owner_id = o.owner_id;
text
hostname | owner | email
web-01   | 张三  | zhangsan@xx
web-02   | 李四  | lisi@xx

s 和 o 是表的别名（FROM servers s），写起来短。JOIN ... ON 指定怎么关联——这里用 owner_id 相等。

三种 JOIN 的区别 ​

sql
-- INNER JOIN（默认 JOIN）：只返回两边都能匹配上的
FROM servers s JOIN owners o ON s.owner_id = o.owner_id

-- LEFT JOIN：左表全要，右表匹配不上的填 NULL
FROM servers s LEFT JOIN owners o ON s.owner_id = o.owner_id

-- RIGHT JOIN：右表全要，左表匹配不上的填 NULL（用得少）

LEFT JOIN 最常用——以左表为主，右表有的就关联，没有的字段是 NULL。比如查所有服务器及其负责人，有的服务器没分配负责人也要列出来，用 LEFT JOIN。

sql
SELECT s.hostname, o.name
FROM servers s
LEFT JOIN owners o ON s.owner_id = o.owner_id;
text
hostname | name
web-01   | 张三
web-02   | 李四
db-01    | NULL    -- 这台没分配负责人，LEFT JOIN 保留这行

二、GROUP BY：分组统计 ​

统计每个环境有多少台服务器：

sql
SELECT env, COUNT(*) AS cnt
FROM servers
GROUP BY env;
text
env  | cnt
prod | 15
test | 8
dev  | 3

GROUP BY env 按环境分组，COUNT(*) 算每组多少行。

常用聚合函数：

函数作用
COUNT(*)行数
SUM(字段)求和
AVG(字段)平均
MAX(字段)最大
MIN(字段)最小

每个环境的服务器数量 + 状态分布：

sql
SELECT env, status, COUNT(*) AS cnt
FROM servers
GROUP BY env, status
ORDER BY env, cnt DESC;

按两个字段分组（env + status），看每个环境各状态有多少台。

HAVING：过滤分组结果 ​

WHERE 在分组前过滤行，HAVING 在分组后过滤组。找服务器数超过 5 的环境：

sql
SELECT env, COUNT(*) AS cnt
FROM servers
GROUP BY env
HAVING cnt > 5;

WHERE 过滤原始行（比如 WHERE status = 1），HAVING 过滤聚合后的结果（比如 HAVING COUNT(*) > 5）。两者区别：WHERE 不能用聚合函数，HAVING 可以。

三、常用函数 ​

字符串 ​

sql
-- 拼接
SELECT CONCAT(hostname, ':', ip_addr) AS node FROM servers;

-- NULL 处理：remark 为 NULL 时显示空串
SELECT hostname, IFNULL(remark, '') AS remark FROM servers;

IFNULL(字段, 默认值) 是高频函数——查出来的 NULL 在应用里处理麻烦，这里直接转成空串或指定值。

时间 ​

sql
SELECT NOW();                  -- 当前时间
SELECT CURRENT_DATE();          -- 当前日期
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month FROM servers;   -- 格式化

按月统计创建量：

sql
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS cnt
FROM servers
GROUP BY month
ORDER BY month;

条件转换 ​

sql
SELECT hostname,
  CASE status
    WHEN 1 THEN 'online'
    ELSE 'offline'
  END AS status_name
FROM servers;

CASE WHEN 把状态码转成可读的字符串。

JSON 函数 ​

8.0+ 支持 JSON 类型。如果 extra 字段是 JSON：

sql
-- 取 JSON 里的某个 key
SELECT hostname, JSON_EXTRACT(extra, '$.role') AS role
FROM servers;

-- 简写（8.0+）
SELECT hostname, extra->'$.role' AS role FROM servers;

-> 是 JSON_EXTRACT 的简写。但前面说过，频繁查询的 JSON key 应该拆成独立列——JSON 函数查询慢且不好建索引。

四、子查询 ​

查询里嵌查询。找负责人是"张三"的服务器：

sql
SELECT hostname FROM servers
WHERE owner_id = (
  SELECT owner_id FROM owners WHERE name = '张三'
);

子查询先算出张三的 owner_id，外层用这个值查。简单场景这么写清晰，但关联子查询（外层每行都跑一次子查询）可能慢：

sql
-- 关联子查询（每行都跑一次，慢）
SELECT hostname FROM servers s
WHERE EXISTS (
  SELECT 1 FROM owners o WHERE o.owner_id = s.owner_id AND o.name LIKE '张%'
);

能用 JOIN 替代的子查询，优先 JOIN——通常更快：

sql
-- 等价的 JOIN 写法，一般更快
SELECT s.hostname
FROM servers s
JOIN owners o ON s.owner_id = o.owner_id
WHERE o.name LIKE '张%';

五、查询写法原则 ​

  1. 能 JOIN 别用子查询——JOIN 通常更优，优化器更好处理
  2. 聚合先过滤——WHERE 在 GROUP BY 前减少参与聚合的行数
  3. 分页用 LIMIT 加 OFFSET——大表深翻页（OFFSET 几百万）慢，用"记住上次最大 id"的方式替代
  4. 少用 SELECT *——取需要的字段
  5. 避免 SELECT 里做计算——WHERE 字段+1 = 2 会让索引失效，写成 WHERE 字段 = 1

查询进阶之后 ​

能关联多表、能分组统计、能用函数了。但这些查询快不快，取决于有没有索引、索引建得对不对。同样一条查询，有索引毫秒返回，没索引扫全表几秒甚至几十秒。下一篇讲索引原理——B+Tree、最左前缀、为什么索引会失效。