数据操作语言（DML）
问题
SQL 中 INSERT、UPDATE、DELETE 怎么用？MERGE 是什么？如何安全地修改数据？
答案
DML（Data Manipulation Language，数据操作语言） 用于对表中的数据进行增删改操作。虽然数据分析师的日常以 SELECT 查询为主，但理解 DML 对于数据清洗、ETL、临时表操作等场景非常重要。
INSERT 插入数据
基本语法
-- 方式 1：指定列名（推荐，即使列顺序变化也不受影响）
INSERT INTO users (name, email, age) VALUES ('张三', 'zhang@example.com', 25);
-- 方式 2：省略列名（必须按照表定义的列顺序提供所有值）
INSERT INTO users VALUES (1, '张三', 'zhang@example.com', 25, NOW());
-- 方式 3：插入多行（一次性插入，性能远好于逐行插入）
INSERT INTO users (name, email, age) VALUES
('张三', 'zhang@example.com', 25),
('李四', 'li@example.com', 28),
('王五', 'wang@example.com', 30);
批量插入性能
逐行 INSERT 需要每次都和数据库通信一次。批量 INSERT 只需要一次通信，性能差距可达 10~100 倍。在做数据导入时，务必使用批量插入。
INSERT ... SELECT
从查询结果中插入数据，常用于数据迁移和 ETL：
-- 将查询结果插入另一张表
INSERT INTO user_archive (name, email, age)
SELECT name, email, age FROM users WHERE created_at < '2023-01-01';
-- 创建汇总表
INSERT INTO monthly_stats (month, total_orders, total_revenue)
SELECT
DATE_FORMAT(order_date, '%Y-%m'),
COUNT(*),
SUM(amount)
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m');
INSERT ... ON DUPLICATE KEY UPDATE（MySQL）
插入数据时，如果主键或唯一键冲突，则更新已有记录：
-- 如果 email 已存在，更新 name 和 age
INSERT INTO users (email, name, age) VALUES ('zhang@example.com', '张三', 26)
ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);
-- 常见场景：统计表的 upsert（有则更新，无则插入）
INSERT INTO daily_stats (stat_date, page_views, unique_visitors)
VALUES ('2024-01-15', 1000, 500)
ON DUPLICATE KEY UPDATE
page_views = page_views + VALUES(page_views),
unique_visitors = VALUES(unique_visitors);
INSERT ... ON CONFLICT（PostgreSQL）
PostgreSQL 的等价写法：
INSERT INTO users (email, name, age) VALUES ('zhang@example.com', '张三', 26)
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age;
-- 冲突时什么都不做
INSERT INTO users (email, name, age) VALUES ('zhang@example.com', '张三', 26)
ON CONFLICT (email) DO NOTHING;
UPDATE 更新数据
基本语法
-- 更新单个字段
UPDATE users SET age = 26 WHERE id = 1;
-- 更新多个字段
UPDATE users SET name = '张三丰', age = 30, updated_at = NOW() WHERE id = 1;
-- 基于表达式更新
UPDATE products SET price = price * 1.1 WHERE category = '电子'; -- 涨价 10%
没有 WHERE 的 UPDATE 会更新所有行！
-- ⚠️ 危险操作：没有 WHERE 条件
UPDATE users SET status = 'inactive';
-- 这会把所有用户的状态都改成 inactive！
安全措施：
- 先用 SELECT 验证 WHERE 条件
- 在事务中执行，确认无误后再提交
- MySQL 开启
sql_safe_updates模式
UPDATE ... JOIN（多表更新）
-- MySQL：根据另一张表的数据更新当前表
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.user_name = u.name
WHERE o.user_name IS NULL;
-- PostgreSQL：使用 FROM 子句
UPDATE orders o
SET user_name = u.name
FROM users u
WHERE o.user_id = u.id AND o.user_name IS NULL;
UPDATE ... 子查询
-- 将每个用户的订单总数更新到用户表中
UPDATE users u
SET order_count = (
SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id
);
-- 只更新特定用户
UPDATE users
SET vip_level = 'gold'
WHERE id IN (
SELECT user_id FROM orders
GROUP BY user_id
HAVING SUM(amount) > 10000
);
CASE WHEN 批量条件更新
-- 根据不同条件设置不同的值
UPDATE products
SET status = CASE
WHEN stock = 0 THEN 'out_of_stock'
WHEN stock < 10 THEN 'low_stock'
ELSE 'in_stock'
END
WHERE category = '电子';
-- 批量更新不同的值（避免多条 UPDATE）
UPDATE users SET age = CASE id
WHEN 1 THEN 25
WHEN 2 THEN 28
WHEN 3 THEN 30
END
WHERE id IN (1, 2, 3);
DELETE 删除数据
基本语法
-- 删除满足条件的行
DELETE FROM users WHERE id = 1;
-- 删除符合子查询条件的行
DELETE FROM users WHERE id IN (
SELECT user_id FROM blacklist
);
没有 WHERE 的 DELETE 会删除所有数据！
-- ⚠️ 极其危险
DELETE FROM users;
-- 删除 users 表的所有数据！
-- 如果要清空表，用 TRUNCATE 更快
TRUNCATE TABLE users;
DELETE vs TRUNCATE
| 对比 | DELETE | TRUNCATE |
|---|---|---|
| WHERE 条件 | ✅ 支持 | ❌ 不支持（删除所有） |
| 事务 | ✅ 可回滚 | ⚠️ DDL 操作，MySQL 隐式提交 |
| 触发器 | ✅ 触发 | ❌ 不触发 |
| 自增 ID | 继续递增 | 重置为 1 |
| 性能 | 慢（逐行删除） | 快（直接释放数据页） |
| 适用场景 | 按条件删除部分数据 | 清空整张表 |
软删除（推荐）
实际开发中，很少直接 DELETE 数据，通常使用软删除：
-- 不是真的删除，而是标记为已删除
UPDATE users SET is_deleted = 1, deleted_at = NOW() WHERE id = 1;
-- 查询时过滤已删除的数据
SELECT * FROM users WHERE is_deleted = 0;
软删除的好处
- 数据可恢复，不怕误操作
- 可以追踪删除时间和删除人
- 关联数据不会出现孤立记录
- 符合数据合规要求（GDPR 等）
DELETE ... JOIN（多表关联删除）
-- MySQL：删除黑名单用户的所有订单
DELETE o FROM orders o
JOIN blacklist b ON o.user_id = b.user_id;
-- PostgreSQL：使用 USING
DELETE FROM orders o
USING blacklist b
WHERE o.user_id = b.user_id;
MERGE / UPSERT
MERGE 语句（也叫 UPSERT）可以在一条 SQL 中同时执行 INSERT 和 UPDATE：有则更新，无则插入。
SQL Server / Oracle 的 MERGE
MERGE INTO target_table t
USING source_table s ON t.id = s.id
WHEN MATCHED THEN
UPDATE SET t.name = s.name, t.amount = s.amount
WHEN NOT MATCHED THEN
INSERT (id, name, amount) VALUES (s.id, s.name, s.amount);
MySQL 的 REPLACE INTO
-- 如果主键冲突，先 DELETE 再 INSERT（注意：会重置自增 ID）
REPLACE INTO users (id, name, email) VALUES (1, '张三', 'new@example.com');
REPLACE INTO 的陷阱
REPLACE INTO 实际上是 DELETE + INSERT，会导致：
- 自增 ID 变化
- 触发 DELETE 和 INSERT 的触发器
- 关联的外键记录可能被级联删除
推荐使用 INSERT ... ON DUPLICATE KEY UPDATE 替代。
安全操作最佳实践
1. 先 SELECT 再操作
-- 第一步：用 SELECT 验证条件
SELECT * FROM users WHERE created_at < '2023-01-01' AND is_deleted = 0;
-- 确认结果是否符合预期
-- 第二步：确认无误后执行操作
DELETE FROM users WHERE created_at < '2023-01-01' AND is_deleted = 0;
2. 使用事务
-- 开启事务
START TRANSACTION;
-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 检查结果
SELECT * FROM accounts WHERE id IN (1, 2);
-- 确认无误后提交（或回滚）
COMMIT; -- 提交
-- ROLLBACK; -- 如果有问题就回滚
3. MySQL 安全模式
-- 开启安全更新模式：禁止没有 WHERE 或没有用到索引的 UPDATE/DELETE
SET sql_safe_updates = 1;
-- 此时以下操作会报错
DELETE FROM users; -- 没有 WHERE → 报错
UPDATE users SET status = 'inactive'; -- 没有 WHERE → 报错
UPDATE users SET age = 20 WHERE name = '张三'; -- name 没有索引可能也报错
4. LIMIT 限制影响行数
-- 限制更新/删除的行数，防止意外影响过多数据
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 10000;
-- 分批删除，避免长时间锁表
常见面试问题
Q1: DELETE、TRUNCATE、DROP 有什么区别？
答案：
| 操作 | 作用 | 事务回滚 | 触发器 | 保留表结构 |
|---|---|---|---|---|
DELETE | 删除行（可带条件） | ✅ 可回滚 | ✅ 触发 | ✅ 保留 |
TRUNCATE | 清空所有行 | ⚠️ 不可回滚 | ❌ 不触发 | ✅ 保留 |
DROP | 删除整张表 | ⚠️ 不可回滚 | ❌ 不触发 | ❌ 不保留 |
速度：DROP > TRUNCATE > DELETE
Q2: 如何高效删除大表中的大量数据？
答案：
-- ❌ 一次删除百万行，会长时间锁表
DELETE FROM logs WHERE created_at < '2023-01-01';
-- ✅ 分批删除
WHILE 还有待删除的行:
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 10000;
SLEEP(0.1); -- 留出时间给其他查询
更好的方案：
-- MySQL 分批删除脚本
DELIMITER $$
CREATE PROCEDURE batch_delete()
BEGIN
DECLARE done INT DEFAULT 0;
REPEAT
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 10000;
SET done = ROW_COUNT(); -- 本次删除的行数
SELECT SLEEP(0.1);
UNTIL done = 0 END REPEAT;
END$$
DELIMITER ;
CALL batch_delete();
Q3: INSERT INTO ... SELECT 和 CREATE TABLE ... AS SELECT 的区别？
答案：
-- INSERT INTO ... SELECT：向已存在的表插入数据
INSERT INTO archive_users SELECT * FROM users WHERE created_at < '2023-01-01';
-- 前提：archive_users 表必须已经存在且结构匹配
-- CREATE TABLE ... AS SELECT (CTAS)：创建新表并填充数据
CREATE TABLE archive_users AS SELECT * FROM users WHERE created_at < '2023-01-01';
-- 自动创建表，但不会复制索引、约束、自增设置等
Q4: 什么是幂等性？DML 操作如何保证幂等？
答案：
幂等性：同一个操作执行一次和执行多次，结果相同。
-- ❌ 非幂等：多次执行结果不同
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- 第一次：balance 从 1000 变为 1100
-- 第二次：balance 从 1100 变为 1200
-- ✅ 幂等：多次执行结果相同
UPDATE accounts SET balance = 1100 WHERE id = 1;
-- 不管执行几次，balance 都是 1100
-- ✅ INSERT 保证幂等：使用 ON DUPLICATE KEY
INSERT INTO configs (key, value) VALUES ('max_retry', '3')
ON DUPLICATE KEY UPDATE value = '3';
Q5: UPDATE 返回值中 Rows matched 和 Rows changed 的区别？
答案：
UPDATE users SET name = '张三' WHERE id = 1;
-- Query OK, 0 rows affected (0.00 sec)
-- Rows matched: 1 Changed: 0 Warnings: 0
- Rows matched：满足 WHERE 条件的行数（找到了 1 行）
- Rows changed：实际被修改的行数（值没变化就不算修改）
如果 name 本来就是"张三"，matched=1 但 changed=0。这个区别在判断"操作是否生效"时很重要。
相关链接
- MySQL INSERT 语法
- MySQL UPDATE 语法
- MySQL DELETE 语法
- PostgreSQL INSERT
- PostgreSQL ON CONFLICT
- SELECT 查询基础 - DQL 数据查询
- SQL 语法基础 - DDL/DML/DQL/DCL 分类
- 事务与并发控制 - DML 操作的事务保障