MySQL函数

聚合函数

聚合函数是 max min avg 等

8.1 MySQL函数简介

各类函数从功能方面主要分为以下几类：
数学函数
字符串函数
日期和时间函数
条件判断函数
系统信息函数和加密函数
其他函数

8.2 数学函数

8.2.1 绝对值函数ABS(x)和返回圆周率的函 数PI()

绝对值函数：ABS(x)
圆周率函数：PI()

8.2.2 平方根函数SQRT(x)和求余函数MOD(x,y)

平方根函数：SQRT(x)
求余函数：MOD(x,y)

8.2.3 获取整数的函数CEIL(x)、CEILING(x)和

 FLOOR(x)

获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)

8.2.4 获取随机数的函数RAND()和RAND(x)

获取随机数的函数RAND()和RAND(x)

8.2.5 四舍五入函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)

8.2.6 符号函数SIGN(x)

SIGN(x)返回参数的符号，x的值为负、零或正时返回结果依次为-1、0或1。

8.2.7 幂运算函数POW(x,y)、POWER(x,y)和EXP(x)

POW(x,y)和POWER(x,y) 返回x的y次乘方的结果值。
EXP(x)返回e的x乘方后的值。

8.2.8 对数运算函数LOG(x)和LOG10(x)

LOG(x)返回x的自然对数，x相对于基数e的对数。对数定义域不能为负数。
LOG10(x)返回x的基数为10的对数。

8.2.9 角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)

RADIANS(x)将参数x由角度转化为弧度。
DEGREES(x)将参数x由弧度转化为度。

8.2.10 正弦函数SIN(x)和反正弦函数ASIN(x)

SIN(x)返回x正弦，其中x为弧度值。
ASIN(x)返回x的反正弦，即正弦为x的值。若x不在-1到1的范围之内，则返回NULL。

8.2.11 余弦函数COS(x)和反余弦函数ACOS(x)

COS(x)返回x的余弦，其中x为弧度值。
ACOS(x)返回x反余弦,即余弦是x的值。若x不在-1到1的范围之内，则返回NULL。

8.2.12 正切函数、反正切函数和余切函数

COS(x)返回x的余弦，其中x为弧度值。
ACOS(x)返回x反余弦,即余弦是x的值。若x不在-1到1的范围之内，则返回NULL。

8.3 字符串函数

8.3.1 计算字符串字符数的函数和字符串长度的函数

CHAR_LENGTH(str)返回值为字符串str的所包含字符个数。一个多字节字符算作一个单字符。
LENGTH(str)返回值为字符串的字节长度，使用utf8编码字符集时，一个汉字是3个字节，一个数字或字母算一个字节。

8.3.2 合并字符串函数

CONCAT(s1,s2,…)、CONCAT_WS(x,s1,s2,…)
CONCAT(s1,s2,…)返回结果为连接参数产生的字符串。
CONCAT_WS(x,s1,s2,…)，CONCAT_WS代表CONCAT With Separator，是CONCAT()的特殊形式。第一个参数x是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。

8.3.3 替换字符串的函数INSERT(s1,x,len,s2)

NSERT(s1,x,len,s2)返回字符串s1，其子字符串起始于x位置和长度被字符串s2取代的len字符。

8.3.4 字母大小写转换函数

LOWER (str)或者LCASE (str)将字符串str中的字母字符全部转换成小写字母。
UPPER(str)或者UCASE(str)将字符串str中的字母字符全部转换成大写字母。

8.3.5 获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)

LEFT(s,n)返回字符串s开始的最左边n个字符。
RIGHT(s,n)返回字符串str最右边len字符。

8.3.6 填充字符串的函数LPAD(s1,len,s2)和

 RPAD(s1,len,s2)

LPAD(s1,len,s2)返回字符串s1,其左边由字符串s2填补到len字符长度。
RPAD(s1,len,s2)返回字符串str，其右边被字符串padstr填补至len字符长度

8.3.7 删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)

LTRIM(s)返回字符串s，字符串左侧空格字符被删除。
RTRIM(s)返回字符串s，字符串右侧空格字符被删除。

8.3.8 删除指定字符串的函数TRIM(s1 FROM s)

TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1。

8.3.9 重复生成字符串的函数REPEAT(s,n)

REPEAT(s,n)返回一个由重复的字符串s组成的字符串，字符串s的数目等于n。

8.3.10 空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)

SPACE(n)返回一个由n个空格组成的字符串。
SPACE(n)返回一个由n个空格组成的字符串。

8.3.11 比较字符串大小的函数STRCMP(s1,s2)

STRCMP(s1,s2)若所有的字符串均相同，则返回0，若根据当前分类次序，第一个参数小于第二个，则返回-1，其它情况返回1。

8.3.12 获取子串的函数SUBSTRING(s,n,len)和

 MID(s,n,len)

SUBSTRING(s,n,len)带有len参数的格式从字符串s返回一个长度同len字符相同的子字符串，起始于位置n。
MID(s,n,len)与SUBSTRING(s,n,len)的作用相同。

8.3.13 匹配子串开始位置的函数

LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str, str1)3个函数作用相同，返回子字符串str1在字符串str中的开始位置。

8.3.14 字符串逆序的函数REVERSE(s)

REVERSE(s)将字符串s反转，返回的字符串的顺序和s字符顺序相反。

8.3.15 返回指定位置的字符串的函数

ELT(N,字符串1,字符串2,字符串3,...，字符串N)若N = 1，则返回值为字符串1，若N=2，则返回值为字符串2，以此类推。若N小于1或大于参数的数目，则返回值为NULL。

8.3.16 返回指定字符串位置的函数FIELD(s,s1,s2,…)

FIELD(s,s1,s2,…)返回字符串s在列表s1,s2,…中第一次出现的位置，在找不到str的情况下，返回值为0。如果str为NULL，则返回值为0。

8.3.17 返回子串位置的函数FIND_IN_SET(s1,s2)

FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号‘,’分开的自符串组成的字符串。如果s1不在s2或s2为空字符串，则返回值为0。如任意一个参数为NULL，则返回值为NULL。

8.3.18 选取字符串的函数MAKE_SET(x,s1,s2,…)

MAKE_SET(x,s1,s2,…)返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1，s2对应比特01以此类推。s1，s2...中的NULL值不会被添加到结果中。

8.4 日期和时间函数

8.4.1 获取当前日期的函数和获取当前时间的函数

CURDATE()和CURRENT_DATE()函数作用相同，将当前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。
计算第一条记录中人的年龄，并计算m_birth字段中的值在那一年中的位置，按照"Saturday October 4th 1997"格式输出问题。这里的m_birth是1970-06-29。

mysql> select year(curdate())-year(m_birth) as age,dayofyear(m_birth) as days,date_format(m_birth,'%W %D %M %Y') as birthdate from me;
+------+------+-----------------------+
| age  | days | birthdate             |
+------+------+-----------------------+
|   51 |  180 | Monday 29th June 1970 |
+------+------+-----------------------+

8.4.2 获取当前日期和时间的函数

CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE()4个函数的作用相同，返回当前日期和时间值，格式为‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS，具体格式根据函数是否用在字符串或数字语境而定。

8.4.3 UNIX时间戳函数

UNIX_TIMESTAMP(date)若无参数调用，则返回一个Unix时间戳（‘1970-01-01 00:00:00’GMT之后的秒数）作为无符号整数。

8.4.4 返回UTC日期的函数和返回UTC时间的函数

UTC_DATE()函数返回当前UTC（世界标准时间）日期值，其格式为‘YYYY-MM-DD’或YYYYMMDD，具体格式取决于函数是否用在字符串或数字语境中。

8.4.5 获取月份的函数MONTH(date)和

 MONTHNAME(date)

MONTH(date)函数返回date对应的月份，范围值从1到12。
MONTHNAME(date)函数返回日期date对应月份的英文全名。

8.4.6 获取星期的函数DAYNAME(d)、

 DAYOFWEEK(d)和WEEKDAY(d)

DAYNAME(d)函数返回d对应的工作日英文名称，例如Sunday，Monday等。
DAYOFWEEK(d)函数返回d对应的一周中的索引（位置）。1表示周日，2表示周一，...，7表示周六)。
WEEKDAY(d)返回d对应的工作日索引。0表示周一，1表示周二，...8表示周日。

8.4.7 获取星期数的函数WEEK(d)和WEEKOFYEAR(d)

WEEK(d)计算日期d是一年中的第几周。
WEEKOFYEAR(d)计算某天位于一年中的第几周，范围是从1到53。

8.4.8 获取天数的函数DAYOFYEAR(d)和

 DAYOFMONTH(d)

8.4.9 获取年份、季度、小时、分钟和秒钟的函数

DAYOFYEAR(d)函数返回d是一年中的第几天，范围是从1到366。
DAYOFMONTH(d)函数返回d 是一个月中的第几天，范围是从1到31。
DAYOFMONTH(d)函数返回d 是一个月中的第几天，范围是从1到31。
QUARTER(date)返回date对应的一年中的季度值，范围是从1到4。
MINUTE(time)返回time对应的分钟数，范围是从0到59。
SECOND(time)返回time 对应的秒数，范围是从0到59。

8.4.10 获取日期的指定值的函数

 EXTRACT(type FROM d)

EXTRACT(type FROM date)函数所使用的时间间隔类型说明符同DATE_ADD()或DATE_SUB()的相同，但它从日期中提取其部分，而不是执行日期运算

8.4.11 时间和秒钟转换的函数

TIME_TO_SEC(time)返回已转化为秒的time参数。转换公式为：小时3600+分钟60+秒。
SEC_TO_TIME(seconds)返回被转化为小时、分钟和秒数的seconds参数值，其格式为‘HH:MM:SS’或HHMMSS，具体格式根据该函数是否用在字符串或数字语境中而定。

8.4.12 计算日期和时间的函数

DATE_ADD(date,INTERVAL expr type)或者ADDDATE(date,INTERVAL expr type) 。
DATE_SUB(date,INTERVAL expr type)或者SUBDATE(date,INTERVAL expr type) 。
ADDTIME(date,expr)
SUBTIME(date,expr)

8.4.13 将日期和时间格式化的函数

DATE_FORMAT(date,format)根据format 指定的格式显示date值。

8.5 条件判断函数

8.5.1 IF(expr,v1,v2)函数

IF(expr, v1, v2)如果表达式expr是TRUE(expr <> 0 and expr <> NULL)，则IF()的返回值为v1；否则返回值则为v2。IF()的返回值为数字值或字符串值，具体情况视其所在语境而定。

8.5.2 IFNULL(v1,v2)函数

IFNULL(v1,v2)假如v1不为NULL，则IFNULL()的返回值为v1，否则其返回值为v2。

8.5.3 CASE函数

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END
CASE WHEN v1 THEN r1 [WHEN v2 THEN r2] ELSE rn] END
下面的语句是使用case进行条件判断。若m_birth<2000，则显示old，大于2000则显示young，else则显示not born.

mysql> select m_birth,case when year(m_birth)<2000 then 'old'
    -> when year(m_birth)>2000 then 'young'
    -> else 'not born'end as status from me;
+---------------------+--------+
| m_birth             | status |
+---------------------+--------+
| 1970-06-29 00:00:00 | old    |
| 2021-10-19 19:44:07 | young  |
+---------------------+--------+

这里的 end as status应该是对这个操作的一个命名。

8.6 系统信息函数

8.6.1 获取MySQL版本号、连接数和数据库名的函数

VERSION()返回指示MySQL服务器版本的字符串。
CONNECTION_ID()返回当前MySQL服务器当前连接的次数，每个连接都有各自的唯一的ID。
DATABASE()和SCHEMA()函数返回使用utf8字符集的默认(当前)数据库名。

8.6.2 获取用户名的函数

USER()
CURRENT_USER
CURRENT_USER()
SYSTEM_USER()
SESSION_USER()

8.6.3 获取字符串的字符集和排序方式的函数

CHARSET(str)返回字符串str自变量的字符集。
COLLATION(str)返回字符串str的字符排列方式。

8.6.4 获取最后一个自动生成的ID值的函数

LAST_INSERT_ID()自动返回最后一个INSERT或UPDATE问询为AUTO_INCREMENT列设置的第一个发生的值。

8.7 MySQL 8.0的新特性1——加密函数

8.7.1 加密函数MD5(str)

MD5(str)为字符串算出一个MD5 128比特校验和。该值以32位十六进制数字的二进制字符串形式返回，若参数为NULL，则会返回NULL。
【例8.97】使用MD5函数加密字符串，输入语句如下：
mysql> SELECT MD5 ('mypwd');

8.7.2 加密函数SHA(str)

SHA(str)从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全。
【例8.98】使用SHA函数加密密码，输入语句如下：
mysql> SELECT SHA('tom123456');

8.7.3 加密函数SHA2(str, hash_length)

SHA2(str, hash_length)使用hash_length作为长度，加密str。hash_length支持的值224、256、384、512和0。其中0等同于256。
【例8.99】使用SHA2加密字符串，输入语句如下：
mysql> SELECT SHA2('tom123456',0) A,sha2('tom123456',256) B\G

8.8 其他函数

8.8.1 格式化函数FORMAT(x,n)

FORMAT(x,n)将数字x的格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0，则返回结果不带有小数部分。

8.8.2 不同进制的数字进行转换的函数

CONV(N, FROM_base, to_base)函数进行不同进制数间转换。

8.8.3 IP地址与数字相互转换的函数

INET_ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。
INET_NTOA(expr)给定一个数字网络地址(4或8比特)，返回作为字符串的该地址的点地址表示。

8.8.4 加锁函数和解锁函数

GET_LOCK(str,timeout)
RELEASE_LOCK(str)
IS_FREE_LOCK(str)
IS_USED_LOCK(str)

8.8.5 重复执行指定操作的函数

BENCHMARK(count,expr)函数重复count次执行表达式expr。

8.8.6 改变字符集的函数

CONVERT(... USING ...)带有USING的CONVERT()函数被用来在不同的字符集之间转化数据。

8.8.7 改变数据类型的函数

CAST(x , AS type)和CONVERT(x, type)函数将一个类型的值转换为另一个类型的值，可转换的type有：BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED。

8.9 MySQL 8.0的新特性2——窗口函数

在MySQL 8.0版本之前，没有排名函数，所以当需要在查询当中实现排名时，必须手写@ 变量，比较麻烦。
在MySQL 8.0版本中，新增了一个窗口函数，用它可以实现很多新的查询方式。窗口函数类似于SUM()、COUNT() 那样的集合函数，但它并不会将多行查询结果合并为一行，而是将结果放回多行当中。也就是说，窗口函数是不需要 GROUP BY 的。

8.10 综合案例——MySQL函数的使用

MySQL函数的使用
本章为读者介绍了大量的MySQL函数，包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统函数、加密函数以及其他函数。读者应该在实践过程中深入了解、掌握这些函数。