9：多表查询-MySQL

举报
Yeats_Liao 发表于 2022/10/23 21:37:17 2022/10/23
【摘要】 9.1 union联合查询UNION关键字用于连接两个以上的SELECT语句的结果组合到一个结果集合中使用union ，mysql会把结果集中重复的记录删掉使用union all，mysql会把所有的记录返回，且效率高于unionmysql> select * from stu;+-------+-------+| stuId | name |+-------+-------+| ...

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)

9.10 哪一个实用？

看业务需求，实际情况是把查询条件的公共字段写全，用inner join增强可读性

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入