PostgreSQL教程，新手如何快速上手？-杰瑞科技汇

PostgreSQL 完整教程

第一部分：初识 PostgreSQL
（图片来源网络，侵删）
- 什么是 PostgreSQL？
- 为什么选择 PostgreSQL？（优势）
- 谁适合学习本教程？
- 环境准备：安装与连接
第二部分：SQL 基础与核心概念
- 数据库、表、行、列
- 创建和管理数据库
- 创建和管理表
- CRUD 操作 (增删改查)
- 数据类型详解
- 约束
- 高级查询：JOIN, GROUP BY, HAVING
第三部分：进阶主题
- 索引
- 事务
- 视图
- 窗口函数
- 用户与权限管理
第四部分：PostgreSQL 特有功能
- JSON/JSONB 支持
- 数组类型
- 扩展
- 窗口函数（再次强调，因为它是 PG 的强项）
第五部分：实战与最佳实践
（图片来源网络，侵删）
- 使用 psql 命令行工具
- 使用图形化工具 (DBeaver, pgAdmin)
- 性能优化入门
- 备份与恢复
第六部分：学习资源
- 官方文档
- 书籍推荐
- 在线课程与社区

第一部分：初识 PostgreSQL

1 什么是 PostgreSQL？

PostgreSQL，通常简称为 PG 或 Postgres，是一款功能极其强大的、开源的对象-关系型数据库管理系统。

关系型：它使用基于关系模型的表来存储数据,表与表之间可以建立关联。
对象-关系型：它不仅支持标准的关系型数据库功能，还支持面向对象的特性，如自定义数据类型、继承、函数等,这使得它非常灵活和可扩展。
高级：它提供了许多现代数据库系统才有的高级功能，如复杂查询、外键、触发器、视图、事务完整性（ACID）等。

2 为什么选择 PostgreSQL？（优势）

开源与免费：完全免费使用,没有版权限制。
标准兼容：高度兼容 SQL 标准，遵循 ACID 原则,保证了数据的可靠性和一致性。
强大的扩展性：可以通过安装扩展来增加新功能，PostGIS（地理信息系统）、pgvector（向量相似性搜索）等。
数据类型丰富：除了标准类型，还支持 JSONB、数组、自定义类型等,能轻松应对复杂数据结构。
出色的性能：在处理复杂查询和大量数据时表现出色,特别是在读多写少的场景下。
活跃的社区：拥有庞大且活跃的开发者社区，文档完善,问题能快速得到解答。
可靠性：以其稳定性和数据完整性著称，被许多大型企业（如苹果、IMDb、Spotify）用于关键业务系统。

3 环境准备：安装与连接

安装

Windows: 访问 PostgreSQL 官方下载页面，下载安装包，运行安装程序，安装过程中会提示你设置一个超级用户 postgres 的密码。
macOS (使用 Homebrew): 在终端中运行 brew install postgresql，安装后，可以通过 brew services start postgresql 来启动服务。
Linux (Ubuntu/Debian): 在终端中运行 sudo apt update && sudo apt install postgresql postgresql-contrib。

连接

安装完成后,你需要一个客户端来连接和管理数据库。

命令行工具 psql (推荐初学者使用)
（图片来源网络，侵删）
- 安装完成后，psql 通常已经包含在内。
- 打开终端，输入 psql -U postgres -d postgres。
  - -U postgres：指定用户名为 postgres。
  - -d postgres：指定连接到 postgres 这个默认数据库。
- 系统会提示你输入密码,就是你在安装时设置的密码。
- 连接成功后，你会看到 postgres=# 的提示符，表示你已进入 PostgreSQL 的命令行界面。
图形化工具
- pgAdmin: 官方推荐的图形化管理工具，功能强大，界面友好，安装 PostgreSQL 时通常会附带安装。
- DBeaver: 一个通用的数据库管理工具，支持几乎所有数据库，包括 PostgreSQL,非常推荐使用。

第二部分：SQL 基础与核心概念

1 数据库、表、行、列

数据库: 存储数据的容器，一个 PostgreSQL 服务器可以管理多个数据库。
表: 数据库中存储数据的结构化集合,由行和列组成。
列: 表中的一个字段，定义了数据的类型（如整数、文本、日期）。
行: 表中的一条记录,是列值的集合。

2 创建和管理数据库

在 psql 中,你可以使用以下命令：

-- 创建一个名为 mydb 的新数据库
CREATE DATABASE mydb;
-- 连接到 mydb 数据库
-- 在 psql 中，使用 \c 命令
\c mydb
-- 删除数据库 (请谨慎使用！)
DROP DATABASE mydb;

3 创建和管理表

-- 在 mydb 数据库中创建一个 users 表
CREATE TABLE users (
    id SERIAL PRIMARY KEY, -- 自增整数主键
    username VARCHAR(50) NOT NULL UNIQUE, -- 非空且唯一的用户名
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP -- 创建时自动设置时间戳
);
-- 查看表结构
\d users
-- 删除表
DROP TABLE users;

关键字解释:

CREATE TABLE: 创建表。
SERIAL: 自增整数类型，自动创建一个序列来生成唯一 ID。
PRIMARY KEY: 主键,唯一标识表中的每一行。
VARCHAR(n): 可变长度的字符串，最多 n 个字符。
NOT NULL: 约束,该列不能为空。
UNIQUE: 约束,该列的值必须唯一。
TIMESTAMP WITH TIME ZONE: 带时区的时间戳。
DEFAULT: 设置默认值。

4 CRUD 操作 (增删改查)

C - 创建

-- 向 users 表中插入一条新记录
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

R - 读取

-- 查询 users 表中的所有数据
SELECT * FROM users;
-- 查询特定列
SELECT username, email FROM users;
-- 带条件的查询 (WHERE)
SELECT * FROM users WHERE username = 'john_doe';
-- 模糊查询 (LIKE)
SELECT * FROM users WHERE email LIKE '%@example.com';
-- 排序 (ORDER BY)
SELECT * FROM users ORDER BY created_at DESC; -- DESC 降序, ASC 升序
-- 限制结果数量 (LIMIT)
SELECT * FROM users LIMIT 10;

U - 更新

-- 更新 id 为 1 的用户的邮箱
UPDATE users SET email = 'john.doe@example.com' WHERE id = 1;

D - 删除

-- 删除 id 为 1 的用户
DELETE FROM users WHERE id = 1;

5 数据类型详解

PostgreSQL 提供了丰富的数据类型：

数值类型: INTEGER, BIGINT, SMALLINT, SERIAL, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION
字符类型: CHAR(n), VARCHAR(n), TEXT (文本,长度不限)
布尔类型: BOOLEAN (true/false)
日期/时间类型: DATE, TIME, TIMESTAMP, INTERVAL
网络地址类型: CIDR, INET, MACADDR
几何类型: POINT, LINE, POLYGON, CIRCLE
UUID 类型: UUID
JSON/JSONB: JSON (文本格式), JSONB (二进制格式,查询更快)

6 约束

约束是保证数据完整性的规则。

PRIMARY KEY: 主键,唯一且非空。
FOREIGN KEY: 外键，用于关联两个表,保证引用完整性。
UNIQUE: 唯一约束,列中的值必须唯一。
NOT NULL: 非空约束,列不能为空。
CHECK: 检查约束,确保列中的值满足特定条件。

外键示例:

-- 创建一个 orders 表，并关联到 users 表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL,
    user_id INTEGER NOT NULL,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 定义外键
    FOREIGN KEY (user_id) REFERENCES users(id)
);

7 高级查询：`JOIN`, `GROUP BY`, `HAVING`

`JOIN` (连接)

用于从多个表中组合数据。

-- 查询所有用户及其订单信息
SELECT
    u.username,
    u.email,
    o.order_number,
    o.amount
FROM
    users u
JOIN
    orders o ON u.id = o.user_id; -- 关联条件

`GROUP BY` 和 `HAVING`

用于聚合数据。

-- 计算每个用户的订单总金额
SELECT
    u.username,
    SUM(o.amount) AS total_spent
FROM
    users u
JOIN
    orders o ON u.id = o.user_id
GROUP BY
    u.id, u.username; -- 按 id 和 username 分组

-- 只查询总金额超过 100 的用户
SELECT
    u.username,
    SUM(o.amount) AS total_spent
FROM
    users u
JOIN
    orders o ON u.id = o.user_id
GROUP BY
    u.id, u.username
HAVING
    SUM(o.amount) > 100; -- HING 用于对分组结果进行筛选

第三部分：进阶主题

1 索引

索引是提高查询性能的关键，它就像一本书的目录,让数据库不必扫描整张表就能快速找到数据。

-- 为 users 表的 username 列创建索引
CREATE INDEX idx_users_username ON users(username);
-- 查看索引
\di users

何时使用索引:

经常用于 WHERE 子句的列。
经常用于 JOIN 和 ORDER BY 的列。

注意: 索引会占用存储空间，并且会降低 INSERT, UPDATE, DELETE 的速度，因为索引也需要更新,只为真正需要的列创建索引。

2 事务

事务是一组 SQL 操作，它们要么全部成功，要么全部失败,保证了操作的原子性。

BEGIN; -- 开始一个事务
-- 执行多个操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 如果所有操作都成功，则提交事务
COMMIT;
-- 如果中途出错，则回滚事务，撤销所有操作
-- ROLLBACK;

3 视图

视图是一个虚拟的表，其内容由查询定义，它不存储数据,而是存储一个查询语句。

-- 创建一个视图，显示用户及其订单总数
CREATE VIEW user_order_summary AS
SELECT
    u.id,
    u.username,
    COUNT(o.id) AS order_count
FROM
    users u
LEFT JOIN
    orders o ON u.id = o.user_id
GROUP BY
    u.id, u.username;
-- 像查询普通表一样查询视图
SELECT * FROM user_order_summary;

用途:

简化复杂查询。
隐藏底层表的结构,提供数据安全性。

4 窗口函数

窗口函数是 PostgreSQL 的一个杀手级特性,它可以在不合并行的情况下对数据进行聚合和排名。

-- 计算每个用户的订单金额，并显示该用户的订单总金额
SELECT
    username,
    order_number,
    amount,
    SUM(amount) OVER (PARTITION BY username) AS user_total_amount
FROM
    orders
JOIN
    users ON orders.user_id = users.id;

关键字:

OVER(): 定义窗口。
PARTITION BY username: 按用户名分组（分区）,计算每个分组的聚合值。
ORDER BY amount: 在窗口内排序。
ROWS/RANGE: 定义窗口的行范围。

常用窗口函数: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG()。

5 用户与权限管理

-- 创建一个新用户
CREATE USER myapp_user WITH PASSWORD 'a_strong_password';
-- 授予 mydb 数据库的所有权限给 myapp_user
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp_user;
-- 授予 users 表的 SELECT 和 INSERT 权限
GRANT SELECT, INSERT ON users TO myapp_user;
-- 撤销权限
REVOKE INSERT ON users FROM myapp_user;

第四部分：PostgreSQL 特有功能

1 JSON/JSONB 支持

PostgreSQL 对 JSON 的支持非常出色，JSONB 是二进制存储，性能更好,且支持索引。

-- 创建一个包含 JSONB 列的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);
-- 插入数据
INSERT INTO products (name, attributes) VALUES
('Laptop', '{"cpu": "Intel i7", "ram": 16, "storage": "512GB SSD"}'),
('Mouse', '{"dpi": 1600, "wireless": true}');
-- 查询 JSONB 数据
-- 使用 -> 获取 JSON 对象 (返回 JSON)
SELECT name, attributes -> 'cpu' FROM products;
-- 使用 ->> 获取文本值
SELECT name, attributes ->> 'cpu' FROM products WHERE attributes ->> 'wireless' = 'true';
-- 使用 @> 检查是否包含某个键值对
SELECT * FROM products WHERE attributes @> '{"wireless": true}';
-- 为 JSONB 列创建 GIN 索引以加速查询
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

2 数组类型

PostgreSQL 原生支持数组类型。

-- 创建一个包含数组列的表
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,VARCHAR(200),
    tags TEXT[]
);
-- 插入数据
INSERT INTO posts (title, tags) VALUES
('PostgreSQL Arrays', ['database', 'sql', 'tutorial']),
('Python Basics', ['python', 'programming']);
-- 查询数组
SELECT * FROM posts WHERE tags @> 'database'; -- 检查数组是否包含 'database'
SELECT * FROM posts WHERE 'python' = ANY(tags); -- 检查数组是否包含 'python'

3 扩展

PostgreSQL 的扩展生态是其强大之处，你可以通过 CREATE EXTENSION 来安装。

-- 启用 pg_trgm 扩展，用于文本相似性搜索和快速模糊匹配
CREATE EXTENSION pg_trgm;
-- 启用 uuid-ossp 扩展，用于生成 UUID
CREATE EXTENSION "uuid-ossp";

著名扩展:

PostGIS: 为 PostgreSQL 添加地理信息系统功能。
pgvector: 用于向量相似性搜索，是 AI 应用的基石。
TimescaleDB: 将 PostgreSQL 变成一个功能强大的时序数据库。

第五部分：实战与最佳实践

1 使用 `psql` 命令行工具

掌握一些常用 psql 命令能极大提高效率：

命令	描述
`\l`	列出所有数据库
`\c dbname`	连接到数据库 `dbname`
`\dt`	列出当前数据库的所有表
`\d tablename`	显示表 `tablename` 的结构（列、索引、约束等）
`\di`	列出所有索引
`\df`	列出所有函数
`\?`	查看 `psql` 的所有内部命令帮助
`\h SQL_COMMAND`	查看 SQL 命令的帮助，`\h SELECT`
`\q`	退出 `psql`

2 使用图形化工具

图形化工具（如 DBeaver 或 pgAdmin）可以让你通过点击和拖拽来管理数据库，非常适合进行数据浏览、复杂的查询编辑和可视化,它们对于初学者尤其友好。

3 性能优化入门

使用 EXPLAIN: 在查询前加上 EXPLAIN，可以查看 PostgreSQL 如何执行这个查询（是否使用了索引、扫描了哪些表等）。
```
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john_doe';
```
- EXPLAIN: 显示查询计划。
- ANALYZE: 实际执行查询并显示耗时。
为慢查询添加索引：EXPLAIN 显示查询使用了 Seq Scan（顺序扫描），并且这是一个频繁执行的查询,考虑为相关列添加索引。
*避免 `SELECT `**: 只查询你需要的列,减少数据传输量。
合理使用连接：避免在 WHERE 子句中对列进行函数操作,这会导致索引失效。

4 备份与恢复

备份 (`pg_dump`)

# 备份整个数据库到一个 SQL 文件
pg_dump -U postgres -d mydb > mydb_backup.sql
# 备份为自定义格式（更高效，恢复更快）
pg_dump -U postgres -d mydb -Fc > mydb_backup.dump

恢复 (`pg_restore`)

# 使用 psql 从 SQL 文件恢复
psql -U postgres -d mydb < mydb_backup.sql
# 使用 pg_restore 从自定义格式文件恢复
pg_restore -U postgres -d mydb mydb_backup.dump

第六部分：学习资源

1 官方文档

PostgreSQL 官方文档: 最权威、最全面的学习资料,涵盖了从入门到专家的所有内容。

2 书籍推荐

《PostgreSQL实战》: 国内优秀书籍，内容详实,案例丰富。
"PostgreSQL: Up and Running" by Regina O. Obe & Leo S. Hsu: 一本非常实用的入门和进阶书籍。
"The Definitive Guide to PostgreSQL" by Hans-Jürgen Schönig: 深入探讨了 PostgreSQL 的内部原理和高级特性。

3 在线课程与社区

freeCodeCamp: 有免费的 PostgreSQL 视频教程。
Udemy / Coursera: 有许多高质量的付费课程。
Stack Overflow: 解决编程问题的最佳社区，标签为 postgresql。
PostgreSQL 邮件列表: 官方的技术讨论社区。

这份教程为你提供了一个学习 PostgreSQL 的完整路线图，从最基础的安装和 SQL 语法，到高级的窗口函数和 JSON 支持，再到实际的运维技巧，希望能帮助你从零开始,逐步掌握这款强大的数据库系统。

学习建议:

动手实践: 不要只看不练，跟着教程，在你的电脑上安装、创建、操作数据库。
由浅入深: 先熟练掌握 CRUD 和基本查询，再学习索引、事务等高级概念。
善用工具: 熟练掌握 psql 和至少一个图形化工具。
阅读官方文档: 当你遇到问题时,官方文档是最好的答案来源。

祝你学习愉快！

PostgreSQL教程，新手如何快速上手？