PostgreSQL 完整学习指南

核心知识实战导向避坑指南

🎯 学习目标

本指南基于 Udemy PostgreSQL 课程，提炼了从基础到进阶的核心知识点，帮助你快速掌握 PostgreSQL 数据库的核心操作技能。

一、📚 环境准备与基础概念

1.1 核心工具

工具	用途	关键提示
PostgreSQL	开源关系型数据库	安装时设置的密码务必记住，遗忘后只能重装
pgAdmin	图形化管理界面	用于执行 SQL、查看结果、管理数据库
psycopg2	Python 连接库	用于在 Python 中操作 PostgreSQL

1.2 数据库 vs 电子表格

💡 为什么选择数据库？

✅ 可处理海量数据（电子表格会崩溃）
✅ 严格的权限控制和数据安全
✅ 高效的多表关联查询
✅ 支持自动化和程序集成

二、🔍 SQL 查询基础

2.1 基本查询结构

sql

SELECT column_name(s)
FROM table_name
WHERE condition(s)
ORDER BY column_name [ASC|DESC]
LIMIT number;

2.2 核心关键字速查

关键字	作用	示例
`SELECT DISTINCT`	去重查询	`SELECT DISTINCT rating FROM film;`
`COUNT()`	计数	`SELECT COUNT(*) FROM customer;`
`WHERE`	条件过滤	`WHERE amount > 5 AND customer_id = 10;`
`ORDER BY`	排序	`ORDER BY payment_date DESC;`
`LIMIT`	限制返回行数	`LIMIT 10;`
`BETWEEN`	范围查询（含边界）	`WHERE cost BETWEEN 5 AND 15;`
`IN`	匹配列表	`WHERE name IN ('John', 'Jake');`
`LIKE` / `ILIKE`	模式匹配	`WHERE name LIKE 'J%';`

2.3 通配符

% — 匹配任意长度字符（包括 0 个）
_ — 匹配单个字符

sql

-- 查找名字以 'Jen' 开头，姓氏包含 'er' 的客户
SELECT first_name, last_name
FROM customer
WHERE first_name ILIKE 'Jen%' AND last_name ILIKE '%er%';

⚠️ 常见错误

LIKE 区分大小写，ILIKE 不区分
BETWEEN 用于日期时注意时间戳边界问题
字符串比较时 = 是区分大小写的

三、📊 聚合与分组（GROUP BY）

3.1 聚合函数

函数	作用
`AVG()`	平均值
`COUNT()`	计数
`MAX()` / `MIN()`	最大/最小值
`SUM()`	求和
`ROUND(value, 2)`	四舍五入

3.2 GROUP BY 核心规则

sql

SELECT
    category_column,
    AGG_FUNC(data_column)
FROM table_name
WHERE row_filter          -- 1️⃣ 先过滤行
GROUP BY category_column  -- 2️⃣ 再分组
HAVING group_filter;      -- 3️⃣ 最后过滤分组结果

🚫 关键规则

SELECT 中的非聚合列必须出现在 GROUP BY 中！

sql

-- ❌ 错误：staff_id 不在 GROUP BY 中
SELECT customer_id, staff_id, SUM(amount) 
FROM payment 
GROUP BY customer_id;

-- ✅ 正确
SELECT customer_id, staff_id, SUM(amount) 
FROM payment 
GROUP BY customer_id, staff_id;

3.3 WHERE vs HAVING

特性	WHERE	HAVING
作用对象	单行数据	分组结果
执行时机	GROUP BY 之前	GROUP BY 之后
能否用聚合函数	❌ 不能	✅ 可以

sql

-- 查找交易超过 40 次的客户
SELECT customer_id, COUNT(*) AS transactions
FROM payment
GROUP BY customer_id
HAVING COUNT(*) >= 40;

四、🔗 表连接（JOINS）

4.1 JOIN 类型对比

JOIN 类型	描述	Venn 图类比
`INNER JOIN`	只返回两表都有匹配的行	交集
`LEFT JOIN`	返回左表所有行 + 右表匹配行	左圆 + 交集
`RIGHT JOIN`	返回右表所有行 + 左表匹配行	右圆 + 交集
`FULL OUTER JOIN`	返回两表所有行	并集

4.2 基本语法

sql

SELECT a.column1, b.column2
FROM table_A AS a
INNER JOIN table_B AS b
ON a.common_column = b.common_column;

4.3 实战示例

查找没有库存的电影（LEFT JOIN + IS NULL）：

sql

SELECT film.title
FROM film
LEFT JOIN inventory
ON film.film_id = inventory.film_id
WHERE inventory.inventory_id IS NULL;

链式连接（多对多关系）：

sql

-- 查找演员 Nick Wahlberg 出演的所有电影
SELECT film.title
FROM film
INNER JOIN film_actor ON film.film_id = film_actor.film_id
INNER JOIN actor ON film_actor.actor_id = actor.actor_id
WHERE actor.first_name = 'Nick' AND actor.last_name = 'Wahlberg';

⚠️ 常见陷阱

列名相同时必须指定表前缀，否则报 "ambiguous column" 错误
LEFT JOIN 的顺序很重要：FROM A LEFT JOIN B ≠ FROM B LEFT JOIN A
多对多关系必须通过桥接表，不能直接连接
别名不能用于 WHERE/HAVING 子句（因为别名在最后才赋值）

五、⚡ 高级 SQL 特性

5.1 时间戳函数

sql

-- 提取日期部分
EXTRACT(YEAR FROM payment_date)
EXTRACT(MONTH FROM payment_date)
EXTRACT(DOW FROM payment_date)  -- Day of Week (0=周日)

-- 计算年龄/时间差
AGE(timestamp)

-- 格式化输出
TO_CHAR(payment_date, 'YYYY-MM-DD')

5.2 子查询

标量子查询（返回单个值）：

sql

-- 查找租金高于平均水平的电影
SELECT title, rental_rate
FROM film
WHERE rental_rate > (SELECT AVG(rental_rate) FROM film);

使用 IN 的子查询：

sql

SELECT customer_id
FROM payment
WHERE amount IN (SELECT MAX(amount) FROM payment);

EXISTS 相关子查询：

sql

SELECT first_name, last_name
FROM customer AS c
WHERE EXISTS (
    SELECT 1 FROM payment AS p 
    WHERE p.customer_id = c.customer_id AND p.amount > 11
);

5.3 自连接（Self-Join）

sql

-- 查找具有相同片长的电影配对
SELECT f1.title, f2.title, f1.length
FROM film AS f1
INNER JOIN film AS f2 
ON f1.length = f2.length 
AND f1.film_id != f2.film_id;  -- 排除自身匹配

🚫 自连接必知

必须使用不同的别名（AS f1, AS f2）
记得排除自身匹配（AND f1.id != f2.id）

5.4 字符串函数

sql

-- 拼接
'Hello' || ' ' || 'World'

-- 大小写转换
UPPER(column), LOWER(column)

-- 提取子串
LEFT(first_name, 1)

-- 生成自定义邮箱示例
SELECT 
    LOWER(LEFT(first_name, 1) || last_name) || '@company.com' AS email
FROM customer;

六、🎯 条件逻辑与视图

6.1 CASE 语句

sql

-- 通用 CASE（最灵活）
SELECT
    title,
    CASE
        WHEN rental_rate < 1 THEN 'Bargain'
        WHEN rental_rate < 3 THEN 'Regular'
        ELSE 'Premium'
    END AS price_category
FROM film;

数据透视（Pivoting）：

sql

-- 将不同评级统计到各自列中
SELECT
    SUM(CASE WHEN rating = 'R' THEN 1 ELSE 0 END) AS r_rated,
    SUM(CASE WHEN rating = 'PG' THEN 1 ELSE 0 END) AS pg_rated,
    SUM(CASE WHEN rating = 'PG-13' THEN 1 ELSE 0 END) AS pg13_rated
FROM film;

6.2 处理 NULL 值

sql

-- COALESCE: 返回第一个非 NULL 值
SELECT item, price - COALESCE(discount, 0) AS final_price
FROM products;

-- NULLIF: 防止除零错误
SELECT SUM(amount) / NULLIF(COUNT(*), 0) AS safe_average
FROM payments;

6.3 视图（VIEW）

sql

-- 创建视图
CREATE VIEW customer_summary AS
SELECT 
    c.first_name,
    c.last_name,
    SUM(p.amount) AS total_spent
FROM customer AS c
JOIN payment AS p ON c.customer_id = p.customer_id
GROUP BY c.customer_id;

-- 使用视图（像普通表一样）
SELECT * FROM customer_summary WHERE total_spent > 100;

💡 视图的优势

📌 简化复杂查询
🔒 权限控制（只暴露特定列）
🔄 代码复用

七、🏗️ 数据库设计与管理

7.1 创建表

sql

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    birth_date DATE CHECK (birth_date > '1900-01-01'),
    hire_date DATE CHECK (hire_date > birth_date),
    salary INT CHECK (salary > 0),
    department_id INT REFERENCES departments(dept_id)
);

7.2 约束类型

约束	作用
`PRIMARY KEY`	唯一标识，不能为 NULL
`SERIAL`	自增整数（用于主键）
`NOT NULL`	不允许空值
`UNIQUE`	值必须唯一
`CHECK`	自定义验证条件
`REFERENCES`	外键，引用其他表

7.3 数据操作

sql

-- 插入
INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('John', 'Doe', '2025-01-15', 60000);

-- 更新
UPDATE employees 
SET salary = 65000 
WHERE employee_id = 5;

-- 删除
DELETE FROM employees 
WHERE employee_id = 5;

-- 修改表结构
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees DROP COLUMN phone;

🚫 致命错误

没有 WHERE 的 UPDATE/DELETE 会影响所有行！

sql

-- ❌ 这会删除整张表的数据！
DELETE FROM employees;

-- ✅ 先用 SELECT 验证
SELECT * FROM employees WHERE hire_date < '2020-01-01';
-- ✅ 再执行删除
DELETE FROM employees WHERE hire_date < '2020-01-01';

7.4 数据类型选择建议

数据	❌ 错误类型	✅ 正确类型	原因
电话号码	`INT`	`VARCHAR`	需要保留前导零、连字符
邮政编码	`INT`	`VARCHAR`	可能包含字母或前导零
价格	`FLOAT`	`NUMERIC(10,2)`	避免浮点数精度问题
日期时间	`VARCHAR`	`TIMESTAMP`	便于时间计算和排序

八、🐍 Python 集成（psycopg2）

8.1 标准工作流程

python

import psycopg2

try:
    # 1. 建立连接
    conn = psycopg2.connect(
        database="dvdrental",
        user="postgres",
        password="your_password",
        host="localhost"
    )
    
    # 2. 创建游标
    cur = conn.cursor()
    
    # 3. 执行查询
    cur.execute("SELECT * FROM customer LIMIT 5;")
    
    # 4. 获取结果
    rows = cur.fetchall()
    for row in rows:
        print(row)
    
    # 5. 提交事务（INSERT/UPDATE/DELETE 时需要）
    conn.commit()
    
except psycopg2.Error as e:
    print(f"Database error: {e}")
    conn.rollback()
    
finally:
    # 6. 关闭连接
    if conn:
        conn.close()

8.2 关键方法

方法	作用
`cur.execute(query)`	执行 SQL 语句
`cur.fetchone()`	获取一行
`cur.fetchmany(n)`	获取 n 行
`cur.fetchall()`	获取所有行
`conn.commit()`	提交事务
`conn.close()`	关闭连接

🚫 SQL 注入风险

永远不要用字符串拼接构建查询！

python

# ❌ 危险！易受 SQL 注入攻击
name = input("Enter name: ")
cur.execute(f"SELECT * FROM customer WHERE first_name = '{name}'")

# ✅ 安全：使用参数化查询
cur.execute(
    "SELECT * FROM customer WHERE first_name = %s",
    (name,)  # 注意逗号，这是一个元组
)

九、💡 最佳实践与避坑指南

9.1 推荐做法

查询优化：

只查询需要的列，避免 SELECT *
使用索引列作为 WHERE 条件
对大数据集使用 LIMIT 分页

代码规范：

SQL 关键字大写，表名/列名小写
复杂查询使用别名提高可读性
为聚合结果使用有意义的 AS 别名

安全性：

使用参数化查询防止 SQL 注入
最小权限原则（用户只获取必要权限）
敏感数据加密存储

数据完整性：

合理使用约束保证数据质量
设计时考虑好主键和外键关系
时间类型选择 TIMESTAMPTZ 而非 DATE

9.2 常见错误总结

错误	后果	正确做法
忘记 WHERE 的 UPDATE/DELETE	修改/删除全表	先用 SELECT 验证条件
用 WHERE 过滤聚合结果	语法错误	使用 HAVING
整数除法（1/50）	结果为 0	使用浮点数（1.0/50）
忘记 GROUP BY 中包含所有非聚合列	语法错误	检查 SELECT 和 GROUP BY 一致性
自连接不排除自身	结果包含无意义配对	加条件 `AND a.id != b.id`
BETWEEN 处理时间戳边界	遗漏当天数据	用 `>= AND <` 代替 BETWEEN
大数据集用 fetchall()	内存溢出	使用 fetchone() 或 fetchmany() 迭代
忘记关闭数据库连接	资源耗尽	使用 try-finally 确保关闭

9.3 调试技巧

sql

-- 1. 查看表结构
\d table_name  -- psql 命令行
-- 或在 pgAdmin 中查看 Columns 标签

-- 2. 检查数据样本
SELECT * FROM table_name LIMIT 10;

-- 3. 验证 JOIN 结果
-- 先单独查询每个表，确认数据存在
SELECT COUNT(*) FROM table_a;
SELECT COUNT(*) FROM table_b;
-- 再执行 JOIN

-- 4. 逐步构建复杂查询
-- 先写 SELECT FROM
-- 加 WHERE
-- 加 GROUP BY
-- 最后加 HAVING 和 ORDER BY

十、📌 快速查询表

10.1 SQL 命令执行顺序

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

10.2 常用查询模板

Top N 查询：

sql

SELECT column_name
FROM table_name
ORDER BY column_name DESC
LIMIT N;

去重统计：

sql

SELECT COUNT(DISTINCT column_name)
FROM table_name;

分组排名：

sql

SELECT category, SUM(amount) AS total
FROM sales
GROUP BY category
ORDER BY total DESC;

外连接查找缺失项：

sql

SELECT a.id
FROM table_a AS a
LEFT JOIN table_b AS b ON a.id = b.a_id
WHERE b.id IS NULL;

十一、🔗 扩展资源

💡 学习建议

📖 理论与实践结合 — 每学一个概念立即在 pgAdmin 中实践
🔁 反复练习 — 对照真实业务场景编写查询
🐛 从错误中学习 — 遇到报错先自己分析，再查文档
🎯 项目驱动 — 尝试为实际项目设计和实现数据库

最后更新：2025-11-23

PostgreSQL 完整学习指南 ​

一、📚 环境准备与基础概念 ​

1.1 核心工具 ​

1.2 数据库 vs 电子表格 ​

二、🔍 SQL 查询基础 ​

2.1 基本查询结构 ​

2.2 核心关键字速查 ​

2.3 通配符 ​

三、📊 聚合与分组（GROUP BY） ​

3.1 聚合函数 ​

3.2 GROUP BY 核心规则 ​

3.3 WHERE vs HAVING ​

四、🔗 表连接（JOINS） ​

4.1 JOIN 类型对比 ​

4.2 基本语法 ​

4.3 实战示例 ​

五、⚡ 高级 SQL 特性 ​

5.1 时间戳函数 ​

5.2 子查询 ​

5.3 自连接（Self-Join） ​

5.4 字符串函数 ​

六、🎯 条件逻辑与视图 ​

6.1 CASE 语句 ​

6.2 处理 NULL 值 ​

6.3 视图（VIEW） ​

七、🏗️ 数据库设计与管理 ​

7.1 创建表 ​

7.2 约束类型 ​

7.3 数据操作 ​

7.4 数据类型选择建议 ​

八、🐍 Python 集成（psycopg2） ​

8.1 标准工作流程 ​

8.2 关键方法 ​

九、💡 最佳实践与避坑指南 ​

9.1 推荐做法 ​

9.2 常见错误总结 ​

9.3 调试技巧 ​

十、📌 快速查询表 ​

10.1 SQL 命令执行顺序 ​

10.2 常用查询模板 ​

十一、🔗 扩展资源 ​