MySQL深度探索：DML与DQL语言精髓
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\GEXPLAIN输出的常用列：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（订单明细）。 需求：
- 查询某用户在最近 30 天内的订单以及总消费金额；
- 更新该用户的 VIP 标示，如果总消费超过 10000 元，则设置
is_vip = 1； - 记录此次 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）保证数据一致性与性能。
评论已关闭