# 准备数据 ~~~ -- 新建 user1 表 create table if not exists user1 ( id smallint unsigned primary key auto_increment, user_name varchar(40), over varchar(40) ); -- 新建 user2 表 create table if not exists user2 ( id smallint unsigned primary key auto_increment, user_name varchar(40), over varchar(40) ); -- 新建 user_kills 表 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 ); insert into user1 (user_name,over) values ('唐僧','功德佛'),('猪八戒','净坛使者'),('孙悟空','斗战胜佛'),('沙僧','金身罗汉'); insert into user2 (user_name) values ('孙悟空','成佛'),('牛魔王','被降服'),('蛟魔王','被降服'),('鹏魔王','被降服'),('狮驼王','被降服'); insert into user_kills (user_id,timestr,kills) values ('2','2017-01-10 00:00:00','10'),('2','2017-02-01 00:00:00','2'),('2','2017-02-05 00:00:00','12'),('4','2017-01-10 00:00:00','3'),('2','2017-02-11 00:00:00','5'),('2','2017-02-06 00:00:00','1'),('3','2017-01-11 00:00:00','20'),('2','2017-02-12 00:00:00','10'),('2','2017-02-07 00:00:00','17'); ~~~ # 全连接 MySQL不支持FULL JOIN 解决方法： 通过UNION将左连接和右连接的结果合并 ~~~ SELECT select_expressions FROM TABLE A LEFT OUTER JOIN TABLE B ON A.key=B.key UNION ALL SELECT select_expressions FROM TABLE A RIGHT OUTER JOIN TABLE B ON A.key=B.key; ~~~ **UNION会自动删除重复项，刷选出部分字段的时候要注意了，如果刷选出来的两个字段的值完全相同就会剩下一个。** **UNION ALL 和 UNION 不同之处在于 UNION ALL 会将左右两个查询的结果的所有的资料都列出来，无论资料值有无重复。**  # 内连接 join的内连接是抽取两张表的公共部分 内连接（inner join） 可以选取A表和B表的交集 select 字段 from A表 a表别名 inner join B表 b表别名 on a.字段=b.字段； # 左外连接 not in 关键字不会使用索引 所以用left join加上关键字为null查找只存在于A表不存在于B表的数据  LEFT OUTER JOIN：包含左表中的所有数据，当某个连接谓词不在右表中时，新生成的表的右表字段为NULL。对左表数据的信息进行扩展，增加右表中的字段，当某个连接谓词不在右表中时，新生成的表的右表字段为NULL。 使用LEFT OUTER JOIN对NOT IN 进行优化。 # 右外连接 RIGHT OUTER JOIN：包含右表中的所有数据，当某个连接谓词不在左表中时，新生成的表的左表字段为NULL。对右表数据的信息进行扩展，增加左表中的字段，当某个连接谓词不在左表中时，新生成的表的左表字段为NULL。 使用RIGHT OUTER JOIN对NOT IN 进行优化。 ~~~ SELECT select_sxpressions FROM TABLE A RIGHT OUTER JOIN TABLE B ON A.key=B.key WHERE A.key IS NULL; ~~~ 表B中不包含表A中的数据。 ~~~ SELECT select_sxpressions FROM TABLE A RIGHT OUTER JOIN TABLE B ON A.key=B.key WHERE A.key IS NOT NULL; ~~~ 表A与表B的交集，相当于INNER JOIN。  # 交叉连接 CROSS JOIN：笛卡尔连接，如果A和B是两个集合，它们的交叉连接就记为A X B。一个表的每个记录与另一个表的所有记录进行连接，形成`A.length*B.length`个数据项的表。（无需提供ON） ~~~ SELECT select_expressions FROM TABLE A CROSS JOIN TABLE B; ~~~ 在实际的使用中，要尽量避免产生笛卡尔积的查询，因为很少情况我们会使用到笛卡尔积的查询结果，但是在一些特殊情况下，我们需要笛卡儿积查询产生我们需要的结果。 # join更新表  先select出同一表中的某些值，再update这个表(在同一语句中) mysql不支持这种,不能更新出现在from从句中,where后面你用了更新表user1作为子查询表这是不容许的,其他的一些sql支持 ~~~ 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='齐天大圣'; ~~~ 也就是说将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于mysql，mssql和oracle不会出现此问题。 # 子查询 ## join优化子查询技巧：  一般子查询写法：(数据小时，没有多大影响，如果数据量大时，则要消耗大量的查询) ~~~ select a.user_name , a.over , (select over from user2 b 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优化聚合子查询 ~~~ -- 如何查询打怪最多的日期 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 inner join user_kills b on a.id=b.user_id inner join user_kills c on c.user_id=b.user_id group by a.user_name,b.timestr,b.kills having b.kills = max(c.kills); ~~~ ## 分组选择数据 分组选择解决的问题？ 如果说在几组数据中寻找最大或者最小的记录那么很好找，可以用聚合函数达到目的 ，但是如果要在分类中选择几条记录那么就需要分组选择了 比如在取经四人组中查出每人前2天的杀怪记录  采用ROW_NUMBER函数，进行分区排序 使用WITH 表名 AS ()，不过MySQL中不支持ROW_NUMBER() OVER(PARTITION BY 字段名 ) row_number() OVER(PARTITION BY)与 rank() OVER(PARTITION BY) 区别：前者有两个并列第一则返回一条记录，后者返回两条，同时是跳跃排序；dense_rank()是连续排序！！（mySql中不支持这种查询方法） 可以通过这个方法来实现  还有个