前置知识: SQL

PostgreSQL 语法速查

1 minBeginner
postgresqldatabasecheatsheet

常用 PostgreSQL 命令速查表，含使用场景、常见错误，支持搜索和复制

学习指引

前置知识

  • 掌握数据库基本概念（表、字段、记录）
  • 已完成 PostgreSQL 本地安装

推荐学习顺序

  1. 先学习【数据库与表操作】分组，掌握建库建表
  2. 再学习【数据查询】分组，掌握查询与联表
▼

数据库与表操作

PostgreSQL 操作的第一步。本组命令用于管理数据库和表结构，与 MySQL 有相似之处但语法存在差异。

创建数据库

CREATE DATABASE mydb
  WITH ENCODING = 'UTF8'
  LC_COLLATE = 'zh_CN.UTF-8'
  LC_CTYPE = 'zh_CN.UTF-8'
  TEMPLATE = template0;
输出: CREATE DATABASE
场景: 创建新数据库。PostgreSQL 建议显式指定编码和排序规则，以正确支持中文排序和比较。

常见错误

  • 错误: CREATE DATABASE mydb; -- 解决: 显式指定 ENCODING、LC_COLLATE 和 LC_CTYPE，或使用 template0 模板确保设置生效
  • 错误: CREATE DATABASE mydb; -- 解决: PostgreSQL 不支持 IF NOT EXISTS 语法创建数据库，需先检查：SELECT 1 FROM pg_database WHERE datname='mydb'
进阶: PostgreSQL 中使用 \l 元命令列出所有数据库，\c mydb 切换数据库，与 MySQL 的 SHOW DATABASES 和 USE 不同。

创建表

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  age INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
输出: CREATE TABLE
场景: 在数据库中创建新表。SERIAL 是 PostgreSQL 的自增整数类型，等同于 INTEGER + 自增序列。

常见错误

  • 错误: CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, ...); -- 解决: PostgreSQL 使用 SERIAL 或 GENERATED ALWAYS AS IDENTITY，不支持 MySQL 的 AUTO_INCREMENT
  • 错误: CREATE TABLE users (...); -- 解决: 确保已连接到正确的数据库，并检查 search_path 设置
进阶: PostgreSQL 10+ 推荐使用 GENERATED ALWAYS AS IDENTITY 替代 SERIAL，更符合 SQL 标准，且可防止手动插入覆盖序列值。

修改表结构

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

-- 修改列类型
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

-- 删除列
ALTER TABLE users DROP COLUMN phone;
输出: ALTER TABLE
场景: 修改已有表的结构，包括添加列、修改列类型、删除列等。PostgreSQL 的 ALTER TABLE 语法与 MySQL 有差异。

常见错误

  • 错误: ALTER TABLE users MODIFY COLUMN age BIGINT; -- 解决: PostgreSQL 使用 ALTER COLUMN ... TYPE 而非 MySQL 的 MODIFY COLUMN
  • 错误: ALTER TABLE users ALTER COLUMN username TYPE INTEGER; -- 解决: 类型不兼容时需提供转换表达式：ALTER TABLE users ALTER COLUMN col TYPE integer USING col::integer
进阶: ALTER TABLE 操作会获取表级锁，大表修改可能导致阻塞。可使用 pg_repack 扩展在不锁表的情况下重建表。

删除表

DROP TABLE IF EXISTS users CASCADE;
输出: DROP TABLE
场景: 删除表及其所有数据。CASCADE 会同时删除依赖于该表的对象（如外键引用、视图等），务必谨慎使用。

常见错误

  • 错误: DROP TABLE users;（有其他表通过外键引用该表） -- 解决: 先删除依赖对象，或使用 CASCADE 自动级联删除：DROP TABLE users CASCADE
  • 错误: DROP TABLE users;（表不存在） -- 解决: 使用 IF EXISTS 避免报错：DROP TABLE IF EXISTS users
进阶: DROP TABLE 是事务性操作，可以在事务中回滚。这与 MySQL 的部分存储引擎不同。
▼

数据查询

查询是 SQL 最核心的能力。本组命令用于从表中检索数据，PostgreSQL 在标准 SQL 基础上提供了丰富的扩展功能。

基本查询

SELECT id, username, email FROM users;
输出: id | username | email ----+----------+------------------- 1 | 张三 | zhangsan@mail.com 2 | 李四 | lisi@mail.com (2 rows)
场景: 从表中检索指定列的数据。PostgreSQL 的输出格式与 MySQL 不同，使用对齐的文本表格而非 ASCII 边框。

常见错误

  • 错误: SELECT id, username FROM Users;（大小写问题） -- 解决: PostgreSQL 中未加双引号的标识符会自动转为小写，如需区分大小写需在建表和查询时都使用双引号
进阶: PostgreSQL 支持 DISTINCT ON 去重并保留指定列的首条记录：SELECT DISTINCT ON (category) * FROM products ORDER BY category, price;

条件查询

SELECT id, username, email FROM users WHERE age >= 18 AND username LIKE '张%';
输出: id | username | email ----+----------+------------------- 1 | 张三 | zhangsan@mail.com (1 row)
场景: 根据条件筛选数据。PostgreSQL 支持 ILIKE 进行不区分大小写的模糊匹配，这是相对于 MySQL 的扩展功能。

常见错误

  • 错误: SELECT * FROM users WHERE username LIKE '%张%';（中文模糊匹配性能差） -- 解决: 中文模糊查询建议使用 pg_trgm 扩展创建 GIN 索引以提升性能
  • 错误: SELECT * FROM users WHERE age = '18';（类型不匹配） -- 解决: 确保比较值的类型与列类型一致：age = 18（整数而非字符串）
进阶: PostgreSQL 支持 ILIKE 不区分大小写匹配，以及 ~ 正则匹配：WHERE username ~ '^张.*三$'。

联表查询

SELECT u.username, o.order_id, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;
输出: username | order_id | total ----------+----------+------- 张三 | 1 | 250.00 (1 row)
场景: 从多个关联表中联合查询数据。JOIN 是最常用的联表方式，还有 LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN。

常见错误

  • 错误: SELECT * FROM users, orders;（缺少 JOIN 条件） -- 解决: 始终使用 JOIN ... ON 指定关联条件，避免隐式联表
  • 错误: SELECT id FROM users JOIN orders ON id = user_id;（列名歧义） -- 解决: 歧义列名必须加表别名前缀：u.id = o.user_id
进阶: PostgreSQL 支持 LATERAL JOIN，允许子查询引用前面表的列；还支持 USING 简化等值连接：JOIN orders USING (user_id)。

分组聚合

SELECT u.username, COUNT(o.id) AS order_count, SUM(o.total) AS total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
HAVING COUNT(o.id) > 0
ORDER BY total_amount DESC;
输出: username | order_count | total_amount ----------+-------------+-------------- 张三 | 3 | 750.00 李四 | 1 | 200.00 (2 rows)
场景: 按指定列分组后进行聚合统计。GROUP BY 分组，HAVING 过滤分组结果。执行顺序：WHERE -> GROUP BY -> HAVING -> ORDER BY。

常见错误

  • 错误: SELECT username, age, COUNT(*) FROM users GROUP BY username; -- 解决: SELECT 中非聚合列必须出现在 GROUP BY 中：GROUP BY username, age
  • 错误: WHERE COUNT(*) > 5（在 WHERE 中使用聚合函数） -- 解决: 聚合函数过滤必须使用 HAVING：HAVING COUNT(*) > 5
进阶: PostgreSQL 支持 GROUPING SETS、ROLLUP 和 CUBE 进行多维分组统计，比多次 UNION ALL 更高效。