MySQL-连接查询

发表于

一.为什么需要连接查询

😊在前面的文章中我们一直使用的是单表查询，虽然我们学习了子查询，但是我们只是使用了子查询的结果作为我们想要查询的那张表的条件，我们并不能将两张表的内容合并在一起，解决这个问题的方法就是连接查询，当然我们也可以将两张表合并在一起，这样就可以满足两张表的内容都查询到了，我们首先分别看下这两张表。

  1. student_info表

  1. student_score表

那么将这两个表合并后保证查询到两张表的数据，那么合并后的数据应该是这样的

number name sex id_number department major enrollment_time subject score
20180101 杜子腾 男 158177199901044792 计算机学院 计算机科学与工程 2018-09-01 母猪的产后护理 78
20180101 杜子腾 男 158177199901044792 计算机学院 计算机科学与工程 2018-09-01 论萨达姆的战争准备 88
20180102 杜琦燕 女 151008199801178529 计算机学院 计算机科学与工程 2018-09-01 母猪的产后护理 100
20180102 杜琦燕 女 151008199801178529 计算机学院 计算机科学与工程 2018-09-01 论萨达姆的战争准备 98
20180103 范统 男 17156319980116959X 计算机学院 软件工程 2018-09-01 母猪的产后护理 59
20180103 范统 男 17156319980116959X 计算机学院 软件工程 2018-09-01 论萨达姆的战争准备 61
20180104 史珍香 女 141992199701078600 计算机学院 软件工程 2018-09-01 母猪的产后护理 55
20180104 史珍香 女 141992199701078600 计算机学院 软件工程 2018-09-01 论萨达姆的战争准备 46
20180105 范剑 男 181048200008156368 航天学院 飞行器设计 2018-09-01 NULL NULL
20180106 朱逸群 男 197995199801078445 航天学院 电子信息 2018-09-01 NULL NULL

可能你就有疑问了为什么杜子腾 杜琦燕原来的表中是一条数据，为什么在合并后就变成两条了？因为首先我们看下第一张表，包含了杜子腾的一条记录，这个是它的个人信息，因为这个表也是个人信息表，但是第二个表是成绩表，包含了杜子腾的很多门课的成绩，我们从上面这个表中就可以看到，包含了两门分别是母猪的产后护理和论萨达姆的战争准备，所以我们想要合并为一张表，对应的肯定是两条数据，个人信息部分是一模一样的，但是仅仅成绩部分不同的两条数据，甚至如果我们想要再添加一个科目成绩的话我们必须再抄写一遍个人信息。

将两张甚至多张表合并为一张会带啦来的问题：

  1. 浪费存储空间，因为这个同学的基本信息合并之后每增加一个科目会被抄写一遍。
  2. 当修改某个学生的基本信息的时候必须修改多处，很容易造成信息不一致，增大维护的困难。

🤡当然鱼和熊掌不可兼得，虽然拆分之后确实解决了数据冗余的问题，但是也带来了查询的困难，为了解决这个问题，所以就有了连接查询的概念和方法。

二.连接的概念

😁为了弄清连接的概念，首先我们先建两张表

1
2
sql复制代码CREATE TABLE t1 (m1 int, n1 char(1));
CREATE TABLE t2 (m2 int, n2 char(1));

然后我们为这两张表插入数据

1
2
sql复制代码INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

🦊我们在连接第一个表的时候知道，在没有主外键依赖的情况下，连接就是把各个表中的数据都拿出来，进行排列组合合并为一张新的表如下：

像这样表连接的结果集就被称为笛卡尔积，我们来尝试着连接查询一下。

1
sql复制代码SELECT * FROM t1,t2;

其实我们还有几种等价的写法，结果依然是一样的。

1
sql复制代码SELECT t1.m1,t1.n1,t2.m2,t2.n2 FROM t1,t2;

由于t1 t2表中的列名没有重复的所以，MySQL服务器并不会懵逼，我门也可以直接这样写。

1
sql复制代码SELECT m1,m2,n1,n2 FROM t1,t2;

也等价于如下的查询语句。

1
sql复制代码SELECT t1.*,t2.* FROM t1,t2;

三.连接过程简介

👽首先我们先看下下面这个语句，然后分析表连接的过程

1
sql复制代码SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

在这个查询中我们看到了三个条件。

t1.m1 > 1

t1.m1 = t2.m2

t2.n2 < d

  1. 首先会确定第一个需要查询的表，这个表被称之为驱动表，那么在这个查询中驱动表就是t1就会查询满足条件t1.m1>1的这个条件的数据，单表查询到的数据如下。
1
2
3
4
5
6
sql复制代码+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
  1. 然后t1表查找到第一条记录，也就是t1.m1=2同时t1.b1 = b这个时候会匹配过滤条件，t1.m1 = t2.m2 AND t2.n2 < 'd'匹配到的时候t2.m2=2并且t2.n2也是小于d的，就会得到如下结果。
1
2
3
4
5
sql复制代码+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
+------+------+------+------+
  1. 然后按照上面的步骤一次匹配类推得到t1.m1=3的时候结果如下。
1
2
3
4
5
sql复制代码+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    3 | c    |    3 | c    |
+------+------+------+------+

🧙在最后MySQL会帮我们把所有查询的数据放在一块，就得到了最后的结果，并且我们可以看到整个过程t1只被查询了一次但是这个过程中t2被查询了多次。

1
2
3
4
5
6
sql复制代码+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+

四.内连接和外连接

🎯我们首先来同学们的查询下基本信息和成绩。

1
sql复制代码SELECT student_info.number,name,major,subject FROM `student_info`,`student_score` WHERE student_info.number = student_score.number;

由于范剑和朱逸群两个人在成绩表里面没有，可能是没有参加考试，导致两个number并不能匹配，所以最后的记录是查询不到两个人的记录的，但是有的时候我们可能想把所有的记录都查出来，不管它有没有参加考试。

🎪为了解决这个问题就有了内连接和外连接我们上边使用的就是内连接，接下来我们看下外连接如何使用，提起内连接和外连接就必须先区分两个关键字那就是WHERE和ON那么WEHER和ON究竟什么区别哪？

where就是我们前边使用的那种，不论是内连接还是外连接，不符合的都不放在结果集中。

on对于外连接而言，如果在被驱动表中无法找到对应的记录被驱动表中的记录仍然会放入结果集，用NULL填充，在内连接中和where是一样的，on一般被称之为连接条件，where被称为过滤条件。

  1. 左侧表为驱动表：左外连接。
1
sql复制代码SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

比如我们使用左外连接查询一下学生的基本信息和成绩。

1
sql复制代码SELECT student_info.number,name,major,subject FROM `student_info` LEFT JOIN `student_score` ON student_info.number =student_score.number;

  1. 右侧表为驱动表：右外连接。
1
sql复制代码SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

我们把查询方法改为右连接查询。

1
sql复制代码SELECT student_info.number,name,major,subject FROM `student_info` RIGHT JOIN `student_score` ON student_info.number =student_score.number;

😊内连接：内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集。

1
sql复制代码SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];

其实我们最开始使用的都是内连接的语法，内连接的写法比较多，一下几种都是等价的，推荐使用INNER JOIN因为语义比较明确，容易和左外连接，右外连接区分。

1
2
3
4
sql复制代码SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;
SELECT * FROM t1, t2;

🚨需要注意的是：内连接驱动表和被驱动表可以互换，但是外连接互换后结果会不同。

五.总结与扩展

😊上边说了很多，给大家的感觉不是很直观，我们直接把表t1和t2的三种连接方式写在一起，这样大家理解起来就很easy了：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
sql复制代码SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+


SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
|    1 | a    | NULL | NULL |
+------+------+------+------+


SELECT * FROM t1 RIGHT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
| NULL | NULL |    4 | d    |
+------+------+------+------+

🤡表的别名，表和列名一样也可以设置别名 依然使用AS，如下：

1
sql复制代码SELECT s1.number, s1.name, s1.major, s2.subject, s2.score FROM student_info AS s1 INNER JOIN student_score AS s2 WHERE s1.number = s2.number;

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%