第三章：数据操作

1. INSERT 插入数据

INSERT 语句用于向表中插入新行。MySQL 支持多种插入方式，从单行到批量、从简单到带冲突处理。

单行插入

-- 指定列名插入（推荐，清晰且不受列顺序影响）
INSERT INTO users (name, email, age) VALUES ('王五', 'wangwu@example.com', 25);

-- 省略列名（必须按表定义的列顺序提供所有值，不推荐）
INSERT INTO users VALUES (NULL, '赵六', 'zhaoliu@example.com', 30, NULL, NULL, NOW());

-- 查看插入结果
SELECT * FROM users WHERE name = '王五';

多行插入

一次 INSERT 可以插入多行数据，比逐条插入高效得多：

-- 批量插入多行
INSERT INTO users (name, email, age, city, salary) VALUES
    ('张三', 'zhangsan@example.com', 28, '北京', 15000.00),
    ('李四', 'lisi@example.com', 32, '上海', 20000.00),
    ('孙七', 'sunqi@example.com', 22, '深圳', 8000.00),
    ('周八', 'zhouba@example.com', 29, '上海', 18000.00),
    ('郑十', 'zhengshi@example.com', 27, '广州', 13000.00);

INSERT ... SELECT

从一张表查询数据并插入另一张表：

-- 创建归档表
CREATE TABLE archive_users LIKE users;

-- 将年龄大于 60 的用户归档
INSERT INTO archive_users SELECT * FROM users WHERE age > 60;

-- 只复制部分列
INSERT INTO archive_users (name, email, age)
SELECT name, email, age FROM users WHERE created_at < '2025-01-01';

INSERT ... ON DUPLICATE KEY UPDATE（Upsert）

当插入的数据与唯一键（PRIMARY KEY 或 UNIQUE）冲突时，执行更新操作而不是报错：

-- 如果 email 已存在则更新 name 和 age，否则插入新行
INSERT INTO users (name, email, age) VALUES ('张三丰', 'zhangsan@example.com', 29)
ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);

-- MySQL 8.0.19+ 推荐使用 AS 别名语法
INSERT INTO users (name, email, age) VALUES ('张三丰', 'zhangsan@example.com', 29) AS new_data
ON DUPLICATE KEY UPDATE name = new_data.name, age = new_data.age;

-- 查看受影响行数：插入返回 1，更新返回 2，未变化返回 0
SELECT ROW_COUNT();

2. UPDATE 更新数据

UPDATE 语句用于修改表中已有行的数据。务必搭配 WHERE 子句使用，避免误更新所有行。

基本更新

-- 更新单个字段
UPDATE users SET age = 29 WHERE name = '张三';

-- 更新多个字段
UPDATE users SET age = 30, email = 'zhangsan_new@example.com' WHERE id = 1;

-- 使用表达式更新
UPDATE users SET salary = salary * 1.1 WHERE city = '北京';

-- 使用函数更新
UPDATE users SET name = TRIM(name), email = LOWER(email);

-- 危险！没有 WHERE 子句
UPDATE users SET age = 0;  -- 所有用户的 age 都变成 0

条件更新

-- 使用 CASE WHEN 批量按条件更新
UPDATE users SET salary = CASE
    WHEN city = '北京' THEN salary * 1.15
    WHEN city = '上海' THEN salary * 1.10
    WHEN city = '广州' THEN salary * 1.08
    ELSE salary * 1.05
END;

-- 使用子查询条件
UPDATE users SET salary = salary + 2000
WHERE id IN (SELECT id FROM (SELECT id FROM users WHERE age > 30) AS tmp);

UPDATE 与 JOIN

MySQL 支持在 UPDATE 中使用 JOIN，根据另一张表的数据来更新：

-- 创建部门表
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    city VARCHAR(50),
    bonus DECIMAL(10, 2)
);

INSERT INTO departments (city, bonus) VALUES
    ('北京', 5000), ('上海', 4000), ('广州', 3000), ('深圳', 3500);

-- 根据部门表的 bonus 更新用户薪资
UPDATE users u
JOIN departments d ON u.city = d.city
SET u.salary = u.salary + d.bonus;

-- 使用 LEFT JOIN（未匹配到部门的用户不受影响）
UPDATE users u
LEFT JOIN departments d ON u.city = d.city
SET u.salary = u.salary + IFNULL(d.bonus, 0);

3. DELETE 删除数据

DELETE 语句用于从表中删除满足条件的行。和 UPDATE 一样，务必搭配 WHERE 子句使用。

基本删除

-- 删除指定行
DELETE FROM users WHERE id = 1;

-- 删除满足条件的行
DELETE FROM users WHERE age < 20;

-- 删除多个条件
DELETE FROM users WHERE city = '广州' AND salary < 10000;

使用子查询删除

-- 删除没有对应部门的用户
DELETE FROM users WHERE city NOT IN (SELECT city FROM departments);

-- MySQL 不允许在 DELETE 中直接引用正在删除的表，需要用子查询包装
DELETE FROM users WHERE id IN (
    SELECT id FROM (
        SELECT id FROM users WHERE salary < (SELECT AVG(salary) FROM users)
    ) AS tmp
);

DELETE 与 LIMIT

-- 配合 LIMIT 分批删除（避免大事务锁表）
DELETE FROM users WHERE created_at < '2024-01-01' LIMIT 1000;

-- 在脚本中循环执行，直到没有更多行要删除
-- 伪代码：
-- WHILE affected_rows > 0:
--     DELETE FROM users WHERE created_at < '2024-01-01' LIMIT 1000;

TRUNCATE TABLE vs DELETE

清空整张表有两种方式，它们的行为差异很大：

-- 清空表（可回滚，较慢）
DELETE FROM users;

-- 清空表（不可回滚，极快）
TRUNCATE TABLE users;

-- 先查看要删除的数据
SELECT * FROM users WHERE created_at < '2024-01-01';
-- 确认无误后再执行删除
DELETE FROM users WHERE created_at < '2024-01-01';

4. REPLACE 与 ON DUPLICATE KEY UPDATE

MySQL 提供两种处理"插入或更新"（Upsert）的方式。虽然功能类似，但内部行为差异很大。

REPLACE INTO

如果主键或唯一键冲突，先删除旧行再插入新行：

-- 如果 email 冲突，删除旧行，插入新行
REPLACE INTO users (name, email, age) VALUES ('张三丰', 'zhangsan@example.com', 29);

-- REPLACE 也支持多行
REPLACE INTO users (name, email, age) VALUES
    ('张三丰', 'zhangsan@example.com', 29),
    ('新用户', 'newuser@example.com', 20);

两者的关键区别

-- 推荐：使用 ON DUPLICATE KEY UPDATE（更安全）
INSERT INTO users (name, email, age, salary) VALUES ('张三', 'zhangsan@example.com', 30, 16000)
ON DUPLICATE KEY UPDATE age = VALUES(age), salary = VALUES(salary);

-- 不推荐：REPLACE 会丢失未指定列的数据
-- 以下语句会让 city、salary 等未指定列重置为默认值
REPLACE INTO users (name, email, age) VALUES ('张三', 'zhangsan@example.com', 30);

5. 安全操作建议

数据操作（尤其是 UPDATE 和 DELETE）直接修改或删除数据，一旦出错可能造成严重后果。以下是保护数据的关键实践。

始终使用 WHERE 子句

-- ❌ 危险：更新所有行
UPDATE users SET salary = 0;

-- ✅ 安全：指定条件
UPDATE users SET salary = 0 WHERE id = 5;

-- ❌ 危险：删除所有行
DELETE FROM users;

-- ✅ 安全：指定条件
DELETE FROM users WHERE id = 5;

使用事务保护重要操作

-- 开启事务
START TRANSACTION;

-- 执行操作
UPDATE users SET salary = salary - 5000 WHERE id = 1;
UPDATE users SET salary = salary + 5000 WHERE id = 2;

-- 检查结果
SELECT * FROM users WHERE id IN (1, 2);

-- 确认无误后提交
COMMIT;

-- 如果发现错误，回滚所有操作
-- ROLLBACK;

操作前备份

-- 创建备份表
CREATE TABLE users_backup_20260416 AS SELECT * FROM users;

-- 或者使用 mysqldump 命令行工具备份
-- mysqldump -u root -p mydb users > users_backup.sql

先 SELECT 再操作

-- 先查看即将受影响的数据
SELECT * FROM users WHERE created_at < '2024-01-01';

-- 确认数据范围正确后，将 SELECT 改为 DELETE
DELETE FROM users WHERE created_at < '2024-01-01';

使用 LIMIT 测试

-- 先更新一条测试
UPDATE users SET salary = salary * 1.1 WHERE city = '北京' LIMIT 1;

-- 检查结果
SELECT * FROM users WHERE city = '北京';

-- 确认无误后去掉 LIMIT
UPDATE users SET salary = salary * 1.1 WHERE city = '北京';

启用安全模式

-- 开启安全更新模式
SET sql_safe_updates = 1;

-- 开启后，以下语句会报错（没有 WHERE 或 WHERE 未使用索引列）
UPDATE users SET age = 0;              -- ERROR 1175
DELETE FROM users;                      -- ERROR 1175

-- 使用索引列的 WHERE 条件才能执行
UPDATE users SET age = 0 WHERE id = 1; -- OK

-- 临时关闭安全模式
SET sql_safe_updates = 0;

6. 本章要点