PostgreSQL SQL 五大分类详细说明文档（企业级标准版）

以下是专为 Java 后端开发者（尤其是使用 Spring Boot 的团队）精心编写的 PostgreSQL SQL 分类详细说明文档，全面覆盖 SQL 的五大标准分类（DDL、DML、DQL、TCL、DCL），每类均包含：

• ✅ 定义与作用
• ✅ 核心内容与企业级必要性
• ✅ 标准、完整、带中文注释的实战示例
• ✅ 团队落地建议与最佳实践

全文采用结构化、注释式、可直接用于团队培训的写法，助你推动数据库规范落地。

---

📜 PostgreSQL SQL 五大分类详细说明文档（企业级标准版）

适用对象：Java 开发、后端架构师、测试工程师、DBA 协作团队
目标：统一团队对 SQL 的认知，规范开发行为，提升数据操作安全性、可维护性与性能。

---

一、SQL 总体分类说明（五大类）

PostgreSQL 遵循 ANSI SQL 标准，将 SQL 语句分为以下五类：

类别	全称	中文名称	作用	是否影响数据内容
DDL	Data Definition Language	数据定义语言	定义/修改数据库结构（表、索引、视图等）	❌ 不影响数据
DML	Data Manipulation Language	数据操作语言	增删改数据（插入、更新、删除）	✅ 影响数据内容
DQL	Data Query Language	数据查询语言	查询数据（SELECT）	✅ 仅读取数据
TCL	Transaction Control Language	事务控制语言	控制事务的提交、回滚	✅ 影响数据持久性
DCL	Data Control Language	数据控制语言	控制用户权限与访问安全	❌ 影响权限，不影响数据

✅ 重要提示：
在企业开发中，DQL、DML、TCL 是 Java 业务代码中最常接触的三类，而 DDL 通常由 DBA 或 Flyway/Liquibase 管理，DCL 由运维或安全团队控制。

---

二、DDL（Data Definition Language）—— 数据库结构的“建筑师”

✅ 定义与作用：

用于创建、修改、删除数据库对象（如表、索引、视图、序列、函数等），不操作数据本身。

🔑 核心语句：

• CREATE：创建对象
• ALTER：修改对象结构
• DROP：删除对象
• TRUNCATE：清空表数据（保留结构）

💡 企业必要性：

• 所有表结构变更必须通过版本化脚本（如 Flyway）管理，禁止直接在生产库执行 DDL。
• 使用 TRUNCATE 替代 DELETE FROM 清空大表，性能提升 100 倍。
• 索引是查询性能的生命线，必须显式创建并命名。

---

✅ 标准使用示例（带中文注释）

-- 📌 示例1：创建用户表（企业标准模板）
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,                      -- ✅ 自增主键（推荐用于整型主键）
    uuid UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),  -- ✅ 业务主键（分布式系统推荐，避免ID暴露）
    username VARCHAR(50) UNIQUE NOT NULL,          -- ✅ 用户名唯一，非空
    email VARCHAR(100) UNIQUE NOT NULL,            -- ✅ 邮箱唯一，非空
    password_hash TEXT NOT NULL,                   -- ✅ 密码加密后存储
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'banned')),  -- ✅ 枚举约束，避免脏数据
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- ✅ 创建时间，自动填充
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- ✅ 更新时间，自动填充
    metadata JSONB DEFAULT '{}'::JSONB             -- ✅ 扩展字段，存储动态配置（如用户偏好），支持索引
);

-- 📌 示例2：为常用查询字段创建索引（必须！）
CREATE INDEX idx_users_email ON users (email);                     -- ✅ 加速按邮箱登录查询
CREATE INDEX idx_users_status ON users (status);                   -- ✅ 加速状态筛选
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);     -- ✅ JSONB 必须用 GIN 索引，否则查询极慢！

-- 📌 示例3：创建带外键的订单表（保证数据一致性）
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,  -- ✅ 外键约束，用户删除则订单级联删除
    order_no VARCHAR(50) UNIQUE NOT NULL,                           -- ✅ 订单号唯一
    total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount > 0),   -- ✅ 金额必须大于0
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 📌 示例4：为订单表创建复合索引（加速多条件查询）
CREATE INDEX idx_orders_user_status ON orders (user_id, status);   -- ✅ 加速“某用户所有订单状态”查询

-- 📌 示例5：使用 TRUNCATE 清空表（比 DELETE 快得多）
TRUNCATE TABLE orders RESTART IDENTITY;  -- ✅ 清空所有数据，重置自增ID（慎用！仅用于测试或初始化）

-- 📌 示例6：添加新字段（必须通过 Flyway 管理，禁止手动执行）
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;  -- ✅ 新增可空字段，兼容旧数据

-- 📌 示例7：修改字段类型（谨慎！需数据兼容）
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(25);  -- ✅ 扩展手机号长度，需确保无超长数据

-- 📌 示例8：删除表（生产环境禁止直接 DROP！）
-- DROP TABLE users CASCADE;  -- ⚠️ 生产环境禁止执行！应通过状态标记软删除

✅ 团队规范建议：

• 所有 DDL 脚本必须放在 src/main/resources/db/migration/ 下，使用 Flyway 管理版本。
• 每个脚本命名格式：V1__create_users_table.sql，V2__add_phone_to_users.sql
• 生产环境禁止手动执行 ALTER / DROP，必须走变更流程。

---

三、DML（Data Manipulation Language）—— 数据的“搬运工”

✅ 定义与作用：

用于增删改数据库中的具体数据记录，直接影响业务数据。

🔑 核心语句：

• INSERT：插入数据
• UPDATE：更新数据
• DELETE：删除数据（物理删除）
• MERGE（PostgreSQL 15+）：Upsert（插入或更新）

💡 企业必要性：

• 禁止物理删除，一律使用 status = 'deleted' 软删除。
• 批量插入必须使用 INSERT ... VALUES (...), (...), (...)，避免循环单条插入。
• 更新必须带 WHERE 条件，禁止无条件更新。

---

✅ 标准使用示例（带中文注释）

-- 📌 示例1：插入单条用户数据（推荐使用参数化，Java 中用 PreparedStatement）
INSERT INTO users (username, email, password_hash, metadata)
VALUES 
    ('zhangsan', 'zhangsan@company.com', 'pbkdf2:hash123', '{"theme": "light", "lang": "zh-CN"}');

-- 📌 示例2：批量插入用户（性能关键！Java 中用 MyBatis Batch）
INSERT INTO users (username, email, password_hash, metadata)
VALUES 
    ('lisi', 'lisi@company.com', 'hash456', '{"theme": "dark"}'),
    ('wangwu', 'wangwu@company.com', 'hash789', '{"theme": "light", "notifications": {"email": false}}'),
    ('zhaoliu', 'zhaoliu@company.com', 'hash000', '{}');  -- ✅ 空 JSONB 用 '{}' 表示

-- 📌 示例3：插入时忽略重复（UPSERT，PostgreSQL 9.5+）
INSERT INTO users (username, email, password_hash)
VALUES ('zhangsan', 'zhangsan@company.com', 'new_hash')
ON CONFLICT (email) DO UPDATE SET
    password_hash = EXCLUDED.password_hash,  -- ✅ 冲突时更新密码
    updated_at = NOW();                      -- ✅ 自动更新时间戳

-- 📌 示例4：更新订单状态（必须带 WHERE！）
UPDATE orders
SET status = 'paid', updated_at = NOW()
WHERE order_no = 'ORD202510170001' AND status = 'pending';  -- ✅ 状态校验，避免误更新

-- 📌 示例5：批量更新（更新多个订单状态）
UPDATE orders
SET status = 'cancelled', updated_at = NOW()
WHERE user_id IN (1001, 1002, 1003) AND status = 'pending';

-- 📌 示例6：软删除（推荐！禁止物理删除）
UPDATE users
SET status = 'deleted', updated_at = NOW()
WHERE id = 1001;

-- 📌 示例7：删除（仅用于测试或清理归档表）
DELETE FROM orders WHERE created_at < '2024-01-01';  -- ⚠️ 生产环境慎用！建议用分区 + 定时任务归档

-- 📌 示例8：使用 MERGE（PostgreSQL 15+）实现原子性 Upsert
-- 适用于：用户登录后更新最后登录时间，若不存在则创建
MERGE INTO users AS target
USING (SELECT 'newuser' AS username, 'new@company.com' AS email, 'hash123' AS password_hash) AS source
ON target.email = source.email
WHEN MATCHED THEN
    UPDATE SET password_hash = source.password_hash, updated_at = NOW()
WHEN NOT MATCHED THEN
    INSERT (username, email, password_hash, created_at, updated_at)
    VALUES (source.username, source.email, source.password_hash, NOW(), NOW());

✅ 团队规范建议：

• 所有 UPDATE / DELETE 必须包含 WHERE 条件，且条件字段必须有索引。
• 所有删除操作必须改为 UPDATE ... SET status = 'deleted'。
• 批量操作必须使用 IN (...) 或 JOIN，禁止循环单条执行。
• Java 中使用 @Modifying + @Transactional 确保 DML 事务一致。

---

四、DQL（Data Query Language）—— 数据的“侦察兵”

✅ 定义与作用：

仅用于查询数据，不修改任何数据。核心语句只有 SELECT。

🔑 核心能力：

• 基础查询、条件过滤、排序、分页
• 多表 JOIN、子查询
• 聚合函数（COUNT、SUM、AVG）
• 窗口函数（ROW_NUMBER、RANK）
• CTE（公共表表达式）
• JSONB 查询、全文检索

💡 企业必要性：

• 所有查询必须分页，禁止 SELECT * FROM table。
• 复杂查询必须用 CTE 拆解，提升可读性。
• JSONB 字段查询必须用 ->>、@>、? 等操作符，禁止用 LIKE。
• 生产环境查询必须使用 EXPLAIN ANALYZE 分析执行计划。

---

✅ 标准使用示例（带中文注释）

-- 📌 示例1：基础查询（必须指定字段，禁止 SELECT *）
SELECT id, username, email, status, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;  -- ✅ 分页查询：第1页，每页10条

-- 📌 示例2：多表关联查询（订单 + 用户）
SELECT
    o.order_no,
    o.total_amount,
    u.username,
    u.email
FROM orders o
JOIN users u ON o.user_id = u.id  -- ✅ 显式 JOIN，清晰关联关系
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;

-- 📌 示例3：JSONB 查询（查询配置中包含特定值）
SELECT username, metadata->>'theme' AS theme
FROM users
WHERE metadata @> '{"notifications": {"email": true}}';  -- ✅ 包含查询：JSONB 中有 email=true 的通知设置

-- 📌 示例4：JSONB 路径提取（深层嵌套）
SELECT
    username,
    metadata->'settings'->>'theme' AS theme,
    metadata->'settings'->'notifications'->>'push' AS push_enabled
FROM users
WHERE metadata->'settings' ? 'theme';  -- ✅ 检查 JSONB 中是否存在 key 'theme'

-- 📌 示例5：使用 CTE 拆解复杂逻辑（推荐！）
WITH recent_orders AS (
    SELECT user_id, SUM(total_amount) AS total_spent
    FROM orders
    WHERE created_at >= '2025-01-01'
    GROUP BY user_id
    HAVING SUM(total_amount) > 1000  -- ✅ 过滤消费超过1000的用户
),
top_users AS (
    SELECT u.username, ro.total_spent,
           ROW_NUMBER() OVER (ORDER BY ro.total_spent DESC) AS rank
    FROM recent_orders ro
    JOIN users u ON ro.user_id = u.id
)
SELECT username, total_spent, rank
FROM top_users
WHERE rank <= 10;  -- ✅ 查询消费榜前10名

-- 📌 示例6：窗口函数（计算每个用户的订单排名）
SELECT
    user_id,
    order_no,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total_amount DESC) AS rank_by_amount,
    RANK() OVER (ORDER BY total_amount DESC) AS global_rank
FROM orders
WHERE status = 'paid'
ORDER BY global_rank;

-- 📌 示例7：全文检索（替代 LIKE，性能提升百倍）
SELECT title, content
FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'spring & boot');  -- ✅ 搜索包含“spring”和“boot”的文章

-- 📌 示例8：分页查询（Spring Boot Pageable 标准写法）
-- Java 中：PageRequest.of(page, size, Sort.by("created_at").descending())
SELECT id, username, email
FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;  -- ✅ 第3页，每页10条

-- 📌 示例9：聚合查询（统计活跃用户数）
SELECT
    COUNT(*) AS total_active_users,
    AVG(total_amount) AS avg_order_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active' AND o.status = 'paid';

✅ 团队规范建议：

• 所有查询必须分页，LIMIT 必须存在。
• 所有 WHERE 条件字段必须有索引，否则查询慢如蜗牛。
• 所有 JSONB 查询使用 @>、->>、?，禁止 LIKE '%xxx%'。
• 复杂查询必须用 CTE 拆解，提升可读性和可维护性。
• 使用 EXPLAIN (ANALYZE, BUFFERS) 分析慢查询，纳入 CI/CD 监控。

---

五、TCL（Transaction Control Language）—— 数据一致性的“守护者”

✅ 定义与作用：

控制事务的提交、回滚、保存点，确保多个操作要么全部成功，要么全部失败。

🔑 核心语句：

• BEGIN / START TRANSACTION：开启事务
• COMMIT：提交事务（永久生效）
• ROLLBACK：回滚事务（撤销所有更改）
• SAVEPOINT：设置保存点（部分回滚）

💡 企业必要性：

• 所有涉及多个表的写操作（如下单、扣库存、发券）必须用事务。
• Java 中使用 @Transactional 注解，但需理解底层 TCL 行为。
• 避免长事务，防止锁表。

---

✅ 标准使用示例（带中文注释）

-- 📌 示例1：转账事务（经典案例）
BEGIN;  -- ✅ 开启事务

-- 扣款
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- 充值
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 检查余额是否足够（业务逻辑）
-- 如果这里发生异常，执行 ROLLBACK；否则 COMMIT

COMMIT;  -- ✅ 提交事务：两个操作同时生效

-- 📌 示例2：事务回滚（模拟异常）
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 999; -- ❌ 目标用户不存在，可能报错

ROLLBACK;  -- ✅ 回滚：扣款操作也被撤销，数据保持一致

-- 📌 示例3：使用保存点（部分回滚）
BEGIN;

INSERT INTO orders (user_id, order_no, total_amount) VALUES (1, 'ORD001', 200);

SAVEPOINT sp1;  -- ✅ 设置保存点

INSERT INTO orders (user_id, order_no, total_amount) VALUES (2, 'ORD002', 300);

-- 如果后续操作失败，可回滚到 sp1，保留 ORD001
ROLLBACK TO SAVEPOINT sp1;

-- 继续执行其他逻辑
INSERT INTO orders (user_id, order_no, total_amount) VALUES (3, 'ORD003', 150);

COMMIT;  -- ✅ 最终提交：ORD001 和 ORD003 生效，ORD002 被回滚

✅ 团队规范建议：

• Java 中使用 @Transactional(propagation = REQUIRES_NEW) 控制嵌套事务。
• 所有 @Transactional 方法必须明确边界，避免跨服务调用导致事务蔓延。
• 生产环境监控长事务（>5s），配置 log_min_duration_statement = 5000。

---

六、DCL（Data Control Language）—— 权限的“守门人”

✅ 定义与作用：

控制用户对数据库对象的访问权限，保障数据安全。

🔑 核心语句：

• GRANT：授予权限
• REVOKE：收回权限
• CREATE USER / DROP USER
• ALTER ROLE

💡 企业必要性：

• 生产环境禁止使用超级用户（postgres）。
• 必须按“最小权限原则”分配账号。
• 分离：应用账号（读写）、报表账号（只读）、运维账号（DDL）。

---

✅ 标准使用示例（带中文注释）

-- 📌 示例1：创建应用专用用户（禁止使用 postgres）
CREATE USER app_user WITH PASSWORD 'SecureAppPass123!';  -- ✅ 密码必须符合安全策略

-- 📌 示例2：创建只读报表用户
CREATE USER report_reader WITH PASSWORD 'ReadOnlyPass456!';

-- 📌 示例3：授予应用用户对所有表的读写权限
GRANT CONNECT ON DATABASE myapp TO app_user;          -- ✅ 允许连接数据库
GRANT USAGE ON SCHEMA public TO app_user;             -- ✅ 允许访问 public 模式
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;  -- ✅ 对所有表有读写权限
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user; -- ✅ 对所有序列有使用权（用于自增ID）

-- 📌 示例4：授予报表用户只读权限
GRANT CONNECT ON DATABASE myapp TO report_reader;
GRANT USAGE ON SCHEMA public TO report_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_reader;  -- ✅ 只允许查询，禁止修改

-- 📌 示例5：设置默认权限（未来新建表自动授权）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO report_reader;

-- 📌 示例6：回收权限（如员工离职）
REVOKE SELECT ON orders FROM report_reader;

-- 📌 示例7：查看当前用户权限（调试用）
\du  -- 在 psql 中执行，列出所有角色及其权限

✅ 团队规范建议：

• 数据库账号必须按角色划分：app_user、report_reader、admin。
• 所有账号密码必须通过 Vault 或 K8s Secret 管理，禁止写在配置文件中。
• 生产环境开启 sslmode=require，强制加密连接。
• 定期审计权限（每季度），使用 pg_roles 和 information_schema.table_privileges 查看。

---

七、总结：五大 SQL 分类企业级使用对照表

类别	核心语句	是否影响数据	Java 开发者最常接触	企业落地建议
DDL	CREATE, ALTER, DROP, TRUNCATE	❌	⚠️ 仅 DBA/Flyway	所有变更走 Flyway 版本管理
DML	INSERT, UPDATE, DELETE	✅	✅✅✅	禁止物理删除，必须软删除；批量操作用 IN
DQL	SELECT	✅（只读）	✅✅✅	必须分页，禁止 SELECT *，JSONB 用操作符
TCL	BEGIN, COMMIT, ROLLBACK	✅	✅✅✅	所有跨表写操作必须事务，Java 用 @Transactional
DCL	GRANT, REVOKE	❌	⚠️ 仅 DBA/运维	分离账号，最小权限，密码加密存储

---

✅ 终极建议：推动团队落地的 5 条铁律

1. 所有 DDL 必须通过 Flyway 管理，禁止手动执行。
2. 所有 DELETE 必须改为 UPDATE status = ‘deleted’。
3. 所有 SELECT 必须分页 + 指定字段，禁止 SELECT *。
4. 所有涉及多个表的写操作必须用 @Transactional。
5. 生产环境禁止使用超级用户，必须按角色分配最小权限。

---