mysql 函数
目录1、mysql函数简介2、数学函数2.1 绝对值函数ABS(x)和返回圆周率的函数pi()2.2 平方根函数SQRT(x)和求余函数MOD(x,y)2.3 获取整数的函数CEIL(x)、CEILING(x)和 FLOOR(x)2.4 获取随机数的函数RAND()和RAND(x)MySQL 数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息
目录
2.3 获取整数的函数CEIL(x)、CEILING(x)和 FLOOR(x)
2.7 幂运算函数POW(x,y)、POWER(x,y)和EXP(x)
2.9 角度与弧度相互转换的函数RADIANS(x)和 DEGREES(x)
3.2 合并字符串的函数CONCAT(s1,s2,...)和CONCAT_WS(x,s1,s2,.….)
3.3 替换字符串的函数INSERT(s1,x,len,s2)
3.5 获取指定长度的字符串的函数LEFT(s,n)和 RIGHT(s,n)
3.6 填充字符串的函数LPAD(s1,len,s2)和 RPAD(s1,len,s2)
3.7 删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)
3.10 空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)
3.12 获取子串的函数SUBSTRING(s,n,len)和 MID(s,n,len)
3.16 返回指定字符串位置的函数FIELD(s,s1,s2,...)
3.17 返回子串位置的函数FIND_IN_SET(s1,s2)
3.18 选取字符串的函数MAKE_SET(x,s1,s2,..….)
4.5 获取月份的函数MONTH(d)和 MONTHNAME(d)
4.6 获取星期的函数DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)
4.7 获取星期数的函数WEEK(d)和WEEKOFYEAR(d)
4.8 获取天数的函数DAYOFYEAR(d)和 DAYOFMONTH(d)
4.10 获取日期的指定值的函数EXTRACT(type FROM d)
7.4 解密函数DECODE(crypt_str,pswd_str)
12.１根据分隔符获取字符串的字串 substring_index(s,d,n)
12.2 计算两日期的时间差 timestampdiff(u,d1,d2)
MySQL 数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数，可以简化用户的操作。例如，字符串连接函数可以很方便地将多个字符串连接在一起。
1、mysql函数简介
MySQL函数是MySQL 数据库提供的内部函数。这些内部函数可以帮助用户更加方便地处理表中的数据。
MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数等。SELECT语句及其条件表达式都可以使用这些函数。同时，INSERT、UPDATE和DELECT 语句及其条件表达式也可以使用这些函数。例如，表中的某个数据是负数，现在需要将这个数据显示为正数。这就可以使用绝对值函数。从上面可以知道，MySQL函数可以对表中数据进行相应的处理，以便得到用户希望得到的数据。这些函数可以使MySQL数据库的功能更加强大。
- 数学函数:这类函数主要用于处理数字。这类函数包括绝对值函数、正弦函数、余弦函数和获取随机数的函数等。
- 字符串函数:这类函数主要用于处理字符串。其中包括字符串连接函数、字符串比较函数、将字符串的字母都变成小写或大写字母的函数和获取子串的函数等。
- 日期和时间函数:这类函数主要用于处理日期和时间。其中包括获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数等。
- 条件判断函数;这类函数主要用于在SQL语句中控制条件选择。其中包括IF语句、CASE语句和WHEN语句等。
- 系统信息函数:这类函数主要用于获取 MySQL数据库的系统信息。其中包括获取数据库名的函数、获取当前用户的函数和获取数据库版本的函数等。
- 加密函数:这类函数主要用于对字符串进行加密解密。其中包括字符串加密函数和字符串解密函数等。
- 其他函数:包括格式化函数和锁函数等。
2、数学函数
数学函数是 MySQL中常用的一类函数。主要用于处理数字，包括整型、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数和获取随机数的函数等。下面是各种数学函数的符号、作用：
2.1 绝对值函数ABS(x)和返回圆周率的函数pi()
ABS(x)用来求绝对值;PI()用来返回圆周率。
下面将演示ABS(x)、PI()这两个函数的使用。
2.2 平方根函数SQRT(x)和求余函数MOD(x,y)
SQRT(x)用来求平方根;MOD(x,y)用来求余数。
下面将演示SQRT(x)、MOD(x,y)两个函数的使用。
结果显示，SQRT(16)和SQRT(2)返回的值分别是4和1.4142135623730951，结果正好分别是16和2的平方根;MOD(5,2)返回余数1。
2.3 获取整数的函数CEIL(x)、CEILING(x)和 FLOOR(x)
.CEIL(x)和CEILING(x)这两个函数返回大于或等于x的最小整数;FLOOR(x)函数返回小于或等于x的最大整数。
下面将演示CEIL(x)、CEILING(x)和FLOOR(x)3个函数的使用。
结果显示，CEIL(2.3)返回的结果为3，因为3是大于2.3的最小整数;CEIL(-2.3)返回的结果为-2，因为-2是大于-2.3的最小整数;CEILING(2.3)与CEIL(2.3)的结果是一样的，CEILING(-2.3)与CEIL(-2.3)的结果是一样的;FLOOR(2.3)返回的结果为2，因为2是小于2.3的最大整数;FLOOR(-2.3)返回的结果为-3，因为-3是小于-2.3的最大整数。
CEIL(x)和CEILING(x)函数返回大于或等于x的最小整数，这相当于直接进行进位处理。FLOOR(x)函数返回小于或等于x的最大整数，这相当于直接舍掉数字x的小数部分。
2.4 获取随机数的函数RAND()和RAND(x)
RAND()和RAND(x)这两个函数都是返回0~1的随机数。但是RAND()返回的数是完全随机的，而 RAND(x)函数的x相同时返回的值是相同的。
下面将演示RANDO)和 RAND(x)两个函数的使用。
结果显示，两个RAND()函数返回的结果是不一样的。而两个RAND(2)返回的结果是一样的。
结果显示，RAND(2)和 RAND(3)返回的结果是不同的。13.2.5四舍五入函数ROUND(x)、
2.5 ROUND(x,y)和TRUNCATE(x.y)
ROUND(x)函数返回离x最近的整数，也就是对x进行四舍五入处理;ROUND(x,y)函数返回x保留到小数点后y位的值，截断时需要进行四舍五入处理;TRUNCATE(x,y)函数返回x保留到小数点后y位的值。
下面将演示 ROUND(x)、ROUND(x,y)和 TRUNCATE(x,y)3个函数的使用。
结果显示，ROUND(2.3)返回的结果是2，ROUND(2.5)返回的结果是 3。这说明ROUND(x)函数返回了整数，而且进行四舍五入处理。ROUND(2.53,1)返回的结果是2.5，ROUND(2.55,1)返回的结果是2.6。这两个数都保留了小数点后一位，而且进行了四舍五入处理。
结果显示，TRUNCATE(2.53,1)和TRUNCATE(2.55,1)的结果都为2.5。结果都保留到小数点后一位。但没有进行四舍五入，而是直接截断的。
！！！ROUND(x)和ROUND(x,y)可以对数字进行四舍五入处理。前者进位成整数，后者进位成指定长度的小数。TRUNCATE(x,y)直接将小数按照指定长度进行截断，不进行任何四舍五入的处理。因此，在选择这两种函数的时候一定特别注意。
2.6 符号函数sign()
SIGN(x)函数返回x的符号，x是负数、0、正数分别返回-1、0和1。
下面将演示SIGN(x)函数的使用。
2.7 幂运算函数POW(x,y)、POWER(x,y)和EXP(x)
POW(x,y)和 POWER(x,y)这两个函数计算x的y次方，即x^y;EXP(x)函数计算e的x次方，即e^x。
下面将演示POW(x,y)、POWER(x,y)和EXP(x)3个函数的使用。
2.8 对数运算函数LOG(x)和 LOG10(x)
LOG(x)函数计算x的自然对数;LOG10(x)函数计算以10为底的对数。其中，EXP(x)和LOG(x)这两个函数互为反函数。
下面将演示LOG(x)和 LOG10(x)两个函数的使用。
2.9 角度与弧度相互转换的函数RADIANS(x)和 DEGREES(x)
RADIANS(x)函数将角度转换为弧度;DEGREES(x)函数将弧度转换为角度。这两个函数互为反函数。
下面将演示RADIANS(x)函数和 DEGREES(x)函数的使用。
2.10 正弦函数SIN(x)和反正弦函数ASIN(x)
SIN(x)函数用来求正弦值，其中x是弧度;ASIN(x)函数用来求反正弦值。ASIN(x)中x的取值必须在-1～1之间。否则返回的结果将会是NULL。
下面将演示SIN(x)、ASIN(x)两个函数的使用。
因为ASIN(2)中的参数都不在-1～1之间，所以结果返回的是NULL。
2.11 余弦函数COS(x)和反余弦函数ACOS(x)
COS(x)函数用来求余弦值，其中x是弧度;ACOS(x)函数用来求反余弦值。COS(x)和ACOS(x)互为反函数。并且，ACOS(x)中x的取值必须在-1~1之间。否则返回的结果将会是NULL。
下面将演示COS(x)和 ACOS(x)两个函数的使用。
因为ACOS(-2)中的参数都不在-1~1之间，所以结果返回的是NULL。
ASIN(x)和ACOS(x)中x的取值必须在-1~1之间。因为ASIN(x)和ACOS(x)分别是SIN(x)和COS(x)的反函数，而SIN(x)和COS(x)函数的结果的范围是-1~1。如果ASIN(x)和ACOS(x)中x取值不在-1~1之间，那么返回的结果将会是NULL。
2.12 正切函数、反正切函数和余切函数
TAN(x)函数用来求正切值，其中x是弧度;ATAN(x)和ATAN2(x)用来求反正切值;COT(x)函数用来求余切值。TAN(x)与ATAN(x)、ATAN2(x)互为反函数。而且TAN(x)返回值是COT(x)返回值的倒数。
下面将演示TAN(x)、ATAN(x)、ATAN2(x)和COT(x)4个函数的使用。
3、字符串函数
字符串函数是MySQL中最常用的一类函数。字符串函数主要用于处理表中的字符串。字符串函数包括求字符串长度、合并字符串、在字符串中插入子串和大小字母之间切换等函数。
3.1 计算字符串字符数的函数和字符串长度的函数
CHAR_LENGTH(s)函数计算字符串s的字符数;LENGTH(s)函数计算字符串s的长度。
下面将演示CHAR_LENGTH(s)函数和LENGTH(s)函数的使用。
字符串“beijing”共有7个字符，但是占用的空间是8个字节。因为，每个字符串都是以0结束的，\0占用一个字节的空间。LENGTH(s)函数计算字符串s 的长度，这个长度是指字符数，而不是指占用的空间。因此字符串“beijing”的长度为7。
3.2 合并字符串的函数CONCAT(s1,s2,...)和CONCAT_WS(x,s1,s2,.….)
CONCAT(s1,s2,...)函数和CONCAT_WS(x,s1,s2,...)函数都可以将s1、s2等多个字符串合并成一个字符串。但CONCAT_WS(x,s1,s2,...)可以将各字符串直接用参数x隔开。
下面将演示CONCAT(s1,s2..)函数和CONCAT_WS(x,s1,s2,....)函数的使用。
CONCAT(s1,s2,...)函数和CONCAT_WS(x,s1,s2,...)函数都是用来合并字符串，这两个函数在操作字符串时非常有用。例如，为了让空格显示更加明显，可以在空格两边加上“+”。这可以使用CONCAT('+,' ', +')的方式组合起来，显示出来就会是“+ +”，这样空格看起来更加明显。
3.3 替换字符串的函数INSERT(s1,x,len,s2)
INSERT(s1,x,len,s2)函数将字符串s1中x位置开始长度为len的字符串用s2替换。【示例13-15】下面将演示INSERT(s1,x,len,s2)函数的使用。
3.4 字母大小写转换函数
UPPER(s)函数和UCASE(s)函数将字符串s的所有字母变成大写字母;LOWER(s)函数和 LCASE(s)函数将字符串s 的所有字母变成小写字母。
下面将演示UPPER(s)、UCASE(s)、LOWER(s)和 LCASE(s)4个函数的使用。
3.5 获取指定长度的字符串的函数LEFT(s,n)和 RIGHT(s,n)
LEFT(s,n)函数返回字符串s的前n个字符;RIGHT(s,n)函数返回字符串s 的后n个字符。
下面将演示LEFT(s,n)函数和RIGHT(s,n)函数的使用。
3.6 填充字符串的函数LPAD(s1,len,s2)和 RPAD(s1,len,s2)
LPAD(s1,len,s2)函数将字符串 s2填充到s1 的开始处，使字符串长度达到 len;RPAD(s1,len,s2)函数将字符串s2填充到s1的结尾处，使字符串长度达到len。
下面将演示LPAD(s1,len,s2)函数和 RPAD(s1,len,s2)函数的使用。
3.7 删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)
LTRIM(s)函数将去掉字符串s开始处的空格;RTRIM(s)函数将去掉字符串s结尾处的空格;TRIM(s)函数将去掉字符串s开始处和结尾处的空格。
下面将演示LTRIM(s)、RTRIM(s)和 TRIM(s)3个函数的使用。使用的字符串是'me'。该字符串的开头和结尾各有一个空格。因为空格不好显示，所以使用CONCAT(）函数来将字符串与‘+'连接起来。
3.8 删除指定字符串的函数TRIM(s1 FROM s)
TRIM(s1 FROM s)函数将去掉字符串s 中开始处和结尾处的字符串s1。
下面将演示 TRIM(s1 FROM s)函数的使用。
结果显示，字符串“ababddddabddab”最前端和最后面的字符串“ab”去掉。而中间的“ab”还保留着。
通常，数据库中数据最好不要以空格开头或结尾。除非有特殊需要，例如，存储一篇文章时，需要使用空格调整格式。在输入字符串数据时，最好使用TRIM(s)去掉字符串开始和结束部分的空格。如果需要过滤掉某些敏感字符，可以使用TRIM(s1 FROM s)函数将指定字符过滤掉。
3.9 重复生成字符串的函数REPEAT(s,n)
REPEAT(s,n)函数将字符串s重复n次。
下面将演示REPEAT(s,n)函数的使用。
3.10 空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)
SPACE(n)函数返回n个空格;REPLACE(s,s1,s2)函数将字符串s2替代字符串s中的字符串s1。
下面将演示SPACE(n)和REPLACE(s,s1,s2)两个函数的使用。
3.11 比较字符串大小的函数STRCMP(s1,s2)
STRCMP(s1,s2)函数用来比较字符串s1和 s2。如果s1大于s2，结果返回1;如果s1等于s2，结果返回0;如果s1小于s2，结果返回-1。
下面将演示STRCMP(s1,s2)函数的使用。
3.12 获取子串的函数SUBSTRING(s,n,len)和 MID(s,n,len)
SUBSTRING(s,n,len)函数和MID(s,n,len)函数从字符串s的第n个位置开始获取长度为len的字符串。
下面将演示SUBSTRING(s,n,len)函数和 MID(s,n,len)函数的使用。
3.13 匹配子串开始位置的函数
LOCATE(s1,s)、POSITION(s1 IN s)和 INSTR(s,s1)3个函数从字符串s中获取s1的开始位置。
下面将演示LOCATE(s1,s)、POSITION(s1 IN s)和 INSTR(s,s1)3个函数的使用。
结果显示，s字段的值中“jin”的起始位置为4。
3.14 字符串逆序的函数REVERSE(s)
REVERSE(s)函数将字符串s的顺序反过来。
下面将演示REVERSE(s)函数的使用。
3.15 返回指定位置的字符串的函数
ELT(n,s1,s2,...)函数返回第n个字符串。
下面将演示ELT(n,s1,s2,...)函数的使用。
3.16 返回指定字符串位置的函数FIELD(s,s1,s2,...)
FIELD(s,s1,s2,...)函数返回第一个与字符串s匹配的字符串的位置。
下面将演示FIELD(s,s1,s2,...)函数的使用。
结果显示，“he”是('me', 'my'; 'he','she')中的第3个字符串。
3.17 返回子串位置的函数FIND_IN_SET(s1,s2)
FIND_IN_SET(s1,s2)函数返回在字符串s2中与s1匹配的字符串的位置。其中，字符串s2中包含了若干个用逗号隔开的字符串。
下面将演示FIND_IN_SET(s1,s2)函数的使用。
结果显示，字符串“like”在字符串“i,like,bei,jing”的第二个位置上。
3.18 选取字符串的函数MAKE_SET(x,s1,s2,..….)
MAKE_SET(x,s1,s2,...)函数按x的二进制数从s1,s2.....n中选取字符串。例如12的二进制是1100。这个二进制数从右到左的第3位和第4位是1，所以选取s3和 s4。
下面将演示MAKE_SET(x,s1,s2,...)函数的使用。
因为11的二进制数是1011，从右到左的第1位、第2位和第4位是1，所以结果选取a、b和d;因为7的二进制数是111，从右到左的第1位、第2位和第3位是1，所以结果选取a、b和 c。
！！！使用MAKE_SET(x,s1,s2,...）函数时一定要特别注意。该函数是按照x所指定的顺序查找指定字符串。数据库系统会先将x转换为二进制数，然后选取位数为1的位置对应的字符串。读取二进制数的顺序是从右到左的，最右边的是第一位。
4、日期和时间函数
日期和时间函数是MySQL中另一类最常用的函数。日期和时间函数主要用于处理表中的日期和时间数据。日期和时间函数包括获取当前日期的函数、获取当前时间的函数、计算日期的函数、计算时间的函数等。
4.1 获取当前日期的函数和获取当前时间的函数
CURDATE()和CURRENT_DATE()函数获取当前日期;CURTIME() 和CURRENT_TIME()函数获取当前时间。
下面将演示CURDATE()、CURRENT_DATE()、CURTIME()和 CURR-ENT_TIME()4个函数的使用。
4.2 获取当前日期和时间的函数
Now（)、CURRENT_TIMESTAMP()、LOCALTIME()和 SYSDATE()等4个函数都用来获取当前的日期和时间。这四个函数表示相同的含义。
下面将演示 NOW()、CURRENT_TIMESTAMP()、·LOCALTIME()和SYSDATE（）4个函数的使用。
结果显示，这4个函数返回的结果都是“2021-11-20 09:19:02”。这正是当前的日期和时间。除了4个函数以外，LOCALTIMESTAMP()函数也可以获取当前时间和日期。其运行结果和上面四个函数是一样的。
NOW（)、CURRENT_TIMESTAMP()、LOCALTIME()、SYSDATE()和LOCALTIMESTAMP（）这几个函数都可以获取系统当前日期和时间。而且，显示时间格式也是一样的。通常情况下都是使用NOW（）函数。
4.3 UNIX时间戳函数
UNIX_TIMESTAMP()函数以UNIX 时间戳的形式返回当前时间﹔UNIX_TIMESTAMP(d)函数将时间d 以 UNIX时间戳的形式返回;FROM_UNIXTIME(d)函数把UNIX 时间戳的时间转换为普通格式的时间。UNIX_TIMESTAMP(d)函数和FROM_UNIXTIME(d)互为反函数。
下面将演示 UNIX_TIMESTAMP()、UNIX_TIMESTAMP(d)和 FROM_UNIXTIME(d)3个函数的使用。
4.4 返回UTC日期的函数和返回UTC时间的函数
UTC_DATE()函数返回UTC日期;UTC_TIME()函数返回UTC 时间。其中，UTC是Universal Coordinated Time的缩写，也就是国际协调时间。
下面将演示UTC_DATE()函数和UTC_TIME()函数的使用。
结果显示，返回的日期是一样的，返回的时间相差几个时区。
4.5 获取月份的函数MONTH(d)和 MONTHNAME(d)
MONTH(d)函数返回日期d中的月份值，其取值范围为1～12;MONTHNAME(d)函数返回日期d 中的月份的英文名称，如l January,February等。其中，参数d可以是日期和时间，也可以是日期。
下面将演示MONTH(d)函数和 MONTHNAME(d)函数的使用。
结果显示，MONTH(d)函数返回了数字表示的月份;MONTHNAME(d)函数返回了月份的英文名。
4.6 获取星期的函数DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)
DAYNAME(d)函数返回日期d是星期几，显示其英文名，如Monday,Tuesday等;DAYOFWEEK(d)函数也返回日期d是星期几，1表示星期日，2表示星期一，依次类推;WEEKDAY(d)函数也返回日期d是星期几，0表示星期一，1表示星期二，依次类推。其中，参数d可以是日期和时间，也可以是日期。
下面将演示DAYNAME(d)、DAYOFWEEK(d)和 WEEKDAY(d)函数的使用。
结果显示,2021-11-20正好是星期六,DAYNAME(d)返回值是Saturday; DAYOFWEEK(d)返回值是7，表示星期六;WEEKDAY(d)返回值是5，表示星期六。
！！！DAYOFWEEK(d)和WEEKDAY(d)函数都是用数字表示星期，但是这两者的表示方法有点不同。DAYOFWEEK(d)的值是1~7，1表示星期日，2表示星期一，依次类推。而WEEKDAY(d)的值是0~6,0表示星期一,1表示星期二，依次类推。使用时一定要注意这两者的区别。
4.7 获取星期数的函数WEEK(d)和WEEKOFYEAR(d)
WEEK(d)函数和WEEKOFYEAR(d)函数都是计算日期d是本年的第几个星期。返回值的范围是1～53。
下面将演示WEEK(d)函数和WEEKOFYEAR(d)函数的使用。
结果显示，这两个函数返回的结果是一样的。而且，参数d可以是日期和时间，也可以只有日期。
4.8 获取天数的函数DAYOFYEAR(d)和 DAYOFMONTH(d)
DAYOFYEAR(d)函数日期d是本年的第几天;DAYOFMONTH(d)函数返回计算日期d是本月的第几天。
下面将演示DAYOFYEAR(d)函数和 DAYOFMONTH(d)函数的使用。
4.9 获取年份、季度、小时、分钟和秒钟的函数
YEAR(d)函数返回日期d中的年份值;QUARTER(d)函数返回日期d是本年第几季度，值的范围是1~4;HOUR(t)函数返回时间t中的小时值;MINUTE(t)函数返回时间t中的分钟值;SECOND(t)函数返回时间t中的秒钟值。
下面将演示YEAR(d)、QUARTER(d)、HOUR(t)、MINUTE(t)和SECOND(t)5个函数的使用。
4.10 获取日期的指定值的函数EXTRACT(type FROM d)
EXTRACT(type FROM d)函数从日期d中获取指定的值。这个值是什么由type的值决定。type的取值可以是YEAR、MONTH、DAY、HOUR、MINUTE和SECOND。如果type的值是YEAR，结果返回年份值;MONTH返回月份值;DAY返回是几号;HOUR返回小时值;MINUTE返回分钟值;SECOND返回秒钟值。
下面将演示EXTRACT(type FROM d)函数的使用。
结果显示，type值为YEAR时，结果从2021-11-20返回了年份2021;type值为MINUTE时，返回了分钟值。
处理日期和时间数据时，EXTRACT(type FROM d)函数非常有用。例如，学生表中将学生的出生年月存储在birth 字段中。但是现在需要查询学生的年龄，那么需要用现在的年份减去学生的出生年份。这就需要使用EXTRACT(YEAR FROM birth)中分离出学生的出生年份。
4.11 时间和秒钟转换的函数
TIME_TO_SEC(t)函数将时间t转换为以秒为单位的时间;SEC_TO_TIME(s)函数将以秒为单位的时间s转换为时分秒的格式。TIME_TO_SEC(t)和SEC_TO_TIME(s)互为反函数。
下面将演示TIME_TO_SEC(t)函数和SEC_TO_TIME(s)函数的使用。
结果显示，时间为10:03:13。TIME_TO_SEC(t)将时间t变化以秒为单位的时间。因为10*60*60+3*60+13 刚好等于36193。SEC_TO_TIME(s)正好是 TIME_TO_SEC(t)的逆运算。
4.12 计算日期和时间的函数
1. TO_DAYS(d)、FROM_DAYS(n)和DATEDIFF(d1,d2)函数
TO_DAYS(d)函数计算日期d与0000年1月1日的天数;FROM_DAYS(n)函数计算从0000年1月1日开始n天后的日期;DATEDIFF(d1,d2)函数计算日期d1与d2之间相隔的天数。
下面将演示TO_DAYS(d)、FROM_DAYS(n)和DATEDIFF(d1,d2)3个函数的使用。
2. ADDDATE(d,n)、SUBDATE(d,n)、ADDTIME(t,n)和SUBTIME(t,n)函数
ADDDATE(d,n)函数返回起始日期d加上n天的日期;SUBDATE(d,n)函数返回起始日期d减去n天的日期;ADDTIME(t,n)函数返回起始时间t加上n秒后的时间;SUBTIME(t,n)函数返回起始时间t减去n秒后的时间。
下面将演示ADDDATE(d,n)、SUBDATE(d,n)、ADDTIME(t,n)和SUBTIME(t,n)4个函数的使用。
3. ADDDATE(d,INTERVAL expr type)和DATE_ADD(d,INTERVAL expr type)函数
ADDDATE(d,INTERVAL expr type)函数和DATE_ADD(d,INTERVAL expr type)函数返回起始日期d加上一个时间段后的日期;SUBDATE(d,INTERVAL expr type)函数返回起始日期d减去一个时间段后的日期。上面3个函数的expr是表示时间段长度的表达式。该表达式与后面的间隔类型type对应。
下面使用ADDDATE(d,INTERVAL expr type)函数计算【一年零一个月后】的日期和时间。
时间间隔用的是YEAR_MONTH; expr表达式中年和月之间用空格隔开;ADDDATE()函数返回的结果是d中日期一年零一个月以后的日期和时间。
MySQL中还可以使用负数来指定时间。
下面使用ADDDATE(d,INTERVAL expr type)函数计算【一年零一个月前】的日期和时间。
结果返回了d中日期一年零一个月以前的日期和时间。可以根据这两个示例练习一下日期间隔类型。由于DATE_ADD(d,INTERVAL expr type)函数和SUBDATE(d,INTERVAL expr type)函数的使用方法一样，可以根据ADDDATE(d,INTERVAL exprtype)函数的使用方法来练习。
！！！ADDDATE(d,INTERVAL expr type)和DATE_ADD(d,INTERVAL expr type)这些函数的type必须在上述的表中。而且，type必须是表中的某一项，不能是其中几项的组合。使用这类函数时，一定要注意type的选择。
4.13 将日期和时间格式化的函数
1. DATE_FORMAT(d,f)函数
DATE_FORMAT(d,f)函数按照表达式f的要求显示日期d。表达式f指定了显示的格式。
下面用与“Jan 1st 1986”一样的形式来显示t4表中d字段中的日期。
下面计算t4表中d字段中的日期是一年中的第几天、星期几。
结果显示，2021-11-20是本年的第324天。这一天是星期六。其中，DAY和 WEEK是SELECT语句中为字段取的别名。
2.TIME_FORMATE(t,f)函数
TIME_FORMATE(t,f)函数按照表达式f的要求显示时间t。表达式f指定了显示的格式。时间格式见上表。因为TIME_FORMATE(t,f)只处理时间，所以f只使用时间格式。
下面将t4表中t字段中的时间用12小时制来显示。
3. GET_FORMAT(type,s)函数
GET_FORMAT(type,s)函数根据字符串s获取 type类型数据的显示格式。其中，参数d的取值包括DATE、DATETIME 和TIME;s参数的取值包括 EUR、USA、JIS、ISO和INTERNAL。
使用GET_FORMAT(type,s)函数只会返回一个格式字符串。
下面SELECT语句中只使用GET_FORMAT(type,s)函数。
结果显示，GET_FORMAT(DATETIME,'ISO')返回的字符串是“%Y-%m-%d%H:%i:%s ”; GET_FORMAT(DATE,'EUR')返回的字符串是“%d.%m.%Y ”;GET_FORMAT(TIME,USA')返回的字符串是“%h:%i:%s %p”。
GET_FORMAT(type,s)函数可以与DATE_FORMAT(d,f)函数和TIME_FORMAT(t,f)函数一起使用。GET_FORMAT(type,s)可以替代那两个函数中的f参数。
下面在 DATE_FORMAT(d,f)函数和 TIME_FORMAT(t,f)函数中使用GET FORMAT(type,s)函数。
GET_FORMAT(DATETIME，s)一般用来为DATETIME类型的数据提供格式字符串。但也可以为DATE类型和TIME类型的数据来提供格式。这种情况下，DATE数据会变成DATETIME类型的数据。时间部分为0时0分0秒，显示格式由函数返回的格式字符串决定。同理，TIME类型也会变成DATETIME类型。日期部分为0年0月0日。
5、条件判断函数
条件判断函数用来在SQL语句中进行条件判断。根据是否满足判断条件，SQL语句执行不同的分支。例如,从员工表中查询员工的业绩。如果业绩高于指定值n,则输出good;否则，输出bad。下面是各种条件判断函数的表达式、作用和使用方法。
5.1 IF(expr,v1,v2)函数
IF(expr,vl,v2)函数中，如果表达式expr成立，返回结果v1;否则，返回结果v2。【示例13-51】下面从 t6中查询学号(id)，分数（grade)。并且，分数大于等于60，显示PASS:否则，显示FAIL。SELECT语句如下:
结果显示，grade的值为90和60的记录后面显示PASS。其他后面显示为FAIL。
5.2 IFNULL(v1,v2)函数
IFNULL(v1,v2)函数中，如果v1的不为空，就显示v1的值;否则就显示v2的值。
下面从 t6中查询学号(id)，分数(grade)。如果分数不为NULL，显示分数，否则，显示NO GRADE。SELECT语句如下:
5.3 CASE函数
1. CASE WHEN expr1 THEN v1 [WHEN expr2 THEN v2...] [ELSE vn] END
CASE WHEN exprl THEN v1 [WHEN expr2 THEN v2...] [ELSE vn] END函数中，CASE表示函数开始，END表示函数结束。如果表达式expr1成立时，返回v1的值。如果表达式expr2成立时，返回v2的值。依次类推，最后遇到ELSE时，返回vn 的值。
下面从t6中查询学号(id),分数( grade)。如果分数人于60,返回·GOOD';如果分数为60，返回‘PASS';其余分数返回“FAIL”。SELECT语句如下:
2.CASE expr WHEN e1 THEN v1 [WHEN e2 THEN v2...] [ELSE vn] END
CASE expr WHEN e1 THEN v1 [WHEN e2 THEN v2...] [ELSE vn] END 函数中，如果表达式expr取值等于e1时,返回v1的值。如果表达式expr取值等于e2时，返回v2的值。依次类推，最后遇到ELSE时，返回vn的值。CASE表示函数开始，END表示函数结束。
下面从 t6中查询学号(id)，分数(grade)。如果分数等于60，返回GOOD;如果分数为60,返回PASS;如果分数为50,返回FAIL;其余分数返回NO GRADE。SELECT语句如下:
6、系统信息函数
系统信息函数用来查询MySQL 数据库的系统信息。例如，查询数据库的版本，查询数据库的当前用户等。下面是各种系统信息函数的符号和作用，如下表所示。
6.1 获取MySQL版本号、连接数和数据库名的函数
VERSION()函数返回数据库的版本号;CONNECTION_ID()函数返回服务器的连接数，也就是到现在为止 MySQL服务的连接次数;DATABASE()和 SCHEMA()返回当前数据库名。
下面将演示 VERSION()、CONNECTION_ID)、DATABASE()和SCHEMA()4个函数的用法。
VERSION()函数返回的版本号为“5.5.27";CONNECTION_ID)返回的连接数为2,DATABASE()和 SCHEMA()返回的当前数据库名是test。
6.2 获取用户名的函数
USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()和 CURRENT_USER这几个函数可以返回当前用户的名称。
下面查询当前用户的用户名。
结果显示，当前用户的用户名为root。localhost是主机名。因为服务器和客户端在一台机器上，所以服务器的主机名为localhost。用户名和主机名之间用符号“@”进行连接。
6.3 获取字符串的字符集和排序方式的函数
CHARSET(str)函数返回字符串str 的字符集，一般情况这个字符集就是系统的默认字符集;COLLATION(str)函数返回字符串 str的字符排列方式。
下面查看字符串‘aa'的字符集和字符串排序方式.
6.4 获取最后一个自动生成的ID值的函数
LAST_INSERT_ID()函数返回最后生成的AUTO_INCREMENT值。
下面测试LAST_INSERT_ID()函数的作用。
首先创建一个如下表：
查询最后一个auto_increment()值
结果显示，LAST_INSERT_ID()返回的结果为3。因为一共插入了3条记录。记录的id值从1开始增加，最后一条记录的id值为3。这说明LAST_INSERT_ID()返回最后生成的AUTO_INCREMENT值。
【一个小插曲】
按如上图所示的方式插入三个数值，id也是会自增到3，但是
last_insert_id（）返回的结果为1；
当我继续插入一个数值时，
当我继续插入，
到这里，id数值已经自增到6了，但是
所以，以防返回不对的数值最好就一条条插入数值吧
7、加密函数
加密函数是MySQL中用来对数据进行加密的函数。因为数据库中有些很敏感的信息不希望被其他人看到，就应该通过加密的方式来使这些数据变成看似乱码的数据。例如用户的密码，就应该经过加密。
7.1 加密函数PASSWORD(str)
PASSWORD(str)函数可以对字符串 str进行加密。一般情况下，PASSWORD(str)函数主要是用来给用户的密码加密的。
下面使用PASSWORD(str)函数为字符串‘abcd'加密。
结果显示，字符串“abcd”加密后的结果是“*A154C52565E9E7F94BFCO8A1FE702624ED8EFFDA”。PASSWORD(str)函数加密是不可逆的。
！！！PASSWORD(str)函数经常用来给密码加密。MySQL用户需要设置密码，用户不能将未加密的密码直接存储到MySQL 的user表中。因为登录MySQL数据库时，数据库系统会将你输入的密码先通过PASSWORD(str)函数加密,然后与数据库中的密码进行比较，匹配成功后才可以登录。
7.2 加密函数MD5(str)
MD5(str)函数可以对字符串 str进行加密。MD5(str)函数主要对普通的数据进行加密。
下面使用MD5(str)函数为字符串‘abcd'加密。
7.3 加密函数ENCODE(str,pswd_str)
ENCODE(str,pswd_str)函数可以使用字符串pswd_str来加密字符串 str。加密的结果是一个二进制数，必须使用BLOB类型的字段来保存它。
下面使用字符串‘aa'来加密字符串‘abcd'，将加密后的数据存入表b的code字段中。code字段是BLOB类型的。
（这里我没搞清楚为什么加密后的效果是这样的）
下面直接使用字符串‘aa'来加密字符串‘abcd'：
7.4 解密函数DECODE(crypt_str,pswd_str)
DECODE(crypt_str,pswd_str)函数可以使用字符串pswd_str来为crypt_str解密.crypt_str是通过ENCODE(str,pswd_str)加密后的二进制数据。字符串pswd_str应该与加密时的字符串pswd_str是相同的。
下面使用DECODE(crypt_str,pswd_str)为 ENCODE(str,pswd_str)加密的数据解密。
8、其他函数
MySQL中除了上述函数以外，还包含了很多函数。例如FORMAT(x,n)函数用来格式化数字x，INET_ATON()函数可以将I转换为数字。
8.1 格式化函数FORMAT(x,n)
FORMAT(x,n)函数可以将数字x进行格式化，将x保留到小数点后n位。这个过程需要进行四舍五入。例如FORMAT(2.356,2)返回的结果将会是2.36;FORMAT(2.353,2)返回的结果将会是2.35。
下面使用FORMAT(x,n)函数来将235.3456和235.3454进行格式化，都保留到小数点后3位。
结果显示，235.3456格式化后的结果是235.346;235.3454格式化后的结果是235.345。这个数都保留到小数点后3位，而且都进行了四舍五入处理。
！！！FORMAT(x,n)函数可以将x保留到小数点后n位。在格式化过程中需要进行四舍五入的操作。FORMAT(x,n)函数与ROUND(x,y)函数相似。ROUND(x,y)函数返回x保留到小数点后y位的值，截断时需要进行四舍五入处理。
8.2 不同进制的数字进行转换的函数
ASCII(s)返回字符串s的第一个字符的ASCII码; BIN(x)返回x的二进制编码;HEX(x)返回x的十六进制编码;OCT(x)返回x的八进制编码;CONV(x,fl,f2)将x 从fl进制数变成f2进制数。
下面返回字符串‘ABC'的第一个字母的ASCII码;将十进制数28分别变成二进制数、十六进制数和八进制数;将28当作十六进制数，将其变成八进制数。
结果显示，ASCII('ABC')的返回值是65，这正是字母A的ASCII码;十进制数28的二进制码是11100，十六进制码是1C，八进制码是34。CONV(28,10,2)中，指定28为十进制数，然后将其变成二进制数，返回结果是11100;CONV(28,16,2)中，指定28为十六进制数，然后将其变成二进制数，返回结果是101000;CONV(28,16,8)中，指定28为十六进制数，然后将其变成八进制数，返回的结果是50。
8.3 IP地址与数字相互转换的函数
INET_ATON(IP)函数可以将IP地址转换为数字表示;INET_NTOA(n)函数可以将数字n转换成IP的形式。其中，INET_ATON(IP)函数中P值需要加上引号。这两个函数互为反函数。
下面演示INET_ATON(IP)函数和 INET_NTOA(n)函数的使用。
结果显示，两函数互为反函数。INET_ATON('59.65.226.15')将IP值59.65.226.15变成994173455;INET_NTOA(994173455)将数值994173455变成IP值59.65.226.15。
8.4 加锁函数和解锁函数
GET_LOCT(name,time)函数定义一个名称为name、持续时间长度为time秒的锁。如果锁定成功，返回1;如果尝试超时，返回0;如果遇到错误，返回 NULL。RELEASE_LOCK(name)函数解除名称为name的锁。如果解锁成功，返回1;如果尝试超时，返回0:如果解锁失败，返回NULL，IS_FREE_LOCK(name)函数判断是否使用名为name的锁。如果使用，返回0;否则，返回1。
下面增加一个名为MYSQL的锁，持续时间是10秒。然后判断这个锁是否加上，最后解除锁定。
结果显示，GET_LOCK(MYSQL',10)返回结果是1，说明成功的加上了一个名为MYSQL且持续时间为10秒的锁;IS_FREE_LOCK(MYSQL')返回的结果是0，说明名为MYSQL的锁已经存在，RELEASE_LOCK(MYSQL')返回的结果是1，说明解锁成功。
！！！当执行RELEASE_LOCK()、一个新的GET_LOCK()或者线程终止，那么之前加上的锁都自动解除。尤其值得注意的是在加上一个新锁后，原来的锁就会解除。例如，GET_LOCK('a',20)加上一个名为a 的锁，然后再通过GET_LOCK(b',20)创建b锁。那么，a锁就自动解除了。
8.5 重复执行指定操作的函数
BENCHMARK(count,expr)函数将表达式expr重复执行count次，然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度。
下面返回系统的时间是100000次，计算使用的时间。
结果显示，BENCHMARK(100000,NOW))返回的结果是0。这并不是说执行过程没有花费时间，而是时间很短，可以忽略不计。
8.6 改变字符集的函数
CONVERT(s USING cs)函数将字符串s 的字符集变成cs。
下面将字符串‘ABC’的字符集变成gbk。
结果显示，ABC原来的字符集是latin1。使用CONVERT('ABC' USING GBK)后，ABC的字符集变成了gbk。
8.7 改变字段数据类型的函数
CAST(x AS type)和CONVERT(x,type)这两个函数将x变成type类型。这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER这些类型起作用。但两种方法只是改变了输出值的数据类型，并没有改变表中字段的类型。
下面t4表中的dt字段为DATETIME类型，将其变为DATE类型，或者TIME类型。
9、表中birth字段存的出生日期，如何来计算年龄?
年龄是通过当前年份减去出生的年份来计算的。但是birth字段中也含年、月和日，这就必须从birth字段中过滤出出生的年份。MySQL中提供了YEAR()函数用来获取日期中的年份。如 YEAR(‘2008-08-08')的返回结果是2008。所以，可以通过YEAR(birth)来获取出生的年份。可以通过 YEAR(NOW))或YEAR(CURRENT_DATE(O)来获取当前的年份。这两者相减就可以获得年龄了。
10、如何改变字符串的字符集?
在安装 MySQL时就已经设置了数据库的字符编码。字符串的字符集与字符编码是一个意思。MySQL中可以通过重新配置字符集来修改字符集。也可以在 MySQL的安装路径下修改my.ini。将default-character-set的值改变来修改字符集。上面这两种方式将改变整个数据库的字符集。如果只想改变某个字符串的字符集，可以使用CONVERT(s USING cs)函数。该函数可以将字符串s 的字符集变成cs。
11、用户的密码应该怎么加密?
在MySQL中可以使用PASSWORD(str)函数来给密码加密。这个密码是不可逆的，即使有人取得了加密后的数据，也不能通过解密来获取密码值。系统会将用户注册时输入的密码通过PASSWORD(str)函数来加密。将加密后的密码存入表中。用户登录时，系统会将用户再次输入的密码用PASSWORD(str)函数加密，将加密后的数据与表中的数据进行比较。如果相等，说明用户输入的密码是正确的。
12、其他函数的补充
12.１根据分隔符获取字符串的字串 substring_index(s,d,n)
substring_index(s,d,n)，其中s指要截取的源字符串，d表示delimiter指的是根据什么样的分隔符，n表示number指的是要第几个分隔符左边还是右边的字符串
函数返回值：返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。
如果 number 是正数，返回第 number 个字符左边的字符串。
如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。
可以看到，字符串的个数不是从0开始的，分隔符要用引号引着。
【注意理解】
12.2 计算两日期的时间差 timestampdiff(u,d1,d2)
参数：
返回值：
- 返回日期或日期时间表达式datetime_expr1和datetime_expr2之间的整数差。 结果的单位由unit参数指定。
1、计算两个时间相隔多少分钟
2、计算两个时间相隔多少天
3、计算两个时间相隔多少月
4、计算两个时间相隔多少年
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
3
0- 0
已为社区贡献4条内容
所有评论(0)