PostgreSQL 常用SQL操作命令

PostgreSQL 常用SQL操作命令

一、数据库操作

-- 创建数据库（指定编码）
CREATE DATABASE mydb ENCODING 'UTF8' LC_COLLATE 'en_US.utf8' LC_CTYPE 'en_US.utf8';-- 切换数据库
\c mydb-- 删除数据库（需断开连接）
DROP DATABASE IF EXISTS mydb WITH (FORCE);

二、表操作

2.1 基础表管理

-- 创建表（含约束）
CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,salary NUMERIC(10,2) CHECK (salary > 0),department_id INT REFERENCES departments(id),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);-- 修改表结构
ALTER TABLE employeesADD COLUMN email VARCHAR(100) UNIQUE,ALTER COLUMN name TYPE VARCHAR(80);

2.2 索引管理

-- 创建复合索引
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary DESC);-- 创建表达式索引
CREATE INDEX idx_emp_lower_name ON employees(LOWER(name));-- 查看索引信息
\di+

三、数据操作

3.1 CRUD操作

-- 批量插入（RETURNING返回结果）
INSERT INTO employees (name, salary) 
VALUES ('Alice', 65000),('Bob', 72000)
RETURNING id, created_at;-- 更新带条件
UPDATE employees 
SET salary = salary * 1.05 
WHERE department_id = 2 AND salary < 80000;-- 删除软删除数据
DELETE FROM logs 
WHERE status = 'archived' AND created_at < NOW() - INTERVAL '2 years';

3.2 高级查询

-- CTE递归查询
WITH RECURSIVE org_tree AS (SELECT id, name, manager_idFROM employeesWHERE id = 100UNION ALLSELECT e.id, e.name, e.manager_idFROM employees eINNER JOIN org_tree o ON o.id = e.manager_id
)
SELECT * FROM org_tree;-- 窗口函数使用
SELECT name,salary,department_id,RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) 
FROM employees;

四、事务控制

BEGIN;SAVEPOINT before_update;UPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;-- 模拟错误检测-- ROLLBACK TO before_update;
COMMIT;

五、JSON操作

-- 创建JSON字段表
CREATE TABLE products (id SERIAL PRIMARY KEY,details JSONB NOT NULL
);-- JSON路径查询
SELECT details->>'name' AS product_name,details->'specs'->'weight' AS weight 
FROM products
WHERE details @> '{"category": "electronics"}';

六、维护命令

-- 分析表统计信息
ANALYZE VERBOSE employees;-- 重建索引
REINDEX INDEX CONCURRENTLY idx_emp_name;-- 查看锁信息
SELECT * FROM pg_locks 
WHERE relation = 'employees'::regclass;-- 备份指定表
pg_dump -t employees mydb > employees_backup.sql

七、权限管理

-- 创建角色
CREATE ROLE analyst WITHLOGINPASSWORD 'secure123'VALID UNTIL '2025-12-31';-- 授权表访问
GRANT SELECT, INSERT ON employees TO analyst;-- 查看权限
\dp employees

八、性能优化

-- 查看查询计划
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM employees WHERE salary > 50000;-- 设置工作内存
SET work_mem = '64MB';-- 强制使用索引
SET enable_seqscan = off;