但行好事
莫论前程❤

Mysql数据库系列(16)—select语句总结之三:多表查询

​ 这篇文章将会详细的总结mysql中多表查询 的相关语句,即mysql中的交叉连接、内连接、外连接、左连接、右连接、联合查询、全连接.

多表查询顾名思义就是数据同时从多张表中获得,查询语句牵扯到多张表,多表查询有多种语法,多种使用场景,

交叉连接:cross join

既然是多表查询,那么我们先来看两张非常简单的表,我们就以这两张表为例,进行演示.

$ select * from t1;
+-------------+------------+
|   t1id      |     t1str  |
|     1       |       1    |
|     2       |       2    |
|     3       |       3    |
+-------------+------------+
3 rows in set (0.00 sec)

$ select * from t2;
+-------------+------------+
|   t2id      |     t2str  |
|     2       |       a    |
|     3       |       b    |
+-------------+------------+
3 rows in set (0.00 sec)

上图中.我们通常两条语句分别查询了表1与表2的内容,t1表中有3条数据,t2表中有2条数据,那么同时查两张表,会查询出什么内容呢?

$ select * from t1 , t2;
+-------------+------------+--------------+------------+
|   t1id      |     t1str  |    t2id      |     t2str  |
|     1       |       1    |      2       |       a    |
|     1       |       1    |      3       |       b    |
|     2       |       2    |      2       |       a    |
|     2       |       2    |      3       |       b    |
|     3       |       3    |      2       |       a    |
|     3       |       3    |      3       |       b    |
+-------------+------------+--------------+------------+
6 rows in set (0.00 sec)

​ 上图中,我们只是单纯的将两张表使用同一条select语句查询了出来,并没有添加任何额外的过滤条件,仔细观察查询出的数据,可以发现,当使用上图中的语句时,t1表中的每一行记录,都与t2表中的任意一条记录相关联,同样,t2表中的每一行记录,都与t1表中的任意一条记录相关联.

换句话说,两张表中的数据会以下图中的方式被”交叉连接”在一起.然后展示出来.

img

当然,上述示例中,t1表中有3条数据,t2表中有2条数据,所以”交叉连接”后如上图,如果t1表中有3条数据,t2表中也有三条数据,那么交叉连接后的结果如下:

img

我们把上述”没有任何限制条件的连接方式”称之为”交叉连接”,”交叉连接”后得到的结果跟线性代数中的”笛卡尔乘积”一样.

可以看到,使用交叉连接时,任意一张表中的记录多出一行,”交叉连接”的数量都会增长很多.

所以通过交叉连接的方式进行多表查询的这种方法,我们并不常用,而且我们应该尽量避免这种查询.

“交叉连接”的英文为”cross join”,被咱们翻译为交叉连接,其实.如下两种写法效果相同:

$ select * from t1 cross join t2;   # 官方建议的最标准的写法。
$ select * from t1 , t2;

我们也可以将多张表使用”cross join”连接起来,比如将t1,t2,t3三张表使用”cross join”连接起来.示例如下:

select * from t1 cross join t2 cross join t3;
select * from t1,t2,t3;

内连接 : inner join

既然”交叉连接”不常用,那么肯定 有其他的常用的”多表查询方式”.

我们来看看另一种常用的多表查询的方式: 内连接.

仍然拿刚才的t1与t2表为例,此处回顾一下这两张表的内容.

$ select * from t1;
+-------------+------------+
|   t1id      |     t1str  |
|     1       |       1    |
|     2       |       2    |
|     3       |       3    |
+-------------+------------+
3 rows in set (0.00 sec)

$ select * from t2;
+-------------+------------+
|   t2id      |     t2str  |
|     2       |       a    |
|     3       |       b    |
+-------------+------------+
3 rows in set (0.00 sec)

那么什么是”内连接”呢? 我们可以把”内连接”理解成”两张表中同时符合某种条件的数据记录的组合”,这样说不容易理解,我们来动手做一个小例子,示例如下.

$ select * from  t1,t2 where t1.t1id = t2.t2id;
+-------------+------------+--------------+------------+
|   t1id      |     t1str  |    t2id      |     t2str  |
|     2       |       2    |      2       |       a    |
|     3       |       3    |      3       |       b    |
+-------------+------------+--------------+------------+
2 rows in set (0.00 sec)

上述的sql语句就使用了”内连接”,上图中的sql语句查询出了t1表与t2表中id号相同的记录,并把两表中id号相同的记录连接在了一起,我们对比着”内连接”的概念,来理解上述中的sql语句,我们说过内连接就是”两张表中同时符合某种条件的数据记录的组合”,那么上述sql中,where t1.t1id = t2.t2id就是所谓的”符合某种条件的”,上述sql中查询出的结果就是”两表中同时符合某种条件的数据记录的组合”,这其实就是所谓的”内连接”.

在mysql中,”内连接”的语句与”交叉连接”的语句的不同之处就是”内连接”语句比”交叉连接”有更多的限制条件.

“内连接”的英文原文为”inner join”, 所以, 刚才的内连接sql语句还能换成另一种写法,两种写法得到的结果是相同的,示例如下:

select * from t1 inner join t2 on t1.t1id = t2.t2id;        # 二者结果相同    官方推荐款
+-------------+------------+--------------+------------+
|   t1id      |     t1str  |    t2id      |     t2str  |
|     2       |       2    |      2       |       a    |
|     3       |       3    |      3       |       b    |
+-------------+------------+--------------+------------+
select * from t1,t2 where t1.t1id = t2.t2id;
+-------------+------------+--------------+------------+
|   t1id      |     t1str  |    t2id      |     t2str  |
|     2       |       2    |      2       |       a    |
|     3       |       3    |      3       |       b    |
+-------------+------------+--------------+------------+

上述sql语句中第一种语法才是官方推荐的标准写法. 内连接的两张表用”inner join”连接在一起, 使用”on”指明”条件”.

我们刚才说过,在mysql中, “内连接”与”交叉连接”的不同之处就是”内连接”语句比”交叉连接”语句有更多的限制条件,那么如果我们把”内连接”的”限制条件”去掉,得出的结果会与”交叉连接”得出的结果相同吗? 我们来做个”实验”.

$ select * from t1 inner join t2 on t1.t1id = t2.t2id;
+-------------+------------+--------------+------------+
|   t1id      |     t1str  |    t2id      |     t2str  |
|     2       |       2    |      2       |       a    |
|     3       |       3    |      3       |       b    |
+-------------+------------+--------------+------------+
2 rows in set (0.00 sec)

$ select * from t1 inner join t2;           # 没有限制条件的"内连接"
+-------------+------------+--------------+------------+
|   t1id      |     t1str  |    t2id      |     t2str  |
|     1       |       1    |      2       |       a    |
|     1       |       1    |      3       |       b    |
|     2       |       2    |      2       |       a    |
|     2       |       2    |      3       |       b    |
|     3       |       3    |      2       |       a    |
|     3       |       3    |      3       |       b    |
+-------------+------------+--------------+------------+
6 rows in set (0.00 sec)

$ select * from t1 cross join t2;       # 交叉连接
+-------------+------------+--------------+------------+
|   t1id      |     t1str  |    t2id      |     t2str  |
|     1       |       1    |      2       |       a    |
|     1       |       1    |      3       |       b    |
|     2       |       2    |      2       |       a    |
|     2       |       2    |      3       |       b    |
|     3       |       3    |      2       |       a    |
|     3       |       3    |      3       |       b    |
+-------------+------------+--------------+------------+
6 rows in set (0.00 sec)

从实验结果可以看出,当不附加任何条件时,内连接与交叉连接查询出的结果并没有什么不同.如果”交叉连接”加上”连接条件”,是否与”内连接”查询得出的结果相同呢?

$ select * from t1 cross join t2 on t1id = t2id;
+-------------+------------+--------------+------------+
|   t1id      |     t1str  |    t2id      |     t2str  |
|     2       |       2    |      2       |       a    |
|     3       |       3    |      3       |       b    |
+-------------+------------+--------------+------------+
2 rows in set (0.00 sec)

$ select * from t1 inner join t2 on t1id = t2id;
+-------------+------------+--------------+------------+
|   t1id      |     t1str  |    t2id      |     t2str  |
|     2       |       2    |      2       |       a    |
|     3       |       3    |      3       |       b    |
+-------------+------------+--------------+------------+
2 rows in set (0.00 sec)

实验证明: 在mysql中,”cross join” 与 “inner join”似乎可以互相替代,但是在通用的sql标准中,这两者是不同的.

同时我们得出了一个结论,在通常情况下,使用内连接时需要指定连接条件,换句话说,就是使用”inner join”时一定不要忘记使用”on”指明连接条件.

我们用图示的方法描述一遍什么是内连接.

我们把t1与t2表当做两个集合,把t1id与t2id分别当做两个集合中的元素,可以理解为下图:

img

还记得我们刚才使用的”内连接”查询语句吗,”内连接”查询语句如下:

$ select * from t1 inner join t2 on t1.t1id = t2.t2id;
+-------------+------------+--------------+------------+
|   t1id      |     t1str  |    t2id      |     t2str  |
|     2       |       2    |      2       |       a    |
|     3       |       3    |      3       |       b    |
+-------------+------------+--------------+------------+
2 rows in set (0.00 sec)

即t1id与t2id相同的记录被查询了出来,从结果来看,由于t2表中并不存在id号为1的记录,所以,只查询出了两张表中id号同为2和3的两条记录,用图表示如下:

img

内连接分为:

  • 等值连接
  • 不等连接
  • 自连接

内连接是否属于”等值连接”取决于”连接条件”中有没有使用”等号”.

上述例子都是等值连接,下面举例一个不等连接

$ select * from t1 inner join t2 on t1.t1id > t2.t2id;
+-------------+------------+--------------+------------+
|   t1id      |     t1str  |    t2id      |     t2str  |
|     3       |       3    |      2       |       a    |
+-------------+------------+--------------+------------+
1 rows in set (0.00 sec)
$ select * from t1 inner join t2 on t1.t1id < t2.t2id;
+-------------+------------+--------------+------------+
|   t1id      |     t1str  |    t2id      |     t2str  |
|     1       |       1    |      2       |       a    |
|     1       |       1    |      3       |       b    |
|     2       |       2    |      3       |       b    |
+-------------+------------+--------------+------------+
1 rows in set (0.00 sec)

用图示的方法表示上图中的”内连接”语句,可以参考下图:

img

从上图中可以发现,使用”内连接”语句查询出的结果集中”同时满足条件的数据”的”组合”,所以我们并不能单纯的用”交集”的去表示这个组合,就以上图为例,按照”交集”的定义,属于集合A且同时属于集合B的元素所组成的集合被称为交集,但是上图中,id号为1的元素只属于t1表,在t2表中并不存在id为1的元素,但是上图中”中间”的结果集就是”内连接”查询出的结果,所以,我们不能单纯的用”交集”表示”内连接”.但是我们可以从另一个角度定义”交集”.我们定义.”交集”为”两个集合中同时满足条件的数据的组合”,那么我们可以把”内连接”查询出的结果集用下图表示:

img

通过上图去理解”内连接”,可能更容易理解一点.

自连接

“自连接”连接的表为一张表,也就是自己连接自己.我们来动手做一个例子

有一张students表,数据如下:

$ select * from students;
+-------------+------------+--------------+
|    id       |      name  |     tid      |
|     1       | zhangsan   |      3       |
|     2       |  wangwu    |      1       |
|     3       |  zhangsi   |      2       |
|     4       |   niuer    |      1       |
+-------------+------------+--------------+
4 rows in set (0.00 sec)

这张表存放了”学习”的名字.同时也存放了”老师的名字”,因为这张表里的学生有可能是其他”学生”的”老师”,他们之间相互学习,所以,上表中tid对应的就是学生的id,那么,我们可以通过”自连接”,查出每个”学生”的老师的名字.示例如下:

$ select * from students s1 inner join students t1 on s1.tid = t1.id;

img

上图的两个sql语句就属于”自连接”,自连接把同一张表连接了起来,这就是”自连接”,很容易理解吧.

其实在mysql中,”inner join” 还可以缩写为”join”,他们是等效的,示例如下:

select * from t1 join t2 on t1.t1id = t2.t2id;
+------------+-----------+-------------+-----------+
|   t1id     |    t1str  |    t2id     |    t2str  |
|     2      |      2    |      2      |      a    |
|     3      |      3    |      3      |      b    |
+------------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

外连接:left join ,right join

“外连接”分为两种,”左外连接”和”右外连接”,我们只要搞明白其中任意一个,就可以了.有了之前”交叉连接”和”内连接”的基础,再看”外连接”,就容易多了.

那么,我们先来了解了解”左外连接”,”左外连接”的英文原名为”left outer join”,我们可以使用”left outer join”将两张表进行左外连接,我们动手做个小例子.

仍然以t1表与t2表为例,老规矩,先回顾一下两张表的数据.

img

左外连接/左连接

进行左外连接操作

select * from t1 left outer join t2 on t1id = t2id; # 等价于
select * from t1 left join t2 on t1id = t2id;
+------------+-----------+-------------+-----------+
|   t1id     |    t1str  |    t2id     |    t2str  |
|     2      |      2    |      2      |      a    |
|     3      |      3    |      3      |      b    |
|     1      |      1    |   null      |   null    |
+------------+-----------+-------------+-----------+

可以看出与”内连接”查询出的数据有一部分相同,但是又不是完全相同,通过对比发现,在相同的条件下,”左外连接”查询出的数据更多一点,多出来的一行记录由t1表中的id号为1的记录和一条”空记录”组成.t2表中并不存在id号为1的记录,也会显示,这就是左外连接的的特性.

左外连接不仅会查询出两表中同时符合条件的记录的组合.同时还会将”left outer join”左侧的表中的不符合条件的记录同时展示出来,由于左侧表中的这一部分记录并不符合连接条件,所以这一部分记录使用”空记录”进行连接.

换句话说,左外连接”左侧的表”中的所有记录都会被展示出来,左侧表中符合条件的记录将会与右侧表中符合条件的记录相互连接组合,左侧表中不符合条件的记录将会与右侧表中的”空记录”进行连接.

我们来画个图来示意一下

img

右外连接 /右连接

正好相反,以右侧的表为主.

select * from t1 right outer join t2 on t1id = t2id; #等价于
select * from t1 right  join t2 on t1id = t2id;

我们还可以在左连接/右连接的基础上添加更多的过滤条件.示例如下 :

select * from t1 left join t2 on t1id = t2id 
where t2id is null ;

联合查询:union 与union all

把多个查询语句集中在一起显示,语法如下:

把谁放前面就以谁为准

select column_name(s) from table_name1 union select column_name(s) from table_name2;

当使用union连接两个查询语句时,两个语句查询出的字段数量必须相同,否则无法使用union进行联合查询,

报错如下:

ERROR 1222 (21000) : The used SELECT statements have a different number of columns

使用union将两个结果集中显示时,重复的数据会被合并为一条

union all 则是将两个结果集中显示时,重复的数据也会被显示出来

全连接:full join

mysql中并不支持”全连接”,更准确的说,mysql中不能直接使用”full join”实现全连接,不过,我们可以变相的实现”全连接”,在mysql中,我们可以使用”left join”,”union”,”right join”的组合实现所谓的”全连接”.

img

由于union会将”左连接”和”右连接”查询出的结果集中的重复数据合并,所以查询出的结果如上.

与”左连接”和”右连接”一样,”全连接”也可以添加更多的连接条件

select * from t1 left join t5 on t1id=t5id where t5id is null
union
select * from t1 right join t5 on t1id = t5id where t1id is null;
赞(1) 打赏
未经允许不得转载:刘鹏博客 » Mysql数据库系列(16)—select语句总结之三:多表查询
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏