首页 > 编程学习 > MySQL的多表间关系

MySQL的多表间关系

发布时间:2022/11/6 10:40:26

一、数据库三范式

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);

3.3 一对一关系

3.3.1 两种一对一关系

  • 从表的一行数据只能对应主表的一行数据。
  • A表中的一行数据对应B表中的一行数据,反之B表中的一行数据也对应A表中的一行数据。

3.3.2 建表原则

在从表中指定一个字段创建外键并指向主表的主键,然后给从表的外键字段添加唯一约束。

Copyright © 2010-2022 dgrt.cn 版权所有 |关于我们| 联系方式