一文掌握 PostgreSQL 的各种指令（PostgreSQL指令备忘）

时间：2025-03-18 16:38:41

引言

PostgreSQL 作为一款功能强大、开源的关系型数据库管理系统（RDBMS），以其高扩展性、SQL 标准兼容性以及丰富的功能特性，成为企业级应用的首选数据库之一。无论是开发、运维还是数据分析，掌握 PostgreSQL 的核心指令是高效工作的关键。本文将从基础到高级，全面梳理 PostgreSQL 的常用指令，并结合实战场景与创新技巧，帮助读者快速掌握 PostgreSQL 的精髓。

一、数据库与用户管理

1.1 数据库操作

创建数据库
CREATE DATABASE mydb WITH OWNER = myuser ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE = template0;
  • OWNER：指定数据库所有者。
  • ENCODING：设置字符编码。
  • TEMPLATE：基于模板创建数据库（template0为纯净模板）。
删除数据库
DROP DATABASE IF EXISTS mydb;
  • IF EXISTS：避免数据库不存在时报错。
切换数据库
\c mydb
  • 在 psql 命令行中快速切换数据库。

1.2 用户与权限管理

创建用户
CREATE USER myuser WITH PASSWORD 'mypassword';
  • 创建用户并设置密码。
修改用户密码
ALTER USER myuser WITH PASSWORD 'newpassword';
授予权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
  • 授予用户对数据库的所有权限。
撤销权限
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM myuser;
删除用户
DROP USER IF EXISTS myuser;

二、表与数据操作

2.1 表操作

创建表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary NUMERIC(10, 2),
    hire_date DATE DEFAULT CURRENT_DATE
);
  • SERIAL：自增主键。
  • NOT NULL：字段不允许为空。
  • DEFAULT：设置默认值。
修改表结构
ALTER TABLE employees ADD COLUMN department VARCHAR(50);
ALTER TABLE employees DROP COLUMN department;
ALTER TABLE employees RENAME COLUMN salary TO annual_salary;
删除表
DROP TABLE IF EXISTS employees;

2.2 数据操作

插入数据
INSERT INTO employees (name, salary, hire_date) VALUES ('Alice', 75000.00, '2023-01-15');
更新数据
UPDATE employees SET salary = 80000.00 WHERE name = 'Alice';
删除数据
DELETE FROM employees WHERE id = 1;
查询数据
SELECT * FROM employees WHERE salary > 50000 ORDER BY hire_date DESC;

三、索引与性能优化

3.1 创建索引

单列索引
CREATE INDEX idx_employees_name ON employees (name);
多列索引
CREATE INDEX idx_employees_name_salary ON employees (name, salary);
唯一索引
CREATE UNIQUE INDEX idx_employees_email ON employees (email);

3.2 删除索引

DROP INDEX IF EXISTS idx_employees_name;

3.3 查询性能分析

使用 EXPLAIN 分析查询计划：

EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 50000;
  • EXPLAIN：显示查询计划。
  • ANALYZE：执行查询并返回实际执行时间。

四、高级查询与数据处理

4.1 聚合函数

SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
  • AVG：计算平均值。
  • COUNT：统计行数。
  • HAVING：对聚合结果进行过滤。

4.2 窗口函数

SELECT name, salary, 
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
  • RANK()：计算排名。
  • OVER：定义窗口范围。

4.3 子查询

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

4.4 联合查询

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

五、事务与并发控制

5.1 事务管理

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
  • BEGIN：开始事务。
  • COMMIT：提交事务。
  • ROLLBACK：回滚事务。

5.2 锁机制

SELECT * FROM employees WHERE id = 1 FOR UPDATE;
  • FOR UPDATE：对查询结果加排他锁。

六、备份与恢复

6.1 逻辑备份

使用 pg_dump 备份数据库：

pg_dump -U myuser -d mydb -f mydb_backup.sql

6.2 逻辑恢复

psql -U myuser -d mydb -f mydb_backup.sql

6.3 物理备份

使用 pg_basebackup 进行全量备份：

pg_basebackup -U myuser -D /backup/mydb -Ft -Xs -P

七、扩展与插件

7.1 安装扩展

CREATE EXTENSION postgis;

7.2 常用扩展

  • postgis：地理信息系统支持。
  • pg_stat_statements：SQL 性能监控。
  • uuid-ossp：生成 UUID。

八、创新技巧与实战场景

8.1 JSONB 数据处理

PostgreSQL 支持 JSONB 数据类型，适用于半结构化数据存储：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB
);

INSERT INTO products (details) VALUES ('{"name": "Laptop", "price": 1200, "tags": ["electronics", "portable"]}');

SELECT details->>'name' AS product_name
FROM products
WHERE details @> '{"tags": ["electronics"]}';

8.2 全文搜索

使用 tsvector 和 tsquery 实现全文搜索：

SELECT title, content
FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'PostgreSQL & performance');

8.3 分区表

对大表进行分区，提升查询性能：

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sale_date DATE,
    amount NUMERIC(10, 2)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

结语

PostgreSQL 的强大功能使其成为现代数据管理的利器。通过本文的指令梳理与实战技巧，读者可以快速掌握 PostgreSQL 的核心操作，并在实际工作中灵活运用。无论是基础的数据管理，还是高级的性能优化与扩展功能，PostgreSQL 都能满足多样化的需求。未来，随着 PostgreSQL 生态的不断发展，其应用场景将更加广泛，成为数据驱动型企业的核心基础设施。

延伸阅读：

  • PostgreSQL 官方文档：https://www.postgresql.org/docs/
  • PostgreSQL 性能优化指南
  • 深入理解 PostgreSQL 的事务与并发控制

相关文章