当前位置：首页 > backend >正文

【MySQL】(7) 数据库设计

backend 2025/7/3 18:04:15

一、范式

1、范式的简介

范式就是数据库设计的规范，满足的范式越多，数据库冗余越小。范式一共有六种，但在实际开发中满足：第一、第二、第三范式即可，因为满足过多的范式，细分的表越多，查询时需要连接的表越多，导致查询效率降低，数据库设计的难度也加大。实际上根据业务需求也会反范式化，让经常一起查询的数据冗余存储在同一个表中。

2、第一范式

2.1、定义

数据表的列不可再分，即能用 MySQL 中的基本数据类型定义。满足第一范式是关系型数据库的基本要求。

2.2、反例

2.3、修改

3、第二范式

3.1、定义

在第一范式的基础上，不存在非关键字段对候选键的部分函数依赖。候选键就是可以唯一标识数据行的列（可以选作主键）或多个列（可以选做复合主键，里面的所有列都是候选键）。部分函数依赖（某些非关键字段只依赖部分候选键，与其它候选键无关系）存在于有复合主键的表中。

3.2、反例

记录学生在某门考试中的分数。

该表中，成绩通过学号+课程名确定，主键可以设计为 (学号，课程名) 复合主键，可能不满足第二范式。学生信息：学生姓名、年龄、性别只依赖学号；学分只依赖课程名确定，即存在部分函数依赖，不满足第二范式。

3.3、修改

解决部分函数依赖，给学生、课程实体分别单独建立表，学生、课程之间存在的关系（学生课程成绩）也单独用表进行描述：

3.4、不满足第二范式可能存在的问题

产生大量数据冗余，如案例中的学生信息、课程学分：

更新异常，如给MySQL修改学分，就要修改表中的所有MySQL记录：

插入异常：学分、成绩与学生有关系，意味着只有学生获得成绩后才能添加课程的学分信息。但这不符合常理，一门课在考试前就是存在的。
删除异常：把所有毕业生的成绩删除，若全部学生毕业，课程的信息也跟着被清空了。

4、第三范式

4.1、定义

在第二范式的基础上，不存在非关键字段对任意候选键的传递依赖。

4.2、反例

记录学生信息，包含学生所在的学院信息：

该表中 id 是主键，学号、姓名、年龄、性别与 id 强相关，学院电话、地址与学院强相关，该表中存在两个强相关关系。且这两个强相关关系间存在传递依赖，即可通过 id 找到学生信息，学生信息又包含学院信息。

4.3、修改

把学院信息从学生表中拆分出来，再将学生表与学院表建立关系：

二、设计过程

从业务中抽象出概念类（需求分析），即定义实体（数据库设计），如教务系统中：

定义实体之间的关系，画 E-R 图，便于设计人员与非专业人士沟通。
根据 E-R 图编写 SQL 语句，定义属性的数据类型、约束，创建数据库。

三、实体-关系图

1、三种基本成分

实体（方框）、属性（椭圆）、关系（菱形）。

2、一对一关系

分别创建实体对应的表，再在其中一个表中加关联字段（另一个表的主键或唯一键）。如账号表-用户表：

3、一对多关系

分别创建实体对应的表，再在多方表（从表）中加入对一方表（主表）得关联字段（不能在多方表加，因为加了就是不同班级有一个学生数组，mysql中没有这种数据类型，且不满足第一范式要求）。如一个班级有多个学生：

4、多对多关系

分别创建实体对应的表，再创建一个关系表，建立两个实体表的联系。如一个学生有多门课成绩，一门课有多个学生考试：

四、编写 SQL 语句

1、一对一，用户 -- 账号

drop table if exists account;
# 账号表
create table account (id bigint primary key auto_increment,username varchar(20) not null,password varchar(32) not null
);drop table if exists users;
# 用户表
create table users (id bigint primary key auto_increment,name varchar(20) not null, nickname varchar(20),phone_num varchar(11), email varchar(50),gender tinyint(1),account_id bigint  # 在用户实体中添加对账户实体的关联
);

2、一对多，班级 -- 学生

drop table if exists class;
# 班级表
create table class (id bigint primary key auto_increment,name varchar(20)
);drop table if exists student;
# 学生表
create table student (id bigint primary key auto_increment,name varchar(20) not null, sno varchar(10) not null,age int default 18,gender tinyint(1), enroll_date date,class_id bigint,  # 在多方表建立对一方表的关联foreign key (class_id) references class(id)
);

3、多对多，学生、课程、成绩

# 课程表
drop table if exists course;
create table course (id bigint primary key auto_increment,name varchar(20)
);# 分数表
drop table if exists score;
create table score (id bigint primary key auto_increment,score float,student_id bigint,course_id bigint,foreign key (student_id) references student(id),  # 一个学生有多门课成绩foreign key (course_id) references course(id)  # 一门课有多个学生的成绩
);