PostgreSQL 学习手册

第一部分：基础入门

1. 什么是 PostgreSQL？

定义：PostgreSQL 是一个功能强大的、开源的对象-关系型数据库管理系统（ORDBMS）。它以其高可靠性、功能丰富、性能卓越和强大的扩展性而闻名。
特点：
- 开源：完全免费，拥有活跃的社区支持。
- 标准兼容：高度兼容 SQL 标准。
- 可扩展：支持自定义函数、存储过程、数据类型、操作符、聚合函数等。
- 并发性好：使用多版本并发控制（MVCC）来实现高并发，读写互不阻塞。
- 可靠性高：支持事务的 ACID（原子性、一致性、隔离性、持久性）特性，支持预写式日志（WAL）来保证数据完整性。

2. 安装与配置

下载：从官方站点 https://www.postgresql.org/download/ 选择对应操作系统（Windows, macOS, Linux）的版本。
安装：
- Linux (Ubuntu/Debian)：
```
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
```
- macOS：推荐使用 Homebrew：brew install postgresql
- Windows：运行图形化安装程序，按向导步骤进行。
初始设置：
- 安装后会自动创建一个名为 postgres 的超级用户和一个同名的数据库。
- 需要设置密码：sudo -u postgres psql，然后执行 \password 命令。
- 创建新用户和数据库：createuser --interactive，createdb mydb

3. 基本操作 (通过 `psql` 命令行工具)

连接数据库：psql -U username -d dbname -h host -p port
常用元命令 (在 psql 中执行)：
- \l：列出所有数据库
- \c dbname：切换到指定数据库
- \dt：列出当前数据库中的所有表
- \d table_name：描述表的结构
- \du：列出所有用户角色
- \?：查看所有元命令帮助
- \q：退出 psql

第二部分：核心概念与 SQL 操作

1. 数据库与模式 (Schema)

数据库 (Database)：最顶层的逻辑容器，数据彼此隔离。
模式 (Schema)：数据库内部的命名空间，用于组织表、视图等对象。默认有一个 public 模式。
- CREATE SCHEMA myschema;
- 访问：SELECT * FROM myschema.mytable;

2. 数据定义语言 (DDL)

创建表：

CREATE TABLE users (
    id SERIAL PRIMARY KEY, -- 自增主键
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) NOT NULL,
    age INT CHECK (age > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

修改表：

ALTER TABLE users ADD COLUMN phone VARCHAR(15);
ALTER TABLE users ALTER COLUMN email TYPE TEXT;
ALTER TABLE users DROP COLUMN age;

删除表：DROP TABLE users;

3. 数据操作语言 (DML)

插入数据 (INSERT)：

INSERT INTO users (username, email, age)
VALUES ('alice', 'alice@example.com', 30);

查询数据 (SELECT)：

SELECT * FROM users; -- 查询所有
SELECT username, email FROM users WHERE age > 25; -- 条件查询
SELECT * FROM users ORDER BY created_at DESC; -- 排序
SELECT COUNT(*), age FROM users GROUP BY age; -- 分组聚合

更新数据 (UPDATE)：

UPDATE users SET age = 31 WHERE username = 'alice';

删除数据 (DELETE)：

DELETE FROM users WHERE username = 'alice';

4. 数据查询进阶

多表连接 (JOIN)：

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN

SELECT o.order_id, u.username
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

子查询 (Subquery)：

SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

常用聚合函数：COUNT(), SUM(), AVG(), MAX(), MIN()
窗口函数 (Window Functions)：用于对一组相关的行进行计算，同时保留各行信息。
```
SELECT username, age,
       RANK() OVER (ORDER BY age DESC) as age_rank
FROM users;
```

第三部分：高级特性

1. 索引

作用：大幅提高查询速度，但会增加写操作的开销。

创建索引：

CREATE INDEX idx_users_email ON users(email); -- B-tree 索引
CREATE INDEX idx_users_username ON users USING gin (to_tsvector('english', username)); -- GIN 索引（用于全文搜索）

2. 事务 (Transactions)

特性：保证一系列操作要么全部成功，要么全部失败（ACID）。

BEGIN; -- 开始事务

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

COMMIT; -- 提交事务
-- 如果发生错误，可以执行 ROLLBACK; 来回滚

3. 视图 (Views)

作用：将复杂的查询保存为一个虚拟表，简化操作。

CREATE VIEW active_users AS
SELECT * FROM users WHERE is_active = true;

SELECT * FROM active_users;

4. 存储过程与函数

使用 PL/pgSQL 语言编写。

函数示例：

CREATE OR REPLACE FUNCTION get_user_count()
RETURNS integer AS $$
DECLARE
    count integer;
BEGIN
    SELECT COUNT(*) INTO count FROM users;
    RETURN count;
END;
$$ LANGUAGE plpgsql;

SELECT get_user_count();

第四部分：管理与维护

1. 用户与权限管理

角色 (Roles)：PostgreSQL 中用户和组统称为角色。
- 创建角色：CREATE ROLE myrole WITH LOGIN PASSWORD 'mypassword';
- 授予权限：
```
GRANT SELECT, INSERT ON TABLE users TO myrole;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myrole;
```

2. 备份与恢复

逻辑备份 (pg_dump)：

# 备份单个数据库
pg_dump -U username -d dbname -f backup.sql

# 备份所有数据库
pg_dumpall -U username -f alldbs.sql

恢复：

psql -U username -d dbname -f backup.sql

3. 性能优化

使用 EXPLAIN：分析查询执行计划，找出性能瓶颈。

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

Vacuum：清理“死元组”，回收存储空间，是 MVCC 的必要维护操作。autovacuum 通常自动运行，也可手动执行 VACUUM FULL;。

第五部分：扩展与实践

1. 常用扩展 (Extensions)

PostGIS：为 PostgreSQL 添加地理空间对象支持，使其成为地理信息系统（GIS）数据库。
pgcrypto：提供加密函数，如密码哈希。

uuid-ossp：生成 UUID（通用唯一识别码）。

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();

2. 编程接口

几乎所有主流编程语言都支持连接 PostgreSQL：
- Python：使用 psycopg2 或 asyncpg 库。
- Java：使用 JDBC 驱动。
- Node.js：使用 pg 库。
- Go：使用 pq 或 pgx 库。

3. 学习资源

官方文档：https://www.postgresql.org/docs/ - 最权威、最全面的参考资料。
在线教程：
- PostgreSQL Tutorial
- TutorialsPoint PostgreSQL
书籍：
- 《PostgreSQL 即学即用》
- 《PostgreSQL 修炼之道：从小工到专家》

总结：

学习 PostgreSQL 的最佳路径是：

安装体验：先动手安装，熟悉 psql 基本操作。
掌握 SQL：扎实学习标准 SQL 的 CRUD 操作、连接和聚合。
深入特性：理解事务、索引、MVCC 等核心机制。
实践管理：尝试用户授权、备份恢复等运维任务。
探索扩展：根据项目需求，学习如 PostGIS 等特定扩展。

PostgreSQL 学习手册

PostgreSQL 学习手册

第一部分：基础入门

1. 什么是 PostgreSQL？

2. 安装与配置

3. 基本操作 (通过 `psql` 命令行工具)

第二部分：核心概念与 SQL 操作

1. 数据库与模式 (Schema)

2. 数据定义语言 (DDL)

3. 数据操作语言 (DML)

4. 数据查询进阶

第三部分：高级特性

1. 索引

2. 事务 (Transactions)

3. 视图 (Views)

4. 存储过程与函数

第四部分：管理与维护

1. 用户与权限管理

2. 备份与恢复

3. 性能优化

第五部分：扩展与实践

1. 常用扩展 (Extensions)

2. 编程接口

3. 学习资源

添加新评论

最新文章

最近回复

分类

PostgreSQL 学习手册

PostgreSQL 学习手册

第一部分：基础入门

1. 什么是 PostgreSQL？

2. 安装与配置

3. 基本操作 (通过 psql 命令行工具)

第二部分：核心概念与 SQL 操作

1. 数据库与模式 (Schema)

2. 数据定义语言 (DDL)

3. 数据操作语言 (DML)

4. 数据查询进阶

第三部分：高级特性

1. 索引

2. 事务 (Transactions)

3. 视图 (Views)

4. 存储过程与函数

第四部分：管理与维护

1. 用户与权限管理

2. 备份与恢复

3. 性能优化

第五部分：扩展与实践

1. 常用扩展 (Extensions)

2. 编程接口

3. 学习资源

添加新评论

最新文章

最近回复

分类

3. 基本操作 (通过 `psql` 命令行工具)