MySQL 查询语法与函数

按“筛选行 → 计算与汇总 → 连接多表 → 排序分页”的顺序查阅。建库、建表与事务入门见 MySQL 入门课程，这里集中整理查询场景。

准备查询样例

在新建的专用练习库中按顺序执行；同名练习库已存在时先检查旧数据，不在业务库创建样例表。示例字段统一为 users(id, name, age, email) 和 orders(id, customer_id, amount, status, created_at)，避免同一篇文章不断切换同义字段。

CREATE DATABASE mysql_query_practice CHARACTER SET utf8mb4;
USE mysql_query_practice;
CREATE TABLE users (
  id INT PRIMARY KEY, name VARCHAR(50), age INT, email VARCHAR(100)
);
CREATE TABLE orders (
  id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2),
  status VARCHAR(20), created_at DATE
);
INSERT INTO users VALUES
  (1, 'Lin', 25, 'lin@example.com'),
  (2, 'Chen', 30, NULL), (3, 'Wu', 22, 'wu@example.com');
INSERT INTO orders VALUES
  (101, 1, 800, 'completed', '2024-01-01'),
  (102, 1, 500, 'pending', '2024-01-02'),
  (103, 2, 200, 'completed', '2024-02-01');

一条 SELECT 的组成

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY total_amount DESC, customer_id ASC
LIMIT 10;

WHERE 过滤参与统计的行，GROUP BY 确定统计粒度，HAVING 过滤统计结果，最后排序与限制条数。这是理解查询语义的顺序，不是对优化器物理执行顺序的承诺。

筛选、运算与模糊匹配

需求	表达式	边界
比较、范围	age >= 18、age BETWEEN 20 AND 30	BETWEEN 包含两端
枚举值	status IN ('pending', 'completed')	注意列值及集合中的 NULL
空值	email IS NULL / IS NOT NULL	不写 email = NULL
多条件	AND、OR、NOT	混用时加括号明确意图
前缀或包含	LIKE 'L%' / LIKE '%in%'	% 匹配任意长度，_ 匹配一个字符
正则	REGEXP '^L'	是否区分大小写受字符集与排序规则等影响
算术	+ - * / %	除法、空值传播及精度要符合字段语义

SELECT id, name, age + 1 AS next_age
FROM users
WHERE age BETWEEN 20 AND 30
  AND email IS NOT NULL
  AND (name LIKE 'L%' OR name LIKE 'W%');

CASE 表达式与全文搜索

SELECT id, name,
  CASE WHEN age < 18 THEN '未成年'
       WHEN age < 65 THEN '成年'
       ELSE '老年' END AS age_group
FROM users;

查询中的 CASE ... END 是表达式，不是存储程序中的 CASE ... END CASE 语句。CONCAT、INSTR 和 SUBSTRING 也可组合查询条件，但对列使用函数或前导 % 可能影响索引使用，需看执行计划。

全文检索使用 MATCH(列...) AGAINST(...)，需要对应的全文索引及适用字段；不能把任意文本列上的 LIKE 直接换成它。该样例表不包含全文索引。

分组统计与 HAVING

先确定每行结果代表什么

按客户统计时，每行代表一个客户；按客户和月份统计时，每行代表一个“客户＋月份”。非聚合列应属于分组键或满足可判定的函数依赖，不从组内随便取一个值。

SELECT customer_id,
  DATE_FORMAT(created_at, '%Y-%m') AS order_month,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount,
  AVG(amount) AS average_amount
FROM orders
WHERE amount > 0
GROUP BY customer_id, DATE_FORMAT(created_at, '%Y-%m')
HAVING COUNT(*) >= 1
ORDER BY total_amount DESC, customer_id ASC;

常见聚合函数

函数	用途
COUNT(*)	行数；COUNT(email) 只计非空值
COUNT(DISTINCT customer_id)	去重后的非空客户数
SUM、AVG、MIN、MAX	总和、平均、最小、最大；通常忽略 NULL
VAR_POP / VAR_SAMP	总体 / 样本方差
STDDEV_POP / STDDEV_SAMP	总体 / 样本标准差
GROUP_CONCAT	组内拼接，可指定排序和分隔符；注意结果长度限制
BIT_AND、BIT_OR、BIT_XOR	按位聚合；只适用于定义清楚的位掩码

SUM(DISTINCT amount) 去重的是金额值，不是订单；两个不同订单金额相同时也只计一次，不能用于订单去重。

SELECT customer_id,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count,
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_amount,
  MAX(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS has_completed,
  GROUP_CONCAT(id ORDER BY id SEPARATOR ',') AS order_ids
FROM orders GROUP BY customer_id;

汇总行使用 MySQL 的 GROUP BY ... WITH ROLLUP，不混入其他数据库的 GROUP BY ROLLUP(...) 或 CUBE 写法；用 GROUPING() 区分汇总标记与真实空值。

连接表并保留未匹配记录

连接	返回哪些行
INNER JOIN	两侧满足连接条件的记录
LEFT JOIN	所有左侧记录，右侧未匹配列补 NULL
RIGHT JOIN	所有右侧记录，可交换表顺序改写为左连接
CROSS JOIN	两表记录的笛卡尔积

-- 包含没有订单的用户，COUNT(o.id) 不把空侧算成订单
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users AS u
LEFT JOIN orders AS o ON o.customer_id = u.id
GROUP BY u.id, u.name;

-- 仍保留没有已完成订单的用户
SELECT u.id, u.name, o.id AS order_id
FROM users AS u
LEFT JOIN orders AS o
  ON o.customer_id = u.id AND o.status = 'completed';

把 o.status = 'completed' 放到 WHERE 会排除外连接产生的空侧记录；先决定业务是否要保留这部分行。一对多连接后统计金额或数量，要检查是否因额外连接放大记录。

MySQL 没有原生 FULL OUTER JOIN。需要完整外连接时，使用左连接加上右侧未匹配记录的 UNION ALL；不要直接复制其他数据库的语法。

子查询与存在性判断

-- 标量子查询：金额高于整体平均值
SELECT id, amount FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- 相关子查询：至少有一笔已完成订单的用户
SELECT u.id, u.name FROM users AS u
WHERE EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = u.id AND o.status = 'completed'
);

-- 反向存在：完全没有订单的用户
SELECT u.id, u.name FROM users AS u
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = u.id);

标量子查询至多返回一行一列；多行匹配可用 IN 或存在性判断。NOT IN 遇到子查询中的 NULL 可能得到未知结果，判断“没有匹配记录”时优先明确空值语义。

派生表用于先汇总再查询，在 FROM (...) 后写别名。相关子查询会引用外层行，但不能简单断言“所有子查询都先执行”或“嵌套超过四层就慢”；优化器可能改写执行方式，用 EXPLAIN 验证实际计划。

排序与分页

SELECT id, name FROM users
ORDER BY age DESC, id ASC
LIMIT 10 OFFSET 20;

每页 10 条，第 3 页偏移为 20。排序字段值相同要增加唯一键作为稳定的次级排序条件；仅写 LIMIT 不保证稳定顺序。

• 偏移量：offset = (page - 1) * pageSize，先在应用中校验并计算，再绑定整数参数。
• 总页数：ceil(total / pageSize)；非负整数也可用整除 (total + pageSize - 1) / pageSize，不能在浮点除法中直接套用。
• 不把 LIMIT (page-1)*20,20 当作可直接执行的 MySQL SQL。
• 大偏移或频繁翻页的场景，可改为按有序唯一键继续读取；选择及边界见 主键分页实践。

常用函数速查

日期、时间与格式化

函数	用途或例子
CURRENT_DATE() / CURRENT_TIME() / CURRENT_TIMESTAMP()	当前日期 / 时间 / 日期时间
DATE(expr) / TIME(expr)	提取日期 / 时间部分
YEAR / MONTH / DAY / HOUR / MINUTE / SECOND	提取日期时间分量
DATEDIFF(a, b)	日期部分的天数差
TIMEDIFF(a, b)	时间差
DATE_FORMAT(created_at, '%Y-%m')	按年月格式化

字符串与字符长度

函数	用途
CONCAT	拼接；参数中有 NULL 时注意整体结果
SUBSTRING / INSTR	取子串 / 查找位置
LENGTH / CHAR_LENGTH	字节数 / 字符数，多字节中文不可混淆
LOWER / UPPER	大小写转换
TRIM / REPLACE	去两端空白 / 替换子串
STRCMP / REVERSE	字符串比较 / 反转
LPAD / RPAD	左 / 右填充到指定长度

数值处理

函数	用途
ABS / CEILING / FLOOR	绝对值 / 向上取整 / 向下取整
ROUND(x, d) / TRUNCATE(x, d)	舍入 / 截断到指定小数位
MOD / POWER / SQRT	取余 / 幂 / 平方根
EXP / LOG / LOG10	指数 / 自然对数 / 常用对数
RAND()	[0, 1) 的伪随机值

存储过程里的 IF、WHILE、REPEAT 和 LOOP 属于程序控制流，见 数据库编程，不与查询表达式混为一套语法。

检查查询结果与继续练习

用上面的数据检查：第一个汇总查询只返回客户 1、金额 1300；没有订单的用户是 Wu；左连接统计 Wu 的订单数应为 0。再增加空值、并列金额与重复关联记录，确认结果符合业务意图。

• MySQL 查询练习
• MySQL 8.4 JOIN 语法
• MySQL 8.4 聚合函数