Python全栈入门到实战【数据库篇 14】MySQL多表关系与笛卡尔积详解，数据库设计的核心基础

前言

上一篇《数据库篇 13》中，我们已经掌握了MySQL六大约束的使用，学会了设计规范的单表结构，保证了单表数据的完整性和一致性。本篇作为数据库篇的第十四篇，我们将学习实际项目中最核心的数据库设计知识——多表关系。真实的业务系统中，数据之间存在着复杂的关联，不可能都存储在一张表中，我们需要根据业务关系将数据拆分到多张表中，并建立正确的关联关系。同时我们还会学习多表查询的基础——笛卡尔积，理解多表查询的底层原理。

本文为Python全栈开发者与数据库入门者量身打造，详细讲解一对多、多对多、一对一三种最常见的多表关系，每一种关系都有清晰的业务案例、设计原则和可直接运行的SQL代码，同时深入解析笛卡尔积的概念和产生原因，教你如何消除无效的笛卡尔积，即使是完全没有数据库设计经验的同学，也能快速掌握多表设计的核心方法。

本节核心学习内容：

1. 多表关系概述：为什么需要多表设计与三种核心关系
2. 一对多关系：最常用的关系类型与实现方式
3. 多对多关系：中间表的设计原则与实现
4. 一对一关系：单表拆分的最佳实践
5. 多表查询概述：从多张表中查询数据的基本概念
6. 笛卡尔积：底层原理、产生原因与消除方法
7. 完整实战：三种关系的表结构创建与关联查询
8. 常见误区：多表设计的常见错误与最佳实践
9. 核心总结：多表关系速查表，方便开发时快速查阅

一、多表关系概述

1.1 为什么需要多表设计

如果将所有业务数据都存储在一张表中，会出现以下严重问题：

• 数据冗余：大量重复的数据会占用不必要的存储空间
• 数据不一致：修改数据时需要修改多处，容易出现不一致的情况
• 扩展性差：新增业务字段时需要修改表结构，影响所有数据
• 维护困难：表结构过于复杂，难以理解和维护

因此，在进行数据库设计时，我们需要根据业务模块之间的关系，将数据拆分到多张表中，每张表只负责存储一类数据，然后通过外键建立表之间的关联关系。

1.2 多表关系的三种类型

在关系型数据库中，表与表之间的关系基本上分为三种：

• 一对多（多对一）：最常见的关系类型，例如部门与员工、班级与学生
• 多对多：例如学生与课程、用户与角色
• 一对一：相对少见，多用于单表拆分，例如用户与用户详情

二、一对多（多对一）关系

一对多是最常用的多表关系，也是所有多表关系的基础。

2.1 关系说明

• 案例：部门与员工的关系
• 关系描述：一个部门可以对应多个员工，一个员工只能对应一个部门
• 实现方式：在多的一方建立外键，指向一的一方的主键

2.2 表结构设计与实现

-- 创建部门表（一的一方）CREATETABLEdept(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'部门ID',nameVARCHAR(20)NOTNULLUNIQUECOMMENT'部门名称')COMMENT'部门表';-- 创建员工表（多的一方）CREATETABLEemp(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'员工ID',nameVARCHAR(20)NOTNULLCOMMENT'员工姓名',ageINTCOMMENT'年龄',dept_idINTCOMMENT'部门ID',-- 建立外键约束，关联部门表的主键CONSTRAINTfk_emp_deptFOREIGNKEY(dept_id)REFERENCESdept(id))COMMENT'员工表';-- 插入测试数据INSERTINTOdept(name)VALUES('研发部'),('市场部'),('财务部');INSERTINTOemp(name,age,dept_id)VALUES('张无忌',20,1),('杨逍',33,1),('赵敏',18,2),('周芷若',22,2),('张三丰',100,3);

三、多对多关系

多对多关系需要通过第三张中间表来实现，中间表至少包含两个外键，分别关联两张主表的主键。

3.1 关系说明

• 案例：学生与课程的关系
• 关系描述：一个学生可以选修多门课程，一门课程也可以供多个学生选择
• 实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

3.2 表结构设计与实现

-- 创建学生表CREATETABLEstudent(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'学生ID',nameVARCHAR(20)NOTNULLCOMMENT'学生姓名',noVARCHAR(20)NOTNULLUNIQUECOMMENT'学号')COMMENT'学生表';-- 创建课程表CREATETABLEcourse(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'课程ID',nameVARCHAR(20)NOTNULLUNIQUECOMMENT'课程名称')COMMENT'课程表';-- 创建学生课程关系表（中间表）CREATETABLEstudent_course(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'主键ID',studentidINTNOTNULLCOMMENT'学生ID',courseidINTNOTNULLCOMMENT'课程ID',-- 关联学生表CONSTRAINTfk_student_course_studentFOREIGNKEY(studentid)REFERENCESstudent(id),-- 关联课程表CONSTRAINTfk_student_course_courseFOREIGNKEY(courseid)REFERENCEScourse(id),-- 保证学生和课程的组合唯一，避免重复选课UNIQUE(studentid,courseid))COMMENT'学生课程关系表';-- 插入测试数据INSERTINTOstudent(name,no)VALUES('黛绮丝','2000100101'),('谢逊','2000100102'),('殷天正','2000100103'),('韦一笑','2000100104');INSERTINTOcourse(name)VALUES('Java'),('PHP'),('MySQL'),('Hadoop');INSERTINTOstudent_course(studentid,courseid)VALUES(1,1),(1,2),(1,3),(2,1),(2,4);

四、一对一关系

一对一关系多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。

4.1 关系说明

• 案例：用户与用户详情的关系
• 关系描述：一个用户只能对应一条用户详情记录，一条用户详情记录也只能对应一个用户
• 实现方式：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

4.2 表结构设计与实现

-- 创建用户基本信息表CREATETABLEtb_user(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'用户ID',nameVARCHAR(20)NOTNULLCOMMENT'姓名',ageINTCOMMENT'年龄',genderCHAR(1)COMMENT'性别',phoneVARCHAR(11)COMMENT'手机号')COMMENT'用户基本信息表';-- 创建用户教育信息表（详情表）CREATETABLEtb_user_edu(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'主键ID',degreeVARCHAR(20)COMMENT'学历',majorVARCHAR(20)COMMENT'专业',primaryschoolVARCHAR(50)COMMENT'小学',middleschoolVARCHAR(50)COMMENT'中学',universityVARCHAR(50)COMMENT'大学',useridINTUNIQUENOTNULLCOMMENT'用户ID',-- 关联用户表CONSTRAINTfk_user_edu_userFOREIGNKEY(userid)REFERENCEStb_user(id))COMMENT'用户教育信息表';-- 插入测试数据INSERTINTOtb_user(name,age,gender,phone)VALUES('黄渤',45,'1','18800001111'),('冰冰',35,'2','18800002222'),('马云',55,'1','18800008888'),('李彦宏',50,'1','18800009999');INSERTINTOtb_user_edu(degree,major,primaryschool,middleschool,university,userid)VALUES('本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),('硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),('本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),('本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

五、多表查询概述与笛卡尔积

5.1 多表查询概述

多表查询就是指从多张表中查询数据。当我们需要查询的数据分布在多张表中时，就需要使用多表查询。

例如：查询员工的姓名及其所在的部门名称，员工姓名在员工表中，部门名称在部门表中，就需要同时查询这两张表。

5.2 笛卡尔积

概念

笛卡尔乘积是指在数学中，两个集合A和B的所有组合情况。在多表查询时，如果没有指定任何关联条件，就会产生笛卡尔积，返回两张表所有记录的组合。

问题演示

-- 查询员工表和部门表，不指定任何关联条件SELECT*FROMemp,dept;

执行结果会返回3个部门 × 5个员工 = 15条记录，但其中大部分都是无效的组合，例如"张无忌"属于"市场部"、"赵敏"属于"研发部"等，这些都是不符合实际业务逻辑的。

消除无效的笛卡尔积

在多表查询时，必须添加关联条件，只保留符合关联条件的有效记录：

-- 添加关联条件，查询员工姓名及其所在部门名称SELECTemp.name,dept.nameFROMemp,deptWHEREemp.dept_id=dept.id;

执行结果会返回5条有效记录，每个员工都对应了正确的部门名称。

六、完整实战演示

下面我们通过一个综合案例，演示三种多表关系的联合使用和简单的多表查询。

6.1 需求说明

1. 查询所有员工的姓名、年龄及其所在的部门名称
2. 查询所有学生的姓名、学号及其选修的课程名称
3. 查询所有用户的姓名、手机号及其学历信息

6.2 实现代码

-- 1. 查询员工及其部门信息SELECTe.nameAS员工姓名,e.ageAS年龄,d.nameAS部门名称FROMemp e,dept dWHEREe.dept_id=d.id;-- 2. 查询学生及其选修的课程信息SELECTs.nameAS学生姓名,s.noAS学号,c.nameAS课程名称FROMstudent s,course c,student_course scWHEREs.id=sc.studentidANDc.id=sc.courseid;-- 3. 查询用户及其教育信息SELECTu.nameAS用户名,u.phoneAS手机号,edu.degreeAS学历FROMtb_user u,tb_user_edu eduWHEREu.id=edu.userid;

七、常见误区与避坑指南

1. 不要用单表存储所有数据：单表存储会导致严重的数据冗余和不一致问题，一定要根据业务关系进行合理的表拆分。
2. 外键命名规范：外键建议使用fk_子表名_父表名的命名方式，例如fk_emp_dept，便于识别和维护。
3. 中间表的设计原则：多对多关系的中间表除了两个外键外，不要添加其他业务字段，中间表只负责建立关联关系。
4. 一对一关系的使用场景：只有当单表字段过多，且大部分查询只需要访问部分字段时，才考虑使用一对一关系进行表拆分。
5. 笛卡尔积的危害：多表查询时一定要添加关联条件，否则会产生大量无效数据，严重影响查询性能，甚至导致数据库崩溃。
6. 表别名的使用：多表查询时建议给表起别名，简化SQL语句，避免字段名冲突。

八、核心总结：多表关系速查表

为了方便后续开发时快速查阅，整理了三种多表关系的核心速查表：

九、专栏订阅

• 专栏优点？《Python从入门到实战》，专栏内容涵盖：Python基础到高级编程、并发编程（进程/线程/协程）、网络编程（TCP/UDP/Socket）、核心内置/第三方模块、数据库核心实战、Web开发（Django/Flask/FastAPI框架）、数据库（MySQL/ORM/异步数据库）、网络爬虫（同步/异步/分布式）、AI实战、Linux部署运维等全栈核心知识，以项目驱动教学，构建清晰学习路径，适合零基础入门和进阶提升的同学，跟着一步步从入门到精通！专栏地址：https://blog.csdn.net/zsh_1314520/category_13108073.html
• 文章是永久吗？一次订阅后可永久免费查看专栏内所有文章，后续会持续更新全栈相关内容，第一时间获取最新教程！
• 有答疑交流群吗？订阅专栏后有专属的全栈学习答疑群，群内提供专业问题答疑、和众多学习者抱团取暖，一起沉淀技术、赋能成长！
• 进群方式？订阅专栏后可直接在专栏内申请加入答疑群，或私信博主沟通进群事宜：https://bbs.csdn.net/topics/620104702
• 更多干货？点赞+收藏+关注博主不迷路！博主博客链接：https://blog.csdn.net/zsh_1314520?spm=1000.2115.3001.5343，专注Python全栈技术分享，评论区留言问题会一一回复，助力大家轻松搞定Python全栈！

【原创声明】
除本文原文地址以外，如发现同款内容皆为盗版，本文已收录于《Python全栈：从入门到实战》，请勿购买盗版文章和专栏，如购买盗版内容不提供任何服务。原文地址：https://blog.csdn.net/zsh_1314520/article/details/163112457