MySQL进阶之多表联查

时羽天

已于 2022-07-27 20:14:16 修改

阅读量515

点赞数

文章标签： mysql 数据库 mariadb

于 2022-07-27 20:02:44 首次发布

本文链接：https://blog.csdn.net/mw5258/article/details/126021454

版权

本文深入介绍了MySQL中的多表查询，包括交叉连接（笛卡尔积）、内连接（INNER JOIN）、外连接（左外连接和右外连接），并提供了丰富的查询示例。通过理解这些概念，开发者可以更有效地处理数据库中的复杂查询需求。

摘要由CSDN通过智能技术生成

文章目录

- 多表联合查询

多表联合查询

什么是多表联查

在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表。
在 MySQL 中，多表查询主要有交叉连接、内连接、外连接、分组查询与子查询等5种。

交叉连接

笛卡尔积

交叉连接(CROSS JOIN)：有两种，显式的和隐式的2种，一般用来返回连接表的笛卡尔积。
笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。
比如有两个合集

A{1,2}
B{3,4,5}
那么这两个合集的笛卡尔积分别是
A × B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5)}
B × A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2)}

两个集合相乘，不满足交换率，即 A×B≠B×A。
A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。

多表查询遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。在实际应用中，应避免使用笛卡尔积，因为笛卡尔积中容易存在大量的不合理数据，简单来说就是容易导致查询结果重复、混乱。

交叉连接

语法

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句];

或者

SELECT <字段名> FROM <表1>, <表2> [WHERE子句];

说明：

字段名：需要查询的字段名称。
<表1><表2>：需要交叉连接的表名。
WHERE 子句：用来设置交叉连接的查询条件。

多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或，即可。以上两种语法的返回结果是相同的。第一种语法是官方建议的标准写法。

当连接的表之间没有关系时，我们会省略掉 WHERE 子句，这时返回结果就是两个表的笛卡尔积，返回结果数量就是两个表的数据行相乘。需要注意的是，如果每个表有 1000 行，那么返回结果的数量就有 1000×1000 = 1000000 行，数据量是非常巨大的。

交叉连接可以查询两个或两个以上的表，这里演示的是两个表的交叉连接查询。

例 1
查询学生信息表和科目信息表，并得到一个笛卡尔积。
1.查询students表中数据

mysql> select * from students;
+----+-------+------+------+--------+-----------+
| id | name  | age  | sex  | height | course_id |
+----+-------+------+------+--------+-----------+
|  1 | one   |   25 | 男   |    160 |         1 |
|  2 | two   |   23 | 男   |    158 |         2 |
|  3 | three |   23 | 女   |    185 |         1 |
|  4 | four  |   22 | 男   |    162 |         3 |
|  5 | five  |   24 | 女   |    175 |         2 |
|  6 | six   |   21 | 女   |    158 |         4 |
|  7 | seven |   20 | 男   |    180 |         4 |
|  8 | keven |   21 | 男   |    170 |         5 |
|  9 | tom   |   23 | 女   |    164 |         5 |
+----+-------+------+------+--------+-----------+
9 rows in set (0.00 sec)

2.查询course表中数据

mysql> select * from course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | C++         |
|  5 | Go          |
+----+-------------+
5 rows in set (0.00 sec)

3.使用交叉连接查出两张表的笛卡尔积

mysql> select * from students cross join course;
+----+-------+------+------+--------+-----------+----+-------------+
| id | name  | age  | sex  | height | course_id | id | course_name |
+----+-------+------+------+--------+-----------+----+-------------+
|  1 | one   |   25 | 男   |    160 |         1 |  1 | Java        |
|  1 | one   |   25 | 男   |    160 |         1 |  2 | MySQL       |
|  1 | one   |   25 | 男   |    160 |         1 |  3 | Python      |
|  1 | one   |   25 | 男   |    160 |         1 |  4 | C++         |
|  1 | one   |   25 | 男   |    160 |         1 |  5 | Go          |
|  2 | two   |   23 | 男   |    158 |         2 |  1 | Java        |
|  2 | two   |   23 | 男   |    158 |         2 |  2 | MySQL       |
|  2 | two   |   23 | 男   |    158 |         2 |  3 | Python      |
|  2 | two   |   23 | 男   |    158 |         2 |  4 | C++         |
|  2 | two   |   23 | 男   |    158 |         2 |  5 | Go          |
|  3 | three |   23 | 女   |    185 |         1 |  1 | Java        |
|  3 | three |   23 | 女   |    185 |         1 |  2 | MySQL       |
|  3 | three |   23 | 女   |    185 |         1 |  3 | Python      |
|  3 | three |   23 | 女   |    185 |         1 |  4 | C++         |
|  3 | three |   23 | 女   |    185 |         1 |  5 | Go          |
|  4 | four  |   22 | 男   |    162 |         3 |  1 | Java        |
|  4 | four  |   22 | 男   |    162 |         3 |  2 | MySQL       |
|  4 | four  |   22 | 男   |    162 |         3 |  3 | Python      |
|  4 | four  |   22 | 男   |    162 |         3 |  4 | C++         |
|  4 | four  |   22 | 男   |    162 |         3 |  5 | Go          |
|  5 | five  |   24 | 女   |    175 |         2 |  1 | Java        |
|  5 | five  |   24 | 女   |    175 |         2 |  2 | MySQL       |
|  5 | five  |   24 | 女   |    175 |         2 |  3 | Python      |
|  5 | five  |   24 | 女   |    175 |         2 |  4 | C++         |
|  5 | five  |   24 | 女   |    175 |         2 |  5 | Go          |
|  6 | six   |   21 | 女   |    158 |         4 |  1 | Java        |
|  6 | six   |   21 | 女   |    158 |         4 |  2 | MySQL       |
|  6 | six   |   21 | 女   |    158 |         4 |  3 | Python      |
|  6 | six   |   21 | 女   |    158 |         4 |  4 | C++         |
|  6 | six   |   21 | 女   |    158 |         4 |  5 | Go          |
|  7 | seven |   20 | 男   |    180 |         4 |  1 | Java        |
|  7 | seven |   20 | 男   |    180 |         4 |  2 | MySQL       |
|  7 | seven |   20 | 男   |    180 |         4 |  3 | Python      |
|  7 | seven |   20 | 男   |    180 |         4 |  4 | C++         |
|  7 | seven |   20 | 男   |    180 |         4 |  5 | Go          |
|  8 | keven |   21 | 男   |    170 |         5 |  1 | Java        |
|  8 | keven |   21 | 男   |    170 |         5 |  2 | MySQL       |
|  8 | keven |   21 | 男   |    170 |         5 |  3 | Python      |
|  8 | keven |   21 | 男   |    170 |