软件测试/测试开发丨SQL多表关系 学习笔记

本文转自测试人社区，霍格沃兹测试开发学社学员笔记
原文链接：https://ceshiren.com/t/topic/26865

一、多表简介

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、一对一

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

推荐

Python全栈开发与自动化测试开发班

由浅入深实战进阶，从小白到高手

以Python全栈开发为基础，深入教授自动化测试技能，为学员打造全面的技术能力。通过系统学习和实际项目实战，学员将具备在职场中脱颖而出的竞争力。不仅能够灵活运用Python进行开发，还能够保障项目质量通过自动化测试手段。这是一个全面提升职业竞争力的机会。

课程详情

Python开发必备基础技能与项目实战

Pvthon 编程语言/算法和数据结构/面向对象编程Web后端开发/前端开发/测试管理平台项目实战

人工智能ChatGPT实战

人工智能辅助学习各种开发和测试技能/Pytorch深度学框架/平台开发实战

数据分析与自动化办公

数据采集/Pandas与数据处理技术/ECharts与数据可视化技术/爬虫实战/自动化办公/批量文件处理

UI自动化测试与高级项目实战

Web自动化测试/App自动化测试/ PageObject设计模式

接口自动化测试

接口协议分析/Mock实战/服务端接口测试

性能测试

性能测试流程与方法/JMeter 脚本参数化/Grafana监控系统搭建

简历指导与模拟面试

1V1简历指导/模拟真实面试/测试开发岗面试全攻略名企私教服务
名企专家1v1辅导/行业专家技术指导/针对性解决工作难题/绩效提升辅导与晋升复盘

课程亮点

名企私教服务  先学习后付费 高额奖学金

专属社群+晚自习在线答疑

5V1全方位辅导作业+考试强化学习效果

简历修改 模拟面试 就业内推 面试复盘