一、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;
