DML 操作

这一课你将能做到

1. 用多值 INSERT、INSERT ... SELECT 批量写入数据，并理解 upsert 的三种形态与取舍
2. 编写带 WHERE、先 SELECT 预览、必要时带 LIMIT 的安全 UPDATE / DELETE
3. 说出每条 DML 语句「影响行数」的语义（插入 1、更新 2、无变化 0），用返回值做幂等判断

为什么要学

生产事故的来源里，UPDATE 忘带 WHERE、批量 DELETE 不分批、upsert 用错导致重复数据常年霸榜。DML 的每一条语法都不难，难的是「写的时候就知道自己在动多少行」——这是初级和资深工程师在 SQL 上的第一个分水岭，也是面试问「如何安全地批量删除」想听的东西。

人话：查询读错了顶多重来，写错就是改生产数据。DML 三件套（INSERT/UPDATE/DELETE）的规矩只有一条：先 SELECT 看清楚要动哪些行，再动手。

先建立图像

DML 影响行数的语义（客户端返回的 Rows matched: x Changed: y）：

INSERT  成功 1 条          → 影响行数 1
UPDATE  匹配 5 条,值有变化 3 条 → matched 5 / changed 3
ON DUPLICATE KEY UPDATE
  ├─ 新插入        → 1
  ├─ 冲突且更新了  → 2   ← 用影响行数判断是否走更新分支
  └─ 冲突但值没变  → 0
DELETE  删除 3 条          → 影响行数 3

记住 2 的含义：冲突走了更新分支——很多幂等导入逻辑靠这个返回值判断。

跟着做

步骤 0：数据准备

若还没建表，先跑第 1 课的建库建表脚本；然后重置数据保证结果可核对：

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
-- 随后原样执行第 1 课步骤 4 的四段 INSERT（6 用户、6 题、12 提交、9 练习记录）

下文所有「预期结果」都基于这份初始数据。

步骤 1：多值 INSERT

INSERT INTO cf_submission (user_id, problem_id, language, status, runtime_ms, submitted_at) VALUES
  (6, 1, 'java', 'accepted',     20, '2025-06-08 10:00:00'),
  (3, 2, 'java', 'wrong_answer', 35, '2025-06-08 10:05:00');

SELECT COUNT(*) FROM cf_submission;  -- 预期 14

一次网络往返写入多行，比循环单条 INSERT 快一个数量级。批量导入的推荐大小是几百到一千行/批。

步骤 2：INSERT ... SELECT

把 qian 对题目 1 的 AC 提交同步成一条练习记录：

INSERT INTO cf_practice_record (user_id, problem_id, is_correct, duration_sec, practiced_at)
SELECT s.user_id, s.problem_id, 1, 45, s.submitted_at
FROM cf_submission s
WHERE s.user_id = 6 AND s.status = 'accepted';

SELECT COUNT(*) FROM cf_practice_record;  -- 预期 10（原 9 + 1）

INSERT ... SELECT 是数据搬迁、数据修正、报表落表的标准姿势，SELECT 部分能跑出什么就写入什么。

步骤 3：安全 UPDATE——先看名单，再动手

目标：给 AC 数 ≥ 2 的活跃用户升一级。

-- 先 SELECT 预览要动的行
SELECT u.id, u.username, u.level,
       (SELECT COUNT(*) FROM cf_submission s
        WHERE s.user_id = u.id AND s.status = 'accepted') AS ac_cnt
FROM cf_user u
WHERE u.status = 'active';
-- 预期：lin(2)、zhao(2)、zhou(2) 满足；wang 1、qian 1、sun 是 banned

-- 再 UPDATE，用 LEAST 兜底防止 level 越过 5
UPDATE cf_user u
SET u.level = LEAST(u.level + 1, 5)
WHERE u.status = 'active'
  AND (SELECT COUNT(*) FROM cf_submission s
       WHERE s.user_id = u.id AND s.status = 'accepted') >= 2;

-- 预期影响 3 行：lin 3→4, zhou 4→5, zhao 保持 5（LEAST 兜住）