PostgreSQL 常用 SQL 命令集合
以下是一份全面的 PostgreSQL 常用 SQL 命令集合,包含数据库管理、表操作、数据查询、性能优化等核心功能:
一、数据库操作
sql复制代码
-- 创建数据库CREATE DATABASE db_name;-- 切换数据库\\c db_name -- psql 命令行-- 删除数据库DROP DATABASE IF EXISTS db_name;-- 查看所有数据库\\l -- psql 命令行SELECT datname FROM pg_database;-- 备份数据库 (命令行)pg_dump -U username db_name > backup.sql-- 恢复数据库 (命令行)psql -U username -d db_name -f backup.sql
二、表操作
1. 创建表
sql复制代码
CREATE TABLE users ( id SERIAL PRIMARY KEY, -- 自增主键 name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, age INT CHECK (age >= 0), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, profile JSONB -- JSON 数据支持);
2. 修改表结构
sql复制代码
-- 添加列ALTER TABLE users ADD COLUMN phone VARCHAR(15);-- 修改列类型ALTER TABLE users ALTER COLUMN phone TYPE TEXT;-- 重命名列ALTER TABLE users RENAME COLUMN phone TO mobile;-- 添加约束ALTER TABLE users ADD CONSTRAINT email_format CHECK (email ~ \'^[\\w.]+@\\w+\\.\\w+$\');-- 删除列ALTER TABLE users DROP COLUMN mobile;
3. 表维护
sql复制代码
-- 清空表 (保留结构)TRUNCATE TABLE logs;-- 删除表DROP TABLE IF EXISTS temp_data;-- 查看表结构\\d users -- psql 命令行
三、数据操作 (CRUD)
1. 插入数据
sql复制代码
-- 插入单行 (RETURNING 返回生成值)INSERT INTO users (name, email) VALUES (\'Alice\', \'alice@example.com\')RETURNING id;-- 插入多行INSERT INTO users (name, email) VALUES (\'Bob\', \'bob@test.com\'), (\'Charlie\', \'charlie@abc.org\');
2. 查询数据
sql复制代码
-- JSON 字段查询SELECT profile->>\'address\' AS address FROM users;-- 窗口函数SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;-- CTE (公共表表达式)WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region)SELECT * FROM regional_sales;-- 全文搜索SELECT * FROM articles WHERE to_tsvector(\'english\', content) @@ to_tsquery(\'database & optimization\');
3. 更新数据
sql复制代码
-- 基于子查询更新UPDATE orders SET status = \'completed\'WHERE id IN ( SELECT order_id FROM payments WHERE paid = true);
4. 删除数据
sql复制代码
-- 使用 USING 关联删除DELETE FROM users uUSING blacklist b WHERE u.email = b.email;
四、索引管理
sql复制代码
-- 创建 B-tree 索引CREATE INDEX idx_users_email ON users(email);-- 创建 GIN 索引 (JSON/数组/全文搜索)CREATE INDEX idx_users_profile ON users USING GIN (profile);-- 并发创建索引 (不阻塞写入)CREATE INDEX CONCURRENTLY idx_users_name ON users(name);-- 查看索引\\di -- psql 命令行
五、高级特性
1. JSON 操作
sql复制代码
-- 插入 JSONINSERT INTO products (data) VALUES (\'{\"name\": \"Laptop\", \"specs\": {\"ram\": 16, \"storage\": \"512GB\"}}\');-- 查询 JSONSELECT data->>\'name\' AS product_name, data->\'specs\'->>\'ram\' AS memory FROM products;
2. 数组操作
sql复制代码
-- 查询包含元素的数组SELECT * FROM posts WHERE tags @> ARRAY[\'postgres\'];-- 数组连接UPDATE users SET tags = tags || ARRAY[\'vip\'];
3. 地理空间 (PostGIS)
sql复制代码
-- 查询 5km 范围内的点SELECT name FROM places WHERE ST_DWithin( location, ST_MakePoint(-74.0, 40.7)::geography, 5000);
六、事务控制
sql复制代码
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 显式设置保存点 SAVEPOINT transfer_point;COMMIT; -- 或 ROLLBACK TO transfer_point;
七、权限管理
sql复制代码
-- 创建角色CREATE ROLE analyst WITH LOGIN PASSWORD \'secure123\';-- 授权表访问GRANT SELECT ON users TO analyst;-- 授权模式访问GRANT USAGE ON SCHEMA sales TO analyst;-- 撤销权限REVOKE DELETE ON orders FROM analyst;
八、性能优化
sql复制代码
-- 查看执行计划EXPLAIN ANALYZE SELECT * FROM large_table WHERE category = \'A\';-- 表统计信息ANALYZE users; -- 更新统计信息-- 重建索引REINDEX INDEX CONCURRENTLY idx_users_email;-- 查询性能监控SELECT * FROM pg_stat_statements; -- 需要启用扩展
九、维护命令
sql复制代码
-- 查看锁信息SELECT * FROM pg_locks;-- 终止查询SELECT pg_cancel_backend(pid); -- 取消查询SELECT pg_terminate_backend(pid); -- 终止会话-- 扩展管理CREATE EXTENSION IF NOT EXISTS postgis; -- 安装扩展\\dx -- 查看已安装扩展 (psql)
十、实用函数
sql复制代码
-- 日期处理SELECT NOW(), EXTRACT(YEAR FROM created_at) AS year, TO_CHAR(created_at, \'YYYY-MM-DD HH24:MI\') AS formatted;-- 条件表达式SELECT name, CASE WHEN age < 20 THEN \'Teen\' WHEN age BETWEEN 20 AND 65 THEN \'Adult\' ELSE \'Senior\' END AS age_groupFROM users;-- 聚合过滤SELECT department_id, AVG(salary) FILTER (WHERE full_time) AS avg_fulltime_salaryFROM employeesGROUP BY department_id;
PostgreSQL 特色功能提醒:
- 支持复杂数据类型:JSON/JSONB、数组、范围类型、几何类型
- 高级索引:GIN (全文搜索/JSON)、GiST (空间数据)、BRIN (大表范围)
- 事务性 DDL:所有 DDL 操作可包含在事务中
- 多版本并发控制 (MVCC):高性能读写并发
- 表分区:支持范围/列表/哈希分区
- 逻辑复制:数据变更流式传输
注意:在 psql 命令行中:
- 使用
\\?
查看帮助- 使用
\\timing
开启查询耗时统计- 使用
\\x
切换扩展显示模式(适用于宽结果集)