第三章 数据增删改（DML）

DML（Data Manipulation Language）用来操作表中的数据——插入、更新、删除。学完这一章，你就能往数据库里"写"数据了。

---

准备工作

本章所有示例基于以下两张表。如果你还没有创建，请先执行：

CREATE DATABASE school;
\c school

CREATE TABLE students (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    email       VARCHAR(100) UNIQUE,
    age         INTEGER CHECK (age BETWEEN 1 AND 150),
    enrolled_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE courses (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id  INTEGER REFERENCES students(id) ON DELETE CASCADE,
    course_name VARCHAR(100) NOT NULL,
    score       NUMERIC(5,2)
);

---

3.1 插入数据（INSERT）

插入单行

INSERT INTO students (name, email, age)
VALUES ('张三', 'zhangsan@example.com', 20);

语法拆解：

• INSERT INTO 表名 (列1, 列2, ...)：指定要插入的列
• VALUES (值1, 值2, ...)：对应的值

没有出现在列名列表中的列，会自动使用默认值（如 id 自增、enrolled_at 默认 NOW()）。

插入多行

INSERT INTO students (name, email, age) VALUES
    ('李四', 'lisi@example.com', 22),
    ('王五', 'wangwu@example.com', 21),
    ('赵六', 'zhaoliu@example.com', 23),
    ('孙七', 'sunqi@example.com', 20),
    ('周八', 'zhouba@example.com', 24);

一次性插入多行，比逐行插入高效得多。

插入并返回结果（RETURNING）

这是 PostgreSQL 的特色功能，插入后立刻返回刚插入的数据：

INSERT INTO students (name, email, age)
VALUES ('钱九', 'qianjiu@example.com', 19)
RETURNING id, name;

输出：

 id |  name
----+-------
  7 | 钱九

这在应用开发中非常有用——插入一行后，立刻拿到自增的 id，不需要再查一次。

从另一张表插入（INSERT ... SELECT）

INSERT INTO archive_students (name, email, age)
SELECT name, email, age FROM students WHERE enrolled_at < '2024-01-01';

把符合条件的数据从一张表复制到另一张表。

---

3.2 更新数据（UPDATE）

基本语法

UPDATE students
SET age = 23
WHERE name = '李四';

语法拆解：

• UPDATE 表名：指定要更新的表
• SET 列名 = 新值：要修改的列和新值
• WHERE 条件：指定更新哪些行

同时更新多列

UPDATE students
SET age = 25, email = 'lisi_new@example.com'
WHERE name = '李四';

基于计算更新

-- 所有人年龄加 1
UPDATE students SET age = age + 1;

-- 课程分数打九折
UPDATE courses SET score = score * 0.9 WHERE score IS NOT NULL;

UPDATE ... RETURNING

和 INSERT 一样，UPDATE 也支持 RETURNING：

UPDATE students
SET age = 26
WHERE name = '李四'
RETURNING id, name, age;

危险操作警告

-- 没有 WHERE 条件 = 更新所有行！
UPDATE students SET age = 18;   -- 全表所有人年龄变成 18

黄金法则：写 UPDATE 和 DELETE 之前，先用相同的 WHERE 条件跑一次 SELECT，确认影响的行是否正确。

-- 第一步：先看看会影响哪些行
SELECT * FROM students WHERE age > 25;

-- 第二步：确认没问题后再更新
UPDATE students SET is_active = false WHERE age > 25;

---

3.3 删除数据（DELETE）

基本语法

DELETE FROM students WHERE name = '钱九';

DELETE ... RETURNING

DELETE FROM students WHERE name = '钱九' RETURNING *;

删除所有行

-- 方式一：DELETE（逐行删除，可以回滚，有日志）
DELETE FROM students;

-- 方式二：TRUNCATE（瞬间清空，更快，但不可在事务中回滚）
TRUNCATE TABLE students;

-- TRUNCATE 同时重置自增序列
TRUNCATE TABLE students RESTART IDENTITY;

对比	DELETE FROM 表	TRUNCATE TABLE 表
速度	慢（逐行删除）	快（直接清空）
可加 WHERE	是	否（只能清空整张表）
触发触发器	是	否
事务回滚	可以	PostgreSQL 中也可以（与 MySQL 不同）
重置自增	不会	可选 RESTART IDENTITY

---

3.4 UPSERT — 插入或更新

实际开发中，经常遇到"如果数据已存在就更新，不存在就插入"的场景。PostgreSQL 通过 ON CONFLICT 实现：

INSERT INTO students (name, email, age)
VALUES ('张三', 'zhangsan@example.com', 21)
ON CONFLICT (email) DO UPDATE
SET age = EXCLUDED.age;

解读：

• 尝试插入一行数据
• 如果 email 已存在（冲突了），就执行 DO UPDATE
• EXCLUDED 是一个特殊关键字，代表"本来要插入的那行数据"

如果冲突时什么都不想做：

INSERT INTO students (name, email, age)
VALUES ('张三', 'zhangsan@example.com', 21)
ON CONFLICT (email) DO NOTHING;

---

3.5 实战练习

-- 确保在 school 数据库中
\c school

-- 1. 批量插入学生数据
INSERT INTO students (name, email, age) VALUES
    ('张三', 'zhangsan@example.com', 20),
    ('李四', 'lisi@example.com', 22),
    ('王五', 'wangwu@example.com', 21),
    ('赵六', 'zhaoliu@example.com', 23),
    ('孙七', 'sunqi@example.com', 20);

-- 2. 插入选课数据
INSERT INTO courses (student_id, course_name, score) VALUES
    (1, '数据库原理', 85.5),
    (1, '操作系统', 90.0),
    (2, '数据库原理', 78.0),
    (2, '数据结构', 92.5),
    (3, '操作系统', 88.0),
    (3, '数据结构', 76.0),
    (4, '数据库原理', 95.0),
    (5, '操作系统', 82.5);

-- 3. 查看插入的数据
SELECT * FROM students;
SELECT * FROM courses;

-- 4. 更新：赵六改名赵大六
UPDATE students SET name = '赵大六' WHERE name = '赵六' RETURNING *;

-- 5. 更新：数据库原理全体加 5 分
UPDATE courses SET score = score + 5 WHERE course_name = '数据库原理';

-- 6. 删除：删除孙七
DELETE FROM students WHERE name = '孙七' RETURNING *;

-- 7. 验证级联删除：孙七的选课记录也应该被自动删除了
SELECT * FROM courses;

-- 8. UPSERT：张三换邮箱了
INSERT INTO students (name, email, age)
VALUES ('张三', 'zhangsan@example.com', 20)
ON CONFLICT (email) DO UPDATE
SET email = 'zhangsan_new@example.com'
RETURNING *;

---

本章小结

操作	语句	要点
插入	INSERT INTO ... VALUES	支持多行插入、RETURNING 返回结果
更新	UPDATE ... SET ... WHERE	一定要写 WHERE，否则更新全表
删除	DELETE FROM ... WHERE	一定要写 WHERE，否则删除全部
清空	TRUNCATE TABLE	比 DELETE 快，可重置自增
插入或更新	INSERT ... ON CONFLICT DO UPDATE	处理数据冲突的利器

核心原则：执行 UPDATE/DELETE 之前，先用 SELECT + 相同 WHERE 条件确认影响范围。

---

下一章：第四章 查询语句（SELECT）— 最核心的部分