MySQL DML 数据操作

INSERT 插入#

单行写法：插入单行 INSERT INTO <表名> (<列1>, <列2>) VALUES (<值1>, <值2>);

-- 插入一条用户记录
INSERT INTO users (username, email, age) VALUES ('zhangsan', 'zs@example.com', 25);

单行写法：插入所有列 INSERT INTO <表名> VALUES (<值1>, <值2>, ...);

-- 按列顺序插入所有列
INSERT INTO users VALUES (NULL, 'lisi', 'ls@example.com', 30, 0.00, 1, NOW(), NOW());

换行写法：插入多行 INSERT INTO <表名> (<列>) VALUES (<值1>), (<值2>), (<值3>);

-- 批量插入多行数据
INSERT INTO users (username, email) VALUES
  ('user1', 'u1@example.com'),
  ('user2', 'u2@example.com'),
  ('user3', 'u3@example.com');

单行写法：插入查询结果 INSERT INTO <目标表> SELECT * FROM <源表> [WHERE <条件>];

-- 将活跃用户插入备份表
INSERT INTO active_users_backup SELECT * FROM users WHERE status = 1;

换行写法：插入时使用 ON DUPLICATE KEY UPDATE INSERT INTO <表名> (<列>) VALUES (<值>) ON DUPLICATE KEY UPDATE <列>=<值>;

-- 主键或唯一键冲突时更新
INSERT INTO users (id, username, email) VALUES (1, 'zhangsan', 'new@example.com')
ON DUPLICATE KEY UPDATE email = VALUES(email), updated_at = NOW();

换行写法：INSERT … IGNORE 忽略冲突 INSERT IGNORE INTO <表名> (<列>) VALUES (<值>);

-- 主键冲突时忽略不报错
INSERT IGNORE INTO users (id, username) VALUES (1, 'zhangsan');

单行写法：REPLACE 替换插入 REPLACE INTO <表名> (<列>) VALUES (<值>);

-- 冲突时先删除旧行再插入新行
REPLACE INTO users (id, username, email) VALUES (1, 'zhangsan', 'new@example.com');

UPDATE 更新#

单行写法：更新单列 UPDATE <表名> SET <列>=<值> WHERE <条件>;

-- 更新指定用户的年龄
UPDATE users SET age = 26 WHERE id = 1;

单行写法：更新多列 UPDATE <表名> SET <列1>=<值1>, <列2>=<值2> WHERE <条件>;

-- 同时更新多个字段
UPDATE users SET age = 26, status = 2 WHERE id = 1;

单行写法：基于表达式更新 UPDATE <表名> SET <列>=<表达式> WHERE <条件>;

-- 所有用户余额增加 10%
UPDATE users SET balance = balance * 1.10 WHERE status = 1;

换行写法：基于 JOIN 更新 UPDATE <表1> JOIN <表2> ON <条件> SET <列>=<值>;

-- 关联订单表更新用户总消费
UPDATE users u
JOIN (SELECT user_id, SUM(total_amount) AS total FROM orders GROUP BY user_id) o
  ON u.id = o.user_id
SET u.balance = u.balance - o.total;

单行写法：使用 LIMIT 限制更新行数 UPDATE <表名> SET <列>=<值> WHERE <条件> LIMIT <数量>;

-- 仅更新前 100 条匹配记录
UPDATE users SET status = 0 WHERE last_login < '2024-01-01' LIMIT 100;

单行写法：使用 CASE 条件更新 UPDATE <表名> SET <列> = CASE <条件列> WHEN <值1> THEN <结果1> ELSE <结果2> END WHERE <条件>;

-- 根据不同状态批量更新
UPDATE users SET status = CASE age
  WHEN 18 THEN 1
  WHEN 30 THEN 2
  ELSE status
END WHERE age IN (18, 30);

DELETE 删除#

单行写法：按条件删除 DELETE FROM <表名> WHERE <条件>;

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

单行写法：限制删除行数 DELETE FROM <表名> WHERE <条件> LIMIT <数量>;

-- 仅删除前 100 条匹配记录
DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 100;

换行写法：基于 JOIN 删除 DELETE <别名> FROM <表1> <别名> JOIN <表2> ON <条件> WHERE <条件>;

-- 删除没有订单的用户
DELETE u FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

换行写法：多表关联删除 DELETE <表1>, <表2> FROM <表1> JOIN <表2> ON <条件> WHERE <条件>;

-- 同时删除用户和其订单
DELETE u, o FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;

单行写法：删除所有数据 DELETE FROM <表名>;

-- 删除全表数据（保留自增ID计数）
DELETE FROM users;

UPSERT 与冲突处理#

换行写法：VALUES() 函数引用插入值 INSERT INTO <表> (<列>) VALUES (<值>) ON DUPLICATE KEY UPDATE <列>=VALUES(<列>);

-- 冲突时引用待插入值更新
INSERT INTO counters (id, count) VALUES (1, 1)
ON DUPLICATE KEY UPDATE count = count + VALUES(count);

换行写法：MySQL 8.0.20+ 使用别名引用 INSERT INTO <表> (<列>) VALUES (<值>) AS <别名> ON DUPLICATE KEY UPDATE <列>=<别名>.<列>;

-- 8.0.20+ 使用别名替代 VALUES()
INSERT INTO counters (id, count) VALUES (1, 1) AS new
ON DUPLICATE KEY UPDATE count = counters.count + new.count;

事务控制#

单行写法：开启事务 START TRANSACTION; 或 BEGIN;

-- 开启事务
START TRANSACTION;

换行写法：提交事务 COMMIT;

-- 提交事务持久化变更
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

单行写法：回滚事务 ROLLBACK;

-- 回滚撤销事务变更
ROLLBACK;

单行写法：设置保存点 SAVEPOINT <保存点名>;

-- 设置保存点
SAVEPOINT sp1;

单行写法：回滚到保存点 ROLLBACK TO <保存点名>;

-- 回滚到指定保存点
ROLLBACK TO sp1;

单行写法：查看隔离级别 SELECT @@transaction_isolation;

-- 查看当前事务隔离级别
SELECT @@transaction_isolation;

单行写法：设置隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL <级别>;

-- 设置会话隔离级别为读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;