摘要

本文为 PostgreSQL 从入门到精通系列第四篇，详细讲解 PostgreSQL 中最核心的增删改查（CRUD）操作。通过大量实战 SQL 示例，涵盖插入、查询、更新、删除的完整语法，并针对实际开发场景，深入分析索引使用、批量操作、避免全表扫描等性能优化关键点，帮助开发者写出高效、安全的 SQL 语句。

---

前言

掌握了数据库和表的创建后，我们就进入了数据库操作的核心 ——增删改查（CRUD）。这是日常开发中使用最频繁的操作，也是衡量一个开发者数据库水平的基础。

本文将通过实战案例，带你全面掌握：

• INSERT：如何高效地插入数据
• SELECT：如何灵活地查询数据（条件、排序、分页、聚合、连接）
• UPDATE：如何安全地更新数据
• DELETE：如何谨慎地删除数据
• 性能优化：如何让你的 CRUD 操作更快、更稳定

---

一、插入数据（INSERT）

1.1 插入单行数据

-- 插入一行数据到 users 表
INSERT INTO users (username, email, password)
VALUES ('zhangsan', 'zhangsan@example.com', '123456');

1.2 插入多行数据（批量插入）

-- 一次性插入多行数据，性能远高于多次单条插入
INSERT INTO users (username, email, password)
VALUES 
('lisi', 'lisi@example.com', '654321'),
('wangwu', 'wangwu@example.com', 'abcdef'),
('zhaoliu', 'zhaoliu@example.com', 'fedcba');

1.3 插入查询结果

-- 将查询结果插入到新表或现有表中
CREATE TABLE users_backup (LIKE users); -- 创建一个与 users 结构相同的备份表

INSERT INTO users_backup
SELECT * FROM users WHERE id > 2; -- 将 id > 2 的用户数据插入到备份表

---

二、查询数据（SELECT）

查询是 CRUD 中最复杂、最灵活的操作，也是性能优化的重点。

2.1 基础查询

-- 查询所有列（不推荐在生产环境使用 *）
SELECT * FROM users;

-- 查询指定列
SELECT id, username, email FROM users;

2.2 条件查询（WHERE）

-- 查询用户名是 'zhangsan' 的用户
SELECT * FROM users WHERE username = 'zhangsan';

-- 查询 id 大于 2 且邮箱以 '@example.com' 结尾的用户
SELECT * FROM users WHERE id > 2 AND email LIKE '%@example.com';

2.3 排序（ORDER BY）

-- 按创建时间降序排列用户
SELECT * FROM users ORDER BY created_at DESC;

-- 按用户名升序，再按创建时间降序排列
SELECT * FROM users ORDER BY username ASC, created_at DESC;

2.4 分页（LIMIT & OFFSET）

-- 查询前 10 条用户数据
SELECT * FROM users LIMIT 10;

-- 分页查询，第 2 页，每页 10 条（OFFSET 从 0 开始）
SELECT * FROM users LIMIT 10 OFFSET 10;

2.5 聚合查询（GROUP BY & HAVING）

-- 统计每个作者发布的文章数量
SELECT author_id, COUNT(*) AS article_count
FROM articles
GROUP BY author_id
HAVING COUNT(*) > 1; -- 只显示发布文章数大于 1 的作者

2.6 多表连接查询（JOIN）

-- 查询所有文章及其作者信息（内连接）
SELECT a.title, u.username, a.created_at
FROM articles a
JOIN users u ON a.author_id = u.id;

-- 查询所有用户及其发布的文章（左连接，即使没有文章也会显示用户）
SELECT u.username, a.title
FROM users u
LEFT JOIN articles a ON u.id = a.author_id;

---

三、更新数据（UPDATE）

3.1 更新单行数据

-- 更新用户名为 'zhangsan' 的用户的邮箱
UPDATE users
SET email = 'new_zhangsan@example.com'
WHERE username = 'zhangsan';

3.2 更新多行数据

-- 将所有用户的密码更新为默认值（示例，实际操作需谨慎）
UPDATE users
SET password = 'default_password';

3.3 使用其他表的数据更新

-- 将用户表的用户名更新为其邮箱的前缀部分
UPDATE users
SET username = SPLIT_PART(email, '@', 1);

---

四、删除数据（DELETE）

4.1 删除单行数据

-- 删除用户名为 'zhaoliu' 的用户
DELETE FROM users
WHERE username = 'zhaoliu';

4.2 删除多行数据

-- 删除所有创建时间在 2023 年之前的文章
DELETE FROM articles
WHERE created_at < '2023-01-01';

4.3 清空整个表（TRUNCATE）

-- 快速清空表数据（不可回滚，谨慎使用！）
TRUNCATE TABLE users_backup;

---

五、CRUD 性能优化注意事项

5.1 为查询字段建立索引

-- 为经常用于 WHERE、JOIN、ORDER BY 的字段建立索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_articles_author_id ON articles(author_id);

5.2 避免使用 SELECT *

明确指定需要查询的列，减少数据传输量和内存消耗。

5.3 合理使用 LIMIT

对于大表查询，务必使用 LIMIT 限制返回行数，避免全表扫描导致数据库压力过大。

5.4 批量操作优于单条操作

无论是插入、更新还是删除，批量操作（如一次插入 1000 条）比循环执行单条操作效率高得多。

5.5 使用事务保证数据一致性

对于需要同时修改多张表的操作，使用事务确保要么全部成功，要么全部失败。

BEGIN; -- 开始事务
UPDATE users SET ...;
UPDATE articles SET ...;
COMMIT; -- 提交事务
-- ROLLBACK; -- 如果出错，回滚事务

5.6 定期分析执行计划

使用 EXPLAIN 或 EXPLAIN ANALYZE 分析慢查询的执行计划，找出性能瓶颈。

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'zhangsan@example.com';

---

总结

增删改查是数据库操作的基石，掌握其语法和性能优化技巧对于开发高效、稳定的应用至关重要。本文通过大量实战示例，详细讲解了 PostgreSQL 的 CRUD 操作，并提供了实用的性能优化建议。在实际开发中，要养成良好的编程习惯，多思考如何写出更高效、更安全的 SQL 语句。下一篇，我们将深入探讨 PostgreSQL 的高级查询特性。