> 技术文档 > PostgreSQL 常用 SQL 命令集合

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 特色功能提醒:

  1. 支持复杂数据类型:JSON/JSONB、数组、范围类型、几何类型
  2. 高级索引:GIN (全文搜索/JSON)、GiST (空间数据)、BRIN (大表范围)
  3. 事务性 DDL:所有 DDL 操作可包含在事务中
  4. 多版本并发控制 (MVCC):高性能读写并发
  5. 表分区:支持范围/列表/哈希分区
  6. 逻辑复制:数据变更流式传输

注意:在 psql 命令行中:

  • 使用 \\? 查看帮助
  • 使用 \\timing 开启查询耗时统计
  • 使用 \\x 切换扩展显示模式(适用于宽结果集)