14.mysql连接(join)查询 交叉连接 内连接 外连接 自连接

mysql评论39字数 2926阅读9分45秒

连接(join)查询是将两个查询的结果以“横向对接”的方式合并起来的结果。
对比:联合查询 是将两个查询的结果以“纵向堆叠”的方式合并起来的结果。

14.1.连接查询概述

连接查询,是将两个查询(或表)的每一行,以“两两横向对接”的方式,所得到的所有行的结果。
即一个表中的某行,跟另一个表中的某行,进行“横向对接”,而得到一个新行。
如下图所示:

则他们对接(连接)之后的结果类似这样:文章源自国强极客生活-https://tagqwl.com/2777.html

可见,假设:
表1有n1行,m1列;
表2有n2行,m2列;
则表1和表2“连接”之后,就会有:
n1*n2行;
m1+m2列。文章源自国强极客生活-https://tagqwl.com/2777.html

select  ...  from  表1  [连接方式] join  表2  [on连接条件]  where ... ;

可见,连接查询只是作为from子句的“数据源”。
或者说,连接查询是扩大了数据源,从原来的一个表作为数据源,扩大为多个表作为数据源。
连接查询包括以下这些不同形式:
交叉连接,内连接,外连接(分:左外连接,右外连接)文章源自国强极客生活-https://tagqwl.com/2777.html

14.2.交叉连接(cross join)

辅助理解连接到概念,实际用处不大

from  表1  [cross]  join  表2

1,交叉连接其实可以认为是连接查询的“完全版本”,即所有行都无条件地都连接起来了
2,关键字“cross”可以省略;
3,交叉连接又称为“笛卡尔积”,通常应用价值不大
4,交叉连接还有一种写法: from 表1, 表2;文章源自国强极客生活-https://tagqwl.com/2777.html

14.3.内连接(inner join)

from  表1  [inner]  join  表2  on  连接条件

1,内连接其实是交叉连接的基础上,再通过on条件而筛选出来的部分数据。
2,关键字“inner”可以省略,但建议写上。
3,内连接是应用最广泛的一种连接查询,其本质是根据条件筛选出“有意义的数据”。文章源自国强极客生活-https://tagqwl.com/2777.html

找出出所有商品及其所属类别。

-- 内连接查询:
SELECT * FROM `product` inner join product_type on product.protype_id = product_type.protype_id;
-- 也可以改写为:
SELECT * FROM product as p inner join product_type as t on p.protype_id = t.protype_id;

因为连接之后的数据表中有两个protype_id,为了区分,需要说明是哪个表中的protype_id;product.protype_id product_type.protype_id文章源自国强极客生活-https://tagqwl.com/2777.html

找出所有价格大于5000的家用电器的商品的完整信息(含所属类别);

SELECT * FROM product as p inner join product_type as t on p.protype_id = t.protype_id
where price > 5000 and protype_name = '家用电器';

找出所有价格大于5000的家用电器的商品的名称,价格和品牌

SELECT pro_name, price, pinpai 
    FROM product as p inner join product_type as t on p.protype _id = t.protype_id
    where price > 5000 and protype_name = '家用电器';

14.4.外连接

外连接分为左外连接和右外连接。文章源自国强极客生活-https://tagqwl.com/2777.html

14.4.1.左外连接(left join):

from  表1  left [outer] join  表2  on  连接条件

1,左外连接其实是保证左边表的数据都能够取出的一种连接。
2,左外连接其实是在内连接的基础上,再加上左边表中所有不能满足条件的数据
3,关键字“outer”可以省略。文章源自国强极客生活-https://tagqwl.com/2777.html

找出所有类别及各类别中的商品(需列出类别名称,商品名称,价格,品牌和产地)

-- 演示左连接:
--找出所有类别及各类别中的商品(需列出类别名称,商品名称,价格,品牌和产地)
select protype_name, pro_name, price, pinpai, chandi 
    from product_type as t left join product as p on p.protype_id = t.protype_id

14.4.2.右外连接(right join):

from  表1  right [outer] join  表2  on  连接条件

1,右外连接其实是保证右边表的数据都能够取出的一种连接。
2,右外连接其实是在内连接的基础上,再加上右边表中所有不能满足条件的数据。
3,关键字“outer”可以省略。文章源自国强极客生活-https://tagqwl.com/2777.html

select * from join1 left join join2 on join1.f1 = join2.c1;

找出所有用户及其订单信息(需列出用户id,用户名,订单号,订单总价,订单地址)文章源自国强极客生活-https://tagqwl.com/2777.html

找出所有用户及其订单信息

-- 演示右连接:
--找出所有用户及其订单信息(需列出用户id,用户名,订单号,订单总价,订单地址) 
select user_info.user_id, user_name, order_id, order_total, order_addr
    from order_info  right join user_info on user_info.user_id = order_info.user_id;

因为是显示用户信息,有的用户没有订单,也要显示,
所以 右连接right user_info
左连接 user_info left文章源自国强极客生活-https://tagqwl.com/2777.html

-- 演示左连接:
--上述语句,也可以写为如下形式,结果一样:
select user_info.user_id, user_name, order_id, order_total, order_addr
    from user_info  left join order_info on user_info.user_id = order_info.user_id;

可见:
左连接,右连接,其实是可以互换的——无非是把两个表的顺序调换一下。文章源自国强极客生活-https://tagqwl.com/2777.html

14.5.自连接

自连接不是一种新的连接形式,而只是一个表“自己跟自己连接”,这怎么做到呢?文章源自国强极客生活-https://tagqwl.com/2777.html

from  表1  as  a  [连接形式]  join  表1  as  b  on  a.xx字段1=b.xx字段名

1,自连接其实还是两个表连接,只是将一个表用不同的别名,当做两个表。
2,自连接适用于一个表中的某个字段的值“来源于”当前表的另一个字段的情况。文章源自国强极客生活-https://tagqwl.com/2777.html

地区表如下所示:
id area_name parent_id
1北京市 0
2河北省 0
3山东省 0
4石家庄 2
5保定 2
6衡水 2
7济南 3
8青岛 3
9烟台 3
... ...
要求查询每个城市及其所在省份,结果类似如下所示:
城市 省份
石家庄 河北省
保定 河北省文章源自国强极客生活-https://tagqwl.com/2777.html

思路:文章源自国强极客生活-https://tagqwl.com/2777.html

select  a.area_name, b.area_name  from area as a  join  area as b  on a.parent_id = b.id;

需求稍作调整:文章源自国强极客生活-https://tagqwl.com/2777.html

---找出所有省份及其下属城市。
select province.area_name, city.area_name  
    from area as province join area as city on province.id = city.parent_id;
-- (如果还要包括不含下属城市的省级);
select province.area_name, city.area_name  
    from area as province left join area as city on province.id = city.parent_id
    where province.parent_id = 0;
文章源自国强极客生活-https://tagqwl.com/2777.html文章源自国强极客生活-https://tagqwl.com/2777.html
weinxin
我的微信
添加我的微信,有任何问题请与我直接联系(备注:国强极客生活)
  • 全部本人精心整理制作,希望大家多多支持。
  • 转载请务必保留本文链接:https://tagqwl.com/2777.html
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: