MySQL约束和数据库设计本文将系统地介绍MySQL的约束类型（主键、非空、唯一、外键约束）以及数据库设计的核心概念。从

在数据库开发中，约束和数据库设计是确保数据完整性和系统性能的关键要素。MySQL约束通过限制数据的输入和修改，保证数据的准确性和一致性；而良好的数据库设计则能够减少数据冗余、提高查询效率，为应用系统提供可靠的数据基础。

本文将系统地介绍MySQL的约束类型（主键、非空、唯一、外键约束）以及数据库设计的核心概念。从基础的DQL查询开始，逐步深入到数据库设计范式和多表关系的处理。无论你是数据库初学者，还是想要提升数据库设计能力的开发者，都能在本文中找到有价值的内容。

建议读者在学习本文时，结合实际案例进行练习，特别是在理解数据库设计范式和多表关系时，动手实践是加深理解的最好方式。

基础查询（DQL）

排序查询

-- 语法：ORDER BY 子句
-- ORDER BY 排序字段1 排序方式1, 排序字段2 排序方式2...

-- 排序方式：
-- ASC：升序（默认）
-- DESC：降序

-- 注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件
SELECT * FROM student ORDER BY math DESC, english ASC;

聚合函数

将一列数据作为一个整体，进行纵向计算。

-- 计算个数
SELECT COUNT(id) FROM student;  -- 一般选择非空的列：主键
SELECT COUNT(*) FROM student;   -- 计算所有行数

-- 计算最大值
SELECT MAX(math) FROM student;

-- 计算最小值
SELECT MIN(math) FROM student;

-- 计算和
SELECT SUM(math) FROM student;

-- 计算平均值
SELECT AVG(math) FROM student;

注意：聚合函数的计算会排除NULL值。解决方案：

选择不包含NULL的列进行计算
使用IFNULL函数处理NULL值

分组查询

-- 语法：GROUP BY 分组字段

-- 按照性别分组，查询男女同学的平均分
SELECT sex, AVG(math) FROM student GROUP BY sex;

-- 按照性别分组，查询男女同学的平均分和人数
SELECT sex, AVG(math), COUNT(id) FROM student GROUP BY sex;

-- 分数低于70分的不参与分组
SELECT sex, AVG(math), COUNT(id) 
FROM student 
WHERE math > 70 
GROUP BY sex;

-- 分组后人数大于2的才显示
SELECT sex, AVG(math), COUNT(id) as 人数
FROM student 
WHERE math > 70 
GROUP BY sex 
HAVING 人数 > 2;

注意：

分组之后查询的字段：分组字段、聚合函数
WHERE和HAVING的区别：
- WHERE在分组之前进行限定，不满足条件的不参与分组
- HAVING在分组之后进行限定，不满足条件的不会被查询出来
- WHERE后不可以跟聚合函数，HAVING可以进行聚合函数的判断

分页查询

-- 语法：LIMIT 开始的索引,每页查询的条数
-- 公式：开始的索引 = (当前页码 - 1) * 每页显示的条数

-- 每页显示3条记录
SELECT * FROM student LIMIT 0,3;  -- 第1页
SELECT * FROM student LIMIT 3,3;  -- 第2页
SELECT * FROM student LIMIT 6,3;  -- 第3页

注意：LIMIT是MySQL的特有语法。

约束（Constraints）

约束是对表中的数据进行限定，保证数据的正确性、有效性和完整性。

约束类型

主键约束：PRIMARY KEY
非空约束：NOT NULL
唯一约束：UNIQUE
外键约束：FOREIGN KEY

非空约束（NOT NULL）

-- 创建表时添加非空约束
CREATE TABLE stu(
    id INT,
    name VARCHAR(20) NOT NULL  -- name不能为空
);

-- 创建表后添加非空约束
ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL;

-- 删除非空约束
ALTER TABLE stu MODIFY name VARCHAR(20);

唯一约束（UNIQUE）

-- 创建表时添加唯一约束
CREATE TABLE stu(
    id INT,
    phone_number VARCHAR(20) UNIQUE  -- 手机号不能重复
);

-- 创建表后添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

-- 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;

注意：唯一约束可以有NULL值，但只能有一条记录为NULL。

主键约束（PRIMARY KEY）

-- 创建表时添加主键约束
CREATE TABLE stu(
    id INT PRIMARY KEY,  -- 给id添加主键约束
    name VARCHAR(20)
);

-- 创建表后添加主键约束
ALTER TABLE stu MODIFY id INT PRIMARY KEY;

-- 删除主键约束
ALTER TABLE stu DROP PRIMARY KEY;

-- 自动增长
CREATE TABLE stu(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键自增
    name VARCHAR(20)
);

-- 删除自动增长
ALTER TABLE stu MODIFY id INT;

-- 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

注意：

主键必须非空且唯一
一张表只能有一个字段为主键
主键就是表中记录的唯一标识

外键约束（FOREIGN KEY）

-- 创建表时添加外键
CREATE TABLE 表名(
    ...
    外键列
    CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)
);

-- 创建表后添加外键
ALTER TABLE 表名 
ADD CONSTRAINT 外键名称 
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

-- 添加级联操作
ALTER TABLE 表名 
ADD CONSTRAINT 外键名称 
FOREIGN KEY (外键字段名称) 
REFERENCES 主表名称(主表列名称) 
ON UPDATE CASCADE 
ON DELETE CASCADE;

级联操作类型：

级联更新：ON UPDATE CASCADE
级联删除：ON DELETE CASCADE

数据库设计

多表关系

一对一关系

示例：人和身份证
实现：在任意一方添加唯一外键，指向另一方的主键

一对多关系

示例：部门和员工
实现：在多的一方建立外键，指向一的一方的主键

多对多关系

示例：学生和课程
实现：需要借助中间表，中间表至少包含两个外键，分别指向两张表的主键

实际案例

-- 旅游线路分类表
CREATE TABLE tab_category (
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(100) NOT NULL UNIQUE
);

-- 旅游线路表
CREATE TABLE tab_route(
    rid INT PRIMARY KEY AUTO_INCREMENT,
    rname VARCHAR(100) NOT NULL UNIQUE,
    price DOUBLE,
    rdate DATE,
    cid INT,
    FOREIGN KEY (cid) REFERENCES tab_category(cid)
);

-- 用户表
CREATE TABLE tab_user (
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(30) NOT NULL,
    name VARCHAR(100),
    birthday DATE,
    sex CHAR(1) DEFAULT '男',
    telephone VARCHAR(11),
    email VARCHAR(100)
);

-- 收藏表（多对多关系）
CREATE TABLE tab_favorite (
    rid INT,           -- 线路id
    date DATETIME,     -- 收藏时间
    uid INT,           -- 用户id
    PRIMARY KEY(rid,uid),  -- 联合主键
    FOREIGN KEY (rid) REFERENCES tab_route(rid),
    FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

数据库设计范式

第一范式（1NF）：原子性

定义：表中的每一列都必须是不可再分的基本数据项，即列中不能包含多个值，也不能包含集合或重复的组。

目的：确保每个字段只存储单一值，方便查询和索引。

示例

不规范：学生（学号，姓名，出生日期(年,月,日)）
这里“出生日期”列实际上包含了三个独立信息（年、月、日），违反了原子性。
规范：学生（学号，姓名，出生年，出生月，出生日）
将出生日期拆分为三个独立列，或用一个 date 类型列存储完整的日期（date 类型在数据库中属于原子值）。

注意：

拆分字段并不等于必须将日期拆成年、月、日三列，只要用数据库支持的原子类型（如 DATE、TIMESTAMP）存储即可。
真正的反例是：在一列中存储逗号分隔的标签、多个电话号码等。

违反后果：

查询某个特定值困难（如查找所有3月出生的学生，无法高效索引）。
更新时容易产生不一致（如只修改了月却没改日）。

第二范式（2NF）：消除部分依赖

前提：表必须满足 1NF，且表的主键是复合主键（由多列组成）。
定义：在 1NF 基础上，要求所有非主键列都完全依赖于整个主键，不能仅依赖于主键的一部分。

目的：避免因部分依赖导致的数据冗余和异常。

示例

不规范：选课（学号，课程号，姓名，学分）
主键是 (学号, 课程号)。
- “姓名”只依赖于“学号”（部分依赖）
- “学分”只依赖于“课程号”（部分依赖）
  因此不符合 2NF。
规范：拆分为三张表
- 学生（学号，姓名）
- 课程（课程号，学分）
- 选课（学号，课程号）

违反后果：

数据冗余：一个学生选了多门课，姓名重复存储多次。
更新异常：学生改名时，需要修改所有选课记录，可能遗漏。
删除异常：如果某课程暂时无人选，其学分信息无法录入（因为学分字段在选课表中，需要课程号存在）。

实际应用：在 ORM 设计中，2NF 自然地引导我们使用中间表来表示多对多关系，并将实体的独立属性放在各自的表中。

第三范式（3NF）：消除传递依赖

前提：表必须满足 2NF。
定义：在 2NF 基础上，要求所有非主键列之间不能存在传递依赖。即：如果 A → B 且 B → C，则 C 不能直接依赖于 A，应通过 B 间接依赖，需将 B 和 C 拆分到独立表中。

目的：避免因传递依赖造成的冗余和更新异常。

示例

不规范：学生（学号，姓名，年龄，学院名称，学院电话）
主键为“学号”。
“学院电话”依赖于“学院名称”，而“学院名称”依赖于“学号” → 存在传递依赖 学号 → 学院名称 → 学院电话。
规范：拆分为
- 学生（学号，姓名，年龄，学院编号）
- 学院（学院编号，学院名称，学院电话）

违反后果：

数据冗余：同一学院的所有学生都重复存储学院电话。
更新异常：学院电话变更时，需要更新所有该学院学生的记录，极易出错。
插入异常：如果某学院还没有学生，无法在学生表中录入学院电话。

实际应用：3NF 是大多数 OLTP 系统设计的常见目标。例如订单表中通常不直接存“用户姓名”，而是存“用户 ID”，通过关联用户表获取姓名。

范式总结与权衡

范式	核心要求	主要解决的问题
1NF	列不可再分	消除重复组，便于查询
2NF	消除部分依赖	解决复合主键下的冗余
3NF	消除传递依赖	解决非主键间的冗余

在实际开发中：

通常遵循 3NF 设计表结构，能有效避免大多数异常。
但在性能优先的场景（如大数据量报表、频繁连表查询）下，可以反范式化（适当冗余字段）来减少 JOIN 开销。例如订单表同时冗余“用户昵称”快照，防止用户改名后历史订单显示错误。
反范式化是有代价的：需要应用层维护冗余字段的一致性。因此需根据业务场景权衡。

数据库维护

数据库备份与还原

命令行方式

# 备份
mysqldump -u用户名 -p密码 数据库名称 > 保存的路径

# 还原
# 1. 登录数据库
# 2. 创建数据库
# 3. 使用数据库
# 4. 执行文件
source 文件路径

Docker容器方式

# 从Docker容器中备份数据库
docker exec mysql容器名称 mysqldump -u用户名 -p密码 数据库名称 > 宿主机保存路径

# 示例：备份test数据库
docker exec mysql8 mysqldump -uroot -ppassword test > /home/backup/test.sql

# 将备份文件还原到Docker容器中的MySQL
# 1. 将备份文件复制到容器内
docker cp 备份文件路径 mysql容器名称:/tmp/

# 2. 在容器中执行还原命令
docker exec -i mysql容器名称 mysql -u用户名 -p密码 数据库名称 < 备份文件路径

# 示例：还原test数据库
docker exec -i mysql8 mysql -uroot -ppassword test < /home/backup/test.sql

注意事项：

确保有足够的磁盘空间存储备份文件
备份前建议停止对数据库的写入操作
还原时如果数据库不存在需要先创建
使用docker cp命令时注意文件权限问题