MySQL數(shù)據(jù)庫基礎及實例教程第6章課件

肖紅MySQL數(shù)據(jù)庫基礎與實例教程

之MySQL編程基礎肖紅MySQL數(shù)據(jù)庫基礎與實例教程為了便于MySQL代碼維護，以及提高MySQL代碼的重用性，MySQL開發(fā)人員經(jīng)常將頻繁使用的業(yè)務邏輯封裝成存儲程序，MySQL的存儲程序分為四類：函數(shù)、觸發(fā)器、存儲過程以及事件。內(nèi)容一覽為了便于MySQL代碼維護，以及提高MySQL代碼的123本章首先介紹了MySQL編程的基礎知識，然后講解了自定義函數(shù)的實現(xiàn)方法，接著介紹了MySQL常用的系統(tǒng)函數(shù)，最后結(jié)合“選課系統(tǒng)”，編寫自定義函數(shù)模擬實現(xiàn)了中文全文檢索。4自定義函數(shù)MySQL編程基礎知識系統(tǒng)函數(shù)中文全文檢索的模擬實現(xiàn)內(nèi)容一覽123本章首先介紹了MySQL編程的基礎知識，然后講123MySQL程序設計結(jié)構(gòu)是在SQL標準的基礎上增加了一些程序設計語言的元素，其中包括常量、變量、運算符、表達式、流程控制以及函數(shù)等內(nèi)容。用戶自定義變量常量運算符與表達式45begin-end語句塊重置命令結(jié)束標記6.1MySQL編程基礎知識123MySQL程序設計結(jié)構(gòu)是在SQL標準的基礎上增按照MySQL的數(shù)據(jù)類型進行劃分，可以將常量劃分為字符串常量、數(shù)值常量、十六進制常量、日期時間常量、二進制常量以及NULL。6.1.1常量按照MySQL的數(shù)據(jù)類型進行劃分，可以將常量劃分為字1．字符串常量字符串常量是指用單引號或雙引號括起來的字符序列。select'I\'ma\teacher'ascol1,"you'reastude\nt"ascol2;由于大多編程語言（例如Java、C等）使用雙引號表示字符串，為了便于區(qū)分，在MySQL數(shù)據(jù)庫中推薦使用單引號表示字符串。6.1.1常量1．字符串常量6.1.1常量2．數(shù)值常量數(shù)值常量可以分為整數(shù)常量（例如2013）和小數(shù)常量（例如5.26、101.5E5），這里不再贅述。6.1.1常量2．數(shù)值常量6.1.1常量3．日期時間常量日期時間常量是一個符合特殊格式的字符串。例如'14:30:24'是一個時間常量，'2008-05-1214:28:24'是一個日期時間常量。日期時間常量的值必須符合日期、時間標準，例如'1996-02-31'是錯誤的日期常量。6.1.1常量3．日期時間常量6.1.1常量3．日期時間常量日期時間常量是一個符合特殊格式的字符串。例如'14:30:24'是一個時間常量，'2008-05-1214:28:24'是一個日期時間常量。日期時間常量的值必須符合日期、時間標準，例如'1996-02-31'是錯誤的日期常量。6.1.1常量3．日期時間常量6.1.1常量4.布爾值布爾值只包含兩個可能的值：true和false。說明：使用select語句顯示布爾值true或者false時，會將其轉(zhuǎn)換為字符串“0”或者字符串“1”。6.1.1常量4.布爾值6.1.1常量5．二進制常量二進制常量由數(shù)字“0”和“1”組成。二進制常量的表示方法：前綴為“b”，后面緊跟一個“二進制”字符串。例如下面的select語句輸出三個字符。其中b‘111101’表示“等號”，b‘1’表示“笑臉”，b‘11’表示“心”。selectb'111101',b'1',b'11';6.1.1常量5．二進制常量6.1.1常量6．十六進制常量十六進制常量由數(shù)字“0”到“9”及字母“a”到“f”或“A”到“F”組成（字母不區(qū)分大小寫）。十六進制常量有兩種表示方法。6.1.1常量6．十六進制常量6.1.1常量6．十六進制常量第一種表示方法：前綴為大寫字母“X”或小寫字母“x”，后面緊跟一個“十六進制”字符串。例如selectX'41',x'4D7953514C';其中X'41'表示大寫字母A。x'4D7953514C'表示字符串MySQL。6.1.1常量6．十六進制常量6.1.1常量6．十六進制常量第二種表示方法：前綴為“0x”，后面緊跟一個“十六進制數(shù)”（不用引號）。例如select0x41,0x4D7953514C;其中0x41表示大寫字母A。0x4D7953514C表示字符串MySQL。6.1.1常量6．十六進制常量6.1.1常量小結(jié)：可以看到，使用select語句顯示十六進制數(shù)時，會將十六進制數(shù)自動轉(zhuǎn)換為“字符串”再進行顯示。如果需要將一個字符串或數(shù)字轉(zhuǎn)換為十六進制格式的字符串，可以用hex()函數(shù)實現(xiàn)。例如selecthex('MySQL');hex()函數(shù)將“MySQL”字符串轉(zhuǎn)換為十六進制數(shù)4D7953514C。6.1.1常量小結(jié)：可以看到，使用select語句顯示十六進制數(shù)時，會將十小結(jié)：十六進制數(shù)與字符之間存在一一對應關(guān)系，利用這個特點，可以模擬實現(xiàn)中文全文檢索。7.NULL值NULL值可適用于各種字段類型，它通常用來表示“值不確定”、“沒有值”等意義，NULL值參與算術(shù)運算、比較運算以及邏輯運算時，結(jié)果依然為NULL。6.1.1常量小結(jié)：十六進制數(shù)與字符之間存在一一對應關(guān)系，利用這個特點，可變量分為系統(tǒng)變量（以@@開頭）以及用戶自定義變量。用戶自定義變量分為用戶會話變量（以@開頭）以及局部變量（不以@開頭）。6.1.2用戶自定義變量變量分為系統(tǒng)變量（以@@開頭）以及用戶自定義變量。6.1.21．用戶會話變量MySQL客戶機1定義了會話變量，會話期間，該會話變量一直有效；MySQL客戶機2不能訪問MySQL客戶機1定義的會話變量；MySQL客戶機1關(guān)閉或者MySQL客戶機1與服務器斷開連接后，MySQL客戶機1定義的所有會話變量將自動釋放，以便節(jié)省MySQL服務器的內(nèi)存空間。6.1.2用戶自定義變量1．用戶會話變量6.1.2用戶自定義變量用戶會話變量6.1.2用戶自定義變量用戶會話變量6.1.2用戶自定義變量1．用戶會話變量系統(tǒng)會話變量與用戶會話變量的共同之處在于：變量名大小寫不敏感。系統(tǒng)會話變量與用戶會話變量的區(qū)別在于：1．用戶會話變量一般以一個“@”開頭；系統(tǒng)會話變量以兩個“@”開頭。2．系統(tǒng)會話變量無需定義可以直接使用。6.1.2用戶自定義變量1．用戶會話變量6.1.2用戶自定義變量（1）用戶會話變量的定義與賦值一般情況下，用戶會話變量的定義與賦值會同時進行。用戶會話變量的定義與賦值有兩種方法：使用set命令或者使用select語句。方法一：使用set命令定義用戶會話變量，并為其賦值，語法格式如下：set@user_variable1＝expression1[,@user_variable2=expression2,…]6.1.2用戶自定義變量（1）用戶會話變量的定義與賦值6.1.2用戶自定義變量說明：用戶會話變量的數(shù)據(jù)類型是根據(jù)賦值運算符“=”右邊表達式的計算結(jié)果自動分配的。也就是說，等號右邊的值（包括字符集和字符序）決定了用戶會話變量的數(shù)據(jù)類型（包括字符集和字符序）。6.1.2用戶自定義變量說明：用戶會話變量的數(shù)據(jù)類型是根據(jù)賦值運算符“=”右邊表達式方法二：使用select語句定義用戶會話變量，并為其賦值，語法格式有兩種。第一種語法格式：select@user_variable1:＝expression1[,user_variable2:=expression2,…]第二種語法格式：selectexpression1into@user_variable1,expression2into@user_variable2,…6.1.2用戶自定義變量方法二：使用select語句定義用戶會話變量，并為其賦值，語方法二：使用select語句定義用戶會話變量，說明：第一種語法格式中需要使用“:＝”賦值語句，原因在于“=”是為“比較”保留的。第一種與第二種語法格式的區(qū)別在于：第一種語法格式中的select語句會產(chǎn)生結(jié)果集，第二種語法格式中的select語句，僅僅用于會話變量的定義及賦值（但不會產(chǎn)生結(jié)果集）。6.1.2用戶自定義變量方法二：使用select語句定義用戶會話變量，說明：6.1.2．用戶會話變量與SQL語句任務布置1：上機操作，完成本書場景描述1：用戶會話變量與SQL語句的任務要求。注意：哪些方法的select語句產(chǎn)生結(jié)果集？哪些方法的select語句不產(chǎn)生結(jié)果集？6.1.2用戶自定義變量2．用戶會話變量與SQL語句6.1.2用戶自定義變量3．局部變量declare命令專門用于定義局部變量及對應的數(shù)據(jù)類型。局部變量必須定義在存儲程序中（例如函數(shù)、觸發(fā)器、存儲過程以及事件中），并且局部變量的作用范圍僅僅局限于存儲程序中，脫離存儲程序，局部變量沒有絲毫意義。局部變量主要用于下面三種場合。6.1.2用戶自定義變量3．局部變量6.1.2用戶自定義變量3．局部變量場合一：局部變量定義在存儲程序的begin-end語句塊（稍后介紹）之間。此時局部變量首先必須使用declare命令定義，并且必須指定局部變量的數(shù)據(jù)類型。只有定義局部變量后，才可以使用set命令或者select語句為其賦值。6.1.2用戶自定義變量3．局部變量6.1.2用戶自定義變量3．局部變量場合二：局部變量作為存儲過程或者函數(shù)的參數(shù)使用，此時雖然不需要使用declare命令定義，但需要指定參數(shù)的數(shù)據(jù)類型。6.1.2用戶自定義變量3．局部變量6.1.2用戶自定義變量3．局部變量場合三：局部變量也可以用在SQL語句中。數(shù)據(jù)檢索時，如果select語句的結(jié)果集是單個值，可以將select語句的返回結(jié)果賦予局部變量，局部變量也可以直接嵌入到select、insert、update以及delete語句的條件表達式中。6.1.2用戶自定義變量3．局部變量6.1.2用戶自定義變量4．局部變量與用戶會話變量的區(qū)別（1）用戶會話變量名以“@”開頭，而局部變量名前面沒有“@”符號。6.1.2用戶自定義變量4．局部變量與用戶會話變量的區(qū)別6.1.2用戶自定義變量（2）局部變量使用declare命令定義（存儲過程參數(shù)、函數(shù)參數(shù)除外），定義時必須指定局部變量的數(shù)據(jù)類型；局部變量定義后，才可以使用set命令或者select語句為其賦值。用戶會話變量使用set命令或select語句定義并進行賦值，定義用戶會話變量時無需指定數(shù)據(jù)類型。諸如“declare@student_noint;”的語句是錯誤語句，用戶會話變量不能使用declare命令定義。6.1.2用戶自定義變量（2）局部變量使用declare命令定義（存儲過程參數(shù)、函數(shù)4．局部變量與用戶會話變量的區(qū)別（3）用戶會話變量的作用范圍與生存周期大于局部變量。局部變量如果作為存儲過程或者函數(shù)的參數(shù)，此時在整個存儲過程或函數(shù)內(nèi)中有效；如果定義在存儲程序的begin-end語句塊中，此時僅在當前的begin-end語句塊中有效。用戶會話變量在本次會話期間一直有效，直至關(guān)閉服務器連接。6.1.2用戶自定義變量4．局部變量與用戶會話變量的區(qū)別6.1.2用戶自定義變量4．局部變量與用戶會話變量的區(qū)別（4）如果局部變量嵌入到SQL語句中，由于局部變量名前沒有“@”符號，這就要求局部變量名不能與表字段名同名，否則將出現(xiàn)無法預期的結(jié)果。6.1.2用戶自定義變量4．局部變量與用戶會話變量的區(qū)別6.1.2用戶自定義變量關(guān)于局部變量的其他說明：在MySQL數(shù)據(jù)庫中，由于局部變量涉及begin-end語句塊、函數(shù)、存儲過程等知識，局部變量的具體使用方法將結(jié)合這些知識稍后一塊兒進行講解。declare命令盡量寫在begin-end語句塊的開頭，盡量寫在任何其他語句的前面。6.1.2用戶自定義變量關(guān)于局部變量的其他說明：6.1.2用戶自定義變量根據(jù)運算符功能的不同，可將MySQL的運算符分為算術(shù)運算符、比較運算符、邏輯運算符以及位操作運算符。1．算術(shù)運算符算術(shù)運算符用于兩個操作數(shù)之間執(zhí)行算術(shù)運算。常用的算術(shù)運算符有：+（加）、-（減）、*（乘）、/（除）、%（求余）以及div（求商）等6種運算符。6.1.3運算符與表達式根據(jù)運算符功能的不同，可將MySQL的運算符分為算術(shù)運算符、2．比較運算符比較運算符（又稱關(guān)系運算符）用于比較操作數(shù)之間的大小關(guān)系，其運算結(jié)果要么為true、要么為false、要么為NULL（不確定）。6.1.3運算符與表達式2．比較運算符6.1.3運算符與表達式select'ab'='ab','ab'='ab','b'>'a',NULL=NULL,NULL<=>NULL,NULLisNULL;結(jié)論：字符串進行比較時，會截掉字符串尾部的空格字符，然后進行比較。6.1.3運算符與表達式select'ab'='ab','ab'='ab',3．邏輯運算符邏輯運算符（又稱布爾運算符）對布爾值進行操作，其運算結(jié)果要么為true、要么為false、要么為NULL（不確定）。

6.1.3運算符與表達式3．邏輯運算符6.1.3運算符與表達式4．位運算符位運算符對二進制數(shù)據(jù)進行操作（如果不是二進制類型的數(shù)，將進行類型自動轉(zhuǎn)換），其運算結(jié)果為二進制數(shù)。使用select語句顯示二進制數(shù)時，會將其自動轉(zhuǎn)換為十進制數(shù)顯示。

6.1.3運算符與表達式4．位運算符6.1.3運算符與表達式4．位運算符6.1.3運算符與表達式4．位運算符6.1.3運算符與表達式[開始標簽:]begin

[局部]變量的聲明; 錯誤觸發(fā)條件的聲明; 游標的聲明; 錯誤處理程序的聲明; 業(yè)務邏輯代碼;end[結(jié)束標簽];6.1.5重置命令結(jié)束標記[開始標簽:]begin6.1.5重置命令結(jié)束標記delimiter$$select*fromstudentwherestudent_namelike'張_'$$delimiter;select*fromstudentwherestudent_namelike'張_';6.1.4begin-end語句塊delimiter$$6.1.4begin-end語句塊123函數(shù)可以看作是一個“加工作坊”，這個“加工作坊”接收“調(diào)用者”傳遞過來的“原料”（實際上是函數(shù)的參數(shù)），然后將這些“原料”“加工處理”成“產(chǎn)品”（實際上是函數(shù)的返回值），再把“產(chǎn)品”返回給“調(diào)用者”。函數(shù)的創(chuàng)建與調(diào)用創(chuàng)建自定義函數(shù)的語法格式函數(shù)的維護45條件控制語句循環(huán)語句6.2定義函數(shù)123函數(shù)可以看作是一個“加工作坊”，這個“加工作坊”接收“createfunction函數(shù)名（參數(shù)1，參數(shù)2，…）returns返回值的數(shù)據(jù)類型[函數(shù)選項]begin 函數(shù)體;

return語句;end;6.2.1創(chuàng)建自定義函數(shù)的語法格式createfunction函數(shù)名（參數(shù)1，參數(shù)2，…）函數(shù)選項由以下一種或幾種選項組合而成。languagesql|[not]deterministic|{containssql|nosql|readssqldata|modifiessqldata}|sqlsecurity{definer|invoker}|comment'注釋'6.2.1創(chuàng)建自定義函數(shù)的語法格式函數(shù)選項由以下一種或幾種選項組合而成。6.2.1創(chuàng)建自定義函數(shù)選項說明：languagesql：默認選項，用于說明函數(shù)體使用SQL語言編寫。6.2.1創(chuàng)建自定義函數(shù)的語法格式函數(shù)選項說明：6.2.1創(chuàng)建自定義函數(shù)的語法格式deterministic（確定性）：當函數(shù)返回不確定值時，該選項是為了防止“復制”時的不一致性。如果函數(shù)總是對同樣的輸入?yún)?shù)產(chǎn)生同樣的結(jié)果，則被認為它是“確定的”，否則就是“不確定”的。例如函數(shù)返回系統(tǒng)當前的時間，返回值是不確定的。如果既沒有給定deterministic也沒有給定notdeterministic，默認的就是notdeterministic。6.2.1創(chuàng)建自定義函數(shù)的語法格式deterministic（確定性）：當函數(shù)返回不確定值時，containssql：表示函數(shù)體中不包含讀或?qū)憯?shù)據(jù)的語句（例如set命令等）。nosql：表示函數(shù)體中不包含SQL語句。readssqldata：表示函數(shù)體中包含select查詢語句，但不包含更新語句。6.2.1創(chuàng)建自定義函數(shù)的語法格式containssql：表示函數(shù)體中不包含讀或?qū)憯?shù)據(jù)的語句modifiessqldata：表示函數(shù)體包含更新語句。如果上述選項沒有明確指定，默認是containssql。sqlsecurity：用于指定函數(shù)的執(zhí)行許可。definer：表示該函數(shù)只能由創(chuàng)建者調(diào)用。invoker：表示該函數(shù)可以被其他數(shù)據(jù)庫用戶調(diào)用。默認值是definer。comment：為函數(shù)添加功能說明等注釋信息。6.2.1創(chuàng)建自定義函數(shù)的語法格式modifiessqldata：表示函數(shù)體包含更新語句。任務布置2：上機操作，完成本書場景描述2：簡單的自定義函數(shù)（空參數(shù)）的任務要求。任務布置3：上機操作，完成場景描述3：自定義函數(shù)可以操作數(shù)據(jù)庫中的數(shù)據(jù)的任務要求。任務布置4：上機操作，完成場景描述4：將查詢結(jié)果賦予變量的任務要求。6.2.2函數(shù)的創(chuàng)建與調(diào)用任務布置2：上機操作，完成本書場景描述2：簡單的自定義函數(shù)（函數(shù)的維護包括查看函數(shù)的定義、修改函數(shù)的定義以及刪除函數(shù)的定義等內(nèi)容。6.2.3函數(shù)的維護函數(shù)的維護包括查看函數(shù)的定義、修改函數(shù)的定義以及刪除函數(shù)的定1．查看函數(shù)的定義（1）查看當前數(shù)據(jù)庫中所有的自定義函數(shù)信息，可以使用MySQL命令“showfunctionstatus;”。如果自定義函數(shù)較多，使用MySQL命令“showfunctionstatuslike模式;”可以進行模糊查詢。

6.2.3函數(shù)的維護1．查看函數(shù)的定義6.2.3函數(shù)的維護1．查看函數(shù)的定義（2）查看指定數(shù)據(jù)庫（例如choose數(shù)據(jù)庫）中的所有自定義函數(shù)名，可以使用下面的SQL語句，如圖6-28所示。selectnamefromcwheredb='choose'andtype='function';6.2.3函數(shù)的維護1．查看函數(shù)的定義6.2.3函數(shù)的維護1．查看函數(shù)的定義（3）使用MySQL命令“showcreatefunction函數(shù)名;”可以查看指定函數(shù)名的詳細信息。例如查看get_name_fn()函數(shù)的詳細信息，可以使用“showcreatefunctionget_name_fn\G”

6.2.3函數(shù)的維護1．查看函數(shù)的定義6.2.3函數(shù)的維護1．查看函數(shù)的定義（4）函數(shù)的信息都保存在information_schema數(shù)據(jù)庫中的routines表中，可以使用select語句檢索routines表，查詢函數(shù)的相關(guān)信息。select*frominformation_schema.routineswhereroutine_name='get_name_fn'\G6.2.3函數(shù)的維護1．查看函數(shù)的定義6.2.3函數(shù)的維護2．函數(shù)定義的修改由于函數(shù)保存的僅僅是函數(shù)體，而函數(shù)體實際上是一些MySQL表達式，因此函數(shù)自身不保存任何用戶數(shù)據(jù)。當函數(shù)的函數(shù)體需要更改時，可以使用dropfunction語句暫時將函數(shù)的定義刪除，然后使用createfunction語句重新創(chuàng)建相同名字的函數(shù)即可。這種方法對于存儲過程、視圖、觸發(fā)器的修改同樣適用。6.2.3函數(shù)的維護2．函數(shù)定義的修改6.2.3函數(shù)的維護3．函數(shù)定義的刪除使用MySQL命令“dropfunction函數(shù)名”刪除自定義函數(shù)。例如刪除get_name_fn()函數(shù)可以使用“dropfunctionget_name_fn;”。6.2.3函數(shù)的維護3．函數(shù)定義的刪除6.2.3函數(shù)的維護條件控制語句分為兩種，一種是if語句，另一種是case語句。6.2.4條件控制語句條件控制語句分為兩種，一種是if語句，另一種是case語句。1．if語句if語句根據(jù)條件表達式的值確定執(zhí)行不同的語句塊，if語句的用法格式如下。if條件表達式1then語句塊1;[elseif條件表達式2then語句塊2]...[else語句塊n]endif;說明：endif后必須以“;”結(jié)束。6.2.4條件控制語句1．if語句6.2.4條件控制語句6.2.4條件控制語句6.2.4條件控制語句2．case語句case語句用于實現(xiàn)比if語句分支更為復雜的條件判斷，case語句的語法格式如下。說明：MySQL中的case語句與C語言、Java語言等高級程序設計語言不同，在高級程序設計語言中，每個case的分支需使用“break”跳出，而MySQL無需使用“break”語句。6.2.4條件控制語句2．case語句6.2.4條件控制語句2．case語句case表達式whenvalue1then語句塊1;whenvalue2then語句塊2;…else語句塊n;endcase;6.2.4條件控制語句2．case語句6.2.4條件控制語句6.2.4條件控制語句6.2.4條件控制語句MySQL提供了三種循環(huán)語句，分別是while、repeat以及l(fā)oop。除此以外，MySQL還提供了iterate語句以及l(fā)eave語句用于循環(huán)的內(nèi)部控制。6.2.5循環(huán)語句MySQL提供了三種循環(huán)語句，分別是while、repeat1．while語句當條件表達式的值為true時，反復執(zhí)行循環(huán)體，直到條件表達式的值為false，while語句的語法格式如下。[循環(huán)標簽:]while條件表達式do循環(huán)體;endwhile[循環(huán)標簽];說明：endwhile后必須以“;”結(jié)束。6.2.5循環(huán)語句1．while語句6.2.5循環(huán)語句6.2.5循環(huán)語句6.2.5循環(huán)語句2．leave語句leave語句用于跳出當前的循環(huán)語句（例如while語句），語法格式如下。leave循環(huán)標簽;說明：leave循環(huán)標簽后必須以“;”結(jié)束。6.2.5循環(huán)語句2．leave語句6.2.5循環(huán)語句3．iterate語句iterate語句用于跳出本次循環(huán)，繼而進行下次循環(huán)。iterate語句的語法格式如下。iterate循環(huán)標簽;說明：iterate循環(huán)標簽后必須以“;”結(jié)束。6.2.5循環(huán)語句3．iterate語句6.2.5循環(huán)語句4．repeat語句當條件表達式的值為false時，反復執(zhí)行循環(huán)，直到條件表達式的值為true，repeat語句的語法格式如下。6.2.5循環(huán)語句4．repeat語句6.2.5循環(huán)語句4．repeat語句[循環(huán)標簽:]repeat循環(huán)體;until條件表達式endrepeat[循環(huán)標簽];說明：endrepeat后必須以“;”結(jié)束。6.2.5循環(huán)語句4．repeat語句6.2.5循環(huán)語句5．loop語句的語法格式由于loop循環(huán)語句本身沒有停止循環(huán)的語句，因此loop通常使用leave語句跳出loop循環(huán)，loop的語法格式如下。6.2.5循環(huán)語句5．loop語句的語法格式6.2.5循環(huán)語句5．loop語句的語法格式[循環(huán)標簽:]loop循環(huán)體;if條件表達式thenleave[循環(huán)標簽];endif;endloop;說明：endloop后必須以“;”結(jié)束。6.2.5循環(huán)語句5．loop語句的語法格式6.2.5循環(huán)語句123MySQL功能強大的一個重要原因是MySQL內(nèi)置了許多功能豐富的函數(shù)。字符串函數(shù)數(shù)學函數(shù)數(shù)據(jù)類型轉(zhuǎn)換函數(shù)45條件控制函數(shù)系統(tǒng)信息函數(shù)6.3系統(tǒng)函數(shù)123MySQL功能強大的一個重要原因是MySQL內(nèi)置了許67本章講解的所有函數(shù)f(x)對數(shù)據(jù)x進行操作時，都會產(chǎn)生返回結(jié)果，并且數(shù)據(jù)x的值以及x的數(shù)據(jù)類型都不會發(fā)生絲毫變化。日期和時間函數(shù)其他常用的MySQL函數(shù)6.3系統(tǒng)函數(shù)67本章講解的所有函數(shù)f(x)對數(shù)據(jù)x進行操作時，都為了便于讀者學習，本書將數(shù)學函數(shù)歸納為三角函數(shù)，指數(shù)函數(shù)及對數(shù)函數(shù)，求近似值函數(shù)，隨機函數(shù)，二進制、十六進制函數(shù)等。6.3.1數(shù)學函數(shù)為了便于讀者學習，本書將數(shù)學函數(shù)歸納為三角函數(shù)，指數(shù)函數(shù)及對1．三角函數(shù)MySQL提供了pi()函數(shù)計算圓周率；radians(x)函數(shù)負責將角度x轉(zhuǎn)換為弧度；degrees(x)函數(shù)負責將弧度x轉(zhuǎn)換為角度。MySQL還提供了三角函數(shù)，正弦函數(shù)sin(x)、余弦函數(shù)cos(x)、tan(x)正切函數(shù)、余切函數(shù)cot(x)、反正弦函數(shù)asin(x)、反余弦函數(shù)acos(x)以及反正切函數(shù)atan(x)。6.3.1數(shù)學函數(shù)1．三角函數(shù)6.3.1數(shù)學函數(shù)2．指數(shù)函數(shù)及對數(shù)函數(shù)MySQL中常用的指數(shù)函數(shù)有sqrt()平方根函數(shù)、pow(x,y)冪運算函數(shù)（計算x的y次方）以及exp(x)函數(shù)（計算e的x次方）。說明：pow(x,y)冪運算函數(shù)還有一個別名函數(shù)：power(x,y)，實現(xiàn)相同的功能。6.3.1數(shù)學函數(shù)2．指數(shù)函數(shù)及對數(shù)函數(shù)6.3.1數(shù)學函數(shù)2．指數(shù)函數(shù)及對數(shù)函數(shù)MySQL中常用的對數(shù)函數(shù)有l(wèi)og(x)函數(shù)（計算x的自然對數(shù)）以及l(fā)og10(x)函數(shù)（計算以10為底的對數(shù)）。

6.3.1數(shù)學函數(shù)2．指數(shù)函數(shù)及對數(shù)函數(shù)6.3.1數(shù)學函數(shù)3．求近似值函數(shù)MySQL提供的round(x)函數(shù)負責計算離x最近的整數(shù)，round(x,y)函數(shù)負責計算離x最近的小數(shù)（小數(shù)點后保留y位）；truncate(x,y)函數(shù)負責返回小數(shù)點后保留y位的x（舍棄多余小數(shù)位，不進行四舍五入）；6.3.1數(shù)學函數(shù)3．求近似值函數(shù)6.3.1數(shù)學函數(shù)3．求近似值函數(shù)format(x,y)函數(shù)負責返回小數(shù)點后保留y位的x（進行四舍五入）；ceil(x)函數(shù)負責返回大于等于x的最小整數(shù)；floor(x)函數(shù)負責返回小于等于x的最大整數(shù)。

6.3.1數(shù)學函數(shù)3．求近似值函數(shù)6.3.1數(shù)學函數(shù)4．隨機函數(shù)MySQL提供了rand()函數(shù)負責返回隨機數(shù)。

6.3.1數(shù)學函數(shù)4．隨機函數(shù)6.3.1數(shù)學函數(shù)5．二進制、十六進制函數(shù)bin(x)函數(shù)、oct(x)函數(shù)和hex(x)函數(shù)分別返回x的二進制、八進制和十六進制數(shù)；ascii(c)函數(shù)返回字符c的ASCII碼（ASCII碼介于0～255）；char(c1,c2,c3,…)函數(shù)將c1、c2……的ASCII碼轉(zhuǎn)換為字符，然后返回這些字符組成的字符串；conv(x,code1,code2)函數(shù)將code1進制的x變?yōu)閏ode2進制數(shù)。6.3.1數(shù)學函數(shù)5．二進制、十六進制函數(shù)6.3.1數(shù)學函數(shù)為便于學習，可以將字符串函數(shù)歸納為字符串基本信息函數(shù)、加密函數(shù)、字符串連接函數(shù)、修剪函數(shù)、子字符串操作函數(shù)、字符串復制函數(shù)、字符串比較函數(shù)以及字符串逆序函數(shù)等。6.3.2字符串函數(shù)為便于學習，可以將字符串函數(shù)歸納為字符串基本信息函數(shù)、加密函注意：字符串函數(shù)在對字符串操作時，字符集、字符序的設置至關(guān)重要。同一個字符串函數(shù)，對同一個字符串進行操作，如果字符集或者字符序設置不同，操作結(jié)果可能不同。6.3.2字符串函數(shù)注意：字符串函數(shù)在對字符串操作時，字符集、字符序的設置至關(guān)重1．字符串基本信息函數(shù)字符串基本信息函數(shù)包括獲取字符串字符集的函數(shù)、獲取字符串長度以及獲取字符串占用字節(jié)數(shù)的函數(shù)等。（1）關(guān)于字符串字符集的函數(shù)charset(x)函數(shù)返回x的字符集；collation(x)函數(shù)返回x的字符序。6.3.2字符串函數(shù)1．字符串基本信息函數(shù)6.3.2字符串函數(shù)1．字符串基本信息函數(shù)（1）關(guān)于字符串字符集的函數(shù)convert(xusingcharset)函數(shù)返回x的charset字符集數(shù)據(jù)（注意x的字符集沒有變化）。6.3.2字符串函數(shù)1．字符串基本信息函數(shù)6.3.2字符串函數(shù)1．字符串基本信息函數(shù)（2）獲取字符串長度以及獲取字符串占用字節(jié)數(shù)函數(shù)char_length(x)函數(shù)用于獲取字符串x的長度；length(x)函數(shù)用于獲取字符串x的占用的字節(jié)數(shù)。6.3.2字符串函數(shù)1．字符串基本信息函數(shù)6.3.2字符串函數(shù)2．加密函數(shù)加密函數(shù)包括不可逆加密函數(shù)以及加密-解密函數(shù)。（1）不可逆加密函數(shù)password(x)函數(shù)用于對x進行加密，默認返回41位的加密字符串；md5(x)函數(shù)用于對x進行加密，默認返回32位的加密字符串。6.3.2字符串函數(shù)2．加密函數(shù)6.3.2字符串函數(shù)2．加密函數(shù)（2）加密-解密函數(shù)MySQL提供了兩對加密-解密函數(shù)分別是：encode(x,key)函數(shù)與decode(password,key)函數(shù)以及aes_encrypt(x,key)函數(shù)與aes_decrypt(password,key)函數(shù)。其中key為加密密鑰（注意讀作mìyuè），需要牢記加密時的密鑰才能實現(xiàn)密碼的解密。6.3.2字符串函數(shù)2．加密函數(shù)6.3.2字符串函數(shù)2．加密函數(shù)（2）加密-解密函數(shù)encode(x,key)函數(shù)使用密鑰key對x進行加密，默認返回值是一個二進制數(shù)（二進制的位數(shù)由x的字節(jié)長度決定）；decode(password,key)函數(shù)使用密鑰key對密碼password進行解密。6.3.2字符串函數(shù)2．加密函數(shù)6.3.2字符串函數(shù)2．加密函數(shù)（2）加密-解密函數(shù)aes_encrypt(x,key)函數(shù)使用密鑰key對x進行加密，默認返回值是一個128位的二進制數(shù)；aes_decrypt(password,key)函數(shù)使用密鑰key對密碼password進行解密。6.3.2字符串函數(shù)2．加密函數(shù)6.3.2字符串函數(shù)3．字符串連接函數(shù)concat(x1,x2,….)函數(shù)用于將x1、x2等若干個字符串連接成一個新字符串；concat_ws(x,x1,x2,….)函數(shù)使用x將x1、x2等若干個字符串連接成一個新字符串。6.3.2字符串函數(shù)3．字符串連接函數(shù)6.3.2字符串函數(shù)4．修剪函數(shù)修剪函數(shù)包括字符串裁剪函數(shù)、字符串大小寫轉(zhuǎn)換函數(shù)、填充字符串函數(shù)等函數(shù)。（1）字符串裁剪函數(shù)ltrim(x)函數(shù)用于去掉字符串x開頭的所有空格字符；rtrim(x)函數(shù)用于去掉字符串x結(jié)尾的所有空格字符；trim(x)函數(shù)用于去掉字符串x開頭以及結(jié)尾的所有空格字符。6.3.2字符串函數(shù)4．修剪函數(shù)6.3.2字符串函數(shù)4．修剪函數(shù)（1）字符串裁剪函數(shù)trim([leading|both|trailing]x1fromx2)函數(shù)用于從x2字符串的前綴或者（以及）后綴中去掉字符串x1。6.3.2字符串函數(shù)4．修剪函數(shù)6.3.2字符串函數(shù)4．修剪函數(shù)（1）字符串裁剪函數(shù)left(x,n)函數(shù)以及righ(x,n)函數(shù)也用于截取字符串。其中l(wèi)eft(x,n)函數(shù)返回字符串x的前n個字符；right(x,n)函數(shù)返回字符串x的后n個字符。6.3.2字符串函數(shù)4．修剪函數(shù)6.3.2字符串函數(shù)4．修剪函數(shù)（2）字符串大小寫轉(zhuǎn)換函數(shù)upper(x)函數(shù)以及ucase(x)函數(shù)將字符串x中的所有字母變成大寫字母，字符串x并沒有發(fā)生變化；lower(x)函數(shù)以及l(fā)case(x)函數(shù)將字符串x中的所有字母變成小寫字母，字符串x并沒有發(fā)生變化。6.3.2字符串函數(shù)4．修剪函數(shù)6.3.2字符串函數(shù)4．修剪函數(shù)（3）填充字符串函數(shù)lpad(x1,len,x2)函數(shù)將字符串x2填充到x1的開始處，使字符串x1的長度達到len；rpad(x1,len,x2)函數(shù)將字符串x2填充到x1的結(jié)尾處，使字符串x1的長度達到len。6.3.2字符串函數(shù)4．修剪函數(shù)6.3.2字符串函數(shù)5．子字符串操作函數(shù)子字符串操作函數(shù)包括取出指定位置的子字符串函數(shù)、在字符串中查找指定子字符串的位置函數(shù)、子字符串替換函數(shù)等。

(1)取出指定位置的子字符串函數(shù)substring(x,start,length)函數(shù)與mid(x,start,length)函數(shù)都是從字符串x的第n個位置開始獲取length長度的字符串。6.3.2字符串函數(shù)5．子字符串操作函數(shù)6.3.2字符串函數(shù)5．子字符串操作函數(shù)（2）在字符串中查找指定子字符串的位置函數(shù)locate(x1,x2)函數(shù)、position(x1inx2)函數(shù)以及instr(x2,x1)函數(shù)都是用于從字符串x2中獲取x1的開始位置。find_in_set(x1,x2)函數(shù)也可以獲取字符串x2中x1的開始位置（第幾個逗號處的位置），不過該函數(shù)要求s2是一個用英文的逗號分隔的字符串。6.3.2字符串函數(shù)5．子字符串操作函數(shù)6.3.2字符串函數(shù)5．子字符串操作函數(shù)（3）子字符串替換函數(shù)MySQL提供了兩個子字符串替換函數(shù)insert(x1,start,length,x2)和replace(x1,x2,x3)。insert(x1,start,length,x2)函數(shù)將字符串x1中從start位置開始、長度為length的子字符串替換為x2。replace(x1,x2,x3)函數(shù)用字符串x3替換x1中所有出現(xiàn)的字符串x2，最后返回替換后的字符串。6.3.2字符串函數(shù)5．子字符串操作函數(shù)6.3.2字符串函數(shù)6．字符串復制函數(shù)字符串復制函數(shù)包括repeat(x,n)函數(shù)以及space(n)函數(shù)。其中repeat(x,n)函數(shù)產(chǎn)生一個新字符串，該字符串的內(nèi)容是字符串x的n次復制；space(n)函數(shù)產(chǎn)生一個新字符串，該字符串的內(nèi)容是空格字符的n次復制。6.3.2字符串函數(shù)6．字符串復制函數(shù)6.3.2字符串函數(shù)7．字符串比較函數(shù)strcmp(x1,x2)函數(shù)用于比較兩個字符串x1和x2，如果x1>x2函數(shù)返回值為1；如果x1=x2函數(shù)返回值為0；如果x1<x2函數(shù)返回值為-1。6.3.2字符串函數(shù)7．字符串比較函數(shù)6.3.2字符串函數(shù)8．字符串逆序函數(shù)reverse(x)函數(shù)返回一個新字符串，該字符串為字符串x的逆序。6.3.2字符串函數(shù)8．字符串逆序函數(shù)6.3.2字符串函數(shù)最為常用的數(shù)據(jù)類型轉(zhuǎn)換函數(shù)是convert(x,type)與cast(xastype)函數(shù)，另外MySQL還提供了“十六進制字符串”轉(zhuǎn)換為“十六進制數(shù)”的函數(shù)unhex(x)。（1）convert()函數(shù)convert()函數(shù)有兩種用法格式：convert(xusingcharset)函數(shù)返回x的charset字符集數(shù)據(jù)（剛剛講過，這里不再贅述）。6.3.3數(shù)據(jù)類型轉(zhuǎn)換函數(shù)最為常用的數(shù)據(jù)類型轉(zhuǎn)換函數(shù)是convert(x,type)與convert()函數(shù)還有另外一種語法格式：convert(x,type)，可以實現(xiàn)數(shù)據(jù)類型的轉(zhuǎn)換。convert(x,type)函數(shù)以type數(shù)據(jù)類型返回x數(shù)據(jù)（注意x的數(shù)據(jù)類型沒有變化）。除此以外cast(xastype)函數(shù)也實現(xiàn)了convert(x,type)函數(shù)相同的功能。6.3.3數(shù)據(jù)類型轉(zhuǎn)換函數(shù)convert()函數(shù)還有另外一種語法格式：convert(（2）unhex(x)函數(shù)負責將十六進制字符串x轉(zhuǎn)換為十六進制的數(shù)值。6.3.3數(shù)據(jù)類型轉(zhuǎn)換函數(shù)（2）unhex(x)函數(shù)負責將十六進制字符串x轉(zhuǎn)換為十六進條件控制函數(shù)的功能是根據(jù)條件表達式的值返回不同的值，MySQL中常用的條件控制函數(shù)有if()、ifnull()以及case函數(shù)。與先前講解的if語句以及case語句不同，這些函數(shù)可以在MySQL客戶機中直接調(diào)用，可以像max()統(tǒng)計函數(shù)一樣直接融入到SQL語句中。6.3.4條件控制函數(shù)條件控制函數(shù)的功能是根據(jù)條件表達式的值返回不同的值，MySQ（1）if()函數(shù)if(condition,v1,v2)函數(shù)中condition為條件表達式，當condition的值為true時，函數(shù)返回v1的值，否則返回v2的值。6.3.4條件控制函數(shù)（1）if()函數(shù)6.3.4條件控制函數(shù)（2）ifnull()函數(shù)ifnull(v1,v2)函數(shù)中，如果v1的值為NULL，則該函數(shù)返回v2的值；如果v1的值不為NULL，則該函數(shù)返回v1的值。6.3.4條件控制函數(shù)（2）ifnull()函數(shù)6.3.4條件控制函數(shù)（3）case函數(shù)case函數(shù)的語法格式如下。如果表達式的值等于when語句中某個“值n”，則case函數(shù)返回值為“結(jié)果n”；如果與所有的“值n”都不相等，case函數(shù)返回值為“其他值”。case表達式when值1then結(jié)果1[when值2then結(jié)果2]…[else其他值]end6.3.4條件控制函數(shù)（3）case函數(shù)6.3.4條件控制函數(shù)1．關(guān)于MySQL服務實例的函數(shù)version()函數(shù)用于獲取當前MySQL服務實例使用的MySQL版本號，該函數(shù)的返回值與@@version靜態(tài)變量的值相同。6.3.5系統(tǒng)信息函數(shù)1．關(guān)于MySQL服務實例的函數(shù)6.3.5系統(tǒng)信息函數(shù)2．關(guān)于MySQL服務器連接的函數(shù)（1）有關(guān)MySQL服務器連接的函數(shù)connection_id()函數(shù)用于獲取當前MySQL服務器的連接ID，該函數(shù)的返回值與@@pseudo_thread_id系統(tǒng)變量的值相同；database()函數(shù)與schema()函數(shù)用于獲取當前操作的數(shù)據(jù)庫。6.3.5系統(tǒng)信息函數(shù)2．關(guān)于MySQL服務器連接的函數(shù)6.3.5系統(tǒng)信息函數(shù)2．關(guān)于MySQL服務器連接的函數(shù)（2）獲取數(shù)據(jù)庫用戶信息的函數(shù)user()函數(shù)用于獲取通過哪一臺登錄主機、使用什么賬戶名成功連接MySQL服務器，system_user()函數(shù)與session_user()函數(shù)是user()函數(shù)的別名。current_user()函數(shù)用于獲取該賬戶名允許通過哪些登錄主機連接MySQL服務器。6.3.5系統(tǒng)信息函數(shù)2．關(guān)于MySQL服務器連接的函數(shù)6.3.5系統(tǒng)信息函數(shù)1．獲取MySQL服務器當前日期或時間函數(shù)（1）curdate()函數(shù)、current_date()函數(shù)用于獲取MySQL服務器當前日期；curtime()函數(shù)、current_time()函數(shù)用于用于獲取MySQL服務器當前時間；6.3.6日期和時間函數(shù)1．獲取MySQL服務器當前日期或時間函數(shù)6.3.6日期和now()函數(shù)、current_timestamp()函數(shù)、localtime()函數(shù)以及sysdate()函數(shù)用于獲取MySQL服務器當前日期和時間，這四個函數(shù)允許傳遞一個整數(shù)值（小于等于6）作為函數(shù)參數(shù)，從而獲取更為精確的時間信息。6.3.6日期和時間函數(shù)now()函數(shù)、current_timestamp()函數(shù)、curdate()函數(shù)、current_date()函數(shù)、curtime()函數(shù)、current_time()函數(shù)、now()函數(shù)、current_timestamp()函數(shù)、localtime()函數(shù)以及sysdate()函數(shù)的返回值與時區(qū)的設置有關(guān)。6.3.6日期和時間函數(shù)curdate()函數(shù)、current_date()函數(shù)、c（2）獲取MySQL服務器當前UNIX時間戳函數(shù)unix_timestamp()函數(shù)用于獲取MySQL服務器當前UNIX時間戳。unix_timestamp(datetime)函數(shù)將日期時間datetime以UNIX時間戳返回，而from_unixtime(timestamp)函數(shù)可以將UNIX時間戳以日期時間格式返回。需要注意的是，這些函數(shù)的返回值與時區(qū)的設置有關(guān)。6.3.6日期和時間函數(shù)（2）獲取MySQL服務器當前UNIX時間戳函數(shù)6.3.6（3）獲取MySQL服務器當前UTC日期和時間函數(shù)utc_date()函數(shù)用于獲取UTC日期；utc_time()函數(shù)用于獲取UTC時間。UTC即世界標準時間，中國大陸、中國香港、中國澳門、中國臺灣、蒙古國、新加坡、馬來西亞、菲律賓、西澳大利亞州的時間與UTC的時差均為+8，也就是UTC+8。這些函數(shù)的返回值與時區(qū)的設置無關(guān)。6.3.6日期和時間函數(shù)（3）獲取MySQL服務器當前UTC日期和時間函數(shù)6.3.62．獲取日期或時間的某一具體信息的函數(shù)（1）獲取年、月、日、時、分、秒、微秒等信息的函數(shù)year(x)函數(shù)、month(x)函數(shù)、dayofmonth(x)函數(shù)、hour(x)函數(shù)、minute(x)函數(shù)、second(x)函數(shù)以及microsecond(x)函數(shù)分別用于獲取日期時間x的年、月、日、時、分、秒、微秒等信息。6.3.6日期和時間函數(shù)2．獲取日期或時間的某一具體信息的函數(shù)6.3.6日期和時間另外MySQL還提供了extract(typefromx)函數(shù)用于獲取日期時間x的年、月、日、時、分、秒、微秒等信息，其中type可以分別指定為year、month、day、hour、minute、second、microsecond。6.3.6日期和時間函數(shù)另外MySQL還提供了extract(typefromx（2）獲取月份、星期等信息的函數(shù)monthname(x)函數(shù)用于獲取日期時間x的月份信息。dayname(x)函數(shù)與weekday(x)函數(shù)用于獲取日期時間x的星期信息；dayofweek(x)函數(shù)用于獲取日期時間x是本星期的第幾天（星期日為第一天，以此類推）。6.3.6日期和時間函數(shù)（2）獲取月份、星期等信息的函數(shù)6.3.6日期和時間函數(shù)（3）獲取年度信息的函數(shù)quarter(x)函數(shù)用于獲取日期時間x在本年是第幾季度；week(x)函數(shù)與weekofyear(x)函數(shù)用于獲取日期時間x在本年是第幾個星期；dayofyear(x)函數(shù)用于獲取日期時間x在本年是第幾天。6.3.6日期和時間函數(shù)（3）獲取年度信息的函數(shù)6.3.6日期和時間函數(shù)3．時間和秒數(shù)之間的轉(zhuǎn)換函數(shù)time_to_sec(x)函數(shù)用于獲取時間x在當天的秒數(shù)；sec_to_time(x)函數(shù)用于獲取當天的秒數(shù)x對應的時間。6.3.6日期和時間函數(shù)3．時間和秒數(shù)之間的轉(zhuǎn)換函數(shù)6.3.6日期和時間函數(shù)4．日期間隔、時間間隔函數(shù)（1）日期間隔函數(shù)to_days(x)函數(shù)用于計算日期x距離0000年1月1日的天數(shù)；from_days(x)函數(shù)用于計算從0000年1月1日開始n天后的日期；6.3.6日期和時間函數(shù)4．日期間隔、時間間隔函數(shù)