MySQL數(shù)據(jù)庫(kù)管理與應(yīng)用任務(wù)式教程（微課版）單元9 mysql 數(shù)據(jù)庫(kù)編程基礎(chǔ)

單元9MySQL數(shù)據(jù)庫(kù)編程基礎(chǔ)《MySQL數(shù)據(jù)庫(kù)管理與應(yīng)用任務(wù)式教程（慕課版）》學(xué)習(xí)內(nèi)容任務(wù)1數(shù)據(jù)類型任務(wù)4函數(shù)數(shù)據(jù)庫(kù)系統(tǒng)的組成任務(wù)2常量與變量任務(wù)3運(yùn)算符與表達(dá)式任務(wù)1數(shù)據(jù)類型任務(wù)5流程控制任務(wù)6游標(biāo)任務(wù)1數(shù)據(jù)類型9.1.1數(shù)值類型類型名字節(jié)數(shù)可表示數(shù)的范圍無(wú)符號(hào)unsigned有符號(hào)signbigint80~264-1即（0~18446744073709551615）-263~263即(-9233372036854775808~9233372036854775807)int40~232-1即（0~4294967295）-231~231即(-2147483648~2147483647)mediumint30~224~1即（0~16777215）-223~223即(-8388608~8388607)smallint20~216-1即（0~65535）-215~215即(-32768~32767)tinyint10~28-1即（0~255）-27~27即(-128~127)

（1)在數(shù)據(jù)表中存儲(chǔ)的一些數(shù)值，如商品銷售量、商品價(jià)格等，這類數(shù)據(jù)都適合用數(shù)值類型來(lái)表達(dá)，數(shù)值類型包括整型、浮點(diǎn)型等。各數(shù)值類型的名稱及使用范圍如表9-1、表9-2所示。表9-1數(shù)值類型—整數(shù)類型表9-1任務(wù)1數(shù)據(jù)類型

(2)在數(shù)據(jù)表中存儲(chǔ)的一些數(shù)值，如商品銷售量、商品價(jià)格等，這類數(shù)據(jù)都適合用數(shù)值類型來(lái)表達(dá)，數(shù)值類型，包括整型、浮點(diǎn)型等。各數(shù)值類型的名稱及使用范圍如表9-1、表9-2所示。表9-1數(shù)值類型—整數(shù)類型

表9-2類型名字節(jié)數(shù)可表示數(shù)的范圍無(wú)符號(hào)unsigned有符號(hào)signfloat40和1.175494351E-38～3.402823466E+38-3.402823466E+38～-1.175494351E-38double80和2.2250738585072014E-308～1.7976931348623157E+308-1.7976931348623157E+308～-2.2250738585072014E-308decimal(m,d)m+2最大取值范圍與DOUBLE相同最大取值范圍與DOUBLE相同任務(wù)1數(shù)據(jù)類型

表達(dá)數(shù)值型數(shù)據(jù)時(shí)，對(duì)涉及到小數(shù)的存儲(chǔ)就要使用浮點(diǎn)數(shù)或定點(diǎn)數(shù)表達(dá)，同時(shí)依據(jù)所要考慮到表達(dá)數(shù)的精度，浮點(diǎn)數(shù)又分為單精度浮點(diǎn)數(shù)float和雙精度浮點(diǎn)數(shù)double兩種類型。其中Float所表達(dá)的小數(shù)位數(shù)是5-6位，double雙精度所表達(dá)的小數(shù)位數(shù)可達(dá)到15位。

對(duì)于定點(diǎn)數(shù)類型decimal是通過(guò)decimal(m,d)來(lái)設(shè)置位數(shù)和精度的，其中m表示所有數(shù)據(jù)字符的總位數(shù)（不包括“.”和“-”），最大值為65，默認(rèn)值為10，其中d表示小數(shù)位數(shù)據(jù)個(gè)數(shù)，最大值為30，默認(rèn)值為0。在任務(wù)1數(shù)據(jù)類型9.1.2字符串類型(1)當(dāng)所用數(shù)據(jù)是字符或字符串時(shí)就要用到字符串類型數(shù)據(jù)，MySQL支持兩類字符型數(shù)據(jù)：文本字符串和二進(jìn)制字符串。文本字符串主要有char、varchar、binary、varbinary、blob、text等，二進(jìn)制字符串主要有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB等，常用的字符串類型如表9-3所示。

表3字符類型名字節(jié)數(shù)數(shù)據(jù)存儲(chǔ)描述Char(m)mm為0至255之間的整數(shù)Varchar(m)mm為0至65535之間的整數(shù)（默認(rèn)）TextL+2允許長(zhǎng)度為0至65535字節(jié)，列值的長(zhǎng)度L+2個(gè)字節(jié)Binarym允許長(zhǎng)度為0至m個(gè)字節(jié)的定長(zhǎng)字節(jié)字符串Varbinarym允許長(zhǎng)度為0至m個(gè)字節(jié)的變長(zhǎng)字節(jié)字符串BlobL+2允許長(zhǎng)度為0至65535字節(jié)，列值的長(zhǎng)度L+2個(gè)字節(jié)ENUM1或2個(gè)取決于枚舉值的數(shù)目（最大值為65535）SET1、2、3、4或8取決于集合成員的數(shù)量（最多64個(gè)成員）任務(wù)1數(shù)據(jù)類型表示字符串類型數(shù)據(jù)時(shí)需用單引號(hào)將字符內(nèi)容括起來(lái)。(1)char表示固定長(zhǎng)度非二進(jìn)制字符串。(2)varchar表示變長(zhǎng)非二進(jìn)制字符串，字段的最大值默認(rèn)為65535，所默認(rèn)對(duì)應(yīng)的字符集是latin1，其他字符集如gbk其對(duì)應(yīng)的m最大值為32766，utf8字符集對(duì)應(yīng)的m最大值為21844。(3)文本類型text表示變長(zhǎng)非二進(jìn)制字符串，適用于存儲(chǔ)大量數(shù)據(jù)。它又分為T(mén)INYTEXT、TEXT、MEDIUMTEXT和LONGTEXT四種類型。不同的TEXT類型的存儲(chǔ)空間和數(shù)據(jù)長(zhǎng)度不同。BINARY和VARBINARY類型類似于CHAR和VARCHAR，不同的是它們包含二進(jìn)制字節(jié)字符串，BINARY類型的長(zhǎng)度是固定的，指定長(zhǎng)度后，不足最大長(zhǎng)度的，將在它們右邊填充“\0”補(bǔ)齊，以達(dá)到指定長(zhǎng)度。任務(wù)1數(shù)據(jù)類型

BLOB類型分為4種：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，它們可容納值的最大長(zhǎng)度不同。(1)BLOB列是字符集，并且排序和比較基于列值字節(jié)的數(shù)值；TEXT列有一個(gè)字符集，并且根據(jù)字符集對(duì)值進(jìn)行排序和比較。(2)enum類型又稱枚舉類型，是一個(gè)字符串對(duì)象。(3)ENUM類型的字段在取值時(shí)，能在指定的枚舉列表中獲取，而且一次只能取一個(gè)。在MySQL中枚舉列表最多可以有65535個(gè)值，且每個(gè)值都有一個(gè)從1開(kāi)始的順序編號(hào)，實(shí)際保存在記錄中的是順序編號(hào)而不是列表中的值。其定義格式如下。Enum('值1'，'值2'，...，'值n')(4)set類型用于保存字符串對(duì)象，值為表創(chuàng)建時(shí)規(guī)定的一列值，SET列最多可以有64個(gè)成員。與ENUM類型相同，SET值在內(nèi)部用整數(shù)表示，列表中每個(gè)值都有一個(gè)索引編號(hào)。不同的是SET類型的列可從定義的列值中選擇多個(gè)字符的聯(lián)合。其定義格式如下。Set('值1'，'值2'，...，'值n')任務(wù)1數(shù)據(jù)類型9.1.3日期和時(shí)間類型在表達(dá)日期或時(shí)間之類的數(shù)據(jù)時(shí)將要用到日期和時(shí)間類型數(shù)據(jù)，MySQL數(shù)據(jù)庫(kù)提供的日期和時(shí)間類型有year、date、time、datetime和timestamp，可以用來(lái)表示不同范圍的日期或時(shí)間值，如表9-4所示。表9-4日期和時(shí)間類型名字節(jié)數(shù)數(shù)據(jù)存儲(chǔ)描述year1取值范圍為1901到2155time3可以包含6位的毫秒微秒精度，其取值范圍為-838:59:59.000000到838:59:59.000000date4可表達(dá)日期范圍1000-01-01至9999-12-31datetime8可表達(dá)時(shí)間范圍1000-01-0100:00:00至9999-12-3123:59:59timestamp4可表達(dá)日期時(shí)間范圍1970-01-0100:00:01到2038-01-1903:14:07任務(wù)1數(shù)據(jù)類型

year

類型用來(lái)僅表示年份，默認(rèn)格式為YYYY。time類型用來(lái)僅表示時(shí)間，默認(rèn)格式為HH:MM:SS，Time類型可以包含6位的毫秒微秒精度。小時(shí)字段可以超過(guò)24是因?yàn)閠ime類型不光代表小時(shí)，也可以代表持續(xù)時(shí)長(zhǎng)中的小時(shí)。date類型僅用來(lái)表示日期，默認(rèn)的格式為yyyy-mm-dd。datetime類型用來(lái)表示日期和時(shí)間，默認(rèn)的格式為yyyy-mm-ddhh:mi:ss。timestamp類型也用來(lái)表示日期和時(shí)間。datetime和timestamp兩個(gè)類型都可以保存到微妙級(jí)別，即6位毫秒微妙精度，即1000-01-0100:00:00.000000到9999-12-3123:59:59.999999和1970-01-0100:00:01.000000到2038-01-1903:14:07.999999。非法的date、datetime、timestamp值將被分別轉(zhuǎn)換成0、0000-00-00、0000-00-0000:00:00。

year首字母大寫(xiě)還是小寫(xiě)？表中為小寫(xiě)？任務(wù)2常量與變量9.2.1常量常量即是在數(shù)據(jù)操作或程序運(yùn)行過(guò)程中值保持不變的量，如數(shù)值常量123，字符常量‘a(chǎn)’，默認(rèn)值default0等。任務(wù)2常量與變量9.2.2變量變量是程序運(yùn)行過(guò)程中數(shù)值可以發(fā)生變化的量，主要用來(lái)臨時(shí)存儲(chǔ)數(shù)據(jù)。變量的使用要遵循先定義（或聲明）后使用的原則（系統(tǒng)變量除外）。在MySQL中根據(jù)變量的作用范圍可以將變量劃分為系統(tǒng)變量（全局變量）、用戶自定義變量（會(huì)話變量）及局部變量。任務(wù)2常量與變量

1.系統(tǒng)變量系統(tǒng)變量也稱為全局變量，是MySQL系統(tǒng)內(nèi)部定義的、具有某種功能的變量，形如@@變量名，如@@query_cache_limit等。系統(tǒng)變量對(duì)所有的MySQL客戶端都有效，默認(rèn)情況下，會(huì)在服務(wù)器啟動(dòng)時(shí)使用命令行上的選項(xiàng)或配置文件完成系統(tǒng)變量的設(shè)置，因此用戶不能再定義系統(tǒng)變量，但可以查看和修改變量值。（1）查看系統(tǒng)變量可使用showvariableslike'auto_%';語(yǔ)句查看系統(tǒng)變量。任務(wù)2常量與變量（2）修改系統(tǒng)變量的值在成功連接MySQL服務(wù)器，且系統(tǒng)變量被初始化后，用戶有時(shí)要根據(jù)實(shí)際需求對(duì)系統(tǒng)變量值作局部修改或全局修改。①局部修改系統(tǒng)變量的值

若修改的系統(tǒng)變量只需在本次連接中有效，且不影響其他連接MySQL服務(wù)器客戶端使用時(shí)，只能使用局部修改，基本語(yǔ)法如下。Set變量名=新值;例如，在一個(gè)客戶端上對(duì)系統(tǒng)變量auto_increment_offset的值作修改，在另一客戶端上看到的auto_increment_offset的值仍然是原值。

②全局修改系統(tǒng)變量的值對(duì)系統(tǒng)變量作全局修改對(duì)所有正在連接的客戶端無(wú)效，只對(duì)新連接的客戶端永久生效，基本語(yǔ)法如下。Setglobal變量名=值;或set@@global.變量名=值;

任務(wù)2常量與變量2.用戶自定義變量用戶自定義變量也稱為會(huì)話變量，是指由用戶根據(jù)需求進(jìn)行定義的變量，跟MySQL當(dāng)前客戶端是綁定的，且僅對(duì)當(dāng)前用戶使用的客戶端有效。用戶自定義變量的語(yǔ)法如下。Select@變量名:=表達(dá)式值;或set@變量名=值;對(duì)用戶自定義變量定義時(shí)還必須為該變量賦初值，賦值方式有如下三種。任務(wù)2常量與變量（1）使用set命令set@變量名=表達(dá)式值;例如，set@xh='20200001';（2）使用select命令select@變量名:=字段值from表名;或select@變量名:=字段值[as輸出標(biāo)識(shí)符名稱]from表名;注意：這里使用了賦值符號(hào):=以區(qū)別于=。例如，select@xm:=姓名fromxswhere學(xué)號(hào)='001101';（3）使用select語(yǔ)句select字段名列表from表名into@變量名;例如，select學(xué)號(hào),姓名,出生時(shí)間fromxslimit1into@xh,@xm,@csrq;任務(wù)2常量與變量3.局部變量相對(duì)于mysql提供的系統(tǒng)變量和用戶自定義的變量，局部變量的作用域僅在復(fù)合語(yǔ)句begin...end中，也就是只能在begin...end中定義和使用，超出范圍則無(wú)效。定義局部變量的語(yǔ)法如下。declare變量名數(shù)據(jù)類型[default默認(rèn)值];任務(wù)3運(yùn)算符與表達(dá)式9.3.1運(yùn)算符

在數(shù)據(jù)庫(kù)的操作及程序中都會(huì)用運(yùn)算符及表達(dá)式，表達(dá)式是程序語(yǔ)句的基本構(gòu)成單位，是由運(yùn)算符將常量、變量等對(duì)象連接起來(lái)構(gòu)成的有意義的式子。

運(yùn)算符用于執(zhí)行程序代碼運(yùn)算及操作數(shù)據(jù)項(xiàng)目運(yùn)算。在MySQL中運(yùn)算符分為算術(shù)運(yùn)算符、關(guān)系運(yùn)算符、賦值運(yùn)算符和邏輯運(yùn)算符等。1.算術(shù)運(yùn)算符算術(shù)運(yùn)算符適用于數(shù)值類型數(shù)據(jù)的運(yùn)算。常用的算術(shù)運(yùn)算符如表9-5所示。符號(hào)作用+加法運(yùn)算-減法運(yùn)算*乘法運(yùn)算/除法運(yùn)算div除法運(yùn)算，結(jié)果去掉小數(shù)部分% （或mod）求余運(yùn)算，返回余數(shù)表9-5任務(wù)3運(yùn)算符與表達(dá)式在MySQL中若運(yùn)算符“+”、“-”、“*”的操作數(shù)都是無(wú)符號(hào)整型，則運(yùn)算結(jié)果也是無(wú)符號(hào)整型。運(yùn)算符“/”在MySQL中用于除法操作，且運(yùn)算結(jié)果使用浮點(diǎn)數(shù)表示，其浮點(diǎn)數(shù)的精度值等于被除數(shù)的精度加上系統(tǒng)變量div_precision_increment設(shè)置的除法精度增長(zhǎng)值。運(yùn)算符div運(yùn)算時(shí)會(huì)去掉結(jié)果中的小數(shù)部分只返回整數(shù)部分。運(yùn)算符“%”和mod在MySQL中功能相同，表達(dá)取模（求余）運(yùn)算，其運(yùn)算結(jié)果的正負(fù)與被模數(shù)（%左邊的操作數(shù)）的符號(hào)相同，與模數(shù)（%右邊的操作數(shù)）的符號(hào)無(wú)關(guān)。任務(wù)3運(yùn)算符與表達(dá)式2.比較運(yùn)算符比較運(yùn)算符通常用于各類條件表達(dá)式中，常用的比較運(yùn)算符如表9-6所示。表9-6符號(hào)作用=加法運(yùn)算<

減法運(yùn)算<=乘法運(yùn)算>

除法運(yùn)算>= 求余運(yùn)算，返回余數(shù)<>或!=不等于Like‘區(qū)配模式’獲取匹配到的數(shù)據(jù)is比較一個(gè)數(shù)據(jù)是否是true,false或nuknown，若是則返回1否則返回0Isnot比較一個(gè)數(shù)據(jù)是否是true,false或nuknown，若不是則返回1否則返回0Isnull比較一個(gè)數(shù)據(jù)是否是null，若是返回1，否則返回0Between...and…比較一個(gè)數(shù)據(jù)是否在指定的閉區(qū)間范圍內(nèi)，若在則返回1，若不在則返回0任務(wù)3運(yùn)算符與表達(dá)式3.邏輯運(yùn)算符邏輯運(yùn)算符通常用于條件表達(dá)式的邏輯判斷，與比較運(yùn)算符相配合運(yùn)用，常用的運(yùn)算符號(hào)如表9-7所示。參與邏輯運(yùn)算的操作數(shù)以及邏輯判斷的結(jié)果是3種布爾型值，分別是真值（1或true）、假值（0或false）和空值(null)。表9-7符號(hào)作用AND或&&邏輯與運(yùn)算，當(dāng)操作數(shù)全為真（1），則結(jié)果為真（1）；否則為假（0）OR或||邏輯或運(yùn)算，當(dāng)操作數(shù)中只要有一個(gè)為真（1），則結(jié)果為真（1）；否則為假（0）NOT或!邏輯非運(yùn)算，當(dāng)操作數(shù)為真（1），則結(jié)果為假（0）；當(dāng)操作數(shù)為假（0），則結(jié)果為真（1）XOR邏輯異或，操作數(shù)一個(gè)為真（1），一個(gè)為假（0），則結(jié)果為真（1）；若操作數(shù)全為真（1）或全為假（0），則結(jié)果為假（0）。任務(wù)3運(yùn)算符與表達(dá)式4.位運(yùn)算符位運(yùn)算符是針對(duì)二進(jìn)制數(shù)的每一位進(jìn)行運(yùn)算的符號(hào)，運(yùn)算的結(jié)果類型為bigint，最大范圍可以是64位，常用的位運(yùn)算符如表9-8所示。位運(yùn)算必須先將數(shù)據(jù)轉(zhuǎn)換為二進(jìn)制，然后在二進(jìn)制格式下進(jìn)行操作,運(yùn)算完成后，將二進(jìn)制的值轉(zhuǎn)換為原來(lái)的類型，返回給用戶。表9-8符號(hào)作用&按位與，將參與運(yùn)算的兩個(gè)數(shù)據(jù)按對(duì)應(yīng)的二進(jìn)制數(shù)逐位進(jìn)行邏輯與運(yùn)算。若對(duì)應(yīng)的二進(jìn)制位都為1，則該位的運(yùn)算結(jié)果為1，否則為0|按位或，將參與運(yùn)算的兩個(gè)數(shù)據(jù)按對(duì)應(yīng)的二進(jìn)制數(shù)逐位進(jìn)行邏輯或運(yùn)算。若對(duì)應(yīng)的二進(jìn)制位有一個(gè)或兩個(gè)為1，則該位的運(yùn)算結(jié)果為1，否則為0^按位異或，將參與運(yùn)算的兩個(gè)數(shù)據(jù)按對(duì)應(yīng)的二進(jìn)制數(shù)逐位進(jìn)行邏輯異或運(yùn)算。對(duì)應(yīng)的二進(jìn)制位不同時(shí)，對(duì)應(yīng)位的結(jié)果才為1。如果兩個(gè)對(duì)應(yīng)位都為0或者都為1，則對(duì)應(yīng)位的結(jié)果為0<<

按位左移，使指定的二進(jìn)制值的所有位都左移指定的位數(shù)。左移指定位數(shù)之后，左邊高位的數(shù)值將被移出并丟棄，右邊低位空出的位置用0補(bǔ)齊。語(yǔ)法格式為：表達(dá)式<<n，這里n指定值要移位的位數(shù)>>

按位右移，使指定的二進(jìn)制值的所有位都右移指定的位數(shù)。右移指定位數(shù)之后，右邊高位的數(shù)值將被移出并丟棄，左邊低位空出的位置用0補(bǔ)齊。語(yǔ)法格式為：表達(dá)式>>n，這里n指定值要移位的位數(shù)~按位取反，將參與運(yùn)算的數(shù)據(jù)按對(duì)應(yīng)的二進(jìn)制數(shù)逐位反轉(zhuǎn)，即1取反后變0，0取反后變?yōu)?任務(wù)3運(yùn)算符與表達(dá)式5.賦值運(yùn)算符賦值運(yùn)算符是一個(gè)較為特殊的算符，可以表示賦值，也可以用于比較數(shù)據(jù)是否相等。為確切表達(dá)其意義，用于賦值運(yùn)算的符號(hào)為“:=”，在MySQL的insert…set和update…set語(yǔ)句中出現(xiàn)的運(yùn)算符“=”也認(rèn)為是賦值運(yùn)算符。賦值運(yùn)算符優(yōu)先級(jí)最低。任務(wù)3運(yùn)算符與表達(dá)式6.運(yùn)算符的優(yōu)先級(jí)運(yùn)算符的優(yōu)先級(jí)決定了不同的運(yùn)算符在表達(dá)式中計(jì)算的先后順序。表9-9列出了MySQL中的各類運(yùn)算符及其優(yōu)先級(jí)。表9-9優(yōu)先級(jí)（由高到低）運(yùn)算符及功能描述1()括號(hào)2-（一元，負(fù)號(hào)）、~（一元，按位取反）3^按位異或4*（乘運(yùn)算）、/（除運(yùn)算）、%（取余運(yùn)算）5-（減運(yùn)算）、+（加運(yùn)算）6<<（按位左移）、>>（按位右移）7&邏輯與8|邏輯或9=（等于）、<=>（可進(jìn)行NULL值比較的相等運(yùn)算符）、>=（大于等于）、>（大于）、<=（小于等于）、<（小于）、<>、!=（不等于）10NOT邏輯非11AND、&&邏輯與12XOR邏輯異或13:=賦值運(yùn)算任務(wù)3運(yùn)算符與表達(dá)式9.3.2表達(dá)式在MySQL中，表達(dá)式是一段邏輯代碼的表達(dá)，通常會(huì)涉及到常量、變量、運(yùn)算符和函數(shù)等，最常見(jiàn)的表達(dá)式有如下幾種類型。1.一般表達(dá)式（1）常量或常數(shù)表達(dá)式一個(gè)常量就是一個(gè)最簡(jiǎn)的表達(dá)式，如數(shù)值常量?0?和字符串常量‘a(chǎn)bc’，分別表達(dá)某個(gè)具體數(shù)據(jù)，另外作為函數(shù)表達(dá)式中的參數(shù)或是select命令的輸出。例如，select?1,'hello',sqrt(4);（2）變量定義表達(dá)式Declare變量名數(shù)據(jù)類型;任務(wù)3運(yùn)算符與表達(dá)式2.賦值語(yǔ)句表達(dá)式（1）set賦值表達(dá)式例如，set@xh='20220001';（2）select賦值表達(dá)式例如，select@xm:=姓名fromxswhere學(xué)號(hào)='001101';例如，selectconcat(‘my’,’sql’)--concat()為字符信息拼接函數(shù)任務(wù)3運(yùn)算符與表達(dá)式3.條件表達(dá)式（1）在if語(yǔ)句中的表達(dá)式，如if(條件表達(dá)式，表達(dá)式1，表達(dá)式2)。（2）模糊匹配表達(dá)式，如Like或notlike語(yǔ)句表達(dá)式。（3）CASE語(yǔ)句表達(dá)式

任務(wù)4函數(shù)9.4.1內(nèi)部函數(shù)函數(shù)是在數(shù)據(jù)庫(kù)中定義一些SQL語(yǔ)句的集合，主要用于計(jì)算并返回一個(gè)值。函數(shù)可以用來(lái)對(duì)數(shù)據(jù)表中的數(shù)據(jù)進(jìn)行相應(yīng)的處理，以便得到用戶希望得到的數(shù)據(jù)。在MySQL中，函數(shù)分為系統(tǒng)提供的內(nèi)部函數(shù)和用戶自定義函數(shù)兩大類。MySQL提供的內(nèi)部函數(shù)就像預(yù)定的公式一樣存放在數(shù)據(jù)庫(kù)里，每個(gè)用戶都可以調(diào)用已經(jīng)存在的函數(shù)來(lái)完成某些特定的功能，簡(jiǎn)化用戶的操作。這些內(nèi)部函數(shù)主要有數(shù)學(xué)函數(shù)、字符串函數(shù)、日期和時(shí)間函數(shù)、數(shù)據(jù)類型轉(zhuǎn)換函數(shù)、條件判斷函數(shù)、系統(tǒng)信息函數(shù)、加密函數(shù)和格式化函數(shù)等。SELECT、INSERT、UPDATE和DELETE語(yǔ)句及其子句（例如WHERE、ORDERBY、HAVING等）中都可以使用內(nèi)部函數(shù)。任務(wù)4函數(shù)1.數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)主要用于處理數(shù)字。常用的數(shù)學(xué)函數(shù)及其功能如表9-10所示。表9-10函數(shù)名稱功能ABS(x)獲取x的絕對(duì)值MOD(x,y)求模運(yùn)算，與x%y的功能相同SQRT(x)求x的平方根POW(x,y)或POWER(x,y)冪運(yùn)算函數(shù)（計(jì)算x的y次方）EXP(x)計(jì)算e（自然對(duì)數(shù)的底約為2.71828）的x次方LOG(x)計(jì)算x的自然對(duì)數(shù)LOG10(x)計(jì)算以10為底的對(duì)數(shù)SIN(x)正弦函數(shù)ASIN(x)反正弦函數(shù)ROUND(x,[y])計(jì)算離x最近的整數(shù)；若設(shè)置參數(shù)y，與FORMAT(x,y)功能相同CEIL(x)或CEILING(x)返回大于等于x的最小整數(shù)FLOOR(x)返回小于等于x的最大整數(shù)TRUNCATE(x,y)返回小數(shù)點(diǎn)后保留y位的x（舍棄多余小數(shù)位，不進(jìn)行四舍五入）FORMAT(x,y)返回小數(shù)點(diǎn)后保留y位的x（進(jìn)行四舍五入）SIGN返回參數(shù)的符號(hào)RAND()默認(rèn)返回[0,1]之間的隨機(jī)數(shù)PI()計(jì)算圓周率ASCII(c)返回字符c的ASCII碼（ASCII碼介于0～255）

CHAR(c1,c2,c3,…)將c1、c2、c3、…的ASCII碼轉(zhuǎn)換為字符，然后返回這些字符組成的字符串BIN(x)返回x的二進(jìn)制數(shù)CONV(x,code1,code2)將code1進(jìn)制的x變?yōu)閏ode2進(jìn)制數(shù)任務(wù)4函數(shù)2.數(shù)據(jù)類型轉(zhuǎn)換函數(shù)數(shù)據(jù)庫(kù)管理和操作經(jīng)常需要對(duì)指定的數(shù)據(jù)類型轉(zhuǎn)換后才能獲取到想要的結(jié)果。常用的數(shù)據(jù)類型轉(zhuǎn)換函數(shù)及其功能如表9-11所示。表9-11函數(shù)名稱功能CONVERT(x,type)以type類型返回x，x可以是任何類型的表達(dá)式CONVERT(x

USING字符集)以指定字符集返回x數(shù)據(jù)，x可以是任何類型的表達(dá)式CAST(x

AS

type)以type類型返回x

，x可以是任何類型的表達(dá)式UNHEX(x)將x轉(zhuǎn)為十六進(jìn)制數(shù)字，然后再轉(zhuǎn)為由數(shù)字表示的字符任務(wù)4函數(shù)3.字符串函數(shù)字符串函數(shù)主要用于處理字符串。常用的字符串函數(shù)及其功能如表9-12所示。表9-12函數(shù)名稱功能LENGTH計(jì)算字符串長(zhǎng)度函數(shù)，返回字符串的字節(jié)長(zhǎng)度CONCAT合并字符串函數(shù)，返回結(jié)果為連接參數(shù)產(chǎn)生的字符串，參數(shù)可以使一個(gè)或多個(gè)INSERT替換字符串函數(shù)LOWER將字符串中的字母轉(zhuǎn)換為小寫(xiě)UPPER將字符串中的字母轉(zhuǎn)換為大寫(xiě)LEFT從左側(cè)字截取符串，返回字符串左邊的若干個(gè)字符RIGHT從右側(cè)字截取符串，返回字符串右邊的若干個(gè)字符TRIM刪除字符串左右兩側(cè)的空格REPLACE字符串替換函數(shù)，返回替換后的新字符串SUBSTRING截取字符串，返回從指定位置開(kāi)始的指定長(zhǎng)度的字符換REVERSE字符串反轉(zhuǎn)（逆序）函數(shù)，返回與原始字符串順序相反的字符串STRCMP比較兩個(gè)字符串的大小INSTR返回子串在一個(gè)字符串中第一次出現(xiàn)的位置。與LOCATE()和POSITION(…IN…)函數(shù)等價(jià)，但參數(shù)順序不同。REPEAT重復(fù)指定次數(shù)的字符串，并保存到一個(gè)新字符串中SPACE重復(fù)指定次數(shù)的空格，并保存到一個(gè)新字符串中任務(wù)4函數(shù)4.日期和時(shí)間函數(shù)日期和時(shí)間函數(shù)主要用于處理日期和時(shí)間。常用的日期和時(shí)間函數(shù)及其功能如表9-13所示。表9-13函數(shù)名稱功能CURDATE和CURRENT_DATE兩個(gè)函數(shù)作用相同，返回當(dāng)前系統(tǒng)的日期值CURTIME和CURRENT_TIME兩個(gè)函數(shù)作用相同，返回當(dāng)前系統(tǒng)的時(shí)間值NOW

和SYSDATE兩個(gè)函數(shù)作用相同，返回當(dāng)前系統(tǒng)的日期和時(shí)間值UNIX_TIMESTAMP獲取UNIX時(shí)間戳函數(shù)，返回一個(gè)以UNIX時(shí)間戳為基礎(chǔ)的無(wú)符號(hào)整數(shù)FROM_UNIXTIME將UNIX時(shí)間戳轉(zhuǎn)換為時(shí)間格式，與UNIX_TIMESTAMP互為反函數(shù)YEAR獲取年份，返回值范圍是1970?2069MONTH獲取指定日期中的月份WEEK獲取指定日期是一年中的第幾周，返回值的范圍是否為0?52或1?53DATE_FORMAT格式化指定的日期，根據(jù)參數(shù)返回指定格式的值WEEKDAY獲取指定日期在一周內(nèi)的對(duì)應(yīng)的工作日索引MONTHNAME獲取指定日期中的月份英文名稱DAYNAME獲取指定曰期對(duì)應(yīng)的星期幾的英文名稱DAYOFWEEK獲取指定日期對(duì)應(yīng)的一周的索引位置值DAYOFYEAR獲取指定曰期是一年中的第幾天，返回值范圍是1~366DAYOFMONTH獲取指定日期是一個(gè)月中是第幾天，返回值范圍是1~31TIME_TO_SEC將時(shí)間參數(shù)轉(zhuǎn)換為秒數(shù)SEC_TO_TIME將秒數(shù)轉(zhuǎn)換為時(shí)間，與TIME_TO_SEC互為反函數(shù)DATE_ADD和ADDDATE兩個(gè)函數(shù)功能相同，都是向日期添加指定的時(shí)間間隔DATE_SUB和SUBDATE兩個(gè)函數(shù)功能相同，都是向日期減去指定的時(shí)間間隔ADDTIME時(shí)間加法運(yùn)算，在原始時(shí)間上添加指定的時(shí)間SUBTIME時(shí)間減法運(yùn)算，在原始時(shí)間上減去指定的時(shí)間DATEDIFF獲取兩個(gè)日期之間間隔，返回參數(shù)1減去參數(shù)2的值任務(wù)4函數(shù)5.其他常用函數(shù)系統(tǒng)信息函數(shù)主要用于獲取MySQL數(shù)據(jù)庫(kù)的系統(tǒng)信息。加密函數(shù)主要用于對(duì)字符串進(jìn)行加密解密。條件判斷函數(shù)主要用于在SQL語(yǔ)句中控制條件選表9-14函數(shù)名稱功能VERSION()用于獲取當(dāng)前MySQL服務(wù)實(shí)例使用的MySQL版本號(hào)DATABASE()用于獲取當(dāng)前操作的數(shù)據(jù)庫(kù)，與SCHEMA()函數(shù)等價(jià)USER()用于獲取登錄服務(wù)器的主機(jī)地址及用戶名，與SYSTEM_USER()和SESSION_USER()函數(shù)等價(jià)CURRENT_USER()用于獲取該賬戶名允許通過(guò)哪些登錄主機(jī)連接MySQL服務(wù)器CONNECTION_ID()用于獲取當(dāng)前MySQL服務(wù)器的連接IDMD5()使用MD5計(jì)算并返回一個(gè)32位的字符串AES_ENCRYPT()使用密鑰對(duì)字符串進(jìn)行加密，默認(rèn)返回一個(gè)128位的二進(jìn)制數(shù)AES_DECRYPT()使用密鑰對(duì)密碼進(jìn)行解密SHA1()或SHA()利用安全散列算法SHA-1字符串，返回40個(gè)十六進(jìn)制數(shù)字組成的字符串SHA2()利用安全散列算法SHA-2字符串

ENCODE()使用密鑰對(duì)字符串進(jìn)行編碼，默認(rèn)返回一個(gè)二進(jìn)制數(shù)DECODE()使用密鑰對(duì)密碼進(jìn)行解碼PASSWORD()計(jì)算并返回一個(gè)41位的密碼字符串

任務(wù)4函數(shù)【例題9.1】獲取系統(tǒng)當(dāng)前日期時(shí)間、MySQL版本號(hào)、連接數(shù)和數(shù)據(jù)庫(kù)名。mysql>SELECTCURDATE(),VERSION(),CONNECTION_ID(),DATABASE();任務(wù)4函數(shù)9.4.2用戶自定義函數(shù)用戶可以根據(jù)需要編寫(xiě)自定義函數(shù)來(lái)實(shí)現(xiàn)某種特定的功能。使用自定義函數(shù)可以避免重復(fù)編寫(xiě)相同的SQL語(yǔ)句，提高代碼的重用性，減少客戶端和服務(wù)器的數(shù)據(jù)傳輸。1.創(chuàng)建用戶自定義函數(shù)在MySQL中，使用CREATEFUNCTION語(yǔ)句來(lái)創(chuàng)建自定義函數(shù)，其語(yǔ)法格式如下。CREATEFUNCTION函數(shù)名(參數(shù)名數(shù)據(jù)類型,…)RETURNS返回值類型

[BEGIN]

函數(shù)體

RETURN返回值數(shù)據(jù);

[END]

任務(wù)4函數(shù)說(shuō)明：（1）創(chuàng)建函數(shù)時(shí)，函數(shù)名不能與已經(jīng)存在的函數(shù)名重名。（2）參數(shù)部分可以由多個(gè)參數(shù)組成。不同于存儲(chǔ)過(guò)程，函數(shù)的參數(shù)類型只能是IN。（3）結(jié)構(gòu)中定義的返回值數(shù)據(jù)必須與定義的返回值類型一致，否則，返回值將被強(qiáng)制為恰當(dāng)?shù)念愋?。?）函數(shù)體可以用BEGIN...END來(lái)標(biāo)示SQL代碼的開(kāi)始和結(jié)束。任務(wù)4函數(shù)【例題9.2】在成績(jī)管理數(shù)據(jù)庫(kù)cjgl中，創(chuàng)建根據(jù)學(xué)生表xs中的某個(gè)學(xué)號(hào)查詢學(xué)生姓名的函數(shù)。執(zhí)行如下SQL語(yǔ)句。mysql>DELIMITER//mysql>CREATEFUNCTIONfunc_xsxm(idchar(6))->RETURNSCHAR(6)->COMMENT'查詢某個(gè)學(xué)生的姓名'->RETURN(SELECT姓名FROMxsWHERExs.學(xué)號(hào)=id);->//mysql>DELIMITER;代碼中的DELIMITER是MySQL分隔符，在MySQL客戶端中分隔符默認(rèn)是分號(hào)。如果一次輸入的語(yǔ)句較多，并且語(yǔ)句中間有分號(hào)，這時(shí)需要新指定一個(gè)特殊的分隔符（如//）告訴MySQL解釋器該段命令是否已經(jīng)結(jié)束。本例中，通過(guò)命令DELIMITER//將SQL語(yǔ)句的結(jié)束符由“;”修改為“//”，最后通過(guò)命令DELIMITER;將結(jié)束符號(hào)修改回默認(rèn)的結(jié)束符號(hào);。任務(wù)4函數(shù)2.調(diào)用用戶自定義函數(shù)在MySQL中，使用用戶自定義函數(shù)的方法與使用內(nèi)部函數(shù)的方法相同，只需要通過(guò)SELECT關(guān)鍵字并指定函數(shù)的名稱和參數(shù)即可。如，mysql>SELECTfunc_xsxm('001101');+---------------------+|func_xsxm('001101')|+----------------------------+|王金華|+----------------------------+1rowinset(0.00sec)任務(wù)4函數(shù)3.管理用戶自定義函數(shù)（1）可以用SHOWCREATEFUNCTION命令查看函數(shù)的定義。如，mysql>SHOWCREATEFUNCTIONfunc_xsxm\G

（2）可以用ALTERFUNCTION命令查看函數(shù)狀態(tài)及系統(tǒng)中所有自定義函數(shù)。如，mysql>SHOWFUNCTIONSTATUSLIKE'func_xsxm';

（3）可以用ALTERFUNCTION命令修改用戶自定義函數(shù)。

（4）可以用DROPFUNCTION命令刪除數(shù)據(jù)庫(kù)中函數(shù)。如，mysql>DROPFUNCTIONIFEXISTSfunc_xsxm;任務(wù)5流程控制9.5.1順序結(jié)構(gòu)在MySQL中，可以使用流程控制語(yǔ)句來(lái)控制程序的流程。常用的流程控制語(yǔ)句有：IF語(yǔ)句、CASE語(yǔ)句、LOOP語(yǔ)句、REPEAT語(yǔ)句和WHILE語(yǔ)句、LEAVE語(yǔ)句、ITERATE語(yǔ)句等。順序結(jié)構(gòu)是指程序的執(zhí)行順序無(wú)邏輯跳轉(zhuǎn)，依次按語(yǔ)句的先后執(zhí)行，即程序結(jié)構(gòu)中無(wú)分支、無(wú)循環(huán)語(yǔ)句順序執(zhí)行。例如，BEGINdeclarex1intdefaut1;declarex2char(2)default'男';...END;任務(wù)5流程控制9.5.2分支結(jié)構(gòu)及分支語(yǔ)句分支結(jié)構(gòu)是指程序根據(jù)是否滿足條件，來(lái)執(zhí)行不同的語(yǔ)句。在MySQL中，表達(dá)分支結(jié)構(gòu)的分支語(yǔ)句有如下2種形式。1.if語(yǔ)句這種語(yǔ)句適用于SQL語(yǔ)句中的條件判斷，其語(yǔ)法結(jié)構(gòu)如下。If(條件表達(dá)式，表達(dá)式1，表達(dá)式2)當(dāng)條件表達(dá)式值為真時(shí)，返回表達(dá)式1的值，否則返回表達(dá)式2的值。

任務(wù)5流程控制2.if...then語(yǔ)句這種語(yǔ)句適用于在函數(shù)、存儲(chǔ)過(guò)程等程序中實(shí)現(xiàn)復(fù)雜的SQL操作，其語(yǔ)法結(jié)構(gòu)如下。if條件式1then語(yǔ)句1elseif條件式2then語(yǔ)句2...else語(yǔ)句nendif當(dāng)條件式1為真時(shí)，就執(zhí)行對(duì)應(yīng)的語(yǔ)句1，否則，就繼續(xù)判斷條件式2的真假，為真則執(zhí)行語(yǔ)句2，如此完成所有的判斷后，若所有的條件式都為假，則執(zhí)行else后的語(yǔ)句n。

任務(wù)5流程控制3.case語(yǔ)句CASE語(yǔ)句提供了多個(gè)條件進(jìn)行選擇。它有兩種語(yǔ)法形式。(1)語(yǔ)法格式1CASE條件表達(dá)式WHEN表達(dá)式1THEN語(yǔ)句1[WHEN表達(dá)式2THEN語(yǔ)句2]...[ELSE語(yǔ)句n]ENDCASE任務(wù)5流程控制用case的條件表達(dá)式與when后的子句的表達(dá)式進(jìn)行比較，直到與其中的一個(gè)表達(dá)式相等，則執(zhí)行then后對(duì)應(yīng)的語(yǔ)句。（2）語(yǔ)法格式2CASEWHEN條件表達(dá)式1THEN語(yǔ)句1[WHEN條件表達(dá)式2THEN語(yǔ)句2]...[ELSE語(yǔ)句n]ENDCASE任務(wù)5流程控制與格式1不同的是，格式2中的WHEN語(yǔ)句將被逐個(gè)執(zhí)行，直到某個(gè)條件表達(dá)式為真，則執(zhí)行后面對(duì)應(yīng)的語(yǔ)句。如果沒(méi)有條件匹配，ELSE子句里的語(yǔ)句被執(zhí)行。【例題9.3】輸出成績(jī)的不同等級(jí)（優(yōu)、良、及格、不及格）。打開(kāi)MySQLWorkbench，在查詢窗口編輯如下語(yǔ)句并執(zhí)行，結(jié)果如圖9-1所示。SELECTxh,cj,(casewhencj>=90then'優(yōu)'whencj<90andcj>=80then'良'whencj<80andcj>=60then'及格'else'不及格'end)as'成績(jī)等級(jí)'FROMcj;任務(wù)5流程控制9.5.3循環(huán)結(jié)構(gòu)及循環(huán)語(yǔ)句循環(huán)結(jié)構(gòu)是程序中常見(jiàn)結(jié)構(gòu)之一，循環(huán)語(yǔ)句指的是符合指定條件的情況下，重復(fù)執(zhí)行一段代碼。在MySQL中實(shí)現(xiàn)循環(huán)結(jié)構(gòu)的語(yǔ)句有3種形式：LOOP、REPEAT和WHILE循環(huán)。任務(wù)5流程控制1.LOOP循環(huán)用于實(shí)現(xiàn)一個(gè)簡(jiǎn)單的循環(huán)操作，基本語(yǔ)法格式如下。[標(biāo)簽：]LOOP

語(yǔ)句列表Endloop[標(biāo)簽]【例題9.4】loop循環(huán)示例。任務(wù)5流程控制通過(guò)MySQL命令行客戶端執(zhí)行如下語(yǔ)句。DELIMITER//#更改語(yǔ)句結(jié)束標(biāo)志為//DROPPROCEDUREIFEXISTStest1;#如果存在test1存儲(chǔ)過(guò)程則刪除CREATEPROCEDUREtest1()#創(chuàng)建無(wú)參存儲(chǔ)過(guò)程test1BEGINDECLAREiINT;#申明變量SETi=0;#變量賦值

lp:LOOP

#lp為循環(huán)體名INSERTINTOtestVALUES(i+11,'test','20');

#向test表中添加數(shù)據(jù)SETi=i+1;#循環(huán)一次,i加一IFi>10THEN

#結(jié)束循環(huán)的條件:當(dāng)i大于10時(shí)跳出loop循環(huán)LEAVElp;ENDIF;ENDLOOP;SELECT*FROMtest;#查看test表數(shù)據(jù)END//CALLtest();#調(diào)用存儲(chǔ)過(guò)程

DELIMITER;#重新將分隔符設(shè)置為;任務(wù)5流程控制2.REPEAT循環(huán)通常用于實(shí)現(xiàn)一個(gè)提供REPEAT循環(huán)條件表達(dá)式符合執(zhí)行的操作，其語(yǔ)法格式如下。[標(biāo)簽：]REPEAT語(yǔ)句列表Until條件表達(dá)式EndREPEAT[標(biāo)簽];【例題9.5】repeat循環(huán)示例。主要代碼如下。SETi=0;REPEATINSERTINTOtestVALUES(i+11,'test','20');

#向test表中添加數(shù)據(jù)SETi=i+1;#循環(huán)一次，i加1UNTILi>10ENDREPEAT;#當(dāng)i大于10時(shí)跳出repeat任務(wù)5流程控制3.WHILE循環(huán)用于創(chuàng)建一個(gè)帶條件的循環(huán)過(guò)程，在執(zhí)行WHILE語(yǔ)句時(shí)，要先滿足條件表達(dá)式的要求，否則不會(huì)執(zhí)行對(duì)應(yīng)的循環(huán)操作語(yǔ)句，其語(yǔ)法格式如下。[標(biāo)簽：]WHILE條件表達(dá)式DO

語(yǔ)句列表EndWHILE[標(biāo)簽]【例題9.6】while循環(huán)示例。主要代碼如下。SETi=0;WHILEi<5DO#當(dāng)i大于5時(shí)跳出while循環(huán)INSERTINTOtestVALUES(i+11,'test','20');#向test表中添加數(shù)據(jù)SETi=i+1;#循環(huán)一次,i加1ENDWHILE;任務(wù)6游標(biāo)1.游標(biāo)的作用在使用SELECT語(yǔ)句時(shí)，雖然可以通過(guò)WHERE子句來(lái)限制只有一條記錄被選中，但沒(méi)有辦法對(duì)結(jié)果集中的記錄逐條單獨(dú)處理，這種情況下，我們就需要借助游標(biāo)機(jī)制來(lái)實(shí)現(xiàn)。游標(biāo)是一種數(shù)據(jù)訪問(wèn)機(jī)制，允許用戶訪問(wèn)包含多條數(shù)據(jù)記錄的結(jié)果集中的某一行，類似C語(yǔ)言中指針的功能。一般通過(guò)游標(biāo)定位到結(jié)果集的某一行記錄進(jìn)行瀏覽或修改。任務(wù)6游標(biāo)2．使用游標(biāo)（1）聲明游標(biāo)游標(biāo)必須先聲明再使用，聲明游標(biāo)的語(yǔ)法格式如下。DECLARE游標(biāo)名CURSORFORSELECT語(yǔ)句;SELECT語(yǔ)句可以根據(jù)需要添加WHERE和其它子句，返回一行或多行數(shù)