数据库教程FGMT48‑PostgreSQL数据查询与SQL语句增删改

## 前言
本套风哥教程面向DBA、数据库运维工程师、后端开发人员，聚焦PostgreSQL基础DML数据操作、各类查询语法、事务并发控制。风哥教程本文分为理论原理与实战操作两大模块，理论部分讲解SQL执行基础、DML语句底层机制、MVCC并发模型、约束与事务隔离；实战部分基于主机`fgedu‑net‑cn1`、`fgedu‑net‑cn2`，硬件规格统一为**64G内存，8CPU**，数据根目录统一使用`/fgedudb`，数据库/实例名`fgedudb`，业务用户名`fgedu`，包含大量可直接复现psql命令与SQL语句，覆盖增删改基础语法、多表关联、子查询、CTE公用表表达式、RETURNING子句、事务实操、锁排查、常见故障处理。

风哥教程本文学习目标：熟练掌握PostgreSQL增删改DML语法，理解MVCC并发读写机制，能够编写多表关联、子查询、公用表表达式SQL，掌握事务提交回滚，能够排查DML执行报错、锁等待问题，具备日常业务SQL编写与简单调优能力。

> 实操提示：所有SQL优先在测试环境执行，生产环境执行DML前务必开启事务验证结果，确认无误再提交；大批量更新删除避免直接裸执行，防止误操作。
> 网上搜索风哥教程可以学习全套数据库教程

## 目录
1. SQL增删改查基础理论
1.1 关系型数据库DML语句概念
1.2 PostgreSQL MVCC多版本并发控制原理
1.3 事务ACID特性与四种隔离级别
1.4 表约束对DML语句的影响（主键、唯一、非空、外键）
1.5 RETURNING子句机制，区别于其他数据库
1.6 DML执行过程与锁机制基础
1.7 64G内存8CPU硬件下相关参数说明
2. 环境准备实战
2.1 主机环境确认，psql连接数据库
2.2 业务测试表初始化建表语句
3. INSERT插入数据实战
3.1 单行数据插入
3.2 多行批量插入
3.3 指定部分字段插入、DEFAULT默认值使用
3.4 INSERT … SELECT 从查询结果插入数据
3.5 INSERT … ON CONFLICT冲突处理（upsert）
3.6 INSERT配合RETURNING获取自增主键
4. UPDATE更新数据实战
4.1 单字段更新、多字段更新
4.2 带条件更新，主键过滤最佳实践
4.3 基于子查询结果做更新
4.4 UPDATE … RETURNING返回修改后数据
4.5 更新常见报错原因分析
5. DELETE删除数据实战
5.1 按条件删除行数据
5.2 DELETE … RETURNING返回被删除行
5.3 TRUNCATE清空表对比DELETE
5.4 基于子查询条件删除
6. SELECT数据查询实战
6.1 基础SELECT字段过滤、WHERE条件过滤
6.2 ORDER BY排序、LIMIT/OFFSET分页查询
6.3 聚合函数GROUP BY、HAVING分组过滤
6.4 多表JOIN连接：内连接、左连接、右连接、全连接
6.5 子查询：标量子查询、IN子查询、EXISTS存在性子查询
6.6 UNION / UNION ALL / INTERSECT / EXCEPT集合运算
6.7 WITH公用表表达式CTE，递归CTE
7. 事务、锁与并发DML实操
7.1 BEGIN、COMMIT、ROLLBACK基础事务实操
7.2 SAVEPOINT保存点实操
7.3 SELECT … FOR UPDATE行锁实操
7.4 查看锁信息pg_locks系统视图
7.5 隔离级别修改实操，并发读写现象验证
8. DML常见问题与故障排查
8.1 约束冲突报错处理（主键重复、外键约束）
8.2 锁等待长时间阻塞排查流程
8.3 大事务风险，长事务对vacuum影响
8.4 RETURNING子句使用误区
9. 风哥针对本文总结

## 1 SQL增删改查基础理论
> 风哥 itpux-com

### 1.1 关系型数据库DML语句概念
DML即数据操纵语言，包含`INSERT`插入、`UPDATE`更新、`DELETE`删除、`SELECT`查询，用于对表内业务行数据进行读写操作。DDL数据定义语言（CREATE/ALTER/DROP）负责对象结构，DCL权限语言负责账号授权；DML只操作行记录，**不会修改表结构**。

PostgreSQL的DML和其他数据库存在差异化特性：支持`RETURNING`子句、`ON CONFLICT`冲突处理、CTE可以直接包裹DML语句，这些语法特性是运维开发必须掌握的重点。

### 1.2 PostgreSQL MVCC多版本并发控制原理
MVCC多版本并发控制，是PostgreSQL实现读写不阻塞的核心机制。
每一行数据存在多个版本，更新或者删除旧行不会直接覆盖物理数据，会生成新版本，旧版本保留；不同事务根据快照看到对应版本的数据。
– 读操作不会阻塞读；读操作默认不会阻塞写；写操作不会阻塞读；
– 旧版本元数据会由VACUUM后台进程进行回收清理；
> 重点风险：长事务会阻止旧版本元数据回收，造成表膨胀，这是PostgreSQL运维高频故障点。

> 网上搜索风哥教程可以学习全套数据库教程

### 1.3 事务ACID特性与四种隔离级别
事务ACID：原子性Atomic、一致性Consistency、隔离性Isolation、持久性Durability。
PostgreSQL支持四种标准事务隔离级别：
1. **READ UNCOMMITTED**：读未提交，PostgreSQL内部实际等价于READ COMMITTED，不会读到脏数据；
2. **READ COMMITTED（默认）**：读已提交，每一条SQL执行生成一次快照，业务绝大多数场景使用；
3. **REPEATABLE READ**：可重复读，事务启动生成快照，整个事务内快照不变，会检测序列化异常；
4. **SERIALIZABLE**：可串行化，最高隔离级别，完全串行执行效果，冲突直接报错回滚，性能开销大。

### 1.4 表约束对DML语句的影响
执行INSERT、UPDATE、DELETE的时候，数据库会实时校验约束，校验失败直接终止当前SQL：
– 主键PRIMARY KEY：非空+唯一，重复主键插入直接报错；
– UNIQUE唯一约束：字段值不能重复；
– NOT NULL非空约束：字段不能传入NULL；
– CHECK检查约束：自定义业务逻辑校验；
– FOREIGN KEY外键约束：父子表参照完整性，子表插入必须父表存在对应主键，父表删除被引用行直接报错。

> 风哥教程 113257174

### 1.5 RETURNING子句机制，区别于其他数据库
PostgreSQL特有的`RETURNING`子句，`INSERT/UPDATE/DELETE`语句后面可以追加，直接返回DML语句处理过的行，不需要额外执行SELECT查询。
– INSERT可以返回自增主键，省去插入之后再查询max(id)；
– UPDATE返回修改之后的完整行；
– DELETE返回已经被删除的原始行；
RETURNING输出结果可以配合CTE做数据迁移，把删除的数据直接插入归档表。

### 1.6 DML执行过程与锁机制基础
执行DML语句流程：
1. SQL解析器解析语法，生成执行计划；
2. 获取需要访问表的表级锁；
3. 访问数据行，对被修改的行施加行锁；
4. 生成新版本元组，旧版本保留；
5. 事务提交时写入WAL预写日志，保证持久化。

行锁只锁住被修改的行，不同事务修改不同行互不阻塞；多个事务修改同一行，后到达的事务会等待行锁释放。可以通过`pg_locks`系统视图查看锁对象。

> 上51CTO搜索风哥可以学习全套数据库教程

### 1.7 64G内存8CPU硬件下相关参数说明
针对**64G内存，8CPU**服务器，和DML、事务相关关键参数：
1. `max_connections = 300`最大连接数；
2. `work_mem = 64MB`排序、hash操作内存，影响大查询、子查询内存消耗；
3. `maintenance_work_mem = 2GB`vacuum清理旧版本使用；
4. `idle_in_transaction_session_timeout = 300000`毫秒，自动断开空闲长事务，防止长事务阻止元组回收；
5. `max_wal_size = 8GB`WAL日志最大大小，大批量DML需要充足WAL空间；
6. `lock_timeout`锁等待超时，业务可以按需设置，避免会话无限等待锁。

## 2 环境准备实战
> 实验主机`fgedu‑net‑cn1`，数据库实例`fgedudb`，业务账号`fgedu`；数据目录`/fgedudb/fgedudb_data`。
### 2.1 主机环境确认，psql连接数据库
Windows环境PowerShell管理员，Linux终端执行psql登录：
“`powershell
#Windows
D:\fgedudb\pgsql18\bin\psql.exe -U fgedu -h 127.0.0.1 -d fgedudb -p 5432
“`
“`bash
#Linux
psql -U fgedu -h 127.0.0.1 -d fgedudb -p 5432
“`
登录成功提示符：`fgedudb=>`

### 2.2 业务测试表初始化建表语句
执行下面SQL创建两张测试业务表，`fg_user`用户主表，`fg_order`订单子表，设置外键关联，用于后续全部DML实战。
“`sql
— 用户业务主表
CREATE TABLE fg_user(
id BIGSERIAL PRIMARY KEY,
user_name VARCHAR(64) NOT NULL,
phone VARCHAR(20),
status SMALLINT NOT NULL DEFAULT 1,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 订单子表，外键关联fg_user.id
CREATE TABLE fg_order(
order_id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
order_no VARCHAR(32) UNIQUE NOT NULL,
amount NUMERIC(12,2) NOT NULL DEFAULT 0,
order_status SMALLINT DEFAULT 0,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_order_uid FOREIGN KEY(user_id) REFERENCES fg_user(id)
);
“`
> 风哥数据库教程 itpux-com

## 3 INSERT插入数据实战
### 3.1 单行数据插入
标准单行INSERT，显式书写字段列表，生产推荐写法，避免表结构变更带来异常。
“`sql
INSERT INTO fg_user(user_name,phone,status)
VALUES(‘zhangsan’,’13800138000′,1);
“`

### 3.2 多行批量插入
一次VALUES写多组记录，数据库一次解析执行，性能优于循环单行插入。
“`sql
INSERT INTO fg_user(user_name,phone,status)
VALUES
(‘lisi’,’13900139000′,1),
(‘wangwu’,’13700137000′,0),
(‘zhaoliu’,’13600136000′,1);
“`

### 3.3 指定部分字段插入、DEFAULT默认值使用
没有书写的字段会自动使用字段DEFAULT默认值，自增列不需要手动赋值。
“`sql
INSERT INTO fg_user(user_name,phone)
VALUES(‘qianqi’,’13500135000′);
“`

### 3.4 INSERT … SELECT 从查询结果插入数据
把查询出来的结果集直接插入目标表，适合数据复制迁移。
“`sql
— 将status=1的用户复制到一张备份表，先建备份表
CREATE TABLE fg_user_bak (LIKE fg_user INCLUDING ALL);

INSERT INTO fg_user_bak
SELECT * FROM fg_user WHERE status = 1;
“`

### 3.5 INSERT … ON CONFLICT冲突处理（upsert）
PostgreSQL特有语法，主键或者唯一索引冲突的时候，选择什么行为：DO NOTHING什么都不做，或者DO UPDATE执行更新，实现插入或者更新（upsert）。
“`sql
— 插入，如果主键冲突直接跳过
INSERT INTO fg_user(id,user_name,phone)
VALUES(1,’zhangsan_new’,’13800138888′)
ON CONFLICT(id) DO NOTHING;

— 冲突的时候执行更新
INSERT INTO fg_user(id,user_name,phone)
VALUES(1,’zhangsan_new’,’13800138888′)
ON CONFLICT(id) DO UPDATE SET
user_name=EXCLUDED.user_name,
phone=EXCLUDED.phone;
“`
> 关键字EXCLUDED代表本次准备插入的那一行虚拟记录。

> 风哥教程 113257174

### 3.6 INSERT配合RETURNING获取自增主键
不需要插入之后再SELECT查询，直接返回生成的id。
“`sql
INSERT INTO fg_user(user_name,phone)
VALUES(‘sunba’,’13400134000′)
RETURNING id;
“`
执行输出直接返回本次插入行的自增id。

## 4 UPDATE更新数据实战
> ⚠️生产环境UPDATE务必携带WHERE条件，不写WHERE会更新整张表全部行，风险极高。

### 4.1 单字段更新、多字段更新
“`sql
–单字段更新
UPDATE fg_user
SET status = 0
WHERE id = 2;

–多字段同时更新，逗号分隔
UPDATE fg_user
SET phone=’13900139999′, status=1
WHERE id = 3;
“`

### 4.2 带条件更新，主键过滤最佳实践
优先使用主键id作为过滤条件，定位行效率最高；尽量避免大范围不带索引字段做更新条件。
“`sql
UPDATE fg_user
SET create_time = CURRENT_TIMESTAMP
WHERE id IN (4,5);
“`

### 4.3 基于子查询结果做更新
根据另外一张表的数据更新当前表字段。
“`sql
UPDATE fg_order o
SET order_status = 2
WHERE o.user_id IN (SELECT id FROM fg_user WHERE status=0);
“`

### 4.4 UPDATE … RETURNING返回修改后数据
RETURNING返回更新完成之后行的全部字段，快速查看修改结果。
“`sql
UPDATE fg_user
SET status=1
WHERE id=3
RETURNING id,user_name,status,phone;
“`

### 4.5 UPDATE常见报错原因分析
1. 更新违反CHECK、NOT NULL约束：传入的值不符合字段约束；
2. 外键报错：更新子表user_id，值在父表fg_user不存在；
3. 锁等待：目标行被别的事务占用，语句卡住等待锁释放。

> 网上搜索风哥教程可以学习全套数据库教程

## 5 DELETE删除数据实战
> ⚠️DELETE不加WHERE条件，会删除整张表全部行；生产严禁裸写DELETE，优先开启事务验证。

### 5.1 按条件删除行数据
“`sql
DELETE FROM fg_user
WHERE status = 0 AND id = 3;
“`

### 5.2 DELETE … RETURNING返回被删除行
可以拿到被删除的数据，适合删除同时归档。
“`sql
DELETE FROM fg_order
WHERE order_status=9
RETURNING *;
“`

“`sql
–清空表，保留表结构
TRUNCATE TABLE fg_user_bak;
“`

### 5.4 基于子查询条件删除
结合IN子查询，根据关联表条件删除数据。
“`sql
DELETE FROM fg_order
WHERE user_id IN (SELECT id FROM fg_user WHERE status=0);
“`

> 上51CTO搜索风哥可以学习全套数据库教程

## 6 SELECT数据查询实战
### 6.1 基础SELECT字段过滤、WHERE条件过滤
“`sql
–指定字段查询
SELECT id,user_name,phone,status FROM fg_user;

–where条件过滤
SELECT id,user_name,phone
FROM fg_user
WHERE status = 1 AND create_time >= ‘2026‑01‑01 00:00:00’;

–模糊匹配LIKE
SELECT * FROM fg_user WHERE user_name LIKE ‘zhang%’;
“`

### 6.2 ORDER BY排序、LIMIT/OFFSET分页查询
“`sql
–排序
SELECT * FROM fg_user ORDER BY create_time DESC;

–分页，取前2条
SELECT * FROM fg_user ORDER BY id DESC LIMIT 2 OFFSET 0;
“`
> 大数据量分页OFFSET性能差，生产推荐主键id游标分页，`where id > last_id limit 20`。

### 6.3 聚合函数GROUP BY、HAVING分组过滤
`GROUP BY`分组；`HAVING`对聚合之后结果过滤，区别WHERE（原始行过滤）。
“`sql
SELECT status,count(*) AS user_cnt
FROM fg_user
GROUP BY status
HAVING count(*) >=1;
“`

### 6.4 多表JOIN连接：内连接、左连接、右连接、全连接
“`sql
–INNER JOIN内连接，两边匹配的数据才返回
SELECT u.id,u.user_name,o.order_no,o.amount
FROM fg_user u
INNER JOIN fg_order o ON u.id = o.user_id;

–LEFT JOIN左连接，左边全部，右边匹配不到显示NULL
SELECT u.id,u.user_name,o.order_no,o.amount
FROM fg_user u
LEFT JOIN fg_order o ON u.id = o.user_id;
“`

### 6.5 子查询：标量子查询、IN子查询、EXISTS存在性子查询
“`sql
–标量子查询
SELECT id,user_name,
(SELECT count(*) FROM fg_order o WHERE o.user_id=u.id) AS order_cnt
FROM fg_user u;

–IN子查询
SELECT * FROM fg_order WHERE user_id IN (SELECT id FROM fg_user WHERE status=1);

–EXISTS 存在性子查询，大数据量性能优于IN
SELECT * FROM fg_user u
WHERE EXISTS (SELECT 1 FROM fg_order o WHERE o.user_id=u.id);
“`

### 6.6 UNION / UNION ALL / INTERSECT / EXCEPT集合运算
– UNION ALL：直接合并结果集，不去重，性能高；
– UNION：合并并且去重，消耗排序；
– INTERSECT：取两个结果集交集；
– EXCEPT：取差集，第一个有第二个没有的数据。
“`sql
SELECT user_name FROM fg_user WHERE status=1
UNION ALL
SELECT user_name FROM fg_user_bak WHERE status=1;
“`

### 6.7 WITH公用表表达式CTE，递归CTE
WITH子句CTE，把子查询提取出来，SQL可读性提升；支持递归WITH RECURSIVE处理树形层级数据。
“`sql
–普通CTE
WITH cte_active_user AS (
SELECT id,user_name FROM fg_user WHERE status=1
)
SELECT * FROM cte_active_user;
“`
> 风哥数据库教程 itpux-com

## 7 事务、锁与并发DML实操
### 7.1 BEGIN、COMMIT、ROLLBACK基础事务实操
psql中开启事务，不写COMMIT不会真正落盘，适合生产DML预校验。
“`sql
BEGIN; –开启事务

UPDATE fg_user SET status=1 WHERE id=3;

SELECT * FROM fg_user WHERE id=3; –本会话看到修改结果，别的会话看不到

COMMIT; –提交，持久化，其他会话可见
–ROLLBACK; –回滚，撤销全部修改
“`

### 7.2 SAVEPOINT保存点实操
事务内部设置保存点，可以局部回滚到保存点，不用回滚整个大事务。
“`sql
BEGIN;
INSERT INTO fg_user(user_name,phone) VALUES(‘test01′,’13100000001’);
SAVEPOINT sp1;

INSERT INTO fg_user(user_name,phone) VALUES(‘test02′,’13100000002’);

ROLLBACK TO SAVEPOINT sp1; –回滚到sp1，test02撤销，test01保留

COMMIT;
“`

### 7.3 SELECT … FOR UPDATE行锁实操
查询的时候对行施加行排他锁，防止别的事务修改这一批行。
会话1执行：
“`sql
BEGIN;
SELECT * FROM fg_user WHERE id=1 FOR UPDATE;
“`
会话2尝试更新id=1这一行，会进入锁等待，直到会话1 commit或者rollback释放行锁。
其他可选锁：`FOR NO KEY UPDATE`、`FOR SHARE`。

### 7.4 查看锁信息pg_locks系统视图
查询当前实例锁、等待锁会话，故障排查使用：
“`sql
SELECT pid,relation::regclass,mode,granted
FROM pg_locks
WHERE relation IS NOT NULL;

–结合会话信息，查看等待锁的SQL
SELECT pid,usename,query,state
FROM pg_stat_activity;
“`

### 7.5 隔离级别修改实操，并发读写现象验证
设置当前会话隔离级别：
“`sql
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM fg_user;
“`

## 8 DML常见问题与故障排查
### 8.1 约束冲突报错处理（主键重复、外键约束）
1. 主键/唯一键冲突：INSERT或者ON CONFLICT的时候，目标主键已经存在；查看已有记录，选择DO NOTHING或者DO UPDATE。
2. 外键报错：子表插入/更新user_id，父表fg_user不存在对应id；先插入父表记录，再操作子表。
3. NOT NULL约束：字段传入NULL，检查业务输入值。

### 8.2 锁等待长时间阻塞排查流程
1. psql查询`pg_stat_activity`找到等待锁的pid；
2. 查询`pg_locks`，确认是哪张表、哪些行持有锁；
3. 找到持有锁的会话，确认业务是否还在运行；
4. 可以选择等待业务事务提交，或者终止持有锁会话`SELECT pg_terminate_backend(pid);`。

### 8.3 大事务风险，长事务对vacuum影响
– 大批量DML不要写成单个超大事务，拆分成小事务，避免WAL暴涨，同时阻止vacuum回收旧元组，引发表膨胀；
– 禁止数据库存在空闲长事务，参数`idle_in_transaction_session_timeout`设置超时自动断开。

### 8.4 RETURNING子句使用误区
RETURNING只能返回当前DML语句处理的行；CTE中DML的RETURNING可以传递给外层语句，但是RETURNING不能直接跨多张表取数据。

> 风哥教程 113257174

## 9 风哥针对本文总结
风哥教程本文完整讲解PostgreSQL的DML增删改查全套知识，硬件规格统一64G内存8CPU，主机`fgedu‑net‑cn1`、`fgedu‑net‑cn2`，数据库`fgedudb`，业务账号`fgedu`。

核心要点梳理：
1. PostgreSQL依靠MVCC多版本并发控制实现读写不阻塞，旧版本元组依靠VACUUM后台进程回收；**长事务会阻止版本回收，引发表膨胀，属于生产高频风险点**。事务支持ACID四大特性，READ COMMITTED为业务最常用隔离级别。
2. INSERT支持单行、多行批量插入、`INSERT … SELECT`、`ON CONFLICT`冲突处理（upsert）；`RETURNING`子句是PostgreSQL特有特性，INSERT/UPDATE/DELETE都可以直接返回处理完成的行，不需要额外SELECT查询。
3. UPDATE、DELETE生产环境**绝对不能省略WHERE条件**，否则会修改/删除整张表全部数据；上线前建议包裹BEGIN事务，校验数据正确之后再执行COMMIT；错误使用ROLLBACK回滚。TRUNCATE属于DDL，清空表速度极快，但是不触发行触发器。
4. SELECT查询包含基础过滤、排序分页、GROUP BY聚合、多表JOIN、子查询、集合运算；EXISTS子查询在大数据量场景性能通常优于IN子查询；WITH CTE公用表表达式简化复杂SQL，支持RECURSIVE递归处理树形数据。
5. 事务支持SAVEPOINT保存点，可以局部回滚，不需要撤销整个事务；`SELECT … FOR UPDATE`施加行锁；`pg_locks`与`pg_stat_activity`是排查锁等待、阻塞故障的核心系统视图。
6. DML高频故障：约束冲突（主键、唯一、非空、外键）、锁等待阻塞、长事务导致表膨胀、大批量DML产生巨大WAL日志；生产需要避免超大事务，拆分为分批提交。
7. 配套64G内存8CPU硬件参数重点关注：`work_mem`、`maintenance_work_mem`、`idle_in_transaction_session_timeout`、`max_wal_size`，合理配置可以降低DML相关故障概率。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

数据库教程FGMT48‑PostgreSQL数据查询与SQL语句增删改

相关推荐

联系我们