数据操作

INSERT 插入数据

基本插入

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

-- 插入多行
INSERT INTO users (name, email, age)
VALUES
  ('Bob', 'bob@example.com', 32),
  ('Charlie', 'charlie@example.com', 25),
  ('Diana', 'diana@example.com', 30);

-- 插入所有列时可省略列名（不推荐）
INSERT INTO users VALUES (1, 'Alice', 'alice@example.com', 28);

INSERT … SELECT

从查询结果插入数据：

-- 将活跃用户复制到归档表
INSERT INTO active_users_archive (id, name, email, archived_at)
SELECT id, name, email, CURRENT_TIMESTAMP
FROM users
WHERE last_login > CURRENT_DATE - INTERVAL '90 days';

-- 跨表同步
INSERT INTO products_backup (id, name, price)
SELECT id, name, price FROM products WHERE updated_at > '2024-01-01';

-- 创建汇总表
INSERT INTO monthly_report (month, total_sales, order_count)
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount) AS total_sales,
  COUNT(*) AS order_count
FROM orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
GROUP BY DATE_TRUNC('month', order_date);

INSERT 特殊用法

-- 从 DEFAULT 值插入
INSERT INTO users (name, email) VALUES ('Eve', 'eve@example.com');
-- age 列使用 DEFAULT 值

-- 显式使用 DEFAULT
INSERT INTO users (name, email, age) VALUES ('Eve', 'eve@example.com', DEFAULT);

-- PostgreSQL: INSERT RETURNING（返回插入的行）
INSERT INTO users (name, email) VALUES ('Frank', 'frank@example.com')
RETURNING id, name;

-- MySQL: 获取自增 ID
INSERT INTO users (name, email) VALUES ('Frank', 'frank@example.com');
SELECT LAST_INSERT_ID();

-- SQL Server: OUTPUT 子句
INSERT INTO users (name, email)
OUTPUT INSERTED.id, INSERTED.name
VALUES ('Frank', 'frank@example.com');

UPDATE 更新数据

基本更新

-- 更新单列
UPDATE users SET status = 'active' WHERE id = 1;

-- 更新多列
UPDATE users SET name = 'Alice Smith', email = 'alice.smith@example.com' WHERE id = 1;

-- 基于条件批量更新
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

--  不加 WHERE 会更新全表！
UPDATE users SET status = 'active';  -- 所有用户都变为 active

多表更新

-- MySQL: 多表 UPDATE
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.discount = 0.1
WHERE c.vip_level = 'gold';

-- PostgreSQL: UPDATE ... FROM
UPDATE orders o
SET discount = 0.1
FROM customers c
WHERE o.customer_id = c.id AND c.vip_level = 'gold';

-- SQL Server: UPDATE ... FROM
UPDATE o
SET discount = 0.1
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.vip_level = 'gold';

UPDATE 与子查询

-- 用子查询更新
UPDATE employees e
SET salary = (
  SELECT AVG(salary) FROM employees WHERE department = e.department
)
WHERE salary < (
  SELECT AVG(salary) FROM employees WHERE department = e.department
) * 0.8;

-- PostgreSQL: UPDATE RETURNING
UPDATE users SET login_count = login_count + 1 WHERE id = 1
RETURNING id, login_count;

-- SQL Server: UPDATE OUTPUT
UPDATE users SET login_count = login_count + 1
OUTPUT INSERTED.id, INSERTED.login_count
WHERE id = 1;

CASE WHEN 更新

-- 根据条件更新不同值
UPDATE products
SET price = CASE
  WHEN category = 'electronics' THEN price * 0.9
  WHEN category = 'clothing' THEN price * 0.7
  ELSE price * 0.95
END
WHERE clearance = true;

DELETE 删除数据

-- 条件删除
DELETE FROM users WHERE status = 'inactive' AND last_login < '2023-01-01';

-- 删除所有数据（保留表结构，记录日志，可回滚）
DELETE FROM temp_data;

--  不加 WHERE 会删除全表！
DELETE FROM users;  -- 删除所有行

-- TRUNCATE: 更快的清空方式（DDL，不可回滚，重置自增）
TRUNCATE TABLE temp_data;

-- 多表删除（MySQL）
DELETE t1, t2 FROM table1 t1
JOIN table2 t2 ON t1.id = t2.ref_id
WHERE t1.status = 'expired';

-- PostgreSQL: DELETE RETURNING
DELETE FROM users WHERE status = 'inactive'
RETURNING id, name;  -- 返回被删除的行

DELETE vs TRUNCATE

UPSERT / MERGE

当记录存在时更新，不存在时插入。

MySQL: ON DUPLICATE KEY UPDATE

INSERT INTO users (id, name, email, login_count)
VALUES (1, 'Alice', 'alice@new.com', 1)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email),
  login_count = login_count + 1;

-- MySQL 8.0.20+ 推荐写法（VALUES() 已弃用）
INSERT INTO users (id, name, email, login_count)
VALUES (1, 'Alice', 'alice@new.com', 1)
AS new_vals
ON DUPLICATE KEY UPDATE
  name = new_vals.name,
  email = new_vals.email,
  login_count = login_count + 1;

PostgreSQL: ON CONFLICT

-- 基于主键/唯一约束冲突
INSERT INTO users (id, name, email, login_count)
VALUES (1, 'Alice', 'alice@new.com', 1)
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  email = EXCLUDED.email,
  login_count = users.login_count + 1;

-- 冲突时什么都不做
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@new.com')
ON CONFLICT (id) DO NOTHING;

-- 基于唯一约束冲突
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

SQL:2003 MERGE 语句

-- SQL Server / Oracle
MERGE INTO users AS target
USING (VALUES (1, 'Alice', 'alice@new.com')) AS source (id, name, email)
ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET name = source.name, email = source.email
WHEN NOT MATCHED THEN
  INSERT (id, name, email) VALUES (source.id, source.name, source.email);

-- 带条件的 MERGE
MERGE INTO inventory AS target
USING daily_shipments AS source
ON target.product_id = source.product_id
WHEN MATCHED AND target.quantity < source.min_stock THEN
  UPDATE SET quantity = quantity + source.quantity, last_restock = GETDATE()
WHEN MATCHED THEN
  UPDATE SET quantity = quantity + source.quantity
WHEN NOT MATCHED THEN
  INSERT (product_id, quantity) VALUES (source.product_id, source.quantity);

批量操作

批量插入优化

--  多行 INSERT（一次网络往返）
INSERT INTO logs (user_id, action, timestamp) VALUES
  (1, 'login', '2024-01-01 08:00:00'),
  (1, 'view', '2024-01-01 08:05:00'),
  (2, 'login', '2024-01-01 09:00:00');

--  逐行 INSERT（多次网络往返）
INSERT INTO logs (user_id, action, timestamp) VALUES (1, 'login', '2024-01-01 08:00:00');
INSERT INTO logs (user_id, action, timestamp) VALUES (1, 'view', '2024-01-01 08:05:00');

-- PostgreSQL: COPY 命令（最快的大批量导入）
COPY users (name, email) FROM '/data/users.csv' WITH (FORMAT csv, HEADER true);

-- MySQL: LOAD DATA INFILE
LOAD DATA INFILE '/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

批量更新策略

-- 使用 CASE WHEN 批量更新
UPDATE products
SET price = CASE id
  WHEN 1 THEN 99.99
  WHEN 2 THEN 149.99
  WHEN 3 THEN 199.99
END
WHERE id IN (1, 2, 3);

-- 使用临时表关联更新
CREATE TEMPORARY TABLE price_updates (product_id INT, new_price DECIMAL(10,2));
INSERT INTO price_updates VALUES (1, 99.99), (2, 149.99), (3, 199.99);

UPDATE products p
SET price = pu.new_price
FROM price_updates pu
WHERE p.id = pu.product_id;

事务

事务是数据库操作的逻辑单元，具有 ACID 特性：

基本事务操作

-- 标准事务语法
BEGIN;  -- 或 START TRANSACTION (MySQL)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 提交

-- 回滚
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 发现错误...
ROLLBACK;  -- 撤销所有操作

-- 保存点（部分回滚）
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 第二步有问题，回滚到保存点
ROLLBACK TO SAVEPOINT after_debit;
-- 可以继续其他操作
UPDATE accounts SET balance = balance + 100 WHERE id = 3;
COMMIT;

隔离级别

-- 设置隔离级别
-- MySQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

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

-- PostgreSQL
SHOW transaction_isolation;

四种隔离级别对比

-- 脏读示例（READ UNCOMMITTED）
-- 事务 A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 未提交

-- 事务 B（READ UNCOMMITTED）
SELECT balance FROM accounts WHERE id = 1;  -- 看到未提交的修改（脏读）

-- 事务 A
ROLLBACK;  -- 事务 B 读到的数据是无效的

-- 不可重复读示例（READ COMMITTED）
-- 事务 A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 返回 1000

-- 事务 B
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;

-- 事务 A（同一事务中再次读取）
SELECT balance FROM accounts WHERE id = 1;  -- 返回 900（两次读取不一致）

-- 幻读示例（REPEATABLE READ）
-- 事务 A
BEGIN;
SELECT COUNT(*) FROM accounts WHERE balance > 500;  -- 返回 10

-- 事务 B
INSERT INTO accounts (id, balance) VALUES (99, 600);
COMMIT;

-- 事务 A
SELECT COUNT(*) FROM accounts WHERE balance > 500;  -- 返回 11（幻读）

各数据库默认隔离级别

注意：MySQL InnoDB 在 REPEATABLE READ 下通过 MVCC + Next-Key Lock 在很大程度上避免了幻读，这是其独特之处。

锁机制

锁类型

-- 共享锁（读锁）
-- MySQL
SELECT * FROM users LOCK IN SHARE MODE;

-- PostgreSQL
SELECT * FROM users FOR SHARE;

-- 排他锁（写锁）
-- MySQL / PostgreSQL
SELECT * FROM users FOR UPDATE;

-- PostgreSQL: 无冲突的排他锁
SELECT * FROM users FOR NO KEY UPDATE;

-- PostgreSQL: 行级共享锁（不阻塞 KEY UPDATE）
SELECT * FROM users FOR KEY SHARE;

行锁与表锁

-- 行级锁（InnoDB 默认）
-- 只锁住匹配的行
UPDATE users SET name = 'Alice' WHERE id = 1;

-- 表级锁
-- MySQL
LOCK TABLES users READ;   -- 读锁
LOCK TABLES users WRITE;  -- 写锁
UNLOCK TABLES;

-- PostgreSQL: 显式表锁
LOCK TABLE users IN SHARE MODE;
LOCK TABLE users IN EXCLUSIVE MODE;

-- 乐观锁（应用层实现）
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- 检查版本号
-- 如果 affected_rows = 0，说明被其他事务修改了

死锁

-- 死锁场景
-- 事务 A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 锁住 id=1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待 id=2

-- 事务 B（同时）
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- 锁住 id=2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- 等待 id=1
-- 死锁！数据库会检测并回滚其中一个事务

-- 避免死锁的策略：
-- 1. 按固定顺序访问表和行
-- 2. 保持事务简短
-- 3. 使用低隔离级别
-- 4. 添加合理的索引（避免锁升级）

小结

• INSERT ... SELECT 适合数据迁移和同步，RETURNING 子句可返回操作后的数据
• UPDATE 务必带 WHERE，多表更新语法因数据库而异
• DELETE 逐行删除可回滚，TRUNCATE 快速但不可回滚
• UPSERT 各数据库语法不同：MySQL 用 ON DUPLICATE KEY，PostgreSQL 用 ON CONFLICT
• 事务的 ACID 特性是数据一致性的保障，选择合适的隔离级别平衡一致性与性能
• 行锁优于表锁，乐观锁适合低冲突场景，避免死锁需按固定顺序访问资源