PostgreSQL 中 DDL、DQL、DML、DCL、TCL 详解

✅ PostgreSQL 中 DDL、DQL、DML、DCL、TCL 详解

在 PostgreSQL（以及其他关系型数据库）中，SQL 语句根据功能被分为五大类：

💡 DDL、DML、DQL、DCL、TCL —— 是数据库操作的“五大支柱”

---

🧭 一、总览：SQL 语句分类

缩写	全称	中文含义	主要语句示例	是否自动提交
DDL	Data Definition Language	数据定义语言	CREATE, ALTER, DROP, TRUNCATE	✅ 是
DML	Data Manipulation Language	数据操作语言	INSERT, UPDATE, DELETE	❌ 否
DQL	Data Query Language	数据查询语言	SELECT	❌ 否
DCL	Data Control Language	数据控制语言	GRANT, REVOKE	✅ 是
TCL	Transaction Control Language	事务控制语言	BEGIN, COMMIT, ROLLBACK, SAVEPOINT	❌ 否

⚠️ 重要区别：

• DDL / DCL：自动提交（Autocommit） —— 执行后立即生效，不能回滚。
• DML / DQL / TCL：需手动提交或回滚 —— 在事务中可控。

---

一、DDL —— 数据定义语言（Data Definition Language）

用于定义或修改数据库结构（表、索引、视图、模式等）。

✅ 常用命令：

语句	作用	示例
CREATE	创建数据库对象	CREATE TABLE users (...)
ALTER	修改已有对象结构	ALTER TABLE users ADD COLUMN age INT;
DROP	删除对象	DROP TABLE users;
TRUNCATE	清空表数据（保留结构）	TRUNCATE TABLE users;
COMMENT	添加注释	COMMENT ON TABLE users IS '用户表';
RENAME	重命名对象	ALTER TABLE old_name RENAME TO new_name;

📌 示例：创建表

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    salary DECIMAL(10,2),
    hire_date DATE DEFAULT CURRENT_DATE
);

⚠️ 注意：

• DDL 语句自动提交事务，不能回滚。
• TRUNCATE 比 DELETE 更快（不写日志，重置序列），但无法触发触发器。

---

二、DQL —— 数据查询语言（Data Query Language）

用于从数据库中查询数据 —— 核心是 SELECT

✅ 常用语法结构：

SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column]
[HAVING condition]
[ORDER BY column [ASC|DESC]]
[LIMIT n] [OFFSET m];

📌 示例：

-- 查询所有员工
SELECT * FROM employees;

-- 查询工资大于5000的员工，按工资降序
SELECT name, salary FROM employees
WHERE salary > 5000
ORDER BY salary DESC;

-- 分组统计各部门人数
SELECT department, COUNT(*) as count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

💡 扩展功能：

• 子查询、JOIN、窗口函数、CTE（WITH）、聚合函数等都属于 DQL 范畴。

---

三、DML —— 数据操作语言（Data Manipulation Language）

用于操作表中的数据（增删改）。

✅ 常用命令：

语句	作用	示例
INSERT	插入新记录	INSERT INTO users (name) VALUES ('Alice');
UPDATE	更新现有记录	UPDATE users SET age = 30 WHERE id = 1;
DELETE	删除记录	DELETE FROM users WHERE id = 1;

📌 示例：

-- 插入数据
INSERT INTO employees (name, email, salary)
VALUES ('张三', 'zhang@example.com', 8000.00);

-- 更新数据
UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT';

-- 删除数据
DELETE FROM employees WHERE hire_date < '2020-01-01';

⚠️ 注意：

• DML 语句不会自动提交，可以被 ROLLBACK 回滚。
• 生产环境建议在事务中执行，并确认无误后再 COMMIT。

---

四、DCL —— 数据控制语言（Data Control Language）

用于控制数据库访问权限和安全。

✅ 常用命令：

语句	作用	示例
GRANT	授予权限	GRANT SELECT ON employees TO hr_user;
REVOKE	撤销权限	REVOKE INSERT ON employees FROM hr_user;
CREATE ROLE / CREATE USER	创建角色/用户	CREATE USER appuser WITH PASSWORD '123';

📌 示例：

-- 创建用户
CREATE USER readonly WITH PASSWORD 'read123';

-- 授予查询权限
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 撤销权限
REVOKE DELETE ON employees FROM readonly;

⚠️ 注意：

• DCL 语句自动提交，执行后立即生效。
• 权限可作用于：数据库、模式、表、列、函数等。

---

五、TCL —— 事务控制语言（Transaction Control Language）

用于管理事务（保证数据一致性）。

✅ 核心命令：

语句	作用	示例
BEGIN / START TRANSACTION	开始事务	BEGIN;
COMMIT	提交事务，使更改永久生效	COMMIT;
ROLLBACK	回滚事务，撤销未提交的更改	ROLLBACK;
SAVEPOINT	设置保存点	SAVEPOINT mypoint;
ROLLBACK TO SAVEPOINT	回滚到保存点	ROLLBACK TO mypoint;

📌 示例：

BEGIN;

INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 500 WHERE user_id = 2;

-- 如果中间出错，可以回滚
ROLLBACK;

-- 如果一切正常，提交
COMMIT;

💡 事务特性（ACID）：

• Atomicity（原子性）：事务内操作要么全成功，要么全失败
• Consistency（一致性）：事务前后数据保持一致状态
• Isolation（隔离性）：并发事务互不干扰
• Durability（持久性）：提交后数据永久保存

---

🔄 事务隔离级别（PostgreSQL 支持）

-- 查看当前隔离级别
SHOW transaction_isolation;

-- 设置隔离级别（在 BEGIN 后）
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 或 SERIALIZABLE, REPEATABLE READ, READ UNCOMMITTED（PG中等同于READ COMMITTED）

PostgreSQL 默认隔离级别是 READ COMMITTED

---

🧩 补充：PostgreSQL 特色支持

功能	说明
RETURNING	DML 语句返回被影响的行（如 INSERT ... RETURNING id）
UPSERT	INSERT ... ON CONFLICT DO UPDATE 实现“不存在则插入，存在则更新”
CTE	WITH 子句，支持递归查询（常用于树形结构）
JSON/JSONB	支持文档型数据存储与查询（DQL/DML 均可操作）

✅ 示例：RETURNING

INSERT INTO employees (name, email)
VALUES ('李四', 'li@example.com')
RETURNING id, name;
-- 返回刚插入的 id 和 name

✅ 示例：UPSERT

INSERT INTO employees (id, name, email)
VALUES (1, '王五', 'wang@example.com')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, email = EXCLUDED.email;

---

🧪 实践小任务

请在 psql 中执行以下操作：

1. 创建一个表 products (id SERIAL, name TEXT, price NUMERIC)
2. 插入 3 条记录
3. 查询所有价格 > 100 的产品
4. 更新其中一条记录的价格
5. 删除一条记录
6. 创建一个只读用户并授权 SELECT
7. 使用事务包裹一次插入 + 更新，然后回滚
8. 再次执行并提交

---

✅ 总结对比表

类型	关键词	是否自动提交	是否可回滚	用途
DDL	CREATE, ALTER, DROP	✅ 是	❌ 否	定义/修改结构
DQL	SELECT	❌ 否	❌ 否*	查询数据
DML	INSERT, UPDATE, DELETE	❌ 否	✅ 是	操作数据
DCL	GRANT, REVOKE	✅ 是	❌ 否	权限管理
TCL	BEGIN, COMMIT, ROLLBACK	❌ 否	✅ 是	事务控制

*注：DQL 本身不修改数据，无需回滚，但在事务中可与其他语句一起回滚上下文。