设为首页 收藏本站
查看: 1086|回复: 0

[经验分享] SQL之Join的使用

[复制链接]

尚未签到

发表于 2017-7-14 15:09:41 | 显示全部楼层 |阅读模式
一.基本概念
  关于sql语句中的连接(join)关键字,是较为常用而又不太容易理解的关键字,下面这个例子给出了一个简单的解释 –建表user1,user2:
table1 : create table user2(id int, user_name varchar(10), over varchar(10));
insert into user1 values(1, ‘tangseng’, ‘dtgdf’);
insert into user1 values(2, ‘sunwukong’, ‘dzsf’);
insert into user1 values(1, ‘zhubajie’, ‘jtsz’);
insert into user1 values(1, ‘shaseng’, ‘jslh’);
table2 : create table user2(id int, user_name varchar(10), over varchar(10));
insert into user2 values(1, ‘sunwukong’, ‘chengfo’);
insert into user2 values(2, ‘niumowang’, ‘chengyao’);
insert into user2 values(3, ‘jiaomowang’, ‘chengyao’);
insert into user2 values(4, ‘pengmowang’, ‘chengyao’);
SQL标准中Join的类型
DSC0000.jpg
1. 内连接(inner join或join)
  (1).概念:内联接是基于连接谓词将两张表的列结合在一起,产生新的结果表
(2).内连接维恩图: DSC0001.jpg
(3).sql语句
select a.id, a.user_name, b.over from user1 a inner join user2 b on a.user_name=b.user_name;
结果: DSC0002.jpg
2. 外连接
  外连接包括左向外联接、右向外联接或完整外部联接
a.左外连接:left join 或 left outer join
  (1)概念:左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值(null)。
(2)左外连接维恩图: DSC0003.jpg
(3)sql语句:
select a.id, a.user_name, b.over from user1 a left join user2 b on a.user
_name=b.user_name;
结果: DSC0004.jpg
b.右外连接:right join 或 right outer join
  (1)右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
(2)右外连接维恩图: DSC0005.jpg
(3)sql语句
select b.user_name, b.over, a.over from user1 a right join user2 b on a.user_name=b.user_name;
结果: DSC0006.jpg
c.全外连接:full join 或 full outer join
  (1)完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
(2)右外连接维恩图: DSC0007.jpg
(3)sql语句
select a.id, a.user_name, b.over from user1 a full join user2 b on a.user_name=b.user_name
在mysql中查询全连接会报1064的错误,mysql不支持全连接查询,代替语句:
select a.user_name,a.over,b.over from user1 a left join user2 b on a.user_name = b.user_name union all select b.user_name,b.over ,a.over from user1 a right join user2 b on a.user_name = b.user_name;
结果:
DSC0008.jpg
3. 笛卡尔连接(交叉连接)
  1.概念:没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。(user1和user2交叉连接产生4*4=16条记录)
2.交叉连接:cross join (不带条件on)
3.sql语句:
select a.user_name,b.user_name, a.over, b.over from user1 a cross join user2 b;
二.使用技巧
1. 使用join更新表
  我们使用下面语句将user1表中同时存在user1表和user2表中记录的over字段更新为 ‘qtda’。
update user1 set over=’qtds’where user1.user_name in (select b.user_namefrom user1 a inner join user2 b on a.user_name = b.user_name);
这条语句在sql server, oracle中都可以正确执行,在mysql却报错,mysql不支持更新子查询的表,那么我们使用下面语句可以在做到。
update user1 a join (select b.user_name from user1 a join user2 b on a.user_name = b.user_name) b on a.user_name = b.user_name set a.over = ‘qtds’
2. 使用join优化子查询
  子查询效率比较低效,使用下面语句进行查询
select a.user_name, a.over,(select over from user2 b where a.user_name=b.user_name) as over2 from user1 a;
使用join优化子查询,可以实现同样的效果
select a.user_name, a.over, b.over as over2 from user1 a left join user2
b on a.user_name = b.user_name;
3. 使用join优化聚合子查询
  引入一张新表:user_kills
create table user_kills(user_id int, timestr varchar(20), kills int(10));
insert into user_kills values(2, ‘2015-5-12’, 20);
insert into user_kills values(2, ‘2015-5-15’, 18);
insert into user_kills values(3, ‘2015-5-11’, 16);
insert into user_kills values(3, ‘2015-5-14’, 13);
insert into user_kills values(3, ‘2015-5-16’, 17);
insert into user_kills values(4, ‘2015-5-12’, 16);
insert into user_kills values(4, ‘2015-5-10’, 13);
查询user1中每人对应user_kills表中kills最大的日期,使用聚合子查询语句:
select a.user_name,b.timestr, b.kills from user1 a join user_kills b on a
.id = b.user_id where b.kills = (select MAX(c.kills) from user_kills c where c.user_id = b.user_id);
使用join优化聚合子查询(避免子查询)
select a.user_name, b.timestr, b.kills from user1 a join user_kills b on
a.id = b.user_id join user_kills c on c.user_id = b.user_id group by a.user_name, b.timestr, b.kills having b.kills = max(c.kills);
结果:
DSC0009.jpg
4. 实现分组选择数据
  要求查询出user1中每个人kills对多的前两天。
首先,我们可以通过下面语句查询出某个人kills最多的两天;
select a.user_name, b.timestr, b.kills from user1 a join user_kills b on
a.id = b.user_id where a.user_name =’sunwukong’ order by b.kills desc limit 2;
那么如何通过一个语句查询出所有人kills最多的两天的呢?看下面的语句:
WITH tmp AS (select a.user_name, b.timestr, b.kills, ROW_NUMBER() over(partition by a.user_name order by b.kills) cnt from user1 a join user_kills b on a.id = b.user_id) select * from tmp where cnt <= 2;
上面的语句在sql server和oracle都是支持的,但是mysql不支持分组排序函数ROW_NUMBER(),下面提供一种替代方法:
select d.user_name,c.timestr, kills from (select user_id, timestr, kills, (select count(*) from user_kills b where b.user_id = a.user_id and a.kills <= b.kills) as cnt from user_kills a group by user_id, timestr, kills) c join user1 d on c.user_id = d.id where cnt <= 2;
结果:
DSC00010.jpg

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-393818-1-1.html 上篇帖子: sql中的 开窗函数over() 聚合函数 排名函数 下篇帖子: SQL分页查询的几种方式
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表