PostgreSQL 常用 SQL 语句大全：从入门到实战-联睿云创

一份涵盖日常开发与运维的 PostgreSQL 语句速查手册

PostgreSQL 作为世界上最先进的开源关系型数据库，凭借其强大的功能、优秀的扩展性和对 SQL 标准的良好兼容性，已成为越来越多企业和开发者的首选。无论是 OLTP 业务、数据分析平台，还是地理信息系统（GIS），PostgreSQL 都能提供可靠的支撑。

本文系统地整理了 PostgreSQL 开发与运维中常用的 SQL 语句，希望能成为你日常工作中的速查手册。

一、数据库连接与基础操作

1.1 连接数据库

使用 psql 命令行工具连接数据库：

# 本地连接
psql -U postgres -d mydatabase# 远程连接
psql -h 192.168.1.100 -p 5432 -U dbuser -d mydb

连接后的常用元命令（以 \ 开头，无需分号结尾）：

命令	说明
`\l` 或 `\l+`	列出所有数据库
`\c 数据库名`	切换当前数据库
`\dt` 或 `\dt+`	列出当前库所有表
`\d 表名`	查看表结构（列、约束、索引）
`\di`	列出所有索引
`\du`	查看所有用户/角色
`\conninfo`	查看当前连接信息
`\q`	退出 psql

1.2 数据库管理

-- 创建数据库
CREATE DATABASE mydb;-- 删除数据库
DROP DATABASE [IF EXISTS] mydb;-- 查看数据库版本
SELECT version();                                    -- 查看当前连接信息
SELECT pid, usename, application_name, client_addr, state, query 
FROM pg_stat_activity WHERE state != 'idle';         -- 获取数据库实例连接数
SELECT COUNT(*) FROM pg_stat_activity;               -- 获取数据库最大连接数
SHOW max_connections;                                -- 查询各用户的数据库连接数
SELECT usename, COUNT(*) FROM pg_stat_activity 
GROUP BY usename;

二、数据定义语言（DDL）

2.1 创建表

-- 基本建表语句
CREATE TABLE students (id SERIAL PRIMARY KEY,          -- SERIAL 为自增整数name VARCHAR(50) NOT NULL,age INT,email VARCHAR(100) UNIQUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

常用数据类型：

数值型：SMALLINT、INT、BIGINT、DECIMAL、NUMERIC、REAL、DOUBLE PRECISION
字符型：CHAR(n)、VARCHAR(n)、TEXT
日期时间：DATE、TIME、TIMESTAMP、INTERVAL
布尔型：BOOLEAN
JSON：JSON、JSONB
自增：SERIAL、BIGSERIAL

2.2 修改表结构

-- 添加列
ALTER TABLE students ADD COLUMN address VARCHAR(100);    -- 删除列
ALTER TABLE students DROP COLUMN email;                  -- 修改列数据类型
ALTER TABLE students ALTER COLUMN age TYPE SMALLINT;     -- 重命名列
ALTER TABLE students RENAME COLUMN name TO full_name;    -- 重命名表
ALTER TABLE students RENAME TO learners;

2.3 删除表与清空数据

-- 删除表
DROP TABLE [IF EXISTS] students;-- 清空表数据（保留结构，速度快，可重置自增ID）
TRUNCATE TABLE students;                                 
TRUNCATE TABLE students RESTART IDENTITY;                -- 清空多个表
TRUNCATE TABLE table1, table2, table3 CASCADE;

三、数据操作语言（DML）

3.1 插入数据（INSERT）

-- 插入单条数据
INSERT INTO students (name, age, email) 
VALUES ('John Doe', 20, 'john.doe@example.com');        -- 插入多条数据
INSERT INTO students (name, age, email) VALUES('Alice', 22, 'alice@example.com'),('Bob', 19, 'bob@example.com');-- 从另一张表插入数据
INSERT INTO students_archive SELECT * FROM students WHERE age > 30;

3.2 更新数据（UPDATE）

-- 更新指定条件的数据
UPDATE students SET age = 21 WHERE id = 1;               -- 更新多列
UPDATE students SET age = 22, email = 'new@example.com' 
WHERE name = 'John Doe';

3.3 删除数据（DELETE）

-- 删除指定条件的数据
DELETE FROM students WHERE age > 25;                     -- 删除所有数据（比 DELETE 慢，建议用 TRUNCATE）
DELETE FROM students;

四、数据查询语言（DQL）

4.1 基础查询（SELECT）

-- 查询所有列
SELECT * FROM users;                                     -- 查询指定列
SELECT id, name, email FROM users;                       -- 使用别名
SELECT name AS full_name, email AS contact_email FROM users;  -- 去重查询
SELECT DISTINCT status FROM orders;                      -- 计数
SELECT COUNT(*) FROM users;                              
SELECT COUNT(DISTINCT status) FROM orders;

4.2 条件过滤（WHERE）

-- 等值条件
SELECT * FROM users WHERE status = 'active';             -- 比较运算
SELECT * FROM products WHERE price > 100;                -- 范围查询（BETWEEN）
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;    -- 模式匹配（LIKE / ILIKE）
SELECT * FROM users WHERE email LIKE '%@example.com';    
SELECT * FROM users WHERE name ILIKE 'john%';  -- 不区分大小写 -- IN 操作符
SELECT * FROM orders WHERE status IN ('pending', 'processing'); -- NULL 判断
SELECT * FROM users WHERE deleted_at IS NULL;            
SELECT * FROM users WHERE phone_number IS NOT NULL;      -- 逻辑运算（AND / OR / NOT）
SELECT * FROM products WHERE price > 100 AND stock > 0;  
SELECT * FROM users WHERE status = 'active' OR verified = true;
SELECT * FROM products WHERE NOT (price > 1000);

4.3 排序与分页（ORDER BY / LIMIT）

-- 排序（ASC 升序为默认）
SELECT * FROM users ORDER BY created_at;                 
SELECT * FROM users ORDER BY created_at DESC;            -- 多列排序
SELECT * FROM orders ORDER BY status ASC, created_at DESC; -- NULL 值处理
SELECT * FROM users ORDER BY last_login NULLS FIRST;     
SELECT * FROM users ORDER BY last_login NULLS LAST;      -- 限制返回行数
SELECT * FROM users LIMIT 10;                            -- 分页查询
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;      -- FETCH 方式（SQL 标准）
SELECT * FROM users OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

五、多表连接（JOIN）

5.1 INNER JOIN（内连接）

返回两张表中匹配的记录：

-- 基础内连接
SELECT orders.id, orders.total, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id; -- 多表连接
SELECT o.id, c.name, p.name AS product
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id;

5.2 LEFT JOIN（左外连接）

返回左表所有记录，右表无匹配则返回 NULL：

-- 查询所有客户及其订单（无订单的客户也显示）
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;               -- 查找没有订单的客户
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

5.3 其他 JOIN 类型

-- RIGHT JOIN（右外连接）
SELECT c.name, o.id AS order_id
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;           -- FULL OUTER JOIN（全外连接）
SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

六、分组与聚合

6.1 分组聚合（GROUP BY）

-- 按状态统计订单数量
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;-- 按用户统计订单总额
SELECT customer_id, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id;-- 多字段分组
SELECT year, month, SUM(amount)
FROM sales
GROUP BY year, month;

6.2 分组过滤（HAVING）

-- 筛选订单数大于 5 的用户
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

6.3 常用聚合函数

函数	说明
`COUNT()`	计数
`SUM()`	求和
`AVG()`	平均值
`MAX()`	最大值
`MIN()`	最小值

七、高级查询

7.1 公用表表达式（CTE）

CTE 可以将复杂查询分解为更小、更易管理的部分：

-- 基本 CTE
WITH order_stats AS (SELECT customer_id, COUNT(*) AS order_countFROM ordersGROUP BY customer_id
)
SELECT c.name, os.order_count
FROM customers c
JOIN order_stats os ON c.id = os.customer_id
WHERE os.order_count > 5;

7.2 递归 CTE

用于查询树形结构数据（如组织架构、菜单树等）：

-- 递归查询组织层级
WITH RECURSIVE org_tree AS (-- 非递归部分：根节点SELECT id, name, parent_id, 1 AS levelFROM departmentsWHERE parent_id IS NULLUNION ALL-- 递归部分：子节点SELECT d.id, d.name, d.parent_id, ot.level + 1FROM departments dJOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree;

7.3 窗口函数

窗口函数在不合并行的情况下，对每一行执行跨行计算：

-- ROW_NUMBER：为每行分配序号
SELECT name, salary,ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;-- 分组内的排名
SELECT department,name,salary,RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;-- LAG / LEAD：访问前后行
SELECT date,sales,LAG(sales, 1) OVER (ORDER BY date) AS prev_day_sales,sales - LAG(sales, 1) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

7.4 子查询

-- WHERE 子句中的子查询
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);-- FROM 子句中的子查询（派生表）
SELECT dept, AVG(salary)
FROM (SELECT department AS dept, salary FROM employees) AS sub
GROUP BY dept;-- EXISTS 子查询
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

八、索引与性能优化

8.1 索引管理

-- 创建索引（B-Tree 为默认类型）
CREATE INDEX idx_users_email ON users(email);            -- 创建唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);-- 创建复合索引（多列）
CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at);-- 查看表中的索引
SELECT * FROM pg_indexes WHERE tablename = 'users';      -- 查看索引大小
SELECT pg_size_pretty(pg_indexes_size('users'));         -- 删除索引
DROP INDEX idx_users_email;-- 重建索引（解决索引膨胀）
REINDEX INDEX idx_users_email;
REINDEX TABLE users;  -- 重建表的所有索引

8.2 执行计划分析

-- 查看执行计划（不实际执行）
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';-- 查看实际执行计划（含执行时间和缓冲区信息）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM users WHERE email = 'test@example.com';

8.3 统计信息维护

-- 更新统计信息（优化查询计划）
ANALYZE users;                                           -- 清理并更新统计信息（推荐日常维护）
VACUUM ANALYZE users;                                    -- 查看表数据量（统计信息中的估算值）
SELECT relname AS table_name, reltuples AS row_count
FROM pg_class
WHERE relkind = 'r' ORDER BY row_count DESC;

九、权限管理

-- 创建用户
CREATE USER appuser WITH PASSWORD 'secure_password';     -- 授予表查询权限
GRANT SELECT ON table_name TO username;                  -- 授予表的所有权限
GRANT ALL PRIVILEGES ON TABLE product TO username;       -- 授予 Schema 下所有表的所有权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username; -- 授予数据库连接权限
GRANT CONNECT ON DATABASE mydb TO appuser;               -- 修改表所有者
ALTER TABLE table_name OWNER TO username;                -- 修改用户密码
ALTER USER appuser WITH PASSWORD 'new_password';         -- 查看所有用户
\du

十、数据库运维与管理

10.1 查看数据库与表大小

-- 查看数据库大小（人类可读格式）
SELECT pg_size_pretty(pg_database_size('mydb'));         -- 查看所有数据库大小
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;                                        -- 查看单表数据大小
SELECT pg_size_pretty(pg_relation_size('users')) AS size; -- 查看表（含索引）总大小
SELECT pg_size_pretty(pg_total_relation_size('users')) AS size; -- 查看表中索引大小
SELECT pg_size_pretty(pg_indexes_size('users'));

10.2 慢查询诊断

-- 查询最耗时的 5 条 SQL（需开启 pg_stat_statements 扩展）
SELECT * FROM pg_stat_statements 
ORDER BY total_time DESC LIMIT 5;                        -- 按平均耗时排序
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;

10.3 终止连接

-- 终止指定连接（慎用）
SELECT pg_terminate_backend(pid);                        -- 查看长时间运行的查询
SELECT datname, usename, client_addr, state, backend_start, xact_start, query_start, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > INTERVAL '5 minutes';

10.4 备份与恢复

# 备份数据库（自定义格式，推荐）
pg_dump -U postgres -Fc -b -v -f /backup/mydb.backup mydb    # 备份为 SQL 文件（可读）
pg_dump -U postgres -Fp -f /backup/mydb.sql mydb             # 恢复数据库
pg_restore -U postgres -d mydb -v /backup/mydb.backup

十一、常用扩展与系统表

11.1 查看扩展

-- 查看已安装的扩展
SELECT * FROM pg_extension;                              -- 查看所有表（含系统表）
SELECT * FROM pg_tables;                                -- 查看当前 Schema 的所有表
SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public';

11.2 查看表注释

-- 查看所有用户表及其注释
SELECT relname AS table_name, col_description(c.oid, 0) AS comment
FROM pg_class c
WHERE relkind = 'r' AND relname NOT LIKE 'pg_%' AND relname NOT LIKE 'sql_%';

总结

本文涵盖了 PostgreSQL 开发与运维中最常用的 SQL 语句，从基础的 CRUD 操作到高级的 CTE 和窗口函数，从索引优化到权限管理。熟练掌握这些语句，可以覆盖日常工作中 90% 以上的数据库操作场景。

几点建议：

善用 EXPLAIN：在编写复杂查询时，先用 EXPLAIN ANALYZE 分析执行计划，确保索引被正确使用。
定期维护：生产环境应定期执行 VACUUM ANALYZE 更新统计信息、清理死元组。
注意安全：避免给应用用户授予 SUPERUSER 权限，远程访问需限制 IP 范围。
善用高级特性：CTE 和窗口函数能让复杂查询更清晰、更高效。

资讯详情

PostgreSQL 常用 SQL 语句大全：从入门到实战