十分钟带你速通 MySQL——关联查询

oageux

于 2024-07-31 10:00:00 发布

阅读量567

点赞数 21

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

本文链接：https://blog.csdn.net/oageux/article/details/140776325

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

左连接

关联查询
从小到大，我们所有人都接触过课程表，比如这样的

在这里插入图片描述

学生、老师、课程等都是我们经常接触到的概念，那么这样的课程表是如何来的呢?如果让你为这些概念创建表格，你会如何创建呢?最简单的方法当然就是原封不动的把这张表格复制到我们的数据库，数据–翻译成字段并插入。但是这样设计的表格比较兄余，一般来说我们设计的表格功能是单一的，比如学生表就是学生表，老师表就是老师表，不会去设计这样包含多个几余字段的复杂表格。

上图中，课程表中出现了老师的名字，老师的名字就是兄余字段因为老师的名字肯定会出现在老师表中。

适当的兄余字段可以加快查询效率，具体的应用要看实际的业务需求。一般来说我们会尽量减少冗余字段。

冗余的坏处是难以保证数据的一致性，维护困难。比如老师的名字发生了修改，我们需要同时修改老师表和课程表。

表和我们 Java 的对象很相似，在 Java 中 SSM 框架中，有一个框架叫做 MyBatis，它会把 Java 对象和数据库表-一映射。那么如果让你来创建上面这张表对应的 Java 对象，你会如何创建呢:

在实际应用中也有不一一映射的，但是总体的设计思路是接近的。经典的关系型数据库设计需要符合范式要求，但是互联网企业在设计表格时，不会严格去遵从范式

经过前面的课程的磨练，大家应该会想到创建学生、老师、课程三个类，而不是去创建课程这个类，含有老师、学生等一系列的属性。

那么问题来了，当我们需要查询课程表的时候，我们就需要从不同的表格中一起查询到结果，从而显示我们的课程表

在这里插入图片描述

这种多表数据记录关联查询，简称关联查询。

关联查询有很多种，常见的有左连接、右连接、内连接等，我们先从左连接开始学习。

语法

我们来看一下左连接关联查询的语法

SELECT
  *
FROM
  TableA LEFT  JOIN
  TableB
  ON condition;

JOIN 是关联查询的关键词，基础的结构是 TableA JOIN TableB，即表 A 和表 B 关联查询，LEFT 表示是左连接
ON 是关联查询的条件，接下来我们会讲到
左连接就是返回左表的所有数据，即使右表没有匹配的数据（此时右表会以 NULL 的形式匹配数据）。

我们在数据库给大家内置了三张表格：ykd_student,ykd_course,ykd_teacher:

在实际的应用中，学生表之外还有班级表和年级表，大学还会有专业表，这里为了简化，我们假设的场景是门派式的，一个长老（老师）负责教一门课，学生之间都是内门弟子，没有多余的概念（其实是为了偷懒哈哈）。

id	name	birthday	course_id	gender	gmt_created	gmt_modified
1	孙悟空	公元前 578 年六月初一	1	男	NULL	NULL
2	刘备	公元 161 年 7 月 16 日	3	男	NULL	NULL
3	妲己	公元前 1076 年七月初三	4	女	NULL	NULL
4	鲁班七号	公元 2016 年 11 月 26 日	2	男	NULL	NULL

id	name	teacher_id	gmt_created	gmt_modified
1	如何击杀脆皮	1	NULL	NULL
2	极限一换一	2	NULL	NULL
3	蹲草的艺术	3	NULL	NULL
4	瑶和明世隐谁才是辅助之王	4	NULL	NULL

id	name	gmt_created	gmt_modified
1	不知火舞	NULL	NULL
2	兰陵王	NULL	NULL
3	王昭君	NULL	NULL
4	蔡文姬	NULL	NULL

现在我们想要查询老师们分别上什么课（不管老师有没有安排课都要显示老师的信息），那么我们需要关联查询 ykd_teacher 和 ykd_course 两张表，假设我们以 ykd_teacher 为左表，左连接查询，关联条件为 ykd_teacher 表的主键和 ykd_course 表中的 teacher_id 字段相等，那么我们应该这么写 SQL：

SELECT
  *
FROM
  ykd_teacher
  LEFT JOIN ykd_course ON ykd_teacher.id = ykd_course.teacher_id;

对比上面的语法结构：

ykd_teacher 是表 A，ykd_course 是表 B
ykd_teacher.id 是指这张表的 id 字段，同理 ykd_course.teacher_id 是指这张表的 teacher_id 这个字段
我们的查询条件，让上面 2 个字段相等并匹配

细心的同学可以发现，ykd_course 表下面的 teacher_id 这个字段本身的含义就是 ykd_teacher 表中的 id 字段

表结构设计规范

互联网公司在使用 MySQL 在设计表结构时，需要遵从以下的规范：

表必须要有主键。
一个字段只表示一个含义。
总是包含两个日期字段:gmt_created(创建日期),gmt_modified(修改日期)，且这两个字段不应该包含有额外的业务逻辑。
MySQL 中，gmt_created、gmt_modified 使用 DATETIME 类型。
禁止使用复杂数据类型（数组，自定义类型等）。
禁止使用物理外键，使用逻辑外键
禁止物理删除，使用逻辑删除 is_deleted

逻辑删除（英语：logical deletion），又被称软删除、假删除，是一种数据库操作，使用标记将数据标为不可用，而不从数据库删除数据本身。使用适当的方法可恢复被删除的数据。在商业公司的实际开发中会使用，本课程暂不涉及。

其中第 6 点，例如表 b 记录了表 a 的 id，我们只需在表 b 中添加一列：a_id 就可以了，然后通过程序来控制外键关系,这就是逻辑外键。在 MySQL 中，我们通过这样的方式去关联两张表。

我们可以看到，在上面的 ykd_teacher 和 ykd_course 就是这样的关联关系。

初学者很容易把 ykd_teacher 的主键也命名成 teacher_id，这是一种不规范的行为，teacher_id 这个字段是指去关联 ykd_teacher 这张表中的主键。对于 ykd_teacher 本身而言，它叫 id 就可以了，尽量避免在 ykd_teacher 表中出现 ykd_teacher_name 这样的字段。

我们来看查询得到的结果，我们其中一条数据：

我们可以看到查询的结果，将左表的所有数据和右表对应的数据匹配起来了。

我们修改查询语句，尝试更好的去理解左连接，现在我们依然关联查询 ykd_teacher 和 ykd_course 两张表，关联条件为 ykd_teacher 表的 name 和 ykd_course 表中的 name 字段相等，那么我们应该这么写 SQL：

SELECT
  *
FROM
  ykd_teacher
  LEFT JOIN ykd_course ON ykd_teacher.name = ykd_course.name;

我们可以看到左表的内容是全的，右表的数据都是 NULL

这是因为左表的 name 和右表的 name 并没有关联关系,无法匹配

在实际的应用中，我们往往使用有关联关系的字段进行关联查询，这样能查询得到有意义的数据。

右连接

在理解了左连接之后，右连接的理解就变得很简单了，右连接就是返回右表的所有数据，即使左表没有匹配的数据。

语法

我们来看一下右连接的语法

SELECT
  *
FROM
  TableA RIGHT JOIN 
  TableB 
  ON condition;

参考上一节的例子，现在我们想用右连接查询课程对应的老师（不管课程有没有对应的老师，都要展示课程信息），那么我们应该这么写SQL：

SELECT
  *
FROM
  ykd_teacher
  RIGHT JOIN ykd_course ON ykd_teacher.id = ykd_course.teacher_id;

我们可以看到，因为每一个id都有匹配的数据，所以右连接查询得到的数据和左连接是一样的。

在实际的应用中，完全匹配的情况比较少，我们手动执行一下下面的UPDATE语句，将两张表的关联id变得不完全一致：

UPDATE ykd_course
   SET teacher_id= 5
 WHERE teacher_id= 1;

我们再手动执行一下下面两个关联查询：

SELECT
  *
FROM
  ykd_teacher
  LEFT JOIN ykd_course ON ykd_teacher.id = ykd_course.teacher_id;
SELECT
  *
FROM
  ykd_teacher
  RIGHT JOIN ykd_course ON ykd_teacher.id = ykd_course.teacher_id;

我们可以看到查询的结果因为左连接或者右连接不一致，当左连接时，会返回左表的所有数据，当右连接时会返回右表的所有数据。

多表关联查询

在实际应用中，我们有时候会对三张表以上进行关联查询，在这种情况下，我们往往会选中一张表作为主表，以它为基准，进行 LEFT JOIN 或 RIGHT JOIN 查询。

只会使用 A LEFT JOIN B、A LEFT JOIN C。不要出现 A RIGHT JOIN B、B RIGHT JOIN C 这种情况

语法

SELECT
  *
FROM
  TableA 
  LEFT JOIN TableB ON conditionA
  LEFT JOIN TableC ON conditionB;

比如我们对ykd_student,ykd_course,ykd_teacher这三张表进行关联查询，我们的应该这么写SQL:

SELECT
  *
FROM
  ykd_course
  LEFT JOIN ykd_student ON ykd_student.course_id = ykd_course.id
  LEFT JOIN ykd_teacher on ykd_course.teacher_id = ykd_teacher.id;

ABC多表查询的本质是A先和B关联查询，然后再和C进行关联查询，更多表格的场景中，原理也是一致的。

内连接

之前我们学习了左连接和右连接，在实际的应用中，左右连接不能满足所有的查询需要，比如我们想查询第二节中选课了的学生信息，这个时候我们就需要使用内连接。

INNER 连接

我们在数据库为大家内置了两张表，table_a 和 table_b

table_a 数据：

id	name
1	张三
2	李四
3	王五
4	钱六
5	七个葫芦娃
6	八大门派
7	九九归一
8	十全十美
9	及格啦
10	叶冰

table_b 数据：

id	math	chinese	student_id
1	40	60	1
2	30	89	2
3	50	79	13

前面讲到MySQL表格应该包含gmt_created，gmt_modified，这里是为了便于演示，省略了这两个字段。表名也是为了便于演示直接命名为A和B，这都是不规范的行为。

在实际的应用场景中，一张表格不会太复杂，并且会根据字段的不同拆分成多张表，比如说学生成绩表中，有些信息是会变得，比如考试会有很多次，每次的成绩可能不同，而有些信息是基本不变的，比如学生姓名和学生的班级，所以我们会设计学生表和成绩表，然后关联查询得到具体某一场的考试成绩。也就是我们上面的表A和表B。

现在假设我们是大学某门课的老师，我们需要查询班级A中参与B考试的学生，（A，B同时符合某一个条件的数据），得到的数据是内部共有的数据，所以连接方式称为内链接（INNER JOIN）：

我们可以这么写查询语句：

SELECT
  *
FROM
  table_a
  INNER JOIN table_b
ON
  table_a.id = table_b.student_id;

这里INNER可以省略，只写JOIN，效果是一样的，LEFT JOIN 和 RIGHT JOIN都属于外连接，和内连接对应。

我们可以看到，表格A中，学生id为1，2的同学参与了B考试，和查询结果符合

外连接的拓展

在前面两节我们学习了外连接，最后查询得到的数据是这样的：

或者是这样的：

在实际的应用中，有时候我们希望查询A中和B完全没有关系的数据，比如我们希望查询班级A中没有参与B考试的人员，类似这样：

结合上面的两张表，我们可以这么写查询语句：

SELECT
  *
FROM
  table_a
  LEFT JOIN table_b ON table_a.id = table_b.student_id
WHERE
  table_b.student_id IS NULL;

我们可以看到，查询得到的结果不包含B表拥有的student_id。

UNION 关键字

在实际的应用场景中，我们有时候需要联合查询所有的内容，就像下图这样：

比如我们现在想要查询A表中所有的学生和B表中所有的学生，不管他们是否参与了考试，有没有成绩，那么我们应该这样写查询语句：

SELECT
  *
FROM
  table_a
  LEFT JOIN table_b
ON
  table_a.id = table_b.student_id
UNION DISTINCT
SELECT
  *
FROM
  table_a
  RIGHT JOIN table_b ON table_a.id=table_b.student_id;