PostgreSQL - 速查表

PostgreSQL 现代速查表

这份 PostgreSQL 速查表提供了基本命令和现代最佳实践的快速参考。PostgreSQL 是一个功能强大、开源的对象关系型数据库系统，以其可靠性、功能健壮性和高性能而闻名。本指南将帮助您在现代应用程序中有效使用 PostgreSQL。

.toc_container { width: 100%; clear: both; display: table; } .toc_column { width: 50%; float: left; }
@media (max-width: 600px) { .toc_column { width: 100%; float: none; } .second_child { margin-top: -15px; } }

1. 连接 PostgreSQL

使用 psql 命令行工具。在本地开发环境运行 PostgreSQL 最简单的方法是使用 Docker：

# 在 Docker 中启动 PostgreSQL
docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

# 使用 psql 连接
psql --host=localhost --username=postgres --dbname=postgres

检查服务器版本：

SELECT version();

2. 数据定义语言 (DDL)

创建表

对于自增主键，请使用 GENERATED ALWAYS AS IDENTITY（SQL 标准，Postgres 10 开始可用）。使用现代数据类型，如 UUID、TIMESTAMPTZ 和 JSONB。

CREATE TABLE employees (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    employee_uuid UUID DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

修改表

-- 添加列
ALTER TABLE employees ADD COLUMN start_date DATE;

-- 重命名列
ALTER TABLE employees RENAME COLUMN start_date TO hire_date;

-- 删除列
ALTER TABLE employees DROP COLUMN hire_date;

-- 添加约束
ALTER TABLE employees ADD CONSTRAINT salary_check CHECK (salary > 0);

删除表

DROP TABLE IF EXISTS employees;

3. 数据操作语言 (DML)

插入数据

-- 插入单行
INSERT INTO employees(name, email, department, salary)
VALUES('Alice', 'alice@example.com', 'Engineering', 90000);

-- 插入多行
INSERT INTO employees(name, email, department, salary) VALUES
('Bob', 'bob@example.com', 'Engineering', 95000),
('Charlie', 'charlie@example.com', 'Marketing', 85000);

更新数据

UPDATE employees
SET salary = salary * 1.05, department = 'Senior Engineering'
WHERE email = 'bob@example.com';

删除数据

DELETE FROM employees WHERE email = 'charlie@example.com';

4. 数据查询语言 (DQL)

SELECT name, salary FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC
LIMIT 10;

聚合函数

-- 获取工程部门的统计信息
SELECT
    COUNT(*) AS num_employees,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_payroll
FROM employees
WHERE department = 'Engineering';

数据分组

SELECT department, AVG(salary) as avg_dept_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5; -- Filter groups after aggregation

连接 (JOIN)

SELECT e.name, p.project_name
FROM employees AS e
INNER JOIN projects AS p ON e.id = p.employee_id;

模式匹配

-- 区分大小写的搜索
SELECT name FROM employees WHERE name LIKE 'A%';

-- 不区分大小写的搜索 (Postgres 特有)
SELECT name FROM employees WHERE name ILIKE 'a%';

5. 高级查询

公用表表达式 (CTE)

使用 CTE（WITH 子句）将复杂的查询分解为逻辑上清晰、易于理解的步骤。

WITH high_earners AS (
    SELECT id, name, department
    FROM employees
    WHERE salary > 100000
)
SELECT * FROM high_earners WHERE department = 'Engineering';

窗口函数

对与当前行相关的行集执行计算。

-- 按部门内薪资排名员工
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

CASE 语句

SELECT name, salary,
    CASE
        WHEN salary > 100000 THEN 'High'
        WHEN salary > 70000 THEN 'Medium'
        ELSE 'Standard'
    END AS salary_grade
FROM employees;

6. 事务与数据完整性

事务

将多个语句包装在事务中，以确保它们要么全部成功，要么全部失败（原子性）。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 或 ROLLBACK 以撤销更改

索引

创建索引以加快查询性能。外键通常是索引的良好候选。

-- 在常用查询列上创建索引
CREATE INDEX idx_employees_department ON employees(department);

-- 使用 EXPLAIN ANALYZE 分析查询性能
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Engineering';