mysql鏁版嵁搴撴晥鐜囥€?00涓囨潯鏁版嵁-500涓囨潯鏁版嵁
mysql鏁版嵁搴撴晥鐜囥€?00涓囨潯鏁版嵁--500涓囨潯鏁版嵁
涓€鑸殑搴旂敤绯荤粺锛岃鍐欐瘮渚嬪湪10:1宸﹀彸锛岃€屼笖鎻掑叆鎿嶄綔鍜屼竴鑸殑鏇存柊鎿嶄綔寰堝皯鍑虹幇鎬ц兘闂锛岄亣鍒版渶澶氱殑锛屼篃鏄渶瀹规槗鍑洪棶棰樼殑锛岃繕鏄竴浜涘鏉傜殑鏌ヨ鎿嶄綔锛屾墍浠ユ煡璇㈣鍙ョ殑浼樺寲鏄剧劧鏄噸涓箣閲嶃€?
1 鏁版嵁搴撹〃寤虹珛绱㈠紩榛樿瑙勫垯锛?
锛?锛夋渶宸﹀墠缂€鍖归厤鍘熷垯锛岄潪甯搁噸瑕佺殑鍘熷垯锛宮ysql浼氫竴鐩村悜鍙冲尮閰嶇洿鍒伴亣鍒拌寖鍥存煡璇?>銆?lt;銆乥etween銆乴ike)灏卞仠姝㈠尮閰嶏紝姣斿a = 1 and b = 2 and c > 3 and d = 4 濡傛灉寤虹珛(a,b,c,d)椤哄簭鐨勭储寮曪紝d鏄敤涓嶅埌绱㈠紩鐨勶紝濡傛灉寤虹珛(a,b,d,c)鐨勭储寮曞垯閮藉彲浠ョ敤鍒帮紝a,b,d鐨勯『搴忓彲浠ヤ换鎰忚皟鏁淬€?
锛?锛?灏介噺鐨勬墿灞曠储寮曪紝涓嶈鏂板缓绱㈠紩銆傛瘮濡傝〃涓凡缁忔湁a鐨勭储寮曪紝鐜板湪瑕佸姞(a,b)鐨勭储寮曪紝閭ｄ箞鍙渶瑕佷慨鏀瑰師鏉ョ殑绱㈠紩鍗冲彲
锛?锛?鍜宨n鍙互涔卞簭锛屾瘮濡俛 = 1 and b = 2 and c = 3 寤虹珛(a,b,c)绱㈠紩鍙互浠绘剰椤哄簭锛宮ysql鐨勬煡璇紭鍖栧櫒浼氬府浣犱紭鍖栨垚绱㈠紩鍙互璇嗗埆鐨勫舰寮?
锛?锛夌储寮曞垪涓嶈兘鍙備笌璁＄畻锛屼繚鎸佸垪鈥滃共鍑€鈥濓紝姣斿from_unixtime(create_time) = 鈥?014-05-29鈥欏氨涓嶈兘浣跨敤鍒扮储寮曪紝鍘熷洜寰堢畝鍗曪紝b+鏍戜腑瀛樼殑閮芥槸鏁版嵁琛ㄤ腑鐨勫瓧娈靛€硷紝浣嗚繘琛屾绱㈡椂锛岄渶瑕佹妸鎵€鏈夊厓绱犻兘搴旂敤鍑芥暟鎵嶈兘姣旇緝锛屾樉鐒舵垚鏈お澶с€傛墍浠ヨ鍙ュ簲璇ュ啓鎴恈reate_time = unix_timestamp(鈥?014-05-29鈥?;
2 鏁版嵁閲忓ぇ鐨勬椂鍊欙紝搴斿敖閲忛伩鍏嶅叏琛ㄦ壂鎻忥紝搴旇€冭檻鍦?where 鍙?order by 娑夊強鐨勫垪涓婂缓绔嬬储寮曪紝寤虹储寮曞彲浠ュぇ澶у姞蹇暟鎹殑妫€绱㈤€熷害銆備絾鏄紝鏈変簺鎯呭喌绱㈠紩鏄笉浼氳捣鏁堢殑锛?
(1)銆佸簲灏介噺閬垮厤鍦?where 瀛愬彞涓娇鐢?=鎴?lt;>鎿嶄綔绗︼紝鍚﹀垯灏嗗紩鎿庢斁寮冧娇鐢ㄧ储寮曡€岃繘琛屽叏琛ㄦ壂鎻忋€?
(2)銆佸簲灏介噺閬垮厤鍦?where 瀛愬彞涓瀛楁杩涜 null 鍊煎垽鏂紝鍚﹀垯灏嗗鑷村紩鎿庢斁寮冧娇鐢ㄧ储寮曡€岃繘琛屽叏琛ㄦ壂鎻忥紝濡傦細
select id from t where num is null
鍙互鍦╪um涓婅缃粯璁ゅ€?锛岀‘淇濊〃涓璶um鍒楁病鏈塶ull鍊硷紝鐒跺悗杩欐牱鏌ヨ锛?
select id from t where num=0
(3)銆佸敖閲忛伩鍏嶅湪 where 瀛愬彞涓娇鐢?or 鏉ヨ繛鎺ユ潯浠讹紝鍚﹀垯灏嗗鑷村紩鎿庢斁寮冧娇鐢ㄧ储寮曡€岃繘琛屽叏琛ㄦ壂鎻忥紝濡傦細
select id from t where num=10 or num=20
鍙互杩欐牱鏌ヨ锛?
select id from t where num=10
union all
select id from t where num=20
(4)銆佷笅闈㈢殑鏌ヨ涔熷皢瀵艰嚧鍏ㄨ〃鎵弿锛?
select id from t where name like 鈥?abc%鈥?
鑻ヨ鎻愰珮鏁堢巼锛屽彲浠ヨ€冭檻鍏ㄦ枃妫€绱€?
(5)銆乮n 鍜?not in 涔熻鎱庣敤锛屽惁鍒欎細瀵艰嚧鍏ㄨ〃鎵弿锛屽锛?
select id from t where num in(1,2,3)
瀵逛簬杩炵画鐨勬暟鍊硷紝鑳界敤 between 灏变笉瑕佺敤 in 浜嗭細
select id from t where num between 1 and 3
(6)銆佸鏋滃湪 where 瀛愬彞涓娇鐢ㄥ弬鏁帮紝涔熶細瀵艰嚧鍏ㄨ〃鎵弿銆傚洜涓篠QL鍙湁鍦ㄨ繍琛屾椂鎵嶄細瑙ｆ瀽灞€閮ㄥ彉閲忥紝浣嗕紭鍖栫▼搴忎笉鑳藉皢璁块棶璁″垝鐨勯€夋嫨鎺ㄨ繜鍒拌繍琛屾椂锛涘畠蹇呴』鍦ㄧ紪璇戞椂杩涜閫夋嫨銆傜劧鑰岋紝濡傛灉鍦ㄧ紪璇戞椂寤虹珛璁块棶璁″垝锛屽彉閲忕殑鍊艰繕鏄湭鐭ョ殑锛屽洜鑰屾棤娉曚綔涓虹储寮曢€夋嫨鐨勮緭鍏ラ」銆傚涓嬮潰璇彞灏嗚繘琛屽叏琛ㄦ壂鎻忥細
select id from t where num=@num
鍙互鏀逛负寮哄埗鏌ヨ浣跨敤绱㈠紩锛?
select id from t with(index(绱㈠紩鍚?) where num=@num
(7)銆佸簲灏介噺閬垮厤鍦?where 瀛愬彞涓瀛楁杩涜琛ㄨ揪寮忔搷浣滐紝杩欏皢瀵艰嚧寮曟搸鏀惧純浣跨敤绱㈠紩鑰岃繘琛屽叏琛ㄦ壂鎻忋€傚锛?
select id from t where num/2=100
搴旀敼涓?
select id from t where num=100*2
(8)銆佸簲灏介噺閬垮厤鍦╳here瀛愬彞涓瀛楁杩涜鍑芥暟鎿嶄綔锛岃繖灏嗗鑷村紩鎿庢斁寮冧娇鐢ㄧ储寮曡€岃繘琛屽叏琛ㄦ壂鎻忋€傚锛?
select id from t where substring(name,1,3)=鈥檃bc鈥欌€搉ame浠bc寮€澶寸殑id
select id from t where datediff(day,createdate,鈥?005-11-30鈥?=0鈥撯€?005-11-30鈥茬敓鎴愮殑id
搴旀敼涓?
select id from t where name like 鈥榓bc%鈥?
select id from t where createdate>=鈥?005-11-30鈥?and createdate<鈥?005-12-1鈥?
鏉ヨ嚜浜庣綉缁滄€荤粨---鏈畬
涓€鑸殑搴旂敤绯荤粺锛岃鍐欐瘮渚嬪湪10:1宸﹀彸锛岃€屼笖鎻掑叆鎿嶄綔鍜屼竴鑸殑鏇存柊鎿嶄綔寰堝皯鍑虹幇鎬ц兘闂锛岄亣鍒版渶澶氱殑锛屼篃鏄渶瀹规槗鍑洪棶棰樼殑锛岃繕鏄竴浜涘鏉傜殑鏌ヨ鎿嶄綔锛屾墍浠ユ煡璇㈣鍙ョ殑浼樺寲鏄剧劧鏄噸涓箣閲嶃€?
1 鏁版嵁搴撹〃寤虹珛绱㈠紩榛樿瑙勫垯锛?
锛?锛夋渶宸﹀墠缂€鍖归厤鍘熷垯锛岄潪甯搁噸瑕佺殑鍘熷垯锛宮ysql浼氫竴鐩村悜鍙冲尮閰嶇洿鍒伴亣鍒拌寖鍥存煡璇?>銆?lt;銆乥etween銆乴ike)灏卞仠姝㈠尮閰嶏紝姣斿a = 1 and b = 2 and c > 3 and d = 4 濡傛灉寤虹珛(a,b,c,d)椤哄簭鐨勭储寮曪紝d鏄敤涓嶅埌绱㈠紩鐨勶紝濡傛灉寤虹珛(a,b,d,c)鐨勭储寮曞垯閮藉彲浠ョ敤鍒帮紝a,b,d鐨勯『搴忓彲浠ヤ换鎰忚皟鏁淬€?
锛?锛?灏介噺鐨勬墿灞曠储寮曪紝涓嶈鏂板缓绱㈠紩銆傛瘮濡傝〃涓凡缁忔湁a鐨勭储寮曪紝鐜板湪瑕佸姞(a,b)鐨勭储寮曪紝閭ｄ箞鍙渶瑕佷慨鏀瑰師鏉ョ殑绱㈠紩鍗冲彲
锛?锛?鍜宨n鍙互涔卞簭锛屾瘮濡俛 = 1 and b = 2 and c = 3 寤虹珛(a,b,c)绱㈠紩鍙互浠绘剰椤哄簭锛宮ysql鐨勬煡璇紭鍖栧櫒浼氬府浣犱紭鍖栨垚绱㈠紩鍙互璇嗗埆鐨勫舰寮?
锛?锛夌储寮曞垪涓嶈兘鍙備笌璁＄畻锛屼繚鎸佸垪鈥滃共鍑€鈥濓紝姣斿from_unixtime(create_time) = 鈥?014-05-29鈥欏氨涓嶈兘浣跨敤鍒扮储寮曪紝鍘熷洜寰堢畝鍗曪紝b+鏍戜腑瀛樼殑閮芥槸鏁版嵁琛ㄤ腑鐨勫瓧娈靛€硷紝浣嗚繘琛屾绱㈡椂锛岄渶瑕佹妸鎵€鏈夊厓绱犻兘搴旂敤鍑芥暟鎵嶈兘姣旇緝锛屾樉鐒舵垚鏈お澶с€傛墍浠ヨ鍙ュ簲璇ュ啓鎴恈reate_time = unix_timestamp(鈥?014-05-29鈥?;
2 鏁版嵁閲忓ぇ鐨勬椂鍊欙紝搴斿敖閲忛伩鍏嶅叏琛ㄦ壂鎻忥紝搴旇€冭檻鍦?where 鍙?order by 娑夊強鐨勫垪涓婂缓绔嬬储寮曪紝寤虹储寮曞彲浠ュぇ澶у姞蹇暟鎹殑妫€绱㈤€熷害銆備絾鏄紝鏈変簺鎯呭喌绱㈠紩鏄笉浼氳捣鏁堢殑锛?
(1)銆佸簲灏介噺閬垮厤鍦?where 瀛愬彞涓娇鐢?=鎴?lt;>鎿嶄綔绗︼紝鍚﹀垯灏嗗紩鎿庢斁寮冧娇鐢ㄧ储寮曡€岃繘琛屽叏琛ㄦ壂鎻忋€?
(2)銆佸簲灏介噺閬垮厤鍦?where 瀛愬彞涓瀛楁杩涜 null 鍊煎垽鏂紝鍚﹀垯灏嗗鑷村紩鎿庢斁寮冧娇鐢ㄧ储寮曡€岃繘琛屽叏琛ㄦ壂鎻忥紝濡傦細
select id from t where num is null
鍙互鍦╪um涓婅缃粯璁ゅ€?锛岀‘淇濊〃涓璶um鍒楁病鏈塶ull鍊硷紝鐒跺悗杩欐牱鏌ヨ锛?
select id from t where num=0
(3)銆佸敖閲忛伩鍏嶅湪 where 瀛愬彞涓娇鐢?or 鏉ヨ繛鎺ユ潯浠讹紝鍚﹀垯灏嗗鑷村紩鎿庢斁寮冧娇鐢ㄧ储寮曡€岃繘琛屽叏琛ㄦ壂鎻忥紝濡傦細
select id from t where num=10 or num=20
鍙互杩欐牱鏌ヨ锛?
select id from t where num=10
union all
select id from t where num=20
(4)銆佷笅闈㈢殑鏌ヨ涔熷皢瀵艰嚧鍏ㄨ〃鎵弿锛?
select id from t where name like 鈥?abc%鈥?
鑻ヨ鎻愰珮鏁堢巼锛屽彲浠ヨ€冭檻鍏ㄦ枃妫€绱€?
(5)銆乮n 鍜?not in 涔熻鎱庣敤锛屽惁鍒欎細瀵艰嚧鍏ㄨ〃鎵弿锛屽锛?
select id from t where num in(1,2,3)
瀵逛簬杩炵画鐨勬暟鍊硷紝鑳界敤 between 灏变笉瑕佺敤 in 浜嗭細
select id from t where num between 1 and 3
(6)銆佸鏋滃湪 where 瀛愬彞涓娇鐢ㄥ弬鏁帮紝涔熶細瀵艰嚧鍏ㄨ〃鎵弿銆傚洜涓篠QL鍙湁鍦ㄨ繍琛屾椂鎵嶄細瑙ｆ瀽灞€閮ㄥ彉閲忥紝浣嗕紭鍖栫▼搴忎笉鑳藉皢璁块棶璁″垝鐨勯€夋嫨鎺ㄨ繜鍒拌繍琛屾椂锛涘畠蹇呴』鍦ㄧ紪璇戞椂杩涜閫夋嫨銆傜劧鑰岋紝濡傛灉鍦ㄧ紪璇戞椂寤虹珛璁块棶璁″垝锛屽彉閲忕殑鍊艰繕鏄湭鐭ョ殑锛屽洜鑰屾棤娉曚綔涓虹储寮曢€夋嫨鐨勮緭鍏ラ」銆傚涓嬮潰璇彞灏嗚繘琛屽叏琛ㄦ壂鎻忥細
select id from t where num=@num
鍙互鏀逛负寮哄埗鏌ヨ浣跨敤绱㈠紩锛?
select id from t with(index(绱㈠紩鍚?) where num=@num
(7)銆佸簲灏介噺閬垮厤鍦?where 瀛愬彞涓瀛楁杩涜琛ㄨ揪寮忔搷浣滐紝杩欏皢瀵艰嚧寮曟搸鏀惧純浣跨敤绱㈠紩鑰岃繘琛屽叏琛ㄦ壂鎻忋€傚锛?
select id from t where num/2=100
搴旀敼涓?
select id from t where num=100*2
(8)銆佸簲灏介噺閬垮厤鍦╳here瀛愬彞涓瀛楁杩涜鍑芥暟鎿嶄綔锛岃繖灏嗗鑷村紩鎿庢斁寮冧娇鐢ㄧ储寮曡€岃繘琛屽叏琛ㄦ壂鎻忋€傚锛?
select id from t where substring(name,1,3)=鈥檃bc鈥欌€搉ame浠bc寮€澶寸殑id
select id from t where datediff(day,createdate,鈥?005-11-30鈥?=0鈥撯€?005-11-30鈥茬敓鎴愮殑id
搴旀敼涓?
select id from t where name like 鈥榓bc%鈥?
select id from t where createdate>=鈥?005-11-30鈥?and createdate<鈥?005-12-1鈥?
鏉ヨ嚜浜庣綉缁滄€荤粨---鏈畬