1. MySQL连接(join)查询
连接(join)查询是将两个查询的结果以“横向对接”的方式合并起来的结果。
对比:联合查询 是将两个查询的结果以“纵向堆叠”的方式合并起来的结果。
1.1. 连接查询概述
连接查询,是将两个查询(或表)的每一行,以“两两横向对接”的方式,所得到的所有行的结果。
即一个表中的某行,跟另一个表中的某行,进行“横向对接”,而得到一个新行。
如下图所示:
则他们对接(连接)之后的结果类似这样:
可见,假设:
表1有n1行,m1列;
表2有n2行,m2列;
则表1和表2“连接”之后,就会有:
n1*n2行;
m1+m2列。
连接查询基本形式如下:select ... from 表1 [连接方式] join 表2 [on连接条件] where ... ;
可见,连接查询只是作为from子句的“数据源”。
或者说,连接查询是扩大了数据源,从原来的一个表作为数据源,扩大为多个表作为数据源。
连接查询包括以下这些不同形式:
交叉连接,内连接,外连接(分:左外连接,右外连接)。
1.2. 交叉连接(cross join)
语法形式:from 表1 [cross] join 表2
说明:
1,交叉连接其实可以认为是连接查询的“完全版本”,即所有行都无条件地都连接起来了。
2,关键字“cross”可以省略;
3,交叉连接又称为“笛卡尔积”,通常应用价值不大。
4,交叉连接还有一种写法: from 表1, 表2;
演示:
1.3. 内连接(inner join)
语法形式:from 表1 [inner] join 表2 on 连接条件
说明:
1,内连接其实是交叉连接的基础上,再通过on条件而筛选出来的部分数据。
2,关键字“inner”可以省略,但建议写上。
3,内连接是应用最广泛的一种连接查询,其本质是根据条件筛选出“有意义的数据”。
演示:
找出出所有商品及其所属类别。
课堂练习:
找出所有价格大于5000的家用电器的商品的完整信息(含所属类别);
1.4. 外连接
外连接分为左外连接和右外连接。
1.4.1. 左外连接(left join):
语法形式:from 表1 left [outer] join 表2 on 连接条件
说明:
1,左外连接其实是保证左边表的数据都能够取出的一种连接。
2,左外连接其实是在内连接的基础上,再加上左边表中所有不能满足条件的数据
3,关键字“outer”可以省略。
演示:
找出所有类别及各类别中的商品(需列出类别名称,商品名称,价格,品牌和产地)
1.4.2. 右外连接(right join):
语法形式:from 表1 right [outer] join 表2 on 连接条件
说明:
1,右外连接其实是保证右边表的数据都能够取出的一种连接。
2,右外连接其实是在内连接的基础上,再加上右边表中所有不能满足条件的数据。
3,关键字“outer”可以省略。select * from join1 left join join2 on join1.f1 = join2.c1;
演示:
找出所有用户及其订单信息(需列出用户id,用户名,订单号,订单总价,订单地址)
扩展一下:
可见:
左连接,右连接,其实是可以互换的——无非是把两个表的顺序调换一下。
1.5. 自连接
自连接不是一种新的连接形式,而只是一个表“自己跟自己连接”,这怎么做到呢?
语法形式:from 表1 as a [连接形式] join 表1 as b on a.xx字段1=b.xx字段名
说明:
1, 自连接其实还是两个表连接,只是将一个表用不同的别名,当做两个表。
2, 自连接适用于一个表中的某个字段的值“来源于”当前表的另一个字段的情况。
示例:
地区表如下所示:
id area_name parent_id
1 北京市 0
2 河北省 0
3 山东省 0
4 石家庄 2
5 保定 2
6 衡水 2
7 济南 3
8 青岛 3
9 烟台 3
... ...
要求查询每个城市及其所在省份,结果类似如下所示:
城市 省份
石家庄 河北省
保定 河北省
思路:
select a.area_name, b.area_name from area as a join area as b on a.parent_id = b.id;
需求稍作调整:
找出所有省份及其下属城市。