PostgreSQL 学习手册
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库。
- Python：使用
3. 学习资源
- 官方文档：https://www.postgresql.org/docs/ - 最权威、最全面的参考资料。
在线教程：
书籍：
- 《PostgreSQL 即学即用》
- 《PostgreSQL 修炼之道：从小工到专家》
总结：
学习 PostgreSQL 的最佳路径是：
- 安装体验：先动手安装，熟悉
psql基本操作。 - 掌握 SQL：扎实学习标准 SQL 的 CRUD 操作、连接和聚合。
- 深入特性：理解事务、索引、MVCC 等核心机制。
- 实践管理：尝试用户授权、备份恢复等运维任务。
- 探索扩展：根据项目需求，学习如 PostGIS 等特定扩展。