9：多表查询-MySQL

Yeats_Liao

已于 2022-10-23 21:37:31 修改

阅读量595

点赞数 1

分类专栏： MySQL 文章标签： sql mysql 数据库

于 2021-09-26 13:54:05 首次发布

本文链接：https://blog.csdn.net/qq_46207024/article/details/120487446

版权

MySQL 专栏收录该内容

14 篇文章 25 订阅

订阅专栏

9.1 union联合查询

UNION关键字用于连接两个以上的SELECT语句的结果组合到一个结果集合中

使用union ，mysql会把结果集中重复的记录删掉

使用union all，mysql会把所有的记录返回，且效率高于union

mysql> select * from stu;
+-------+-------+
| stuId | name  |
+-------+-------+
|     4 | frank |
|     5 | Tom   |
+-------+-------+
2 rows in set (0.01 sec)

mysql>
mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  0 | 999.0000 |     5 |
|  1 |  20.5000 |  NULL |
|  2 |  78.6000 |     4 |
|  3 |  99.9000 |  NULL |
|  4 | 748.4000 |     4 |
|  5 | 748.4000 |  NULL |
|  6 | 999.0000 |     5 |
|  7 | 345.0000 |     4 |
+----+----------+-------+
8 rows in set (0.01 sec)

mysql> select stuId from eatery union all select stuId from stu;
+-------+
| stuId |
+-------+
|  NULL |
|  NULL |
|  NULL |
|     4 |
|     4 |
|     4 |
|     5 |
|     5 |
|     4 |
|     5 |
+-------+
10 rows in set (0.00 sec)

mysql> select stuId from eatery union  select stuId from stu;
+-------+
| stuId |
+-------+
|  NULL |
|     4 |
|     5 |
+-------+
3 rows in set (0.00 sec)

9.2 inner join内联查询

inner join与 join 是相同的

图片引用自菜鸟教程

mysql> select * from stu;
+-------+-------+
| stuId | name  |
+-------+-------+
|     4 | frank |
|     5 | Tom   |
+-------+-------+
2 rows in set (0.01 sec)


mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  0 | 999.0000 |     5 |
|  1 |  20.5000 |  NULL |
|  2 |  78.6000 |     4 |
|  3 |  99.9000 |  NULL |
|  4 | 748.4000 |     4 |
|  5 | 748.4000 |  NULL |
|  6 | 999.0000 |     5 |
|  7 | 345.0000 |     4 |
+----+----------+-------+
8 rows in set (0.01 sec)

mysql> select eatery.money, stu.name from eatery inner join stu on eatery.stuId = stu.stuId;
+----------+-------+
| money    | name  |
+----------+-------+
| 999.0000 | Tom   |
|  78.6000 | frank |
| 748.4000 | frank |
| 999.0000 | Tom   |
| 345.0000 | frank |
+----------+-------+
5 rows in set (0.00 sec)

mysql> select id,name,money from eatery  inner join stu on stu.stuId=eatery.stuId;
+----+-------+----------+
| id | name  | money    |
+----+-------+----------+
|  0 | Tom   | 999.0000 |
|  2 | frank |  78.6000 |
|  4 | frank | 748.4000 |
|  6 | Tom   | 999.0000 |
|  7 | frank | 345.0000 |
+----+-------+----------+
5 rows in set (0.00 sec)

9.3 inner join注意事项

必须指定id，例如eatery.stuId = stu.stuId

如果还要连接两张以上的表，可以继续添加Inner join，但是不建议这样操作

9.4 left join 外连接

在某些数据库中，left out称为left outer join

图片引用自菜鸟教程

在这里插入图片描述

9.5 rigth join 外连接

在某些数据库中，left out 称为right outer join

图片引用自菜鸟教程

在这里插入图片描述

9.6 cross join 交叉连接

cross join 返回笛卡尔积，使用时须谨慎，可能产生非常大的表

mysql> select * from eatery cross join stu;
+----+----------+-------+-------+-------+
| id | money    | stuId | stuId | name  |
+----+----------+-------+-------+-------+
|  0 | 999.0000 |     5 |     4 | frank |
|  0 | 999.0000 |     5 |     5 | Tom   |
|  1 |  20.5000 |  NULL |     4 | frank |
|  1 |  20.5000 |  NULL |     5 | Tom   |
|  2 |  78.6000 |     4 |     4 | frank |
|  2 |  78.6000 |     4 |     5 | Tom   |
|  3 |  99.9000 |  NULL |     4 | frank |
|  3 |  99.9000 |  NULL |     5 | Tom   |
|  4 | 748.4000 |     4 |     4 | frank |
|  4 | 748.4000 |     4 |     5 | Tom   |
|  5 | 748.4000 |  NULL |     4 | frank |
|  5 | 748.4000 |  NULL |     5 | Tom   |
|  6 | 999.0000 |     5 |     4 | frank |
|  6 | 999.0000 |     5 |     5 | Tom   |
|  7 | 345.0000 |     4 |     4 | frank |
|  7 | 345.0000 |     4 |     5 | Tom   |
+----+----------+-------+-------+-------+
16 rows in set (0.00 sec)

可以通过交叉连接实现内连接

mysql> select id,name,money from eatery  inner join stu on stu.stuId=eatery.stuId;
+----+-------+----------+
| id | name  | money    |
+----+-------+----------+
|  0 | Tom   | 999.0000 |
|  2 | frank |  78.6000 |
|  4 | frank | 748.4000 |
|  6 | Tom   | 999.0000 |
|  7 | frank | 345.0000 |
+----+-------+----------+
5 rows in set (0.00 sec)

mysql> select eatery.id,name,eatery.money from eatery cross join stu where eatery.stuId = stu.stuId;
+----+-------+----------+
| id | name  | money    |
+----+-------+----------+
|  0 | Tom   | 999.0000 |
|  2 | frank |  78.6000 |
|  4 | frank | 748.4000 |
|  6 | Tom   | 999.0000 |
|  7 | frank | 345.0000 |
+----+-------+----------+
5 rows in set (0.00 sec)

9.7 natural join

自然连接是在两张表中寻找那些数据类型和列名都相同的字段

自动地将他们连接起来，并返回所有符合条件按的结果

mysql> select * from t_1;
+----------+----------+
| number_1 | number_2 |
+----------+----------+
|      2.1 |     2.23 |
|      2.9 |     2.78 |
|      3.0 |     3.00 |
+----------+----------+
3 rows in set (0.00 sec)

mysql> select * from t_2;
+-----------------------+
| number                |
+-----------------------+
| 9.1111111111111100000 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from t_1 natural join t_2;
+----------+----------+-----------------------+
| number_1 | number_2 | number                |
+----------+----------+-----------------------+
|      2.1 |     2.23 | 9.1111111111111100000 |
|      2.9 |     2.78 | 9.1111111111111100000 |
|      3.0 |     3.00 | 9.1111111111111100000 |
+----------+----------+-----------------------+
3 rows in set (0.00 sec)

mysql> select * from eatery natural join stu;
+-------+----+----------+-------+
| stuId | id | money    | name  |
+-------+----+----------+-------+
|     5 |  0 | 999.0000 | Tom   |
|     4 |  2 |  78.6000 | frank |
|     4 |  4 | 748.4000 | frank |
|     5 |  6 | 999.0000 | Tom   |
|     4 |  7 | 345.0000 | frank |
+-------+----+----------+-------

9.8 无公共同名字段的自然返回笛卡尔积

mysql> select * from t_1 natural join t_5;
+----------+----------+--------+
| number_1 | number_2 | gender |
+----------+----------+--------+
|      2.1 |     2.23 | man    |
|      2.9 |     2.78 | man    |
|      3.0 |     3.00 | man    |
|      2.1 |     2.23 | woman  |
|      2.9 |     2.78 | woman  |
|      3.0 |     3.00 | woman  |
+----------+----------+--------+
6 rows in set (0.00 sec)

9.9 using

using相当于join操作中的on

根据id字段关联，以下命令的等价的，注意使用using时前面不要加on

on eatery.stuId = stu.stuId

using(stuId)

mysql> select id,money,name from eatery inner join stu on eatery.stuId = stu.stuId;
+----+----------+-------+
| id | money    | name  |
+----+----------+-------+
|  0 | 999.0000 | Tom   |
|  2 |  78.6000 | frank |
|  4 | 748.4000 | frank |
|  6 | 999.0000 | Tom   |
|  7 | 345.0000 | frank |
+----+----------+-------+
5 rows in set (0.00 sec)

mysql> select id,money,name from eatery inner join stu using(stuId);
+----+----------+-------+
| id | money    | name  |
+----+----------+-------+
|  0 | 999.0000 | Tom   |
|  2 |  78.6000 | frank |
|  4 | 748.4000 | frank |
|  6 | 999.0000 | Tom   |
|  7 | 345.0000 | frank |
+----+----------+-------+
5 rows in set (0.00 sec)