MySQL多表关联查询

MySQL多表关联查询SQL连接(JOIN)子句用于把来自两个或多个表的行结合起来,基于这些表之间的共同字段。连接的结果可以在逻辑上看作是由SELECT语句指定的列组成的新表。左连接与右连接的左右指的是以两张表中的哪一张为基准,它们都是外连接。外连接就好像是为非基准表添加了一行全为空值的万能行,用来与基准表中找不到匹配的行进行匹配。假设两个没有空值的表进行左连接,左表是基准表,左表的所有行都出现在结果中,右表则可能

大家好,又见面了,我是你们的朋友全栈君。

SQL 连接(JOIN) 子句用于把来自两个或多个表的行结合起来,基于这些表之间的共同字段。连接的结果可以在逻辑上看作是由SELECT语句指定的列组成的新表。左连接与右连接的左右指的是以两张表中的哪一张为基准,它们都是外连接。外连接就好像是为非基准表添加了一行全为空值的万能行,用来与基准表中找不到匹配的行进行匹配。假设两个没有空值的表进行左连接,左表是基准表,左表的所有行都出现在结果中,右表则可能因为无法与基准表匹配而出现是空值的字段。


不同的 SQL JOIN

在我们继续讲解实例之前,我们先列出您可以使用的不同的 SQL JOIN 类型:

 INNER JOIN:如果表中有至少一个匹配,则返回行

  • LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行
  • RIGHT JOIN:即使左表中没有匹配,也从右表返回所有的行
  • FULL JOIN:只要其中一个表中存在匹配,则返回行(MySQL不支持FULL JOIN)
实例表1:

mysql> select * from websites;
+----+---------------+---------------------------+-------+---------+
| id | name          | url                       | alexa | country |
+----+---------------+---------------------------+-------+---------+
|  1 | Google        | https://www.google.cm/    |     1 | USA     |
|  2 | 淘宝          | https://www.taobao.com/   |    13 | CN      |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |
|  4 | 微博          | http://weibo.com/         |    20 | CN      |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |
|  6 | stackoverflow | http://stackoverflow.com/ |     0 | IND     |
|  7 | 百度          | https://www.baidu.com/    |     4 | CN      |
+----+---------------+---------------------------+-------+---------+
7 rows in set

实例表2:

mysql> select * from access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set

取交集:

mysql> SELECT w.name, a.count, a.date FROM websites w, access_log a WHERE w.id = a.site_id;
+----------+-------+------------+
| name     | count | date       |
+----------+-------+------------+
| Google   |    45 | 2016-05-10 |
| 菜鸟教程 |   100 | 2016-05-13 |
| Google   |   230 | 2016-05-14 |
| 淘宝     |    10 | 2016-05-14 |
| Facebook |   205 | 2016-05-14 |
| 微博     |    13 | 2016-05-15 |
| 菜鸟教程 |   220 | 2016-05-15 |
| Facebook |   545 | 2016-05-16 |
| 菜鸟教程 |   201 | 2016-05-17 |
+----------+-------+------------+
9 rows in set
mysql> SELECT w.name, a.count, a.date FROM websites w INNER JOIN access_log a ON w.id = a.site_id;
+----------+-------+------------+
| name     | count | date       |
+----------+-------+------------+
| Google   |    45 | 2016-05-10 |
| 菜鸟教程 |   100 | 2016-05-13 |
| Google   |   230 | 2016-05-14 |
| 淘宝     |    10 | 2016-05-14 |
| Facebook |   205 | 2016-05-14 |
| 微博     |    13 | 2016-05-15 |
| 菜鸟教程 |   220 | 2016-05-15 |
| Facebook |   545 | 2016-05-16 |
| 菜鸟教程 |   201 | 2016-05-17 |
+----------+-------+------------+
9 rows in set

MySQL多表关联查询

注释:
INNER JOIN 与 JOIN 是相同的。


取左并集:
MySQL多表关联查询
注释:在某些数据库中,LEFT JOIN 称为 LEFT OUTER JOIN。
LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL

mysql> SELECT w.name, a.count, a.date FROM websites w LEFT JOIN access_log a ON w.id = a.site_id;
+---------------+-------+------------+
| name          | count | date       |
+---------------+-------+------------+
| Google        |    45 | 2016-05-10 |
| 菜鸟教程      |   100 | 2016-05-13 |
| Google        |   230 | 2016-05-14 |
| 淘宝          |    10 | 2016-05-14 |
| Facebook      |   205 | 2016-05-14 |
| 微博          |    13 | 2016-05-15 |
| 菜鸟教程      |   220 | 2016-05-15 |
| Facebook      |   545 | 2016-05-16 |
| 菜鸟教程      |   201 | 2016-05-17 |
| stackoverflow | NULL  | NULL       |
| 百度          | NULL  | NULL       |
+---------------+-------+------------+
11 rows in set


取右并集:



MySQL多表关联查询

注释:在某些数据库中,RIGHT JOIN 称为 RIGHT OUTER JOIN。
RIGHT JOIN 关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL。

mysql> SELECT Websites.name, access_log.count, access_log.date
FROM access_log
RIGHT JOIN Websites
ON access_log.site_id=Websites.id;
+---------------+-------+------------+
| name          | count | date       |
+---------------+-------+------------+
| Google        |    45 | 2016-05-10 |
| 菜鸟教程      |   100 | 2016-05-13 |
| Google        |   230 | 2016-05-14 |
| 淘宝          |    10 | 2016-05-14 |
| Facebook      |   205 | 2016-05-14 |
| 微博          |    13 | 2016-05-15 |
| 菜鸟教程      |   220 | 2016-05-15 |
| Facebook      |   545 | 2016-05-16 |
| 菜鸟教程      |   201 | 2016-05-17 |
| stackoverflow | NULL  | NULL       |
| 百度          | NULL  | NULL       |
+---------------+-------+------------+
11 rows in set


取全集:


MySQL Full Join的实现因为MySQL不支持FULL JOIN,下面是替代方法

left join + union(可去除重复数据)+ right join

select * from A left join B on A.id = B.id (where 条件)union

select * from A right join B on A.id = B.id (where条件);

mysql> SELECT * FROM websites LEFT JOIN access_log ON Websites.id=access_log.site_id 
UNION SELECT * FROM websites RIGHT JOIN access_log ON Websites.id=access_log.site_id;
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
| id | name          | url                       | alexa | country | aid  | site_id | count | date       |
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
|  1 | Google        | https://www.google.cm/    |     1 | USA     |    1 |       1 |    45 | 2016-05-10 |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |    2 |       3 |   100 | 2016-05-13 |
|  1 | Google        | https://www.google.cm/    |     1 | USA     |    3 |       1 |   230 | 2016-05-14 |
|  2 | 淘宝          | https://www.taobao.com/   |    13 | CN      |    4 |       2 |    10 | 2016-05-14 |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |    5 |       5 |   205 | 2016-05-14 |
|  4 | 微博          | http://weibo.com/         |    20 | CN      |    6 |       4 |    13 | 2016-05-15 |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |    7 |       3 |   220 | 2016-05-15 |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |    8 |       5 |   545 | 2016-05-16 |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |    9 |       3 |   201 | 2016-05-17 |
|  6 | stackoverflow | http://stackoverflow.com/ |     0 | IND     | NULL | NULL    | NULL  | NULL       |
|  7 | 百度          | https://www.baidu.com/    |     4 | CN      | NULL | NULL    | NULL  | NULL       |
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
11 rows in set

SQL UNION 操作符合并两个或多个 SELECT 语句的结果。


SQL UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意,UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT 
column_name(s) FROM 
table1

UNION

SELECT 
column_name(s) FROM 
table2;

注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。

SQL UNION ALL 语法

SELECT 
column_name(s) FROM 
table1

UNION ALL

SELECT 
column_name(s) FROM 
table2;

注释:UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

mysql> SELECT * FROM websites LEFT JOIN access_log ON Websites.id=access_log.site_id 
UNION ALL SELECT * FROM websites RIGHT JOIN access_log ON Websites.id=access_log.site_id;
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
| id | name          | url                       | alexa | country | aid  | site_id | count | date       |
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
|  1 | Google        | https://www.google.cm/    |     1 | USA     |    1 |       1 |    45 | 2016-05-10 |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |    2 |       3 |   100 | 2016-05-13 |
|  1 | Google        | https://www.google.cm/    |     1 | USA     |    3 |       1 |   230 | 2016-05-14 |
|  2 | 淘宝          | https://www.taobao.com/   |    13 | CN      |    4 |       2 |    10 | 2016-05-14 |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |    5 |       5 |   205 | 2016-05-14 |
|  4 | 微博          | http://weibo.com/         |    20 | CN      |    6 |       4 |    13 | 2016-05-15 |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |    7 |       3 |   220 | 2016-05-15 |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |    8 |       5 |   545 | 2016-05-16 |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |    9 |       3 |   201 | 2016-05-17 |
|  6 | stackoverflow | http://stackoverflow.com/ |     0 | IND     | NULL | NULL    | NULL  | NULL       |
|  7 | 百度          | https://www.baidu.com/    |     4 | CN      | NULL | NULL    | NULL  | NULL       |
|  1 | Google        | https://www.google.cm/    |     1 | USA     |    1 |       1 |    45 | 2016-05-10 |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |    2 |       3 |   100 | 2016-05-13 |
|  1 | Google        | https://www.google.cm/    |     1 | USA     |    3 |       1 |   230 | 2016-05-14 |
|  2 | 淘宝          | https://www.taobao.com/   |    13 | CN      |    4 |       2 |    10 | 2016-05-14 |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |    5 |       5 |   205 | 2016-05-14 |
|  4 | 微博          | http://weibo.com/         |    20 | CN      |    6 |       4 |    13 | 2016-05-15 |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |    7 |       3 |   220 | 2016-05-15 |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |    8 |       5 |   545 | 2016-05-16 |
|  3 | 菜鸟教程      | http://www.runoob.com/    |  4689 | CN      |    9 |       3 |   201 | 2016-05-17 |
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
20 rows in set



 

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/137897.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号