1. 内置函数的介绍
不同DBMS的内置函数相差很大。从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。而从处理数据的条数可以分为单行函数、聚合函数(分组函数)
2. 数值函数
2.1 基本函数
函数 | 用法 |
---|
ABS(x) | 返回x的绝对值 |
SIGN(x) | 返回x的符号。正数返回1,负数返回-1,0返回0 |
PI() | 返回圆周率的值 |
CEIL(x),CEILING(x) | 返回大于或等于某个值的最小整数 |
FLOOR(x) | 返回小于或等于某个值的最大整数 |
LEAST(e1, e2, e3……) | 返回列表中的最小值 |
GREATEST(e1, e2, e3……) | 返回列表中的最大值 |
MOD(x, y) | 返回x除以y后的余数 |
RAND() | 返回0~1的随机值 |
RAND(x) | 返回0~1的随机值,其中x的值用作种子值,相同的x值会产生相同的随机数 |
ROUND(x) | 返回一个对x的值进行四舍五入后,最接近于x的整数 |
ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近x的值,并保留到小数点后面y位。例如select ROUND(12.324,-1); 的结果为10 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
SQRT(x) | 返回x的平方根。当x的值为负数时,返回NULL |
2.2 角度与弧度互换函数
函数 | 用法 |
---|
RADIANS(x) | 将角度转化为弧度,其中参数x为角度值。例如select RADIANS(180); 的结果为3.141592653589793 |
DEGREES(x) | 将弧度转化为角度,其中参数x为弧度值。例如select DEGREES(PI()); 的结果为180 |
2.3 三角函数
函数 | 用法 |
---|
SIN(x) | 返回x的正弦值,其中参数x为弧度值 |
ASIN(x) | 返回x的反正弦值,其中x为正弦值,即获取正弦为x的值。如果x的值不在-1到1之间,则返回NULL |
COS(x) | 返回x的余弦值,其中参数x为弧度值 |
ACOS(x) | 返回x的反余弦值,其中x为余弦值,即获取余弦为x的值。如果x的值不在-1到1之间,则返回NULL |
TAN(x) | 返回x的正切值,其中参数x为弧度值 |
ATAN(x) | 返回x的反正切值,其中x为正切值,即返回正切值为x的值 |
ATAN2(m, n) | 返回两个参数的反正切值。等价于ATAN(m/n),但n可以为0 |
COT(x) | 返回x的余切值,其中x为弧度值 |
2.4 指数与对数
函数 | 用法 |
---|
POW(x, y),POWER(x, y) | 返回x的y次方 |
EXP(x) | 返回e的x次方,其中e是一个常数2.718281828459045 |
LN(x),LOG(x) | 返回以e为底的x的对数,当x <= 0时,返回的结果为NULL |
LOG10(x) | 返回以10为底的x的对数,当x <= 0时,返回的结果为NULL |
LOG2(x) | 返回以2为底的x的对数,当x <= 0时,返回NULL |
2.5 进制间的转换
函数 | 用法 |
---|
BIN(x) | 返回x的二进制编码 |
HEX(x) | 返回x的十六进制编码 |
OCT(x) | 返回x的八进制编码 |
CONV(x, f1, f2) | 将f1进制编码的数字转换为f2进制编码的数字,然后以字符串显示。例如select CONV(10, 2, 8); 的结果为2 |
3. 字符串函数
函数 | 用法 |
---|
ASCII(s) | 返回字符串s中的第一个字符的ASCII码值 |
CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
LENGTH(s) | 返回字符串s的字节数,和字符集有关 |
CONCAT(s1, s2, …, sn) | 连接s1, s2, …, sn为一个字符串 |
CONCAT_WS(x, s1, s2, …, sn) | 同CONCAT(s1, s2, …)函数,但是每个字符串之间要加上x |
INSERT(str, idx, len, replacestr) | 将字符串str从第idx位置开始,len个字符长的子串替换为字符串replacestr。其中字符串的位置是从1开始的 |
REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
UPPER(s), UCASE(s) | 将字符串s的所有字母转成大写字母 |
LOWER(s), LCASE(s) | 将字符串s的所有字母转成小写字母 |
LEFT(str, n) | 返回字符串str最左边的n个字符 |
RIGHT(str, n) | 返回字符串str最右边的n个字符 |
LPAD(str, len, pad) | 用字符串pad对str最左边进行填充,直到str的长度为len个字符。例如select LPAD('Hello', 10, 'ab'); 的结果为ababaHello |
RPAD(str, len, pad) | 用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) | 去掉字符串s左侧的空格 |
RTRIM(s) | 去掉字符串s右侧的空格 |
TRIM(s) | 去掉字符串s开始与结尾的空格 |
TRIM(s1 FROM s) | 去掉字符串s开始与结尾的s1 |
TRIM(LEADING s1 FROM s) | 去掉字符串s开始处的s1 |
TRIM(TRAILING s1 FROM s) | 去掉字符串s结尾处的s1 |
REPEAT(str, n) | 返回str重复n次的结果 |
SPACE(n) | 返回n个空格 |
STRCMP(s1, s2) | 比较字符串s1, s2的ASCII码值的大小。例如select STRCMP('ab', 'cd'); 的结果为-1 |
SUBSTR(s, index, len) | 返回从字符串s的index位置其len个字符,作用与SUBSTRING(s, n, len)、MID(s, n, len)相同 |
LOCATE(substr, str) | 返回字符串substr在字符串str中首次出现的位置,作用与POSITION(substr IN str)、INSTR(str, substr)相同。未找到则返回0 |
ELT(m, s1, s2, …, sn) | 返回指定位置的字符串,如果m = 1,则返回s1,如果m = 2,则返回s2,如果m = n,则返回sn |
FIELD(s, s1, s2, …, sn) | 返回字符串s在字符串列表中第一次出现的位置 |
FIND_IN_SET(s1, s2) | 返回字符串s1在字符串s2中出现的位置。其中字符串s2是一个以逗号分隔的字符串 |
REVERSE(s) | 返回s反转后的字符串 |
NULLIF(value1, value2) | 比较两个字符串,如果value1与value2相等,则返回NULL,否则返回value1 |
4. 日期和时间函数
4.1 获取日期、时间
函数 | 用法 |
---|
CURDATE() ,CURRENT_DATE() | 返回当前日期,只包含年、月、日 |
CURTIME() , CURRENT_TIME() | 返回当前时间,只包含时、分、秒 |
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期和时间 |
UTC_DATE() | 返回UTC(世界标准时间)日期 |
UTC_TIME() | 返回UTC(世界标准时间)时间 |
例如:
mysql> select CURDATE(), CURTIME(), NOW(), SYSDATE(), CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP(), UTC_DATE(), UTC_TIME();
+------------+-----------+---------------------+---------------------+---------------------+---------------------+---------------------+------------+------------+
| CURDATE() | CURTIME() | NOW() | SYSDATE() | CURRENT_TIMESTAMP() | LOCALTIME() | LOCALTIMESTAMP() | UTC_DATE() | UTC_TIME() |
+------------+-----------+---------------------+---------------------+---------------------+---------------------+---------------------+------------+------------+
| 2022-08-17 | 09:56:58 | 2022-08-17 09:56:58 | 2022-08-17 09:56:58 | 2022-08-17 09:56:58 | 2022-08-17 09:56:58 | 2022-08-17 09:56:58 | 2022-08-17 | 01:56:58 |
+------------+-----------+---------------------+---------------------+---------------------+---------------------+---------------------+------------+------------+
1 row in set (0.00 sec)
mysql>
4.2 获取月份、星期、星期数、天数等函数
函数 | 用法 |
---|
YEAR(date) / MONTH(date) / DAY(date) | 返回具体的年、月、日。例如select MONTH(NOW()); 的结果为8 |
HOUR(time) / MINUTE(time) / SECOND(time) | 返回具体的时、分、秒 |
MONTHNAME(date) | 返回月份:January,… |
DAYNAME(date) | 返回星期几:MONDAY,TUESDAY, …, SUNDAY |
WEEKDAY(date) | 返回周几,注意周1是0,周2是1,…,周日是6 |
QUARTER(date) | 返回日期对应的季度,范围为1~4 |
WEEK(date) , WEEKOFYEAR(date) | 返回一年中的第几周 |
DAYOFYEAR(date) | 返回日期是一年中的第几天 |
DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
DAYOFWEEK(date) | 返回周几,注意周日是1,周一是2,…,周六是7 |
4.3 日期与时间戳的转换
函数 | 用法 |
---|
UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间 |
UNIX_TIMESTAMP(date) | 将date日期以UNIX时间戳的形式返回 |
FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
例如:
mysql> select UNIX_TIMESTAMP(), UNIX_TIMESTAMP(CURTIME()), FROM_UNIXTIME(1660702175);
+------------------+---------------------------+---------------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(CURTIME()) | FROM_UNIXTIME(1660702175) |
+------------------+---------------------------+---------------------------+
| 1660702184 | 1660702184 | 2022-08-17 10:09:35 |
+------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)
mysql>
4.4 日期的操作函数
函数 | 用法 |
---|
EXTRACT(type FROM date) | 返回指定日期中特定的部分,type指定返回的值 |
中type的取值与含义如下:
type取值 | 含义 |
---|
DAY | 返回天数 |
DAY_HOUR | 返回天和小时 |
DAY_MICROSECOND | 返回天和毫秒值。例如select EXTRACT(DAY_MICROSECOND FROM '2022-08-06 11:05:30.1234566'); 的结果为6110530123457 |
DAY_MINUTE | 返回天和分钟值 |
DAY_SECOND | 返回天和秒值 |
HOUR | 返回小时数 |
HOUR_MICROSECOND | 返回小时和毫秒值 |
HOUR_MINUTE | 返回小时和分钟值 |
HOUR_SECOND | 返回小时和秒值 |
MICROSECOND | 返回毫秒值 |
MINUTE | 返回分钟数 |
MINUTE_MICROSECOND | 返回分钟和毫秒值 |
MINUTE_SECOND | 返回分钟和秒值 |
MONTH | 返回日期在一年中的第几个月 |
QUARTER | 返回日期在一年中的第几个季度 |
SECOND | 返回秒数 |
SECOND_MICROSECOND | 返回秒和毫秒值 |
WEEK | 返回日期在一年中的第几个星期 |
YEAR | 返回日期的年份 |
YEAR_MONTH | 返回年和月 |
4.5 时间和秒钟转换的函数
函数 | 用法 |
---|
TIME_TO_SEC(time) | 将time转化为秒并返回结果值。转化的公式为: 小时 * 3600 + 分钟 * 60 + 秒 |
SEC_TO_TIME(seconds) | 将seconds描述转化为包含小时、分钟和秒的时间 |
例如:
mysql> select TIME_TO_SEC(NOW()), SEC_TO_TIME(39860);
+--------------------+--------------------+
| TIME_TO_SEC(NOW()) | SEC_TO_TIME(39860) |
+--------------------+--------------------+
| 39860 | 11:04:20 |
+--------------------+--------------------+
1 row in set (0.00 sec)
mysql>
4.6 计算日期和时间的函数
日期加减:
函数 | 用法 |
---|
DATE_ADD(datetime, INTERVAL expr type), ADDDATE(date, INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时间段的日期时间 |
DATE_SUB(date, INTERVAL expr type), SUBDATE(date, INTERVAL expr type) | 返回与date相差INTERVAL时间间隔的日期 |
type的取值如下:
间隔类型 | 含义 |
---|
HOUR | 小时 |
MINUTE | 分钟 |
SECOND | 秒 |
YEAR | 年 |
MONTH | 月 |
DAY | 日 |
YEAR_MONTH | 年和月 |
DAY_HOUR | 日和小时 |
HOUR_MINUTE | 小时和分钟 |
MINUTE_SECOND | 分钟和秒 |
例如:
mysql> select DATE_SUB(NOW(), INTERVAL '-1 -1' YEAR_MONTH);
+----------------------------------------------+
| DATE_SUB(NOW(), INTERVAL '-1 -1' YEAR_MONTH) |
+----------------------------------------------+
| 2023-09-17 11:40:27 |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql>
其它:
函数 | 用法 |
---|
ADDTIME(time1, time2) | 返回time1加上time2(只处理time部分)的时间。当time2为一个数字时,代表的是秒 ,可以为负数 |
SUBTIME(time1, time2) | 返回time1减去time2(只处理time部分)后的时间。当time2为一个数字时,代表的是秒 ,可以为负数 |
DATEDIFF(date1, date2) | 返回date1 - date2的日期间隔天数 |
TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔。如果时间相隔多天,也会将天转换成小时 |
FROM_DAYS(n) | 返回从0000年1月1日起,n(n > 365)天以后的日期 |
TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 |
LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
MAKEDATE(year, n) | 针对给定年份与所在年份中的天数返回一个日期 |
MAKETIME(hour, minute, second) | 将给定的小时、分钟和秒组合成时间并返回 |
PERIOD_ADD(period, number) | 将指定的月数添加到period,其中period格式:YYMM或YYYYMM,月数可以为正数或负数。例如select PERIOD_ADD('2208', -1); 的结果为202207 |
4.7 日期的格式化与解析
函数 | 用法 |
---|
DATE_FORMAT(date, fmt) | 按照字符串fmt格式化日期date值 |
TIME_FORMAT(time, fmt) | 按照字符串fmt格式化时间time值 |
STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析,解析为一个日期 |
GET_FORMAT(date_type, format_type) | 返回日期字符串的显示格式 |
其中字符串fmt的规则如下:
格式符 | 说明 | 格式符 | 说明 |
---|
%Y | 4位数字表示年份 | %y | 表示两位数字表示年份 |
%M | 月名表示月份(January, …) | %m | 两位数字表示月份(01, 02, 03, …) |
%b | 缩写的月名(Jan.,Feb.,…) | %c | 数字表示月份(1, 2, 3, …) |
%D | 英文后缀表示月中的天数(1st, 2nd, 3rd, …) | %d | 两位数字表示月中的天数(01, 02, …) |
%e | 数字形式表示月中的天数(1, 2, 3, 4, 5, …) | | |
%H | 两位数字表示小数,24小时制(01, 02, …) | %h和%I | 两位数字表示小时,12小时制(01, 02, …) |
%k | 数字形式的小时,24小时制(1, 2, 3, …) | %l | 数字形式表示小时,12小时制(1, 2, 3, 4, …) |
%i | 两位数字表示分钟(00, 01, 02, …) | %S和%s | 两位数字表示秒(00, 01, 02, …) |
%W | 一周中的星期名称(Sunday, …) | %a | 一周中的星期缩写(Sun.,Mon., Tues.,…) |
%w | 以数字表示一周中的星期(0=Sunday, 1=Monday, …) | | |
%j | 以3位数字表示年中的天数(001, 002, …) | %U | 以数字表示年中的第几周(1, 2, 3, …), 其中Sunday为周中第一天 |
%u | 以数字表示年中的第几周(1, 2, 3, …), 其中Monday为周中第一天 | | |
%T | 24小时制 | %r | 12小时制 |
%p | AM或PM | %% | 表示% |
GET_FORMAT函数中date_type和format_type参数取值如下:
date_type | 字符串format_type | 返回的格式化字符串 |
---|
DATE | USA | %m.%d.%Y |
DATE | JIS | %Y-%m-%d |
DATE | ISO | %Y-%m-%d |
DATE | EUR | %d.%m.%Y |
DATE | INTERNAL | %Y%m%d |
TIME | USA | %h:%i:%s %p |
TIME | JIS | %H:%i:%s |
TIME | ISO | %H:%i:%s |
TIME | EUR | %H.%i.%s |
TIME | INTERNAL | %H%i%s |
DATETIME | USA | %Y-%m-%d %H.%i.%s |
DATETIME | JIS | %Y-%m-%d %H:%i:%s |
DATETIME | ISO | %Y-%m-%d %H:%i:%s |
DATETIME | EUR | %Y-%m-%d %H.%i.%s |
DATETIME | INTERNAL | %Y%m%d%H%i%s |
5. 流程控制函数
流程处理函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中实现不同的条件选择
函数 | 用法 |
---|
IF(value, value1, value2) | 如果value的值为TRUE,返回value1,否则返回value2 |
IFNULL(value1, value2) | 如果value1不为NULL,返回value1,否则返回value2 |
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE 结果n] END | 相当于Java的if … else if … else … |
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值2 THEN 值2 … [ELSE 值n] END | 相当于Java的switch … case … |
6. 加密与解密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理
函数 | 用法 |
---|
PASSWORD(str) | 返回字符串str的加密版本,41位长的字符串。加密结果不可逆,常用于用户的密码加密 |
MD5(str) | 返回字符串str的md5加密后的值,也是一种加密方式。若参数为NULL,则会返回NULL |
SHA(str) | 从原明文密码str计算并返回加密后的密码字符串,当参数为NULL时,返回NULL。SHA加密算法比MD5更加安全 |
ENCODE(value, password_seed) | 返回使用password_seed作为加密密码加密value |
DECODE(value, password_seed) | 返回使用password_seed作为加密密码解密value |
7. MySQL信息函数
MySQL中内置了一些可以查询MySQL信息的函数
函数 | 用法 |
---|
VERSION() | 返回当前MySQL的版本号 |
CONNECTION_ID() | 返回当前MySQL客户端连接MySQL服务器的连接ID |
DATABASE(), SCHEMA() | 返回MySQL命令行当前所在的数据库 |
USER(), CURRENT_USER(), SYSTEM_USER(), SESSION_USER() | 返回当前连接MySQL的用户名,返回结果格式为“主机名@用户名” |
CHARSET(value) | 返回字符串value自变量的字符集 |
COLLATION(value) | 返回字符串value的排序规则 |
8. 其他函数
函数 | 用法 |
---|
FORMAT(value, n) | 返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位。如果n的值小于或者等于0,则只保留整数部分 |
CONV(value, from, to) | 将value的值进行不同进制之间的转换 |
INET_ATON(ipvalue) | 将以点分隔的IP地址转化为一个数字。例如SELECT INET_ATON('192.168.1.100'); 的结果为3232235876,计算方式为192 * 256^3 + 168 * 256^2 + 1 * 256 + 100 |
INET_NTOA(value) | 将数字形式的IP地址转化为以点分隔的IP地址 |
BENCHMARK(n, expr) | 将表达式expr重复执行n次所耗费的时间。用于测试MySQL处理expr表达式所耗费的时间 |
CONVERT(value USING char_code) | 将value所使用的字符编码修改为char_code |