MySQL教程（第4版） 課件 第6章 SQL擴(kuò)展語言和系統(tǒng)函數(shù)

第6章SQL擴(kuò)展語言和系統(tǒng)函數(shù)——SQL語言簡介SQL語言簡介在MySQL數(shù)據(jù)庫中，SQL語言由以下幾部分組成。（1）數(shù)據(jù)定義語言（DDL）。它用于定義數(shù)據(jù)庫及其對象，包括表、約束、規(guī)則、視圖、觸發(fā)器和存儲過程等。主要語句包括創(chuàng)建（CREATE）、修改（ALTER）和刪除（DROP）。（2）數(shù)據(jù)操縱語言（DML）。它用于操作數(shù)據(jù)庫數(shù)據(jù)記錄。主要語句包括插入（INSERT）、修改（UPDATE）、刪除（DELETE）和查詢（SELECT）等。（3）數(shù)據(jù)控制語言（DCL）。它用于安全管理，對用戶分配定義和操作數(shù)據(jù)庫及其對象的權(quán)限。主要語句包括授予權(quán)限（GRANT）和收回權(quán)限（REVOKE）等。（4）MySQL語言元素。它是MySQL對SQL的擴(kuò)展，包括常量、變量、運算符、函數(shù)、流程控制語句和注解等，用于SQL語言編程，每個SQL語句都以分號結(jié)束，并且忽略空格、制表符和回車符。第6章SQL擴(kuò)展語言和系統(tǒng)函數(shù)——常量和變量01常

量數(shù)值常量字符串常量布爾值NULL值常

量1．?dāng)?shù)值常量數(shù)值常量可分為整數(shù)常量和實數(shù)常量。整數(shù)常量是不帶小數(shù)點的十進(jìn)制數(shù)，如1894、2、+145345234、–2147483648。實數(shù)常量是使用小數(shù)點的數(shù)值常量，可以是普通小數(shù)常量和浮點數(shù)常量。如5.26、

1.39、101.5E5、0.5E

2。2．字符串常量字符串是指用單引號或雙引號括起來的字符序列，分為普通編碼字符串常量、Unicode編碼字符串常量、十六進(jìn)制字符串常量、二進(jìn)制字符串常量和日期時間常量。（1）普通編碼字符串常量例如："hello,大家好！"。ASCII字符按照ASCII編碼，中文字符按照漢字編碼，常用漢字編碼為GB2312-80和GBK。在計算機(jī)中字符串取決于字符集，它們對應(yīng)相應(yīng)的編碼。如果采用gbk字符集，一個ASCII字符占1個字節(jié)，一個漢字為2個字節(jié)。常

量在字符串中不僅可以使用普通的字符，也可以使用幾個轉(zhuǎn)義序列，它們用來表示特殊的字符，如表。序列含義\0一個ASCII0（NUL）字符\n一個換行符\r一個回車符（Windows中使用\r、\n作為新行標(biāo)志）\t一個定位符\b一個退格符\Z一個ASCII26字符（Ctrl+Z）\'一個單引號\"一個雙引號\\一個反斜杠（\）\%一個%字符。它用于在正文中搜索“%”的文字實例，否則將解釋為多個字符通配符\_一個_字符。它用于在正文中搜索“_”的文字實例，否則將解釋為一個字符通配符常

量每個轉(zhuǎn)義序列都以一個反斜杠（“\”）開始。注意，NUL字節(jié)與NULL值不同，NUL為一個零值字節(jié)，而NULL代表沒有值。有以下幾種方式可以在字符串中包括引號。在字符串內(nèi)用單引號可以寫成兩個單引號（''）。在字符串內(nèi)用雙引號可以寫成兩個雙引號（""）?？梢栽谝柷凹愚D(zhuǎn)義字符（“\”）。在字符串內(nèi)用雙引號引用的單引號不需要特殊處理。同樣，在字符串內(nèi)用單引號引用的雙引號也不需要特殊處理。例如，下面語句執(zhí)行結(jié)果如圖。SELECTNULL,"It'stime.",N'It\'stime.','It''stime.';常

量（2）Unicode編碼字符串常量例如：N'你好，Howareyou!'。在普通字符串前面有一個N標(biāo)識符（N代表SQL-92標(biāo)準(zhǔn)中的國際語言）。N這個前綴必須為大寫，只能用單引號括起字符串。Unicode編碼的字符集常用uft8，每個ASCII字符用1字節(jié)，作為字符用3個字節(jié)存儲。（3）十六進(jìn)制字符串常量例如：x'4D7953514C'。MySQL支持十六進(jìn)制值。一個十六進(jìn)制值通常指定為一個字符串常量，每對十六進(jìn)制數(shù)字被轉(zhuǎn)換為一個字符，其最前面有一個字母“X”或“x”。在引號中只可以使用數(shù)字“0”到“9”及字母“a”到“f”或“A”到“F”。例如：X'41'表示大寫字母A，x'4D7953514C'表示字符串“MySQL”。十六進(jìn)制數(shù)值以“0x”（只能小寫）打頭，即X'41'可以替換為0x41。但它的默認(rèn)類型是字符串。如果想要確保該值作為數(shù)字處理，可以使用CAST(...ASunsigned)。將一個字符串轉(zhuǎn)換為十六進(jìn)制，可以用HEX()函數(shù)。用來存儲圖片（如JPG）和視頻（如AVI）等格式的數(shù)據(jù)可以表示為十六進(jìn)制。例如，下面語句執(zhí)行結(jié)果如圖。SELECT0x41,CAST(0x41ASunsigned);SELECTHEX('CAT');常

量（4）二進(jìn)制字符串常量可以使用b'二進(jìn)制'符號寫位值，例如：b'01000011'還可以表達(dá)一系列特殊的符號，例如，b'0'顯示為空白，b'1'顯示為一個笑臉圖標(biāo)。使用BIN函數(shù)可以將位值轉(zhuǎn)換為二進(jìn)制格式。使用OCT函數(shù)可以將位字符串轉(zhuǎn)換為數(shù)值型格式。例如，下面語句執(zhí)行結(jié)果如圖。SELECTb'01000011',BIN(b'01000011'+0),OCT(b'01000011'+0);（5）日期時間常量日期時間常量用單引號將表示日期時間的字符串括起來構(gòu)成，例如：'2014-06-1712:30:43'。日期型常量包括年、月、日，數(shù)據(jù)類型為date，表示為“2014-06-17”這樣的值。時間型常量包括小時數(shù)、分鐘數(shù)、秒數(shù)及微秒數(shù)，數(shù)據(jù)類型為time，表示為“12:30:43.00013”這樣的值。常

量3．布爾值布爾值只包含兩個可能的值：TRUE和FALSE。其中，TRUE的數(shù)字值為“1”，F(xiàn)ALSE的數(shù)字值為“0”。例如，下面語句執(zhí)行結(jié)果如圖。SELECTTRUE,FALSE,1<2,'a'<'A';4．NULL值NULL值可適用于各種列類型，通常用來表示“沒有值”“無數(shù)據(jù)”等意義，并且不同于數(shù)字類型的“0”或字符串類型的空字符串。02變

量自定義變量系統(tǒng)變量變

量1．自定義變量自定義變量包括用戶變量（以@符打頭）），在當(dāng)前會話中有效。局部變量僅在定義它的BEGIN…END語句體中有效。用戶可以先在用戶變量中保存值，然后在以后引用它，這樣可以將值從一個數(shù)據(jù)庫對象傳遞到另一個數(shù)據(jù)庫對象，或者從一個語句體傳遞到另一個語句體。定義一個變量可以使用SET語句。SET@變量名＝表達(dá)式,...]說明：（1）變量名可以由當(dāng)前字符集的文字?jǐn)?shù)字字符、“.”、“_”和“$”組成。當(dāng)變量名中需要包含一些特殊符號（如空格、#等）時，可以使用雙引號或單引號將整個變量括起來。（2）可以使用“=”或“:=”作為分配符，分配給每個變量的值可以為整數(shù)、實數(shù)、字符串或NULL值?？梢酝瑫r定義多個變量，中間用逗號隔開。（3）表達(dá)式可以是常量、變量或它們通過運算符組成的式子，變量的數(shù)據(jù)類型是由賦值表達(dá)式自動分配的，重新賦不同類型的值，則數(shù)據(jù)類型也會跟著改變。變

量（4）如果使用沒有賦值的變量，它的值為NULL例如：SET@name='王林';SET@user1=1,@user2=2,@user3=@user2+1;SELECT@name;SELECT@t2:=(@t2:=2)+5ASt2;第1句：創(chuàng)建用戶變量name并賦值為“王林”；第2句：創(chuàng)建用戶變量user1并賦值為1，user2賦值為2，user3賦值user2的值加1（為3）；第3句：查詢創(chuàng)建的用戶變量name的值；第4句：查詢結(jié)果t2的值。執(zhí)行結(jié)果如圖。變

量【例】根據(jù)姓名查詢學(xué)生成績信息。USExscj;SET@xm='林一帆';SET@xh=(SELECT學(xué)號FROMxsWHERE姓名=@xm);SELECT@xh,@xm,課程號,成績 FROMcj WHERE學(xué)號=@xh;查詢結(jié)果如圖。變

量2．系統(tǒng)變量系統(tǒng)變量包括全局變量和會話變量。服務(wù)器每次啟動都會為所有的全局變量賦初始值，跨會話（連接）有效。會話變量僅在本次會話有效。（1）@@符號大多數(shù)系統(tǒng)變量應(yīng)用時必須在名稱前加@@符號，例如，獲得現(xiàn)在使用的MySQL版本：SELECT@@version;說明：為了與其他SQL產(chǎn)品保持一致，某些特定的系統(tǒng)變量是要省略這兩個符號的，如CURRENT_DATE（系統(tǒng)日期）、CURRENT_TIME（系統(tǒng)時間）、CURRENT_TIMESTAMP（系統(tǒng)時間戳）和CURRENT_USER（當(dāng)前SQL用戶的名字）。例如，獲得系統(tǒng)當(dāng)前時間：SELECTCURRENT_TIME;變

量（2）系統(tǒng)變量的修改在MySQL中，有些系統(tǒng)變量的值是不可以改變的，如VERSION和系統(tǒng)日期。而有些系統(tǒng)變量是可以通過下列SET語句來修改的。SET系統(tǒng)變量名=表達(dá)式 |[GLOBAL|SESSION]系統(tǒng)變量名=表達(dá)式 |@@[GLOBAL.|SESSION.]系統(tǒng)變量名=表達(dá)式（3）指定了GLOBAL關(guān)鍵字的是全局系統(tǒng)變量；指定了SESSION關(guān)鍵字的則是會話系統(tǒng)變量，它的同義詞為LOCAL。如果在使用系統(tǒng)變量時不指定關(guān)鍵字，則默認(rèn)為會話系統(tǒng)變量。例如，將全局系統(tǒng)變量SORT_BUFFER_SIZE的值改為25000。SET@@GLOBAL.SORT_BUFFER_SIZE=25000;變

量（4）如果在使用SETGLOBAL同時又使用了一個只能在SESSION中使用的會話變量，或者如果在設(shè)置一個全局變量時未指定GLOBAL（或@@），則MySQL會產(chǎn)生錯誤。（5）大多數(shù)會話系統(tǒng)變量的名字和全局系統(tǒng)變量的名字相同。當(dāng)啟動會話的時候，每個會話系統(tǒng)變量都和同名的全局系統(tǒng)變量的值相同。一個會話系統(tǒng)變量的值是可以改變的，但是這個新的值僅適用于正在運行的會話，不適用于所有其他會話。例如，將當(dāng)前會話的SQL_WARNINGS變量設(shè)置為TRUE：SET@@SQL_WARNINGS=ON;MySQL對于大多數(shù)系統(tǒng)變量都有默認(rèn)值。當(dāng)數(shù)據(jù)庫服務(wù)器啟動的時候，就使用這些值。修改后如果要將一個系統(tǒng)變量值重新設(shè)置為MySQL默認(rèn)值，可以使用DEFAULT作為其值。例如，把當(dāng)前會話系統(tǒng)變量SQL_SELECT_LIMIT的值設(shè)置為10。這個變量決定了SELECT語句結(jié)果集中的最大行數(shù)。SET@@SESSION.SQL_SELECT_LIMIT=10;SELECT@@LOCAL.SQL_SELECT_LIMIT;執(zhí)行結(jié)果如圖。變

量（6）使用SHOWVARIABLES語句可以返回所有全局系統(tǒng)變量，而SHOWSESSIONVARIABLES則返回所有會話系統(tǒng)變量。要獲得與指定樣式匹配的具體的變量名稱或名稱清單，需使用LIKE子句，通配符為“%”。例如，以下語句得到符合指定要求的系統(tǒng)變量清單：SHOWVARIABLESLIKE'max_join_size';SHOWGLOBALVARIABLESLIKE'max_join_size';SHOWVARIABLESLIKE'character%';第6章SQL擴(kuò)展語言和系統(tǒng)函數(shù)——運算符與表達(dá)式01算術(shù)運算符算術(shù)運算符（1）“+”運算符。用于獲得一個或多個值的和。例如，下面語句執(zhí)行結(jié)果如圖。SELECT1.2+3.09345,0.00000000001+0.00000000001;（2）“–”運算符。用于從一個值中減去另一個值。例如，下面語句執(zhí)行結(jié)果如圖。SELECT200-201,0.14-0.1,-2,-23.4;其中，“+”（加）和“-”（減）運算符還可用于對日期時間值（如datetime）進(jìn)行算術(shù)運算。例如，下面語句執(zhí)行結(jié)果如圖。SELECT'2014-01-20'+INTERVAL22DAY;算術(shù)運算符（3）“*”運算符。用來獲得兩個或多個值的乘積。例如，下面語句執(zhí)行結(jié)果如圖。SELECT5*12,5*0,-11.2*8.2,-19530415*-19540319;（4）“/”運算符。用來獲得一個值除以另一個值的商。例如，下面語句執(zhí)行結(jié)果如圖。SELECT12/2,1.6/-0.1,23/7,23.00/7.00000,1/0;算術(shù)運算符（5）“%”運算符。用來獲得一個或多個除法運算的余數(shù)。例如，下面語句執(zhí)行結(jié)果如圖。SELECT12%5,-32%7,3%0;在運算過程中，字符串可以被自動地轉(zhuǎn)換為數(shù)值。如果字符串前面是部分?jǐn)?shù)字，那么它就會被轉(zhuǎn)換為這個數(shù)字的值，否則，被轉(zhuǎn)換為0。例如，下面語句執(zhí)行結(jié)果如圖。SELECT'80AA'+'1','AA80'+1,'10x'*2*'qwe';02比較運算符比較運算符比較運算符（又稱關(guān)系運算符），用于比較兩個表達(dá)式的值，其運算結(jié)果可以為3種之一：1（真）、0（假）及NULL（不確定）。表列出了在MySQL中可以使用的各種比較運算符。運算符含義運算符含義=等于<=小于等于>

大于<>、!=不等于<

小于<=>相等或都等于空>=大于等于

比較運算符（1）“=”運算符。用于比較表達(dá)式的兩邊是否相等，也可以對字符串進(jìn)行比較。例如，下面語句執(zhí)行結(jié)果如圖。SELECT3.14=3.142,5.12=5.120,'a'='A','A'='B','apple'='banana';例如，下面語句執(zhí)行結(jié)果如圖。SELECT'Apple'='apple',BINARY'Apple'='apple';比較運算符（2）“<>”運算符。與“=”運算符相對立的是“<>”運算符，它用來檢測表達(dá)式的兩邊是否不相等，如果不相等則返回真，相等則返回假。例如，下面語句執(zhí)行結(jié)果如圖。SELECT5<>5,5<>6,'a'<>'a','5a'<>'5b';例如，下面語句執(zhí)行結(jié)果如圖。SELECTNULL<>NULL,0<>NULL,0<>0;比較運算符（3）“<=”“>=”“<”和“>”運算符“<=”“>=”“<”和“>”運算符用來比較表達(dá)式的左邊是小于或等于、大于或等于、小于還是大于它的右邊。例如，下面語句執(zhí)行結(jié)果如圖。SELECT10>10,10>9,10<9,3.14>3.142;03邏輯運算符邏輯運算符MySQL提供的邏輯運算符如表。運算符含義運算符含義NOT或!邏輯非OR或||邏輯或AND或&&邏輯與XOR邏輯異或（1）NOT運算符。邏輯運算符中最簡單的是NOT運算符，它對跟在它后面的邏輯測試判斷取反，把真變假，把假變真。例如，下面語句執(zhí)行結(jié)果如圖。SELECTNOT1,NOT0,NOT(1=1),NOT(10>9);邏輯運算符（2）AND運算符。AND運算符用于測試兩個或更多的值（或表達(dá)式求值）的有效性，如果它的所有項為真，并且不是NULL，就返回真值，否則返回假值。例如，下面語句執(zhí)行結(jié)果如圖。SELECT(1=1)AND(9>10),('a'='a')AND('c'<'d');（3）OR運算符。如果包含的值或表達(dá)式有一個為真，并且不是NULL（不需要所有項為真），就返回1，若全為假則返回0。例如，下面語句執(zhí)行結(jié)果如圖。SELECT(1=1)OR(9>10),('a'='b')OR(1>2);邏輯運算符（4）XOR運算符。如果包含的值或表達(dá)式有一個為真，而另一個為假并且不是NULL，那么返回是真，否則返回是假。例如，下面語句執(zhí)行結(jié)果如圖。SELECT(1=1)XOR(2=3),(1<2)XOR(9<10);04位

運

算

符位運算符位運算符在兩個表達(dá)式之間執(zhí)行二進(jìn)制位操作，這兩個表達(dá)式的類型可為整型或與整型兼容的數(shù)據(jù)類型（如字符型），但不能為image類型。位運算符如表。運算符含義運算符含義&位AND~位取反|位OR>>

位右移^位XOR<<

位左移（1）“|”運算符和“&”運算符。“|”運算符用于執(zhí)行一個位的或操作，而“&”用于執(zhí)行一個位的與操作。例如，下面語句執(zhí)行結(jié)果如圖。SELECT13|28,3|4,13&28,3&4;位運算符（2）“<<”和“>>”運算符。分別用于向左和向右移動位。例如，下面語句執(zhí)行結(jié)果如圖。SELECT1<<7,64>>1;（3）“^”運算符。執(zhí)行位異或（XOR）操作。例如，下面語句執(zhí)行結(jié)果如圖。SELECT1^0,12^5,123^23;位運算符（4）“～”運算符。執(zhí)行位取反操作，并返回64位整型結(jié)果。例如，下面語句執(zhí)行結(jié)果如圖。SELECT~18446744073709551614,~1;05運算符優(yōu)先級運算符優(yōu)先級當(dāng)一個復(fù)雜的表達(dá)式有多個運算符時，運算符優(yōu)先級決定執(zhí)行運算的先后次序。執(zhí)行的次序有時會影響所得到的運算結(jié)果。運算符優(yōu)先級如表。運算符優(yōu)先級運算符優(yōu)先級+（正）、–（負(fù)）、～（按位NOT）1NOT6*（乘）、/（除）、%（模）2AND7+（加）、–（減）3ALL、ANY、BETWEEN、IN、LIKE、OR、SOME8=，>，<，>=，<=，<>，!=，!>，!<比較運算符4=（賦值）9^（位異或）、&（位與）、|（位或）5

06表

達(dá)

式表

達(dá)

式表達(dá)式還可以根據(jù)值的復(fù)雜性來分類。（1）當(dāng)表達(dá)式的結(jié)果只是一個值就是單一表達(dá)式。例如：'221101'又例如：學(xué)號（2）由運算符將多個單一表達(dá)式連接而成的表達(dá)式是復(fù)合表達(dá)式。例如：專業(yè)='計算機(jī)'AND性別=0表

達(dá)

式（3）當(dāng)表達(dá)式的結(jié)果是由不同類型數(shù)據(jù)組成的一行值時，這種表達(dá)式叫作行表達(dá)式。例如：'221101','王林','計算機(jī)',1,'2004-02-10',15（4）若表達(dá)式的結(jié)果為0個、1個或多個行表達(dá)式的集合，那么這個表達(dá)式就叫作表表達(dá)式。例如：'221101','王林','計算機(jī)',1,'2004-02-10',15'221102','程明','計算機(jī)',1,'2005-02-01',15第6章SQL擴(kuò)展語言和系統(tǒng)函數(shù)——系統(tǒng)內(nèi)置函數(shù)01數(shù)

學(xué)

函

數(shù)最大值和最小值函數(shù)最大整數(shù)和最小整數(shù)函數(shù)四舍五入函數(shù)生成隨機(jī)數(shù)數(shù)值絕對值函數(shù)符號函數(shù)平方根函數(shù)指數(shù)函數(shù)三角函數(shù)反三角函數(shù)進(jìn)制轉(zhuǎn)換函數(shù)數(shù)學(xué)函數(shù)1．最大值和最小值函數(shù)GREATEST(表達(dá)式,...)：求所有表達(dá)式最大值。LEAST(表達(dá)式,...)：求所有表達(dá)式最小值。例如，下面語句執(zhí)行結(jié)果如圖。SELECTGREATEST(10,9,128,1),LEAST(1,2,3);數(shù)學(xué)函數(shù)還可以嵌套使用，例如，下面語句執(zhí)行結(jié)果如圖。SELECTGREATEST(-2,LEAST(0,3)),LEAST(1,GREATEST(1,2));數(shù)學(xué)函數(shù)2．最大整數(shù)和最小整數(shù)函數(shù)FLOOR(數(shù)值)：獲得小于一個數(shù)的最大整數(shù)值。CEILING(數(shù)值)：獲得大于一個數(shù)的最小整數(shù)值。例如，下面語句執(zhí)行結(jié)果如圖。SELECTFLOOR(-1.2),CEILING(-1.2),FLOOR(9.9),CEILING(9.9);數(shù)學(xué)函數(shù)3．四舍五入函數(shù)ROUND(數(shù)值,位數(shù))：獲得一個數(shù)的指定位數(shù)四舍五入的數(shù)值。位數(shù)>0，為小數(shù)點后面；位數(shù)<0，為小數(shù)點前面。例如，下面語句執(zhí)行結(jié)果如圖。SELECTROUND(-234,-1),ROUND(25.416,2),ROUND(9.8);TRUNCATE(數(shù)值,位數(shù))：把一個數(shù)截取為一個指定位數(shù)的數(shù)值。位數(shù)>0，為小數(shù)點后面；位數(shù)<0，為小數(shù)點前面。例如，下面語句執(zhí)行結(jié)果如圖。SELECTTRUNCATE(1.54578,2),TRUNCATE(-76.12,5),TRUNCATE(-129,-1);數(shù)學(xué)函數(shù)4．生成隨機(jī)數(shù)RAND()：生成一個0~1之間的隨機(jī)數(shù)。例如，下面語句執(zhí)行結(jié)果如圖。SELECTRAND(),RAND()*10,RAND()*100;5．?dāng)?shù)值絕對值函數(shù)ABS(數(shù)值)：用來獲得一個數(shù)的絕對值。例如，下面語句執(zhí)行結(jié)果如圖。SELECTABS(-878),ABS(-8.345);數(shù)學(xué)函數(shù)6．符號函數(shù)SIGN(數(shù)值)：返回數(shù)字的符號，返回的結(jié)果是正數(shù)（1）、負(fù)數(shù)（

1）或者零（0）。例如，下面語句執(zhí)行結(jié)果如圖。SELECTSIGN(-2),SIGN(2),SIGN(0);7．平方根函數(shù)SQRT(正數(shù))：返回一個數(shù)的平方根。例如，下面語句執(zhí)行結(jié)果如圖。SELECTSQRT(25),SQRT(15),SQRT(1);數(shù)學(xué)函數(shù)8．指數(shù)函數(shù)POW(數(shù)值,指數(shù))：以一個數(shù)作為另外一個數(shù)的指數(shù)，并返回結(jié)果。例如，下面語句執(zhí)行結(jié)果如圖。SELECTPOW(2,2),POW(10,-2),POW(0,3);9．三角函數(shù)SIN(弧度)、COS(弧度)和TAN(弧度)：返回一個弧度的正弦、余弦和正切值。例如，下面語句執(zhí)行結(jié)果如圖。SELECTSIN(RADIANS(30)),COS(0),TAN(RADIANS(45)),DEGREES(PI());數(shù)學(xué)函數(shù)10．反三角函數(shù)ASIN(弧度)、ACOS(弧度)和ATAN(弧度)：返回一個弧度的反正弦、反余弦和反正切值。例如，下面語句執(zhí)行結(jié)果如圖。SELECTASIN(1),ACOS(1),ATAN(45);11．進(jìn)制轉(zhuǎn)換函數(shù)BIN(整數(shù))、OCT(整數(shù))和HEX(整數(shù))：分別返回一個數(shù)的二進(jìn)制、八進(jìn)制和十六進(jìn)制值，這個值以字符串返回。例如，下面語句執(zhí)行結(jié)果如圖。SELECTBIN(2),OCT(12),HEX(80);02聚

合

函

數(shù)聚合函數(shù)聚合意味著是對一組值（而不是單個值）求和、平均值、頻次等，聚合函數(shù)常用于對一組值進(jìn)行聚合計算，然后返回單個值。一個GROUPBY子句的SELECT語句中包含聚合函數(shù)，數(shù)據(jù)就可以聚合。03字符串函數(shù)字符和對應(yīng)的ASCII()相互轉(zhuǎn)換函數(shù)獲得字符串的長度函數(shù)字符串左邊和右邊截取字符函數(shù)刪除字符串左右空格函數(shù)填補字符串函數(shù)字符串替換函數(shù)字符串連接函數(shù)求字符串子串函數(shù)字符串比較函數(shù)字符串函數(shù)1．字符和對應(yīng)的ASCII()相互轉(zhuǎn)換函數(shù)ASCII(字符)：返回字符表達(dá)式最左端字符的ASCII碼值。CHAR(整數(shù),…)：將整數(shù)x1、x2...的ASCII碼轉(zhuǎn)換為字符，結(jié)果組合成一個字符串。參數(shù)x1、x2、x3...為0～255范圍內(nèi)的整數(shù)，返回值為字符型。例如，下面語句執(zhí)行結(jié)果如圖。SELECTASCII('A'),CHAR(65,66,67);字符串函數(shù)2．獲得字符串的長度函數(shù)LENGTH(字符串)：獲得指定字符串占用字節(jié)數(shù)。ASCII碼字符，每個字符占用1個字節(jié)，如果是其他類型字符，則需要看目前系統(tǒng)客戶端采用的字符集。下列語句顯示當(dāng)前所有字符集：SHOWVARIABLESLIKE'character%';顯示結(jié)果如圖。字符串函數(shù)可以看出，當(dāng)前連接（character_set_connection）采用utf8mb4字符集，該字符集ASCII碼字符，每個字符占用1個字節(jié)，1個漢字編碼為3個字節(jié)。例如，下面語句執(zhí)行結(jié)果如圖。SELECTLENGTH('漢字abc');修改連接字符集為gb2312（標(biāo)準(zhǔn)漢字編碼）和gbk（擴(kuò)展?jié)h字編碼），該字符集ASCII碼字符，每個字符占用1個字節(jié)，1個漢字編碼為2個字節(jié)。例如：SETcharacter_set_connection=gbk;SELECTLENGTH('漢字abc');執(zhí)行結(jié)果如圖。字符串函數(shù)3．字符串左邊和右邊截取字符函數(shù)LEFT(字符串,字符個數(shù))和RIGHT(字符串,字符個數(shù))：截取字符串左邊或者右邊指定個數(shù)字符?！纠匡@示學(xué)生表包含的班級，測試字符串長度。USExscj;SELECTDISTINCTLEFT(學(xué)號,4)AS班級 FROMxs; #（a）SETcharacter_set_connection=gbk;SET@str='漢字abc';SELECTLENGTH(@str),LEFT(@str,3); #（b）執(zhí)行結(jié)果如圖。

字符串函數(shù)4．刪除字符串左右空格函數(shù)LTRIM(字符串)、RTRIM(字符串)和TRIM(字符串)：分別刪除字符串中左部空格、右部空格和兩端空格，返回值為字符串。例如，下面語句執(zhí)行結(jié)果如圖。SET@str='MySQL';SELECTTRIM(@str),LENGTH(TRIM(@str))),LENGTH(RTRIM(@str));字符串函數(shù)5．填補字符串函數(shù)RPAD(字符串,長度,填補字符串)和LPAD(字符串,長度,填補字符串)：分別對字符串右側(cè)和左側(cè)進(jìn)行填補，直至字符數(shù)目達(dá)指定長度，最后返回填補后的字符串。若填補字符串的字符個數(shù)大于指定長度，則返回前面指定長度字符。例如，下面語句執(zhí)行結(jié)果如圖。SET@x='ABC';SELECTRPAD(@x,8,'='),LPAD(@x,8,'=');字符串函數(shù)6．字符串替換函數(shù)REPLACE(字符串1,字符串2,字符串3)：用字符串3替換字符串1中所有出現(xiàn)的字符串2，最后返回替換后的字符串。例如，下面語句執(zhí)行結(jié)果如圖。SELECTREPLACE("It'stime.",'\'','i');7．字符串連接函數(shù)CONCAT(字符串,...)：連接指定的函數(shù)參數(shù)字符串。例如，下面語句執(zhí)行結(jié)果如圖。SELECTCONCAT('江蘇省','南京市','玄武區(qū)');字符串函數(shù)8．求字符串子串函數(shù)SUBSTRING(表達(dá)式,起始位置,長度)：返回表達(dá)式中起始位置開始、長度個數(shù)（字節(jié)）的字串。表達(dá)式可為字符串、二進(jìn)制串、text、image列或表達(dá)式。如果表達(dá)式是字符類型和二進(jìn)制類型，則返回值類型與表達(dá)式的類型相同；如果為text類型，返回的是varchar類型?！纠糠謩e顯示xs表中所有女學(xué)生的姓和名。USExscj;SELECTSUBSTRING(姓名,1,1)as姓,SUBSTRING(姓名,2,LENGTH(姓名)-1)AS名 FROMxs WHERE性別=0 ORDERBY姓名;查詢結(jié)果如圖。字符串函數(shù)9．字符串比較函數(shù)STRCMP(字符串1,字符串2)：用于比較兩個字符串，s1與s2相等返回0，s1大于s2返回1，s1小于s2返回-1。例如，下面語句執(zhí)行結(jié)果如圖。SELECTSTRCMP('A','A'),STRCMP('ABC','OPQ'),STRCMP('T','B');04日期和時間函數(shù)當(dāng)前日期時間函數(shù)當(dāng)前時間和日期函數(shù)指定日期對應(yīng)年函數(shù)指定日期對應(yīng)月函數(shù)指定日期對應(yīng)的年、星期和月函數(shù)指定日期對應(yīng)的星期函數(shù)指定時間對應(yīng)的時分秒函數(shù)日期時間增減間隔值函數(shù)日期和時間函數(shù)1．當(dāng)前日期時間函數(shù)NOW()：獲得當(dāng)前的日期和時間，它以YYYY-MM-DDHH:MM:SS的格式返回當(dāng)前的日期和時間。2．當(dāng)前時間和日期函數(shù)CURTIME()和CURDATE()：比NOW()函數(shù)更為具體化，它們返回的分別是當(dāng)前的時間和日期。例如，下面語句執(zhí)行結(jié)果如圖。SELECTNOW(),CURTIME(),CURDATE();日期和時間函數(shù)3．指定日期對應(yīng)年函數(shù)YEAR(日期)：獲得一個日期中年的部分。例如，下面語句執(zhí)行結(jié)果如圖。SELECTYEAR(20180512142800),YEAR('2022-11-02');4．指定日期對應(yīng)月函數(shù)MONTH(日期)和MONTHNAME(日期)：分別以數(shù)值和字符串的格式返回月的部分。例如，下面語句執(zhí)行結(jié)果如圖。SELECTMONTH(20180512142800),MONTHNAME('2022-11-02');日期和時間函數(shù)5．指定日期對應(yīng)的年、星期和月函數(shù)DAYOFYEAR(日期)、DAYOFWEEK(日期)和DAYOFMONTH(日期)：分別返回指定一天在一年、一星期及一個月中的序數(shù)。例如，下面語句執(zhí)行結(jié)果如圖。SELECTDAYOFYEAR(20180512),DAYOFMONTH('2022-11-02'),DAYOFWEEK(20221102);日期和時間函數(shù)6．指定日期對應(yīng)的星期函數(shù)DAYNAME(日期)：以字符串形式返回星期幾。WEEK(日期)：返回指定的日期是這一年的第幾個星期。YEARWEEK(日期)：返回指定的日期是哪一年的第幾個星期。例如，下面語句執(zhí)行結(jié)果如圖。SELECTDAYNAME('2018-06-26'),WEEK('2021-05-01'),YEARWEEK(20210501);日期和時間函數(shù)7．指定時間對應(yīng)的時分秒函數(shù)HOUR(時間)、MINUTE(時間)和SECOND(時間)：分別返回時間值的小時、分鐘和秒的部分。例如，下面語句執(zhí)行結(jié)果如圖。SELECTHOUR(155300),MINUTE('15:53:00'),SECOND(143415);日期和時間函數(shù)8．日期時間增減間隔值函數(shù)DATE_ADD(日期,INTERVAL間隔值關(guān)鍵字)DATE_SUB(日期,INTERVAL間隔值關(guān)鍵字)DATE_ADD()函數(shù)用來對指定日期時間增加間隔值，DATE_SUB()減少間隔值。INTERVAL表示一個時間間隔，關(guān)鍵字和間隔值格式如表。關(guān)鍵字間隔值格式關(guān)鍵字間隔值格式DAY日期MINUTE分鐘DAY_HOUR日期:小時MINUTE_SECOND分鐘:秒DAY_MINUTE日期:小時:分鐘MONTH月DAY_SECOND日期:小時:分鐘:秒SECOND秒HOUR小時YEAR年HOUR_MINUTE小時:分鐘YEAR_MONTH年-月HOUR_SECOND小時:分鐘:秒

日期和時間函數(shù)DATE_ADD()函數(shù)計算指定日期加上時間間隔后的值，DATE_SUB()函數(shù)則計算指定日期減去時間間隔后的值。例如，下面語句執(zhí)行結(jié)果如圖。SELECTDATE_ADD('2014-08-08',INTERVAL17DAY);SELECTDATE_SUB('2014-08-2010:25:35',INTERVAL20MINUTE);日期和時間函數(shù)【例】求xs表中所有女學(xué)生的年齡。USExscj;SELECT學(xué)號,姓名,YEAR(NOW())-YEAR(出生日期)AS年齡 FROMxs WHERE性別=0;查詢結(jié)果如圖。05加

密

函

數(shù)高級加密和解密函數(shù)加密和解密函數(shù)固定加密函數(shù)加密函數(shù)1．高級加密和解密函數(shù)AES_ENCRYPT(字符串,密鑰)和AES_DECRYPT(字符串,密鑰)：返回對字符串用高級加密標(biāo)準(zhǔn)（AES）算法和密鑰加密后的結(jié)果，是一個二進(jìn)制字符串，以blob類型存儲。AES_DECRYPT()函數(shù)進(jìn)行解密。2．加密和解密函數(shù)ENCODE(字符串,密鑰)和DECODE(字符串,密鑰)：ENCODE()函數(shù)用來對一個字符串采用密鑰加密，返回的結(jié)果是一個二進(jìn)制字符串，以blob類型存儲。DECODE函數(shù)使用正確的密鑰對加密后的字符串進(jìn)行解密。加密函數(shù)3.固定加密函數(shù)PASSWORD(字符串)和MD5(字符串)：返回字符串加密后的密碼字符串，適用于插入MySQL的安全系統(tǒng)，該加密過程不可逆。MySQL5.7版后，廢除了PASSWORD。例如，下面語句執(zhí)行結(jié)果如圖。SET@x='ABC';SELECTAES_DECRYPT(AES_ENCRYPT(@x,'123'),'123')=@X; #（a）SELECTDECODE(ENCODE(@x,'123'),'123')=@X; #（b）SELECTPASSWORD(@x),MD5(@x); #（c）

06控制流函數(shù)判斷是否為空函數(shù)判斷是否相等函數(shù)判斷函數(shù)控制流函數(shù)1.判斷是否為空函數(shù)IFNULL(表達(dá)式1,表達(dá)式2)：判斷參數(shù)表達(dá)式1是否為NULL，為NULL時返回表達(dá)式2，不為NULL時返回表達(dá)式1。例如，下面語句執(zhí)行結(jié)果如圖。SELECTIFNULL(1,2),IFNULL(NULL,'MySQL'),IFNULL(1/0,10);控制流函數(shù)2.判斷是否相等函數(shù)NULLIF(表達(dá)式1,表達(dá)式2)：檢驗提供的兩個參數(shù)是否相等，如果相等，則返回NULL，如果不相等就返回第一個參數(shù)。例如，下面語句執(zhí)行結(jié)果如圖。SELECTNULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,3+4);控制流函數(shù)3.判斷函數(shù)IF(表達(dá)式1,表達(dá)式2,表達(dá)式3)：如果表達(dá)式1為真，返回第2個參數(shù)；如果表達(dá)式1為假，返回第3個參數(shù)。例如，下面語句執(zhí)行結(jié)果如圖。SELECTIF(2*4>9-5,'是','否');加密函數(shù)【例】查詢xs表中名字為兩個字的學(xué)生的姓名、性別和專業(yè)名。性別值如為0顯示“女”，如為1則顯示“男”。USExscj;SELECT姓名,IF(性別=0,'女','男')AS性別,專業(yè) FROMxs WHERE姓名LIKE'__';查詢結(jié)果如圖。07格式化函數(shù)格式化函數(shù)MySQL還有一些函數(shù)是特意為格式化數(shù)據(jù)設(shè)計的。（1）數(shù)值格式化函數(shù)。FORMAT(數(shù)值,小數(shù)位數(shù))：把數(shù)值格式化為以逗號為間隔的數(shù)字序列。例如，下面語句執(zhí)行結(jié)果如圖。SELECTFORMAT(1625610.23654,2),FORMAT(-5468,4);格式化函數(shù)（2）日期和時間格式化函數(shù)。DATE_FORMAT(日期,格式化符)TIME_FORMAT(時間,格式化符)MySQL中的日期/時間格式化符號如表。符號值的格式符號值的格式%a縮寫的星期名（Sun，Mon…）%pAM或PM%b縮寫的月份名（Jan，F(xiàn)eb…）%r時間，12小時的格式%d月份中的天數(shù)%S秒（00，01…）%H小時（01，02…）%T時間，24小時的格式%I分鐘（00，01…）%w一周中的天數(shù)（0，1…）%j一年中的天數(shù)（001，002…）%W長型星期的名字（Sunday，Monday…）%m月份，2位（00,01…）%Y年份，4位%M長型月份的名字（January，F(xiàn)ebruary）

例如，下面語句執(zhí)行結(jié)果如圖。SELECTDATE_FORMAT(NOW(),'%W,%d,%M,%Y%r');格式化函數(shù)（3）IP地址和數(shù)字相互轉(zhuǎn)換函數(shù)。INET_NTOA()和INET_AT