SQL 语法详解

SQL 分四类

写 SQL 之前先认清手上的语句属于哪一类，出问题才知道去哪查：

类别	全称	代表语句	一句话说明
DDL	Data Definition Language	CREATE、ALTER、DROP、TRUNCATE	改结构，隐式提交，不可回滚
DML	Data Manipulation Language	INSERT、UPDATE、DELETE	改数据，可回滚，产生 undo 和 binlog
DQL	Data Query Language	SELECT	只读，运维日常的大头
DCL	Data Control Language	GRANT、REVOKE	权限管理，立即生效

本篇讲前三大类怎么用，DCL 在《安装与快速上手》里已经走过一遍。

DDL：建表与改表

常用类型怎么选

类型选错，后面全是坑。几条经验：

场景	推荐	说明
主键 ID	BIGINT UNSIGNED	INT 上限约 21 亿，大表迟早不够，别赌
字符串	VARCHAR(n)	n 按业务上限定，改长度大表要重建
时间	DATETIME	占 5 字节、范围到 9999 年；TIMESTAMP 只到 2038 年，且会跟随时区转换
金额	DECIMAL(10,2)	不要用 FLOAT/DOUBLE，浮点数存金额会丢精度
状态	TINYINT + 注释	加 COMMENT 说明枚举含义，否则三个月后没人看得懂

完整建表示例

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  user_id     BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  order_no    VARCHAR(32)     NOT NULL COMMENT '订单号',
  amount      DECIMAL(10,2)   NOT NULL DEFAULT 0.00 COMMENT '金额',
  status      TINYINT         NOT NULL DEFAULT 0 COMMENT '0待付 1已付 2已发 3完成 4取消',
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_created (user_id, created_at)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '订单表';

ALTER 改表

-- 加列
ALTER TABLE orders ADD COLUMN remark VARCHAR(255) NOT NULL DEFAULT '' AFTER amount;

-- 改列类型
ALTER TABLE orders MODIFY COLUMN remark VARCHAR(500) NOT NULL DEFAULT '';

-- 加索引
ALTER TABLE orders ADD INDEX idx_status (status);

8.0 有两个值得知道的特性：Instant Add Column（8.0.12+）在表末尾加列只改元数据、秒级完成；Online DDL 支持 ALGORITHM=INPLACE, LOCK=NONE 不阻塞读写，但改列类型、改字符集这类操作仍然要重建表。

-- 明确要求不锁表，做不到就报错，而不是悄悄锁表
ALTER TABLE orders ADD INDEX idx_created (created_at), ALGORITHM = INPLACE, LOCK = NONE;

DROP 与 TRUNCATE

TRUNCATE TABLE order_log;   -- 清空表、重置自增，DDL，不可回滚
DROP TABLE order_log_bak;   -- 连表结构一起删

DDL 没有后悔药

DROP 和 TRUNCATE 都是 DDL，不走事务、不能回滚，TRUNCATE 之后 undo log 里也救不回来。恢复只能靠备份 + binlog（见《备份与恢复》）。执行前确认三件事：连的是不是生产库、表名对不对、有没有最近的备份。

DML：增删改的正确姿势

-- 插入
INSERT INTO orders (user_id, order_no, amount, status)
VALUES (1001, 'SN20240101001', 199.00, 1);

-- 批量插入：一条语句多行，比循环单条快一个数量级
INSERT INTO orders (user_id, order_no, amount, status) VALUES
  (1002, 'SN20240101002', 88.00, 0),
  (1003, 'SN20240101003', 129.00, 0);

-- 更新：必须带 WHERE
UPDATE orders SET status = 2 WHERE order_no = 'SN20240101001' AND status = 1;

-- 删除
DELETE FROM orders WHERE id = 1003;

批量操作必须分批。一次 UPDATE/DELETE 几百万行会带来三个问题：事务太大回滚代价高、持有锁时间长阻塞业务、binlog 暴涨把主从延迟打飞。标准做法是分小批 + 间隔：

-- 每次只处理 1000 行，循环执行直到影响行数为 0
DELETE FROM order_log
WHERE created_at < '2024-01-01'
LIMIT 1000;

应用层循环执行上面这条，每批之间 sleep 一下，给主从复制喘息时间。生产执行任何批量变更前，先用同样的 WHERE 跑一遍 SELECT COUNT(*) 确认影响范围。

SELECT 基础与执行顺序

写复杂查询之前，先记住 SQL 的逻辑执行顺序——它不是按书写顺序跑的：

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

理解这个顺序能解释很多「诡异」现象：

• WHERE 在 SELECT 之前执行，所以 WHERE 里不能用 SELECT 起的别名，要用得重写表达式。
• HAVING 在分组之后执行，所以它能用聚合函数，WHERE 不能。
• ORDER BY 在 SELECT 之后，所以排序可以用别名。

JOIN 详解

三种 JOIN 的语义差异：

JOIN 类型	结果集	典型场景
INNER JOIN	只保留两边都匹配的行	查有关联数据的结果
LEFT JOIN	左表全保留，右表没匹配补 NULL	查「全部用户及其订单，没下过单的也要」
RIGHT JOIN	右表全保留	和 LEFT 对称，实际很少用，通常调个方向写成 LEFT

-- INNER JOIN：只返回有订单的用户
SELECT u.user_id, u.nickname, o.order_no, o.amount
FROM user_profile u
JOIN orders o ON o.user_id = u.user_id;

-- LEFT JOIN：所有用户，没下过单的订单字段为 NULL
SELECT u.user_id, u.nickname, COUNT(o.id) AS order_cnt
FROM user_profile u
LEFT JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id, u.nickname;

两个坑：

• 忘写 ON 条件变成笛卡尔积：10 万行的表 JOIN 10 万行的表，结果是 100 亿行，直接把库拖死。写完 JOIN 先检查 ON。
• 小表驱动大表：驱动表（外层循环）越小越好。MySQL 优化器一般自己选，但自己写 SQL 时心里要有数——用小结果集去驱动大表的索引查找。

子查询与常用写法

-- WHERE 子查询：查消费额前 10 的用户
SELECT * FROM user_profile
WHERE user_id IN (
  SELECT user_id FROM orders GROUP BY user_id ORDER BY SUM(amount) DESC LIMIT 10
);

-- FROM 派生表：先聚合再关联
SELECT u.nickname, t.total
FROM user_profile u
JOIN (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id) t
  ON t.user_id = u.user_id;

-- EXISTS：判断是否存在
SELECT * FROM user_profile u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id AND o.status = 0);

EXISTS vs IN 的经验法则：外表小内表大用 IN，外表大内表小用 EXISTS。另外 NOT IN 遇到子查询结果含 NULL 会返回空集，这是经典坑，改用 NOT EXISTS 更稳。

GROUP BY 与聚合

五个聚合函数：COUNT、SUM、AVG、MAX、MIN。配合 HAVING 过滤分组结果：

-- 每个用户的订单数和总金额，只保留消费超 500 的
SELECT user_id,
       COUNT(*)      AS order_cnt,
       SUM(amount)   AS total,
       AVG(amount)   AS avg_amount,
       MAX(created_at) AS last_order_at
FROM orders
WHERE status IN (1, 2, 3)
GROUP BY user_id
HAVING total > 500
ORDER BY total DESC;

8.0 默认开启 ONLY_FULL_GROUP_BY：SELECT 里的非聚合列必须出现在 GROUP BY 里，否则报 ERROR 1055。这是防止返回不确定的值——比如按 user_id 分组却 SELECT nickname，MySQL 不知道该给你哪一行的 nickname。正确做法是把函数依赖的列补进 GROUP BY，或者用 ANY_VALUE() 显式告诉它「随便来一行」。不要图省事去关这个 SQL mode。

排序与分页

-- 多列排序：先按状态，同状态按时间倒序
SELECT id, order_no, status, created_at
FROM orders
ORDER BY status ASC, created_at DESC;

-- 普通分页：前几页没问题
SELECT id, order_no FROM orders ORDER BY id LIMIT 0, 20;

深分页为什么慢：LIMIT 100000, 10 的意思是「扫描前 100010 行，扔掉前 100000 行」。offset 越大，白扫的行越多。解决办法是游标式分页——记住上一页最后一条的 id，下一页直接从它之后开始：

-- 第一页
SELECT id, order_no FROM orders ORDER BY id LIMIT 20;

-- 后续页：把上一页最大 id 带过来，无论翻到多深都是常数时间
SELECT id, order_no FROM orders WHERE id > 12345678 ORDER BY id LIMIT 20;

游标分页的代价

游标分页不能跳页，只适合「下一页」式的场景（App 下拉加载、日志流）。后台管理系统要跳到第 500 页的场景，得用延迟关联或干脆限制最大页数，具体优化思路见《索引与查询优化》。

常用函数

字符串函数

SELECT CONCAT(nickname, '-', city) FROM user_profile;          -- 拼接
SELECT SUBSTRING(order_no, 1, 8) FROM orders;                  -- 截取
SELECT REPLACE(order_no, 'SN', 'NO') FROM orders;              -- 替换
SELECT LENGTH(nickname), CHAR_LENGTH(nickname) FROM user_profile; -- 字节数 vs 字符数

日期函数

SELECT NOW();                                              -- 当前时间
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');               -- 格式化
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);                    -- 7 天后
SELECT DATEDIFF('2024-06-01', '2024-01-01');               -- 相差天数
SELECT TIMESTAMPDIFF(HOUR, created_at, NOW()) FROM orders; -- 相差小时

条件函数

-- IF：二选一
SELECT order_no, IF(status = 0, '待支付', '已处理') AS status_text FROM orders;

-- CASE WHEN：多分支
SELECT order_no,
       CASE status
         WHEN 0 THEN '待支付'
         WHEN 1 THEN '已支付'
         WHEN 4 THEN '已取消'
         ELSE '其他'
       END AS status_text
FROM orders;

-- COALESCE / IFNULL：NULL 兜底
SELECT COALESCE(remark, '无备注') FROM orders;       -- 多个参数取第一个非 NULL
SELECT IFNULL(nickname, '匿名用户') FROM user_profile;

窗口函数简介

窗口函数是 8.0 才有的能力（5.7 没有），它在一组行上计算但不折叠行数。经典场景：取每组最新一条——比如每个用户最近一次下单：

SELECT user_id, order_no, amount, created_at
FROM (
  SELECT user_id, order_no, amount, created_at,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

PARTITION BY user_id 把数据按用户分组，ORDER BY created_at DESC 组内按时间倒序，ROW_NUMBER() 给组内每行编号，取 rn = 1 就是每组最新一条。同类函数还有 RANK()（并列排名跳号）、DENSE_RANK()（并列排名不跳号）、LAG()/LEAD()（取前后行的值，算环比很方便）。

字符集与排序规则

utf8mb4 是唯一正确答案。MySQL 里的 utf8 是阉割版，最多 3 字节，存不了 emoji 和部分生僻字，插进去直接报错或截断。utf8mb4 才是真正的 UTF-8。

-- 建库、建表都显式指定，别依赖默认值
CREATE DATABASE shop
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

排序规则（collation）影响两件事：大小写敏感和比较规则。utf8mb4_0900_ai_ci 里的 ci 表示大小写不敏感（'ABC' = 'abc' 成立），ai 表示不区分重音。8.0 默认用它，比老的 utf8mb4_general_ci 更准确。如果你需要区分大小写的比较（比如某些校验码），用 utf8mb4_bin。

小结

• SQL 分 DDL/DML/DQL/DCL；DDL 隐式提交不可回滚，DROP/TRUNCATE 前想清楚。
• 类型选型：金额用 DECIMAL、时间用 DATETIME、主键用 BIGINT UNSIGNED。
• 批量写必须分批，大事务会撑爆 undo、阻塞业务、打飞主从延迟。
• 记住逻辑执行顺序（FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT），复杂查询才不会写错。
• 深分页用游标式分页；每组取最新一条用窗口函数；字符集一律 utf8mb4。

会写 SQL 只是第一步，写得快不快要看索引和执行计划。下一篇《索引与查询优化》讲索引设计原则和 EXPLAIN 怎么看。