mysql学习之join从句

一、join从句共有5种类型

• 内连接（inner join）
• 全外连接（full outer join）
• 左外连接（left outer join）
• 右外连接（right outer join）
• 交叉连接（cross join）

二、演示案例

新建两张表user1表和user2表

CREATE TABLE `user2` (`id` int(11),`user_name` varchar(255),`over` varchar(255) ,PRIMARY KEY (`id`)) ;
INSERT INTO `user2` VALUES ('1', '孙悟空', '成佛');
INSERT INTO `user2` VALUES ('2', '牛魔王', '被降服');
INSERT INTO `user2` VALUES ('3', '蛟魔王', '被降服');
INSERT INTO `user2` VALUES ('4', '鹏魔王', '被降服');

CREATE TABLE `user1` (`id` int(11),`user_name` varchar(255),`over` varchar(255),PRIMARY KEY (`id`)) ;
INSERT INTO `user1` VALUES ('1', '唐僧', '檀功德佛');
INSERT INTO `user1` VALUES ('2', '猪八戒', '净坛使者');
INSERT INTO `user1` VALUES ('3', '孙悟空', '斗战胜佛');
INSERT INTO `user1` VALUES ('4', '沙僧', '金身罗汉');

            

内连接：

将两张表的列组合在一起，产生新的结果表

问题：查询出同时存在于user1表和user2表中的数据

方法：

select a.user_name, a.over, b.user_name, b.over from user1 a inner join user2 b on a.user_name = b.user_name;

左外连接：

问题：查询user1表中哪些人不在user2表中

方法：

select a.user_name, a.over, b.user_name, b.over from user1 a left join user2 b on a.user_name = b.user_name where b.user_name is null;

右外连接：

问题：查询user2表中哪些人不在user1表中

方法：

select a.user_name, a.over, b.user_name, b.over from user1 a right join user2 b on a.user_name = b.user_name where a.user_name is null;

全连接：

mysql中并不支持直接使用full join，需要通过间接的方式实现，使用union all实现查询user1表和user2表中所有数据集合

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

交叉连接：

又称笛卡尔连接

select a.user_name, a.over, b.user_name, b.over from user1 a cross join user2 b

三、使用join优化

使用join更新表
问题：把同时存在于user1表和user2表中的记录的人在user1表中的over字段更新为‘齐天大圣’

update user1 a join (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 = '齐天大圣'

使用join优化聚合子查询

这里我们需要另外再创建一张表记录打怪数量和日期

CREATE TABLE `user_kills` (`id` int(11),`user_id` int(11),`timestr` varchar(255) ,`kills` int(11), PRIMARY KEY (`id`)) ;

问题：查询每个人打怪最多的日期
方法一：

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);

子查询效率低

方法二：

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 b.user_id=c.user_id group by a.user_name,b.timestr,b.kills HAVING b.kills=MAX(c.kills);

分组选择查询

问题：查询每个人打怪最多的两个日期

SELECT a.user_name, b.timestr, b.kills FROM user1 a JOIN user_kills b ON a.id = b.user_id WHERE (SELECT COUNT(c.id) FROM user_kills c where c.user_id = b.user_id and c.kills >= b.kills) <= 2 ORDER BY a.id;