目录
0、警醒自己
一、SQL标准中Join的类型
1、含义解释
二、Inner Join(内连接)
1、概述
2、语法格式
3、使用演示
相关表:
相关表的SQL语句:
查询需求:
SQL语句:
执行结果:
三、Left Outer Join(左外连接)
1、概述
2、语法格式
3、补充
图示:
语法格式:
4、演示
SQL语句:
执行结果:
另一句SQL语句:
执行结果:
还有一句SQL语句:
执行结果:
四、RightOuter Join(右外连接)
1、概述
2、语法格式
3、演示
SQL语句:
执行结果:
五、Full Join(全连接)
1、概述
2、语法格式
第一种情况:
第二种情况:
3、演示问题
问题:
答案:
4、解决问题
原理:
语法格式:
SQL语句演示:
执行结果:
另一句SQL语句:
执行结果:
六、Cross Join(交叉连接或笛卡尔连接)
1、概述
2、语法格式
3、演示
SQL语句:
执行结果:
七、使用Join更新表
1、需求描述
2、SQL语句实现(存在问题)
3、执行结果
4、解决方案:
5、执行结果:
八、使用Join优化子查询
1、优化前
SQL语句:
执行结果:
2、优化后
SQL语句:
执行结果(据说效率更高了):
九、使用Join优化聚合子查询
1、打怪表相关SQL
2、需求描述
3、演示
SQL语句:
执行结果:
优化后的SQL语句:
执行结果:
0、警醒自己
1、学习不用心,骗人又骗己;
2、学习不刻苦,纸上画老虎;
3、学习不惜时,终得人耻笑;
4、学习不复习,不如不学习;
5、学习不休息,毁眼伤身体;
7、狗才等着别人喂,狼都是自己寻找食物;
一、SQL标准中Join的类型
1、含义解释
①内连接仅选出两张表中相互匹配的记录,外链接会选出其他不匹配的记录,最常用的是内连接;
②左外连接:包含所有左表中的记录,甚至是右表中没有和它匹配的记录;
③右外连接:包含所有右表中的记录,甚至是左表中没有和它匹配的记录;
二、Inner Join(内连接)
1、概述
仅选出两张表中相互匹配的记录;
(join和inner join没有区别)
基于链接谓词将两张表(如A和B)的列组合在一起,产生新的结果表;
(红色代表的是A和B共同拥有的字段,比如id、username等等,相当于将两张表组成一张表,再根据条件进行查询)
2、语法格式
select <select_list> from TableA A inner join TableB B on A.Key = B.Key
3、使用演示
相关表:
相关表的SQL语句:
-- 创建user1表CREATE TABLE `user1` (`id` int(11) NOT NULL COMMENT '主键',`user_name` varchar(255) DEFAULT NULL COMMENT '姓名',`over` varchar(255) DEFAULT NULL COMMENT '结局',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 插入数据到user1表INSERT INTO `zibo`.`user1`(`id`, `user_name`, `over`) VALUES (1, '唐僧', '旃檀功德佛');INSERT INTO `zibo`.`user1`(`id`, `user_name`, `over`) VALUES (2, '猪八戒', '净坛使者');INSERT INTO `zibo`.`user1`(`id`, `user_name`, `over`) VALUES (3, '孙悟空', '斗战胜佛');INSERT INTO `zibo`.`user1`(`id`, `user_name`, `over`) VALUES (4, '沙僧', '金身罗汉');-- 创建user2表CREATE TABLE `user2` (`id` int(11) NOT NULL COMMENT '主键',`user_name` varchar(255) DEFAULT NULL COMMENT '姓名',`over` varchar(255) DEFAULT NULL COMMENT '结局',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 插入数据到user2表INSERT INTO `zibo`.`user2`(`id`, `user_name`, `over`) VALUES (1, '孙悟空', '成佛');INSERT INTO `zibo`.`user2`(`id`, `user_name`, `over`) VALUES (2, '牛魔王', '被降服');INSERT INTO `zibo`.`user2`(`id`, `user_name`, `over`) VALUES (3, '蛟魔王', '被降服');INSERT INTO `zibo`.`user2`(`id`, `user_name`, `over`) VALUES (4, '鹏魔王', '被降服');INSERT INTO `zibo`.`user2`(`id`, `user_name`, `over`) VALUES (5, '狮驼王', '被降服');
查询需求:
同时是取经四人表与孙悟空和他的结拜兄弟表中的人的最终结局(同时在A、B两表中的实体的数据);
(两个表:A表和B表,A表中有孙悟空,B表中也有孙悟空,我们将A和B表连接组合,肯定是满足某一条件的数据进行整合,否则杂乱的数据无法排列,这个条件可以是名字相同,也可以是年龄相同等等)
SQL语句:
select a.user_name,a.over,b.over from user1 a inner join user2 b on a.user_name = b.user_name;
执行结果:
三、Left Outer Join(左外连接)
1、概述
包含所有左表中的记录,甚至是右表中没有和它匹配的记录;
2、语法格式
select <select_list> from TableA A left join TableB B on A.Key = B.Key
3、补充
图示:
语法格式:
select <select_list> from TableA A left join TableB B on A.Key = B.Key where B.key is null
4、演示
SQL语句:
select a.user_name,a.over,b.over from user1 a left join user2 b on a.user_name = b.user_name
执行结果:
另一句SQL语句:
select a.user_name,a.over,b.over from user1 a left join user2 b on a.user_name = b.user_name WHERE b.user_name is null;
执行结果:
还有一句SQL语句:
select a.user_name,a.over,b.over from user1 a left join user2 b on a.user_name = b.user_name WHERE b.user_name is not null;
执行结果:
四、RightOuter Join(右外连接)
1、概述
包含所有右表中的记录,甚至是左表中没有和它匹配的记录;
2、语法格式
select <select_list> from TableA A right join TableB B on A.Key = B.Key;
3、演示
(左连接和有连接正好是相反的,所以非常简单,咱们不上图了,直接演示;)
SQL语句:
select b.user_name,b.over,a.over from user1 a right join user2 b on a.user_name = b.user_name;
执行结果:
五、Full Join(全连接)
1、概述
全连接也就是左连接和右连接的合集;
(第一种情况)
(第二种情况)
2、语法格式
第一种情况:
select <select_list> from TableA A full join TableB B on A.Key = B.Key;
第二种情况:
select <select_list> from TableA A full join TableB B on A.Key = B.Key where a.key is null or b.key is null;
3、演示问题
问题:
select * from user1 a full join user2 b on a.user_name = b.user_name;
答案:
MySQL不支持全连接!
4、解决问题
原理:
全连接 = 左连接 + 有连接;
语法格式:
select <select_list> from TableA A left join TableB B on A.Key = B.Key union all select <select_list> from TableA A right join TableB B on A.Key = B.Key;
SQL语句演示:
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;
执行结果:
另一句SQL语句:
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 where a.user_name is null or b.user_name is null;
执行结果:
六、Cross Join(交叉连接或笛卡尔连接)
1、概述
交叉连接,又称笛卡尔连接,或叉乘,如果A和B是两个集合,他们的交叉连接就是A x B;
集合A中的每一条数据与集合B中的每一条数据进行匹配产生的结果集;
2、语法格式
select * from TableA cross join TablkB;
3、演示
SQL语句:
select * from user1 cross join user2;
执行结果:
七、使用Join更新表
1、需求描述
将同时存在于user1和user2中的人的user1中的over字段更改为“齐天大圣”;
2、SQL语句实现(存在问题)
UPDATE user1 SET over = '齐天大圣' WHEREuser1.user_name IN ( SELECT b.user_name FROM user1 a INNER JOIN user2 b ON a.user_name = b.user_name );
3、执行结果
4、解决方案:
(join和inner join没有区别)
UPDATE user1 aJOIN ( SELECT b.user_name FROM user1 a INNER JOIN user2 b ON a.user_name = b.user_name ) b ON a.user_name = b.user_name SET a.over = '齐天大圣';
5、执行结果:
八、使用Join优化子查询
1、优化前
SQL语句:
SELECTa.user_name,a.over,( SELECT b.over FROM user2 b WHERE a.user_name = b.user_name ) AS over2 FROMuser1 a;
执行结果:
2、优化后
SQL语句:
SELECTa.user_name,a.over,b.over AS over2 FROMuser1 aLEFT JOIN user2 b ON a.user_name = b.user_name;
执行结果(据说效率更高了):
九、使用Join优化聚合子查询
1、打怪表相关SQL
-- 创建打怪表create table if not exists user_kills (id smallint unsigned primary key auto_increment,user_id smallint unsigned,timestr timestamp default CURRENT_TIMESTAMP,kills smallint unsigned);-- 插入 user_kills 表数据insert into user_kills(user_id, timestr, kills) values (2, timestamp('-01-10'), 10);insert into user_kills(user_id, timestr, kills) values (2, timestamp('-02-01'), 2);insert into user_kills(user_id, timestr, kills) values (2, timestamp('-02-05'), 12);insert into user_kills(user_id, timestr, kills) values (4, timestamp('-01-10'), 3);insert into user_kills(user_id, timestr, kills) values (4, timestamp('-02-11'), 5);insert into user_kills(user_id, timestr, kills) values (2, timestamp('-02-06'), 1);insert into user_kills(user_id, timestr, kills) values (3, timestamp('-01-11'), 20);insert into user_kills(user_id, timestr, kills) values (2, timestamp('-02-12'), 10);insert into user_kills(user_id, timestr, kills) values (3, timestamp('-02-07'), 17);
2、需求描述
查询出取经四人组中打怪最多的日期;
3、演示
SQL语句:
SELECTa.user_name,b.timestr,b.kills FROMuser1 aJOIN user_kills b ON a.id = b.user_id WHEREb.kills =(SELECTMAX( c.kills ) FROMuser_kills c WHEREc.user_id = b.user_id );
执行结果:
优化后的SQL语句:
SELECTa.user_name,b.timestr,b.kills FROMuser1 aJOIN user_kills b ON a.id = b.user_idJOIN user_kills c ON c.user_id = b.user_id GROUP BYa.user_name,b.timestr,b.kills HAVINGb.kills = max( c.kills );