relational-database教程 - MySQL与PostgreSQL完整指南
无论你是刚开始接触后端开发的初学者，还是已经写了几年 CRUD 想要补齐短板的开发者，relational-database都是必须扎实掌握的技术基石。本教程围绕两大开源主流数据库——MySQL 8.0+ 和 PostgreSQL 14+，从核心概念、SQL 语言到 Python 实战，带你系统建立从建表到优化、从原生查询到 ORM 编程的完整能力。
教程采用「基础 + 进阶 + Python 落地」三层结构，你可以根据自己的水平选择跳读或按顺序学习。文中所有代码都经过实际验证，并标注了双雄之间的差异点，方便你快速选型或迁移。
一、先理解relational-database的核心逻辑
relational-database（RDBMS）将数据存储在表（Table）中，表由行（Row）和列（Column）组成，每张表可以与其他表通过外键建立联系。这种模型的核心保障是ACID：
- 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部回滚。
- 一致性（Consistency）：事务前后数据必须满足所有预定义的规则（约束、触发器）。
- 隔离性（Isolation）：并发事务之间互不干扰。
- 持久性（Durability）：已提交的事务改动不会丢失，即使系统崩溃。
MySQL 和 PostgreSQL 都完整支持 ACID，但在实现细节和advanced-features上各有侧重：MySQL 以简单易用、读写分离成熟著称；PostgreSQL 更贴近 SQL 标准，对复杂查询、地理空间、JSON 结构化数据的支持更为强大。
二、快速安装与工具准备
MySQL 8.0
- macOS:
brew install mysql
- Windows: 下载社区版 MSI 安装包，安装时设置 root 密码
- Linux:
sudo apt install mysql-server
PostgreSQL 14
- macOS:
brew install postgresql
- Windows: 从官网下载安装程序
- Linux:
sudo apt install postgresql
安装后建议搭配可视化工具：MySQL Workbench、pgAdmin 4 或 VS Code 的数据库插件，方便前期直观查看表结构和执行结果。
三、SQL 语言五件套：DDL / DML / DQL / DCL / TCL
SQL 按照功能可以分成五类，这是学习relational-database的骨架。
1. DDL（数据定义语言）—— 建库、建表、定义约束
创建数据库
-- MySQL 和 PostgreSQL 通用
CREATE DATABASE school;
创建表
以班级和学生两张表为例：
-- 创建班级表
CREATE TABLE class (
id INT PRIMARY KEY AUTO_INCREMENT, -- MySQL 自增主键
name VARCHAR(50) NOT NULL
);
PostgreSQL 中自增列使用 SERIAL 或 GENERATED AS IDENTITY：
CREATE TABLE class (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
创建学生表，并用外键关联班级
-- MySQL
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
class_id INT,
CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class(id)
);
PostgreSQL 写法几乎一致，只需注意外键约束的定义方式相同。
如果想删除表或数据库，使用 DROP TABLE student; 或 DROP DATABASE school;，谨慎操作。
常用约束
PRIMARY KEY：唯一标识一行，自动创建唯一索引FOREIGN KEY：保证引用完整性UNIQUE：值不可重复NOT NULL：不允许为空DEFAULT：设置默认值
2. DML（数据操作语言）—— 增删改数据
插入数据
-- 插入班级
INSERT INTO class (name) VALUES ('三年级一班');
INSERT INTO class (name) VALUES ('三年级二班');
-- 批量插入学生
INSERT INTO student (name, class_id) VALUES
('张三', 1),
('李四', 1),
('王五', 2);
更新数据
-- 将张三的名字改为张三丰
UPDATE student SET name = '张三丰' WHERE name = '张三';
千万注意：不加 WHERE 条件会更新全表。
删除数据
DELETE FROM student WHERE class_id = 2;
同样，没有 WHERE 就会清空整张表，生产环境务必三思。
3. DQL（数据查询语言）—— 灵活取数
基本查询
-- 查询所有列
SELECT * FROM student;
-- 查询特定列
SELECT name, class_id FROM student;
-- 条件过滤
SELECT * FROM student WHERE class_id = 1;
模糊查询、范围与排序
-- 名字中包含'张'的学生
SELECT * FROM student WHERE name LIKE '%张%';
-- 某范围内的ID
SELECT * FROM student WHERE id BETWEEN 1 AND 3;
-- 按ID降序，分页取前2条（MySQL/PostgreSQL通用）
SELECT * FROM student ORDER BY id DESC LIMIT 2;
多表关联
-- 内连接：只取匹配到的行
SELECT s.name, c.name AS class_name
FROM student s
INNER JOIN class c ON s.class_id = c.id;
-- 左连接：保留左表所有行，右表无匹配则为NULL
SELECT s.name, c.name AS class_name
FROM student s
LEFT JOIN class c ON s.class_id = c.id;
聚合与分组
-- 每个班级的学生人数
SELECT class_id, COUNT(*) AS student_count
FROM student
GROUP BY class_id;
-- 筛选人数大于2的班级
SELECT class_id, COUNT(*) AS cnt
FROM student
GROUP BY class_id
HAVING cnt > 2;
子查询
-- 查询和张三同班的学生
SELECT * FROM student
WHERE class_id = (SELECT class_id FROM student WHERE name = '张三丰');
4. DCL（数据控制语言）—— 用户与权限
-- MySQL 创建用户并授权
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE ON school.* TO 'app_user'@'%';
FLUSH PRIVILEGES;
PostgreSQL 的命令稍有不同，但思想一致：使用 CREATE ROLE、GRANT 赋予权限，使用 REVOKE 回收。
权限最小化原则：只给应用程序实际需要的权限，切忌图方便直接给 ALL。
5. TCL（事务控制语言）—— 保证操作原子性
-- 开始事务
START TRANSACTION;
-- 执行一系列操作
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
-- 如果一切正常，提交事务
COMMIT;
-- 如果中间有错误，回滚
ROLLBACK;
MySQL 默认自动提交，生产环境常设为手动提交或显式包裹事务。PostgreSQL 也推荐显式使用 BEGIN…COMMIT/ROLLBACK。
四、进阶利器：索引优化与执行计划
当数据量超过十万级，索引设计直接影响性能。B+树是最常用的索引结构，理解「最左前缀原则」和「覆盖索引」就能避免大部分慢查询。
创建索引
-- 给 student 表的 name 字段创建索引
CREATE INDEX idx_student_name ON student(name);
-- 联合索引
CREATE INDEX idx_student_class_name ON student(class_id, name);
最左前缀原则：联合索引 (class_id, name) 在查询 WHERE class_id = 1 或 WHERE class_id = 1 AND name = '张三' 时生效，但 WHERE name = '张三' 无法利用该索引。
使用 EXPLAIN 分析查询
-- MySQL
EXPLAIN SELECT * FROM student WHERE class_id = 1 AND name LIKE '%张%';
-- PostgreSQL（更加详细）
EXPLAIN ANALYZE SELECT * FROM student WHERE class_id = 1 AND name LIKE '%张%';
重点关注 type 字段（如 ALL 表示全表扫，需要优化）、key 使用的索引及扫描行数。
常见索引失效场景：
- 对索引列进行函数运算，如
WHERE YEAR(create_time) = 2021
- 前导模糊查询
LIKE '%abc'
- 隐式类型转换，例如把字符串字段和数字比较
OR 条件中有一侧未使用索引
五、视图、函数与存储过程（适度使用）
视图可以封装复杂查询，简化应用层代码：
CREATE VIEW student_class_view AS
SELECT s.id, s.name, c.name AS class_name
FROM student s
JOIN class c ON s.class_id = c.id;
-- 之后可直接像查表一样查询
SELECT * FROM student_class_view;
存储函数与过程适合将业务逻辑下沉到数据库，但不要过度使用，否则会降低代码可维护性和迁移灵活性。一般建议只用于数据清洗、批量统计等场景，业务逻辑尽量放在应用层。
六、Python 实战：从原生驱动到 ORM
1. 原生连接 MySQL
import pymysql
conn = pymysql.connect(
host='localhost',
user='root',
password='your_password',
database='school',
charset='utf8mb4'
)
cursor = conn.cursor()
cursor.execute("SELECT * FROM student WHERE class_id = %s", (1,))
rows = cursor.fetchall()
for row in rows:
print(row)
conn.close()
连接 PostgreSQL 可以使用 psycopg2，接口类似。
2. 参数化查询（防止SQL注入）
永远不要用字符串拼接的方式构造 SQL，务必使用 %s（MySQL）或 %s（Postgres）占位符并将参数以元组传入。这是防止 SQL 注入最有效的手段。
3. SQLAlchemy ORM 入门
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
engine = create_engine('mysql+pymysql://user:pass@localhost/school')
Base = declarative_base()
class Student(Base):
__tablename__ = 'student'
id = Column(Integer, primary_key=True)
name = Column(String(50))
class_id = Column(Integer, ForeignKey('class.id'))
# 创建会话
Session = sessionmaker(bind=engine)
session = Session()
# 查询学生
students = session.query(Student).filter(Student.class_id == 1).all()
for s in students:
print(s.name)
ORM 能自动处理对象与表之间的映射，并支持事务、连接池等高级功能。SQLAlchemy 2.0 全面拥抱异步，是现代 Python 项目的首选。
七、实践项目建议
光看不练假把式，建议你按以下顺序动手：
- 班级考勤原型：实现班级、学生、考勤记录三表，练习多表关联查询和聚合统计。
- 电商订单表优化：导入十万条模拟数据，用 EXPLAIN 分析慢查询，尝试建立单列索引和联合索引，比较性能变化。
- 博客后端数据层：用 FastAPI + SQLAlchemy 搭建用户、文章、评论的 CRUD 接口，加入分页、事务和连接池。
八、常见问题速查
Q：MySQL 和 PostgreSQL 该选哪个入门？
A：纯小白首选 MySQL，社区资源丰富，报错更容易搜索到答案；如果你的业务依赖 JSON、地理信息或对数据完整性要求极高，直接上 PostgreSQL 也不会后悔。
Q：索引是不是越多越好？
A：绝对不是。索引会拖慢写操作，并占用磁盘。只为高频查询的 WHERE、JOIN ON、ORDER BY 列建立索引。
Q：Python 数据库编程用原生驱动还是 ORM？
A：小工具、简单脚本用原生驱动；团队协作的中大型项目，推荐 SQLAlchemy ORM，能显著降低重复代码，提升可维护性。
学习relational-database没有捷径，最好的方法就是边学边敲代码。每掌握一个新知识点，都用自己的数据验证一遍。遇到慢查询就打开 EXPLAIN，遇到死锁就理解事务隔离级别。保持这样的习惯，你很快就能从“会用”进阶到“懂原理、能优化”。
接下来学什么？
掌握本教程内容后，你可以继续深入：
- 缓存层：Redis 实现热点数据缓存
- 分布式扩展：MySQL 主从复制、PostgreSQL 流复制
- 云上实践：阿里云 RDS、AWS Aurora 等云数据库服务
- NoSQL：MongoDB、ClickHouse 应对非结构化或分析型场景
祝你早日构建出稳健、高效的数据层，为更复杂的系统打下坚实基础。
