SQL详解之DML - 数据操作语言完整指南

引言

想象一下，你是一名电商后端工程师——今天商品部门要你批量导入一万条新款数据，运营团队急着清理去年过期的优惠券，而订单系统每天还要处理上百万笔插入操作。这些需求背后，无一例外都需要 SQL 的数据操作语言（DML）。

DML 是每个后端工程师、数据分析师和运维人员都必须扎实掌握的基本功。它负责对数据库表里的数据进行增、删、改、查，是业务逻辑的“手脚”。本文将以通俗易懂的方式，深入拆解 SELECT、INSERT、UPDATE、DELETE 每个命令的核心用法，同时对比 MySQL 与 PostgreSQL 的常见差异，并带你避开生产环境中的几个大坑。

1. DML 基础扫盲

1.1 核心定义

DML（Data Manipulation Language，数据操作语言）主要操作表中的行数据，包含四类核心命令：

  • SELECT：查询数据（虽然有时被独立称为 DQL，但业界普遍把它放在 DML 里一起讲）
  • INSERT：向表中新增数据
  • UPDATE：修改已有数据
  • DELETE：删除数据

DML 只针对数据内容，不会改变表结构（改变结构是 DDL 的事）。并且，DML 操作通常支持事务回滚，具体行为取决于数据库的自动提交设置。

1.2 DML 与 DDL 的区别

很多新手会把 DML 和 DDL 搞混，这里用一张表快速厘清：

类型常用命令作用对象事务特性
DMLSELECT、INSERT、UPDATE、DELETE表中的业务数据默认需手动提交（或在自动提交模式下立即生效）
DDLCREATE、ALTER、DROP表、索引、视图等数据库结构自动提交（多数数据库如此，PostgreSQL 可调整）

简单理解：DML 动内容，DDL 动骨架。

2. SELECT —— 数据查询

对大多数系统来说，查询占了 DML 使用频率的 80% 以上。我们先从最基础的查询写起，再看 MySQL 和 PostgreSQL 各自的高频特性。

2.1 通用基础查询

无论你用哪种数据库，下面这些写法都通用：

-- 1. 查询指定列（推荐只查需要的字段，减少网络传输和数据库压力）
SELECT order_id, user_id, total_amount, create_time
FROM orders;

-- 2. 查询所有列（仅限开发调试，生产环境禁止 SELECT * ）
SELECT *
FROM employees;

-- 3. 使用别名让结果更易读
SELECT 
    u.user_name AS buyer,
    o.order_id,
    o.total_amount * 0.95 AS final_pay   -- 别名也可以给计算字段
FROM orders o
JOIN users u ON o.user_id = u.user_id;

-- 4. 去重查询：找出有过订单的用户 ID
SELECT DISTINCT user_id
FROM orders;

-- 5. 多条件查询：2024年6月已完成且金额≥100的订单
SELECT *
FROM orders
WHERE create_time BETWEEN '2024-06-01 00:00:00' AND '2024-06-30 23:59:59'
  AND pay_status = 'completed'
  AND total_amount >= 100;

2.2 MySQL 与 PostgreSQL 的差异特性

基础语法之外，两个数据库各有让人直呼“好用”的特色功能。

MySQL 专属技巧

-- 1. 分页查询（直接用 LIMIT + OFFSET）
SELECT order_id, user_id, total_amount
FROM orders
ORDER BY create_time DESC
LIMIT 10 OFFSET 20;  -- 跳过前 20 条，取 10 条

-- 2. 简易全文搜索（适合小型站内搜索）
SELECT article_id, title, publish_time
FROM articles
WHERE MATCH(title, content) AGAINST('SQL 性能优化' IN NATURAL LANGUAGE MODE);

PostgreSQL 专属技巧

-- 1. 窗口函数（不需要 GROUP BY 也能做排名、累计统计）
SELECT 
    order_id,
    user_id,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS user_latest_order_rank
FROM orders;

-- 2. 原生数组操作（非常适合存储标签、技能等数据）
SELECT employee_id, first_name, skills[1] AS primary_skill
FROM employee_profiles
WHERE 'Python' = ANY(skills);

-- 3. RETURNING 子句（在增删改后直接返回操作的行，不用再查一次）
WITH deleted_inactive_users AS (
    DELETE FROM users
    WHERE last_login < '2023-01-01'
    RETURNING user_id
)
DELETE FROM user_profiles
WHERE user_id IN (SELECT user_id FROM deleted_inactive_users);

PostgreSQL 的窗口函数和 RETURNING 功能，让它在复杂数据处理时显得尤为强大。

3. INSERT —— 数据插入

3.1 基础操作

-- 单行插入
INSERT INTO products (product_name, category, price, stock)
VALUES ('无线蓝牙耳机', '电子数码', 199.00, 200);

-- 批量插入（生产效率至少提升几十倍）
INSERT INTO products (product_name, category, price, stock)
VALUES 
    ('机械键盘青轴', '电子数码', 399.00, 150),
    ('棉麻短袖T恤', '服饰', 79.00, 500),
    ('不锈钢保温杯', '家居', 49.00, 300);

3.2 冲突处理：插入或更新

当插入的数据可能与主键或唯一索引冲突时，我们常常需要“有则更新，无则插入”，也就是 UPSERT 操作。两个数据库的写法差异很大。

MySQL：ON DUPLICATE KEY UPDATE

INSERT INTO products (product_id, product_name, price, stock)
VALUES (101, '无线蓝牙耳机升级款', 229.00, 250)
ON DUPLICATE KEY UPDATE
    product_name = VALUES(product_name),  -- 使用新值覆盖
    price = VALUES(price),
    stock = stock + VALUES(stock);       -- 结合旧值做加法

PostgreSQL：ON CONFLICT

-- 方式1：冲突时直接跳过
INSERT INTO products (product_id, product_name, price, stock)
VALUES (101, '无线蓝牙耳机升级款', 229.00, 250)
ON CONFLICT (product_id) 
DO NOTHING;

-- 方式2：冲突时更新
INSERT INTO products (product_id, product_name, price, stock)
VALUES (101, '无线蓝牙耳机升级款', 229.00, 250)
ON CONFLICT (product_id) 
DO UPDATE SET
    product_name = EXCLUDED.product_name,   -- EXCLUDED 代表试图插入的那一行
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

PostgreSQL 的 ON CONFLICT ... DO NOTHING 非常直观，而 MySQL 的方式更紧凑，各有千秋。

4. UPDATE —— 数据修改

4.1 通用基础修改

-- 1. 简单修改：给“服饰”类商品打 8 折
UPDATE products
SET price = price * 0.8
WHERE category = '服饰';

-- 2. 条件分支修改：按绩效发放年终奖
UPDATE employees
SET year_end_bonus = CASE
    WHEN performance_level = 'S' THEN 100000
    WHEN performance_level = 'A' THEN 50000
    WHEN performance_level = 'B' THEN 20000
    ELSE 10000
END
WHERE hire_date >= '2023-01-01';

4.2 跨表修改

需要根据另一张表的内容来更新数据时，写法不同。

MySQL

-- 给“电子数码”部门所有员工涨薪 5%
UPDATE employees e
INNER JOIN departments d ON e.department_id = d.department_id
SET e.salary = e.salary * 1.05
WHERE d.department_name = '电子数码';

PostgreSQL

-- 同样需求，使用 FROM 子句，并可以用 RETURNING 返回结果
UPDATE employees
SET salary = employees.salary * 1.05
FROM departments
WHERE employees.department_id = departments.department_id
  AND departments.department_name = '电子数码'
RETURNING employee_id, first_name, salary;

5. DELETE —— 数据删除

5.1 通用基础删除

-- 1. 删除所有已取消的订单
DELETE FROM orders
WHERE order_status = 'cancelled';

-- 2. 清空整张表（保留表结构，速度极快）
TRUNCATE TABLE temp_cart_data;

5.2 跨表删除

同样，MySQL 用 JOIN 而 PostgreSQL 用 USING。

MySQL

-- 删除“已关闭部门”下的所有员工
DELETE e FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_status = 'closed';

PostgreSQL

-- 删除“已关闭部门”下的所有员工，并返回被删记录
DELETE FROM employees
USING departments
WHERE employees.department_id = departments.department_id
  AND departments.department_status = 'closed'
RETURNING employee_id, first_name;

5.3 大表分批删除（防止锁表）

要删除几百万行数据时，绝对不能一条 DELETE 直接全量执行，否则可能锁死整张表。

-- MySQL：循环删除 2022 年之前的日志，每次只删 1 万条
DELETE FROM logs
WHERE log_time < '2022-01-01 00:00:00'
LIMIT 10000;

-- 写一个简单脚本，循环执行直到受影响行数为 0

PostgreSQL 没有 LIMIT 删除语法，但可以通过子查询配合主键来分批：

DELETE FROM logs
WHERE log_id IN (
    SELECT log_id FROM logs
    WHERE log_time < '2022-01-01 00:00:00'
    LIMIT 10000
);

6. MySQL vs PostgreSQL DML 差异速览

功能MySQLPostgreSQL
分页查询LIMIT + OFFSETLIMIT + OFFSET（写法一致）
插入冲突处理ON DUPLICATE KEY UPDATEON CONFLICT（更灵活）
修改/删除后返回数据不支持，需再查一次RETURNING 子句原生支持
数组字段操作通常用 JSON 模拟原生数组类型和函数支持
全文搜索MATCH ... AGAINSTto_tsvector / to_tsquery
跨表增删改的关联写法INSERT/UPDATE/DELETE ... JOININSERT/UPDATE/DELETE ... USING

7. DML 性能优化小贴士

7.1 查询优化

-- 1. 使用 EXPLAIN 分析执行计划
-- MySQL
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND pay_status = 'completed';
-- PostgreSQL（ANALYZE 会真实执行并给出实际耗时）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND pay_status = 'completed';

-- 2. 永远不要用 SELECT * 获取所有字段，只拿你需要的
-- 3. 为 WHERE、JOIN、ORDER BY 的高频字段建立索引
CREATE INDEX idx_orders_user_pay ON orders(user_id, pay_status);

7.2 批量操作优化

-- 1. 批量插入 > 逐行循环插入
-- 2. MySQL 大批量插入时，可以暂时关闭自动提交，集中一次性提交
SET autocommit = 0;
START TRANSACTION;
-- ... 批量插入
COMMIT;
SET autocommit = 1;

-- 3. PostgreSQL 用 COPY 命令从 CSV 文件导入，速度最快
-- COPY products FROM '/path/to/products.csv' WITH (FORMAT csv, HEADER true);

相关教程

1. 执行 DELETE、TRUNCATE 或大批量 UPDATE 前，**一定先做好备份！** 2. 时间消耗大的 DML 操作，尽量安排在业务低峰期执行。

总结

DML 是连接业务与数据库的桥梁，是每一位后端工程师的“基本功”。MySQL 和 PostgreSQL 在 DML 上各有特色：

  • MySQL：语法简洁，批量插入性能突出，适合快速开发的中小型应用。
  • PostgreSQL：标准兼容性强，内置窗口函数、数组、RETURNING 等advanced-features，适合数据密集和分析型场景。

掌握两套常用写法，再配合索引规划和分批执行策略，你就能写出既高效又安全的 DML 语句，从容应对各种业务需求。