MySQL 深度探索：DML 与 DQL 语言精髓

以下内容从DML（数据操作语言）与DQL（数据查询语言）两大领域出发，结合代码示例、ASCII 图解和详细说明，帮助你深入理解 MySQL 中常见的增、删、改、查操作。希望通过系统化的讲解，让你在实际开发中游刃有余。

1. 概述：DML 与 DQL 的定位与区别

DML（Data Manipulation Language）
- 用于修改数据库中已有的数据，包括：INSERT、UPDATE、DELETE、REPLACE、TRUNCATE 等。
- 主要关注“如何将数据写入/修改/删除”，是业务写操作的核心。
DQL（Data Query Language）
- 用于查询数据库中的数据，最常用的语句是 SELECT。
- 主要关注“如何高效地从数据库中获取数据”，是业务读操作的核心。

维度	DML 操作	DQL 操作
主要目的	写入、更新、删除数据	读取、筛选、聚合数据
常见语句	INSERT、UPDATE、DELETE	SELECT
事务影响	会生成事务日志、持有锁	只读操作（可生成共享锁）
性能关注点	并发写冲突、事务回滚	查询优化、索引利用

2. DML（Data Manipulation Language）语言精髓

DML 主要处理对表中数据的插入、更新与删除。下面分别展开讲解。

2.1 INSERT：插入数据

2.1.1 基本插入

-- 向 users 表中插入一行
CREATE TABLE users (
  user_id   INT AUTO_INCREMENT PRIMARY KEY,
  username  VARCHAR(50) NOT NULL,
  email     VARCHAR(100) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 单行插入
INSERT INTO users (username, email)
VALUES ('alice', 'alice@example.com');

如果省略 user_id，因其为 AUTO_INCREMENT，MySQL 会自动生成下一个数值。
created_at 列有默认值 CURRENT_TIMESTAMP，若不指定插入，会自动填充当前时间。

2.1.2 多行插入

-- 一次性插入多行，减少网络往返
INSERT INTO users (username, email) VALUES
  ('bob',   'bob@example.com'),
  ('carol', 'carol@example.com'),
  ('david', 'david@example.com');

多行插入可显著提高批量写入性能。
MySQL 最多允许插入行数受 max_allowed_packet 限制，如果报 “Packet too large”，需分批次执行或调大该参数。

2.1.3 INSERT ... SELECT 插入

场景：将查询结果插入到另一张表。

-- 假设有一个临时表 user_temp，用于缓存新用户信息
CREATE TABLE user_temp (
  username  VARCHAR(50),
  email     VARCHAR(100)
) ENGINE=InnoDB;

-- 将临时表数据批量插入到 users 表中
INSERT INTO users (username, email)
SELECT username, email FROM user_temp;

此方式避免了在客户端拉取数据后再逐行插入，MySQL 内部一次性完成“查 + 写”操作。

2.1.4 INSERT IGNORE 与 ON DUPLICATE KEY UPDATE

INSERT IGNORE

如果插入时违反唯一约束（如主键、唯一索引），不会报错，而是跳过冲突行并给警告。

-- 如果 email 列有唯一索引，则重复时跳过
ALTER TABLE users ADD UNIQUE INDEX idx_email(email);

INSERT IGNORE INTO users (username, email) VALUES
  ('eve', 'eve@example.com'),
  ('frank', 'alice@example.com');  -- alice@example.com 已存在，跳过

ON DUPLICATE KEY UPDATE
- 如果插入时遇到键冲突，则执行 UPDATE 操作，可实现“插入或更新”的功能。
```
INSERT INTO users (user_id, username, email)
VALUES (1, 'alice_new', 'alice_new@example.com')
ON DUPLICATE KEY UPDATE
  username = VALUES(username),
  email    = VALUES(email);
```
- 当 user_id = 1 已存在时，改为对该行执行 UPDATE username, UPDATE email。

2.2 UPDATE：更新数据

2.2.1 基本更新

-- 假设 users 表结构如上
-- 更新某个用户的 email
UPDATE users
SET email = 'alice2023@example.com'
WHERE username = 'alice';

WHERE 条件必须明确，否则会把符合条件的所有行都更新。
如果省略 WHERE，则整张表所有行都会被更新（谨慎操作）。

2.2.2 带 JOIN 的更新

场景：根据另一张表的数据同步更新。

-- 假设有 user_profile 表，存储 \`users\` 中用户的资料
CREATE TABLE user_profile (
  profile_id   INT AUTO_INCREMENT PRIMARY KEY,
  user_id      INT NOT NULL,
  avatar_url   VARCHAR(255),
  bio          TEXT,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB;

-- 需要将 user_profile 中的 avatar_url 同步更新到 users 表的一个新列 avatar
ALTER TABLE users ADD COLUMN avatar VARCHAR(255);

-- 根据 user_profile 更新 users.avatar
UPDATE users u
JOIN user_profile p ON u.user_id = p.user_id
SET u.avatar = p.avatar_url
WHERE p.avatar_url IS NOT NULL;

上述语句中，JOIN 连接两张表，MySQL 首先执行联接得到中间结果，然后对 u.avatar 逐行更新。

2.2.3 带子查询的更新

-- 将所有未设置 email 的用户更新为“no-reply@example.com”
UPDATE users
SET email = 'no-reply@example.com'
WHERE user_id NOT IN (SELECT user_id FROM user_profile);

如果子查询可能返回大量 ID，性能会受影响。

推荐使用 LEFT JOIN 方式替代子查询：

UPDATE users u
LEFT JOIN user_profile p ON u.user_id = p.user_id
SET u.email = 'no-reply@example.com'
WHERE p.user_id IS NULL;

2.2.4 批量与分批更新

场景：对大表进行更新时，避免长事务、高并发锁等待。

-- 假设要给 orders 表中 2022 年以前的记录添加一个标签
CREATE TABLE orders (
  order_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_date DATE,
  status     VARCHAR(20),
  flag_old   TINYINT DEFAULT 0,
  INDEX idx_order_date(order_date)
) ENGINE=InnoDB;

-- 不要一次性执行：UPDATE orders SET flag_old = 1 WHERE order_date < '2022-01-01';
-- 而是分批次更新，每次 1000 条
SET @batch_size = 1000;
SET @last_id = 0;

WHILE 1 = 1 DO
  -- 找出本批次要更新的主键范围
  SELECT order_id
  FROM orders
  WHERE order_date < '2022-01-01' AND order_id > @last_id
  ORDER BY order_id
  LIMIT @batch_size
  INTO @ids;  -- 简化示例，实际可用临时表存储 IDs

  -- 如果本批次没有数据，则退出循环
  IF @ids IS NULL THEN
    LEAVE;
  END IF;

  -- 更新本批次
  UPDATE orders
  SET flag_old = 1
  WHERE order_id IN (@ids);

  -- 更新游标
  SET @last_id = (SELECT MAX(order_id) FROM (SELECT order_id FROM orders WHERE order_date < '2022-01-01' AND order_id > @last_id ORDER BY order_id LIMIT @batch_size) AS tmp);
END WHILE;

分批更新可缩短单次事务持锁时间，降低对并发读写的影响。

2.3 DELETE：删除数据

2.3.1 基本删除

-- 删除指定用户
DELETE FROM users WHERE user_id = 10;

同样，若省略 WHERE，则会删除整张表的所有行。

2.3.2 带 JOIN 的删除

场景：根据关联表信息删除主表行。

-- 假设要删除那些没有 profile 的用户
DELETE u
FROM users u
LEFT JOIN user_profile p ON u.user_id = p.user_id
WHERE p.user_id IS NULL;

语法格式：DELETE alias FROM table AS alias JOIN ... WHERE ...。
MySQL 先执行 JOIN，然后根据 WHERE 条件筛选的行再做删除。

2.3.3 分批删除

场景：对大表按条件删除时，同样需要分批。

-- orders 表示例，删除 2020 年以前的旧记录
SET @batch_size = 1000;

WHILE 1 = 1 DO
  DELETE FROM orders
  WHERE order_date < '2020-01-01'
  ORDER BY order_id
  LIMIT @batch_size;

  -- 如果受影响行数 < 批量大小，表示已删除完毕
  IF ROW_COUNT() < @batch_size THEN
    LEAVE;
  END IF;
END WHILE;

ORDER BY + LIMIT 分批删除，避免一次性删除带来的长事务与高锁冲突。

2.4 REPLACE 与 TRUNCATE：替换与清空

2.4.1 REPLACE

REPLACE INTO 是 MySQL 的扩展语法，行为类似 INSERT，但如果要插入的行与主键或唯一索引冲突，先删除旧行，再插入新行。

-- 假设 users.email 上有唯一索引
REPLACE INTO users (user_id, username, email)
VALUES (5, 'eve_new', 'eve@example.com');

如果 user_id = 5 原本存在一行，则被更新为新值；如果不存在，则相当于普通插入。
因为先执行删除再插入，可能会触发删除/插入触发器，且会重新生成 AUTO_INCREMENT 值（若未指定主键时）。

2.4.2 TRUNCATE

TRUNCATE TABLE table_name 相当于快速删除整张表所有行，并重置 AUTO_INCREMENT 计数。
与 DELETE FROM table_name 不同，TRUNCATE 不会触发 DELETE 触发器，且速度更快，因为它底层执行的是“丢弃表并重建”的操作。

TRUNCATE TABLE logs;

注意：如果存在外键约束，需要先删除或禁用外键，否则可能会报错。

2.5 事务与并发控制

2.5.1 事务基础

-- 演示事务的基本用法
START TRANSACTION;

-- 插入一条新用户
INSERT INTO users (username, email) VALUES ('grace', 'grace@example.com');

-- 更新另一个用户
UPDATE users SET email='bob2023@example.com' WHERE user_id=2;

-- 如果一切正常，提交
COMMIT;

-- 如果出错，回滚
ROLLBACK;

MySQL InnoDB 在执行 INSERT、UPDATE、DELETE 时，会对相应行加锁，保证数据一致性。
如果在事务期间发生错误，使用 ROLLBACK 撤销所有在本事务内的修改；使用 COMMIT 将修改永久写入。

2.5.2 锁粒度与并发

行锁
- InnoDB 支持行级锁，锁定具体的索引记录，避免对整张表加锁，提高并发。
- 例如，执行 UPDATE users SET email=... WHERE user_id = 2 只会锁住 user_id=2 那一行。
意向锁与锁升级
- 在执行行锁之前，InnoDB 会先在表层面加“意向锁”（Intention Lock），标记该事务想要加行锁，便于其他事务快速判定冲突。
- 如果一个事务需要锁定大量行，并且经过索引扫描发现会涉及大范围范围锁（Gap Lock），就有可能将锁升级为更高粒度的锁。

锁等待与死锁

并发事务可能在更新相同的数据时发生锁等待：

┌───────────────┐               ┌───────────────┐
│ 事务 A        │               │ 事务 B        │
│               │               │               │
│ UPDATE users  │               │ UPDATE users  │
│ SET email=... │               │ SET email=... │
│ WHERE user_id=2│ <─等待锁──   │ WHERE user_id=3│ 
│               │               │               │
└───────────────┘               └───────────────┘

如果出现循环等待（A 等待 B 释放，B 等待 A 释放），InnoDB 会检测到死锁，自动回滚其中一个事务，避免永久阻塞。

示例：并发更新导致死锁

会话 A：

START TRANSACTION;
SELECT * FROM accounts WHERE acc_id = 1 FOR UPDATE;
-- 持有 acc_id=1 的行锁
UPDATE accounts SET balance = balance - 100 WHERE acc_id = 2;  -- 需要锁 acc_id=2

会话 B：

START TRANSACTION;
SELECT * FROM accounts WHERE acc_id = 2 FOR UPDATE;
-- 持有 acc_id=2 的行锁
UPDATE accounts SET balance = balance - 200 WHERE acc_id = 1;  -- 需要锁 acc_id=1

此时会话 A 等待 B 释放 acc_id=2 的锁，会话 B 等待 A 释放 acc_id=1 的锁，InnoDB 检测到死锁，并回滚其中一个事务。

3. DQL（Data Query Language）语言精髓

DQL 主要负责查询数据，最核心的就是 SELECT 语句。下面从基础到高级功能逐步展开。

3.1 SELECT 基础与筛选

3.1.1 基本 SELECT

-- 查询整个表的全部列
SELECT * FROM users;

-- 查询指定列，避免 SELECT * 带来不必要开销
SELECT user_id, username, email FROM users;

建议明确列名，防止表结构变更导致客户端应用错误。

3.1.2 WHERE 条件筛选

-- 简单比较运算
SELECT * FROM users WHERE user_id = 5;

-- 多条件组合：AND / OR
SELECT * FROM users 
WHERE (username LIKE 'a%' OR username LIKE 'b%')
  AND created_at >= '2023-01-01';

-- 范围查询：BETWEEN ... AND ...
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';

-- IN / NOT IN
SELECT * FROM users 
WHERE user_id IN (1, 2, 3);

-- NULL 检测
SELECT * FROM user_profile WHERE bio IS NULL;

3.1.3 支持的常用表达式

字符串匹配：LIKE（%、_）；若数据量大，需结合全文索引或前缀索引，否则全表扫描开销大。
日期函数：DATE(), DATE_FORMAT(), YEAR(), MONTH(), CURDATE() 等。
数值运算与聚合（见后文聚合部分）。

3.2 排序与分页（ORDER BY、LIMIT）

3.2.1 ORDER BY

-- 按用户注册时间降序排列
SELECT user_id, username, created_at
FROM users
ORDER BY created_at DESC;

-- 可同时对多列排序
SELECT order_id, order_date, total_amt
FROM orders
WHERE user_id = 5
ORDER BY order_date DESC, total_amt ASC;

ORDER BY 会对结果集进行额外排序，如果排序字段没有合适索引，大数据量时会产生文件排序（external sort）开销。

3.2.2 LIMIT

-- 只获取前 10 条记录
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10;

-- 分页查询：获取第 3 页的数据，每页 20 条（OFFSET 从 0 开始）
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 40, 20;  -- OFFSET 40，取 20 条

大量偏移量（OFFSET 很大）时，性能会下降，因为 MySQL 需要扫描并跳过前面所有行。可考虑“基于索引范围分页”：

-- 假设已知上一次最后一行的 created_at = '2023-05-01 00:00:00'
SELECT * FROM users
WHERE created_at < '2023-05-01 00:00:00'
ORDER BY created_at DESC
LIMIT 20;

3.3 聚合与分组（GROUP BY、HAVING）

3.3.1 聚合函数

常用聚合函数：COUNT()、SUM()、AVG()、MIN()、MAX()。

-- 统计总用户数
SELECT COUNT(*) AS total_users FROM users;

-- 统计订单总金额
SELECT SUM(total_amt) AS total_revenue FROM orders;

3.3.2 GROUP BY 基本用法

-- 统计每个用户的订单总金额与笔数
SELECT user_id, 
       COUNT(*) AS order_count, 
       SUM(total_amt) AS total_spent
FROM orders
GROUP BY user_id;

GROUP BY 将行分为若干组，对每组执行聚合。
MySQL 默认允许 SELECT 中出现非聚合列，但严格 SQL 模式下会报错。建议配合聚合函数或将非聚合列放入 GROUP BY。

3.3.3 HAVING 过滤分组结果

HAVING 与 WHERE 区别：
WHERE 作用于分组前，筛选基本行。
HAVING 作用于分组后，对聚合结果进行过滤。

-- 只保留订单笔数 >= 5 的用户
SELECT user_id, 
       COUNT(*) AS order_count, 
       SUM(total_amt) AS total_spent
FROM orders
GROUP BY user_id
HAVING order_count >= 5;

3.4 连接查询（JOIN）

连接查询是 DQL 的核心功能之一，用于跨表获取关联数据。下面依次介绍常见的几种 JOIN 类型。

3.4.1 INNER JOIN（内连接）

-- 查询每个订单对应的用户信息
SELECT o.order_id, o.order_date, u.username, u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
WHERE o.order_date >= '2023-01-01';

INNER JOIN 只返回两个表中都能匹配上的行（交集）。

3.4.2 LEFT JOIN / RIGHT JOIN（左/右连接）

-- 查询所有用户及其最近一笔订单（若无订单则 NULL）
SELECT u.user_id, u.username, o.order_id, o.order_date
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
  AND o.order_date = (
    SELECT MAX(order_date) FROM orders WHERE user_id = u.user_id
  );

-- 右连接示例（不如 LEFT JOIN 常用）
SELECT o.order_id, o.order_date, u.username
FROM orders o
RIGHT JOIN users u ON o.user_id = u.user_id;

LEFT JOIN 会保留左表（如 users）所有行，即使右表（orders）不存在对应行也显示 NULL。

3.4.3 CROSS JOIN（笛卡尔积）

-- 不加 ON 条件的 JOIN 会产生笛卡尔积，通常需谨慎
SELECT u.username, p.plan_name
FROM users u
CROSS JOIN plans p;

一般用于产生两个集合的所有配对，量级迅速膨胀，不常用于日常业务查询。

3.4.4 FULL OUTER JOIN（MySQL 不原生支持）

MySQL 未提供 FULL OUTER JOIN，可通过 UNION 组合 LEFT JOIN 与 RIGHT JOIN 来模拟：

SELECT u.user_id, u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id

UNION

SELECT u2.user_id, u2.username, o2.order_id
FROM users u2
RIGHT JOIN orders o2 ON u2.user_id = o2.user_id;

结果包含左、右表所有行，匹配与未匹配都展示。

3.4.5 ASCII 图解：JOIN 逻辑示意

INNER JOIN

+----------+    ON     +----------+
|  users   |---------->| orders   |
+----------+           +----------+
    ▲ ▲                     | |
    | |                     v v
    | |      ------------>  (匹配行)
    | +----> A ∩ B
    |
(仅返回匹配部分)

LEFT JOIN

+----------+           +----------+
|  users   |---------->| orders   |
+----------+           +----------+
| |
| |      +------+     +--------+
| +----> |  u   |◄----|   o    |
|        +------+     +--------+
|    A∖B  (仅左表)   A ∩ B (匹配行)
|
(返回 users 的所有行)

3.5 子查询（Subquery）

子查询可分为标量子查询、相关子查询、多行子查询等类型。

3.5.1 标量子查询

返回单个数值，可放在 SELECT 列表或 WHERE 比较中。

-- 查询所有订单，并附带每笔订单的用户名称
SELECT o.order_id, o.order_date,
       (SELECT username FROM users WHERE user_id = o.user_id) AS username
FROM orders o;

标量子查询要保证只返回一行一列，否则会报错 Subquery returns more than 1 row。

3.5.2 多行子查询（IN / EXISTS）

-- 查询所有有订单的用户
SELECT * FROM users
WHERE user_id IN (SELECT DISTINCT user_id FROM orders);

-- 或用 EXISTS，性能通常更好
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);

IN (subquery) 适用于子查询结果不太大的场景；
EXISTS 在匹配到第一行后即可终止查询，通常性能更优。

3.5.3 关联子查询（Correlated Subquery）

子查询中引用了外层查询的列，每行执行时子查询都会重新计算一次。

-- 查询订单表中，订单金额大于该用户平均订单金额的订单
SELECT o1.order_id, o1.user_id, o1.total_amt
FROM orders o1
WHERE o1.total_amt > (
  SELECT AVG(o2.total_amt)
  FROM orders o2
  WHERE o2.user_id = o1.user_id
);

对每个外层行，子查询都要重新执行一次，性能可能较差；可考虑重写为 JOIN + GROUP BY。

3.6 集合操作（UNION、UNION ALL）

3.6.1 UNION

-- 从表 A 与表 B 中分别选取用户邮箱，然后去重
SELECT email FROM table_a
UNION
SELECT email FROM table_b;

UNION 会自动去重，生成不重复的结果集，会隐含执行排序，性能较 UNION ALL 差。

3.6.2 UNION ALL

-- 不去重，直接合并结果，性能更高
SELECT email FROM table_a
UNION ALL
SELECT email FROM table_b;

如果确认两个结果集没有重复，或不需要去重，可使用 UNION ALL 提升效率。

3.7 执行计划与索引优化

3.7.1 EXPLAIN 简介

-- 对 SELECT 语句添加 EXPLAIN，查看执行计划
EXPLAIN 
SELECT u.username, COUNT(o.order_id) AS cnt
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.user_id
HAVING cnt > 5
ORDER BY cnt DESC
LIMIT 10\G

EXPLAIN 输出的常用列：
- id：表示查询中 SELECT 的序号；
- select_type：基本 SELECT、联接类型、子查询等；
- table：访问的表名；
- type：访问类型，如 ALL（全表扫描）、index、range、ref、eq_ref、const；
- possible_keys：可能使用的索引；
- key：实际使用的索引；
- rows：扫描的估算行数；
- Extra：额外信息，如是否使用文件排序、临时表等。

3.7.2 常见执行类型

类型	描述
`ALL`	全表扫描
`index`	全索引扫描（比全表扫描稍快）
`range`	索引范围扫描
`ref`	通过索引列查到若干行
`eq_ref`	基于唯一索引查到精确一行
`const`	常数（仅匹配一行），性能最好

优化目标：尽量让 type 列出现 const、eq_ref 或 ref，避免 ALL。

3.7.3 索引使用建议

WHERE 条件中的列需建索引
- 对于常用的筛选条件，如 user_id, created_at, status 等，应创建单列或复合索引。
JOIN 条件索引
- 确保 ON 子句中使用的列都已建立索引，如 orders.user_id、users.user_id。

覆盖索引（Covering Index）

如果查询只涉及索引中的列，称为覆盖索引。MySQL 可直接从索引返回结果，无需回表，提高性能。

-- users 表创建复合索引 (created_at, username)
CREATE INDEX idx_created_username 
ON users(created_at, username);

-- 查询时只访问这两个列，走覆盖索引
SELECT username
FROM users
WHERE created_at >= '2023-01-01'
ORDER BY created_at DESC
LIMIT 10;

避免函数操作导致索引失效

如果在 WHERE 中对列做函数运算，索引将失效。

-- 索引会失效，改写前
SELECT * FROM users WHERE DATE(created_at) = '2023-10-01';

-- 改为范围查询，索引可用
SELECT * FROM users 
WHERE created_at >= '2023-10-01 00:00:00' 
  AND created_at <  '2023-10-02 00:00:00';

4. 综合案例：DML 与 DQL 协同应用

下面以一个电商场景为例，综合演示 DML 与 DQL 在业务中如何协同。

4.1 场景描述

有三张表：users（用户）、orders（订单）、order_items（订单明细）。
需求：
1. 查询某用户在最近 30 天内的订单以及总消费金额；
2. 更新该用户的 VIP 标示，如果总消费超过 10000 元，则设置 is_vip = 1；
3. 记录此次 VIP 状态更新到日志表 user_status_log 中。

4.2 表结构

CREATE TABLE users (
  user_id    INT AUTO_INCREMENT PRIMARY KEY,
  username   VARCHAR(50),
  is_vip     TINYINT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_created_at(created_at)
) ENGINE=InnoDB;

CREATE TABLE orders (
  order_id   INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT NOT NULL,
  order_date DATETIME,
  total_amt  DECIMAL(10,2),
  INDEX idx_user_date(user_id, order_date),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  item_id    INT AUTO_INCREMENT PRIMARY KEY,
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT,
  unit_price DECIMAL(10,2),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
) ENGINE=InnoDB;

CREATE TABLE user_status_log (
  log_id     INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT NOT NULL,
  old_vip    TINYINT,
  new_vip    TINYINT,
  changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

4.3 步骤 1：计算最近 30 天总消费（DQL）

-- 假设我们关注 user_id = 123
SELECT SUM(o.total_amt) AS total_spent
FROM orders o
WHERE o.user_id = 123
  AND o.order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);

利用索引 (user_id, order_date)，使范围筛选与聚合性能较好。

4.4 步骤 2：根据结果更新用户 VIP 状态（DML + 子查询）

-- 用子查询将总消费金额嵌入到更新语句中
UPDATE users u
JOIN (
  SELECT SUM(total_amt) AS total_spent
  FROM orders
  WHERE user_id = 123
    AND order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
) tmp ON 1=1
SET 
  u.is_vip = CASE WHEN tmp.total_spent > 10000 THEN 1 ELSE 0 END
WHERE u.user_id = 123;

JOIN (子查询) tmp ON 1=1 技巧：让 tmp 只有一行结果，与 users u“笛卡尔”后再用 WHERE 过滤到目标用户，避免子查询和外层表混淆。
也可以使用变量或先查询后更新，但该方式在一条 SQL 中完成“查询 → 更新”更简洁。

4.5 步骤 3：记录状态变更日志（DML）

-- 假设在上一步之前，我们已记录 old_vip 的值（简化示例用变量）
SET @old_vip = (SELECT is_vip FROM users WHERE user_id = 123);
SET @new_vip = (SELECT is_vip FROM users WHERE user_id = 123);

-- 如果状态发生变化，则插入日志
INSERT INTO user_status_log (user_id, old_vip, new_vip)
VALUES (123, @old_vip, @new_vip)
WHERE @old_vip <> @new_vip;  -- 只有状态改变才写日志

注意：标准 SQL INSERT ... WHERE 并不支持这种写法，但在应用逻辑中可先比较再插入，或使用条件表达式：
INSERT INTO user_status_log (user_id, old_vip, new_vip)
SELECT 123, @old_vip, @new_vip
WHERE @old_vip <> @new_vip;

4.6 步骤 4：在事务中完成所有操作

START TRANSACTION;

-- 1. 获取旧状态
SELECT is_vip INTO @old_vip FROM users WHERE user_id = 123 FOR UPDATE;

-- 2. 计算并更新
UPDATE users u
JOIN (
  SELECT SUM(total_amt) AS total_spent
  FROM orders
  WHERE user_id = 123
    AND order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
) tmp ON 1=1
SET 
  u.is_vip = CASE WHEN tmp.total_spent > 10000 THEN 1 ELSE 0 END
WHERE u.user_id = 123;

-- 3. 获取新状态
SELECT is_vip INTO @new_vip FROM users WHERE user_id = 123;

-- 4. 记录日志（如果变化）
INSERT INTO user_status_log (user_id, old_vip, new_vip)
SELECT 123, @old_vip, @new_vip
WHERE @old_vip <> @new_vip;

COMMIT;

通过 FOR UPDATE 锁定用户行，避免并发事务修改导致状态不一致。
整个业务逻辑在一个事务中完成，要么全部成功，要么全部回滚，保证一致性。

5. 小结

DML 部分：
- INSERT 支持单行、多行、INSERT ... SELECT、INSERT IGNORE、ON DUPLICATE KEY UPDATE 等高级用法。
- UPDATE 支持简单更新、JOIN 更新、子查询更新、分批更新，以减少对并发的影响。
- DELETE 同样支持 JOIN 删除、分批删除，避免一次性大事务。
- REPLACE、TRUNCATE 是 MySQL 特有或特殊语义的写操作。
- 事务（Transaction）是 DML 核心，通过 START TRANSACTION / COMMIT / ROLLBACK 来控制原子性。
- 并发控制依赖 InnoDB 行锁、意向锁和死锁检测机制，需合理设计锁粒度与事务时长。
DQL 部分：
- SELECT 语句是核心，通过 WHERE、ORDER BY、LIMIT、GROUP BY、HAVING 等子句实现多维度筛选、排序、分组与聚合。
- 连接查询（JOIN）是跨表关联的利器，包括 INNER JOIN、LEFT JOIN、RIGHT JOIN 等，多表关联要注意索引和数据量。
- 子查询（Subquery）可分为标量子查询、相关子查询、IN/EXISTS 子查询，但性能可能不如 JOIN。
- 集合操作（UNION / UNION ALL）可合并多个 SELECT 结果集，需注意去重与排序开销。
- 优化查询时要借助 EXPLAIN 查看执行计划，尽量让 MySQL 使用合适的索引，避免全表扫描与文件排序；使用覆盖索引与范围查询技巧提高性能；避免在索引列上使用函数。
结合场景实践：
- 通过一个电商用户“VIP 状态更新”的综合案例，演示了如何在一个事务内使用 DQL 计算数据并结合 DML 更新，然后记录日志。
- 在高并发环境下，要分批处理大数据量 DML 操作，减少锁等待与死锁概率；同时 DQL 查询需注意索引化、分页技术与聚合优化。

通过上述系统化的讲解与示例，你应当能够深入理解 MySQL 中 DML 与 DQL 的核心技术要点，并在实际项目中合理应用：一方面通过高效的查询（DQL）获取所需数据，另一方面通过谨慎的写操作（DML）保证数据一致性与性能。