3.2 数据操作与查询

3.2 数据操作与查询

本节摘要：DML 与 DQL 覆盖日常百分之九十的语句量。本节讲增删改的批量写法与安全边界、SELECT 的逻辑执行顺序，以及最常用函数的取用要点。位置：结构建好之后的日常主力，所有性能讨论（第 4、5 章）都以这里的语句为分析对象。

先看逻辑执行顺序，再写查询

很多人写了几年 SQL 也说不清一条 SELECT 是怎么被"读"的。MySQL 的逻辑执行顺序是：FROM 确定数据源 → JOIN 连表 → WHERE 逐行过滤 → GROUP BY 分组 → HAVING 过滤组 → SELECT 计算投影 → ORDER BY 排序 → LIMIT 截断。这个顺序解释了三个高频困惑：WHERE 里不能用 SELECT 的别名（因为 WHERE 先执行）；HAVING 才能引用聚合结果（因为分组在投影之前）；LIMIT 前 ORDER BY 已生效（所以分页是稳定的排序后截断）。评审会上看懂一条复杂查询，第一步就是按这个顺序把语句"翻译"回执行步骤。

DML 三件套与批量写法

单行插入没什么可讲，值得讲的是批量。逐条插入与多值批量插入的网络往返次数相差一个数量级：

-- 逐条插入：10 次 round trip，每条各有事务开销
INSERT INTO order_item (order_id, product_id, quantity, deal_price)
VALUES (1001, 301, 1, 199.00);

-- 批量插入：1 次 round trip
INSERT INTO order_item (order_id, product_id, quantity, deal_price)
VALUES
    (1001, 302, 2, 89.50),
    (1001, 303, 1, 459.00),
    (1001, 305, 3, 25.00);

-- 也可以从已有数据批量生成
INSERT INTO orders (customer_id, amount, status)
SELECT customer_id, total_refund, 5
FROM refund_batch WHERE imported = 0;
​

批量也有上限：单条语句过长会撑爆 max_allowed_packet，一般几百到一两千行一批，分批提交。UPDATE 和 DELETE 的工程要点是永远带索引条件、永远先 SELECT 验证：

-- 先看影响范围
SELECT COUNT(*) FROM orders WHERE status = 1 AND created_at < '2024-01-01';
-- 确认无误后再删，并分批
DELETE FROM orders
WHERE status = 1 AND created_at < '2024-01-01'
LIMIT 5000;
-- 分批循环执行，直到影响行数为 0
​

大事务一次性删几百万行的后果：undo log 膨胀、主从延迟爆炸、锁持有时间过长阻塞业务。分批加 LIMIT 是标准答案。另外开启 sql_safe_updates 模式后，无 WHERE 或无索引条件的 UPDATE/DELETE 会被直接拒绝——用模式约束代替人的自觉，这在团队协作里非常值。

演练：一次被回滚救回的批量改价

背景：运营要把某分类商品提价百分之五。操作：先包事务，再验证，后提交。

START TRANSACTION;
UPDATE product
SET list_price = list_price * 1.05
WHERE category_id = 12;
-- 结果：Query OK, 342 rows affected

-- 验证：抽查三条
SELECT product_id, list_price FROM product
WHERE category_id = 12 LIMIT 3;
-- 发现价格变成 105.525 —— 三位小数超出 DECIMAL(10,2) 定义？

SELECT list_price, list_price * 1.05 AS calc FROM product LIMIT 2;
-- 原来是 DECIMAL 运算保留了额外精度，ROUND 后落库
UPDATE product SET list_price = ROUND(list_price * 1.05, 2)
WHERE category_id = 12;
COMMIT;
​

解读：如果没有事务包裹，第一次错误的 UPDATE 已经生效，342 个价格全乱；有事务，一条 ROLLBACK 就回到原点。任何影响行数超过"心里有数"范围的写操作，都该在事务里先跑一遍再定稿。变式：跨表级联改价（商品表加订单明细快照校验）则要在同一事务里按依赖顺序执行，这引出下一节的事务控制。

常用函数的取用边界

聚合五件套 COUNT、SUM、AVG、MIN、MAX 配 GROUP BY 是报表主力；注意 COUNT(*) 与 COUNT(列) 语义不同——前者数行数，后者数该列非 NULL 的数量。字符串函数里 CONCAT 拼接、SUBSTRING 截取最常用；日期函数里 DATE_FORMAT 格式化、DATE_ADD 偏移、DATEDIFF 差值三件套覆盖大部分需求。条件聚合是必会技巧：

SELECT
    customer_id,
    COUNT(*)                                AS total_orders,
    SUM(status = 5)                         AS refund_orders,
    ROUND(AVG(amount), 2)                   AS avg_amount
FROM orders
GROUP BY customer_id
HAVING total_orders >= 10;
​

一行里给函数喂了两个关键概念：SUM(status = 5) 利用布尔表达式当 0/1 参与求和，等价于带条件的统计；HAVING 引用了 SELECT 别名（MySQL 允许这一扩展，标准 SQL 不允许，跨库时别依赖）。

易错点与评审清单

• 在索引列上包函数：WHERE DATE(created_at) = '2024-06-01' 让索引失效，改写成范围条件 created_at >= '2024-06-01' AND created_at < '2024-06-02'，这是第 5 章的主题，先在这里立下规矩；
• UPDATE 忘 WHERE：全表被改，幸好有备份。安全模式加双人复核，别赌手稳；
• 隐式类型转换：WHERE phone = 13800001111（数字字面量对 CHAR 列）触发隐式转换导致索引失效还可能查错——字符串列一律用字符串字面量；
• LIMIT 无 ORDER BY：每次返回的行可能不同，分页翻着翻着重复或丢行。

要点回顾：逻辑执行顺序是读懂查询的钥匙；批量写省往返但分批控量；写操作先 SELECT 后执行、大影响包事务；函数写在索引列上是自毁索引。下一节把多步操作放进事务，并聊聊权限怎么给。