postgresql增删改查

PostgreSQL 增删改查（CRUD）操作

以下是 PostgreSQL 中常用的增、删、改、查操作详解。

---

准备工作：创建示例表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

增（INSERT）

插入单条记录

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

插入多条记录

INSERT INTO users (name, email, age)
VALUES
    ('李四', 'lisi@example.com', 32),
    ('王五', 'wangwu@example.com', 25),
    ('赵六', 'zhaoliu@example.com', 30);

插入并返回数据（PostgreSQL 特色）

INSERT INTO users (name, email, age)
VALUES ('孙七', 'sunqi@example.com', 27)
RETURNING *;

RETURNING 子句是 PostgreSQL 的扩展特性，可以在插入/更新/删除后直接返回结果，无需再执行一次查询。

从其他表插入数据

INSERT INTO users_backup (name, email, age)
SELECT name, email, age FROM users WHERE age > 25;

冲突处理（UPSERT）

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

---

删（DELETE）

删除满足条件的记录

DELETE FROM users WHERE age < 20;

删除所有记录（保留表结构）

DELETE FROM users;

使用 TRUNCATE 快速清空表

TRUNCATE TABLE users RESTART IDENTITY;

RESTART IDENTITY 会重置自增序列，TRUNCATE 比 DELETE 更快但不可回滚。

删除并返回被删数据

DELETE FROM users WHERE age < 20 RETURNING *;

关联删除（子查询）

DELETE FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'cancelled');

---

改（UPDATE）

更新满足条件的记录

UPDATE users SET age = 33 WHERE name = '李四';

更新多个字段

UPDATE users
SET age = 29, email = 'zhangsan_updated@example.com'
WHERE name = '张三';

更新并返回结果

UPDATE users SET age = age + 1 WHERE age < 30 RETURNING *;

基于子查询更新

UPDATE users
SET age = age + 5
WHERE id IN (SELECT id FROM users WHERE name LIKE '张%');

使用 FROM 子句进行关联更新

UPDATE users
SET age = orders.total_amount
FROM orders
WHERE users.id = orders.user_id AND orders.status = 'completed';

---

查（SELECT）

基本查询

-- 查询所有列
SELECT * FROM users;

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

-- 去重查询
SELECT DISTINCT age FROM users;

条件查询（WHERE）

SELECT * FROM users WHERE age > 25 AND name LIKE '张%';

排序（ORDER BY）

SELECT * FROM users ORDER BY age DESC, name ASC;

分页（LIMIT / OFFSET）

-- 跳过10条，取5条（第11~15条）
SELECT * FROM users ORDER BY id LIMIT 5 OFFSET 10;

聚合查询

SELECT
    COUNT(*) AS total,
    AVG(age) AS avg_age,
    MIN(age) AS min_age,
    MAX(age) AS max_age,
    SUM(age) AS sum_age
FROM users;

分组查询（GROUP BY + HAVING）

SELECT age, COUNT(*) AS cnt
FROM users
GROUP BY age
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

连接查询（JOIN）

-- 内连接
SELECT u.name, o.order_no
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接
SELECT u.name, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

子查询

SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

常用条件操作符

操作符	说明	示例
= / != / <>	等于 / 不等于	WHERE age = 25
> / < / >= / <=	比较	WHERE age >= 30
BETWEEN ... AND ...	范围	WHERE age BETWEEN 20 AND 30
IN (...)	集合匹配	WHERE age IN (25, 28, 30)
LIKE / ILIKE	模糊匹配	WHERE name LIKE '张%'
IS NULL / IS NOT NULL	空值判断	WHERE email IS NOT NULL
EXISTS	存在性判断	WHERE EXISTS (SELECT 1 FROM ...)

💡 ILIKE 是 PostgreSQL 特有的不区分大小写的模糊匹配操作符。

---

常用进阶技巧

CTE（公用表表达式）

WITH young_users AS (
    SELECT * FROM users WHERE age < 30
)
SELECT * FROM young_users ORDER BY age;

窗口函数

SELECT name, age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS rank
FROM users;

事务操作

BEGIN;
    UPDATE users SET age = 30 WHERE id = 1;
    DELETE FROM users WHERE id = 99;
COMMIT;  -- 或 ROLLBACK; 回滚

---

以上就是 PostgreSQL 中增删改查的核心操作。PostgreSQL 在标准 SQL 基础上提供了许多强大扩展（如 RETURNING、ON CONFLICT、ILIKE、窗口函数等），善用这些特性可以大幅提升开发效率。