![](https://i-blog.csdnimg.cn/blog_migrate/27a267f211519d70c5a10978cb1eea0d.jpeg)
一:内连接:取两个表的公共部分
内连接inner join:
select a.user_name,a.over,b.over from user1 a join user2 b on a.user_name=b.user_name;
二:左外连接
![](https://i-blog.csdnimg.cn/blog_migrate/02e58b96f13febee1ffb7ac3ac602fe4.jpeg)
例如:select A.内容,B.内容 from A left join B ON a.key=b.key where b.key is null。选择只在A不在B的部分
三:右外连接
四:全连接
MySQL 中 不会支持 full join 解决方法:采用左连接和右连接结合+ union all 方法来取得两表的合集 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
![](https://i-blog.csdnimg.cn/blog_migrate/16f155bd565a301796f978faaae22f75.jpeg)
五:笛卡尔积
![](https://i-blog.csdnimg.cn/blog_migrate/530e5a32fce847c658e3e3ff2132a596.jpeg)
使用技巧(1):联合更新
update user1 set over=‘齐天大圣’ where user1.user_name in ( select b.user_name from user1 a join user2 b on a.user_name = b.user_name); ERROR 1093错误 不能更新from语句中出现的表字段 解决办法: 通过使用join进行联合更新 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=‘齐天大圣’;
使用技巧二:join优化子查询技巧: 一般子查询写法:(数据小时,没有多大影响,如果数据量大时,则要消耗大量的查询) select a.user_name , a.voer , (select over from user2 where a.user_name = b,user_name) as over2 from user1 a; 如果这两张表的记录相当多 那么这个子查询相当于对A标的每一条记录都要进行一次子查询。 join优化(左连接)后的写法: select a.user_name , a.over , b.over from user1 a left join user2 b on a.user_name = b.user_name
使用技巧三:使用join + having优化聚合子查询: 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,btimestr,b.kills having b.kills = max(c.kills);使用技巧四: 分组选择
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