一、psql 客户端 / 连接 / 帮助
| 功能 | 命令 |
|---|
| 启动 psql 并以指定用户连接（本地） | psql -U username |
| 指定主机 / 端口 / 数据库 | psql -U username -h host -p port -d dbname |
| 退出 | \q |
| 帮助（SQL 命令语法） | \h |
| 帮助（psql 内建命令） | \? |
| 显示当前连接信息 | \conninfo |
| 执行文件中的 SQL 脚本 | \i filename.sql |
| 将输出重定向到文件 | \o filename（之后的查询结果写入该文件） |
| 切换输出格式 | \x （切换扩展或垂直显示模式） |
| 在 psql 中执行 shell 命令 | \! ls 或 \! 命令 |
二、数据库 / 模式 / 对象 列表与信息
| 功能 | 命令 |
|---|
| 列出所有数据库 | \l 或 \list |
| 列出所有模式（schemas） | \dn |
| 列出当前数据库下所有表（当前 schema） | \dt |
| 列出所有表（所有 schema） | \dt *.* |
| 查表结构 / 列 / 索引 / 约束 / 触发器 | \d table_name |
| 更详细的表信息（含描述，表空间，行数估计等） | \d+ table_name |
| 列出视图 | \dv |
| 列出函数 / 存储过程 | \df |
| 列出扩展（Extensions） | \dx |
| 列出数据类型 | \dT / \dT+ |
| 列出索引 | \di |
| 列出角色 / 用户 | \du |
三、数据库 / 表 / 模式操作（DDL）
| 功能 | SQL 命令 |
|---|
| 创建数据库 | CREATE DATABASE dbname; |
| 删除数据库 | DROP DATABASE IF EXISTS dbname; |
| 重命名数据库 | ALTER DATABASE oldname RENAME TO newname; |
| 创建表 | sql CREATE TABLE table_name ( <列定义>, … ); |
| 创建临时表 | CREATE TEMP TABLE temp_name (...); |
| 删除表 | DROP TABLE IF EXISTS table_name; |
| 增加列 | ALTER TABLE table_name ADD COLUMN col_name datatype; |
| 删除列 | ALTER TABLE table_name DROP COLUMN col_name; |
| 修改列类型 | ALTER TABLE table_name ALTER COLUMN col_name TYPE new_type; |
| 重命名列 | ALTER TABLE table_name RENAME COLUMN old TO new; |
| 重命名表 | ALTER TABLE old_table RENAME TO new_table; |
| 添加 / 删除约束 | ALTER TABLE table_name ADD CONSTRAINT ... / DROP CONSTRAINT constraint_name; |
| 注释表 / 列 | COMMENT ON TABLE table_name IS '...'; / COMMENT ON COLUMN table_name.col IS '...'; |
四、数据操作（DML：增删改查）
| 操作 | 命令示例 |
|---|
| 插入单条记录 | INSERT INTO table_name (col1, col2) VALUES (val1, val2); |
| 插入多条 | INSERT INTO table_name (cols…) VALUES (…),(…); |
| 查询所有 | SELECT * FROM table_name; |
| 查询指定列 | SELECT col1, col2 FROM table_name; |
| 带条件查询 | SELECT * FROM table WHERE condition; |
| 排序 | `... ORDER BY col1 ASC |
| 限制 / 偏移 | ... LIMIT n OFFSET m |
| 聚合 + 分组 | SELECT col, COUNT(*) FROM table GROUP BY col; |
| 分组过滤 | HAVING 子句用于过滤分组后结果 |
| 更新 | UPDATE table_name SET col1 = val1, col2 = val2 WHERE condition; |
| 删除 | DELETE FROM table_name WHERE condition; |
| 删除全部行 | TRUNCATE TABLE table_name; |
五、事务 / 并发控制 / 锁
| 功能 | 命令 |
|---|
| 开始事务 | BEGIN; 或 START TRANSACTION; |
| 提交 | COMMIT; |
| 回滚 | ROLLBACK; |
| 保存点 | SAVEPOINT savepoint_name; |
| 回滚到保存点 | ROLLBACK TO SAVEPOINT savepoint_name; |
| 设置事务隔离级别 | SET TRANSACTION ISOLATION LEVEL <级别>（如 READ COMMITTED, REPEATABLE READ, SERIALIZABLE） |
| 控制锁 / 并发 | PostgreSQL 通常使用行锁（行级锁）与 MVCC（多版本并发控制），显式锁可用 LOCK TABLE 等 |
六、索引 / 约束 / 视图 / 函数
| 功能 | 命令 |
|---|
| 创建索引 | CREATE INDEX idx_name ON table_name (col1, col2); |
| 创建唯一索引 | CREATE UNIQUE INDEX idx_name ON table_name (col); |
| 删除索引 | DROP INDEX IF EXISTS idx_name; |
| 创建视图 | CREATE VIEW view_name AS SELECT ...; |
| 删除视图 | DROP VIEW IF EXISTS view_name; |
| 创建函数 / 存储过程 | PostgreSQL 用 CREATE FUNCTION ... RETURNS ... LANGUAGE ... AS $$ ... $$; |
| 删除函数 | DROP FUNCTION IF EXISTS func_name(arg_types); |
| 创建触发器 | `CREATE TRIGGER trg_name BEFORE |
| 删除触发器 | DROP TRIGGER IF EXISTS trg_name ON table_name; |
七、备份 / 恢复 / 导入导出
| 操作 | 命令 / 工具 |
|---|
| 导出单个数据库 | pg_dump -U user -h host -d dbname > backup.sql |
| 导出所有数据库 | pg_dumpall -U user > alldb_backup.sql |
| 导出表 / 指定对象 | pg_dump -t table_name dbname > table_backup.sql |
| 导入 SQL 文件 | psql -U user -d dbname < backup.sql |
| 使用 pg_restore | pg_restore -U user -d dbname backup_file |
| 导出为 CSV | 在 psql 使用 \copy table TO 'file.csv' CSV HEADER; |
| 从 CSV 导入 | \copy table FROM 'file.csv' CSV HEADER; |
八、管理 / 监控 / 优化 / 配置
| 功能 | 命令 / 示例 |
|---|
| 显示服务器版本 | SHOW server_version; |
| 查看系统 / 配置变量 | SHOW ALL; 或 SHOW config_var; |
| 列出当前活动进程 / 会话 | 查询 pg_stat_activity |
| 获取表统计信息 | ANALYZE table_name; |
| 查询执行计划 | EXPLAIN SELECT …; / EXPLAIN ANALYZE SELECT …; |
| 管理扩展 | CREATE EXTENSION ext_name; / DROP EXTENSION ext_name; |
| 启用 / 查看日志参数 | 修改 postgresql.conf，如 log_min_duration_statement、log_statement 等 |
| 重载配置 | SELECT pg_reload_conf(); 或重启服务 |
| 清理 / 垃圾回收 | VACUUM; 或 VACUUM FULL; |
九、用户 / 角色 / 权限管理
PostgreSQL 的用户（user）与角色（role）是统一的概念。一个角色既可以登录（LOGIN 权限），也可以作为其他用户的组（类似 UNIX group）。
| 功能 | 命令 |
|---|
| 创建用户 | CREATE USER username WITH PASSWORD 'secret'; |
| 创建角色 | CREATE ROLE rolename; |
| 授予登录权限 | ALTER ROLE rolename WITH LOGIN; |
| 授权角色 | GRANT rolename TO username; |
| 撤销角色 | REVOKE rolename FROM username; |
| 授权数据库访问 | GRANT CONNECT ON DATABASE dbname TO username; |
| 授权表级操作 | GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO username; |
| 授权模式（schema）访问 | GRANT USAGE ON SCHEMA schema_name TO username; |
| 授权序列（sequence）访问 | GRANT USAGE, SELECT, UPDATE ON SEQUENCE seq_name TO username; |
| 超级用户 | CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'pw'; |
| 修改密码 | ALTER ROLE username WITH PASSWORD 'newpw'; |
| 删除角色 / 用户 | DROP ROLE username; |
| 查看当前用户 | SELECT current_user; 或 \conninfo |
| 查看角色属性 | \du+ |
十、JSON / JSONB 操作（NoSQL 风格）
PostgreSQL 的 JSONB 是结构化文档存储的利器，性能优秀且支持索引。
| 操作 | 示例 |
|---|
| 插入 JSON 数据 | INSERT INTO users(data) VALUES ('{"name":"Falko","age":28}'::jsonb); |
| 查询字段 | SELECT data->>'name' FROM users; |
| 条件查询 | SELECT * FROM users WHERE data->>'age' = '28'; |
| 更新 JSON 字段 | UPDATE users SET data = jsonb_set(data, '{age}', '30', false); |
| 合并 JSON | `SELECT '{"a":1}'::jsonb |
| 删除字段 | SELECT data - 'age' FROM users; |
| 创建索引 | CREATE INDEX idx_gin_users_data ON users USING GIN (data); |
| 查询包含关系 | SELECT * FROM users WHERE data @> '{"country":"China"}'; |
| 提取数组元素 | data->'friends'->>0（取第一个元素） |
十一、数组（Array）类型操作
PostgreSQL 原生支持数组列，这在 SQL 世界中十分罕见。
| 功能 | 命令 |
|---|
| 定义数组列 | CREATE TABLE t (tags TEXT[]); |
| 插入数组 | INSERT INTO t VALUES (ARRAY['music','code']); |
| 查询数组 | SELECT * FROM t WHERE 'code' = ANY(tags); |
| 连接数组 | `SELECT ARRAY['a','b'] |
| 删除元素 | SELECT array_remove(ARRAY[1,2,3,2], 2); |
| 展开数组（拆分为行） | SELECT unnest(tags) FROM t; |
十二、窗口函数（Window Functions）
窗口函数让你在不聚合的情况下执行排名、累积、分区统计。
| 功能 | 示例 |
|---|
| 排名 | SELECT name, score, RANK() OVER (ORDER BY score DESC) FROM students; |
| 行号 | SELECT ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC); |
| 累计和 | SUM(amount) OVER (ORDER BY date); |
| 移动平均 | AVG(temp) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW); |
| 差值计算 | LAG(value,1) OVER (ORDER BY id) / LEAD(value,1) |
十三、CTE（公用表表达式）
CTE 是“临时命名子查询”，尤其在递归查询时非常强大。
-- 基本 CTE
WITH recent AS (
SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT * FROM recent WHERE amount > 100;
-- 递归 CTE（求层级关系）
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id FROM employees WHERE id = 1
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;
十四、时间与日期操作
| 功能 | 示例 |
|---|
| 当前时间 | SELECT now(); |
| 仅日期 / 时间 | SELECT current_date, current_time; |
| 时间加减 | SELECT now() + interval '2 hours'; |
| 时间差 | SELECT age('2025-10-07', '2000-06-01'); |
| 提取年月日 | SELECT extract(year FROM now()), extract(month FROM now()); |
| 格式化输出 | TO_CHAR(now(), 'YYYY-MM-DD HH24:MI:SS'); |
| 生成序列日期 | SELECT generate_series('2025-01-01'::date, '2025-12-31', '1 day'); |
十五、全文检索（Full Text Search）
PostgreSQL 原生支持强大的全文检索。
| 功能 | 示例 |
|---|
| 建立 tsvector 字段 | ALTER TABLE docs ADD COLUMN textsearch tsvector; |
| 更新字段 | UPDATE docs SET textsearch = to_tsvector('english', content); |
| 查询 | SELECT * FROM docs WHERE textsearch @@ to_tsquery('database & system'); |
| 创建索引 | CREATE INDEX idx_docs_textsearch ON docs USING GIN(textsearch); |
| 排序（匹配度） | SELECT *, ts_rank(textsearch, to_tsquery('data')) AS rank FROM docs ORDER BY rank DESC; |
十六、地理空间（PostGIS 简介）
安装扩展：
CREATE EXTENSION postgis;
常见用法：
| 功能 | 示例 |
|---|
| 创建地理字段 | ALTER TABLE cities ADD COLUMN geom GEOGRAPHY(Point); |
| 插入 | INSERT INTO cities(name, geom) VALUES ('Paris', ST_GeogFromText('POINT(2.3522 48.8566)')); |
| 计算距离 | SELECT ST_Distance(a.geom, b.geom) FROM cities a, cities b; |
| 判断包含 | ST_Contains(poly.geom, point.geom) |
| 空间索引 | CREATE INDEX idx_geom ON cities USING GIST (geom); |
十七、性能调优与维护
| 功能 | 命令 / 工具 |
|---|
| 查看查询计划 | EXPLAIN (ANALYZE, BUFFERS) SELECT …; |
| 分析表统计信息 | ANALYZE table_name; |
| 重建索引 | REINDEX TABLE table_name; |
| 手动清理垃圾行 | VACUUM; 或 VACUUM FULL; |
| 查看当前锁 | 查询 pg_locks、pg_stat_activity |
| 强制终止连接 | SELECT pg_terminate_backend(pid); |
| 并行查询 | 确保 max_parallel_workers_per_gather > 0 |
| 监控 I/O、缓存命中率 | 查询视图 pg_stat_bgwriter、pg_stat_database |
| 自动化清理 | 使用 autovacuum，默认启用 |
十八、扩展功能与奇技淫巧
| 类别 | 示例 |
|---|
| UUID 自动生成 | CREATE EXTENSION "uuid-ossp"; → uuid_generate_v4() |
| JSON + GIN 索引混合查询 | 支持 JSONB @> + B-tree 联合索引 |
| UPSERT（存在则更新） | INSERT INTO t (id, val) VALUES (1, 'A') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val; |
| 临时统计表 | CREATE TEMP TABLE tmp AS SELECT …; |
| 随机取样 | TABLESAMPLE BERNOULLI(10);（抽取约 10% 数据） |
| 复制结构不复制数据 | CREATE TABLE new_table (LIKE old_table INCLUDING ALL); |
十九、信息视图（Information Schema）
PostgreSQL 有丰富的系统信息视图：
| 视图 | 内容 |
|---|
information_schema.tables | 所有表元数据 |
information_schema.columns | 列名、类型、默认值等 |
pg_stat_activity | 当前活动查询与连接 |
pg_stat_user_tables | 表的读写次数统计 |
pg_indexes | 索引定义 |
pg_roles | 所有角色信息 |