MySQL的多表间关系

程序员丶星霖

已于 2022-11-15 14:38:14 修改

阅读量295

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 java

于 2022-11-06 08:45:00 首次发布

本文链接：https://blog.csdn.net/HunterArley/article/details/127682463

版权

MySQL 专栏收录该内容

14 篇文章 1 订阅

订阅专栏

一、数据库三范式

1.1 第一范式：确保每列保持原子性

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

如果不遵守第一范式，查询出数据还需要进一步处理（查询不方便）。遵守第一范式，需要什么字段的数据就查询什么数据（方便查询）。

1.2 第二范式：确保表中的每列都和主键相关

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关。

也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存到同一张数据库表中。

1.3 第三范式：确保每列都和主键列直接相关，而不是间接相关

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

二、外键约束

2.1 外键约束的概念

在遵循三范式的前提下，很多时候需要进行拆表，将数据分别存放多张表中，以减少冗余数据。但是拆分出来的表与表之间是有着关联关系的，必须得通过一种约束来约定表与表之间的关系，这种约束就是外键约束。

2.2 外键约束的作用

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个表的字段之间的参照关系。

2.3 创建外键约束的语法

2.3.1 在建表时指定外键约束

-- 外键
-- 1. 创建部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	dept_name VARCHAR(50),
	dept_location VARCHAR(50)
);

-- 2. 创建员工表，并且在员工表中添加一个外键指向部门表的id主键
CREATE TABLE emp(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(50) NOT NULL,
	sex VARCHAR(10),
	dept_id INT,
	CONSTRAINT fkey_01 FOREIGN KEY (dept_id) REFERENCES dept (id) -- 在建表时指定外键约束
);

2.3.2 在建表后指定外键约束

-- 在创建表之后添加外键
alter table emp add constraint 	fkey_01 foreign key (dept_id) references dept (id);

2.4 删除外键约束的语法

-- 删除外键
ALTER TABLE emp DROP FOREIGN KEY fkey_01;

2.5 外键约束的要求

在从表上建立外键，并且主表要先存在；
一个表可以建立多个外键约束；
通常情况下，从表的外键列一定要指向主表的主键列；
从表的外键列与主表被参照的列名可以不相同，但数据类型必须相同。

2.6 外键约束等级

Cascade方式：在主表上update/delete记录时，同步update/delete掉从表的匹配记录
Set null方式：在主表上update/delete记录时，将从表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
No action方式：如果子表中有匹配的记录，则不允许对主表对应候选键进行update/delete操作
Restrict方式：同no action，都是立即检查外键约束
Set default方式：主表有变更时，子表将外键列设置成一个默认的值，但是InnoDB不能识别

如果没有指定等级，就相当于Restrict方式。

三、多表间关系

3.1 一对多关系

3.1.1 概念

一对多的关系是指主表的一行数据可以同时对应从表的多行数据，反过来就是从表的多行数据指向主表的同一行数据。

3.1.2 应用场景

分类表和商品表、班级表和学生表、用户表和订单表。

3.1.3 建表原则

将一的一方作为主表，多的一方作为从表，在从表中指定一个字段作为外键，指向主表的主键。

-- 2. 一对多的表关系
-- 建表原则：以一的一方作为主表，多的一方作为从表，在从表中指定一个字段作为外键指向主表的主键
CREATE TABLE category(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(50)
);

CREATE TABLE product1(
	pid INT PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(50),
	price DOUBLE,
	cid INT
);

3.2 多对多关系

3.2.1 概念

两张表都是多的一方，A表的一行数据可以同时对应B表的多行数据，反之B表的一行数据也可以同时对应A表的多行数据

3.2.2 应用场景

订单表和商品表、学生表和课程表

3.2.3 建表原则

新建一张中间表，在中间表中定义两个字段，这两个字段分别作为外键指向两张表各自的主键。

-- 3. 多对多的表关系
-- 建表原则：两张表都是多的一方，要新创建一张中间表，在中间表中指定俩字段分别作为外键指向两张主表的主键
CREATE TABLE student1(
	sid INT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(50)
);

CREATE TABLE course(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(20)
);

CREATE TABLE t_s_c(
	sno INT,
	cno INT
);


ALTER TABLE t_s_c ADD CONSTRAINT fkey01 FOREIGN KEY (sno) REFERENCES student1 (sid);
ALTER TABLE t_s_c ADD CONSTRAINT fkey03 FOREIGN KEY (cno) REFERENCES course (cid);