鎴戜互鍓嶄粙缁嶈繃鏈€澶氱殑灏辨槸鍗曡〃闅忔満鏌ヨ鏃剁殑浼樺寲浜嗭紝浠婂ぉ 鐪嬪埌涓€绔欓暱鍒嗕韩浜嗕竴涓琛ㄩ殢鏈烘煡璇紭鍖栫殑涓€浜涙柟娉曚笌鏂规锛屼笅闈㈡垜浠氨涓€璧锋潵鐪嬬湅鍚?
鏈枃涓昏璋堣濡備綍瀹炵幇Mysql鐨勯殢鏈烘煡璇紝澶氳〃闅忔満鏌ヨ銆傚湪Mysql涓殢鏈哄彇鍑轰竴鏉¤褰曠殑瀹炵幇鏂规硶銆?/p>
鎴戜滑閫氬父鐨勬煡璇㈡槸娌℃湁where鎴栬€厀here fields>2杩欐牱鐨勬柟寮忥紝杩欐牱鍙兘鍙栧嚭鍦ㄦ煇绉嶆潯浠朵笅鐨勪竴鏉℃垨澶氭潯锛屽鏋滄潯浠朵笉鍙?渚嬪2)锛岄偅涔堢粨鏋滃氨涓€鐩翠笉浼氭湁鍙樺寲銆?/p>
閭ｄ箞濡備綍瀹炵幇闅忔満鏌ヨ鍛?鏈汉鏈変袱绉嶆柟娉曘€?/p>
鏂规硶涓€銆佹暟鎹〃璁板綍涓嶅ぇ鐨勬儏鍐典笅锛?/p>
select * from `table`
鏌ュ嚭鏉ユ墍鏈夌殑璁板綍鍒楄〃锛岀劧鍚巃rray_rand()闅忔満鍑轰竴涓粨鏋滅殑鏁扮粍鐨刱ey銆傝繛缁殑key鍙互浣跨敤mt_rand(1, count($list)); 涓轰粈涔堜笉浣跨敤rand鑰屾槸mt_rand鍛?鍥犱负mt_rand甯乺and蹇?鍊嶃€?/p>
杩欑鎯呭喌涓嬶紝鏌ュ嚭鏁翠釜鍒楄〃锛屽瓨鍏ュ埌Memcache鐨勭紦瀛樻垨鑰匯edis鐨凬oSQL涓紝涓嬫鐩存帴鍙栧嚭缁撴灉闆嗚€屼笉闇€瑕佹煡琛ㄣ€備笉杩囧綋鏁版嵁閲忎竴鏃﹁秴杩囦竾绾у埆锛屽彇鍑哄垪琛ㄥ氨寰堝洶闅句簡銆?/p>
鏂规硶浜岋細浣跨敤SQL璇彞闅忔満
MYSQL鍑芥暟RAND()锛屼骇鐢熶竴涓?-1涔嬮棿鐨勫皬鏁帮紝鐒跺悗MAX(`id`)鍙互鑾峰緱璇ヨ〃涓渶澶х殑ID銆傞偅涔圡AX(`id`) * RAND()灏卞彲浠ュ彇鍒拌〃涓墍鏈夌殑ID銆侽K锛岀湅璇彞銆?/p>
SELECT * FROM `table` WHERE `id` > (SELECT RAND() * (SELECT MAX(`id`) FROM `table`) LIMIT 0, 1
鏃㈢劧MAX(`id`) * MAX(`id`)鍙互鍙栧埌琛ㄩ噷鎵€鏈夊€硷紝閭ｄ箞鏈鍙ョ殑WHERE灏卞彲浠ュ彇鍒版湰琛ㄧ殑鎵€鏈夋儏鍐碉紝閭ｄ箞杩欏氨鏄竴涓墍鏈夎褰曢兘鏈夊彲鑳借鍙栧埌鐨勯殢鏈篠QL璇彞銆?/p>
琛ュ厖鍙︿竴绡囨枃绔?/p>
1. 澶氳〃杩炴帴绫诲瀷
1. 绗涘崱灏旂Н(浜ゅ弶杩炴帴) 鍦∕ySQL涓彲浠ヤ负CROSS JOIN鎴栬€呯渷鐣ROSS鍗矹OIN锛屾垨鑰呬娇鐢?," 濡傦細
SELECT * FROM table1 CROSS JOIN table2
SELECT * FROM table1 JOIN table2
SELECT * FROM table1,table2
鐢变簬鍏惰繑鍥炵殑缁撴灉涓鸿杩炴帴鐨勪袱涓暟鎹〃鐨勪箻绉紝鍥犳褰撴湁WHERE, ON鎴朥SING鏉′欢鐨勬椂鍊欎竴鑸笉寤鸿浣跨敤锛屽洜涓哄綋鏁版嵁琛ㄩ」鐩お澶氱殑鏃跺€欙紝浼氶潪甯告參銆備竴鑸娇鐢↙EFT [OUTER] JOIN鎴栬€匯IGHT [OUTER] JOIN
2. 鍐呰繛鎺NNER JOIN 鍦∕ySQL涓妸INNER JOIN鍙仛绛夊€艰繛鎺ワ紝鍗抽渶瑕佹寚瀹氱瓑鍊艰繛鎺ユ潯浠跺湪MySQL涓瑿ROSS鍜孖NNER JOIN琚垝鍒嗗湪涓€璧枫€?join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]
3. MySQL涓殑澶栬繛鎺ワ紝鍒嗕负宸﹀杩炴帴鍜屽彸杩炴帴锛屽嵆闄や簡杩斿洖绗﹀悎杩炴帴鏉′欢鐨勭粨鏋滀箣澶栵紝杩樿杩斿洖宸﹁〃(宸﹁繛鎺?鎴栬€呭彸琛?鍙宠繛鎺?涓笉绗﹀悎杩炴帴鏉′欢鐨勭粨鏋滐紝鐩稿搴旂殑浣跨敤NULL瀵瑰簲銆?/p>
渚嬪瓙锛?/p>
user琛?
id | name
———
1 | libk
2 | zyfon
3 | daodao
user_action琛?
user_id | action
—————
1 | jump
1 | kick
1 | jump
2 | run
4 | swim
sql:
select id, name, action from user as u
left join user_action a on u.id = a.user_id
result:
id | name | action
——————————–
1 | libk | jump 鈶?/p>
1 | libk | kick 鈶?/p>
1 | libk | jump 鈶?/p>
2 | zyfon | run 鈶?/p>
3 | daodao | null 鈶?/p>
鍒嗘瀽锛?/p>
娉ㄦ剰鍒皍ser_action涓繕鏈変竴涓猽ser_id=4, action=swim鐨勭邯褰曪紝浣嗘槸娌℃湁鍦ㄧ粨鏋滀腑鍑虹幇锛?/p>
鑰寀ser琛ㄤ腑鐨刬d=3, name=daodao鐨勭敤鎴峰湪user_action涓病鏈夌浉搴旂殑绾綍锛屼絾鏄嵈鍑虹幇鍦ㄤ簡缁撴灉闆嗕腑
鍥犱负鐜板湪鏄痩eft join锛屾墍鏈夌殑宸ヤ綔浠eft涓哄噯.
缁撴灉1锛?锛?锛?閮芥槸鏃㈠湪宸﹁〃鍙堝湪鍙宠〃鐨勭邯褰曪紝5鏄彧鍦ㄥ乏琛紝涓嶅湪鍙宠〃鐨勭邯褰?/p>
宸ヤ綔鍘熺悊锛?/p>
浠庡乏琛ㄨ鍑轰竴鏉★紝閫夊嚭鎵€鏈変笌on鍖归厤鐨勫彸琛ㄧ邯褰?n鏉?杩涜杩炴帴锛屽舰鎴恘鏉＄邯褰?鍖呮嫭閲嶅鐨勮锛屽锛氱粨鏋?鍜岀粨鏋?)锛屽鏋滃彸杈规病鏈変笌on鏉′欢鍖归厤鐨勮〃锛岄偅杩炴帴鐨勫瓧娈甸兘鏄痭ull.鐒跺悗缁х画璇讳笅涓€鏉°€?/p>
寮曠敵锛?/p>
鎴戜滑鍙互鐢ㄥ彸琛ㄦ病鏈塷n鍖归厤鍒欐樉绀簄ull鐨勮寰? 鏉ユ壘鍑烘墍鏈夊湪宸﹁〃锛屼笉鍦ㄥ彸琛ㄧ殑绾綍锛?娉ㄦ剰鐢ㄦ潵鍒ゆ柇鐨勯偅鍒楀繀椤诲０鏄庝负not null鐨勩€?/p>
濡傦細
sql:
select id, name, action from user as u
left join user_action a on u.id = a.user_id
where a.user_id is NULL
(娉ㄦ剰:
1.鍒楀€间负null搴旇鐢╥s null 鑰屼笉鑳界敤=NULL
2.杩欓噷a.user_id 鍒楀繀椤诲０鏄庝负 NOT NULL 鐨?
)
涓婇潰sql鐨剅esult:
id | name | action
————————–
3 | daodao | NULL
——————————————————————————–
涓€鑸敤娉曪細
a. LEFT [OUTER] JOIN锛?/p>
闄や簡杩斿洖绗﹀悎杩炴帴鏉′欢鐨勭粨鏋滀箣澶栵紝杩橀渶瑕佹樉绀哄乏琛ㄤ腑涓嶇鍚堣繛鎺ユ潯浠剁殑鏁版嵁鍒楋紝鐩稿搴斾娇鐢∟ULL瀵瑰簲
SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column
b. RIGHT [OUTER] JOIN锛?/p>
RIGHT涓嶭EFT JOIN鐩镐技涓嶅悓鐨勪粎浠呮槸闄や簡鏄剧ず绗﹀悎杩炴帴鏉′欢鐨勭粨鏋滀箣澶栵紝杩橀渶瑕佹樉绀哄彸琛ㄤ腑涓嶇鍚堣繛鎺ユ潯浠剁殑鏁版嵁鍒楋紝鐩稿簲浣跨敤NULL瀵瑰簲
SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column
Tips:
1. on a.c1 = b.c1 绛夊悓浜?using(c1)
2. INNER JOIN 鍜?, (閫楀彿) 鍦ㄨ涔変笂鏄瓑鍚岀殑
3. 褰?MySQL 鍦ㄤ粠涓€涓〃涓绱俊鎭椂锛屼綘鍙互鎻愮ず瀹冮€夋嫨浜嗗摢涓€涓储寮曘€?/p>
濡傛灉 EXPLAIN 鏄剧ず MySQL 浣跨敤浜嗗彲鑳界殑绱㈠紩鍒楄〃涓敊璇殑绱㈠紩锛岃繖涓壒鎬у皢鏄緢鏈夌敤鐨勩€?/p>
閫氳繃鎸囧畾 USE INDEX (key_list)锛屼綘鍙互鍛婅瘔 MySQL 浣跨敤鍙兘鐨勭储寮曚腑鏈€鍚堥€傜殑涓€涓储寮曞湪琛ㄤ腑鏌ユ壘璁板綍琛屻€?/p>
鍙€夌殑浜岄€変竴鍙ユ硶 IGNORE INDEX (key_list) 鍙鐢ㄤ簬鍛婅瘔 MySQL 涓嶄娇鐢ㄧ壒瀹氱殑绱㈠紩銆傚锛?/p>
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
-> WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
-> WHERE key1=1 AND key2=2 AND key3=3;
2. 琛ㄨ繛鎺ョ殑绾︽潫鏉′欢
娣诲姞鏄剧ず鏉′欢WHERE, ON, USING
1. WHERE瀛愬彞
mysql>
SELECT * FROM table1,table2 WHERE table1.id=table2.id;
2. ON
mysql>
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
LEFT JOIN table3 ON table2.id=table3.id;
3. USING瀛愬彞锛屽鏋滆繛鎺ョ殑涓や釜琛ㄨ繛鎺ユ潯浠剁殑涓や釜鍒楀叿鏈夌浉鍚岀殑鍚嶅瓧鐨勮瘽鍙互浣跨敤USING
渚嬪锛?/p>
SELECT FROM LEFT JOIN USING ()
杩炴帴澶氫簬涓や釜琛ㄧ殑鎯呭喌涓句緥锛?/p>
mysql>
SELECT artists.Artist, cds.title, genres.genre
FROM cds
LEFT JOIN genres N cds.genreID = genres.genreID
LEFT JOIN artists ON cds.artistID = artists.artistID;
鎴栬€?mysql>
SELECT artists.Artist, cds.title, genres.genre
FROM cds
LEFT JOIN genres ON cds.genreID = genres.genreID
LEFT JOIN artists -> ON cds.artistID = artists.artistID
WHERE (genres.genre = "Pop");
--------------------------------------------
鍏堣繃婊ゆ潯浠剁劧鍚庡啀鏍规嵁琛ㄨ繛鎺?鍚屾椂鍦ㄨ〃涓缓绔嬬浉鍏虫煡璇㈠瓧娈电殑绱㈠紩杩欐牱鍦ㄥぇ鏁版嵁澶氳〃鑱斿悎鏌ヨ鐨勬儏鍐典笅閫熷害鐩稿綋蹇?/p>
SELECT M.*,SS.SensorCode,SS.SensorStatus,SS.ManufacturerId,SS.Electricity,
SS.Voltage,SS.MinElectricity,SS.MinVoltage,SS.Temperature,SS.StatusUpdteDate,SS.UpdateStatus ,tp.PricingStrategyid,tps.FreeDuration,bat.BerthTypeId
FROM
(SELECT T.* , BS.ParkStatus,BS.ChangeTime ,CA.CantonName, SE.SectionName
FROM
(SELECT A.* ,B.BerthId,B.BerthCode,B.BerthAddress,B.BerthStatus,B.LineDirection,B.CantonId,B.SectionId
FROM
(SELECT AR.AreaId,AR.AreaCode,AR.AreaName FROM SYS_Area AS AR WHERE 1=1 AND AR.AreaCode="110" ) A
LEFT JOIN SYS_Berth AS B ON B.AreaId=A.AreaId ) T
JOIN SYS_BerthStatus AS BS ON T.BerthCode=BS.BerthCode
JOIN SYS_Canton AS CA ON T.CantonId=CA.CantonId
JOIN SYS_Section AS SE ON T.SectionId=SE.SectionId )M
LEFT JOIN SYS_Sensor SS ON M.BerthCode=SS.BerthCode
LEFT JOIN TRA_PricingBerth AS tp ON tp.BerthCode=M.BerthCode
LEFT JOIN TRA_PricingStrategy AS tps ON tps.PricingStrategyId=tp.PricingStrategyId
LEFT JOIN SYS_BerthAndType AS bat ON bat.BerthCode=M.BerthCode
ORDER BY BerthCode ASC
鍙﹀闇€瑕佹敞鎰忕殑鍦版柟 鍦∕ySQL涓秹鍙婂埌澶氳〃鏌ヨ鐨勬椂鍊欙紝闇€瑕佹牴鎹煡璇㈢殑鎯呭喌锛屾兂濂戒娇鐢ㄥ摢绉嶈繛鎺ユ柟寮忔晥鐜囨洿楂樸€?/p>
1. 浜ゅ弶杩炴帴(绗涘崱灏旂Н)鎴栬€呭唴杩炴帴 [INNER | CROSS] JOIN
2. 宸﹀杩炴帴LEFT [OUTER] JOIN鎴栬€呭彸澶栬繛鎺IGHT [OUTER] JOIN 娉ㄦ剰鎸囧畾杩炴帴鏉′欢WHERE, ON锛孶SING.
3. MySQL濡備綍浼樺寲LEFT JOIN鍜孯IGHT JOIN
鍦∕ySQL涓紝A LEFT JOIN B join_condition鎵ц杩囩▼濡備笅锛?/p>
1)· 鏍规嵁琛ˋ鍜孉渚濊禆鐨勬墍鏈夎〃璁剧疆琛˙銆?/p>
2)· 鏍规嵁LEFT JOIN鏉′欢涓娇鐢ㄧ殑鎵€鏈夎〃(闄や簡B)璁剧疆琛ˋ銆?/p>
3)· LEFT JOIN鏉′欢鐢ㄤ簬纭畾濡備綍浠庤〃B鎼滅储琛屻€?鎹㈠彞璇濊锛屼笉浣跨敤WHERE瀛愬彞涓殑浠讳綍鏉′欢)銆?/p>
4)· 鍙互瀵规墍鏈夋爣鍑嗚仈鎺ヨ繘琛屼紭鍖栵紝鍙槸鍙湁浠庡畠鎵€渚濊禆鐨勬墍鏈夎〃璇诲彇鐨勮〃渚嬪銆傚鏋滃嚭鐜板惊鐜緷璧栧叧绯伙紝MySQL鎻愮ず鍑虹幇涓€涓敊璇€?/p>
5)· 杩涜鎵€鏈夋爣鍑哤HERE浼樺寲銆?/p>
6)· 濡傛灉A涓湁涓€琛屽尮閰峎HERE瀛愬彞锛屼絾B涓病鏈変竴琛屽尮閰峅N鏉′欢锛屽垯鐢熸垚鍙︿竴涓狟琛岋紝鍏朵腑鎵€鏈夊垪璁剧疆涓篘ULL銆?/p>
7)· 濡傛灉浣跨敤LEFT JOIN鎵惧嚭鍦ㄦ煇浜涜〃涓笉瀛樺湪鐨勮锛屽苟涓旇繘琛屼簡涓嬮潰鐨勬祴璇曪細WHERE閮ㄥ垎鐨刢ol_name IS NULL锛屽叾涓璫ol_name鏄竴涓０鏄庝负 NOT NULL鐨勫垪锛孧ySQL鎵惧埌鍖归厤LEFT JOIN鏉′欢鐨勪竴涓鍚庡仠姝?涓哄叿浣撶殑鍏抽敭瀛楃粍鍚?鎼滅储鍏跺畠琛屻€?/p>
RIGHT JOIN鐨勬墽琛岀被浼糒EFT JOIN锛屽彧鏄〃鐨勮鑹插弽杩囨潵銆?/p>
鑱旀帴浼樺寲鍣ㄨ绠楄〃搴旇仈鎺ョ殑椤哄簭銆侺EFT JOIN鍜孲TRAIGHT_JOIN寮哄埗鐨勮〃璇婚『搴忓彲浠ュ府鍔╄仈鎺ヤ紭鍖栧櫒鏇村揩鍦板伐浣滐紝鍥犱负妫€鏌ョ殑琛ㄤ氦鎹㈡洿灏戙€傝娉ㄦ剰杩欒鏄庡鏋滄墽琛屼笅闈㈢被鍨嬬殑鏌ヨ锛孧ySQL杩涜鍏ㄦ壂鎻廱锛屽洜涓篖EFT JOIN寮哄埗瀹冨湪d涔嬪墠璇诲彇锛?/p>
SELECT *
FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
鍦ㄨ繖绉嶆儏鍐典笅淇鏃剁敤a鐨勭浉鍙嶉『搴忥紝b鍒椾簬FROM瀛愬彞涓細
SELECT *
FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
MySQL鍙互杩涜涓嬮潰鐨凩EFT JOIN浼樺寲锛氬鏋滃浜庝骇鐢熺殑NULL琛岋紝WHERE鏉′欢鎬讳负鍋囷紝LEFT JOIN鍙樹负鏅€氳仈鎺ャ€?/p>
渚嬪锛屽湪涓嬮潰鐨勬煡璇腑濡傛灉t2.column1涓篘ULL锛學HERE 瀛愬彞灏嗕负false锛?/p>
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
鍥犳锛屽彲浠ュ畨鍏ㄥ湴灏嗘煡璇㈣浆鎹负鏅€氳仈鎺ワ細
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
杩欐牱鍙互鏇村揩锛屽洜涓哄鏋滃彲浠ヤ娇鏌ヨ鏇翠匠锛孧ySQL鍙互鍦ㄨ〃t1涔嬪墠浣跨敤琛╰2銆備负浜嗗己鍒朵娇鐢ㄨ〃椤哄簭锛屼娇鐢⊿TRAIGHT_JOIN銆?/p>