Mysql連接用法及效率分析

1、Mysql連接用法及效率分析Newly compiled on以MySql為例。在MySQL數(shù)據(jù)庫(kù)中建立兩張數(shù)據(jù)表，并分別插入一些數(shù)據(jù)。示例腳本如下：1 droptabletable1;2 CREATETABLEandrews、tablel3 (4 、name、VARCHAR(32)NOTNULL,5 、city、VARCHAR(32)NOTNULL6 )7 ENGINE=MyISAM;8 insertintoTABLEl. (name, city) values ( PersonA . *BJ);9 insertintoTABLE, (name, city) values (1PersonB

2、rBJ);10 insertintoTABLEl. (name, city) values ( PersonC *r SH); insertintoTABLEl. (name, city) values ( PersonDr1SZ );12 commit;13 droptabletabk2;14 CREATETABLE4andrew、table2%15 (16、nameVARCHAR(32)NOTNULL.17、cityVARCHAR32)NOTNULL18 )19 ENGINE=MyISAM;20 insertintoTABLE2(name,city)values(PersonWrBJ);2

3、1 insertintxTABLE2(name,city)values(PersonX . SH);22 insertintxTABLE2(name,city)values(PerscnY1r1SH);23 insertintxTABLE2(name,city)values(PersonZ, NJ);24 commit;1. MySQL外連接-左連接結(jié)果table 1居左，故謂之左連接。這種情況下,以tablel為主,B|J table 1中的所有記錄均 會(huì)被列出。有一下三種情況：MySQL左連接右連接和內(nèi)連接詳解a.對(duì)于tablel中的每一條記錄對(duì)應(yīng)的城市如果在table2中也恰好存在而且剛

4、好只有 條，那么就會(huì)在返回的結(jié)果中形成一條新的記錄。如上面Person A和Person B對(duì)應(yīng)的情況。b.對(duì)于table 1中的每一條記錄對(duì)應(yīng)的城市如果在table2中也恰好存在而且有N條，那么就會(huì)在返回的結(jié)果中形成N條新的記錄。如上面的Person C對(duì)應(yīng)的情況。c.對(duì)于table 1中的每一條記錄對(duì)應(yīng)的城市如果在table2中不存在，那么就會(huì)在返回 的結(jié)果中形成一條條新的記錄，且該記錄的右邊全部NULL。如上面的Person D對(duì)應(yīng)的情況。不符合上面三條規(guī)則的記錄不會(huì)被列出。2. MySQL外連接-右連接結(jié)果table2居右，故謂之右連接。這種情況下,以table2為主,即table2中

5、的所有記錄均 會(huì)被列出。有一下三種情況：a.對(duì)于table2中的每一條記錄對(duì)應(yīng)的城市如果在table 1中也恰好存在而且剛好只有 條，那么就會(huì)在返回的結(jié)果中形成一條新的記錄。如上面Person X和Person Y對(duì)應(yīng)的情況。b.對(duì)于table2中的每一條記錄對(duì)應(yīng)的城市如果在table 1中也恰好存在而且有N條，那么就會(huì)在返回的結(jié)果中形成N條新的記錄。如上面的Person W對(duì)應(yīng)的情況。c.對(duì)于table2中的每一條記錄對(duì)應(yīng)的城市如果在tablel中不存在，那么就會(huì)在返回 的結(jié)果中形成一條條新的記錄，且該記錄的左邊全部NULL。如上面的Person Z對(duì)應(yīng)的情況。不符合上面三條規(guī)則的記錄不會(huì)被

6、列出。3. MySQL內(nèi)連接MySQL內(nèi)連接的數(shù)據(jù)記錄中，不會(huì)存在字段為NULL的情況?？梢院?jiǎn)單地認(rèn)為， 內(nèi)鏈接的結(jié)果就是在左連接或者右連接的結(jié)果中剔除存在字段為NULL的記錄后所得到 的結(jié)果。 其至可以認(rèn)為，如果兩個(gè)表中僅分別剩下內(nèi)連接運(yùn)算后所得的數(shù)據(jù)記錄，如table 1中只有PersonA、Person B和Person C, table2中只有Person W、Person X和Person Y,那么這兩個(gè)表的之間的左連接和右連接的返回的結(jié)果是一樣的。注意：select * from table 1 a inner join table2 b on= 和select * from ta

7、ble 1 a join table2 bon=的效果是一樣的，即如果join的左邊沒有諸如left, right或者inner這樣的關(guān)鍵 字時(shí)，缺省的是內(nèi)連接。另外，MySQL不支持fulljoinoMySQL 的聯(lián)結(jié)(Join)語(yǔ)法1.內(nèi)聯(lián)結(jié)、外聯(lián)結(jié)、左聯(lián)結(jié)、右聯(lián)結(jié)的含義及區(qū)別:在講 MySQL 的 Join 語(yǔ)法前還是先回顧下聯(lián)結(jié)的語(yǔ)法，呵呵，其實(shí)連我自己都忘得差不多了，那就 大家起溫習(xí)吧（如果內(nèi)容有錯(cuò)誤或有疑問，可以來(lái)信咨詢：陳朋奕 chenpengyiS）,國(guó)內(nèi)關(guān)于 MySQL 聯(lián)結(jié)查詢的資料十分少，相信人家在看了本文后會(huì)對(duì) MySQL 聯(lián)結(jié)語(yǔ)法有相當(dāng)清晰的了解，也不會(huì)被 Oracl

8、e 的外聯(lián)結(jié)的（“ + ”號(hào)）弄得糊涂了。在 SQL 標(biāo)準(zhǔn)中規(guī)劃的（Join）聯(lián)結(jié)人致分為下面四種：1.內(nèi)聯(lián)結(jié)：將兩個(gè)農(nóng)中存在聯(lián)結(jié)關(guān)系的字段符合聯(lián)結(jié)關(guān)系的那些記錄形成記錄集的聯(lián)結(jié)。2.外聯(lián)結(jié)：分為外左聯(lián)結(jié)和外右聯(lián)結(jié)。左聯(lián)結(jié) A、B 衣的盤思就是將農(nóng) A 中的全部記錄和農(nóng) B 中聯(lián)結(jié)的字段與衣 A 的聯(lián)結(jié)字段符合聯(lián)結(jié)條 件的那些記錄形成的記錄集的聯(lián)結(jié)，這里注總的是最后岀來(lái)的記錄集會(huì)包括衣 A 的全部記錄。 右聯(lián)結(jié) A、B 衣的結(jié)果和左聯(lián)結(jié) B、A 的結(jié)果是樣的，也就是說(shuō)：Select From A Left Join B On =和 Select From B Right Join A on =

9、執(zhí)行后的結(jié)果是樣的。3. 全聯(lián)結(jié)：將兩個(gè)衣中存在聯(lián)結(jié)關(guān)系的字段的所有記錄取出形成記錄集的聯(lián)結(jié)（這個(gè)不需要記憶，只耍是查詢中提到了的農(nóng)的字段都會(huì)取出，無(wú)論是否符合聯(lián)結(jié)條件，因此意義不人）。4. 無(wú)聯(lián)結(jié)：不用解釋了吧，就是沒有使用聯(lián)結(jié)功能唄，也有自聯(lián)結(jié)的說(shuō)法。這里我有個(gè)比較簡(jiǎn)便的記憶方法，內(nèi)外聯(lián)結(jié)的區(qū)別是內(nèi)聯(lián)結(jié)將去除所有不符合條件的記錄，而外聯(lián)結(jié) 則保留其中部分。夕卜左聯(lián)結(jié)與外右聯(lián)結(jié)的區(qū)別在于如果用 A 左聯(lián)結(jié) B 則 A 中所有記錄都會(huì)保留在結(jié)果 中，此時(shí) B 中只有符合聯(lián)結(jié)條件的記錄，而右聯(lián)結(jié)相反，這樣也就不會(huì)混淆了。其實(shí)人家回憶高等教育 出版社出版的數(shù)據(jù)庫(kù)系統(tǒng)概論書中講到關(guān)系代數(shù)那章（就是將

10、笛卡兒積和投影那章的內(nèi)容，相信 不難理解這些聯(lián)結(jié)功能的內(nèi)涵。2. MySQL聯(lián)結(jié)（Join）的語(yǔ)法MySQL 支持 Select 和某些 Update 和 Delete 情況下的 Join 語(yǔ)法，具體語(yǔ)法上的細(xì)節(jié)有：table_references:table_reference , table_referenceZ table_reference:table_factorjoin_tabletable_factor:tbl name AS alias!USE IGNORE FORCE INDEX (key.list)(table_references ) OJ table_reference

11、 LEFT OUTER JOIN table_referenceON conditional_expr join_table:table_reference INNER CROSS JOIN table_factor ljoin_conditiontable_reference STRAIGHT_JOIX tablefactor table_reference STRAIGHT_JOIN table_factor ONconditiontable_reference LEFT OUTER JOIN table_reference join_conditiontable.reference NA

12、TURAL LEFT OUTER JOIN table.factorRIGHT OUTER JOIN table_reference join_conditionNATURAL RIGHT OUTER JOIN table.factorjoin_condition:ON conditional_expr USING (columnist)上面的用法摘 Pl 權(quán)威資料，不過(guò)人家看了是否有點(diǎn)暈?zāi)睾呛?，?yīng)該問題主要還在于 table_reference 是什么，table_factor 又是什么這里的 table_reference 其實(shí)就是衣的引用的意思，因?yàn)樵?MySQL 看 來(lái)，聯(lián)結(jié)就是-種對(duì)

13、表的引用， 因此把需要聯(lián)結(jié)的我定義為 table_reference， 同時(shí)在 SQL Standard 中 也是如此看待的。 而 table_factor則是 MySQL 對(duì)這個(gè)引用的功能上的增強(qiáng)和擴(kuò)充，使得引用的衣可以是 括號(hào)內(nèi)的系列表,如下面例子中的 JOIN 后面括號(hào):SELECT * FROM tl LEFT JOIN (t2, t3, t4) ON = AND = AND =這個(gè)語(yǔ)句的執(zhí)行結(jié)果和下面語(yǔ)句其實(shí)是樣的：SELECT * FROM tl LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)ON = AND = AND =這兩個(gè)例不僅讓我們了

14、解了 MySQL 中 table_factor 和 table.reference 含義，同時(shí)能理解點(diǎn)CROSS JOIN 的用法，我要補(bǔ)充的是在 MySQL 現(xiàn)有版本中 CROSS JOIN 的作用和 INNER JOIN 是樣的(雖 然在 SQLStandard 中是不樣的，然而在 MySQL 中他們的區(qū)別僅僅是 INNER JOIN 需要附加皿參數(shù)的語(yǔ) 句，而 CROSS JOIN 不需要)。既然說(shuō)到了 ON 語(yǔ)句，那就解釋下吧，ON 語(yǔ)句其實(shí)和 WHERE 語(yǔ)句功能人致相當(dāng)，只是這里的 0、語(yǔ) 句是專門針對(duì)聯(lián)結(jié)衣的， 0N語(yǔ)句后面的條件的要求和書寫方式和WHERE語(yǔ)句的要求是樣的， 人

15、家基本 上可以把0N當(dāng)作WHERE用。table_referencetable_reference人家也許也看到了 OJ table_reference LEFT OUTER JOIN table_reference 這個(gè)句 & 這不是 MySQL 的標(biāo)準(zhǔn)寫法，只是為了和 ODBC 的 SQL 語(yǔ)法兼容而設(shè)定的，我很少用，Java 的人更是不會(huì)用，所以 也不多解釋了。那下面就具體講講簡(jiǎn)單的 JOIN 的用法了。首先我們假設(shè)有 2 個(gè)農(nóng) A 和 B.他們的農(nóng)結(jié)構(gòu)和字段分別 為：衣 A：IDName1Tim2Jimmy3John1Tom衣 B：IDHobby1Football2Basket

16、ball2Tennis1Soccer1.內(nèi)聯(lián)結(jié)：Select from A, B where =,這是隱式的內(nèi)聯(lián)結(jié)，査詢的結(jié)果是:NameHobbyTimFootballJimmyBasketballJimmyTennisTomSoccer它的作用和 Select from A INNER JOIN B ON =是樣的。這里的 INNER JOIN 換成 CROSS JOIN 也是 可以的。2.外左聯(lián)結(jié)Select from A Left JOIN B ON 二，典型的外左聯(lián)結(jié)，這樣查詢得到的結(jié)果將會(huì)是保留所有 A 農(nóng)中聯(lián)結(jié)字段的記錄，若無(wú)與其相對(duì)應(yīng)的 B 衣中的字段記錄則留空.結(jié)果如下:Na

17、meHobbyTimFootballJimmyBasketball, TennisJohnTomSoccer所以從上啲結(jié)果看 ilh W 為 A 衣中的 John 記錄的 ID 沒有在 B 農(nóng)中有對(duì)應(yīng) ID,因此為空，但 Name 欄仍有John 記錄。3.外右聯(lián)結(jié)如果把上而查詢改成外右聯(lián)結(jié)：Select from A Right JOIN B ON 二，則結(jié)果將會(huì)是:NameHobbyTimFootballJimmyBasketballJimmyTennisTomSoccer這樣的結(jié)果都是我們可以從外左聯(lián)結(jié)的結(jié)果中猜到的了。說(shuō)到這里人家是否對(duì)聯(lián)結(jié)査詢了解多了這個(gè)原本看來(lái)高深的概念下就理解了,

18、恍然大悟了吧 (呵呵，開玩笑了)最后給人家講講 MySQL 聯(lián)結(jié)査詢中的某些參數(shù)的作用：1.USING (column_list)：其作用是為了方便書寫聯(lián)結(jié)的多對(duì)應(yīng)關(guān)系，人部分情況下 USING 語(yǔ)句可 以用泊語(yǔ)句來(lái)代替，如下面例了：a LEFT JOIN b USING (cl,c2,c3),其作用相當(dāng)于下面語(yǔ)句a LEFT JOIN b ON = AND = AND =只是用 ON 來(lái)代替會(huì)書寫比較麻煩而已。2.NATURAL LEFT JOIN：這個(gè)句了的作用相當(dāng)于 INNER JOIN,或者是在 USING(句中包含了聯(lián)結(jié) 的衣中所有字段的 Left JOIN (左聯(lián)結(jié))。3.STRA

19、IGHT_JOIN： 由于默認(rèn)情況下 MySQL 在進(jìn)行衣的聯(lián)結(jié)的時(shí)候會(huì)先讀入左農(nóng)， 當(dāng)使用了這個(gè)參 數(shù)后 MySQL將會(huì)先讀入右農(nóng)，這是個(gè) MySQL 的內(nèi)置優(yōu)化參數(shù)，人家應(yīng)該在特定情況下使用，譬如已經(jīng)確 認(rèn)右衣中的記錄數(shù)雖少，在篩選后能人人捉高查詢速度。最后要說(shuō)的就是，在以后，運(yùn)算順序得到了重視，所以對(duì)多農(nóng)的聯(lián)結(jié)査詢可能會(huì)錯(cuò)謀以了聯(lián)結(jié)査詢 的方式進(jìn)行。譬如你需要進(jìn)行多衣聯(lián)結(jié)，肉此你輸入了下面的聯(lián)結(jié)查詢：SELECT ,FROM tl, t2LEFT JOIN t3 ON =WHERE =;但是 MySQL 并不是這樣執(zhí)行的，其后臺(tái)的真正執(zhí)行方式是下而的語(yǔ)句：SELECT ,FROM tl,

20、 ( t2 LEFT JOIN t3 ON =)WHERE 二；這并不是我們想要的效果，所以我們需要這樣輸入：SELECT ,FROM (tl, t2)LEFT JOIN t3 ON =WHERE =;在這里括號(hào)是相當(dāng)重要的，因此以后在寫這樣的查詢的時(shí)候我們不耍忘記了多寫幾個(gè)括號(hào)，至少這 樣能避免很多錯(cuò)誤 （因?yàn)檫@樣的錯(cuò)誤是很難被開發(fā)人員發(fā)現(xiàn)的 。 如果對(duì)上面內(nèi)容有疑問可以來(lái)信查 詢： 陳朋奕 chenpengyi#,轉(zhuǎn)載請(qǐng)注明出處及作者。MySQL left join聯(lián)合查詢的效率分析2010-05-21 14:36 佚名博客園摘要：我們今天主婆向大家介紹的是 MySQL left join

21、 聯(lián)合査詢的效率分析以及在實(shí)際操作中值得我們大 家注意的爭(zhēng)項(xiàng)的描述。標(biāo)簽：以下的文章主要講述的是left join聯(lián)合查詢的效率分析，我在一個(gè)信譽(yù)度很好的網(wǎng) 站找到一個(gè)關(guān)于MySQL left join聯(lián)合查詢的效率分析的資料，今天拿出來(lái)供大家分享, 希望會(huì)給你帶來(lái)一些幫助在此方面。user表：代碼:id I name1 llibk2 I zyfon3 Idaodaouser_action表：代碼：user_id I action1 I jump1 I kick1 I jump2 I run4 I swim代碼:select id, name, action from user as ulef

22、t join user_action a on =result:代碼：id I name I action1 I libk I jump1 I libk I kick1 I libk I jump2 I zyfon I run3 I daodao I null分析：注意到user_action中還有一個(gè)user_id=4, action=swim的紀(jì)錄，但是沒有在結(jié)果中出 現(xiàn)，而user表中的id=3, name=daodao的用戶在user_action中沒有相應(yīng)的紀(jì)錄，但是卻 出現(xiàn)在了結(jié)果集中因?yàn)楝F(xiàn)在是MySQL left join,所有的工作以left為準(zhǔn).結(jié)果1, 2, 3, 4都是既

23、在左表乂在右表的紀(jì)錄，5是只在左表，不在右表的紀(jì)錄結(jié)論：我們可以想象MySQL left join是這樣工作的從左表讀出一條，選岀所有與on匹配的右表紀(jì)錄（n條）進(jìn)行連接，形成n條紀(jì)錄（包 括重復(fù)的行，如：結(jié)果1和結(jié)果3）,如果右邊沒有與on條件匹配的表，那連接的字段都是null.然后繼續(xù)讀下一條。引申：我們可以用右表沒有on匹配則顯示null的規(guī)律，來(lái)找出所有在左表，不在右表的紀(jì) 錄，注意用來(lái)判斷的那列必須聲明為not null的。如:代碼:1selectid neime, actlonf romuserasu2leftjoinuser_actionaon=3 whereisNULL(注意：

24、1.列值為null應(yīng)該用is null而不能用=NULL2.這里列必須聲明為NOT NULL的)代碼：1 result:2 id I name action33 1daodaoI NULLTips:1. on=等同于using(cl)2. INNER JOIN和，(逗號(hào))在語(yǔ)義上是等同的3當(dāng)MySQL在從一個(gè)表中檢索信息時(shí)，你可以提示它選擇了哪一個(gè)索引。如果EXPLAIN顯示MySQL使用了可能的索引列表中錯(cuò)誤的索引，這個(gè)特性將是 很有用的。通過(guò)指定USE INDEX (keyjist),你可以告訴MySQL使用可能的索引中最合適的一 個(gè)索引在表中查找記錄行?？蛇x的二選一句法IGNORE IN

25、DEX (keyjist)可被用于告訴MySQL不使用特定的索 引。4.些例子：代碼：1.MySQLSELECT*FROMtablel,table2WHERE=;2.MySQLSELECT*FROMtablelLEFTJOINtable2ON=;3 .MySQLSELECT*FROMtablelLEFTJOINtable2USING(id);4.MySQLSELECT*FROMtablelLEFTJOINtable2ON=5.-LEFTJOINtable3ON=;6.MySQLSELECT* FROMtable1USEINDEX(keyl,key2)7.-WHEREkeyl=lANDkey2=2ANDkey3=3;8.MySQLSELECT*FROMtab丄elIGNOREINDEX(key3)9.-WHEREkeyl=lANDkey2=2ANDkey3=3;如何優(yōu)化LEFT JOIN和RIGHT JOIN在MySQL中，A LEFT JOIN B join.condition執(zhí)行過(guò)程如下：根據(jù)表A和A依賴的所有表設(shè)置表Bo根據(jù)MySQL LEFT JOIN條件中使用的所有表（除了B）設(shè)置表A。LEFT JOIN條件用