鍒涘缓绱㈠紩銆佷慨鏀圭储寮曘€佸垹闄ょ储寮曠殑鍛戒护璇彞
鍒涘缓绱㈠紩銆佷慨鏀圭储寮曘€佸垹闄ょ储寮曠殑鍛戒护璇彞
2012-05-07 11:53:09 | 1671娆￠槄璇?| 璇勮锛? 鏉?| itokit  mysql绱㈠紩瀛︿範----2----鍒涘缓绱㈠紩銆佷慨鏀圭储寮曘€佸垹闄ょ储寮曠殑鍛戒护璇彞
鏌ョ湅琛ㄤ腑宸茬粡瀛樺湪 index锛歴how index from table_name;

鍒涘缓鍜屽垹闄ょ储寮?
绱㈠紩鐨勫垱寤哄彲浠ュ湪CREATE TABLE璇彞涓繘琛岋紝涔熷彲浠ュ崟鐙敤CREATE INDEX鎴朅LTER TABLE鏉ョ粰琛ㄥ鍔犵储寮曘€傚垹闄ょ储寮曞彲浠ュ埄鐢ˋLTER TABLE鎴朌ROP INDEX璇彞鏉ュ疄鐜般€?

锛?锛変娇鐢ˋLTER TABLE璇彞鍒涘缓绱㈠紩銆?
璇硶濡備笅锛?
alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;
鍏朵腑鍖呮嫭鏅€氱储寮曘€乁NIQUE绱㈠紩鍜孭RIMARY KEY绱㈠紩3绉嶅垱寤虹储寮曠殑鏍煎紡锛宼able_name鏄澧炲姞绱㈠紩鐨勮〃鍚嶏紝column_list鎸囧嚭瀵瑰摢浜涘垪杩涜绱㈠紩锛屽鍒楁椂鍚勫垪涔嬮棿鐢ㄩ€楀彿鍒嗛殧銆傜储寮曞悕index_name鍙€夛紝缂虹渷鏃讹紝MySQL灏嗘牴鎹涓€涓储寮曞垪璧嬩竴涓悕绉般€傚彟澶栵紝ALTER TABLE鍏佽鍦ㄥ崟涓鍙ヤ腑鏇存敼澶氫釜琛紝鍥犳鍙互鍚屾椂鍒涘缓澶氫釜绱㈠紩銆?
鍒涘缓绱㈠紩鐨勭ず渚嬪涓嬶細
mysql> use tpsc
Database changed
mysql> alter table tpsc add index shili (tpmc ) ;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0

锛?锛変娇鐢–REATE INDEX璇彞瀵硅〃澧炲姞绱㈠紩銆?
鑳藉澧炲姞鏅€氱储寮曞拰UNIQUE绱㈠紩涓ょ銆傚叾鏍煎紡濡備笅锛?
create index index_name on table_name (column_list) ;
create unique index index_name on table_name (column_list) ;
璇存槑锛歵able_name銆乮ndex_name鍜宑olumn_list鍏锋湁涓嶢LTER TABLE璇彞涓浉鍚岀殑鍚箟锛岀储寮曞悕涓嶅彲閫夈€傚彟澶栵紝涓嶈兘鐢–REATE INDEX璇彞鍒涘缓PRIMARY KEY绱㈠紩銆?

锛?锛夊垹闄ょ储寮曘€?
鍒犻櫎绱㈠紩鍙互浣跨敤ALTER TABLE鎴朌ROP INDEX璇彞鏉ュ疄鐜般€侱ROP INDEX鍙互鍦ˋLTER TABLE鍐呴儴浣滀负涓€鏉¤鍙ュ鐞嗭紝鍏舵牸寮忓涓嬶細
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
鍏朵腑锛屽湪鍓嶉潰鐨勪袱鏉¤鍙ヤ腑锛岄兘鍒犻櫎浜唗able_name涓殑绱㈠紩index_name銆傝€屽湪鏈€鍚庝竴鏉¤鍙ヤ腑锛屽彧鍦ㄥ垹闄RIMARY KEY绱㈠紩涓娇鐢紝鍥犱负涓€涓〃鍙彲鑳芥湁涓€涓狿RIMARY KEY绱㈠紩锛屽洜姝や笉闇€瑕佹寚瀹氱储寮曞悕銆傚鏋滄病鏈夊垱寤篜RIMARY KEY绱㈠紩锛屼絾琛ㄥ叿鏈変竴涓垨澶氫釜UNIQUE绱㈠紩锛屽垯MySQL灏嗗垹闄ょ涓€涓猆NIQUE绱㈠紩銆?
濡傛灉浠庤〃涓垹闄ゆ煇鍒楋紝鍒欑储寮曚細鍙楀奖鍝嶃€傚浜庡鍒楃粍鍚堢殑绱㈠紩锛屽鏋滃垹闄ゅ叾涓殑鏌愬垪锛屽垯璇ュ垪涔熶細浠庣储寮曚腑鍒犻櫎銆傚鏋滃垹闄ょ粍鎴愮储寮曠殑鎵€鏈夊垪锛屽垯鏁翠釜绱㈠紩灏嗚鍒犻櫎銆?
鍒犻櫎绱㈠紩鐨勬搷浣滐紝濡備笅闈㈢殑浠ｇ爜锛?
mysql> drop index shili on tpsc ;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
璇ヨ鍙ュ垹闄や簡鍓嶉潰鍒涘缓鐨勫悕绉颁负鈥渟hili鈥濈殑绱㈠紩銆?




===========================================

MYSQL绱㈠紩鏃犳晥鍜岀储寮曟湁鏁堢殑璇︾粏浠嬬粛锛岄渶瑕佺殑鏈嬪弸鍙互鍙傝€冧竴涓?
1銆乄HERE瀛楀彞鐨勬煡璇㈡潯浠堕噷鏈変笉绛変簬鍙凤紙WHERE column!=...锛夛紝MYSQL灏嗘棤娉曚娇鐢ㄧ储寮?
2銆佺被浼煎湴锛屽鏋淲HERE瀛楀彞鐨勬煡璇㈡潯浠堕噷浣跨敤浜嗗嚱鏁帮紙濡傦細WHERE DAY(column)=...锛夛紝MYSQL灏嗘棤娉曚娇鐢ㄧ储寮?
3銆佸湪JOIN鎿嶄綔涓紙闇€瑕佷粠澶氫釜鏁版嵁琛ㄦ彁鍙栨暟鎹椂锛夛紝MYSQL鍙湁鍦ㄤ富閿拰澶栭敭鐨勬暟鎹被鍨嬬浉鍚屾椂鎵嶈兘浣跨敤绱㈠紩锛屽惁鍒欏嵆浣垮缓绔嬩簡
绱㈠紩涔熶笉浼氫娇鐢?
4銆佸鏋淲HERE瀛愬彞鐨勬煡璇㈡潯浠堕噷浣跨敤浜嗘瘮杈冩搷浣滅LIKE鍜孯EGEXP锛孧YSQL鍙湁鍦ㄦ悳绱㈡ā鏉跨殑绗竴涓瓧绗︿笉鏄€氶厤绗︾殑鎯呭喌涓嬫墠鑳?
浣跨敤绱㈠紩銆傛瘮濡傝锛屽鏋滄煡璇㈡潯浠舵槸LIKE 'abc%',MYSQL灏嗕娇鐢ㄧ储寮曪紱濡傛灉鏉′欢鏄疞IKE '%abc'锛孧YSQL灏嗕笉浣跨敤绱㈠紩銆?
5銆佸湪ORDER BY鎿嶄綔涓紝MYSQL鍙湁鍦ㄦ帓搴忔潯浠朵笉鏄竴涓煡璇㈡潯浠惰〃杈惧紡鐨勬儏鍐典笅鎵嶄娇鐢ㄧ储寮曘€傚敖绠″姝わ紝鍦ㄦ秹鍙婂涓暟鎹〃鐨勬煡
璇㈤噷锛屽嵆浣挎湁绱㈠紩鍙敤锛岄偅浜涚储寮曞湪鍔犲揩ORDER BY鎿嶄綔鏂归潰涔熸病浠€涔堜綔鐢ㄣ€?
6銆佸鏋滄煇涓暟鎹垪閲屽寘鍚潃璁稿閲嶅鐨勫€硷紝灏辩畻涓哄畠寤虹珛浜嗙储寮曚篃涓嶄細鏈夊緢濂界殑鏁堟灉銆傛瘮濡傝锛屽鏋滄煇涓暟鎹垪閲屽寘鍚簡鍑€鏄?
浜涜濡傗€?/1鈥濇垨鈥淵/N鈥濈瓑鍊硷紝灏辨病鏈夊繀瑕佷负瀹冨垱寤轰竴涓储寮曘€?

7銆佺储寮曟湁鐢ㄧ殑鎯呭喌涓嬪氨澶浜嗐€傚熀鏈彧瑕佸缓绔嬩簡绱㈠紩锛岄櫎浜嗕笂闈㈡彁鍒扮殑绱㈠紩涓嶄細浣跨敤鐨勬儏鍐典笅涔嬪锛屽叾浠栨儏鍐靛彧瑕佹槸浣跨敤鍦?
WHERE鏉′欢閲岋紝ORDER BY 瀛楁锛岃仈琛ㄥ瓧娈碉紝涓€鑸兘鏄湁鏁堢殑銆?寤虹珛绱㈠紩瑕佺殑灏辨槸鏈夋晥鏋溿€?涓嶇劧杩樼敤瀹冨共鍚楋紵 濡傛灉涓嶈兘纭畾鍦?


=================================

MySQL绱㈠紩绫诲瀷涓€瑙?璁㎝ySQL楂樻晥杩愯璧锋潵
2009-10-15 17:51 浣氬悕 缃戠粶杞浇 瀛楀彿锛歍 | T
涓€閿敹钘忥紝闅忔椂鏌ョ湅锛屽垎浜ソ鍙嬶紒
鏈枃浠嬬粛浜嗕竷绉峂ySQL绱㈠紩绫诲瀷銆傚湪鏁版嵁搴撹〃涓紝瀵瑰瓧娈靛缓绔嬬储寮曞彲浠ュぇ澶ф彁楂樻煡璇㈤€熷害銆傞€氳繃鍠勭敤杩欎簺绱㈠紩锛屽彲浠ヤ护MySQL鐨勬煡璇㈠拰杩愯鏇村姞楂樻晥銆?
AD锛?1CTO瀛﹂櫌锛欼T绮惧搧璇剧▼鍦ㄧ嚎鐪嬶紒
绱㈠紩鏄揩閫熸悳绱㈢殑鍏抽敭銆侻ySQL绱㈠紩鐨勫缓绔嬪浜嶮ySQL鐨勯珮鏁堣繍琛屾槸寰堥噸瑕佺殑銆備笅闈粙缁嶅嚑绉嶅父瑙佺殑MySQL绱㈠紩绫诲瀷銆?

鍦ㄦ暟鎹簱琛ㄤ腑锛屽瀛楁寤虹珛绱㈠紩鍙互澶уぇ鎻愰珮鏌ヨ閫熷害銆傚亣濡傛垜浠垱寤轰簡涓€涓?mytable琛細

CREATE TABLE mytable( 

ID INT NOT NULL,  

username VARCHAR(16) NOT NULL

); 

鎴戜滑闅忔満鍚戦噷闈㈡彃鍏ヤ簡10000鏉¤褰曪紝鍏朵腑鏈変竴鏉★細5555, admin銆?

鍦ㄦ煡鎵緐sername="admin"鐨勮褰?SELECT * FROM mytable