MySQL表的设计（三大范式，表的关系，ER图）

什么是主键？外键？1.主键：主键是表的一个特殊字段，可以唯一地标识表中的每条信息。比如：身份证号。一个表中可以有一个或多个主键。　　　作用：唯一标识、快速查找　　　2.外键：如果表B的一个字段（外键）依赖于表A的主键。则成表A为父表，表B为子表。　　　原则：外键必须依赖于数据库中已经存在的父表的主键，外键可以为空　　　作用：建立该表与父表的关联关系使用外键了解背景创建三个表：分别为借阅者、书籍信息

bruin_du

5519人浏览 · 2022-05-02 13:43:08

bruin_du · 2022-05-02 13:43:08 发布

1️⃣表的三大范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则，满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

1.第一范式(确保每列保持原子性)

第一范式是最基本的范式,满足第一范式需要做到以下两点：

每一列属性都是不可再分的属性值，确保每一列的原子性
两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，以下举例分析。

字段值还可以继续拆分的，就不满足第一范式，如下：
在这里插入图片描述
下面这个，更加贴合第一范式：

2.第二范式（属性完全依赖于主键）

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

例如：创建一个订单表

-- 订单表
CREATE TABLE myorder (
    product_id INT,
    customer_id INT,
    product_name VARCHAR(20),
    customer_name VARCHAR(20),
    PRIMARY KEY (product_id, customer_id)
);

在这张订单表中，product_name 只依赖于 product_id ，customer_name 只依赖于 customer_id 。也就是说，product_name 和 customer_id 是没有关系的，customer_name 和 product_id 也是没有关系的。
这就不满足第二范式。

下面将主表拆分：

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

拆分之后，myorder 表中的 product_id 和 customer_id 完全依赖于 order_id 主键，而 product 和 customer 表中的其他字段又完全依赖于主键。
满足了第二范式的设计。

3.第三范式（属性不依赖于其它非主属性，属性直接依赖于主键）

数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。
如下，我们在myorder 中添加customer_phone

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    customer_phone VARCHAR(15)
);

发现，表中的 customer_phone 有可能依赖于 order_id 、 customer_id 两列，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。
我们应该这样设计：

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    phone VARCHAR(15)
);

修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！

总结：三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

2️⃣表的关系

在关系型数据库中，基于三大范式设计表结构，使得不同的表之间产生不同的关系。
不同的表之间有三种关系 —— 一对一、一对多、多对多

1.一对一

什么样的关系属于一对一？
比如：一个人只能有一个身份证号码，一个身份证号码只能属于一个人

2.一对多

比如：一个班级有多个学生

3.多对多

例如：一个课程可以被多个学生选择，一个学生可以选择多个课程

3️⃣主键和外键

什么是主键？外键？

1.主键：主键是表的一个特殊字段，可以唯一地标识表中的每条信息。比如：身份证号。一个表中可以有一个或多个主键。
　　　作用：唯一标识、快速查找
　　　
2.外键：如果表B的一个字段（外键）依赖于表A的主键。则成表A为父表，表B为子表。
　　　原则：外键必须依赖于数据库中已经存在的父表的主键，外键可以为空
　　　作用：建立该表与父表的关联关系

使用外键

了解背景

创建三个表：分别为借阅者、书籍信息和借阅记录。

CREATE TABLE `books` (
  `bid` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL COMMENT '书籍名称',
  `count` INT NOT NULL COMMENT '存量',
  `total` INT NOT NULL COMMENT '总量',
  PRIMARY KEY (`bid`))
COMMENT = '书籍信息';

CREATE TABLE `readers` (
  `rid` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`rid`))
COMMENT = '借阅者信息';

CREATE TABLE `records` (
  `reid` INT NOT NULL AUTO_INCREMENT,
  `rid` INT NOT NULL COMMENT '谁借的',
  `bid` INT NOT NULL COMMENT '借的哪本书',
  `borrowed_at` DATETIME NOT NULL DEFAULT current_timestamp COMMENT '借阅时间',
  PRIMARY KEY (`reid`))
COMMENT = '借阅记录';

借阅记录的产生的前提是有借阅者和书籍，如果不使用外键的情况，可以随意在借阅记录表中插入数据。但是如果在借阅记录表中插入外键，有了外键的约束后，强行将书籍表和借阅者表产生关联后，借阅记录表变为了子表，其他两个表就变为了父表。

此时就不能在借阅表中随便添加数据，只有当那两个表中存在数据后，才能生成对应的记录信息，这就是外键存在的意义。

创建外键的界面
在这里插入图片描述
注意
1.表位空或者表中的数据能对上的时候才能创建外键
2.要求外键和关联表的字段类型一致
3.关联的表的字段得是主键

插入数据

-- 先添加借阅者和书籍信息
insert into books (name, count, total) values ('MySQL', 10, 10);
insert into readers (name) values ('小红');
-- 再添加借阅记录
insert into records (rid, bid) values (1, 1);