# 準備數據 ~~~ -- 新建 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中不支持這種查詢方法） 可以通過這個方法來實現  還有個