1 識導 3 識 mysql入mysqlmysqlmysqlmysqlmysqlmysql重排auto_increment4 識入 入mysql>createtablet2likemysql>insertintot2select*from5 1.ALTERTABLE用來創建普通索引、UNIQUE索引或PRIMARYKEY索ALTERTABLEtable_nameADDINDEXindex_name(column_list)ALTERTABLEtable_nameADDUNIQUE(column_list)ALTERTABLEtable_nameADDPRIMARYKEYcreateCREATEINDEXindex_nameONtable_nameCREATEUNIQUEINDEXindex_nameONtable_namedropDROPINDEXindex_nameONaltertabletableALTERTABLEtable_nameDROPINDEXindex_nameALTERTABLEtable_nameDROPPRIMARYKEY6 mysql>createviewv_t1asselect*fromt1whereid>4andid<11;QueryOK,0rowsaffected(0.00sec)mysql>?viewALTERVIEWCREATEVIEWDROPVIEWmysql>showtables;mysql>dropview7 識 字符串函數 CONCAT(string2[,…LCASE(string2)UCASE(string2)LENGTH(stringLTRIM(string2)RTRIM(string2REPEAT(string2,count

//重復countRECE(str,search_str,rece_str)//在str中用rece_str替換search_strSUBSTRING(str,position[,length])//從str的position開始,取length個字符 8 識入 入BIN(decimal_number)CEILING(number2)FLOOR(number2)MAX(num1,num2)

9 識 入

識 入mysql>preparestmt1from'select*fromt1wheremysqlset@i=1;mysqlexecutestmt1using@i;mysqlset@i=5;mysql>executestmt1using如何刪除預處理mysql>dropprepare 識 mysql>setmysql>savepointp1;mysql>savepointp2;mysql>rollbacktop1;退回到最原始的還原點:mysql>rollback 識 創建一 mysql>\dmysql>createprocedure->->set->while@i<10->select->set->end->mysql>\d;mysql>callp1();查看procedurep1()的status信息mysql>showprocedurestatus\G查看procedurep1()的具體信息:mysql>showcreateprocedure 識 修改delimiter為 mysql>\dmysql>createtriggertg1beforeinsertont1foreach>insertintot2(id)mysql>select*fromt1;mysql>select*fromt2;mysql>insertintot1 識 如何制作刪除表t1后t2表中的記錄也會跟著刪除呢 mysql>\dmysql>createtriggertg2beforedeleteont1foreach>begindeletefromt2wheremysql>\d;mysql>\d//mysql>createtriggertg3beforeupdateont1foreach>beginupdatet2setid=new.idwheremysql>\d;mysql>show 識 MYSQL數據庫自動增長的ID如何恢 deletefromtablename;truncatetable這樣auto_increment就恢復成1altertabletablenameauto_increment=1; 識入 入利用GROUPBY的WITHROLLUP用BITGROUPFUNCTIONS 識入 入^匹配1個或零個$.匹配0到n個 識入 入mysql>selectname, like“”

fromt

like“mysql>selectmysql>selectfromt 巧用RAND()提取隨機識入 入個函數一起和orderby能夠把數據隨機排序。mysql>select*fromstuorderbyrand()；mysql>select*fromstuorderbyrandlimit3; 利用GROUPBY的WITH識 使用groupby的withrollup子句可以檢索 的分組聚合信息 mysql>select*from |cname|pname | | | | | | | | | | | | | | | | | | 9rowsinset(0.00 利用GROUPBY的WITH識 mysql>selectcname,pname,count(pname)fromdemogroupby |cname|pname|count(pname) ||||

| 3| 2| 3| 1 4rowsinset(0.00 利用GROUPBY的WITH識 同樣使用withrollup關鍵字后，統計 的信息，如下。注意： rollup不可以和orderyby同時使mysql>selectcname,pname,count(pname)fromdemogroupbycname,pnamewithrollup; |cname|pname|count(pname) | | 3|| | 2|||||

|NULL 5|| 3|| 1||NULL 4||NULL|NULL 9| 7rowsinset(0.00知2.4BITGROUPFUNCTIONS知 ?在使用groupby語句時可以同時用使用bit_and、mysql>selectmysql>selectid，bit_or(kind)fromorder_rabgroupbymysql>selectmysql>selectid，bit_and(kind)fromorder_rabgroupby知2.5知 mysql>createmysql>createtabletemp(idint,namechar(20),foreignkey(id)referencesoutTable(id)ondeletecascadeonupdate 2.6mysqlhelp使識導入在mysql中那么多令如何才能記得住是個問題，這里?%可以獲得所有的mysql>里 ?opti因為記不住optimize的全稱，這個時候可以用%reg%獲得了記不住了的regexp用法查看所有用?contents可以得到所有的幫助大綱,通過這個 識導 知知 3.1.1通過showstatus命令了解各種SQL的執行頻格式：mysqlshowmysql>show mysql>showglobal mysql>showstatuslike mysql>showglobalstatuslike 參數說明Com_XXX表示每個XXX執行執行只針對于 引擎的 inselect*fromtablewhere2)descselect*fromtablewheremysql> inselectcount(*)fromstumysql> inselectcount(*)fromstuwherenamelikeid:1table:stutype:key:namekey_len:50ref:NULLrows:8Extra:Usingwhere;Usingindex1rowinset(0.00sec) 每一列的簡單解 id: select_type:SIMPLE表示select的類型，常見的取值有SIMPLE（）簡單表，即不使用表連接或 table type:range表示表的連接類型，性能有好到差：system（表僅一行）、const（只一行匹配）、ref_or_null（同前面對null查詢）、index_merge（索引合并優化）、unique_subquery（:key key_len:50索引字段的長度ref:rows Extra:Usingwhere;Usingindex知知 3.2.1索引

indexpany2_namepany2_name3.2.2MySQL 于快速找出在某個列中有一特定值的行。對相關列使用索引是提高導 操作性能的最佳途徑導入1mysql>createmysql>createindex_mon然后 _id進行查詢，發現使用到了復合索 inselect*fromsales2 inselect*fromsales2where識 識2where2wherenamelikeinselect*mysql>******************************************************1.rowid:1 type:ALLkey:NULLkey_len:NULLref:NULLrows:1000Extra:Using1rowinset(0.00 mysql> inselect* 2wherenamelike ***************************1.rowid:select_type: 2type:rangepossible_keys:pany2_namekey:pany2_namekey_len:11ref:rows:Extra:Usingwhere1rowinset(0.00sec)mysql>mysql>inselect*2wherenameis***************************1.rowid:select_type:SIMPLE 2type:refpossible_keys:pany2_namekey:pany2_namekey_len:11ref:(4)如果列名是索引，使用column_nameisnull將使用索引。如下知rows:Extra:Usingwhere1rowinset(0.00sec)知導 導 mysql>selectmysql>select*fromtable_namewherekey_part1>1andmysql>showindexmysql>showindexfrom***************************1.……key_name:ind_sales_yearColumn_name:year……知 mysql> inselect*fromsaleswhereyear=2001or***************************1.rowid:1table:key:NULL1row1rowin2sing.00::t11rowUsing(0.00: 識inselectinselect*fromsales2wheremoneys=1mysql>******************************************************1.rowid:1type:ALLkey:NULLkey_len: 識導 mysql> inselect* 2wherename***************************1.rowid:1 type:ALLkey:key_len:1rowinUsingt(0.00emysql>mysql>inselect*2where***************************1.rowid:select_type: 2type:ALLpossible_keys:pany2_namekey:NULLkey_len:NULLref:NULLrows:Extra:Using 識1rowinset(0.00mysql>mysql>inselect*2where***************************1.row***************************id:select_type:SIMPLEpossible_keys:pany2_namekey:pany2_namekey_len:ref:constrows:1Extra:Usingwhere2type:1rowinset(0.003.2.3 入 入mysql>mysql>showstatuslike | | | ||||Handler_read_rnd_next|2055 6rowsinset(0.00|||||||||知知導 導mysql>mysql>CHECKTABLEtbl_name[,tbl_name]…[option]…option{QUICK|FAST|MEDIUM|EXTENDED|mysql>checktable | | |Msg_type|Msg_text |sakila.sales|check| | 1rowinset(0.01 導 導mysql>mysql>optimizetable | ||Msg_type|Msg_text|sakila.sales|optimize| | 1rowinset(0.05|知知 3.4.1 loadingtheALTERTABLEtbl_nameENABLEDISABLEKEYS和ENABLEKEYS用來打開或關閉MyISAM表非 導 mysql>loaddatainfile‘/home/mysql/film_test.txt’intotablefilm_test2導 terminatedbyQueryOK,529056rowsaffected(1min55.12sec)mysql>altertablefilm_test2disablekeys;QueryOK,0rowsaffected(0.0sec)mysql>loaddatainfile‘/home/mysql/film_test.txt’intotableQueryOK,529056rowsaffected(6.34sec)mysql>altertablefilm_test2enablekeys;QueryOK,0rowsaffected(12.25 mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotableQuery rowsaffected(22.92 Deleted:0Skipped:0mysql>loaddatainfile‘/home/mysql/film_test4.txt’intotableQueryOK, rowsaffected(31.16sec) Deleted:0Skipped:0Warnings:0入 在導入數據前先執行setunique_checks=0，關閉唯 一性效驗，在導入結束后執行setunique_checks=1， 入mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotableQueryOK, rowsaffected(22.92sec) Deleted:0Skipped:0Warnings:0mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotableQueryOK, rowsaffected(19.92sec) 入知?在導入數據前先執行setmit=0，關閉自動提交事 務，在導入結束后執行setmit=1，恢復自動提 入當mit=1當mit=1mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotablefilm_test4;Queryrowsaffected(22.92sec)Deleted:0Skipped:0Warnings:0當mit=0mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotablefilm_test4;Queryrowsaffected(20.87Deleted:0Skipped:0 可以使用insertdelayed（馬上執行）語句得到更高可以增加bulk_insert_buffer_size變量值的方法來當從一個文件中裝載一個表時，使用LOADDATAmysql>mysql>inselectid,sum(moneys)fromsales2groupbyid:1table:sales2type:ALLkey:NULLref:NULLrows:1000Extra:Usingtemporary;Using1rowinset(0.003.4.3groupby導 ?如果查詢包含groupby但用戶想要避免排序結果 的損耗，則可以使用使用orderbynull來排 導如下沒有使用orderbynull來排序inselectinselectid,sum(moneys)fromsales2groupbyidordermysql>******************************************************1.rowid:1type:ALLkey:NULLref:NULLrows:1000Extra:Usingtemporary1rowinset(0.00sec)3.4.5 導入mysql> inselect*fromsales2 _idnotin(select ***************************1.row***************************id:1table:sales2type:ALLkey:NULLref:NULLrows:Extra:Using1rowinset(0.00 id:2 type:index_subquerypossible_keys:pany2_idkey:pany2_idkey_len:5ref:funcrows:2Extra:Using1rowinset(0.00sec第2/2下面是使用更有效 查詢 id:2select_type:入 入type:index_subquerypossible_keys:pany2_idkey:pany2_idkey_len:5ref:funcrows:1Extra:1rowinset(0.00sec第2/2知知 知4.1知 ?在MySQL中，可以使用函數PROCEDUREYSE（）對當前應用的表進行 mysql>select*fromduck_cust Field_n