多表关系简介

学习笔记
1

一、多表简介

1.1、多表及使用场景介绍

  • 多表就是数据库在设计时使用多张表来实现数存储的要求
  • 在实际项目开发中，数据量大而且纷杂，需要分库分表
  • 分表：按照一定的规则，对原有的数据库和表进行拆分
  • 表与表之间可以通过外键建立链接

1.2、多表设计案例

  • 假定我们现在需要创建一张员工信息表，包含字段：
    • eid 员工ID (自增主键)
    • ename 员工姓名
    • age 年龄
    • gender 性别
    • dept_name 所在部门
    • dept_id 部门ID
    • dept_manager 部门主管
    • dept_location 所在地点

多表设计模式

  • 将数据拆分为员工信息表employee和部门信息表dept
  • 两个表之间通过部门id：dept_id字段连接
# 创建员工信息表
CREATE TABLE staff_info (
	staff_id INT PRIMARY KEY AUTO_INCREMENT,
	staff_name VARCHAR (10),
	staff_age INT,
	gender CHAR (5),
	dept_id INT
);

# 创建部门表
CREATE TABLE dept (
	dept_id INT PRIMARY KEY AUTO_INCREMENT,
	dept_name VARCHAR (20),
	dept_manager VARCHAR (20),
	dept_location VARCHAR (20)
);

INSERT INTO dept(dept_name,dept_manager,dept_location) VALUES('研发部','张无忌','北京');
INSERT INTO dept(dept_name,dept_manager,dept_location) VALUES('运营部','赵敏','深圳');

INSERT INTO staff_info(staff_name,age,gender,dept_id) VALUES ('李四', 25, '男',1);
INSERT INTO staff_info(staff_name,age,gender,dept_id) VALUES ('宋江', 40, '男',1);
INSERT INTO staff_info(staff_name,age,gender,dept_id) VALUES ('张三', 20, '男',1);
INSERT INTO staff_info(staff_name,age,gender,dept_id) VALUES ('林冲', 25, '男',1);
INSERT INTO staff_info(staff_name,age,gender,dept_id) VALUES ('林徽因', 25,'女',1);
INSERT INTO staff_info(staff_name,age,gender,dept_id) VALUES ('周芷若', 25,'女',2);
INSERT INTO staff_info(staff_name,age,gender,dept_id) VALUES ('任盈盈', 25, '女',2);

1.3、使用多表的优点

  1. 简化数据
  2. 提高复用性
  3. 方便权限控制
  4. 提高系统的稳定性和负载能力

二、外键约束

2.1、外键约束的定义与意义

主键：可以唯一标识一条记录的列
外键：从表中与主表的主键对应的字段

主表：外键所指向的表,约束其他表的表
从表：外键所在的表，被约束的表
价值：建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性

2.2、建立外键约束

  • 创建表时添加外键约束：

CONSTRAINT [外键约束的名称] FOREIGN KEY (外键字段) REFERENCES [主表名称(主键字段)]

  • 添加外键约束：

ALTER TABLE [从表] ADD CONSTRAINT [外键约束的名称] FOREIGN KEY [外键字段] REFERENCES [主表名称(主键字段)]

2.3、删除外键约束

  • 语法：
    • ALTER TABLE [表名] DROP FOREIGN KEY [外键约束名称]
  • 注意事项
    • 从表外键数据类型必须与主表的主键一致
    • 删除数据时，需先删除从表数据再删除主表的数据
    • 添加数据时先添加主表数据，再添加从表数据
  • 删除主表数据的同时,也删除掉从表中相关的数据
    • ON DELETE CASCADE ```sql

创建员工信息表并添加级联删除的外键约束

emp_id INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT ,
gender VARCHAR(10), dept_id INT, – 添加外键约束 CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id) – 设置允许级联删除 ON DELETE CASCADE ); # 向员工信息表中添加一条数据 INSERT INTO emp_part VALUES(1,‘cindy’,20,‘female’,‘2’) #删除主表中部门id=2的部门 DELETE FROM dept WHERE id=2 # 查看从表中的数据是否同时被删除 SELECT * FROM emp_part

三、多表关系

3.1、一对多

  • 定义： 主表的一条记录可以对应从表的多条记录
  • 例子： 部门表，员工表
  • 建表原则：在一对多关系中，多的表定位从表，设置外键指向主表

3.2、多对多

  • 定义：主表的多条记录可以对应从表的多条记录
  • 例子：商品信息表，客户表，订单表
  • 建表原则：需要创建第三张表作为中间表，中间表需要包含两张表的主键。

3.3、一对一

  • 定义：从表的一条记录对应主表的一条记录
  • 例子：员工信息表与身份证表,联系方式
  • 建表原则： 这种对应关系的数据，通常放在单表里