【数据库】mysql多表查询的四种视图联接方式

需求场景:

数据库中,我们一般不会使所有数据都放在一个表中,但查询时可以同时关联查询多个表从而输出我们想要看到的数据。

是说同时从多个表中获取我们想要的数据展现在视图中,但在查询时存在一定限制,多个表之间一般需要一个关联的列。

查询中并不是所有行都能满足关联条件,自然查询不匹配就会重复或遗漏显示数据,所以sql给出多种查询视图联接类型。

这种联接条件可在from或where子句中指定,大致分为三类联接方式:内联接、外联接、交叉联接

其中内联接一种典型的联接运算,使用=或<>之类的比较运算符号,包含相等联接和自然联接。

就拿左右两个表来说,内部联接,不论是左表还是右表,只要其在另一个表中没有匹配的值,则此行均不显示;

外联接有三种视图类型:左向外联接、右向外联接、完整外部联接;

其中左向外联接、关键字left join 或left outer join,意思是显示左表所有数据,如果其在右表中没有匹配的值则视图结果右表的值为空“null”;而右表的行如果在左表中没有匹配的值则不显示;

同理右向外联接、关键字right join或right outer join,意思是显示右表所有数据,如果其在左表中没有匹配的值则视图结果左表的值为null;而左表的行如果对应匹配的值则不显示;

完全外部联接、关键字full join或full outer join,意思是查询视图返回两表所有的行,两表完全匹配的行显示成一行,与另一表中没有匹配值时,则视图结果中对应行显示为null;

交叉联接、返回左表中所有的行,左表中的每一行和右表中的所有行组合、交叉联接也被称为笛卡尔积

今天我们就重点说下内外部联接类型的四种链接,以mysql为例

♥mysql多表查询的四种联接:内联接、左联接、右联接、完全联接

四种方式关键字分别是:inner join、left join、fight join、full join;

下面我们直接通过一个案例直观的明白其中的关联:


a表

(a)

id name b表

(b)

id age sex parent_id
1 龙一 1 20 boy 1
2 龙二 2 19 boy 2
3 龙三 3 18 girl 5

(其中两表的id和parent_id互为关联的列,从而使两表一一对应)


①、内联接

mysql>select a.*,b.* from a inner join b on a.id=b.parent_id;
-----------------------------
1   龙一   1   20   boy   1
2   龙二   2   19   boy   2
-----------------------------

(内部自然联接查询后,两表的第三行均没有匹配的对应行,所以两表第三行的数据不显示了)

②、左联接

mysql>select a.*,b.* from a left join b on a.id=b.parent_id;
----------------------------
1   龙一   1   20   boy   1
2   龙二   2   19   boy   2
3   龙三   null
----------------------------

(左联接,显示所有左表数据,如果没有匹配右表行则右表数据显示为null,而右表的第三行对应左表没有匹配的数据则不显示)

③、右联接

mysql>select a.*,b.* from a right join b on a.id=b.parent_id;
----------------------------
1   龙一   1   20   boy   1
2   龙二   2   19   boy   2
null       3   18   girl  5
----------------------------

(右联接,显示所有右表数据,如果左表没有匹配值则左表对应视图显示为null,而左表的第三行没有匹配的值则不显示)

④、完全链接

mysql>select a.*,b.* from a full join b on a.id=b.parent_id;
-------------------------------------------------
1   龙一   1   20   boy   1
2   龙二   2   19   boy   2
3   龙三   null
null       3   18   girl  5

(完全链接,可以看出查询结果视图中包含两表的所有数据值)

思考·总结

上面我们知道了内联接,其关键字为 inner join,但是其在多表查询中数据显示不完整,所以外部联接使用比较多。但还有一种联接方式交叉联接,关键字cross join,事实上也不常用;

我们来一起看一下交叉联接的小案例:

mysql> select * from heping.mytable;
+------+------+------------+----------------------+
| id | name | date | data |
+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data |
| 3 | 003 | 2017-10-30 | i'm third data |
+------+------+------------+----------------------+
3 rows in set (0.00 sec)
mysql> select * from heping.mytable001;
+------+------+------------+----------------------+
| id | name | date | data |
+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
+------+------+------------+----------------------+
1 row in set (0.00 sec)
mysql> select * from heping.mytable,heping.mytable001;
+------+------+------------+----------------------+------+------+------------+----------------------+
| id | name | date | data | id | name | date | data |
+------+------+------------+----------------------+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat | 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data      | 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 3 | 003 | 2017-10-30 | i'm third data       | 1 | 001 | 2017-09-19 | welcom i'm first dat |
+------+------+------------+----------------------+------+------+------------+----------------------+
3 rows in set (0.00 sec)
mysql> select * from heping.mytable corss join heping.mytable001;
+------+------+------------+----------------------+------+------+------------+----------------------+
| id | name | date | data | id | name | date | data |
+------+------+------------+----------------------+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat | 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data      | 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 3 | 003 | 2017-10-30 | i'm third data       | 1 | 001 | 2017-09-19 | welcom i'm first dat |
+------+------+------------+----------------------+------+------+------------+----------------------+
3 rows in set (0.00 sec)

可以看到简单的交叉联接和普通直接查询,显示结果是一样的,我们在继续观察:

mysql> select * from heping.mytable003;
+------+------+------------+----------------------+
| id | name | date | data |
+------+------+------------+----------------------+
| 2 | 002 | 2017-09-20 | welcom i'm second da |
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
+------+------+------------+----------------------+
2 rows in set (0.00 sec)

mysql> select * from heping.mytable corss join heping.mytable003;
+------+------+------------+----------------------+------+------+------------+----------------------+
| id | name | date | data | id | name | date | data |
+------+------+------------+----------------------+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat | 2 | 002 | 2017-09-20 | welcom i'm second da |
| 1 | 001 | 2017-09-19 | welcom i'm first dat | 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data      | 2 | 002 | 2017-09-20 | welcom i'm second da |
| 2 | 002 | 2017-10-30 | i'm second data      | 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 3 | 003 | 2017-10-30 | i'm third data       | 2 | 002 | 2017-09-20 | welcom i'm second da |
| 3 | 003 | 2017-10-30 | i'm third data       | 1 | 001 | 2017-09-19 | welcom i'm first dat |
+------+------+------------+----------------------+------+------+------------+----------------------+
6 rows in set (0.00 sec)

所以交叉联接查询,两表的每行数据都要和另外一个表中的所有行做匹配形成一行,也就是说会形成两表行数相乘的行数,也就是所说的“笛卡尔乘积”;

而如果数据上千上万条,且这种查询没有对应关系所以不常用;当然除此之外,还有一种查询方式:联合查询、关键字union或union all

联合查询顾名思义就是把多个查询语句的查询结果集中在一起显示:

mysql> select * from mytable union select * from mytable001;
+------+------+------------+----------------------+
| id | name | date | data |
+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data |
| 3 | 003 | 2017-10-30 | i'm third data |
+------+------+------------+----------------------+
3 rows in set (0.01 sec)
mysql> select * from mytable union select * from mytable003;
+------+------+------------+----------------------+
| id | name | date | data |
+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data |
| 3 | 003 | 2017-10-30 | i'm third data |
| 2 | 002 | 2017-09-20 | welcom i'm second da |
+------+------+------------+----------------------+
4 rows in set (0.00 sec)
mysql> select * from mytable union all select * from mytable001;
+------+------+------------+----------------------+
| id | name | date | data |
+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data |
| 3 | 003 | 2017-10-30 | i'm third data |
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
+------+------+------------+----------------------+
4 rows in set (0.00 sec)

可以看出,当我们使用关键字union联合查询时,视图结果显示查询罗列所有内容,但是如果两条数据记录相同,则只显示一条数据,如果想所有都显示,则使用关键字“union all”;

所以具体如何查询,还是需要根据不同的需求,斟酌进行匹配查询方式哦;

(好的今天就分享到这里,如果您有高见或好的分享,记得留言哦!)


原创文章,转载请注明:转自于公牛博客

本文链接地址:【数据库】mysql多表查询的四种视图联接方式

5
祝福我们的祖国繁荣昌盛
  • 请尽情挥洒您的笔墨!

    欢迎来到公牛博客更多分享更多精彩记录美丽点亮生活

    公牛博客·统计碑运行:2227 D
    博文:202 P
    评论:1124 S