一、关系数据库概述
1.1 核心概念
关系数据库（Relational Database）是基于关系模型组织数据的数据库系统，由E.F. Codd于1970年提出。其核心思想是将数据组织为二维表（Table）的形式，通过表与表之间的关联关系来反映现实世界中的事物联系。
表（Table）：也称关系（Relation），是数据的二维集合，由行和列组成。每个表代表一个实体类型，如"用户表"、"订单表"。
行（Row）：也称元组（Tuple）或记录（Record），代表一个具体的实体实例。例如用户表中的一行对应一个具体的用户。
列（Column）：也称字段（Field）或属性（Attribute），代表实体的某种特征。例如用户表中的"姓名"、"年龄"字段。
主键（Primary Key）：唯一标识表中每一行的一列或列组合。主键值必须唯一且不为NULL。例如用户表中的"用户ID"。
外键（Foreign Key）：一个表中的字段，引用另一个表的主键，用于建立表之间的关联关系。例如订单表中的"用户ID"引用用户表的"用户ID"。
1.2 主流关系数据库对比
| 特性 |
MySQL |
PostgreSQL |
SQLite |
| 许可证 |
GPL / 商业版 |
MIT-like |
Public Domain |
| 架构 |
客户端-服务器 |
客户端-服务器 |
嵌入式 |
| ACID支持 |
取决于存储引擎 |
完全支持 |
完全支持 |
| 并发控制 |
表级/行级锁 |
MVCC |
文件级锁 |
| JSON支持 |
有限 |
完善（二进制JSONB） |
有限 |
| 全文搜索 |
内置 |
内置（更强大） |
内置（FTS5） |
| 典型场景 |
Web应用、LAMP栈 |
复杂查询、数据仓库 |
移动端、嵌入式、测试 |
选型建议：中小型Web项目推荐MySQL（生态成熟、文档丰富）；需要复杂查询和高级数据类型推荐PostgreSQL；本地开发测试和移动应用推荐SQLite。
1.3 ACID事务特性
ACID是关系数据库事务的四个基本特性，确保数据操作的可靠性：
- 原子性（Atomicity）：事务中的所有操作要么全部成功提交，要么全部失败回滚。不存在部分执行的情况。例如银行转账，扣款和入账必须同时成功或同时失败。
- 一致性（Consistency）：事务执行前后，数据库必须保持一致状态。所有定义的数据约束（如主键、外键、CHECK约束）在任何事务前后都必须满足。
- 隔离性（Isolation）：并发执行的事务之间互不干扰。每个事务都感觉不到其他事务的存在。SQL标准定义了四个隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。
- 持久性（Durability）：一旦事务提交成功，其结果就是永久的，即使系统崩溃也不会丢失。通过预写日志（WAL）等技术来保证。
二、SQL语言基础
2.1 DDL（数据定义语言）
DDL用于定义和管理数据库结构，包括创建、修改和删除数据库对象。
CREATE TABLE — 创建表：
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
age INT CHECK (age >= 0 AND age <= 150),
balance DECIMAL(10, 2) DEFAULT 0.00,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
常用字段类型：
| 类型 | 说明 | 示例 |
|---|
| INT / INTEGER | 整数 | INT, INT UNSIGNED, BIGINT |
| VARCHAR(n) | 可变长度字符串 | VARCHAR(50) |
| TEXT | 长文本 | TEXT, LONGTEXT |
| FLOAT / DOUBLE | 浮点数 | FLOAT(10,2) |
| DECIMAL(p,s) | 精确小数（财务用） | DECIMAL(10,2) |
| BOOLEAN | 布尔值 | TRUE / FALSE |
| DATE | 日期 | '2026-05-05' |
| TIMESTAMP | 日期时间戳 | '2026-05-05 12:00:00' |
| ENUM | 枚举值 | ENUM('male','female') |
ALTER TABLE — 修改表：
-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
-- 修改列类型
ALTER TABLE users MODIFY COLUMN age TINYINT UNSIGNED;
-- 重命名列
ALTER TABLE users CHANGE COLUMN is_active status BOOLEAN DEFAULT TRUE;
-- 删除列
ALTER TABLE users DROP COLUMN phone;
-- 添加索引
ALTER TABLE users ADD INDEX idx_email (email);
DROP TABLE — 删除表：
-- 删除表（不可恢复）
DROP TABLE IF EXISTS users;
-- 清空表数据但保留表结构
TRUNCATE TABLE users;
约束类型：
- PRIMARY KEY：主键约束，唯一标识行，自动包含NOT NULL和UNIQUE
- FOREIGN KEY：外键约束，维护表间引用完整性
- UNIQUE：唯一约束，确保列值不重复
- NOT NULL：非空约束，禁止NULL值
- DEFAULT：默认值约束，未提供值时使用默认值
- CHECK：检查约束，确保值满足条件（MySQL 8.0.16+支持）
2.2 DML（数据操作语言）
INSERT — 插入数据：
-- 插入单条记录
INSERT INTO users (username, email, password_hash, age)
VALUES ('zhangsan', 'zhangsan@example.com', 'hashed_pwd_123', 28);
-- 插入多条记录
INSERT INTO users (username, email, password_hash, age) VALUES
('lisi', 'lisi@example.com', 'hashed_pwd_456', 35),
('wangwu', 'wangwu@example.com', 'hashed_pwd_789', 22),
('zhaoliu', 'zhaoliu@example.com', 'hashed_pwd_abc', 30);
SELECT — 查询数据：
-- 查询所有列
SELECT * FROM users;
-- 查询特定列
SELECT id, username, email FROM users;
-- 查询并设置别名
SELECT username AS 用户名, email AS 邮箱 FROM users;
-- 去重查询
SELECT DISTINCT age FROM users;
-- 简单表达式计算
SELECT username, age, age + 10 AS age_in_10_years FROM users;
UPDATE — 更新数据：
-- 更新特定记录
UPDATE users SET age = 29 WHERE id = 1;
-- 更新多条记录
UPDATE users SET is_active = FALSE WHERE age > 60;
-- 使用表达式更新
UPDATE products SET price = price * 0.9 WHERE category = '清仓';
DELETE — 删除数据：
-- 删除特定记录
DELETE FROM users WHERE id = 5;
-- 删除所有记录（逐行删除，可回滚）
DELETE FROM users;
-- 注意：TRUNCATE不能带WHERE条件
TRUNCATE TABLE users; -- 快速清表，不可回滚
三、SQL查询进阶
3.1 WHERE条件过滤
-- 比较运算符
SELECT * FROM users WHERE age >= 18;
SELECT * FROM products WHERE price < 100;
SELECT * FROM orders WHERE status != 'cancelled';
-- 逻辑运算符
SELECT * FROM users WHERE age >= 18 AND is_active = TRUE;
SELECT * FROM products WHERE category = '电子' OR category = '家电';
SELECT * FROM users WHERE NOT is_active = TRUE;
3.2 ORDER BY排序
-- 升序（ASC，默认）
SELECT * FROM users ORDER BY created_at;
-- 降序（DESC）
SELECT * FROM products ORDER BY price DESC;
-- 多字段排序
SELECT * FROM orders ORDER BY status ASC, created_at DESC;
3.3 LIMIT / OFFSET分页
-- 限制返回行数
SELECT * FROM products LIMIT 10;
-- 分页查询（每页20条，查询第3页）
SELECT * FROM products LIMIT 20 OFFSET 40;
-- MySQL专用语法
SELECT * FROM products LIMIT 40, 20; -- 偏移40，取20条
3.4 LIKE模糊查询
-- % 匹配任意多个字符
SELECT * FROM users WHERE username LIKE '张%'; -- 以"张"开头
SELECT * FROM users WHERE email LIKE '%gmail.com'; -- 以gmail.com结尾
SELECT * FROM users WHERE username LIKE '%小%'; -- 包含"小"
-- _ 匹配单个字符
SELECT * FROM users WHERE phone LIKE '138________'; -- 以138开头的11位号码
3.5 IN / NOT IN
SELECT * FROM products WHERE category IN ('手机', '电脑', '平板');
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist);
3.6 BETWEEN范围查询
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31';
-- BETWEEN包含边界值，等价于 price >= 100 AND price <= 500
3.7 IS NULL / IS NOT NULL
-- 注意：不能用 = NULL 判断
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;
3.8 聚合函数与GROUP BY
-- 常用聚合函数
SELECT COUNT(*) AS 总数 FROM users;
SELECT AVG(price) AS 均价 FROM products;
SELECT SUM(amount) AS 总收入 FROM orders WHERE status = 'completed';
SELECT MAX(price) AS 最高价, MIN(price) AS 最低价 FROM products;
-- GROUP BY分组统计
SELECT category, COUNT(*) AS 商品数, AVG(price) AS 均价
FROM products
GROUP BY category;
-- 多字段分组
SELECT category, status, COUNT(*) AS 数量
FROM products
GROUP BY category, status;
3.9 HAVING分组过滤
-- HAVING在分组后过滤，WHERE在分组前过滤
SELECT category, COUNT(*) AS 商品数, AVG(price) AS 均价
FROM products
WHERE price > 0
GROUP BY category
HAVING 商品数 >= 10 AND AVG(price) > 50
ORDER BY 均价 DESC;
四、表连接（JOIN）
核心理解：表连接是关系数据库最强大的特性之一，它允许你在一个查询中组合来自多个表的数据。理解JOIN的语义和执行方式是成为合格数据库开发者的关键。
4.1 INNER JOIN（内连接）
返回两个表中满足连接条件的匹配行。如果某行在另一个表中没有匹配，则不会出现在结果中。
-- 查询每个订单对应的用户信息
SELECT o.id AS 订单号, u.username AS 用户名, o.amount AS 金额, o.created_at AS 下单时间
FROM orders o
INNER JOIN users u ON o.user_id = u.id;
-- 多条件连接
SELECT *
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.id AND oi.price = p.price;
4.2 LEFT JOIN（左外连接）
返回左表的所有行，右表没有匹配的行用NULL填充。常用于查找"有A但没有B关联"的数据。
-- 查询所有用户及其订单（包括从未下单的用户）
SELECT u.username, o.id AS 订单号, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 查找从未下单的用户
SELECT u.username, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
4.3 RIGHT JOIN（右外连接）
返回右表的所有行，左表没有匹配的行用NULL填充。功能等同于将LEFT JOIN的表顺序交换。
-- 等价于上例交换表顺序
SELECT u.username, o.id AS 订单号, o.amount
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;
4.4 FULL OUTER JOIN（全外连接）
返回两个表的所有行，不匹配的用NULL填充。MySQL不直接支持FULL OUTER JOIN，需用UNION模拟。
-- 完整外连接（MySQL模拟）
SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.*, o.* FROM users u RIGHT JOIN orders o ON u.id = o.user_id;
4.5 CROSS JOIN（交叉连接）
返回两个表的笛卡尔积，左表的每一行与右表的每一行组合。通常需要WHERE条件过滤，否则结果集巨大。
-- 所有商品和所有颜色的组合
SELECT p.name AS 商品, c.name AS 颜色
FROM products p
CROSS JOIN colors c;
-- 隐式交叉连接（不推荐）
SELECT * FROM products, colors;
4.6 多表连接实践
-- 三表连接：用户 -> 订单 -> 订单详情 -> 商品
SELECT
u.username,
o.id AS 订单号,
p.name AS 商品名,
oi.quantity AS 数量,
oi.price AS 单价,
(oi.quantity * oi.price) AS 小计
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
ORDER BY o.id, oi.id;
五、子查询与联合
5.1 WHERE子查询
-- 查询价格高于平均价的商品
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- 查询有订单的用户（使用IN）
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);
-- 查询没有订单的用户（使用NOT IN，注意NULL问题）
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
5.2 FROM子查询（派生表）
-- 统计每个分类的商品数，再筛选
SELECT category, 商品数
FROM (
SELECT category, COUNT(*) AS 商品数
FROM products
GROUP BY category
) AS stats
WHERE 商品数 > 5
ORDER BY 商品数 DESC;
5.3 EXISTS / NOT EXISTS
EXISTS只关心子查询是否返回行，不关心具体值。通常比IN更高效，尤其是子查询表很大时。
-- 查询有过订单的用户（EXISTS版本）
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- 查询从未下单的用户
SELECT * FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
5.4 UNION / UNION ALL
UNION合并多个查询结果，自动去重；UNION ALL不去重，性能更好。
-- 合并当月和上月的畅销商品
SELECT product_id, SUM(quantity) AS total_sold
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
WHERE MONTH(o.created_at) = 5
GROUP BY product_id
HAVING total_sold >= 100
UNION ALL
SELECT product_id, SUM(quantity) AS total_sold
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
WHERE MONTH(o.created_at) = 4
GROUP BY product_id
HAVING total_sold >= 100;
-- UNION要求各SELECT的列数相同，对应列类型兼容
六、数据库设计原则
6.1 规范化理论
数据库规范化是通过分解表来减少数据冗余、避免更新异常的过程。理解规范化对于设计高质量的数据库至关重要。
第一范式（1NF）：每个列都是不可再分的原子值，每列的值都是单一值。反例：一个"爱好"字段存储了"游泳,阅读,音乐"多个值。
第二范式（2NF）：满足1NF，且每个非主键列完全依赖于主键（消除部分依赖）。适用于联合主键的表。反例：(学生ID, 课程ID)为主键，"学生姓名"只依赖于学生ID，存在部分依赖。
第三范式（3NF）：满足2NF，且非主键列不传递依赖于主键。反例：订单表中有"用户ID"和"用户地址"，用户地址传递依赖于用户ID。
BCNF（Boyce-Codd范式）：3NF的增强版，要求每个决定因素都是候选键。更严格地处理函数依赖关系。
"规范化是为了消除冗余，但完全规范化到3NF并不总是最佳选择。在实际项目中，合理使用反规范化可以在查询性能和数据一致性之间取得平衡。"
6.2 反规范化
在某些场景下，刻意引入冗余数据以提高查询性能。常见策略包括：
- 冗余列：在订单表中直接存储用户姓名，避免每次查询都JOIN用户表
- 汇总表：定期汇总统计数据到独立表，避免实时计算大量数据
- 分区表：按时间或地域将大表拆分为多个物理分区
6.3 实体关系图（ER图）
ER图是数据库设计的可视化工具，使用实体（矩形）、属性（椭圆）和关系（菱形）来描述数据模型。常见的实体关系包括：
- 一对一（1:1）：一个用户对应一个身份证号
- 一对多（1:N）：一个用户有多个订单，一个订单属于一个用户
- 多对多（M:N）：一个学生选修多门课程，一门课程有多个学生。通常通过中间表实现
6.4 索引设计原则
索引是提高查询性能的重要手段，但不合理使用会降低写入性能并占用存储空间。
- 主键索引：每个表都应有一个主键，InnoDB默认使用聚簇索引
- 唯一索引：对业务上要求唯一的字段加唯一索引（如邮箱、身份证号）
- 普通索引：对经常出现在WHERE、JOIN、ORDER BY中的字段加索引
- 复合索引：多个字段组合查询时，遵循"最左前缀"原则
- 覆盖索引：索引包含查询所需的所有字段，可避免回表查询
-- 创建索引示例
CREATE INDEX idx_users_email ON users(email); -- 单列索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status); -- 复合索引
CREATE UNIQUE INDEX idx_users_phone ON users(phone); -- 唯一索引
-- 慢查询优化：使用EXPLAIN分析查询计划
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'pending';
6.5 选择合适的字段类型
- 整数类型：优先使用TINYINT/SMALLINT/MEDIUMINT，而非直接使用INT或BIGINT
- 字符串：定长用CHAR，变长用VARCHAR，大文本用TEXT
- 小数：金融场景用DECIMAL，避免FLOAT/DOUBLE的精度问题
- 日期时间：只需要日期用DATE，需要精确时间用DATETIME/TIMESTAMP
- 避免过度使用NULL：NULL值占用额外空间，且索引效率较低
七、Python连接数据库
7.1 SQLite3（内置模块）
Python内置sqlite3模块，无需安装任何额外依赖即可操作SQLite数据库，非常适合本地开发和测试。
import sqlite3
# 连接数据库（文件不存在则自动创建）
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL,
age INTEGER
)
''')
# 插入数据
cursor.execute(
'INSERT INTO users (username, email, age) VALUES (?, ?, ?)',
('zhangsan', 'zhangsan@example.com', 28)
)
conn.commit()
# 查询数据
cursor.execute('SELECT * FROM users WHERE age > ?', (20,))
rows = cursor.fetchall()
for row in rows:
print(row)
# 使用上下文管理器自动提交/回滚
with conn:
conn.execute(
'UPDATE users SET age = ? WHERE username = ?',
(30, 'zhangsan')
)
# 关闭连接
conn.close()
7.2 PyMySQL连接MySQL
import pymysql
# 建立连接
connection = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='your_password',
database='web_dev',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor # 返回字典格式结果
)
try:
with connection.cursor() as cursor:
# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
''')
# 插入数据（参数化查询防SQL注入）
sql = 'INSERT INTO articles (title, content) VALUES (%s, %s)'
cursor.execute(sql, ('Python入门', 'Python是一种...'))
connection.commit()
# 查询数据
cursor.execute('SELECT * FROM articles ORDER BY created_at DESC')
articles = cursor.fetchall()
for article in articles:
print(article['title'], article['created_at'])
finally:
connection.close()
7.3 psycopg2连接PostgreSQL
import psycopg2
from psycopg2 import sql, extras
# 建立连接
conn = psycopg2.connect(
host='localhost',
port=5432,
dbname='web_dev',
user='postgres',
password='your_password'
)
try:
cursor = conn.cursor()
# 创建表（PostgreSQL支持丰富的数据类型）
cursor.execute('''
CREATE TABLE IF NOT EXISTS articles (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT,
tags TEXT[],
metadata JSONB,
created_at TIMESTAMPTZ DEFAULT NOW()
)
''')
# 插入数据
cursor.execute(
'INSERT INTO articles (title, content, tags) VALUES (%s, %s, %s)',
('PostgreSQL教程', 'PostgreSQL是...', ['数据库', 'SQL', '后端'])
)
conn.commit()
# 查询JSONB字段
cursor.execute("""
SELECT * FROM articles
WHERE metadata->>'author' = '张三'
ORDER BY created_at DESC
""")
articles = cursor.fetchall()
finally:
cursor.close()
conn.close()
7.4 连接池管理
在生产环境中，频繁创建和关闭数据库连接开销巨大。连接池可以复用连接，显著提升性能。
from dbutils.pooled_db import PooledDB
import pymysql
# 创建连接池（应用启动时初始化一次）
pool = PooledDB(
creator=pymysql,
maxconnections=20, # 最大连接数
mincached=5, # 最小空闲连接数
maxcached=10, # 最大空闲连接数
blocking=True, # 无可用连接时阻塞等待
host='localhost',
port=3306,
user='root',
password='your_password',
database='web_dev',
charset='utf8mb4'
)
# 从连接池获取连接（用完自动归还）
def query_users(page=1, page_size=20):
conn = pool.connection()
try:
with conn.cursor() as cursor:
offset = (page - 1) * page_size
cursor.execute(
'SELECT * FROM users LIMIT %s OFFSET %s',
(page_size, offset)
)
return cursor.fetchall()
finally:
conn.close() # 归还到连接池，而非真正关闭
八、核心要点总结
1. 关系模型基础：表、行、列、主键、外键是核心概念，理解它们之间的关系是数据库学习的起点。
2. SQL分类记忆：DDL定义结构、DML操作数据、DCL控制权限、TCL管理事务。日常开发中DML使用频率最高。
3. JOIN是灵魂：熟练掌握INNER JOIN和LEFT JOIN，理解它们的结果集语义，能够应对90%以上的多表查询场景。
4. 规范化与反规范化平衡：设计时先按3NF规范设计，遇到性能瓶颈时有策略地反规范化。
5. 索引不是越多越好：索引加速查询但拖慢写入。为高频查询字段创建索引，定期使用EXPLAIN分析慢查询。
6. 参数化查询防注入：永远不要使用字符串拼接SQL，使用参数化查询（?、%s）防止SQL注入攻击。
7. 连接池提升性能：生产环境务必使用连接池，避免频繁创建/销毁连接的开销。
九、进一步思考
NoSQL与关系数据库的选择：关系数据库适合结构化数据、事务一致性要求高的场景。对于大规模非结构化数据、高并发写入、灵活模式的场景，可以结合NoSQL（如MongoDB、Redis）使用，形成混合架构。
ORM框架的利弊：Python中SQLAlchemy、Django ORM等框架可以大幅提升开发效率，但抽象层可能产生低效的SQL。建议在ORM之外，储备直接编写SQL的能力，以便在性能调优时能够深入底层。
数据库扩展策略：当单库无法满足性能需求时，可考虑读写分离（主库写入、从库读取）、分库分表（水平拆分）、引入缓存层（Redis）等扩展策略。