在数据库开发中，约束和数据库设计是确保数据完整性和系统性能的关键要素。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值。解决方案：

1. 选择不包含NULL的列进行计算
2. 使用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;

注意：

1. 分组之后查询的字段：分组字段、聚合函数
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）

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

约束类型

1. 主键约束：PRIMARY KEY
2. 非空约束：NOT NULL
3. 唯一约束：UNIQUE
4. 外键约束：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;

注意：

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

外键约束（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;

级联操作类型：

1. 级联更新：ON UPDATE CASCADE
2. 级联删除：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）

要求属性具有原子性，不可再分解。

示例：

• 不规范：学生（学号，姓名，出生日期(年,月,日)）
• 规范：学生（学号，姓名，出生年，出生月，出生日）

第二范式（2NF）

在1NF的基础上，消除非主键列对主键的部分依赖。

示例：

• 不规范：选课（学号，课程号，姓名，学分）
• 规范：
  • 学生（学号，姓名）
  • 课程（课程号，学分）
  • 选课（学号，课程号）

第三范式（3NF）

在2NF的基础上，消除传递依赖。

示例：

• 不规范：学生（学号，姓名，年龄，学院名称，学院电话）
• 规范：
  • 学生（学号，姓名，年龄，学院编号）
  • 学院（学院编号，学院名称，学院电话）

数据库维护

数据库备份与还原

命令行方式

复制

# 备份
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

注意事项：

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