MySQL数据库—多表设计（有这一篇够！）

▐ 数据库设计范式

• 第一范式：确保每列保持原子性 ( 列不可再分解 )

例如联系方式包括：电话/邮箱/微信... 那么我们设计表时就需要将它具体化

• 第二范式：要有主键，通过主键可以精确的定位到某行数据.

其他字段都依赖于主键列，没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录

• 第三范式：确保每列都和主键列直接相关

不同的信息放在不同表中，如果两张表有关系的话，只需要在一张表中放置另一张表的主键，再进行关联即可，关联表中的其它非主键信息不需要了。

如图，我们在订单表中关联了商品表，那么只需在订单表中添加商品表的主键(商品编号)即可，关联表(商品表)中的非主键信息(商品名称,单价)就不需要了

▐ 多对一关系表设计

• 经典案例：学生---专业表

• 一个学生对应一个专业，但一个专业可以对应多个学生

表与表关系为：多对一关系也可以称为一对多

创建一个学生表并添加信息

-- 创建一个学生表
CREATE TABLE student(
num INT PRIMARY KEY AUTO_INCREMENT,-- 学号 -主键
NAME VARCHAR(10) NOT NULL,         -- 名字    
gender CHAR(1) NOT NULL           -- 性别    
) 
-- 插入数据
INSERT INTO student(NAME,gender)
VALUE
('周杰伦','男'),
('许嵩','男'),
('孙燕姿','女'),
('张韶涵','女'),
('赵雷','男'),
('邓紫棋','女')

创建一个专业表并添加专业

-- 创建一个专业表
CREATE TABLE major(
 id INT PRIMARY KEY AUTO_INCREMENT,-- 主键
 NAME VARCHAR(20) NOT NULL         -- 专业名称
 )
-- 添加专业
 INSERT INTO major(NAME)
 VALUE
('计算机'),
('电子信息'),
('土木工程'),
('生物科学'),
('应用数学')

接下来我们在学生表中添加一个外键列

 -- 添加列
ALTER TABLE student ADD majorid INT

majorid 称为外键，外键必须是与另一个表的主键关联,，且数据类型一致
通过学生表中的majorid列，把学生与专业关联起来，在学生表中只需要添加专业编号即可,
其他信息不需要添加到学生表中

注意：

仅仅添加外键是不够的，还需要添加外键约束

为什么要添加约束？

如果没有添加约束，数据与数据之间没有任何束缚，可以随意操作，例如删除专业，但是专业还有学生与他关联，此种场景删除专业就不可行了，为外键添加约束后，操作时就不能随便操作，要保证数据的完整性。

所以接下来还需添加外键约束

ALTER TABLE student ADD CONSTRAINT fk_student_major_on_majorid FOREIGN KEY(majorid) REFERENCES major(id)

加上约束之后，由于计算机专业有学生关联，所以就不能删除 （没有关联的可以删除）

▐ 多对多关系表设计

• 经典案例：学生---选课表

• 由于一个学生可以选择多个课程，所以在学生表中添加课程外键就行不通了。

• 需要再设计一个学生选课表(只要存储学生和课程的编号(主键)即可), 一般把这种表也称为关系表

再新创建一个课程表并添加课程

CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
)
 INSERT INTO course(NAME)
 VALUE
('java'),
('数据库'),
('web前端'),
('数据结构'),
('操作系统')

创造一个学生_课程表，分别存储学生表和课程的主键表

CREATE TABLE student_course(
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_num INT,
  courseid  INT,
  CONSTRAINT fk_student_student_course_on_student_num FOREIGN KEY(student_num) REFERENCES student(num),
  CONSTRAINT fk_course_student_course_on_courseid FOREIGN KEY(courseid) REFERENCES course(id)
)