PostgreSQL 学习

口渴的水

2025-11-25

PostgreSQL 从入门到精通：一份小白友好的“接地气”指南

欢迎来到 PostgreSQL (简称 "PG") 的世界！

你可能听说过“数据库”，觉得它很高大上，充满了复杂的术语。别怕！这份指南就是为你这样的小白准备的。我们将用最“接地气”的比喻，从零开始，带你一步步探索这个“世界上最先进的开源关系型数据库”。

我们的目标是：让你不仅“能看懂”，还能“用得溜”，同时不知不觉地掌握那些“技术性”很强的核心知识。

准备好了吗？让我们发车！

第一章：数据库是啥？PG 又是啥？ (入门篇)
- 1.1 什么是数据库？(你的超级“大仓库”)
- 1.2 什么是“关系型”数据库？(仓库的“分区管理”)
- 1.3 什么是 SQL？(仓库“管理员”的“普通话”)
- 1.4 为什么选择 PostgreSQL？(为什么这个“仓库”特别牛)
第二章：安装和启动 (准备篇)
- 2.1 在你的电脑上“建仓库” (Windows, macOS, Linux)
- 2.2 你的第一个工具：psql (“对讲机”)
- 2.3 连接到你的数据库 (“呼叫管理员”)
第三章：SQL 核心大法 (基础篇)
- 3.1 数据库的基本结构 (仓库、货架、货物)
- 3.2 万能四宝：CURD (增删改查)
- 3.3 CREATE：创建你自己的“货架” (表)
- 3.4 INSERT：往“货架”上“放货” (数据)
- 3.5 SELECT 和 WHERE：找到你想要的“货” (查询与过滤)
- 3.6 UPDATE：给“货物”换个“标签” (更新)
- 3.7 DELETE：把“货”下架 (删除)
第四章：SQL 进阶神技 (强化篇)
- 4.1 JOIN：跨“货架”找东西 (连接)
- 4.2 聚合函数：算算总账 (COUNT, SUM, AVG)
- 4.3 GROUP BY：给“货物”分分类 (分组)
- 4.4 ORDER BY 和 LIMIT：让“货物”排好队 (排序和分页)
- 4.5 子查询：套娃式查找 (复杂查询)
第五章：数据“规矩” (设计篇)
- 5.1 什么是“数据类型”？(规定货物“长啥样”)
- 5.2 PRIMARY KEY：每件“货”的“身份证” (主键)
- 5.3 FOREIGN KEY：建立“货物”间的“关系网” (外键)
- 5.4 NOT NULL 和 UNIQUE (“不能为空”和“不能重复”)
第六章：PG 内部探秘 (架构篇)
- 6.1 客户端/服务器模型 (C/S 架构)
- 6.2 它的“大脑”和“工人” (进程结构)
- 6.3 它是怎么“存东西”的 (存储结构)
- 6.4 MVCC：多人同时用也不打架的秘密 (并发控制)
第七章：仓库管理员的日常 (管理篇)
- 7.1 “发门禁卡”：用户和角色 (Users & Roles)
- 7.2 “授权”：GRANT 和 REVOKE
- 7.3 “数据备份”：pg_dump (有备无患)
- 7.4 “数据恢复”：pg_restore (出事别慌)
第八章：让查询飞起来 (性能篇)
- 8.1 什么是“索引”？(书的“目录”)
- 8.2 CREATE INDEX：创建“目录”
- 8.3 什么时候该用“目录”？
- 8.4 EXPLAIN：看看 PG 是怎么“找东西”的 (查询计划)
第九章：“事务” (安全篇)
- 9.1 什么是“事务”？(“要么全做完，要么全不做”)
- 9.2 ACID 是什么？(听着很“酸”，其实很重要)
- 9.3 BEGIN, COMMIT, ROLLBACK (开始、提交、反悔)
第十章：PG 的“独门绝技” (高级篇)
- 10.1 JSONB：直接存“非结构化”数据 (像 NoSQL 一样)
- 10.2 窗口函数：超级“分析师” (Window Functions)
- 10.3 CTE：让SQL更“易读” (Common Table Expressions)
- 10.4 扩展：PG 的“插件”生态 (PostGIS 等)
第十一章：从这里走向“精通” (后续)
- 11.1 官方文档：永远的“圣经”
- 11.2 社区和资源
- 11.3 总结

第一章：数据库是啥？PG 又是啥？ (入门篇)

1.1 什么是数据库？(你的超级“大仓库”)

想象一下，你开了一个巨大的电商公司，你有成千上万的商品、数百万的用户、每天上亿的订单。你怎么管理这些信息？

用 Excel？ 当数据量大了，几百万行，Excel 打开就得几分钟，多人同时编辑更是灾难。
用记事本？ ... 你在开玩笑吗？

数据库 (Database)，就是一个专门用来科学、高效、持久、安全地存储和管理数据的“超级数字大仓库”。

它就像一个纪律严明的巨型仓库：

持久存储：数据放在里面，关机重启也不会丢。
结构化：数据不是乱堆的，是分门别类（比如“用户区”、“订单区”）放好的。
高效查询：你想要找某个用户的信息，数据库能在一秒内给你翻出来。
并发控制：可以很多人（比如1万个用户）同时访问仓库，还不会乱套。
安全：有保安（权限控制），不是谁都能随便进出和拿东西。

1.2 什么是“关系型”数据库？(仓库的“分区管理”)

数据库也分很多种。最主流的一种叫做“关系型数据库”(Relational Database, RDBMS)。

“关系型”听着很玄乎，其实特简单。它就是用二维表 (Table) 来组织数据的。

`` (插图说明：一个像 Excel 一样的表格，有行和列。)

表 (Table)：就像仓库里的一个“货架”。比如，我们有“用户货架”（用户表）、“商品货架”（商品表）。
列 (Column)：就是“货架”上固定的“标签”。比如“用户货架”上，每一格都有“用户ID”、“姓名”、“手机号”这几个标签。
行 (Row)：就是放在“货架”上的一件件“货物”。一行就代表一个具体的用户（比如：ID=1, 姓名=张三, 手机号=138...）。
关系 (Relation)：就是“货架”之间的联系。比如，“订单货架”上的一笔订单，会通过“用户ID”关联到“用户货架”上的某个具体用户。这就是“关系”的由来。

1.3 什么是 SQL？(仓库“管理员”的“普通话”)

你有了这个大仓库，怎么跟它打交道？你总不能走过去拍拍服务器说：“嘿，哥们，帮我把张三的订单拿一下。”

你需要一种通用的语言来和数据库“沟通”。这就是 SQL (Structured Query Language，结构化查询语言)。

SQL 就是你和所有“关系型”数据库（不只是 PG）沟通的“普通话”。

你想：

“存货”（插入数据）：INSERT ...
“取货”（查询数据）：SELECT ...
“换标签”（更新数据）：UPDATE ...
“扔货”（删除数据）：DELETE ...

学会了 SQL，你就学会了和数据库打交道的核心技能。

1.4 为什么选择 PostgreSQL？(为什么这个“仓库”特别牛)

市面上的关系型数据库很多，比如：

MySQL：最流行的开源数据库，中小型公司最爱。
Oracle：最强大的商业数据库，巨贵，大型企业（银行、金融）在用。
SQL Server：微软家的，和 Windows 生态绑得深。

那 PostgreSQL (简称 PG) 凭什么被称为“世界上最先进的开源关系型数据库”？

接地气的说：

血统纯正，功能齐全：PG 是学院派出身（加州大学伯克利分校），严格遵守 SQL 标准，不像 MySQL 有些“野路子”。别人有的功能它基本都有，别人没有的它也经常有。
超级稳定，值得信赖：PG 在数据一致性和稳定性上口碑极好。它就像一个极其靠谱的老管家，你交给它的东西，它绝不会给你弄丢或弄错。
扩展性逆天：PG 就像“乐高”，可塑性极强。
- 想存地理位置信息？装个 PostGIS 插件，它就成了最牛的地理数据库。
- 想存时间序列数据？装个 TimescaleDB 插件，它就成了时序数据库。
- 想存“非关系型”的 JSON 数据？它内置的 JSONB 类型比很多专门的 NoSQL 数据库还快。
完全开源，还很“浪”：它不属于任何一个商业公司（不像 MySQL 被 Oracle 收了）。它由全球社区共同维护，技术迭代非常活跃，既稳重又新潮。

一句话总结：选择 PG，就像是选择了一个既有 Oracle 的强大稳定（还免费），又有 NoSQL 数据库的灵活性（JSONB）的超级仓库。

第二章：安装和启动 (准备篇)

2.1 在你的电脑上“建仓库”

安装 PG 是第一步。我们不在这里赘述每一步的点击，只提供方向。

官方下载：最推荐的方式是去 PostgreSQL 官方网站，它会推荐 EDB 公司的图形化安装包。
Windows：下载安装包，一路“下一步”即可。中途会让你设置一个超级管理员 postgres 的密码，这个密码一定要记住！
macOS：
- 推荐使用 Homebrew (macOS 的包管理器)。打开“终端”，输入：
```
brew install postgresql
```
- 安装完后，按提示启动服务：
```
brew services start postgresql
```

Linux (Ubuntu/Debian)：

sudo apt update
sudo apt install postgresql postgresql-contrib

2.2 你的第一个工具：`psql` (“对讲机”)

安装完 PG 后，它会自带一个最重要的工具：psql。

psql 是一个命令行客户端。你可以把它想象成和“仓库管理员”沟通的“对讲机”。你在这个黑乎乎的窗口里输入 SQL 命令，数据库就会返回结果。

`` (插图说明：一个黑色的终端窗口，显示着 postgres=# 这样的提示符。)

2.3 连接到你的数据库 (“呼叫管理员”)

安装完成后，PG 会自动创建一个默认的超级用户（通常叫 postgres）和一个同名数据库 postgres。

打开你的“终端”或“命令行提示符”：

在 macOS 或 Linux 上： PG 默认使用“ident”认证，意思是它认为你的操作系统用户名就是数据库用户名。你可以先切换到 postgres 这个系统用户，再登录：

# 切换到 postgres 系统用户
sudo -i -u postgres

# 运行 psql 对讲机
psql

你会看到提示符变成了 postgres=#，恭喜你，连接成功！

在 Windows 上：你可以在开始菜单找到一个叫 SQL Shell (psql) 的程序。打开它，会提示你输入：

Server (服务器): localhost (按回车)
Database (数据库): postgres (按回车)
Port (端口): 5432 (按回车)
Username (用户名): postgres (按回车)
Password (密码): (输入你安装时设置的那个密码，输入时看不见，输完按回车)

看到 postgres=#，同样代表连接成功！

psql 里的常用“快捷键” (元命令)： 在 psql 里，以 \ (反斜杠) 开头的命令不是 SQL，是 psql 自己才懂的“快捷键”。

\l：(list) 列出所有“仓库” (Databases)。
\c 数据库名：(connect) 切换到另一个“仓库”。
\dt：(display tables) 列出当前“仓库”里所有的“货架” (Tables)。
\d 表名：(describe) 查看某个“货架”的详细结构（有哪些“标签”）。
\q：(quit) 挂断“对讲机”，退出。

第三章：SQL 核心大法 (基础篇)

我们来学习怎么用“普通话” (SQL) 来操作“仓库”。

约定：
SQL 命令不区分大小写 (但我们习惯将 SQL 关键字大写，比如 SELECT，而表名、列名小写，比如 users，这样更清晰)。
每条 SQL 命令必须以分号 (;) 结尾。

3.1 数据库的基本结构 (仓库、货架、货物)

我们先创建一个新的“仓库”来学习，而不是用默认的 postgres 仓库。

1. 创建一个“仓库” (Database) 假设我们要开个“小卖部”，我们建一个叫 xiaomaibu 的仓库。

CREATE DATABASE xiaomaibu;

psql 快捷键 \l 查看，是不是多了一个 xiaomaibu？

2. 切换到新“仓库”

\c xiaomaibu

提示符会变成 xiaomaibu=#。

3. 在“仓库”里建“货架” (Table) 一个“小卖部”需要什么？我们先来个“商品货架” (表名叫 products)。这个货架需要有以下“标签”（列）：

商品ID (id)：数字，用来唯一标识商品。
商品名称 (name)：文字。
价格 (price)：数字，可以有小数。
库存 (quantity)：数字，整数。

CREATE TABLE products (
    id INT,
    name VARCHAR(100),
    price NUMERIC(10, 2),
    quantity INT
);

CREATE TABLE：SQL 命令，意思是“创建表”。
products：我们给表起的名字。
(...)：括号里定义所有的“标签”（列）。
id INT：列名叫 id，类型是 INT (整数)。
name VARCHAR(100)：列名叫 name，类型是 VARCHAR(100) (可变长度的字符串，最多100个字符)。
price NUMERIC(10, 2)：列名叫 price，类型是 NUMERIC(10, 2) (高精度数字，总共最多10位数，其中2位是小数。存钱用这个最准)。
quantity INT：列名叫 quantity，类型是 INT。

现在，用 \dt 看看，是不是有了 products 这个“货架”？用 \d products 看看，它的结构是不是和我们定义的一样？

3.2 万能四宝：CURD (增删改查)

CURD 是四个操作的首字母缩写：Create (增), Update (改), Read (查), Delete (删)。（注意：这里的 Create 指的是创建数据/行，而不是创建表）。

我们按最常用的顺序 R-C-U-D 来学，也就是：查、增、改、删。

3.5 `SELECT` 和 `WHERE`：找到你想要的“货” (查询与过滤)

SELECT 是 SQL 中最最最重要、最最最常用的命令。它的作用就是“查”。

1. 查所有“货” 我想看看“商品货架”上现在有啥。

SELECT * FROM products;

SELECT *：* (星号) 代表“所有列”。
FROM products：从 products 这张表里查。

你会得到一个空表，因为我们还没“放货”。

3.4 `INSERT`：往“货架”上“放货” (数据)

INSERT 的作用就是“增”。我们来进点货。

1. 放第一件货 我们进 100 瓶“可乐”，ID=1，卖 3.5 元。

INSERT INTO products (id, name, price, quantity)
VALUES (1, '可乐', 3.50, 100);

INSERT INTO products (...)：告诉 PG 我们要往 products 表的这几个指定列里放数据。
VALUES (...)：按前面列的顺序，填入具体的值。注意，字符串要用单引号 ' ' 括起来。

2. 再放几件货

INSERT INTO products (id, name, price, quantity)
VALUES (2, '雪碧', 3.50, 80);

INSERT INTO products (id, name, price, quantity)
VALUES (3, '方便面', 5.00, 50);

INSERT INTO products (id, name, price, quantity)
VALUES (4, '火腿肠', 1.50, 200);

3. 再次查询 现在，我们再用刚才的查询命令看看：

SELECT * FROM products;

输出结果：

 id |  name   | price | quantity
----+---------+-------+----------
  1 | 可乐    |  3.50 |      100
  2 | 雪碧    |  3.50 |       80
  3 | 方便面  |  5.00 |       50
  4 | 火腿肠  |  1.50 |      200
(4 rows)

太好了！货都上架了。

SELECT 的高级用法

1. 只看“部分标签” 我不想看所有信息，我只想看“商品名称”和“价格”：

SELECT name, price FROM products;

输出：

  name   | price
---------+-------
 可乐    |  3.50
 雪碧    |  3.50
 方便面  |  5.00
 火腿肠  |  1.50
(4 rows)

2. WHERE：按条件过滤 SELECT 是“查”，WHERE 就是“怎么查”。

场景1：我只想找“可乐”的信息。
```
SELECT * FROM products WHERE name = '可乐';
```
- WHERE name = '可乐'：条件是 name (列) 等于 '可乐' (值)。
场景2：我想找价格大于 3 元的商品。
```
SELECT * FROM products WHERE price > 3;
```
- 常见的“比较符”：= (等于), > (大于), < (小于), >= (大于等于), <= (小于等于), <> 或 != (不等于)。
场景3：我想找价格小于 2 元，并且 (AND) 库存大于 100 的商品。
```
SELECT * FROM products WHERE price < 2 AND quantity > 100;
```
- (只有“火腿肠”满足)
场景4：我想找价格大于 4 元，或者 (OR) 库存小于 100 的商品。
```
SELECT * FROM products WHERE price > 4 OR quantity < 100;
```
- (“雪碧”和“方便面”都满足)
场景5：我想找名字里带“乐”字的商品 (模糊查询)。
```
SELECT * FROM products WHERE name LIKE '%乐%';
```
- LIKE：用于模糊匹配。
- %：称为“通配符”，代表“任意多个任意字符”。'%乐%' 的意思就是：前面随便是什么，中间有个“乐”，后面随便是什么。

3.6 `UPDATE`：给“货物”换个“标签” (更新)

UPDATE 的作用就是“改”。

场景：“可乐”涨价了，要从 3.50 涨到 4.00。

UPDATE products
SET price = 4.00
WHERE name = '可乐';

UPDATE products：告诉 PG 我们要更新 products 这张表。
SET price = 4.00：把 price 这一列的值设置为 4.00。
WHERE name = '可乐'：极其重要！ 告诉 PG 只更新 name 是“可乐”的那一行。

!! 严重警告 !! 如果你执行 UPDATE 时忘记写 WHERE 子句... 比如你执行了：UPDATE products SET price = 1.00; ... 这将导致所有商品的价格都变成 1.00！这是新手最容易犯的灾难性错误之一。

“安全提示”：在执行 UPDATE 或 DELETE 之前，可以先用 SELECT 语句和相同的 WHERE 条件试一下，看看选中的是不是你想要操作的那些行。例如，先执行 SELECT * FROM products WHERE name = '可乐';，确认只有“可乐”被选中，然后再执行 UPDATE。

3.7 `DELETE`：把“货”下架 (删除)

DELETE 的作用就是“删”。

场景：“雪碧”卖得不好，我们决定不卖了，要下架。

DELETE FROM products
WHERE name = '雪碧';

DELETE FROM products：从 products 表删除。
WHERE name = '雪碧'：同样极其重要！ 只删除 name 是“雪碧”的那一行。

!! 同样严重的警告 !! 如果你执行 DELETE FROM products; 而不带 WHERE... 恭喜你，你的“商品货架”被清空了。

第四章：SQL 进阶神技 (强化篇)

你已经掌握了对“一个货架”的增删改查。但“关系型”数据库的真正威力在于处理“多个货架之间的关系”。

我们再来建一个“货架”：“订单表” (orders)。一份订单应该包含：订单ID、订单时间、顾客姓名、买了哪个商品、买了多少个。

为了“关联”到商品表，我们不在订单表里存商品名字（比如“可乐”），而是存商品的“身份证”—— id。在 orders 表里，我们管这个“商品ID”叫 product_id。

CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(100),
    order_time TIMESTAMP,
    product_id INT,
    amount INT
);

TIMESTAMP：一种新的数据类型，用于存储日期和时间。

我们来插几条订单数据：

-- 张三在 2025-01-01 10:00 买了个 '可乐' (id=1), 买了 2 瓶
INSERT INTO orders (order_id, customer_name, order_time, product_id, amount)
VALUES (101, '张三', '2025-01-01 10:00:00', 1, 2);

-- 李四在 2025-01-01 11:30 买了个 '方便面' (id=3), 买了 10 包
INSERT INTO orders (order_id, customer_name, order_time, product_id, amount)
VALUES (102, '李四', '2025-01-01 11:30:00', 3, 10);

-- 张三又在 2025-01-02 14:00 买了个 '火腿肠' (id=4), 买了 5 根
INSERT INTO orders (order_id, customer_name, order_time, product_id, amount)
VALUES (103, '张三', '2025-01-02 14:00:00', 4, 5);

现在我们有两个表： products (商品表) orders (订单表)

4.1 `JOIN`：跨“货架”找东西 (连接)

需求：我想看看到目前为止，所有订单里，卖出的具体是啥商品？我只查 orders 表，只能看到 product_id (1, 3, 4)，我看不到商品名字。我只查 products 表，只能看到商品列表，看不到谁买了它。

这时，我们就需要 JOIN (连接)。

[Diagram of JOIN types (Venn diagrams)] (插图说明：两个重叠的圆圈。左圈代表 A 表，右圈代表 B 表。)

(插图-内连接)：两个圆圈重叠的部分。
(插图-左连接)：整个左圈，加上与右圈重叠的部分。

1. INNER JOIN (内连接) INNER JOIN 只保留两张表中都能匹配上的数据。

SELECT
    orders.customer_name,
    orders.amount,
    products.name,
    products.price
FROM orders
INNER JOIN products ON orders.product_id = products.id;

FROM orders INNER JOIN products：我们要连接 orders 和 products 这两张表。
ON orders.product_id = products.id：连接的“桥梁”！
- 这个 ON 条件告诉 PG：orders 表里的 product_id 列，对应的是 products 表里的 id 列。
SELECT orders.customer_name, ...：当两张表里有重名列时（虽然这里没有），最好用 表名.列名 的方式来指明你要的是哪张表的列。

输出结果：

 customer_name | amount |  name   | price
---------------+--------+---------+-------
 张三          |      2 | 可乐    |  4.00
 李四          |     10 | 方便面  |  5.00
 张三          |      5 | 火腿肠  |  1.50
(3 rows)

看！我们成功地把两个“货架”的信息拼在了一起！

2. LEFT JOIN (左连接) INNER JOIN 只显示“有订单的商品”。如果我想看所有商品，以及“它们分别被谁买了”（如果没被买，就显示空），怎么办？

LEFT JOIN 会以左边的表 (FROM 后面的第一张表) 为准，显示它的所有数据，再去右边的表里找匹配项。

-- 我们想看所有商品 (products 是左表) 的售卖情况
SELECT
    products.name,
    products.quantity AS "库存",
    orders.customer_name,
    orders.amount AS "售出数量"
FROM products
LEFT JOIN orders ON products.id = orders.product_id;

AS "库存"：AS 关键字可以给列起一个“别名”，让表头更易读。如果别名是中文，用双引号 " 括起来。

输出结果：

  name   | 库存 | customer_name | 售出数量
---------+------+---------------+----------
 可乐    |  100 | 张三          |        2
 方便面  |   50 | 李四          |       10
 火腿肠  |  200 | 张三          |        5
 (null)  | (null)| (null)        |   (null)
(4 rows)

等等，我的“雪碧”呢？ 啊，记起来了，我们在 3.7 节把它删掉了。我们先把“雪碧”加回来，让它成为一个“没被卖出过”的商品：

INSERT INTO products (id, name, price, quantity)
VALUES (2, '雪碧', 3.50, 80);

现在重新执行上面的 LEFT JOIN 查询：

  name   | 库存 | customer_name | 售出数量
---------+------+---------------+----------
 可乐    |  100 | 张三          |        2
 方便面  |   50 | 李四          |       10
 火腿肠  |  200 | 张三          |        5
 雪碧    |   80 | (null)        |   (null)
(4 rows)

看到了吗？products (左表) 里的“雪碧”被显示出来了，即使它在 orders (右表) 里没有任何匹配项。这些不匹配的地方，PG 会用 (null) 来填充。

NULL 是一个特殊值，代表“缺失、未知或不存在”。

4.2 聚合函数：算算总账

聚合函数 (Aggregate Functions) 是用来“算总账”的，它们把多行数据“聚合”成一个结果。

COUNT(*)：数一数总共有多少行。
SUM(列名)：把某一列的总和算出来。
AVG(列名)：算平均值。
MAX(列名)：找最大值。
MIN(列名)：找最小值。

场景1：我们总共卖出了多少笔订单？

SELECT COUNT(*) FROM orders;

(输出: 3)

场景2：我们总共卖出了多少“个”商品？(不是多少笔，是总件数)

SELECT SUM(amount) FROM orders;

(输出: 17，即 2+10+5)

场景3：“火腿肠”(id=4) 的最大一笔订单买了多少根？

SELECT MAX(amount) FROM orders WHERE product_id = 4;

(输出: 5)

场景4：我们商品的平均价格是多少？

SELECT AVG(price) FROM products;

(输出: 3.625...，即 (4+5+1.5+3.5)/4 )

4.3 `GROUP BY`：给“货物”分分类 (分组)

COUNT(*) 算的是“总共”的。如果我想知道**“每个顾客”**分别下了多少单，怎么办？

这就是 GROUP BY (分组) 的用武之地。

SELECT
    customer_name,
    COUNT(*) AS "订单数"
FROM orders
GROUP BY customer_name;

GROUP BY customer_name：告诉 PG，以 customer_name 这一列为标准进行“分类”。
SELECT customer_name, COUNT(*)：在 SELECT 里，我们既要显示“分类标准” (customer_name)，也要显示这个分类的“聚合结果” (COUNT(*))。

PG 的执行逻辑是：

找到 orders 表。
看到 GROUP BY customer_name，它开始“分堆”：
- “张三” 堆：(订单 101, 订单 103)
- “李四” 堆：(订单 102)
然后对每一堆分别执行 COUNT(*)：
- “张三” 堆：COUNT=2
- “李四” 堆：COUNT=1
返回结果。

输出：

 customer_name | 订单数
---------------+--------
 李四          |      1
 张三          |      2
(2 rows)

HAVING 子句：如果我们想在“分组后”再进行过滤，比如，我只想看“订单数大于1”的顾客。不能用 WHERE，WHERE 是在“分组前”过滤原始数据的。要用 HAVING：

SELECT
    customer_name,
    COUNT(*) AS "订单数"
FROM orders
GROUP BY customer_name
HAVING COUNT(*) > 1;

(输出: 只有“张三”那一行)

4.4 `ORDER BY` 和 `LIMIT`：让“货物”排好队

1. ORDER BY (排序) 默认情况下，SELECT 出来的结果是“无序”的（取决于 PG 怎么方便怎么拿）。如果我们想让结果按一定顺序排列，就要用 ORDER BY。

场景1：我想看商品列表，按“价格”从低到高排。
```
SELECT * FROM products ORDER BY price;
```
- (默认就是 ASC - Ascending 升序)
场景2：我想看商品列表，按“库存”从高到低排。
```
SELECT * FROM products ORDER BY quantity DESC;
```
- DESC - Descending 降序
场景3：我想看订单，先按“顾客姓名”排 (A-Z)，如果姓名相同，再按“订单时间”从晚到早 (DESC) 排。
```
SELECT * FROM orders ORDER BY customer_name, order_time DESC;
```

2. LIMIT (限制数量) 当数据有几百万条时，你 SELECT * 会导致“刷屏”和性能灾难。 LIMIT 用来限制只返回“前 N 条”数据。

场景：我只想看我们店里“最贵”的 2 个商品是什么？
```
SELECT * FROM products
ORDER BY price DESC
LIMIT 2;
```
- (输出：“方便面” 和 “可乐”)

LIMIT + OFFSET (分页) LIMIT 和 OFFSET 配合使用，是实现“分页”功能（比如看第2页评论）的法宝。

LIMIT 10 OFFSET 0：取 10 条，跳过 0 条 (第 1 页)
LIMIT 10 OFFSET 10：取 10 条，跳过 10 条 (第 2 页)
LIMIT 10 OFFSET 20：取 10 条，跳过 20 条 (第 3 页)

4.5 子查询：套娃式查找 (复杂查询)

子查询 (Subquery) 就是把一个 SELECT 语句嵌套在另一个 SQL 语句（比如 WHERE 子句）里。

场景：我想知道，哪些订单买的商品，其“价格”高于所有商品的“平均价”？

分析：

我需要先知道“平均价”是多少。(<code>SELECT AVG(price) FROM products</code>)
然后我需要找出 products 表里，价格大于这个“平均价”的商品。
最后我去 orders 表里找，哪些订单买了这些商品。

用子查询可以一步到位：

SELECT * FROM orders
WHERE product_id IN (
    -- 这是子查询，它会先运行，返回一个 "id 列表" [1, 3]
    SELECT id FROM products WHERE price > (
        -- 这又是一个子查询，它会先运行，返回一个值 3.625
        SELECT AVG(price) FROM products
    )
);

IN (...)：WHERE 的一个操作符，意思是“在...列表里”。

这个查询虽然有点绕，但它展示了 SQL 组合的强大威力。

第五章：数据“规矩” (设计篇)

我们在第三章创建的表，其实很“不专业”。它允许你插入各种“脏数据”：

id 重复了怎么办？
price 插入一个负数怎么办？
orders 表里的 product_id 填了一个 999 (商品表里根本没有 999)，怎么办？

“规矩”，在数据库里叫“约束 (Constraints)”，是用来保证数据完整性和准确性的。

我们来重新设计一下我们的“货架” (表)。

5.1 什么是“数据类型”？(规定货物“长啥样”)

选对数据类型是第一道“规矩”。

INT：存整数 (id, quantity)。
NUMERIC(10, 2)：存精确小数 (price)。
VARCHAR(100)：存短文本 (name)。
TEXT：存长文本 (比如商品描述、博客文章)。
BOOLEAN：存 true 或 false (比如 is_on_sale 是否促销)。
TIMESTAMP：存日期和时间 (order_time)。
DATE：只存日期 (比如生日)。

PG 有上百种数据类型，甚至包括“IP地址”、“几何图形”、“UUID”等，这是它强大的表现。

5.2 `PRIMARY KEY`：每件“货”的“身份证” (主键)

主键 (Primary Key, PK) 是一列（或多列），它必须满足两个条件：

UNIQUE (唯一)：不能重复。
NOT NULL (非空)：不能为空。

主键就是这一行数据的“唯一身份证”。

我们之前的 products 表，id 列就应该是主键。但我们没指定。我们来创建一个更“专业”的 products_v2 表：

CREATE TABLE products_v2 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
    quantity INT DEFAULT 0
);

这个定义里加了很多“规矩”：

id SERIAL PRIMARY KEY：
- PRIMARY KEY：指定 id 是主键。
- SERIAL：这是 PG 的一个“黑魔法”。它代表“自动增长的整数”。
- 这意味着，你 INSERT 数据时根本不用管 id 列，PG 会自动帮你填：第一条是 1，第二条是 2，以此类推。这保证了 id 永远不会重复。
name VARCHAR(100) NOT NULL：NOT NULL 约束，代表“不能为空”。你 INSERT 时必须提供 name。
price NUMERIC(10, 2) NOT NULL CHECK (price > 0)：
- NOT NULL：价格不能为空。
- CHECK (price > 0)：CHECK 约束，提供一个“自定义规矩”。这里我们规定 price 必须大于 0。（防止有人捣乱填个负数）
quantity INT DEFAULT 0：DEFAULT 约束，如果我们 INSERT 时不提供 quantity，PG 会自动帮我们填上默认值 0。

5.3 `FOREIGN KEY`：建立“货物”间的“关系网” (外键)

外键 (Foreign Key, FK) 是用来**强制建立表与表之间“关系”**的。

我们之前的 orders 表，你可以随便插入一个 product_id = 999，尽管 products 表里没有 999。这叫“数据孤岛”或“脏数据”。

外键就是用来防止这种情况的。它规定：orders 表里的 product_id，必须是 products 表里真实存在的 id。

我们来创建“专业”的 orders_v2 表：

CREATE TABLE orders_v2 (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    product_id INT,
    amount INT CHECK (amount > 0),

    -- 这就是外键约束
    CONSTRAINT fk_product
        FOREIGN KEY (product_id)
        REFERENCES products_v2 (id)
        ON DELETE SET NULL
);

order_id SERIAL PRIMARY KEY：订单表也需要自己的“身份证”。
order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP：CURRENT_TIMESTAMP 是 PG 的一个内置值，代表“当前时间”。DEFAULT 它，意味着 INSERT 时不填时间，PG 会自动填入当前服务器时间。
CONSTRAINT fk_product ...：定义一个约束，名字叫 fk_product。
FOREIGN KEY (product_id)：指定本表 (orders_v2) 的 product_id 列是外键。
REFERENCES products_v2 (id)：它“引用” products_v2 表的 id 列。

加了这个“规矩”后，会发生什么？

INSERT 限制：如果你试图 INSERT 一条 product_id = 999 (而 products_v2 里没有 999) 的订单，PG 会直接报错并拒绝插入！
DELETE 限制：如果你试图 DELETE products_v2 表里某个商品（比如 id=1 的“可乐”），而 orders_v2 表里已经有了引用它的订单...
- ON DELETE SET NULL：这是我们指定的“连锁反应”。意思是：如果“可乐”被删了，那么 orders_v2 表里所有买了“可乐”的订单，它们的 product_id 会自动被设置成 NULL。(表示这个商品“信息丢失了”)
- 其他选项还有 ON DELETE RESTRICT (默认，直接报错不让删) 或 ON DELETE CASCADE (级联删除，如果“可乐”被删，所有买“可乐”的订单也会被一起删除！慎用！)。

5.4 `UNIQUE` (“不能重复”)

PRIMARY KEY 强制了“唯一 + 非空”。如果我只想要“唯一”，但允许“空”呢？比如，我想给 products_v2 加一个“商品条码” (barcode) 列，这个条码不能重复，但有些商品可能暂时没有条码（允许为 NULL）。

ALTER TABLE products_v2
ADD COLUMN barcode VARCHAR(50) UNIQUE;

ALTER TABLE：修改已存在的“货架” (表) 结构。
ADD COLUMN：添加一个新“标签” (列)。
UNIQUE：这就是“唯一约束”。

第六章：PG 内部探秘 (架构篇)

（本章内容偏“技术性”，但理解它有助于你成为“高手”。）

6.1 客户端/服务器模型 (C/S 架构)

你安装的 PostgreSQL，其实是一个“服务端” (Server) 软件。它是一个在后台默默运行的“服务进程”，真正管理着数据文件。

你运行的 psql，或者你未来会用的图形化工具 (DBeaver, Navicat)，或者你写的 Python/Java/Go 代码，都是“客户端” (Client)。

[Diagram of Client-Server Architecture] (插图说明：左边是多个“客户端”(psql, App, Web)，通过“网络”连接到右边的一个“PostgreSQL 服务器”。)

客户端把 SQL 语句（比如 SELECT * ...）打包，通过网络（即使在同一台电脑上，也是通过“本地网络”）发送给服务器。
服务器接收 SQL，解析它，执行它（去磁盘上扒拉数据）。
服务器把执行结果（比如那 4 行数据）打包，通过网络发回给客户端。
客户端（比如 psql）收到结果，把它美化一下，打印在你的屏幕上。

6.2 它的“大脑”和“工人” (进程结构)

你启动 PostgreSQL 服务时，操作系统会启动一个“总管进程”，叫 postmaster (现在也常叫 postgres)。

这个“总管”不自己干活。它就像一个“包工头”。

当一个客户端（比如 psql）发起连接请求时，“总管”会**“派生 (fork)”出一个新的“工人进程”** (也叫 postgres 或 backend process)，专门服务这个客户端。
你用 psql 发 SQL，这个 SQL 语句就是交给这个“工人”去执行的。
你断开 psql 连接，这个“工人”进程就结束了。
如果有 100 个客户端连接，服务器上就会有 1 个“总管”和 100 个“工人”，外加一些“辅助工人”（比如写日志的、做清理的）。

这种“一人一个坑” (One Process Per Connection) 的模型，是 PG 历史悠久的设计，非常稳定，但在超高并发（上万连接）时会比“线程池”模型更耗费内存。

6.3 它是怎么“存东西”的 (存储结构)

你 CREATE DATABASE 的所有数据，都存在你安装时指定的一个“数据目录” (Data Directory) 里。

PG 是把所有东西都当“文件”来存的。

一个“仓库” (Database) 对应一个子目录。
一个“货架” (Table) 在物理上对应一个（或多个）文件。
你创建的“目录” (Index) 也对应一个单独的文件。

6.4 MVCC：多人同时用也不打架的秘密

MVCC (Multi-Version Concurrency Control，多版本并发控制) 是 PG（以及 Oracle、MySQL InnoDB）的核心精髓。

这是个什么“黑科技”？

没有 MVCC 的世界 (比如锁)： 想象一下，你（张三）在 UPDATE “可乐”的价格（从 4.0 改成 4.5）。在你的操作完成（COMMIT）之前，数据库必须把“可乐”这行数据**“锁住” (Lock)。这时，另一个顾客（李四）想 SELECT “可乐”的价格。因为数据被“锁”了，李四必须等待**，直到你（张三）操作完成，锁被释放，李四才能读到 4.5。如果张三的操作很慢，李四就会“卡住”。这就是“读写阻塞”。

有了 MVCC 的世界 (多版本)： MVCC 的思想是：“写操作不阻塞读操作”。

初始状态：products 表里，“可乐”的价格是 4.0。我们称之为“版本 1”。
张三 UPDATE：张三 UPDATE 价格到 4.5。 PG 不会“覆盖”掉 4.0。它会在表里**创建一个“新版本”**的数据行，价格是 4.5，并标记这个“版本 2”是张三的事务创建的，但“尚未提交”。同时，它把“版本 1” (4.0) 标记为“已过期”（但暂不删除）。
李四 SELECT：在张三提交之前，李四来了，SELECT “可乐”的价格。 PG 查看数据行，发现“版本 2” (4.5) 是“未提交”的，李四看不见。 PG 会自动**“回溯”**，找到对李四来说“可见”的那个旧版本——“版本 1” (4.0)。 PG 把 4.0 返回给李四。李四立刻拿到了数据，没有被阻塞！
张三 COMMIT (提交)：张三的操作完成了。 PG 把“版本 2” (4.5) 标记为“已提交，对所有人可见”。同时把“版本 1” (4.0) 标记为“彻底废弃”。
王五 SELECT：这时王五来了，SELECT “可乐”的价格。 PG 发现“版本 2” (4.5) 是最新的、已提交的版本，于是把 4.5 返回给王五。

PG 通过为数据保留“多个版本”，巧妙地实现了“读写分离”，大大提高了并发性能。（代价是，PG 需要一个叫 VACUUM 的“垃圾回收”机制，定期去清理那些“彻底废弃”的旧版本数据行。）

第七章：仓库管理员的日常 (管理篇)

7.1 “发门禁卡”：用户和角色 (Users & Roles)

在 PG 里，“用户 (User)” 和 “角色 (Role)” 基本上是同义词。我们统一叫“角色”。一个“角色”就是一套“身份凭证”，可以用来登录数据库。

-- 创建一个新角色 (用户)，名叫 'zhangsan'，并且设置密码
CREATE ROLE zhangsan WITH LOGIN PASSWORD 'abc123';

CREATE ROLE zhangsan：创建角色。
WITH LOGIN：表示这个角色“可以登录”。（你也可以创建不能登录、只用于“权限打包”的角色组）
WITH PASSWORD 'abc123'：设置密码。

7.2 “授权”：`GRANT` 和 `REVOKE`

新创建的 zhangsan 登录后，是“身无分文”的。他连 xiaomaibu 仓库都进不去，更别提查 products 表了。超级管理员（比如 postgres）需要给他“授权”。

1. GRANT (授予权限)

-- 允许 zhangsan 连接 (CONNECT) 到 xiaomaibu 数据库
GRANT CONNECT ON DATABASE xiaomaibu TO zhangsan;

-- 允许 zhangsan 在 xiaomaibu 数据库的 'public' 模式 (默认) 下创建表
GRANT CREATE ON SCHEMA public TO zhangsan;

-- 允许 zhangsan 对 products 表进行 SELECT 查询
GRANT SELECT ON TABLE products TO zhangsan;

-- 允许 zhangsan 对 products 表进行 INSERT 和 UPDATE 操作
GRANT INSERT, UPDATE ON TABLE products TO zhangsan;

-- 允许 zhangsan 对 orders_v2 表的所有操作 (ALL PRIVILEGES)
GRANT ALL PRIVILEGES ON TABLE orders_v2 TO zhangsan;

2. REVOKE (撤销权限) 如果 zhangsan 离职了，我们要收回他的权限。

-- 撤销 zhangsan 对 products 表的 UPDATE 权限
REVOKE UPDATE ON TABLE products FROM zhangsan;

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON TABLE orders_v2 FROM zhangsan;

7.3 “数据备份”：`pg_dump` (有备无患)

pg_dump 不是 SQL 命令，它是 PG 自带的另一个“命令行工具”（像 psql 一样）。它用于“逻辑备份”。

它会把你整个数据库（或某张表）的数据，转换成一大堆 SQL 语句 (比如 CREATE TABLE ... 和 INSERT ...)，并存成一个文件。

在你的操作系统终端（不是 psql 里）运行：

# 备份整个 'xiaomaibu' 数据库，保存为 xiaomaibu_backup.sql 文件
# -U postgres 指定用 postgres 用户执行
# -d xiaomaibu 指定数据库
# -f xiaomaibu_backup.sql 指定输出文件
pg_dump -U postgres -d xiaomaibu -f xiaomaibu_backup.sql

打开这个 .sql 文件看看，里面都是你熟悉的 SQL 语句！

7.4 “数据恢复”：`pg_restore` (出事别慌)

如果你的 xiaomaibu 数据库被你（不小心）删了：

-- (在 psql 里)
DROP DATABASE xiaomaibu;

别慌！我们有备份。

恢复方法：

你先得有一个“空仓库”来装数据。

-- (在 psql 里)
CREATE DATABASE xiaomaibu_new_owner;

然后用 psql “执行” 那个 SQL 备份文件，把数据“倒灌”回去。

# (在操作系统终端)
# -U postgres 指定用户
# -d xiaomaibu_new_owner 指定要恢复到哪个数据库
# -f xiaomaibu_backup.sql 指定从哪个备份文件读取 SQL
psql -U postgres -d xiaomaibu_new_owner -f xiaomaibu_backup.sql

psql 会自动执行文件里所有的 CREATE TABLE 和 INSERT 语句，数据就回来了。

（注：pg_dump 还有一种 -Fc 的“自定义格式”，更高效，需要用 pg_restore 命令来恢复。对于小白，.sql 纯文本格式更直观。）

第八章：让查询飞起来 (性能篇)

当你的 products 表只有 4 条数据时，查询很快。当它有 400 万条数据时，SELECT * FROM products WHERE name = '可乐'; 可能会慢得让你抓狂。

为什么慢？因为默认情况下，PG 必须一行一行地去检查（这叫“全表扫描” (Full Table Scan)），看 name 是不是“可乐”。

8.1 什么是“索引”？(书的“目录”)

索引 (Index)，就是一本字典的“目录”（比如按拼音首字母排序的目录）。

如果没有目录，你查一个字（比如“数据库”），你必须从字典的第一页翻到最后一页。有了目录，你可以先在目录里快速定位到“S” (Shu)，找到“数据库”在第 500 页，然后直接翻到第 500 页。

索引就是用“空间换时间”。它会额外占用一些磁盘空间（用来存这个“目录”），但能极大地提升“查询” (SELECT) 速度。

8.2 `CREATE INDEX`：创建“目录”

我们经常按“商品名称” (name) 来查询，所以我们应该给 name 列创建一个索引。

CREATE INDEX idx_products_name ON products_v2 (name);

CREATE INDEX：创建索引。
idx_products_name：我们给索引起个名字（通常以 idx_ 开头）。
ON products_v2 (name)：在 products_v2 表的 name 列上创建。

PG 会在后台默默地把所有 name 拿出来，排好序，存成一个 B-Tree 结构（一种很高效的查找树）。

8.3 什么时候该用“目录”？

索引不是万能的，不能滥用！ 因为你 INSERT 或 UPDATE 数据时，PG 不仅要更新“表文件”，还要去更新“索引文件”（维护那个目录），这会拖慢“写”操作的速度。

黄金法则：

经常在 WHERE 子句中被用作“查询条件”的列，必须建索引。(比如 WHERE name = ...)
经常在 JOIN ... ON 子句中被用作“连接桥梁”的列，必须建索引。(比如 orders_v2 的 product_id 列)
主键 (PK) 和唯一约束 (UNIQUE)，PG 会自动为你创建索引。

不适合的场景：

**“写”远多于“读”**的表。
列的“区分度”很低。比如，你给“性别” (gender) 列建索引，只有“男”、“女”两种值。索引基本没用，还不如全表扫描。

8.4 `EXPLAIN`：看看 PG 是怎么“找东西”的 (查询计划)

你怎么知道 PG 用没用你建的索引？在你的 SELECT 语句前加上 EXPLAIN。

1. 没有索引时

EXPLAIN SELECT * FROM products_v2 WHERE name = '可乐';

输出可能会显示：

                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on products_v2  (cost=0.00..1.05 rows=1 width=44)
   Filter: (name = '可乐'::varchar)
(2 rows)

Seq Scan：就是 Sequential Scan (顺序扫描)，即“全表扫描”。

2. 创建索引后 (见 8.2)

-- 重新 EXPLAIN
EXPLAIN SELECT * FROM products_v2 WHERE name = '可乐';

输出（在数据量大时）会变成：

                                 QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using idx_products_name on products_v2  (cost=0.12..8.14 rows=1 width=44)
   Index Cond: (name = '可乐'::varchar)
(2 rows)

Index Scan：索引扫描！PG 告诉你它用了 idx_products_name 这个索引（目录）来快速定位数据，性能大大提升。

第九章：“事务” (安全篇)

9.1 什么是“事务”？(“要么全做完，要么全不做”)

事务 (Transaction) 是数据库管理里最核心的概念之一。它就是把“一组 SQL 操作”打包，作为一个“不可分割的整体”来执行。

经典比喻：银行转账 张三要给李四转 100 块钱。这个操作必须包含两步：

UPDATE zhangsan_account SET balance = balance - 100 WHERE ... (张三-100)
UPDATE lisi_account SET balance = balance + 100 WHERE ... (李四+100)

如果没有“事务”：万一第一步成功了（张三钱扣了），数据库突然崩溃（或断电）了，第二步没执行。结果：张三钱没了，李四没收到。钱，凭空消失了！

有了“事务”： PG 保证，这一“组”操作：

要么 (1) 和 (2) 全部成功。
要么 (如果中途出错了) 全部失败（回到操作前的状态，张三的钱会“退回”）。

这保证了数据的“一致性”。

9.2 ACID 是什么？(听着很“酸”，其实很重要)

ACID 是衡量一个数据库（事务）是否“靠谱”的四大标准：

A (Atomicity) - 原子性：就是我们刚说的“要么全做完，要么全不做”。一个事务就像一个“原子”，不可再分。
C (Consistency) - 一致性：事务必须使数据库从一个“一致”（数据不错乱）的状态，转变到另一个“一致”的状态。转账前后，银行的总钱数不能变。
I (Isolation) - 隔离性：多个事务并发（同时）执行时，它们之间互不干扰。张三给李四转账时，不能被王五给赵六转账的操作“插队”或“看走眼”。（这和 MVCC 息息相关）
D (Durability) - 持久性：一旦事务被“提交” (Commit) 了，那么它对数据的更改就是永久性的。即使数据库马上崩溃重启，数据也必须在。

PostgreSQL 在 ACID 方面是“模范生”。

9.3 `BEGIN`, `COMMIT`, `ROLLBACK` (开始、提交、反悔)

在 psql 里，默认情况下，你的每一条 SQL 语句都是一个“自动事务”。 INSERT ...; (执行完就自动提交了) DELETE ...; (执行完就自动提交了)

如果你想手动控制“转账”这种多步操作，你需要：

1. BEGIN (或 START TRANSACTION)：

喊一声“事务开始！”，PG 会关闭“自动提交”。

BEGIN;

2. 执行你的 SQL 组：

UPDATE products_v2 SET quantity = quantity - 1 WHERE name = '可乐';
UPDATE orders_v2 SET amount = amount + 1 WHERE order_id = 101;

3. 抉择时刻：

COMMIT (提交)： 你检查无误，喊一声“确认，就这样了！”。PG 会把这个事务里的所有更改（-1 和 +1）永久写入磁盘。
```
COMMIT;
```
ROLLBACK (回滚)： 你突然发现，啊，不该是 orders_v2，操作错了！你喊一声“撤销，全都不算了！”。PG 会撤销这个事务里的所有更改（可乐的库存会加回去，订单的 amount 会减回去），数据库回到 BEGIN 之前的状态。
```
ROLLBACK;
```

第十章：PG 的“独门绝技” (高级篇)

（本章介绍 PG 为什么“先进”，小白可先跳过，中级再看）

10.1 `JSONB`：直接存“非结构化”数据

传统关系型数据库，要求你必须先 CREATE TABLE 定义好“规矩”（Schema）。但如果我想存的数据“规矩”不固定呢？比如，products 表，“可乐”有“含糖量”属性，“方便面”有“口味”属性。

PG 提供了 JSONB 类型（B 代表 Binary 二进制，是优化过的 JSON）。

-- 给 products_v2 增加一个 '属性' 列，类型是 JSONB
ALTER TABLE products_v2
ADD COLUMN attributes JSONB;

-- 更新 '可乐'，给它加属性
UPDATE products_v2
SET attributes = '{"sugar_content": "10g/100ml", "is_caffeine_free": false}'
WHERE name = '可乐';

-- 更新 '方便面'
UPDATE products_v2
SET attributes = '{"flavor": "红烧牛肉", "includes_fork": true}'
WHERE name = '方便面';

你可以在 JSONB 里塞任意结构的 JSON。

更牛的是，PG 还能“看懂” JSONB 里的内容，并能对它建索引！

-- 查找所有 'flavor' 是 '红烧牛肉' 的商品
SELECT * FROM products_v2 WHERE attributes->>'flavor' = '红烧牛肉';

->> 是 PG 操作 JSONB 的特殊符号，意思是“按文本取出键值”。

JSONB 让 PG 兼具了“关系型”的严谨和“NoSQL” (如 MongoDB) 的灵活性。

10.2 窗口函数：超级“分析师” (Window Functions)

窗口函数 (Window Functions) 是 SQL 里的“大杀器”，用于复杂的数据分析（比如计算“排名”、“移动平均值”）。

它和 GROUP BY 的区别是：GROUP BY 会把多行“压成”一行（比如“张三” 2单）。而窗口函数是“开一扇窗”，它在看“当前行”的同时，还能**“瞄一眼”与它相关的“前后几行”（比如同一个顾客的其他订单），然后计算，但不压缩行数**。

场景：我想看 orders_v2 表，并为每个顾客的订单，按“购买数量 (amount)”进行排名。

SELECT
    customer_name,
    amount,
    -- 这就是窗口函数
    RANK() OVER (
        PARTITION BY customer_name
        ORDER BY amount DESC
    ) AS "排名"
FROM orders_v2;

RANK() OVER (...)：“排名”函数，在“窗口”上执行。
PARTITION BY customer_name：按“顾客”**“隔开”**窗口 (像 GROUP BY)。
ORDER BY amount DESC：在“窗口内”按“数量”倒序排。

(假设张三有3个订单，数量分别是 5, 2, 10) (假设李四有2个订单，数量分别是 10, 8) 输出会是：

 customer_name | amount | 排名
---------------+--------+------
 李四          |     10 |    1
 李四          |      8 |    2
 张三          |     10 |    1
 张三          |      5 |    2
 张三          |      2 |    3
(5 rows)

行数没变！但我们得到了“组内排名”。

10.3 CTE：让SQL更“易读” (Common Table Expressions)

当你的 SQL 查询（比如带了三层子查询）变得几百行长，像“天书”一样时，CTE (公共表表达式) 能救你。

CTE 允许你用 WITH 关键字，给一个“子查询”起一个“临时表名”，让你的主查询更清晰。

场景：找出购买了“最贵商品”的顾客是谁。

-- 使用 CTE
WITH expensive_products AS (
    -- 第1步：定义一个叫 'expensive_products' 的“临时表”
    -- (它只在本次查询中有效)
    -- 内容是：价格 > 4 的商品
    SELECT id, name
    FROM products_v2
    WHERE price > 4.00
)
-- 第2步：在主查询中，直接使用这个“临时表”
SELECT
    o.customer_name,
    ep.name AS "购买的贵重商品"
FROM orders_v2 AS o
JOIN expensive_products AS ep ON o.product_id = ep.id;

(输出: 李四, 方便面)

这比写一个复杂的子查询 JOIN (SELECT ...) 要清晰得多！

10.4 扩展：PG 的“插件”生态 (PostGIS 等)

PG 的终极武器是它的“扩展 (Extensions)”。你可以在 PG 数据库里 CREATE EXTENSION ...，像“装插件”一样，给它增加全新的功能。

PostGIS：最强“插件”。装上它，PG 就成了地球上最牛的“地理空间数据库”，可以存“点、线、面”，查询“我周围 5 公里内的所有餐馆”。
pg_trgm：提供“三角模型”算法，用于超快速的“模糊字符串匹配”。
TimescaleDB：(一个复杂的扩展) 把 PG 变成一个高性能的“时序数据库”，用于存储物联网、监控数据。

第十一章：从这里走向“精通” (后续)

恭喜你！你已经读完了这份 2 万多字的“接地气”指南。

你现在已经：

理解了什么是数据库、RDBMS 和 SQL。
掌握了 psql 的基本使用。
精通了 SQL 的核心：CURD (增删改查)。
掌握了 SQL 的精髓：JOIN (连接), GROUP BY (分组), ORDER BY (排序)。
理解了“规矩”的重要性：主键、外键、约束。
窥探了 PG 的内部原理：C/S 架构、MVCC。
学会了管理员的日常：用户、授权、备份 (pg_dump)。
掌握了性能优化的钥匙：索引 (Index) 和 EXPLAIN。
理解了数据安全的基石：事务 (ACID, COMMIT, ROLLBACK)。
见识了 PG 的独门绝技：JSONB, 窗口函数, CTE。

你绝对已经脱离“小白”，达到了“中级”水平。

11.1 官方文档：永远的“圣经”

你从这份指南“入门”了，但“精通”之路要靠“官方文档”。 PG 的官方文档是业界公认写得最好的，非常详细、严谨、准确。 https://www.postgresql.org/docs/current/

11.2 社区和资源

PG 中文社区：http://www.postgres.cn/
练手的地方 (SQL Fiddle)：https://www.db-fiddle.com/ (记得在左上角选 PostgreSQL)

11.3 总结

数据库技术是IT行业的基石。而 PostgreSQL 则是这块基石上最璀璨的明珠之一。它强大、开源、稳定、灵活，学习它，是你职业生涯中回报率最高的投资之一。

动手去练吧！ 去 CREATE TABLE，去 INSERT，去 SELECT！

PostgreSQL 从入门到精通：一份小白友好的“接地气”指南

目录

第一章：数据库是啥？PG 又是啥？ (入门篇)

1.1 什么是数据库？(你的超级“大仓库”)

1.2 什么是“关系型”数据库？(仓库的“分区管理”)

1.3 什么是 SQL？(仓库“管理员”的“普通话”)

1.4 为什么选择 PostgreSQL？(为什么这个“仓库”特别牛)

第二章：安装和启动 (准备篇)

2.1 在你的电脑上“建仓库”

2.2 你的第一个工具：psql (“对讲机”)

2.3 连接到你的数据库 (“呼叫管理员”)

第三章：SQL 核心大法 (基础篇)

3.1 数据库的基本结构 (仓库、货架、货物)

3.2 万能四宝：CURD (增删改查)

3.5 SELECT 和 WHERE：找到你想要的“货” (查询与过滤)

3.4 INSERT：往“货架”上“放货” (数据)

3.6 UPDATE：给“货物”换个“标签” (更新)

3.7 DELETE：把“货”下架 (删除)

第四章：SQL 进阶神技 (强化篇)

4.1 JOIN：跨“货架”找东西 (连接)

4.2 聚合函数：算算总账

4.3 GROUP BY：给“货物”分分类 (分组)

4.4 ORDER BY 和 LIMIT：让“货物”排好队

4.5 子查询：套娃式查找 (复杂查询)

第五章：数据“规矩” (设计篇)

5.1 什么是“数据类型”？(规定货物“长啥样”)

5.2 PRIMARY KEY：每件“货”的“身份证” (主键)

5.3 FOREIGN KEY：建立“货物”间的“关系网” (外键)

5.4 UNIQUE (“不能重复”)

第六章：PG 内部探秘 (架构篇)

6.1 客户端/服务器模型 (C/S 架构)

6.2 它的“大脑”和“工人” (进程结构)

6.3 它是怎么“存东西”的 (存储结构)

6.4 MVCC：多人同时用也不打架的秘密

第七章：仓库管理员的日常 (管理篇)

7.1 “发门禁卡”：用户和角色 (Users & Roles)

7.2 “授权”：GRANT 和 REVOKE

7.3 “数据备份”：pg_dump (有备无患)

7.4 “数据恢复”：pg_restore (出事别慌)

第八章：让查询飞起来 (性能篇)

8.1 什么是“索引”？(书的“目录”)

8.2 CREATE INDEX：创建“目录”

8.3 什么时候该用“目录”？

8.4 EXPLAIN：看看 PG 是怎么“找东西”的 (查询计划)

第九章：“事务” (安全篇)

9.1 什么是“事务”？(“要么全做完，要么全不做”)

9.2 ACID 是什么？(听着很“酸”，其实很重要)

9.3 BEGIN, COMMIT, ROLLBACK (开始、提交、反悔)

第十章：PG 的“独门绝技” (高级篇)

10.1 JSONB：直接存“非结构化”数据

10.2 窗口函数：超级“分析师” (Window Functions)

10.3 CTE：让SQL更“易读” (Common Table Expressions)

10.4 扩展：PG 的“插件”生态 (PostGIS 等)

第十一章：从这里走向“精通” (后续)

11.1 官方文档：永远的“圣经”

11.2 社区和资源

11.3 总结

2.2 你的第一个工具：`psql` (“对讲机”)

3.5 `SELECT` 和 `WHERE`：找到你想要的“货” (查询与过滤)

3.4 `INSERT`：往“货架”上“放货” (数据)

3.6 `UPDATE`：给“货物”换个“标签” (更新)

3.7 `DELETE`：把“货”下架 (删除)

4.1 `JOIN`：跨“货架”找东西 (连接)

4.3 `GROUP BY`：给“货物”分分类 (分组)

4.4 `ORDER BY` 和 `LIMIT`：让“货物”排好队

5.2 `PRIMARY KEY`：每件“货”的“身份证” (主键)

5.3 `FOREIGN KEY`：建立“货物”间的“关系网” (外键)

5.4 `UNIQUE` (“不能重复”)

7.2 “授权”：`GRANT` 和 `REVOKE`

7.3 “数据备份”：`pg_dump` (有备无患)

7.4 “数据恢复”：`pg_restore` (出事别慌)

8.2 `CREATE INDEX`：创建“目录”

8.4 `EXPLAIN`：看看 PG 是怎么“找东西”的 (查询计划)

9.3 `BEGIN`, `COMMIT`, `ROLLBACK` (开始、提交、反悔)

10.1 `JSONB`：直接存“非结构化”数据