《MySQL數(shù)據(jù)庫應(yīng)用案例教程》教學(xué)課件第10章MySQL常用函數(shù)

1、10MySQL常用函數(shù)第章第1頁，共69頁。10.1 數(shù)值函數(shù)第2頁，共69頁。數(shù)值函數(shù)是MySQL中一種很重要的函數(shù)，主要用于處理數(shù)值方面的運算。如果沒有這些函數(shù)，用戶在編寫有關(guān)數(shù)值運算方面的代碼時將會復(fù)雜很多。例如，如果沒有ABS求絕對值函數(shù)，要取一個數(shù)的絕對值，就需要進(jìn)行多次判斷，直接使用該函數(shù)可以大大提高用戶的工作效率。表10-1 MySQL中常用的數(shù)值函數(shù)及其功能函 數(shù)功 能ABS(x)返回數(shù)值x的絕對值MOD(x,y)返回數(shù)值x除以數(shù)值y后的余數(shù)CEIL(x)返回大于數(shù)值x的最小整數(shù)值FLOOR(x)返回小于數(shù)值x的最大整數(shù)值RAND()返回01內(nèi)的隨機數(shù)ROUND(x)返回對參數(shù)

2、x進(jìn)行四舍五入后的值，ROUND(x)返回整數(shù)值，ROUND(x,y)返回參數(shù)x四舍五入后保留y位小數(shù)的值TRUNCATE(x,y)對數(shù)值x進(jìn)行截取，保留小數(shù)點后y位數(shù)字第3頁，共69頁?！緦嵗?0-1】執(zhí)行SQL語句，求5，-5和-5.5的絕對值，執(zhí)行結(jié)果如下：mysql SELECT ABS(5),ABS(-5),ABS(-5.5);+-+-+-+| ABS(5)| ABS(-5)| ABS(-5.5) |+-+-+-+| 5| 5| 5.5 |+-+-+-+1 row in set (0.03 sec)函數(shù)ABS(x)的返回值是數(shù)值x的絕對值。正數(shù)的絕對值是其本身，負(fù)數(shù)的絕對值是其相反數(shù)

3、。10.1.1 求絕對值函數(shù)第4頁，共69頁。函數(shù)MOD(x,y)的返回值是數(shù)值x除以數(shù)值y后的余數(shù)。與x%y的結(jié)果相同，除數(shù)和被除數(shù)任何一個為NULL，返回結(jié)果都將為NULL；除數(shù)為0將是非法運算，返回結(jié)果為NULL。mysql SELECT MOD(6,4),MOD(6,-4), MOD(NULL,6), MOD(6,0),MOD(0,2.5);+-+-+-+-+-+| MOD(6,4) | MOD(6,-4) | MOD(NULL,6)| MOD(6,0) | MOD(0,2.5) |+-+-+-+-+-+| 2 | 2 | NULL| NULL | 0.0 |+-+-+-+-+-+1

4、row in set (0.00 sec)【實例10-2】執(zhí)行SQL語句，求6除以4，6除以-4，NULL除以6，6除以0，及0除以2.5的余數(shù)，執(zhí)行結(jié)果如下：10.1.2 求余函數(shù)第5頁，共69頁。mysql SELECT CEIL(2.45),CEIL(-2.45);+-+-+| CEIL(2.45) | CEIL(-2.45)|+-+-+| 3 | -2|+-+-+1 row in set (0.00 sec)【實例10-3】執(zhí)行SQL語句，分別求大于數(shù)值2.45和-2.45的最小整數(shù)值，執(zhí)行結(jié)果如下：10.1.3 用于獲取整數(shù)的函數(shù)CEIL(x)1MySQL中用于獲取整數(shù)的函數(shù)主要有C

5、EIL(x)和FLOOR(x)，下面分別介紹。函數(shù)CEIL(x)的返回值是大于數(shù)值x的最小整數(shù)值，下面通過實例介紹。第6頁，共69頁。10.1.3 用于獲取整數(shù)的函數(shù)mysql SELECT FLOOR(2.45),FLOOR(-2.45);+-+-+| FLOOR(2.45) | FLOOR(-2.45) |+-+-+| 2 | -3 |+-+-+1 row in set (0.03 sec)【實例10-4】執(zhí)行SQL語句，求小于數(shù)值2.45和-2.45的最大整數(shù)值，執(zhí)行結(jié)果如下：FLOOR(x)2函數(shù)FLOOR(x)的返回值是小于數(shù)值x的最大整數(shù)值，下面通過實例介紹。第7頁，共69頁。函數(shù)

6、RAND()的返回值是01內(nèi)的小數(shù)，并且每次的運行結(jié)果都不同，下面通過實例介紹。mysql SELECT RAND(),RAND(),RAND();+-+-+-+| RAND() | RAND() | RAND() |+-+-+-+| 0.32945327863977597 | 0.017887058684497196 | 0.10107548823680308|+-+-+-+1 row in set (0.04 sec)【實例10-5】執(zhí)行SQL語句，使用函數(shù)RAND()獲取隨機數(shù)，執(zhí)行結(jié)果如下：10.1.4 獲取隨機數(shù)的函數(shù)第8頁，共69頁。函數(shù)ROUND()的作用是對數(shù)值執(zhí)行四舍五入操作

7、，當(dāng)函數(shù)格式為ROUND(x)時，返回值為整數(shù)；當(dāng)函數(shù)格式為ROUND(x,y)時，對數(shù)值x進(jìn)行四舍五入并保留小數(shù)點后y位，下面通過實例進(jìn)行介紹。mysql SELECT ROUND(100.144),ROUND(100.568),ROUND(100.144,2),ROUND(100.568,2);+-+-+-+-+| ROUND(100.144)| ROUND(100.568)| ROUND(100.144,2)| ROUND(100.568,2)|+-+-+-+-+| 100| 101| 100.14 | 100.57 |+-+-+-+-+1 row in set (0.03 sec)【實

8、例10-6】執(zhí)行SQL語句，使用函數(shù)ROUND(x)和ROUND(x,y)對數(shù)值進(jìn)行四舍五入操作，執(zhí)行結(jié)果如下：10.1.5 四舍五入函數(shù)第9頁，共69頁。函數(shù)TRUNCATE(x,y)的作用是對數(shù)值x進(jìn)行截取，保留小數(shù)點后y位。其與ROUND()函數(shù)的區(qū)別是，ROUND()函數(shù)在截取值時會四舍五入；而TRUNCATE(x,y)函數(shù)直接截取值，并不進(jìn)行四舍五入。mysql SELECT TRUNCATE(1.42,1),TRUNCATE(1.58,1),ROUND(1.58,1);+-+-+-+| TRUNCATE(1.42,1)| TRUNCATE(1.58,1)| ROUND(1.58,1

9、)|+-+-+-+| 1.4| 1.5| 1.6|+-+-+-+1 row in set (0.03 sec)【實例10-7】執(zhí)行SQL語句，使用函數(shù)TRUNCATE(x,y)和ROUND(x,y)分別截取數(shù)值，執(zhí)行結(jié)果如下：10.1.6 截取小數(shù)函數(shù)第10頁，共69頁。10.2字符串函數(shù)第11頁，共69頁。字符串函數(shù)是MySQL中使用最頻繁的函數(shù)，主要用于處理數(shù)據(jù)庫中字符串類型的數(shù)據(jù)。表10-2列出了MySQL中常用的字符串函數(shù)及其功能。表10-2 MySQL中常用的字符串函數(shù)及其功能函 數(shù)功 能LENGTH(str)，CHAR_LENGTH(str)返回字符串長度或字符個數(shù)CONCAT(s

10、tr1,str2strn)，CONCAT_WS(x,str1,str2strn)合并字符串INSERT(str,x,y,instr)，REPLACE(str,a,b)替換字符串LOWER(str)，UPPER(str)字符大小寫轉(zhuǎn)換LEFT(str,x)，RIGHT(str,x)，SUBSTRING(str,x,y)獲取字符串的一部分LPAD(str1,n,str2)，RPAD(str1,n,str2)填充字符串LTRIM(str)，RTRIM(str)，TRIM(str)刪除字符串左側(cè)、右側(cè)或兩側(cè)空格REPEAT(str,n)返回字符串str重復(fù)n次的結(jié)果LOCATE(str1,str)返回

11、子字符串的開始位置REVERSE(str)反轉(zhuǎn)字符串第12頁，共69頁。10.2.1 返回字符串長度和字符串中字符個數(shù)的函數(shù)函數(shù)LENGTH(str)用于返回字符串的長度，一個漢字占用2個字節(jié)，一個英文字符和數(shù)字占用1個字節(jié)。mysql SELECT LENGTH(abcdef),LENGTH(字符長度);+-+-+| LENGTH(abcdef) | LENGTH(字符長度)|+-+-+| 6 | 8|+-+-+1 row in set (0.00 sec)【實例10-8】執(zhí)行SQL語句，使用函數(shù)LENGTH(str)返回字符串長度，執(zhí)行結(jié)果如下：第13頁，共69頁。10.2.1 返回字符串

12、長度和字符串中字符個數(shù)的函數(shù)函數(shù)CHAR_LENGTH(str)用于返回字符串中的字符個數(shù)。mysql SELECT CHAR_LENGTH(abcdef),CHAR_LENGTH(字符個數(shù));+-+-+| CHAR_LENGTH(abcdef) | CHAR_LENGTH(字符個數(shù))|+-+-+| 6 | 4 |+-+-+1 row in set (0.00 sec)【實例10-9】執(zhí)行SQL語句，使用函數(shù)CHAR_LENGTH(str)返回字符串中的字符個數(shù)，執(zhí)行結(jié)果如下：第14頁，共69頁。10.2.2 合并字符串的函數(shù)函數(shù)CONCAT(str1,str2strn)可以將多個字符串拼接成

13、為一個字符串，但如果參數(shù)中有一個NULL值，則返回結(jié)果都將為NULL。mysql SELECT CONCAT(abcd,efg),CONCAT(abcd,NULL,efg);+-+-+| CONCAT(abcd,efg)| CONCAT(abcd,NULL,efg)|+-+-+| abcdefg | NULL |+-+-+1 row in set (0.02 sec)【實例10-10】執(zhí)行SQL語句，使用函數(shù)CONCAT(str1,str2strn)拼接字符串，執(zhí)行結(jié)果如下：第15頁，共69頁。10.2.2 合并字符串的函數(shù)函數(shù)CONCAT_WS(x,str1,str2strn)是函數(shù)CONC

14、AT(str1,str2strn)的特殊形式，作用是以第一個參數(shù)為分隔符，連接后面的多個字符串。mysql SELECT CONCAT_WS(_,ab,cd,ef),CONCAT_WS(_,gh,NULL,ij);+-+-+| CONCAT_WS(_,ab,cd,ef) | CONCAT_WS(_,gh,NULL,ij) |+-+-+| ab_cd_ef | gh_ij |+-+-+1 row in set (0.00 sec)【實例10-11】執(zhí)行SQL語句，使用函數(shù)CONCAT_WS(x,str1,str2strn)拼接字符串，執(zhí)行結(jié)果如下： 提示由執(zhí)行結(jié)果可以看出，函數(shù)CONCAT_WS

15、(x,str1,str2strn)會忽略分隔符后的NULL值，但如果分隔符為NULL，則返回結(jié)果為NULL。第16頁，共69頁。10.2.3 替換字符串的函數(shù)函數(shù)INSERT(str,x,y,instr)的作用是將字符串str從第x位置開始，y個字符長的子串替換為字符串instr。mysql SELECT INSERT(beijinglvyoushichang,13,8,gonglue);+-+| INSERT(beijinglvyoushichang,13,8,gonglue)|+-+| beijinglvyougonglue |+-+1 row in set (0.00 sec)【實例10

16、-12】執(zhí)行SQL語句，使用函數(shù)INSERT(str,x,y,instr)把字符串“beijinglvyoushichang”從第13個字符開始后面的8個字符替換為字符串“gonglue”，執(zhí)行結(jié)果如下：第17頁，共69頁。10.2.3 替換字符串的函數(shù)函數(shù)REPLACE(str,a,b)也可以替換字符串，作用是使用字符串b替換字符串str中的子串a(chǎn)。mysql SELECT REPLACE(abcabc,abc,you);+-+| REPLACE(abcabc,abc,you)|+-+| youyou |+-+1 row in set (0.00 sec)【實例10-13】執(zhí)行SQL語句，使

17、用函數(shù)REPLACE(str,a,b)把字符串“abcabc”中的子串“abc”替換為字符串“you”，執(zhí)行結(jié)果如下：第18頁，共69頁。10.2.4 字母大小寫轉(zhuǎn)換函數(shù)函數(shù)LOWER(str)用于將字符串str中的字母全部轉(zhuǎn)換為小寫字母，函數(shù)UPPER(str)用于將字符串str中的字母全部轉(zhuǎn)換為大寫字母。mysql SELECT LOWER(aBcD),UPPER(aBcD);+-+-+| LOWER(aBcD)| UPPER(aBcD)|+-+-+| abcd | ABCD |+-+-+1 row in set (0.07 sec)【實例10-14】執(zhí)行SQL語句，分別使用函數(shù)LOWER

18、(str)和UPPER(str)把字符串“aBcD”轉(zhuǎn)換為小寫字母和大寫字母，執(zhí)行結(jié)果如下：第19頁，共69頁。10.2.5 獲取指定長度字符串的函數(shù)函數(shù)LEFT(str,x)用于獲取字符串str中最左邊的x個字符，函數(shù)RIGHT(str,x)用于獲取字符串str中最右邊的x個字符。mysql SELECT LEFT(beijinglvyougonglue,7),RIGHT(beijinglvyougonglue,7);+-+-+| LEFT(beijinglvyougonglue,7)| RIGHT(beijinglvyougonglue,7)|+-+-+| beijing | gonglu

19、e |+-+-+1 row in set (0.02 sec)【實例10-15】執(zhí)行SQL語句，分別使用函數(shù)LEFT(str,x)和RIGHT(str,x)獲取字符串“beijinglvyougonglue”左邊和右邊的7個字符，執(zhí)行結(jié)果如下：第20頁，共69頁。10.2.5 獲取指定長度字符串的函數(shù)函數(shù)SUBSTRING(str,x,y)用于獲取字符串str中從x位置開始，后面y個字符長度的子串。該函數(shù)常用于在給定字符串中提取子串。mysql SELECT SUBSTRING(beijinglvyougonglue,8,5);+-+| SUBSTRING(beijinglvyougonglu

20、e,8,5)|+-+| lvyou |+-+1 row in set (0.00 sec)【實例10-16】執(zhí)行SQL語句，使用函數(shù)SUBSTRING(str,x,y)獲取字符串“beijinglvyougonglue”從第8個字符開始，后面的5個字符，執(zhí)行結(jié)果如下：第21頁，共69頁。10.2.6 填充字符串的函數(shù)函數(shù)LPAD(str1,n,str2)的作用是使用字符串str2對字符串str1最左邊進(jìn)行填充，直到字符串str1總長度達(dá)到n個字符長度。如果str1的字符長度大于或等于n，則不填充。mysql SELECT LPAD(abcdefg,5,km),LPAD(abcdefg,10,k

21、m);+-+-+| LPAD(abcdefg,5,km)| LPAD(abcdefg,10,km)|+-+-+| abcde | kmkabcdefg |+-+-+1 row in set (0.00 sec)【實例10-17】執(zhí)行SQL語句，使用字符串km對字符串“abcdefg”最左邊進(jìn)行填充，直到字符串達(dá)到5個或10個字符長度。執(zhí)行結(jié)果如下：第22頁，共69頁。10.2.6 填充字符串的函數(shù)函數(shù)RPAD(str1,n,str2)的作用是使用字符串str2對字符串str1最右邊進(jìn)行填充，直到字符串str1總長度達(dá)到n個字符長度。mysql SELECT RPAD(abcdefg,5,km)

22、,RPAD(abcdefg,10,km);+-+-+| RPAD(abcdefg,5,km)| RPAD(abcdefg,10,km)|+-+-+| abcde | abcdefgkmk |+-+-+1 row in set (0.02 sec)【實例10-18】執(zhí)行SQL語句，使用字符串km對字符串“abcdefg”最右邊進(jìn)行填充，直到字符串達(dá)到5個或10個字符長度。執(zhí)行結(jié)果如下：第23頁，共69頁。10.2.7 刪除字符串中空格的函數(shù)函數(shù)LTRIM(str)用于刪除字符串左側(cè)的空格字符，函數(shù)RTRIM(str)用于刪除字符串右側(cè)的空格字符。mysql SELECT CONCAT(ab, c

23、d ,ef) AS str1, - CONCAT(ab,LTRIM( cd ),ef) AS str2, - CONCAT(ab, cd ,ef) AS str3, - CONCAT(ab,RTRIM( cd ),ef) AS str4;+-+-+-+-+| str1 | str2 | str3 | str4 |+-+-+-+-+| ab cd ef| abcd ef| ab cd ef| ab cdef|+-+-+-+-+1 row in set (0.03 sec)【實例10-19】執(zhí)行SQL語句，驗證函數(shù)LTRIM(str)和RTRIM(str)的應(yīng)用。執(zhí)行結(jié)果如下： 提示上述語句中，字

24、符串“cd”兩側(cè)各有一個空格。在語句CONCAT(ab,LTRIM( cd ),ef) AS str2中，函數(shù)LTRIM( cd )將cd左側(cè)的空格刪除了，所以語句執(zhí)行結(jié)果為abcd ef；在語句CONCAT(ab,RTRIM( cd ),ef) AS str4中，函數(shù)RTRIM( cd )將cd右側(cè)的空格刪除了，所以語句執(zhí)行結(jié)果為ab cdef。第24頁，共69頁。10.2.7 刪除字符串中空格的函數(shù)函數(shù)TRIM(str)用于刪除字符串開頭和結(jié)尾的空格，另外，它還可以刪除字符串兩側(cè)的指定字符。mysql SELECT CONCAT(ab, cd ,ef) AS str1, - CONCAT(

25、ab,TRIM( cd ),ef) AS str2, - TRIM(a from aabacaa) AS str3;+-+-+-+| str1 | str2 | str3 |+-+-+-+| ab cd ef| abcdef| bac |+-+-+-+1 row in set (0.00 sec)【實例10-20】執(zhí)行SQL語句，驗證函數(shù)TRIM(str)的應(yīng)用。執(zhí)行結(jié)果如下：可以看到，在語句CONCAT(ab,TRIM( cd ),ef) AS str2中，函數(shù)TRIM( cd )將cd兩側(cè)的空格全部刪除了，所以合并字符串的結(jié)果為abcdef；在語句TRIM(a from aabacaa)

26、AS str3中，函數(shù)TRIM(a from aabacaa)將字符串a(chǎn)abacaa兩側(cè)的指定字符a全部刪除了。第25頁，共69頁。10.2.8 重復(fù)生成字符串的函數(shù)函數(shù)REPEAT(str,n)返回字符串str重復(fù)n次的結(jié)果。mysql SELECT REPEAT(abc ,3);+-+| REPEAT(abc ,3) |+-+| abc abc abc |+-+1 row in set (0.01 sec)【實例10-21】執(zhí)行SQL語句，驗證函數(shù)REPEAT(str,n)的應(yīng)用。執(zhí)行結(jié)果如下： 提示此處c后面有一個空格。第26頁，共69頁。10.2.9 獲取字符串中子串開始位置的函數(shù)函數(shù)

27、LOCATE(str1,str)返回子串str1在字符串str中的開始位置，返回值的最小值為1，如果字符串str中不包含字符串str1，則返回0。mysql SELECT LOCATE(abc,ababcabd),LOCATE(efg,ababcabd);+-+-+| LOCATE(abc,ababcabd) | LOCATE(efg,ababcabd)|+-+-+| 3 | 0|+-+-+1 row in set (0.04 sec)【實例10-22】執(zhí)行SQL語句，驗證函數(shù)LOCATE(str1,str)的應(yīng)用。執(zhí)行結(jié)果如下：第27頁，共69頁。10.2.10 反轉(zhuǎn)字符串的函數(shù)函數(shù)REVE

28、RSE(str)返回將字符串str中字符倒序排列后的結(jié)果。mysql SELECT REVERSE(abcdefg);+-+| REVERSE(abcdefg) |+-+| gfedcba |+-+1 row in set (0.00 sec)【實例10-23】執(zhí)行SQL語句，驗證函數(shù)REVERSE(str)的應(yīng)用。執(zhí)行結(jié)果如下：第28頁，共69頁。10.3日期與時間函數(shù)第29頁，共69頁。在實際應(yīng)用中，有時可能會遇到這樣的需求：獲取當(dāng)前時間，或者下個月的今天是星期幾，等等類似的問題。這些需求就需要使用日期與時間函數(shù)來實現(xiàn)。表10-3列出了MySQL中常用的日期與時間函數(shù)及其功能。表10-3

29、MySQL中常用的日期與時間函數(shù)及其功能函 數(shù)功 能CURDATE()獲取當(dāng)前日期CURTIME()獲取當(dāng)前時間NOW()獲取當(dāng)前的日期和時間UNIX_TIMESTAMP(date)獲取日期date的UNIX時間戳YEAR(d)，MONTH(d)，WEEK(d)，DAY(d)，HOUR(d)，MINUTE(d)，SECOND(d)返回指定日期的年份、月份、星期、日、時、分和秒DATE_FORMAT(d,format)按format指定的格式顯示日期d的值A(chǔ)DDDATE(date,INTERVAL expr unit)，SUBDATE(date,INTERVAL expr unit)獲取一個日期

30、或時間值加上一個時間間隔的時間值TIME_TO_SEC(d),SEC_TO_TIME(d)獲取將“HH:MM:SS”格式的時間換算為秒，或?qū)⒚霐?shù)換算為“HH:MM:SS”格式的值第30頁，共69頁。10.3.1 獲取當(dāng)前日期的函數(shù)函數(shù)CURDATE()返回包含年月日的當(dāng)前日期。mysql SELECT CURDATE();+-+| CURDATE()|+-+| 2018-05-10|+-+1 row in set (0.04 sec)【實例10-24】執(zhí)行SQL語句，使用函數(shù)CURDATE()獲取當(dāng)前日期。執(zhí)行結(jié)果如下：第31頁，共69頁。10.3.2 獲取當(dāng)前時間的函數(shù)函數(shù)CURTIME()

31、返回“HH:MM:SS”格式的當(dāng)前時間。mysql SELECT CURTIME();+-+| CURTIME()|+-+| 15:54:13 |+-+1 row in set (0.00 sec)【實例10-25】執(zhí)行SQL語句，使用函數(shù)CURTIME()獲取當(dāng)前時間。執(zhí)行結(jié)果如下：第32頁，共69頁。10.3.3 獲取當(dāng)前日期和時間的函數(shù)函數(shù)NOW()返回當(dāng)前日期和時間（同時包含年月日和時分秒）。mysql SELECT NOW();+-+| NOW() |+-+| 2018-05-10 16:03:56|+-+1 row in set (0.00 sec)【實例10-26】執(zhí)行SQL語句

32、，使用函數(shù)NOW()獲取當(dāng)前日期和時間。執(zhí)行結(jié)果如下：第33頁，共69頁。10.3.4 獲取UNIX時間戳函數(shù)UNIX時間戳是從1970年1月1日（UTC/GMT的午夜）開始到當(dāng)前時間所經(jīng)過的秒數(shù)（不考慮閏秒）。一分鐘表示為UNIX時間戳為60秒，一小時表示為UNIX時間戳為3600秒，一天表示為UNIX時間戳為86400秒。函數(shù)UNIX_TIMESTAMP(date)返回日期date的UNIX時間戳。mysql SELECT NOW(),UNIX_TIMESTAMP(NOW();+-+-+| NOW() | UNIX_TIMESTAMP(NOW()|+-+-+| 2018-05-10 16:

33、39:20| 1525941560|+-+-+1 row in set (0.00 sec)【實例10-27】執(zhí)行SQL語句，使用函數(shù)UNIX_TIMESTAMP(date)獲取UNIX格式的當(dāng)前日期和時間。執(zhí)行結(jié)果如下：第34頁，共69頁。10.3.5 獲取年份、月份、星期、日、時、分和秒的函數(shù)MySQL提供多個函數(shù)分別用于獲取參數(shù)的年份、月份、星期、日、時、分和秒，這幾個函數(shù)分別是YEAR(d)，MONTH(d)，WEEK(d)，DAY(d)，HOUR(d)，MINUTE(d)和SECOND(d)。下面分別介紹它們的用法。mysql SELECT YEAR(07-01-15),YEAR(n

34、ow();+-+-+| YEAR(07-01-15) | YEAR(now()|+-+-+| 2007| 2018|+-+-+1 row in set (0.04 sec)【實例10-28】執(zhí)行SQL語句，驗證函數(shù)YEAR(d)的應(yīng)用。執(zhí)行結(jié)果如下：YEAR(d)1函數(shù)YEAR(d)返回所給的日期d是哪一年。第35頁，共69頁。10.3.5 獲取年份、月份、星期、日、時、分和秒的函數(shù)mysql SELECT MONTH(07-01-15),MONTH(NOW(),WEEK(07-01-15),WEEK(NOW();+-+-+-+-+| MONTH(07-01-15) | MONTH(NOW()

35、 | WEEK(07-01-15) | WEEK(NOW() |+-+-+-+-+| 1 | 5 | 2 | 18 |+-+-+-+-+1 row in set (0.00 sec)【實例10-29】執(zhí)行SQL語句，驗證函數(shù)MONTH(d)和WEEK(d)的應(yīng)用。執(zhí)行結(jié)果如下：MONTH(d)和WEEK(d)2函數(shù)MONTH(d)返回所給的日期d是一年中的第幾個月，函數(shù)WEEK(d)返回所給的日期d是一年中的第幾周。第36頁，共69頁。10.3.5 獲取年份、月份、星期、日、時、分和秒的函數(shù)mysql SELECT HOUR(07-01-15 05:16:21) AS HOUR, - MINU

36、TE(07-01-15 05:16:21) AS MINUTE, - SECOND(07-01-15 05:16:21) AS SECOND;+-+-+-+| HOUR| MINUTE| SECOND|+-+-+-+| 5| 16| 21|+-+-+-+1 row in set (0.00 sec)【實例10-30】執(zhí)行SQL語句，驗證函數(shù)HOUR(d)，MINUTE(d)和SECOND(d)的應(yīng)用。執(zhí)行結(jié)果如下：HOUR(d)，MINUTE(d)和SECOND(d)3函數(shù)HOUR(d)返回所給時間d的小時，函數(shù)MINUTE(d)返回所給時間d的分鐘，函數(shù)SECOND(d)返回所給時間d的秒。

37、第37頁，共69頁。10.3.6 格式化日期和時間的函數(shù)函數(shù)DATE_FORMAT(d,format)按字符串format格式化日期d的值，其中的format格式符及其作用如表10-4所示。表10-4 日期與時間格式符及其意義參 數(shù) 值意 義%Y四位數(shù)形式的年份%y兩位數(shù)形式的年份%c數(shù)字形式（012）的月份%M英文形式（JanuaryDecember）的月份名%m數(shù)字形式（0012）的月份%W一周中每天為周幾，用英文表示（Sunday,Monday, ,Saturday）%D月中的第幾天，英文后綴形式，如0th，1st，2nd，3rd%d兩位數(shù)字表示月中的第幾天，形式為0031%j一年的第幾

38、日（001366）%H24小時形式的小時（0023）%h12小時形式的小時（0112）%r12小時形式的小時，后綴為上午（AM）或下午（PM）%i兩位數(shù)字形式的分（0059）%S兩位數(shù)字形式的秒（0059）第38頁，共69頁。10.3.6 格式化日期和時間的函數(shù)mysql SELECT DATE_FORMAT(2008-01-05 14:30:30,%y %M %D %r);+-+| DATE_FORMAT(2008-01-05 14:30:30,%y %M %D %r)|+-+| 08 January 5th 02:30:30 PM |+-+1 row in set (0.00 sec)【實

39、例10-31】執(zhí)行SQL語句，使用DATE_FORMAT()函數(shù)格式化指定的日期。SQL語句及其執(zhí)行結(jié)果如下：第39頁，共69頁。10.3.7 計算日期和時間的函數(shù)計算日期和時間的函數(shù)主要有ADDDATE()，SUBDATE()和DATEDIFF()，下面分別介紹。ADDDATE(date,INTERVAL expr unit)SUBDATE(date,INTERVAL expr unit)ADDDATE()和SUBDATE()1函數(shù)ADDDATE()與SUBDATE()分別用于執(zhí)行日期和時間的加運算與減運算，其語法形式如下：表10-5 MySQL中的日期與時間間隔類型間隔類型值描 述格 式Y(jié)

40、EAR年YYMONTH月MMDAY日DDYEAR_MONTH年和月YY-MMDAY_HOUR日和小時DD hhDAY_MINUTE日和分鐘DD hh:mmDAY_SECOND日和秒DD hh:mm:ssHOUR小時hhMINUTE分mmSECOND秒ssHOUR_MINUTE小時和分hh:mmHOUR_SECOND小時和秒hh:ssMINUTE_SECOND分鐘和秒mm:ss第40頁，共69頁。10.3.7 計算日期和時間的函數(shù)mysql SELECT ADDDATE(2009-01-01,INTERVAL 2 year) as date1, -ADDDATE(2009-01-01 06:20

41、:20,INTERVAL 2 hour) as date2, -ADDDATE(2009-01-01 06:20:20,INTERVAL 10:10 minute_second) as date3;+-+-+-+| date1 | date2 | date3 |+-+-+-+| 2011-01-01| 2009-01-01 08:20:20| 2009-01-01 06:30:30|+-+-+-+1 row in set (0.08 sec)【實例10-32】執(zhí)行SQL語句，使用ADDDATE()函數(shù)對日期執(zhí)行加運算。SQL語句及其執(zhí)行結(jié)果如下：mysql SELECT SUBDATE(200

42、9-01-01,INTERVAL 2 year) as date1, - SUBDATE(2009-01-01 06:20:20,INTERVAL 7 hour) as date2;+-+-+| date1 | date2 |+-+-+| 2007-01-01| 2008-12-31 23:20:20 |+-+-+1 row in set (0.00 sec)【實例10-33】執(zhí)行SQL語句，使用SUBDATE()函數(shù)對日期執(zhí)行減運算。SQL語句及其執(zhí)行結(jié)果如下：第41頁，共69頁。10.3.7 計算日期和時間的函數(shù)mysql SELECT DATEDIFF(2022-02-04,NOW();

43、+-+| DATEDIFF(2022-02-04,NOW()|+-+| 1365 |+-+1 row in set (0.06 sec)【實例10-34】執(zhí)行SQL語句，計算現(xiàn)在距離2022年2月4日的北京冬奧會還有多少天，SQL語句及其執(zhí)行結(jié)果如下：DATEDIFF()函數(shù)2函數(shù)DATEDIFF()用于計算兩個日期之間相差的天數(shù)。第42頁，共69頁。10.3.8 時間和秒相互轉(zhuǎn)換的函數(shù)函數(shù)TIME_TO_SEC(d)可將指定的時間d換算為秒，函數(shù)SEC_TO_TIME(d)可將指定的秒換算為“HH:MM:SS”形式的時間格式。mysql SELECT TIME_TO_SEC(05:30:30

44、),SEC_TO_TIME(19830);+-+-+| TIME_TO_SEC(05:30:30)| SEC_TO_TIME(19830)|+-+-+| 19830| 05:30:30 |+-+-+1 row in set (0.05 sec)【實例10-35】執(zhí)行SQL語句，使用TIME_TO_SEC(d)函數(shù)和SEC_TO_TIME(d)函數(shù)，分別將時間轉(zhuǎn)換為秒和將秒轉(zhuǎn)換為時間格式。SQL語句及其執(zhí)行結(jié)果如下：第43頁，共69頁。10.4條件判斷函數(shù)第44頁，共69頁。條件判斷函數(shù)又稱為流程控制函數(shù)，也是MySQL中使用較多的一種函數(shù)。用戶可以使用這類函數(shù)在SQL語句中實現(xiàn)條件選擇。表10

45、-6列出了MySQL中與條件選擇相關(guān)的函數(shù)及其功能。表10-6 MySQL中的條件判斷函數(shù)及其功能函 數(shù)功 能IF(expr,v1,v2)如果expr為真，返回v1，否則返回v2IFNULL(v1,v2)如果v1不為NULL，返回v1，否則返回v2CASE WHEN expr1 THEN r1 WHEN expr2 THEN r2 ELSE rn END根據(jù)條件將數(shù)據(jù)分為幾個檔次CASE expr WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE rn END根據(jù)條件將數(shù)據(jù)分為幾個檔次第45頁，共69頁。IF(expr,v1,v2)函數(shù)的意義是，如果表達(dá)式expr的結(jié)果

46、為真，函數(shù)的返回值為v1，如果表達(dá)式expr的結(jié)果為假，則返回值為v2。mysql SELECT name,money,IF(money10000,high,low) FROM staff;+-+-+-+| name | money | IF(money10000,high,low)|+-+-+-+| 劉長生 | 20000.00| high | 趙霞 | 10000.00 | low | 季慶奇 | 15000.00| high | 李星宇 | 15000.00 | high | 張向陽 | 15000.00 | high | 張旭 | 10000.00 | low |+-+-+-+6 ro

47、ws in set (0.02 sec)【實例10-36】執(zhí)行SQL語句，使用IF(expr,v1,v2)函數(shù)將員工分為高薪和低薪兩類，此處認(rèn)為月薪在10000元以上的員工屬于高薪，用high表示；月薪在10000元以下的員工屬于低薪，用low表示。10.4.1 IF()函數(shù)步驟1 執(zhí)行以下語句，選擇數(shù)據(jù)庫staff。步驟2 執(zhí)行SQL語句，查找員工姓名和月薪，并將staff表中的員工工資分為高薪和低薪兩個級別后將級別情況輸出。SQL語句及其執(zhí)行結(jié)果如下：USE staff;第46頁，共69頁。IFNULL(v1,v2)函數(shù)的意義是，如果v1不為NULL，則函數(shù)的返回值為v1，否則返回值為v2

48、。我們知道，NULL值是不能參與數(shù)值運算的，實際應(yīng)用中常用該函數(shù)來替換NULL值，下面通過實例介紹?！緦嵗?0-37】執(zhí)行SQL語句，使用IFNULL(v1,v2)函數(shù)將staff表中money字段的NULL值替換為0。10.4.2 IFNULL()函數(shù)步驟1 為便于后面操作，此處需要先將staff表中某條記錄的money字段值改為NULL。為此，打開可視化編輯軟件，首先在左側(cè)列表中選中staff表，然后單擊“設(shè)計表”按鈕，進(jìn)入表結(jié)構(gòu)設(shè)計界面，單擊money字段右側(cè)的“不是null”列，取消選擇該項，然后按【Ctrl+S】組合鍵保存修改，如圖10-1所示。圖10-1 取消“不是null”列選項

49、只有取消該選項，money字段才可以為NULL。第47頁，共69頁。10.4.2 IFNULL()函數(shù)步驟3 打開命令行窗口，登錄MySQL后執(zhí)行以下語句，選擇數(shù)據(jù)庫staff。圖10-2 刪除第4條記錄的money字段步驟2 雙擊左側(cè)列表中的staff表將其打開，刪除第4條記錄的money字段，并單擊下面的 按鈕確認(rèn)刪除，如圖10-2所示。USE staff;第48頁，共69頁。10.4.2 IFNULL()函數(shù)步驟4 執(zhí)行SQL語句，將staff表中money字段的NULL值替換為0，SQL語句及其執(zhí)行結(jié)果如下：mysql SELECT IFNULL(money,0) FROM staff

50、;+-+| IFNULL(money,0) |+-+| 20000.00 | 10000.00 | 15000.00 | 0.00 | 15000.00 | 10000.00 |+-+6 rows in set (0.05 sec)第49頁，共69頁?！緦嵗?0-38】使用CASE函數(shù)實現(xiàn)實例10-35中的高低薪問題。SQL語句及其執(zhí)行結(jié)果如下：10.4.3 CASE函數(shù)CASE WHEN expr1 THEN r1 WHEN expr2 THEN r2 ELSE rn ENDmysql SELECT CASE WHEN money10000 THEN high ELSE low END FR

51、OM staff;+-+| CASE WHEN money10000 THEN high ELSE low END|+-+| high | low | high | low | high | low |+-+6 rows in set (0.02 sec)第50頁，共69頁?！緦嵗?0-39】使用CASE函數(shù)將員工的月薪分成多個檔次，此處分為3個檔次。SQL語句及其執(zhí)行結(jié)果如下：10.4.3 CASE函數(shù)CASE expr WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE rn ENDmysql SELECT CASE money WHEN 20000 THEN hig

52、h WHEN 15000 THEN mid ELSE low END FROM staff;+-+| CASE money WHEN 20000 THEN high WHEN 15000 THEN mid ELSE low END |+-+| high | low | mid | low | mid | low |+-+6 rows in set (0.00 sec)第51頁，共69頁。10.5JSON函數(shù)第52頁，共69頁。從MySQL 5.7.8起，開始支持JSON數(shù)據(jù)類型。JSON函數(shù)就是用于處理JSON類型的數(shù)據(jù)，表10-7列出了MySQL支持的JSON函數(shù)及其功能。表10-7 MyS

53、QL中的JSON函數(shù)及其功能函 數(shù)功 能JSON_ARRAY()創(chuàng)建JSON數(shù)組JSON_OBJECT()創(chuàng)建JSON對象JSON_ARRAY_APPEND()向JSON數(shù)組中追加數(shù)據(jù)JSON_SET()修改JSON對象中的數(shù)據(jù)JSON_REMOVE()刪除JSON數(shù)組和JSON對象中的數(shù)據(jù)JSON_EXTRACT()返回JSON數(shù)組中KEY所對應(yīng)的數(shù)據(jù)JSON_SEARCH()返回JSON數(shù)組中給定數(shù)據(jù)的路徑第53頁，共69頁。在MySQL中創(chuàng)建JSON值的函數(shù)有兩個，一個用于創(chuàng)建數(shù)組形式的JSON值，另一個用于創(chuàng)建對象形式的JSON值。創(chuàng)建JSON數(shù)組110.5.1 創(chuàng)建JSON值的函數(shù)J

54、SON_ARRAY()函數(shù)用于創(chuàng)建數(shù)組形式的JSON值，其語法形式如下：JSON_ARRAY(val1,val2,valn)【實例10-40】執(zhí)行SQL語句，創(chuàng)建JSON數(shù)組。SQL語句及其執(zhí)行結(jié)果如下：mysql SELECT JSON_ARRAY(1, abc, null, true,CURTIME();+-+| JSON_ARRAY(1, abc, null, true,CURTIME()|+-+| 1, abc, null, true, 13:23:42.000000 |+-+1 row in set (0.03 sec)第54頁，共69頁。創(chuàng)建JSON對象210.5.1 創(chuàng)建JSON

55、值的函數(shù)JSON_OBJECT()函數(shù)用于創(chuàng)建對象形式的JSON值，其語法形式如下：JSON_OBJECT(key1: val1,key2: val2,keyn: valn)【實例10-41】執(zhí)行SQL語句，創(chuàng)建JSON對象。SQL語句及其執(zhí)行結(jié)果如下：mysql SELECT JSON_OBJECT(id, 8, name, Tom);+-+| JSON_OBJECT(id, 8, name, Tom) |+-+| id: 8, name: Tom |+-+1 row in set (0.00 sec)第55頁，共69頁。10.5.2 修改JSON值的函數(shù)在MySQL中常用修改JSON值的函

56、數(shù)也有兩個：JSON_ARRAY_APPEND()和JSON_SET()。JSON_ARRAY_APPEND(json_doc,key,val,key,val.)【實例10-42】執(zhí)行SQL語句，修改JSON數(shù)組的值。mysql SELECT JSON_ARRAY_APPEND(j, $1, 1);+-+| JSON_ARRAY_APPEND(j, $1, 1) |+-+| a, b, c, 1, d |+-+1 row in set (0.00 sec)JSON_ARRAY_APPEND()1使用JSON_ARRAY_APPEND()函數(shù)，可以將值附加到JSON文檔中指示數(shù)組的結(jié)尾并返回結(jié)果

57、。步驟1 定義JSON類型的數(shù)組，SQL語句及其執(zhí)行結(jié)果如下：mysql SET j = a, b, c, d;Query OK, 0 rows affected (0.00 sec)步驟2 使用JSON_ARRAY_APPEND()函數(shù)在數(shù)組中附加值，并查看結(jié)果。SQL語句及其執(zhí)行結(jié)果如下：第56頁，共69頁。JSON_SET()210.5.2 修改JSON值的函數(shù)使用JSON_SET()函數(shù)，可以在JSON文檔中插入或更新數(shù)據(jù)并返回結(jié)果，其語法形式如下：JSON_SET(json_doc,key,val,key,val.)【實例10-43】執(zhí)行SQL語句，修改JSON對象的值。mysql

58、SELECT JSON_SET(j, $.a, 10, $.c, true, false);+-+| JSON_SET(j, $.a, 10, $.c, true, false) |+-+| a: 10, b: 2, 3, c: true, false|+-+1 row in set (0.05 sec)步驟1 定義JSON類型的對象，SQL語句及其執(zhí)行結(jié)果如下：mysql SET j = a: 1, b: 2, 3;Query OK, 0 rows affected (0.01 sec)步驟2 使用JSON_SET()函數(shù)在JSON文檔中更新和插入數(shù)據(jù)，并查看結(jié)果。SQL語句及其執(zhí)行結(jié)果如下

59、：第57頁，共69頁。10.5.3 從JSON文檔中刪除數(shù)據(jù)的函數(shù)如果用戶需要刪除JSON數(shù)組或者JSON對象中的數(shù)據(jù)，可以使用JSON_REMOVE()函數(shù)。JSON_REMOVE(json_doc,key,val,key,val.)【實例10-44】執(zhí)行SQL語句，刪除JSON數(shù)組和JSON對象中的數(shù)據(jù)。mysql SELECT JSON_REMOVE(j, $1), JSON_REMOVE(h, $.a);+-+-+| JSON_REMOVE(j, $1)| JSON_REMOVE(h, $.a)|+-+-+| a, d | b: 2, 3 |+-+-+1 row in set (0.0

60、0 sec)步驟1 執(zhí)行以下SQL語句，定義JSON類型的數(shù)組和對象：SET j = a, b, c, d, h = a: 1, b: 2, 3;步驟2 使用JSON_REMOVE()函數(shù)刪除JSON數(shù)組和JSON對象中的數(shù)據(jù)，并查看結(jié)果。SQL語句及其執(zhí)行結(jié)果如下：第58頁，共69頁。10.5.4 返回JSON文檔中數(shù)據(jù)和路徑的函數(shù)使用JSON_EXTRACT()函數(shù)，可以根據(jù)給出的key，返回JSON文檔中其所對應(yīng)的數(shù)據(jù)。JSON_EXTRACT (json_doc,key1,key2.)【實例10-45】執(zhí)行SQL語句，根據(jù)key返回JSON文檔中其所對應(yīng)的數(shù)據(jù)。mysql SELECT