mysql高級部分(非常實(shí)用的教學(xué)提綱

1、Good is good, but better carries it.精益求精，善益求善。mysql高級部分(非常實(shí)用的Mysql高級部分TOCo1-1hzuHYPERLINKl_Toc294126667（1）索引（index）PAGEREF_Toc294126667h1HYPERLINKl_Toc294126668（2）視圖（view）PAGEREF_Toc294126668h2HYPERLINKl_Toc294126669（3）觸發(fā)器（trigger）PAGEREF_Toc294126669h6HYPERLINKl_Toc294126670（4）游標(biāo)（cursor）PAGEREF_Toc

2、294126670h8HYPERLINKl_Toc294126671（5）事務(wù)(Transaction)PAGEREF_Toc294126671h10HYPERLINKl_Toc294126672（6）存儲過程（StoredProcedure）PAGEREF_Toc294126672h12索引（index）索引是一個單獨(dú)的、物理的數(shù)據(jù)庫結(jié)構(gòu)，它是某個表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識這些值的數(shù)據(jù)頁的邏輯指針清單。優(yōu)點(diǎn)：大大加快數(shù)據(jù)的檢索速度;創(chuàng)建唯一性索引，保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性;加速表和表之間的連接;在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時，可以顯著減少查詢中分組和排序的時

3、間。缺點(diǎn)：索引需要占物理空間當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時候，索引也要動態(tài)的維護(hù)，降低了數(shù)據(jù)的維護(hù)速度。更好的理解索引的提示：如果經(jīng)常使用表中的某一列或某幾列為條件進(jìn)行查詢，且表中的數(shù)據(jù)量比較大時，可以創(chuàng)建索引，以提高查詢的速度。索引是與表關(guān)聯(lián)的可選結(jié)構(gòu)。通過有目的的創(chuàng)建索引，可以加快對表執(zhí)行SELECT語句的速度。不管索引是否存在，都無需修改任何SQL語句的書寫方式。索引只是一種快速訪問數(shù)據(jù)的途徑，它只影響查詢執(zhí)行的效率?？梢允褂肅REATEINDEX命令在一列或若干列的組合上創(chuàng)建索引。創(chuàng)建索引時，將獲取要創(chuàng)建索引的列，并對其進(jìn)行排序。然后，將一個指針連同每一行的索引值存儲起來，組成

4、鍵值對（目錄名和頁碼）。使用索引時，系統(tǒng)首先通過已排序的列值執(zhí)行快速搜索，然后使用相關(guān)聯(lián)的指針值來定位具有所要查找值的行。一旦創(chuàng)建了索引，MySQL會自動維護(hù)和使用它們。只要修改了數(shù)據(jù)，如添加新行、更新現(xiàn)有行或刪除行，MySQL都會自動更新索引。但是為表創(chuàng)建過多的索引會降低更新、刪除以及插入的性能，因?yàn)镸ySQL還必須更新與該表關(guān)聯(lián)的索引。索引的分類普通索引：這是最基本的索引，它沒有任何限制唯一索引：它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一主鍵索引（通過主鍵約束間接創(chuàng)建）：它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候

5、同時創(chuàng)建主鍵索引組合索引：在表中的多個列上創(chuàng)建的索引。組合索引中列的順序是任意的，可以是相鄰的列，也可以是不相鄰的列。索引的創(chuàng)建：普通（唯一）索引的創(chuàng)建：CREATEUNIQUEINDEXindex_nameONtbl_name(index_col_name,)index_col_name:col_name(length)ASC|DESC對于字符類型的列，可以編制“前綴索引”，Length表示按照列的指定長度的字符串索引創(chuàng)建組合索引：CREATEINDEXindex_nameONtbl_name(index_col_name1,index_col_name2,.)基于（列A，列B）兩列創(chuàng)建索引

6、：可應(yīng)用索引的情況：A;AB兩列結(jié)合;不可用索引的情況：BCreateindexindex_nameontable_name(列A，列B)；其他創(chuàng)建索引的方式：創(chuàng)建表時創(chuàng)建索引：CREATETABLEtbl_name(列的定義，INDEX|KEYidx_name(index_col_name);Eg:CREATETABLEt1(tidintprimarykey,#既創(chuàng)建約束，又創(chuàng)建索引tnamevarchar(20),indexidx_tname(tname),#創(chuàng)建一個普通索引tbirthdaydate);修改表時創(chuàng)建索引:ALTERTABLEtbl_nameADDINDEX|KEYidx_

7、name(indxe_col_name);Eg:ALTERTABLEt1ADDKEY(tbirthday);查看索引：Showindex|keysfrom表名；（SHOWKEYSFROMt1;）刪除索引：dropindex索引名on表名。（dropindexcont3;）視圖（view）從用戶角度來看，一個視圖是從一個特定的角度來查看數(shù)據(jù)庫中的數(shù)據(jù)。從數(shù)據(jù)庫系統(tǒng)內(nèi)部來看，一個視圖是由SELECT語句組成的查詢定義的虛擬表。從數(shù)據(jù)庫系統(tǒng)內(nèi)部來看，視圖是由一張或多張表中的數(shù)據(jù)組成的，從數(shù)據(jù)庫系統(tǒng)外部來看，視圖就如同一張表一樣，對表能夠進(jìn)行的一般操作都可以應(yīng)用于視圖，例如查詢，插入，修改，刪除操作等

8、。視圖是一個虛擬表，其內(nèi)容由查詢定義。概述:視圖以經(jīng)過定制的方式顯示來自一個或多個表的數(shù)據(jù)視圖是一種數(shù)據(jù)庫對象，用戶可以象查詢普通表一樣查詢視圖。視圖內(nèi)其實(shí)沒有存儲任何數(shù)據(jù)，它只是對表的一個查詢。視圖的定義保存在數(shù)據(jù)字典內(nèi)。創(chuàng)建視圖所基于的表為“基表”。視圖一經(jīng)定義以后，就可以像表一樣被查詢、修改、刪除和更新作用：簡化數(shù)據(jù)查詢語句使用戶能從多角度看到同一數(shù)據(jù)提高了數(shù)據(jù)的安全性提供了一定程度的邏輯獨(dú)立性減少帶寬流量、優(yōu)化后還可提高執(zhí)行效率優(yōu)點(diǎn)：提供了另外一種級別的表安全性隱藏的數(shù)據(jù)的復(fù)雜性簡化的用戶的SQL命令通過重命名列，從另一個角度提供數(shù)據(jù)視圖的創(chuàng)建：CREATEORREPLACEALGOR

9、ITHM=UNDEFINED|MERGE|TEMPTABLEVIEWview_name(column_list)ASselect_statementWITHCASCADED|LOCALCHECKOPTION說明：ORREPLACE：給定了ORREPLACE子句，語句能夠替換已有的同名視圖。ALGORITHM：可選的mysql算法擴(kuò)展，算法會影響MySQL處理視圖的方式。有以下三個值：UNDEFINED-MySQL將選擇所要使用的算法。如果可能，它傾向于MERGE而不是TEMPTABLE，這是因?yàn)镸ERGE通常更有效，而且如果使用了臨時表，視圖是不可更新的。MERGE-會將引用視圖的語句的文本與

10、視圖定義合并起來，使得視圖定義的某一部分取代語句的對應(yīng)部分。TEMPTABLE-視圖的結(jié)果將被置于臨時表中，然后使用它執(zhí)行語句。veiw_name:視圖名。column_list：要想為視圖的列定義明確的名稱，列出由逗號隔開的列名。column_list中的名稱數(shù)目必須等于SELECT語句檢索的列數(shù)。若使用與源表或視圖中相同的列名時可以省略column_list。select_statement:用來創(chuàng)建視圖的SELECT語句，可在SELECT語句中查詢多個表或視圖。但對SELECT語句有以下的限制：1.定義視圖的用戶必須對所參照的表或視圖有查詢（即可執(zhí)行SELECT語句）權(quán)限；2.在定義中引

11、用的表或視圖必須存在；WITHcascaded|localCHECKOPTION:在關(guān)于可更新視圖的WITHCHECKOPTION子句中，當(dāng)視圖是根據(jù)另一個視圖定義的時，LOCAL和CASCADED關(guān)鍵字決定了檢查測試的范圍。LOCAL關(guān)鍵字對CHECKOPTION進(jìn)行了限制，使其僅作用在定義的視圖上，CASCADED會對將進(jìn)行評估的基表進(jìn)行檢查。如果未給定任一關(guān)鍵字，默認(rèn)值為CASCADED。WITHCHECKOPTION指出在可更新視圖上所進(jìn)行的修改都要符合select_statement所指定的限制條件，這樣可以確保數(shù)據(jù)修改后，仍可通過視圖看到修改的數(shù)據(jù)。視圖定義服從下述限制：SELEC

12、T語句不能包含F(xiàn)ROM子句中的子查詢。SELECT語句不能引用系統(tǒng)或用戶變量。SELECT語句不能引用預(yù)處理語句參數(shù)。在存儲子程序內(nèi)，定義不能引用子程序參數(shù)或局部變量。在定義中引用的表或視圖必須存在。但是，創(chuàng)建了視圖后，能夠舍棄定義引用的表或視圖。要想檢查視圖定義是否存在這類問題，可使用CHECKTABLE語句。在定義中不能引用TEMPORARY表，不能創(chuàng)建TEMPORARY視圖。在視圖定義中命名的表必須已存在。不能將觸發(fā)程序與視圖關(guān)聯(lián)在一起。修改視圖：ALTERALGORITHM=UNDEFINED|MERGE|TEMPTABLEVIEWview_name(column_list)ASsel

13、ect_statementWITHCASCADED|LOCALCHECKOPTION說明：該語句用于更改已有視圖的定義。其語法與CREATEVIEW類似。該語句需要具有針對視圖的CREATEVIEW和DROP權(quán)限，也需要針對SELECT語句中引用的每一列的某些權(quán)限。查看視圖：SHOWCREATEVIEWview_name說明：該語句給出了1個創(chuàng)建給定視圖的CREATEVIEW語句。刪除視圖：DROPVIEWIFEXISTSview_name,view_name.RESTRICT|CASCADE說明：DROPVIEW能夠刪除1個或多個視圖。必須在每個視圖上擁有DROP權(quán)限?？梢允褂藐P(guān)鍵字IFEX

14、ISTS來防止因不存在的視圖而出錯。如果給定了RESTRICT和CASCADE，將解析并忽略它們。更新視圖：概述：視圖的使用與表一樣，有增刪改查四種操作，且語法也與表相同。在視圖上也可以使用修改數(shù)據(jù)的DML語句，如INSERT、UPDATE和DELETE可以統(tǒng)稱為“通過視圖更新數(shù)據(jù)”。通過視圖更新數(shù)據(jù)有如下限制：一次只能修改一個底層的基表如果修改違反了基表的約束條件，則無法更新視圖如果視圖中的列不是表中的原始列（如創(chuàng)建視圖時使用了連接操作符、聚合函數(shù)等），則不能通過視圖更新。視圖更新操作：可更新的視圖：要通過視圖更新基本表數(shù)據(jù)，必須保證視圖是可更新視圖，即可以在INSET、UPDATE或DEL

15、ETE等語句當(dāng)中使用它們。對于可更新的視圖，在視圖中的行和基表中的行之間必須具有一對一的關(guān)系。還有一些特定的其他結(jié)構(gòu)，這類結(jié)構(gòu)會使得視圖不可更新。如果視圖包含下述結(jié)構(gòu)中的任何一種，那么它就是不可更新的：聚合函數(shù)；DISTINCT關(guān)鍵字；GROUPBY子句；ORDERBY子句；HAVING子句；UNION運(yùn)算符；位于選擇列表中的子查詢；FROM子句中包含多個表；SELECT語句中引用了不可更新視圖；插入數(shù)據(jù)：使用INSERT語句通過視圖向基本表插入數(shù)據(jù)注意：當(dāng)視圖所依賴的基本表有多個時，不能向該視圖插入數(shù)據(jù)，因?yàn)檫@將會影響多個基本表。對INSERT語句還有一個限制：SELECT語句中必須包含F(xiàn)R

16、OM子句中指定表的所有不能為空的列。修改數(shù)據(jù)：使用UPDATE語句可以通過視圖修改基本表的數(shù)據(jù)注意：若一個視圖依賴于多個基本表，則一次修改該視圖只能變動一個基本表的數(shù)據(jù)。刪除數(shù)據(jù)：使用DELETE語句可以通過視圖刪除基本表的數(shù)據(jù)注意：對依賴于多個基本表的視圖，不能使用DELETE語句。觸發(fā)器（trigger）它是個特殊的HYPERLINK/view/68525.htmt_blank存儲過程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動，而是由事件來觸發(fā)，比如當(dāng)對一個表進(jìn)行操作（insert，delete，update）時就會激活它執(zhí)行。觸發(fā)器經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。觸發(fā)器可以從DB

17、A_TRIGGERS，USER_TRIGGERS數(shù)據(jù)字典中查到。為什么要使用觸發(fā)器：可以使用它來檢查或預(yù)防壞的數(shù)據(jù)進(jìn)入數(shù)據(jù)庫?？梢愿淖兓蛉∠鸌NSERT、UPDATE、以及DELETE語句。可以在一個會話中監(jiān)視數(shù)據(jù)改變的動作。創(chuàng)建觸發(fā)器：CREATEDEFINER=user|CURRENT_USERTRIGGERBEFORE|AFTERINSERT|UPDATE|DELETEONFOREACHROW說明：DEFINER：TheDEFINERclausespecifiestheMySQLaccounttobeusedwhencheckingaccessprivilegesattriggeract

18、ivationtime.Ifauservalueisgiven,itshouldbeaMySQLaccountspecifiedasuser_namehost_name。觸發(fā)器名稱：觸發(fā)器必須有名字，最多64個字符，可能后面會附有分隔符.它和MySQL中其他對象的命名方式基本相象觸發(fā)程序的動作時間：BEFOREAFTER.可以設(shè)置為事件發(fā)生前或后.事件：指明了激活觸發(fā)程序的語句的類型。可以是下述值之一：INSERT：將新行插入表時激活觸發(fā)程序，例如，通過INSERT、LOADDATA和REPLACE語句。UPDATE：更改某一行時激活觸發(fā)程序，例如，通過UPDATE語句。DELETE：從表中刪

19、除某一行時激活觸發(fā)程序，例如，通過DELETE和REPLACE語句。表：觸發(fā)器是屬于某一個表的:當(dāng)在這個表上執(zhí)行插入、更新或刪除操作的時候就導(dǎo)致觸發(fā)器的激活。我們不能給同一張表的同一個事件安排兩個觸發(fā)器，而且必須引用永久性表，不能將觸發(fā)程序與TEMPORARY表或視圖關(guān)聯(lián)起來。觸發(fā)間隔：FOREACHROW通知觸發(fā)器每隔一行執(zhí)行一次動作，而不是對整個表執(zhí)行一次。關(guān)于舊的和新創(chuàng)建的列的標(biāo)識在觸發(fā)器的SQL語句中，你可以關(guān)聯(lián)表中的任意列。但你不能僅使用列的名稱去標(biāo)識，那會使系統(tǒng)混淆，因?yàn)槟抢锟赡軙辛械男旅ㄟ@可能正是你要修改的，你的動作可能正是要修改列名），還有列的舊名存在。因此你必須用這樣的語

20、法來標(biāo)識：NEW.column_name或者OLD.column_name.這樣在技術(shù)上處理（NEW|OLD.column_name）新和舊的列名屬于創(chuàng)建了過渡變量（transitionvariables）。對于INSERT語句,只有NEW是合法的；對于DELETE語句，只有OLD才合法；而UPDATE語句可以在和NEW以及OLD同時使用。下面是一個UPDATE中同時使用NEW和OLD的例子。CREATETRIGGERt21_auBEFOREUPDATEONt22FOREACHROWBEGINSETold=OLD.s1;SETnew=NEW.s1;END;觸發(fā)的SQL語句：是當(dāng)觸發(fā)程序激活時執(zhí)

21、行的語句。如果你打算執(zhí)行多個語句，可使用BEGIN.END復(fù)合語句結(jié)構(gòu)。這樣，就能使用存儲子程序中允許的相同語句。刪除觸發(fā)器：DROPTRIGGERschema_name.trigger_name說明：方案名稱（schema_name）是可選的。如果省略了schema（方案），將從當(dāng)前方案中舍棄觸發(fā)程序。DROPTRIGGER語句需要SUPER權(quán)限。查詢觸發(fā)器：SHOWTRIGGERSFROM|INdb_nameLIKEpattern|WHEREexprmysqlSHOWTRIGGERSLIKEacc%G*1.row*Trigger:ins_sumEvent:INSERTTable:accou

22、ntStatement:SETsum=sum+NEW.amountTiming:BEFORECreated:NULLsql_mode:Definer:mynamelocalhostcharacter_set_client:latin1collation_connection:latin1_swedish_ciDatabaseCollation:latin1_swedish_ciTrigger：Thenameofthetrigger.Event：Theeventthatcausestriggeractivation:oneofINSERT,UPDATE,orDELETE.Table：Thetab

23、leforwhichthetriggerisdefined.Statement：Thestatementtobeexecutedwhenthetriggerisactivated.ThisisthesameasthetextshownintheACTION_STATEMENTcolumnofHYPERLINK/doc/refman/5.1/en/triggers-table.htmlo19.16.TheINFORMATION_SCHEMATRIGGERSTableINFORMATION_SCHEMA.TRIGGERS.Timing：OneofthetwovaluesBEFOREorAFTER.

24、Created：Currently,thevalueofthiscolumnisalwaysNULL.sql_mode：TheSQLmodeineffectwhenthetriggerexecutes.Definer：Theaccountthatcreatedthetrigger游標(biāo)（cursor）是系統(tǒng)為用戶開設(shè)的一個HYPERLINK/view/1503706.htmt_blank數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果。每個游標(biāo)區(qū)都有一個名字。用戶可以用SQL語句逐一從游標(biāo)中獲取記錄，并賦給主變量，交由主語言進(jìn)一步處理。概述：游標(biāo)提供了一種對從表中檢索出的數(shù)據(jù)進(jìn)行操作的靈活手段，就本質(zhì)而言，

25、游標(biāo)實(shí)際上是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。游標(biāo)總是與一條SQL選擇語句相關(guān)聯(lián)因?yàn)橛螛?biāo)由結(jié)果集（可以是零條、一條或由相關(guān)的選擇語句檢索出的多條記錄）和結(jié)果集中指向特定記錄的游標(biāo)位置組成。當(dāng)決定對結(jié)果集進(jìn)行處理時，必須聲明一個指向該結(jié)果集的游標(biāo)。如果曾經(jīng)用C語言寫過對文件進(jìn)行處理的程序，那么游標(biāo)就像您打開文件所得到的文件句柄一樣，只要文件打開成功，該文件句柄就可代表該文件。對于游標(biāo)而言，其道理是相同的?？梢娪螛?biāo)能夠?qū)崿F(xiàn)按與傳統(tǒng)程序讀取平面文件類似的方式處理來自基礎(chǔ)表的結(jié)果集，從而把表中數(shù)據(jù)以平面文件的形式呈現(xiàn)給程序。游標(biāo)實(shí)現(xiàn)了對mysql的存儲過程中循環(huán)讀取數(shù)據(jù)表中的對

26、象的過程。游標(biāo)的特性：READONLY只讀，只能取值而不能賦值；NOTSCROOLABLE不可回滾，只能順序讀取；ASENSITIVE敏感，不能在已經(jīng)打開游標(biāo)的表上執(zhí)行update事務(wù)；游標(biāo)操作：聲明游標(biāo)：DECLAREcursor_nameCURSORFORselect_statement這個語句聲明一個光標(biāo)。也可以在子程序中定義多個光標(biāo)，但是一個塊中的每一個光標(biāo)必須有唯一的名字。打開游標(biāo):OPENcursor_name游標(biāo)FETCH：FETCHcursor_nameINTOvar_name,var_name.這個語句用指定的打開光標(biāo)讀取下一行（如果有下一行的話），并且前進(jìn)光標(biāo)指針。關(guān)閉游標(biāo)

27、CLOSE：CLOSEcursor_name這個語句關(guān)閉先前打開的光標(biāo)。如果未被明確地關(guān)閉，光標(biāo)在它被聲明的復(fù)合語句的末尾被關(guān)閉。使用游標(biāo)的實(shí)例：CREATEPROCEDUREcurdemo(tblNameVARCHAR(100)BEGINDECLAREdoneINTDEFAULT0;DECLAREb,cINT;DECLAREcur1CURSORFORSELECTidFROMv_wondyfox;聲明游標(biāo)DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;dropviewifexistsv_wondyfox;setsql=concat(createviewv_

28、wondyfoxasselect*from,tblName);PREPAREstmt1FROMsql;EXECUTEstmt1;DEALLOCATEPREPAREstmt1;OPENcur1;打開游標(biāo)REPEATFETCHcur1INTOb;獲取游標(biāo)內(nèi)容selectb;UNTILdoneENDREPEAT;CLOSEcur1;關(guān)閉游標(biāo)ENDCREATEPROCEDUREcurdemo()BEGINDECLAREdoneINTDEFAULT0;DECLAREaCHAR(16);DECLAREb,cINT;DECLAREcur1CURSORFORSELECTid,dataFROMtest.t1;D

29、ECLAREcur2CURSORFORSELECTiFROMtest.t2;DECLARECONTINUEHANDLERFORSQLSTATE02000SETdone=1;OPENcur1;OPENcur2;REPEATFETCHcur1INTOa,b;FETCHcur2INTOc;IFNOTdoneTHENIFbshowengines;(或執(zhí)行mysql-showvariableslikehave_%;),查看InnoDB為YES,即表示數(shù)據(jù)庫支持InnoDB了。也就說明支持事務(wù)transaction了。4.在創(chuàng)建表時，就可以為StorageEngine選擇InnoDB引擎了。如果是以前創(chuàng)建

30、的表，可以使用mysql-altertabletable_nametype=InnoDB;或mysql-altertabletable_nameengine=InnoDB;來改變數(shù)據(jù)表的引擎以支持事務(wù)。實(shí)例：/*transaction-1*/$conn=mysql_connect(localhost,root,root)ordie(數(shù)據(jù)連接錯誤!);mysql_select_db(test,$conn);mysql_query(setnamesGBK);/使用GBK中文編碼;/開始一個事務(wù)mysql_query(BEGIN);/或者mysql_query(STARTTRANSACTION);$

31、sql=INSERTINTOuser(id,username,sex)VALUES(NULL,test1,0);$sql2=INSERTINTOuser(did,username,sex)VALUES(NULL,test1,0);/這條我故意寫錯$res=mysql_query($sql);$res1=mysql_query($sql2);if($res&$res1)mysql_query(COMMIT);echo提交成功。;elsemysql_query(ROLLBACK);echo數(shù)據(jù)回滾。;mysql_query(END);/*transaction-2*/*方法二*/mysql_que

32、ry(SETAUTOCOMMIT=0);/設(shè)置mysql不自動提交，需自行用commit語句提交$sql=INSERTINTOuser(id,username,sex)VALUES(NULL,test1,0);$sql2=INSERTINTOuser(did,username,sex)VALUES(NULL,test1,0);/這條我故意寫錯$res=mysql_query($sql);$res1=mysql_query($sql2);if($res&$res1)mysql_query(COMMIT);echo提交成功。;elsemysql_query(ROLLBACK);echo數(shù)據(jù)回滾。;

33、mysql_query(END);/事務(wù)處理完時別忘記mysql_query(SETAUTOCOMMIT=1);自動提交/*對于不支持事務(wù)的MyISAM引擎數(shù)據(jù)庫可以使用表鎖定的方法*/MyISAM&InnoDB都支持,/*LOCKTABLES可以鎖定用于當(dāng)前線程的表。如果表被其它線程鎖定，則造成堵塞，直到可以獲取所有鎖定為止。UNLOCKTABLES可以釋放被當(dāng)前線程保持的任何鎖定。當(dāng)線程發(fā)布另一個LOCKTABLES時，或當(dāng)與服務(wù)器的連接被關(guān)閉時，所有由當(dāng)前線程鎖定的表被隱含地解鎖。*/mysql_query(LOCKTABLESuserWRITE);/鎖住user表$sql=INSERT

34、INTOuser(id,username,sex)VALUES(NULL,test1,0);$res=mysql_query($sql);if($res)echo提交成功。!;elseecho失敗!;mysql_query(UNLOCKTABLES);/解除鎖定存儲過程（StoredProcedure）是一組為了完成特定功能的SQL語句集，是利用SQLServer所提供的Transact-SQL語言所編寫的程序。經(jīng)編譯后存儲在數(shù)據(jù)庫中。存儲過程是數(shù)據(jù)庫中的一個重要對象，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。存儲過程是由流控制和SQL語句書寫的過程，這個過程經(jīng)編

35、譯和優(yōu)化后存儲在數(shù)據(jù)庫服務(wù)器中，存儲過程可由應(yīng)用程序通過一個調(diào)用來執(zhí)行，而且允許用戶聲明變量。同時，存儲過程可以接收和輸出參數(shù)、返回執(zhí)行存儲過程的狀態(tài)值，也可以嵌套調(diào)用。為什么要使用存儲過程：存儲過程是已經(jīng)被認(rèn)證的技術(shù)！存儲過程會使系統(tǒng)運(yùn)行更快！存儲過程是可復(fù)用的組件！它是數(shù)據(jù)庫邏輯而不是應(yīng)用程序。存儲過程將被保存！存儲過程的優(yōu)點(diǎn)：存儲過程只在創(chuàng)造時進(jìn)行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次,所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。當(dāng)對數(shù)據(jù)庫進(jìn)行復(fù)雜操作時（如對多個表進(jìn)行Update、Insert、Query、Delete時），可將此復(fù)雜操作用存儲過程封

36、裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。存儲過程可以重復(fù)使用，可減少數(shù)據(jù)庫開發(fā)人員的工作量。安全性高，可設(shè)定只有某此用戶才具有對指定存儲過程的使用權(quán)。存儲過程與函數(shù)的區(qū)別：自定義函數(shù)有且只有一個返回值，就像普通的函數(shù)一樣，可以直接在表達(dá)式中嵌入調(diào)用。存儲過程可以沒有返回值，也可以有任意個輸出參數(shù)，必須單獨(dú)調(diào)用。執(zhí)行的本質(zhì)都一樣。只是函數(shù)有如只能返回一個變量的限制。而存儲過程可以返回多個。而函數(shù)是可以嵌入在sql中使用的，可以在select中調(diào)用，而存儲過程不行。函數(shù)限制比較多，比如不能用臨時表，只能用表變量。還有一些函數(shù)都不可用等等。而存儲過程的限制相對就比較少函數(shù)限制比較多，比如不能用臨時

37、表，只能用表變量。還有一些函數(shù)都不可用等等。而存儲過程的限制相對就比較少一般來說，存儲過程實(shí)現(xiàn)的功能要復(fù)雜一點(diǎn)，而函數(shù)的實(shí)現(xiàn)的功能針對性比較強(qiáng)。對于存儲過程來說可以返回參數(shù)，而函數(shù)只能返回值或者表對象。存儲過程一般是作為一個獨(dú)立的部分來執(zhí)行，而函數(shù)可以作為查詢語句的一個部分來調(diào)用，由于函數(shù)可以返回一個表對象，因此它可以在查詢語句中位于FROM關(guān)鍵字的后面。創(chuàng)建存儲過程：CREATEDEFINER=user|CURRENT_USERPROCEDUREsp_name(proc_parameter,.)characteristic.routine_body說明：DEFINER：指明使用存儲過程的訪問

38、權(quán)限。sp_name:存儲過程名稱。proc_parameter:IN|OUT|INOUTparam_nametypein：表示向存儲過程中傳入?yún)?shù)；存儲過程默認(rèn)為傳入?yún)?shù)，所以參數(shù)in可以省略；out：表示向外傳出參數(shù)；inout：表示定義的參數(shù)可傳入存儲過程，并可以被存儲過程修改后傳出存儲過程；param_name：參數(shù)名；type：參數(shù)的類型，可以為mysql任何合法得數(shù)據(jù)類型。如果有多個參數(shù)，參數(shù)之間可以用逗號進(jìn)行分割。Characteristic：LANGUAGESQL|NOTDETERMINISTIC|CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESS

39、QLDATA|SQLSECURITYDEFINER|INVOKER|COMMENTstring這個LANGUAGESQL子句是沒有作用的。僅僅是為了說明下面過程的主體使用SQL語言編寫。這條是系統(tǒng)默認(rèn)的。如果程序或線程總是對同樣的輸入?yún)?shù)產(chǎn)生同樣的結(jié)果，則被認(rèn)為它是“確定的”（DETERMINISTIC），否則就是“非確定”的。默認(rèn)的就是NOTDETERMINISTIC。CONTAINSSQL表示子程序不包含讀或?qū)憯?shù)據(jù)的語句。NOSQL表示子程序不包含SQL語句。READSSQLDATA表示子程序包含讀數(shù)據(jù)的語句，但不包含寫數(shù)據(jù)的語句。MODIFIESSQLDATA表示子程序包含寫數(shù)據(jù)的語句。

40、如果這些特征沒有明確給定，默認(rèn)的是CONTAINSSQL。SQLSECURITY特征可以用來指定子程序該用創(chuàng)建子程序者的許可來執(zhí)行，還是使用調(diào)用者的許可來執(zhí)行。默認(rèn)值是DEFINER。COMMENT子句是一個MySQL的擴(kuò)展，它可以被用來描述存儲程序。這個信息被SHOWCREATEPROCEDURE和SHOWCREATEFUNCTION語句來顯示。存儲子程序不能使用LOADDATAINFILE。特征子句也有默認(rèn)值，如果省略了就相當(dāng)于：LANGUAGESQLNOTDETERMINISTICSQLSECURITYDEFINERCOMMENTroutine_body:包含合法的SQL過程語句?？梢允?/p>

41、用復(fù)合語句語法,復(fù)合語句可以包含聲明，循環(huán)和其它控制結(jié)構(gòu)語句。修改存儲過程：ALTERPROCEDUREsp_namecharacteristic.說明：這個語句可以被用來改變一個存儲程序的特征。必須用ALTERROUTINE權(quán)限才可用此子程序。這個權(quán)限被自動授予子程序的創(chuàng)建者。在ALTERPROCEDURE語句中，可以指定超過一個的改變。刪除存儲過程：DROPPROCEDURE|FUNCTIONIFEXISTSsp_name不能在一個存儲過程中刪除另一個存儲過程，只能調(diào)用另一個存儲過程顯示存儲過程：SHOWCREATEPROCEDUREsp_name似于SHOWCREATETABLE，它返回

42、一個可用來重新創(chuàng)建已命名子程序的確切字符串。顯示存儲過程特征：SHOWPROCEDURESTATUSLIKEpattern它返回子程序的特征，如數(shù)據(jù)庫，名字，類型，創(chuàng)建者及創(chuàng)建和修改日期。調(diào)用存儲過程：CALLsp_name(parameter,.)調(diào)用一個先前用CREATEPROCEDURE創(chuàng)建的程序。CALL語句可以用聲明為OUT或的INOUT參數(shù)的參數(shù)給它的調(diào)用者傳回值。它也“返回”受影響的行數(shù)，客戶端程序可以在SQL級別通過調(diào)用ROW_COUNT()函數(shù)獲得這個數(shù)，從C中是調(diào)用themysql_affected_rows()CAPI函數(shù)來獲得。存儲過程實(shí)例（基本的創(chuàng)建、調(diào)用、刪除語法）

43、：delimiter/DROPPROCEDUREIFEXISTStest/如果存在test則刪除CREATEPROCEDUREtest/*存儲過程名*/(INinparmsINT,OUToutparamsvarchar(32)/*輸入、輸出參數(shù)*/BEGIN/*語句塊頭*/DECLAREvarCHAR(10);/*變量聲明*/IFinparms=1THEN/*IF條件開始*/SETvar=hello;/*賦值*/ELSESETvar=world;ENDIF;/*IF結(jié)束*/INSERTINTOt1VALUES(var);/*SQL語句*/SELECTnameFROMt1LIMIT1INTOou

44、tparams;END/delimiter;calltest(1,out);/*調(diào)用存儲過程*/存儲過程的變量：聲明變量：DECLAREvar_name,.typeDEFAULTvalue這個語句被用來聲明局部變量。要給變量提供一個默認(rèn)值，需要包含一個DEFAULT子句。值可以被指定為一個表達(dá)式，不需要為一個常數(shù)。如果沒有DEFAULT子句，初始值為NULL。局部變量的作用范圍在它被聲明的BEGIN.END塊內(nèi)。它可以被用在嵌套的塊中，除了那些用相同名字聲明變量的塊。變量賦值，SET語句：SETvar_name=expr,var_name=expr.也可以用語句代替SET來為用戶變量分配一個值

45、。在這種情況下，分配符必須為:=而不能用=，因?yàn)樵诜荢ET語句中=被視為一個比較操作符，如下所示：mysqlSETt1=0,t2=0,t3=0;mysqlSELECTt1:=0,t2:=0,t3:=0;對于使用select語句為變量賦值的情況，若返回結(jié)果為空，即沒有記錄，此時變量的值為上一次變量賦值時的值，如果沒有對變量賦過值，則為NULL。變量賦值，SELECT.INTO語句SELECTcol_name,.INTOvar_name,.table_expr這個SELECT語法把選定的列直接存儲到變量。因此，只有單一的行可以被取回。SELECTid,dataINTOx,yFROMtest.t1L

46、IMIT1;存儲過程的語句：BEGIN.END復(fù)合語句begin_label:BEGINstatement_listENDend_label存儲子程序可以使用BEGIN.END復(fù)合語句來包含多個語句。statement_list代表一個或多個語句的列表。statement_list之內(nèi)每個語句都必須用分號（；）來結(jié)尾。復(fù)合語句可以被標(biāo)記。除非begin_label存在，否則end_label不能被給出，并且如果二者都存在，他們必須是同樣的流程控制結(jié)構(gòu)IF語句IFsearch_conditionTHENstatement_listELSEIFsearch_conditionTHENstateme

47、nt_list.ELSEstatement_listENDIFstatement_list可以包括一個或多個語句。舉例：DELIMITER/CREATEPROCEDUREp1(INparameter1INT)BEGINDECLAREvariable1INT;SETvariable1=parameter1+1;IFvariable1=0THENINSERTINTOtVALUES(17);ENDIF;IFparameter1=0THENUPDATEtSETs1=s1+1;ELSEUPDATEtSETs1=s1+2;ENDIF;END;/DELIMITER;CASE語句CASEcase_valueW

48、HENwhen_valueTHENstatement_listWHENwhen_valueTHENstatement_list.ELSEstatement_listENDCASEOr:CASEWHENsearch_conditionTHENstatement_listWHENsearch_conditionTHENstatement_list.ELSEstatement_listENDCASE舉例：CREATEPROCEDUREp2(INparameter1INT)BEGINDECLAREvariable1INT;SETvariable1=parameter1+1;CASEvariable1W

49、HEN0THENINSERTINTOtVALUES(17);WHEN1THENINSERTINTOtVALUES(18);ELSEINSERTINTOtVALUES(19);ENDCASE;END;/循環(huán)語句WHILEENDWHILELOOPENDLOOPREPEATENDREPEATGOTO前三種是標(biāo)準(zhǔn)的循環(huán)方式，至于GOTO就如C語言里的GOTO一樣，盡量少用！在循環(huán)中還穿插一些循環(huán)控制語句，如LEAVE（類似C語言的break）、ITERATE（類似C語言的continue）等。LEAVE語句LEAVElabel這個語句被用來退出任何被標(biāo)注的流程控制構(gòu)造。它和BEGIN.END或循環(huán)一起

50、被使用。ITERATE語句ITERATElabelITERATE只可以出現(xiàn)在LOOP,REPEAT,和WHILE語句內(nèi)。ITERATE意思為：再次循環(huán)。WHILEENDWHILE舉例：CREATEPROCEDUREp4()BEGINDECLAREvINT;SETv=0;WHILEv=5THENLEAVEloop_label;ENDIF;ENDLOOP;END;/begin_label:LOOPstatement_listENDLOOPend_labelLOOP允許某特定語句或語句群的重復(fù)執(zhí)行，實(shí)現(xiàn)一個簡單的循環(huán)構(gòu)造。在循環(huán)內(nèi)的語句一直重復(fù)直到循環(huán)被退出，退出通常伴隨著一個LEAVE語句。REP

51、EATENDREPEAT舉例：CREATEPROCEDUREp6()BEGINDECLAREvINT;SETv=0;REPEATINSERTINTOtVALUES(v);SETv=v+1;UNTILv=5ENDREPEAT;END;/迭代（ITERATE）語句CREATEPROCEDUREp7()BEGINDECLAREvINT;SETv=0;loop_label:LOOPIFv=3THENSETv=v+1;ITERATEloop_label;ENDIF;INSERTINTOtVALUES(v);SETv=v+1;IFv=5THENLEAVEloop_label;ENDIF;ENDLOOP;E

52、ND;/存儲過程的注釋語法：mysql存儲過程可使用兩種風(fēng)格的注釋雙模杠：-，該風(fēng)格一般用于單行注釋c風(fēng)格：/*注釋內(nèi)容*/，一般用于多行注釋存儲過程的條件和異常處理程序：DECLAREhandler_typeHANDLERFORcondition_value,.sp_statementhandler_type:CONTINUE|EXIT|UNDOcondition_value:SQLSTATEVALUEsqlstate_value|condition_name|SQLWARNING|NOTFOUND|SQLEXCEPTION這個語句指定每個可以處理一個或多個條件的處理程序。如果產(chǎn)生一個或多個

53、條件，指定的語句被執(zhí)行。對一個CONTINUE處理程序，當(dāng)前子程序的執(zhí)行在執(zhí)行處理程序語句之后繼續(xù)。對于EXIT處理程序，當(dāng)前BEGIN.END復(fù)合語句的執(zhí)行被終止。UNDO處理程序類型語句還不被支持。SQLWARNING是對所有以01開頭的SQLSTATE代碼的速記。NOTFOUND是對所有以02開頭的SQLSTATE代碼的速記。SQLEXCEPTION是對所有沒有被SQLWARNING或NOTFOUND捕獲的SQLSTATE代碼的速記。聲明自定義條件：DECLAREcondition_nameCONDITIONFORcondition_valuecondition_value:SQLSTA

54、TEVALUEsqlstate_value舉例：CREATETABLEtest.t(s1int,primarykey(s1);delimiter/CREATEPROCEDUREhandlerdemo()BEGINDECLARECONTINUEHANDLERFORSQLSTATE23000SETx2=1;SETx=1;INSERTINTOtest.tVALUES(1);SETx=2;INSERTINTOtest.tVALUES(1);SETx=3;END;/delimiter;存儲過程綜合實(shí)例：（包含事務(wù),參數(shù),嵌套調(diào)用,游標(biāo),循環(huán)等）dropprocedureifexistspro_rep_s

55、hadow_rs;delimiter|-rep_shadow_rs-用來處理信息的增加，更新和刪除-每次只更新上次以來沒有做過的數(shù)據(jù)-根據(jù)不同的標(biāo)志位-需要一個輸出的參數(shù)，-如果返回為0，則調(diào)用失敗，事務(wù)回滾-如果返回為1，調(diào)用成功，事務(wù)提交-測試方法-callpro_rep_shadow_rs(rtn);-selectrtn;createprocedurepro_rep_shadow_rs(outrtnint)begin-聲明變量，所有的聲明必須在非聲明的語句前面declareiLast_rep_sync_idintdefault-1;declareiMax_rep_sync_idintde

56、fault-1;-如果出現(xiàn)異常，或自動處理并rollback,但不再通知調(diào)用方了-如果希望應(yīng)用獲得異常，需要將下面這一句，以及啟動事務(wù)和提交事務(wù)的語句全部去掉declareexithandlerforsqlexceptionrollback;-查找上一次的selecteidintoiLast_rep_sync_idfromrep_de_proc_logwheretbl=rep_shadow_rs;-如果不存在，則增加一行ifiLast_rep_sync_id=-1theninsertintorep_de_proc_log(rid,eid,tbl)values(0,0,rep_shadow_rs

57、);setiLast_rep_sync_id=0;endif;-下一個數(shù)字setiLast_rep_sync_id=iLast_rep_sync_id+1;-設(shè)置默認(rèn)的返回值為0：失敗setrtn=0;-啟動事務(wù)starttransaction;-查找最大編號selectmax(rep_sync_id)intoiMax_rep_sync_idfromrep_shadow_rs;-有新數(shù)據(jù)ifiMax_rep_sync_id=iLast_rep_sync_idthen-調(diào)用callpro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id);-

58、更新日志updaterep_de_proc_logsetrid=iLast_rep_sync_id,eid=iMax_rep_sync_idwheretbl=rep_shadow_rs;endif;-運(yùn)行沒有異常，提交事務(wù)commit;-設(shè)置返回值為1setrtn=1;end;|delimiter;dropprocedureifexistspro_rep_shadow_rs_do;delimiter|-處理指定編號范圍內(nèi)的數(shù)據(jù)-需要輸入2個參數(shù)-last_rep_sync_id是編號的最小值-max_rep_sync_id是編號的最大值-無返回值createprocedurepro_rep_s

59、hadow_rs_do(last_rep_sync_idint,max_rep_sync_idint)begindeclareiRep_operationtypevarchar(1);declareiRep_statusvarchar(1);declareiRep_Sync_idint;declareiIdint;-這個用于處理游標(biāo)到達(dá)最后一行的情況declarestopintdefault0;-聲明游標(biāo)declarecurcursorforselectid,Rep_operationtype,iRep_status,rep_sync_idfromrep_shadow_rswhererep_s

60、ync_idbetweenlast_rep_sync_idandmax_rep_sync_id;-聲明游標(biāo)的異常處理，設(shè)置一個終止標(biāo)記declareCONTINUEHANDLERFORSQLSTATE02000SETstop=1;-打開游標(biāo)opencur;-讀取一行數(shù)據(jù)到變量fetchcurintoiId,iRep_operationtype,iRep_status,iRep_Sync_id;-這個就是判斷是否游標(biāo)已經(jīng)到達(dá)了最后whilestop1do-各種判斷ifiRep_operationtype=Itheninsertintors0811(id,fnbm)selectid,fnbmfro