《MySQL数据库应用案例教程》教学课件第10章MySQL常用函数

1、10MySQL常用函数第章第1页，共69页。10.1 数值函数第2页，共69页。数值函数是MySQL中一种很重要的函数，主要用于处理数值方面的运算。如果没有这些函数，用户在编写有关数值运算方面的代码时将会复杂很多。例如，如果没有ABS求绝对值函数，要取一个数的绝对值，就需要进行多次判断，直接使用该函数可以大大提高用户的工作效率。表10-1 MySQL中常用的数值函数及其功能函 数功 能ABS(x)返回数值x的绝对值MOD(x,y)返回数值x除以数值y后的余数CEIL(x)返回大于数值x的最小整数值FLOOR(x)返回小于数值x的最大整数值RAND()返回01内的随机数ROUND(x)返回对参数

2、x进行四舍五入后的值，ROUND(x)返回整数值，ROUND(x,y)返回参数x四舍五入后保留y位小数的值TRUNCATE(x,y)对数值x进行截取，保留小数点后y位数字第3页，共69页。【实例10-1】执行SQL语句，求5，-5和-5.5的绝对值，执行结果如下：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)函数ABS(x)的返回值是数值x的绝对值。正数的绝对值是其本身，负数的绝对值是其相反数

3、。10.1.1 求绝对值函数第4页，共69页。函数MOD(x,y)的返回值是数值x除以数值y后的余数。与x%y的结果相同，除数和被除数任何一个为NULL，返回结果都将为NULL；除数为0将是非法运算，返回结果为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】执行SQL语句，求6除以4，6除以-4，NULL除以6，6除以0，及0除以2.5的余数，执行结果如下：10.1.2 求余函数第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】执行SQL语句，分别求大于数值2.45和-2.45的最小整数值，执行结果如下：10.1.3 用于获取整数的函数CEIL(x)1MySQL中用于获取整数的函数主要有C

5、EIL(x)和FLOOR(x)，下面分别介绍。函数CEIL(x)的返回值是大于数值x的最小整数值，下面通过实例介绍。第6页，共69页。10.1.3 用于获取整数的函数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】执行SQL语句，求小于数值2.45和-2.45的最大整数值，执行结果如下：FLOOR(x)2函数FLOOR(x)的返回值是小于数值x的最大整数值，下面通过实例介绍。第7页，共69页。函数

6、RAND()的返回值是01内的小数，并且每次的运行结果都不同，下面通过实例介绍。mysql SELECT RAND(),RAND(),RAND();+-+-+-+| RAND() | RAND() | RAND() |+-+-+-+| 0.32945327863977597 | 0.017887058684497196 | 0.10107548823680308|+-+-+-+1 row in set (0.04 sec)【实例10-5】执行SQL语句，使用函数RAND()获取随机数，执行结果如下：10.1.4 获取随机数的函数第8页，共69页。函数ROUND()的作用是对数值执行四舍五入操作

7、，当函数格式为ROUND(x)时，返回值为整数；当函数格式为ROUND(x,y)时，对数值x进行四舍五入并保留小数点后y位，下面通过实例进行介绍。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】执行SQL语句，使用函数ROUND(x)和ROUND(x,y)对数值进行四舍五入操作，执行结果如下：10.1.5 四舍五入函数第9页，共69页。函数TRUNCATE(x,y)的作用是对数值x进行截取，保留小数点后y位。其与ROUND()函数的区别是，ROUND()函数在截取值时会四舍五入；而TRUNCATE(x,y)函数直接截取值，并不进行四舍五入。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】执行SQL语句，使用函数TRUNCATE(x,y)和ROUND(x,y)分别截取数值，执行结果如下：10.1.6 截取小数函数第10页，共69页。10.2字符串函数第11页，共69页。字符串函数是MySQL中使用最频繁的函数，主要用于处理数据库中字符串类型的数据。表10-2列出了MySQL中常用的字符串函数及其功能。表10-2 MySQL中常用的字符串函数及其功能函 数功 能LENGTH(str)，CHAR_LENGTH(str)返回字符串长度或字符个数CONCAT(s

10、tr1,str2strn)，CONCAT_WS(x,str1,str2strn)合并字符串INSERT(str,x,y,instr)，REPLACE(str,a,b)替换字符串LOWER(str)，UPPER(str)字符大小写转换LEFT(str,x)，RIGHT(str,x)，SUBSTRING(str,x,y)获取字符串的一部分LPAD(str1,n,str2)，RPAD(str1,n,str2)填充字符串LTRIM(str)，RTRIM(str)，TRIM(str)删除字符串左侧、右侧或两侧空格REPEAT(str,n)返回字符串str重复n次的结果LOCATE(str1,str)返回

11、子字符串的开始位置REVERSE(str)反转字符串第12页，共69页。10.2.1 返回字符串长度和字符串中字符个数的函数函数LENGTH(str)用于返回字符串的长度，一个汉字占用2个字节，一个英文字符和数字占用1个字节。mysql SELECT LENGTH(abcdef),LENGTH(字符长度);+-+-+| LENGTH(abcdef) | LENGTH(字符长度)|+-+-+| 6 | 8|+-+-+1 row in set (0.00 sec)【实例10-8】执行SQL语句，使用函数LENGTH(str)返回字符串长度，执行结果如下：第13页，共69页。10.2.1 返回字符串

12、长度和字符串中字符个数的函数函数CHAR_LENGTH(str)用于返回字符串中的字符个数。mysql SELECT CHAR_LENGTH(abcdef),CHAR_LENGTH(字符个数);+-+-+| CHAR_LENGTH(abcdef) | CHAR_LENGTH(字符个数)|+-+-+| 6 | 4 |+-+-+1 row in set (0.00 sec)【实例10-9】执行SQL语句，使用函数CHAR_LENGTH(str)返回字符串中的字符个数，执行结果如下：第14页，共69页。10.2.2 合并字符串的函数函数CONCAT(str1,str2strn)可以将多个字符串拼接成

13、为一个字符串，但如果参数中有一个NULL值，则返回结果都将为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】执行SQL语句，使用函数CONCAT(str1,str2strn)拼接字符串，执行结果如下：第15页，共69页。10.2.2 合并字符串的函数函数CONCAT_WS(x,str1,str2strn)是函数CONC

14、AT(str1,str2strn)的特殊形式，作用是以第一个参数为分隔符，连接后面的多个字符串。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】执行SQL语句，使用函数CONCAT_WS(x,str1,str2strn)拼接字符串，执行结果如下： 提示由执行结果可以看出，函数CONCAT_WS

15、(x,str1,str2strn)会忽略分隔符后的NULL值，但如果分隔符为NULL，则返回结果为NULL。第16页，共69页。10.2.3 替换字符串的函数函数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】执行SQL语句，使用函数INSERT(str,x,y,instr)把字符串“beijinglvyoushichang”从第13个字符开始后面的8个字符替换为字符串“gonglue”，执行结果如下：第17页，共69页。10.2.3 替换字符串的函数函数REPLACE(str,a,b)也可以替换字符串，作用是使用字符串b替换字符串str中的子串a。mysql SELECT REPLACE(abcabc,abc,you);+-+| REPLACE(abcabc,abc,you)|+-+| youyou |+-+1 row in set (0.00 sec)【实例10-13】执行SQL语句，使

17、用函数REPLACE(str,a,b)把字符串“abcabc”中的子串“abc”替换为字符串“you”，执行结果如下：第18页，共69页。10.2.4 字母大小写转换函数函数LOWER(str)用于将字符串str中的字母全部转换为小写字母，函数UPPER(str)用于将字符串str中的字母全部转换为大写字母。mysql SELECT LOWER(aBcD),UPPER(aBcD);+-+-+| LOWER(aBcD)| UPPER(aBcD)|+-+-+| abcd | ABCD |+-+-+1 row in set (0.07 sec)【实例10-14】执行SQL语句，分别使用函数LOWER

18、(str)和UPPER(str)把字符串“aBcD”转换为小写字母和大写字母，执行结果如下：第19页，共69页。10.2.5 获取指定长度字符串的函数函数LEFT(str,x)用于获取字符串str中最左边的x个字符，函数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】执行SQL语句，分别使用函数LEFT(str,x)和RIGHT(str,x)获取字符串“beijinglvyougonglue”左边和右边的7个字符，执行结果如下：第20页，共69页。10.2.5 获取指定长度字符串的函数函数SUBSTRING(str,x,y)用于获取字符串str中从x位置开始，后面y个字符长度的子串。该函数常用于在给定字符串中提取子串。mysql SELECT SUBSTRING(beijinglvyougonglue,8,5);+-+| SUBSTRING(beijinglvyougonglu

20、e,8,5)|+-+| lvyou |+-+1 row in set (0.00 sec)【实例10-16】执行SQL语句，使用函数SUBSTRING(str,x,y)获取字符串“beijinglvyougonglue”从第8个字符开始，后面的5个字符，执行结果如下：第21页，共69页。10.2.6 填充字符串的函数函数LPAD(str1,n,str2)的作用是使用字符串str2对字符串str1最左边进行填充，直到字符串str1总长度达到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】执行SQL语句，使用字符串km对字符串“abcdefg”最左边进行填充，直到字符串达到5个或10个字符长度。执行结果如下：第22页，共69页。10.2.6 填充字符串的函数函数RPAD(str1,n,str2)的作用是使用字符串str2对字符串str1最右边进行填充，直到字符串str1总长度达到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】执行SQL语句，使用字符串km对字符串“abcdefg”最右边进行填充，直到字符串达到5个或10个字符长度。执行结果如下：第23页，共69页。10.2.7 删除字符串中空格的函数函数LTRIM(str)用于删除字符串左侧的空格字符，函数RTRIM(str)用于删除字符串右侧的空格字符。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】执行SQL语句，验证函数LTRIM(str)和RTRIM(str)的应用。执行结果如下： 提示上述语句中，字

24、符串“cd”两侧各有一个空格。在语句CONCAT(ab,LTRIM( cd ),ef) AS str2中，函数LTRIM( cd )将cd左侧的空格删除了，所以语句执行结果为abcd ef；在语句CONCAT(ab,RTRIM( cd ),ef) AS str4中，函数RTRIM( cd )将cd右侧的空格删除了，所以语句执行结果为ab cdef。第24页，共69页。10.2.7 删除字符串中空格的函数函数TRIM(str)用于删除字符串开头和结尾的空格，另外，它还可以删除字符串两侧的指定字符。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】执行SQL语句，验证函数TRIM(str)的应用。执行结果如下：可以看到，在语句CONCAT(ab,TRIM( cd ),ef) AS str2中，函数TRIM( cd )将cd两侧的空格全部删除了，所以合并字符串的结果为abcdef；在语句TRIM(a from aabacaa)

26、AS str3中，函数TRIM(a from aabacaa)将字符串aabacaa两侧的指定字符a全部删除了。第25页，共69页。10.2.8 重复生成字符串的函数函数REPEAT(str,n)返回字符串str重复n次的结果。mysql SELECT REPEAT(abc ,3);+-+| REPEAT(abc ,3) |+-+| abc abc abc |+-+1 row in set (0.01 sec)【实例10-21】执行SQL语句，验证函数REPEAT(str,n)的应用。执行结果如下： 提示此处c后面有一个空格。第26页，共69页。10.2.9 获取字符串中子串开始位置的函数函数

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】执行SQL语句，验证函数LOCATE(str1,str)的应用。执行结果如下：第27页，共69页。10.2.10 反转字符串的函数函数REVE

28、RSE(str)返回将字符串str中字符倒序排列后的结果。mysql SELECT REVERSE(abcdefg);+-+| REVERSE(abcdefg) |+-+| gfedcba |+-+1 row in set (0.00 sec)【实例10-23】执行SQL语句，验证函数REVERSE(str)的应用。执行结果如下：第28页，共69页。10.3日期与时间函数第29页，共69页。在实际应用中，有时可能会遇到这样的需求：获取当前时间，或者下个月的今天是星期几，等等类似的问题。这些需求就需要使用日期与时间函数来实现。表10-3列出了MySQL中常用的日期与时间函数及其功能。表10-3

29、MySQL中常用的日期与时间函数及其功能函 数功 能CURDATE()获取当前日期CURTIME()获取当前时间NOW()获取当前的日期和时间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的值ADDDATE(date,INTERVAL expr unit)，SUBDATE(date,INTERVAL expr unit)获取一个日期

30、或时间值加上一个时间间隔的时间值TIME_TO_SEC(d),SEC_TO_TIME(d)获取将“HH:MM:SS”格式的时间换算为秒，或将秒数换算为“HH:MM:SS”格式的值第30页，共69页。10.3.1 获取当前日期的函数函数CURDATE()返回包含年月日的当前日期。mysql SELECT CURDATE();+-+| CURDATE()|+-+| 2018-05-10|+-+1 row in set (0.04 sec)【实例10-24】执行SQL语句，使用函数CURDATE()获取当前日期。执行结果如下：第31页，共69页。10.3.2 获取当前时间的函数函数CURTIME()

31、返回“HH:MM:SS”格式的当前时间。mysql SELECT CURTIME();+-+| CURTIME()|+-+| 15:54:13 |+-+1 row in set (0.00 sec)【实例10-25】执行SQL语句，使用函数CURTIME()获取当前时间。执行结果如下：第32页，共69页。10.3.3 获取当前日期和时间的函数函数NOW()返回当前日期和时间（同时包含年月日和时分秒）。mysql SELECT NOW();+-+| NOW() |+-+| 2018-05-10 16:03:56|+-+1 row in set (0.00 sec)【实例10-26】执行SQL语句

32、，使用函数NOW()获取当前日期和时间。执行结果如下：第33页，共69页。10.3.4 获取UNIX时间戳函数UNIX时间戳是从1970年1月1日（UTC/GMT的午夜）开始到当前时间所经过的秒数（不考虑闰秒）。一分钟表示为UNIX时间戳为60秒，一小时表示为UNIX时间戳为3600秒，一天表示为UNIX时间戳为86400秒。函数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】执行SQL语句，使用函数UNIX_TIMESTAMP(date)获取UNIX格式的当前日期和时间。执行结果如下：第34页，共69页。10.3.5 获取年份、月份、星期、日、时、分和秒的函数MySQL提供多个函数分别用于获取参数的年份、月份、星期、日、时、分和秒，这几个函数分别是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】执行SQL语句，验证函数YEAR(d)的应用。执行结果如下：YEAR(d)1函数YEAR(d)返回所给的日期d是哪一年。第35页，共69页。10.3.5 获取年份、月份、星期、日、时、分和秒的函数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】执行SQL语句，验证函数MONTH(d)和WEEK(d)的应用。执行结果如下：MONTH(d)和WEEK(d)2函数MONTH(d)返回所给的日期d是一年中的第几个月，函数WEEK(d)返回所给的日期d是一年中的第几周。第36页，共69页。10.3.5 获取年份、月份、星期、日、时、分和秒的函数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】执行SQL语句，验证函数HOUR(d)，MINUTE(d)和SECOND(d)的应用。执行结果如下：HOUR(d)，MINUTE(d)和SECOND(d)3函数HOUR(d)返回所给时间d的小时，函数MINUTE(d)返回所给时间d的分钟，函数SECOND(d)返回所给时间d的秒。

37、第37页，共69页。10.3.6 格式化日期和时间的函数函数DATE_FORMAT(d,format)按字符串format格式化日期d的值，其中的format格式符及其作用如表10-4所示。表10-4 日期与时间格式符及其意义参 数 值意 义%Y四位数形式的年份%y两位数形式的年份%c数字形式（012）的月份%M英文形式（JanuaryDecember）的月份名%m数字形式（0012）的月份%W一周中每天为周几，用英文表示（Sunday,Monday, ,Saturday）%D月中的第几天，英文后缀形式，如0th，1st，2nd，3rd%d两位数字表示月中的第几天，形式为0031%j一年的第几

38、日（001366）%H24小时形式的小时（0023）%h12小时形式的小时（0112）%r12小时形式的小时，后缀为上午（AM）或下午（PM）%i两位数字形式的分（0059）%S两位数字形式的秒（0059）第38页，共69页。10.3.6 格式化日期和时间的函数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】执行SQL语句，使用DATE_FORMAT()函数格式化指定的日期。SQL语句及其执行结果如下：第39页，共69页。10.3.7 计算日期和时间的函数计算日期和时间的函数主要有ADDDATE()，SUBDATE()和DATEDIFF()，下面分别介绍。ADDDATE(date,INTERVAL expr unit)SUBDATE(date,INTERVAL expr unit)ADDDATE()和SUBDATE()1函数ADDDATE()与SUBDATE()分别用于执行日期和时间的加运算与减运算，其语法形式如下：表10-5 MySQL中的日期与时间间隔类型间隔类型值描 述格 式Y

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 计算日期和时间的函数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】执行SQL语句，使用ADDDATE()函数对日期执行加运算。SQL语句及其执行结果如下：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】执行SQL语句，使用SUBDATE()函数对日期执行减运算。SQL语句及其执行结果如下：第41页，共69页。10.3.7 计算日期和时间的函数mysql SELECT DATEDIFF(2022-02-04,NOW();

43、+-+| DATEDIFF(2022-02-04,NOW()|+-+| 1365 |+-+1 row in set (0.06 sec)【实例10-34】执行SQL语句，计算现在距离2022年2月4日的北京冬奥会还有多少天，SQL语句及其执行结果如下：DATEDIFF()函数2函数DATEDIFF()用于计算两个日期之间相差的天数。第42页，共69页。10.3.8 时间和秒相互转换的函数函数TIME_TO_SEC(d)可将指定的时间d换算为秒，函数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】执行SQL语句，使用TIME_TO_SEC(d)函数和SEC_TO_TIME(d)函数，分别将时间转换为秒和将秒转换为时间格式。SQL语句及其执行结果如下：第43页，共69页。10.4条件判断函数第44页，共69页。条件判断函数又称为流程控制函数，也是MySQL中使用较多的一种函数。用户可以使用这类函数在SQL语句中实现条件选择。表10

45、-6列出了MySQL中与条件选择相关的函数及其功能。表10-6 MySQL中的条件判断函数及其功能函 数功 能IF(expr,v1,v2)如果expr为真，返回v1，否则返回v2IFNULL(v1,v2)如果v1不为NULL，返回v1，否则返回v2CASE WHEN expr1 THEN r1 WHEN expr2 THEN r2 ELSE rn END根据条件将数据分为几个档次CASE expr WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE rn END根据条件将数据分为几个档次第45页，共69页。IF(expr,v1,v2)函数的意义是，如果表达式expr的结果

46、为真，函数的返回值为v1，如果表达式expr的结果为假，则返回值为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】执行SQL语句，使用IF(expr,v1,v2)函数将员工分为高薪和低薪两类，此处认为月薪在10000元以上的员工属于高薪，用high表示；月薪在10000元以下的员工属于低薪，用low表示。10.4.1 IF()函数步骤1 执行以下语句，选择数据库staff。步骤2 执行SQL语句，查找员工姓名和月薪，并将staff表中的员工工资分为高薪和低薪两个级别后将级别情况输出。SQL语句及其执行结果如下：USE staff;第46页，共69页。IFNULL(v1,v2)函数的意义是，如果v1不为NULL，则函数的返回值为v1，否则返回值为v2

48、。我们知道，NULL值是不能参与数值运算的，实际应用中常用该函数来替换NULL值，下面通过实例介绍。【实例10-37】执行SQL语句，使用IFNULL(v1,v2)函数将staff表中money字段的NULL值替换为0。10.4.2 IFNULL()函数步骤1 为便于后面操作，此处需要先将staff表中某条记录的money字段值改为NULL。为此，打开可视化编辑软件，首先在左侧列表中选中staff表，然后单击“设计表”按钮，进入表结构设计界面，单击money字段右侧的“不是null”列，取消选择该项，然后按【Ctrl+S】组合键保存修改，如图10-1所示。图10-1 取消“不是null”列选项

49、只有取消该选项，money字段才可以为NULL。第47页，共69页。10.4.2 IFNULL()函数步骤3 打开命令行窗口，登录MySQL后执行以下语句，选择数据库staff。图10-2 删除第4条记录的money字段步骤2 双击左侧列表中的staff表将其打开，删除第4条记录的money字段，并单击下面的 按钮确认删除，如图10-2所示。USE staff;第48页，共69页。10.4.2 IFNULL()函数步骤4 执行SQL语句，将staff表中money字段的NULL值替换为0，SQL语句及其执行结果如下：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页。【实例10-38】使用CASE函数实现实例10-35中的高低薪问题。SQL语句及其执行结果如下：10.4.3 CASE函数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页。【实例10-39】使用CASE函数将员工的月薪分成多个档次，此处分为3个档次。SQL语句及其执行结果如下：10.4.3 CASE函数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函数第52页，共69页。从MySQL 5.7.8起，开始支持JSON数据类型。JSON函数就是用于处理JSON类型的数据，表10-7列出了MySQL支持的JSON函数及其功能。表10-7 MyS

53、QL中的JSON函数及其功能函 数功 能JSON_ARRAY()创建JSON数组JSON_OBJECT()创建JSON对象JSON_ARRAY_APPEND()向JSON数组中追加数据JSON_SET()修改JSON对象中的数据JSON_REMOVE()删除JSON数组和JSON对象中的数据JSON_EXTRACT()返回JSON数组中KEY所对应的数据JSON_SEARCH()返回JSON数组中给定数据的路径第53页，共69页。在MySQL中创建JSON值的函数有两个，一个用于创建数组形式的JSON值，另一个用于创建对象形式的JSON值。创建JSON数组110.5.1 创建JSON值的函数J

54、SON_ARRAY()函数用于创建数组形式的JSON值，其语法形式如下：JSON_ARRAY(val1,val2,valn)【实例10-40】执行SQL语句，创建JSON数组。SQL语句及其执行结果如下：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页。创建JSON对象210.5.1 创建JSON

55、值的函数JSON_OBJECT()函数用于创建对象形式的JSON值，其语法形式如下：JSON_OBJECT(key1: val1,key2: val2,keyn: valn)【实例10-41】执行SQL语句，创建JSON对象。SQL语句及其执行结果如下：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值的函数在MySQL中常用修改JSON值的函

56、数也有两个：JSON_ARRAY_APPEND()和JSON_SET()。JSON_ARRAY_APPEND(json_doc,key,val,key,val.)【实例10-42】执行SQL语句，修改JSON数组的值。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()函数，可以将值附加到JSON文档中指示数组的结尾并返回结果

57、。步骤1 定义JSON类型的数组，SQL语句及其执行结果如下：mysql SET j = a, b, c, d;Query OK, 0 rows affected (0.00 sec)步骤2 使用JSON_ARRAY_APPEND()函数在数组中附加值，并查看结果。SQL语句及其执行结果如下：第56页，共69页。JSON_SET()210.5.2 修改JSON值的函数使用JSON_SET()函数，可以在JSON文档中插入或更新数据并返回结果，其语法形式如下：JSON_SET(json_doc,key,val,key,val.)【实例10-43】执行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语句及其执行结果如下：mysql SET j = a: 1, b: 2, 3;Query OK, 0 rows affected (0.01 sec)步骤2 使用JSON_SET()函数在JSON文档中更新和插入数据，并查看结果。SQL语句及其执行结果如下

59、：第57页，共69页。10.5.3 从JSON文档中删除数据的函数如果用户需要删除JSON数组或者JSON对象中的数据，可以使用JSON_REMOVE()函数。JSON_REMOVE(json_doc,key,val,key,val.)【实例10-44】执行SQL语句，删除JSON数组和JSON对象中的数据。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 执行以下SQL语句，定义JSON类型的数组和对象：SET j = a, b, c, d, h = a: 1, b: 2, 3;步骤2 使用JSON_REMOVE()函数删除JSON数组和JSON对象中的数据，并查看结果。SQL语句及其执行结果如下：第58页，共69页。10.5.4 返回JSON文档中数据和路径的函数使用JSON_EXTRACT()函数，可以根据给出的key，返回JSON文档中其所对应的数据。JSON_EXTRACT (json_doc,key1,key2.)【实例10-45】执行SQL语句，根据key返回JSON文档中其所对应的数据。mysql SELECT