9：多表查询 -MySQL
作者：Yeats_Liao
- 2022-10-30 江西
本文字数：3483 字
阅读完需：约 11 分钟
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增强可读性
划线
评论
复制
发布于: 刚刚阅读数: 3
版权声明: 本文为 InfoQ 作者【Yeats_Liao】的原创文章。
原文链接:【http://xie.infoq.cn/article/9fe0585d88e98d1cbeac67505】。文章转载请联系作者。
Yeats_Liao
关注
Hello，World！ 2022-10-02 加入
这里更多的是记录个人学习，如果有侵权内容请联系我！ 个人邮箱是：yeats_liao@foxmail.com
评论