PostgreSQL

这一课你将能做到

1. 在本地装好 PostgreSQL 并用 psql 连上，看懂 \l、\dt、\d 这组元命令
2. 建库建表，正确使用主键、NOT NULL、UNIQUE、外键等约束
3. 熟练写出 INSERT / SELECT / UPDATE / DELETE，并用 WHERE、ORDER BY、聚合做日常查询

为什么要学

程序一重启内存数据就没了，持久化是后端的第一刚需。关系型数据库（RDBMS）用「表 + 行 + 关系」组织数据，五十年来一直是业务系统的存储底座，SQL 也是你面试必考、工作天天写的东西。

人话：数据库就是一个「管得极其严格的 Excel 文件集合」——每张表有固定的列（schema），行与行之间可以关联（外键），所有修改都受约束保护。PostgreSQL（简称 PG）是当前功能最全、增长最快的开源关系库，JSONB、数组、全文检索全都内置；国内公司常用的 MySQL 你学会了 PG 之后只需半天适应。

本课先直接用 psql 敲 SQL——先理解数据库本身，第 9 课再学用 ORM 隔着代码操作它。

先建立图像

一张 notes 表长这样：

表: notes
┌────┬──────────────┬─────────────────┬─────────────────────┐
│ id │ title        │ content         │ created_at          │
├────┼──────────────┼─────────────────┼─────────────────────┤
│ 1  │ 学习计划      │ 学完 PG 基础     │ 2026-01-01 10:00:00 │
│ 2  │ 购物清单      │ 咖啡、键盘       │ 2026-01-01 11:30:00 │
│ 3  │ 面试笔记      │ 事务隔离级别...  │ 2026-01-02 09:15:00 │
└────┴──────────────┴─────────────────┴─────────────────────┘
  ↑ 主键：唯一标识一行      ↑ 每列都有类型和约束

实例（数据库服务）→ 数据库（database）→ 模式（schema）→ 表（table）→ 行（row），是一层层嵌套的包含关系。

跟着做

步骤 1：安装并连接

方式 A：Docker（推荐，干净可删）：

docker run --name pg-demo -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:16
docker exec -it pg-demo psql -U postgres

方式 B：Windows 安装包：从 postgresql.org/download 下载 EDB 安装器，安装时设置 postgres 用户密码，装完后在开始菜单打开 SQL Shell (psql)，一路回车（默认本机 5432、用户 postgres），输入安装时设的密码。

连接成功会看到提示符 postgres=#。先认识几个元命令（psql 专属，反斜杠开头，不需要分号）：

\l        -- 列出所有数据库
\c mydb   -- 切换数据库
\dt       -- 列出当前库的表
\d notes  -- 查看表结构
\q        -- 退出

步骤 2：建库、建表

CREATE DATABASE notedb;
\c notedb

建表（Modern 写法：IDENTITY 自增，别再用老的 SERIAL）：

CREATE TABLE notes (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title      varchar(100) NOT NULL,
    content    text         NOT NULL DEFAULT '',
    done       boolean      NOT NULL DEFAULT false,
    created_at timestamptz  NOT NULL DEFAULT now(),
    UNIQUE (title)          -- 标题不许重复
);

验证：

\d notes

能看到列名、类型、约束——这就是 schema。先有 schema 再有数据，是关系库和「随手塞 JSON」最大的区别。

步骤 3：INSERT 写入数据

INSERT INTO notes (title, content) VALUES
    ('学习计划', '本周学完 PG 基础'),
    ('购物清单', '咖啡、机械键盘'),
    ('面试笔记', '事务隔离级别与 MVCC');

-- RETURNING 是 PG 特色：插入的同时把结果拿回来
INSERT INTO notes (title) VALUES ('带 RETURNING 的一条') RETURNING id, created_at;

步骤 4：SELECT 查询——日常 80% 的工作

SELECT * FROM notes;                          -- 全查（只用于调试）
SELECT id, title FROM notes;                  -- 只取需要的列
SELECT * FROM notes WHERE done = false;       -- 条件过滤
SELECT * FROM notes WHERE title LIKE '%面试%'; -- 模糊匹配（ilike 忽略大小写）
SELECT * FROM notes ORDER BY created_at DESC LIMIT 10;  -- 排序 + 截断

聚合统计：

SELECT count(*)                            AS 总数,
       count(*) FILTER (WHERE done)        AS 已完成,   -- PG 特色语法
       min(created_at)                     AS 最早一条
FROM notes;

-- 配合 GROUP BY 按天统计
SELECT date_trunc('day', created_at) AS day, count(*)
FROM notes
GROUP BY day
ORDER BY day;

步骤 5：UPDATE 与 DELETE

UPDATE notes SET done = true WHERE id = 1 RETURNING *;   -- RETURNING 确认改了哪行
DELETE FROM notes WHERE title = '购物清单' RETURNING id;

步骤 6：外键关联——关系型数据库的「关系」

CREATE TABLE tags (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name varchar(50) NOT NULL UNIQUE
);

CREATE TABLE note_tags (
    note_id bigint REFERENCES notes(id) ON DELETE CASCADE,
    tag_id  bigint REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (note_id, tag_id)        -- 联合主键：防止重复关联
);

INSERT INTO tags (name) VALUES ('学习'), ('生活');
INSERT INTO note_tags VALUES (1, 1), (2, 2);   -- note 1 关联「学习」，note 2 关联「生活」

-- 多表连接查询
SELECT n.title, t.name AS tag
FROM note_tags nt
JOIN notes n ON n.id = nt.note_id
JOIN tags  t ON t.id = nt.tag_id
ORDER BY n.id;

ON DELETE CASCADE 表示删除笔记时自动清理它的关联行——完整性由数据库兜底，不靠应用代码自觉。

步骤 7：与 MySQL 的差异速查

用惯了 PG，遇到 MySQL 项目只需对照这张表：

维度	PostgreSQL	MySQL 8
自增主键	GENERATED ALWAYS AS IDENTITY（老写法 SERIAL）	AUTO_INCREMENT
时间类型	timestamptz（带时区，推荐）	datetime / timestamp
JSON	jsonb（可索引、可查询，强）	json（能力弱一档）
插入返回新行	RETURNING *	无，需再查一次或用 LAST_INSERT_ID()
模糊大小写	ilike	like（取决于排序规则）
upsert	INSERT ... ON CONFLICT (id) DO UPDATE	INSERT ... ON DUPLICATE KEY UPDATE
「schema」一词	库下有多个 schema	database 和 schema 近似同义
默认端口	5432	3306
引号规则	双引号 = 标识符（表名/列名），单引号 = 字符串值	相同，但 MySQL 反引号 ` 也可包标识符

讲清楚要点

1. 约束是数据库级的安全网：NOT NULL/UNIQUE/PRIMARY KEY/FOREIGN KEY 让非法数据根本进不了库。应用层校验会被绕过，约束不会。
2. 类型要选对：布尔用 boolean 不用 int；时间用 timestamptz 不用 varchar；长文本用 text（PG 里 text 和 varchar 性能无差异，不限长就用 text）。
3. NULL 是「未知」不是 0 或空串：WHERE col = NULL 永远查不到，必须用 IS NULL；count(col) 不计 NULL 行而 count(*) 计所有行。
4. 主键选型：自增 bigint 是默认答案，简单有序对索引友好。分布式场景再考虑雪花 ID 等方案，别一上来就 UUID（随机写会打乱索引页）。
5. 永远别裸奔执行 UPDATE/DELETE：养成「先 SELECT 同条件确认范围 → 再改/删」的习惯，生产事故一大半来自忘写 WHERE。

你可能会懵的地方

• schema、database、表到底什么关系？ PG 里：实例 → database → schema → table。\c 切换 database，一个 database 内默认 schema 叫 public。初学者全程待在 public 即可；多 schema 用于大型系统按模块隔离表名。

• SERIAL 和 IDENTITY 用哪个？ 效果相同，IDENTITY 是 SQL 标准写法且能禁止手动插入 id（GENERATED ALWAYS），新项目一律 IDENTITY。

• 为什么我的表名变大写了反而查不到？ PG 把未加引号的标识符全部折叠为小写。CREATE TABLE Notes (...) 实际创建 notes；想用大写就得每次都写 "Notes"——别这么干，全小写加下划线是规范。

• LIKE 和 ILIKE 性能如何？前缀模糊能走索引吗？ LIKE 'abc%'（前缀确定）可以走 B-tree 索引；LIKE '%abc'（前置通配）走不了，数据量大要用 pg_trgm 扩展 + GIN 索引，第 5 课细讲索引。

• 事务不是这课的吗？为什么先不提？ psql 里每条语句默认就是一个自动提交的事务。多语句打包（BEGIN/COMMIT/ROLLBACK）、隔离级别、锁，是下一课的正题。

错法对照

错法	问题	正解
WHERE title = "abc"	双引号被当成列名，报列不存在	字符串值用单引号 'abc'
UPDATE notes SET done=true;（忘 WHERE）	全表被改	先 SELECT 确认条件，再补 WHERE 执行
= NULL 判断空值	永远为假	IS NULL / IS NOT NULL
所有字段都用 varchar(255)	无意义的习惯数字，语义不清	按业务定长（枚举、状态码）或直接 text
存 JSON 字符串到 text 列逃避建模	无法用 SQL 查询和建索引	该建表建表，真需要灵活结构再用 jsonb
误删后才发现没备份	无法恢复	本地练习也养成先 pg_dump 的习惯

卡点急救

1. psql: error: connection refused：服务没起。Docker 方式 docker start pg-demo；Windows 服务方式在「服务」里启动 postgresql-x64-16，或 pg_ctl start。
2. FATAL: password authentication failed：密码错。Docker 环境重置最快：docker rm -f pg-demo 重建并设好 -e POSTGRES_PASSWORD。
3. \dt 显示 Did not find any relations：连错库了。\c notedb 切换后再看。
4. 中文乱码或报编码错误：Windows 终端先执行 chcp 65001；确认建库编码为 UTF8（\l 查看）。
5. 表删不干净想重来：DROP TABLE note_tags, notes, tags;（先删被引用的从表），或重建整个库 DROP DATABASE notedb; CREATE DATABASE notedb;。

面试常问

• PostgreSQL 和 MySQL 怎么选？ PG：功能更全（jsonb/数组/窗口函数/CTE 更早完善）、标准兼容好、复杂查询与分析强，新项目技术选型越来越偏它；MySQL：国内存量生态最大、运维体系成熟、复制方案多。两者核心 SQL 通用，精通一个另一个很快上手。

• CHAR / VARCHAR / TEXT 的区别？ CHAR(n) 定长补空格；VARCHAR(n) 变长限长；TEXT 不限长。PG 内部 varchar 和 text 存储一致，性能无差，n 只是约束。

• 什么是外键约束？生产上为什么有时禁用？ 保证引用完整性（子表记录必须指向存在的父表记录）。超高并发写入场景外键校验有额外开销，有些团队把完整性上移到应用层——这是有取舍的工程决策，不是「外键没用」。

• 为什么主键不建议用无序 UUID？ InnoDB/B-tree 索引按主键有序组织，随机 UUID 导致频繁页分裂、缓存命中率低。要 UUID 就用有序版本（如 UUIDv7）或雪花 ID。

自测三问

1. 写 SQL：查询最近 7 天创建、未完成的笔记，按创建时间倒序，只返回 id 和 title。
2. notes 表有 3 行，content 有 1 行为 NULL，count(*)、count(content) 各返回多少？
3. 给 notes 加一张子表 comments（评论），要求：删笔记时评论自动删除。写出建表 SQL。

小结一句话

PostgreSQL 用 schema 约束保证数据质量，用 SQL 完成增删改查和关联分析；先 SELECT 再 UPDATE、值用单引号、NULL 用 IS NULL——这三条习惯从今天开始养成。

延伸

• PostgreSQL 官方教程
• pg-tutor 在线练 SQL——浏览器里直接敲 SQL，不想装环境时用
• 下一课：数据库进阶——索引让查询快 100 倍的原理、事务与隔离级别、连接池。