Mysql开发技巧（1）join的使用

一：内连接：取两个表的公共部分

内连接inner join:

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

二：左外连接

例如：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

五：笛卡尔积

使用技巧（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