PostgreSQL 速查表

本章提供 PostgreSQL 常用命令和操作的快速参考。

连接命令

# 本地连接
psql -U postgres

# 指定数据库连接
psql -U postgres -d mydb

# 指定主机和端口
psql -h localhost -p 5432 -U postgres -d mydb

# 执行命令后退出
psql -U postgres -d mydb -c "SELECT version();"

# 导入 SQL 文件
psql -U postgres -d mydb < backup.sql

# 导出查询结果
psql -U postgres -d mydb -c "SELECT * FROM users" -o output.txt

psql 常用命令

# 数据库操作
\l                  # 列出所有数据库
\l+ mydb           # 查看数据库详情

# 表操作
\d                  # 列出当前数据库所有表
\d users            # 查看 users 表结构
\d+ users           # 详细查看 users 表
\dt                 # 列出所有表

# 索引操作
\di                 # 列出所有索引

# 用户和权限
\du                 # 列出所有用户
\du+                # 详细列出用户

# 其他
\?                  # 帮助
\h                  # SQL 命令帮助
\h SELECT           # 查看 SELECT 命令帮助
\! cmd              # 执行 shell 命令
\c mydb            # 切换数据库
\conninfo          # 查看连接信息
\x                  # 切换扩展显示模式
\timing            # 开启/关闭执行时间显示

数据库操作

-- 创建数据库
CREATE DATABASE mydb;

-- 创建数据库（带选项）
CREATE DATABASE mydb 
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    TEMPLATE = template0;

-- 删除数据库
DROP DATABASE IF EXISTS mydb;

-- 查看数据库
SELECT datname FROM pg_database;

-- 查看当前数据库
SELECT current_database();

表操作

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100),
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
\d users
SELECT * FROM information_schema.columns 
WHERE table_name = 'users';

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 修改列
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

-- 重命名表
ALTER TABLE users RENAME TO app_users;

-- 删除表
DROP TABLE IF EXISTS users;

数据类型

数值类型

-- 整数
SMALLINT, INTEGER, BIGINT

-- 浮点数
REAL, DOUBLE PRECISION, NUMERIC(10, 2)

-- 自增
SERIAL, BIGSERIAL

字符串类型

CHAR(10)     -- 固定长度
VARCHAR(100) -- 可变长度
TEXT         -- 无限制

日期时间类型

DATE         -- 日期
TIME         -- 时间
TIMESTAMP    -- 日期时间（无时区）
TIMESTAMPTZ  -- 日期时间（带时区）
INTERVAL     -- 时间间隔

JSON 类型

JSON   -- 原始 JSON
JSONB  -- 二进制 JSON（推荐）

数据操作

INSERT 插入

-- 插入单行
INSERT INTO users (username, email) VALUES ('john', '[email protected]');

-- 插入多行
INSERT INTO users (username, email) VALUES 
    ('a', '[email protected]'),
    ('b', '[email protected]');

-- 插入并返回
INSERT INTO users (username) VALUES ('john') RETURNING id;

SELECT 查询

-- 基本查询
SELECT * FROM users;
SELECT username, email FROM users;

-- 条件查询
SELECT * FROM users WHERE age >= 18;

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 分页
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 去重
SELECT DISTINCT department FROM users;

UPDATE 更新

-- 更新数据
UPDATE users SET age = 25 WHERE username = 'john';

-- 批量更新
UPDATE users SET age = age + 1 WHERE status = 'active';

-- 更新并返回
UPDATE users SET age = 30 WHERE id = 1 RETURNING *;

DELETE 删除

-- 删除数据
DELETE FROM users WHERE id = 1;

-- 清空表
DELETE FROM users;
-- 或
TRUNCATE TABLE users RESTART IDENTITY;

条件查询

-- WHERE 条件
WHERE age = 25
WHERE age != 25
WHERE age > 25
WHERE age >= 25 AND age <= 30
WHERE age BETWEEN 20 AND 30
WHERE name IN ('john', 'jane')
WHERE name LIKE 'j%'
WHERE name ILIKE 'JOHN'  -- 不区分大小写
WHERE name IS NULL
WHERE name IS NOT NULL

连接查询

-- 内连接
SELECT * FROM a INNER JOIN b ON a.id = b.a_id;

-- 左连接
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;

-- 右连接
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

-- 全外连接
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;

-- 自连接
SELECT a.name, b.name AS manager
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.id;

聚合函数

-- 常用聚合
COUNT(*)      -- 行数
SUM(column)   -- 求和
AVG(column)   -- 平均
MAX(column)   -- 最大
MIN(column)   -- 最小

-- 分组聚合
SELECT department, COUNT(*), AVG(salary)
FROM users
GROUP BY department
HAVING AVG(salary) > 5000;

-- 字符串聚合
STRING_AGG(name, ',')           -- 逗号分隔
STRING_AGG(name, ',' ORDER BY salary)  -- 带排序

窗口函数

-- 排名
ROW_NUMBER() OVER (ORDER BY salary DESC)
RANK() OVER (ORDER BY salary DESC)
DENSE_RANK() OVER (ORDER BY salary DESC)

-- 前后值
LAG(salary) OVER (ORDER BY id)
LEAD(salary) OVER (ORDER BY id)

-- 聚合窗口
SUM(salary) OVER (PARTITION BY department)
AVG(salary) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

索引操作

-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_dept_salary ON users(department, salary);
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 创建特殊索引
CREATE INDEX idx_users_email_gin ON users USING GIN (email);
CREATE INDEX idx_users_name_lower ON users (LOWER(name));

-- 查看索引
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- 删除索引
DROP INDEX idx_users_email;

事务控制

-- 开始事务
BEGIN;
-- 或
START TRANSACTION;

-- 提交
COMMIT;

-- 回滚
ROLLBACK;

-- 保存点
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;

用户和权限

-- 创建用户
CREATE USER john WITH PASSWORD 'password';

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO john;
GRANT SELECT, INSERT ON users TO john;
GRANT ALL ON ALL TABLES IN SCHEMA public TO john;

-- 撤销权限
REVOKE INSERT ON users FROM john;

-- 查看权限
\dp users

-- 删除用户
DROP USER john;

备份和恢复

# 备份数据库
pg_dump -U postgres mydb > backup.sql

# 备份表
pg_dump -U postgres -t users mydb > users.sql

# 恢复数据库
psql -U postgres mydb < backup.sql

# 备份所有数据库
pg_dumpall -U postgres > all_databases.sql

# 只备份结构
pg_dump -U postgres -s mydb > schema.sql

# 只备份数据
pg_dump -U postgres -a mydb > data.sql

常用函数

字符串函数

LENGTH('hello')           -- 5
UPPER('hello')           -- 'HELLO'
LOWER('HELLO')           -- 'hello'
TRIM('  hello  ')        -- 'hello'
SUBSTRING('hello', 1, 3) -- 'hel'
REPLACE('hello', 'l', 'x') -- 'hexxo'
CONCAT('a', 'b', 'c')    -- 'abc'

数值函数

ROUND(3.567, 2)          -- 3.57
CEIL(3.1)                -- 4
FLOOR(3.9)               -- 3
ABS(-5)                  -- 5
MOD(10, 3)               -- 1

日期函数

NOW()                    -- 当前时间戳
CURRENT_DATE             -- 当前日期
CURRENT_TIME             -- 当前时间
EXTRACT(YEAR FROM now()) -- 年份
DATE_TRUNC('day', now()) -- 日期截断

流程控制

-- CASE
CASE 
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'C'
END

-- COALESCE
COALESCE(NULL, 'default')  -- 'default'

-- NULLIF
NULLIF(10, 10)            -- NULL

JSON 操作

-- 创建 JSON
'{"name": "John", "age": 30}'::json

-- 提取字段
data->>'name'    -- 获取为文本
data->'name'     -- 获取为 JSON

-- 查询
WHERE data @> '{"age": 30}'
WHERE data ? 'name'

-- 函数
JSON_OBJECT_AGG(key, value)
JSON_AGG(column)

性能分析

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 详细分析（实际执行）
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- 查看慢查询日志
SHOW log_min_duration_statement;
SELECT * FROM pg_stat_statements;

-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('users'));
SELECT pg_size_pretty(pg_relation_size('users'));

系统信息

-- 版本信息
SELECT version();

-- 当前用户
SELECT current_user;
SELECT user;

-- 数据库信息
SELECT current_database();
SELECT current_schema();

-- 配置信息
SHOW max_connections;
SELECT * FROM pg_settings;

小结

本速查表涵盖了 PostgreSQL 的常用操作：

连接和 psql 命令
数据库和表操作
数据类型
CRUD 操作
查询和连接
聚合和窗口函数
索引操作
事务控制
用户和权限
备份恢复
常用函数
JSON 操作
性能分析

建议将本页面收藏，作为日常开发的快速参考。

连接命令​

psql 常用命令​

数据库操作​

表操作​

数据类型​

数值类型​

字符串类型​

日期时间类型​

JSON 类型​

数据操作​

INSERT 插入​

SELECT 查询​

UPDATE 更新​

DELETE 删除​

条件查询​

连接查询​

聚合函数​

窗口函数​

索引操作​

事务控制​

用户和权限​

备份和恢复​

常用函数​

字符串函数​

数值函数​

日期函数​

流程控制​

JSON 操作​

性能分析​

系统信息​

小结​

连接命令

psql 常用命令

数据库操作

表操作

数据类型

数值类型

字符串类型

日期时间类型

JSON 类型

数据操作

INSERT 插入

SELECT 查询

UPDATE 更新

DELETE 删除

条件查询

连接查询

聚合函数

窗口函数

索引操作

事务控制

用户和权限

备份和恢复

常用函数

字符串函数

数值函数

日期函数

流程控制

JSON 操作

性能分析

系统信息

小结