MYSQL常用函数
1.数值函数
函数 | 功能 |
ABS(x) | 返回数值x的绝对值 |
MOD(x,y) | 返回数值x除以数值y后的余数 |
CEIL(x) | 返回大于数值x的最小整数值 |
FLOOR(x) | 返回小于数值x的最大整数值 |
RAND() | 返回0～1内的随机数 |
ROUND(x) | 返回对参数x进行四舍五入后的值，ROUND(x)返回整数值，ROUND(x,y)返回参数x四舍五入后保留y位小数的值 |
TRUNCATE(x,y) | 对数值x进行截取，保留小数点后y位数字 |
2.字符串函数
函数 | 功能 |
LENGTH(str)，CHAR_LENGTH(str) | 返回字符串长度或字符个数 |
CONCAT(str1,str2…strn)，CONCAT_WS(x,str1,str2…strn) | 合并字符串 |
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) | 返回子字符串的开始位置 |
REVERSE(str) | 反转字符串 |
2.1字符串函数使用实例
-- 1.使用length函数返回字符串的长度
select length("abcdbpmf");
-- 2.使用concat函数拼接字符串-查询商品名称中包含"三"的商品信息
select * from goods where name like concat('%','三','%')
-- 3.函数CONCAT_WS(x,str1,str2…strn)是函数CONCAT(str1,str2…strn)的特殊形式，作用是以第一个参数为分隔符，连接后面的多个字符串。
SELECT CONCAT_WS('_','ab','cd','ef'),CONCAT_WS('_','gh',NULL,'ij');
-- 4.函数REPLACE(str,a,b)用于替换字符串，作用是使用字符串b替换字符串str中的子串a。
SELECT REPLACE('abcabc','abc','you');
-- 5.函数LEFT(str,x)用于获取字符串str中最左边的x个字符，函数RIGHT(str,x)用于获取字符串str中最右边的x个字符。
SELECT LEFT('beijinghuanyingni',7),RIGHT('beijinghuanyingni',7);
-- 6.函数SUBSTRING(str,x,y)用于获取字符串str中从x位置开始，后面y个字符长度的子串。该函数常用于在给定字符串中提取子串。
SELECT SUBSTRING('beijinghuanyingni',8,5);3.日期和试卷函数
函数 | 功能 |
CURDATE() | 获取当前日期 |
CURTIME() | 获取当前时间 |
NOW() | 获取当前的日期和时间 |
UNIX_TIMESTAMP(date) | 获取日期date的UNIX时间戳,UNIX时间戳是从1970年1月1日（UTC/GMT的午夜）开始到当前时间所经过的秒数 |
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) | 获取一个日期或时间值加上一个时间间隔的时间值 |
TIME_TO_SEC(d),SEC_TO_TIME(d) | 获取将“HH:MM:SS”格式的时间换算为秒，或将秒数换算为“HH:MM:SS”格式的值 |
3.1、使用实例
-- 1.CURDATE()返回“yyyy-mm-dd”的当前日期。
SELECT CURDATE();
-- 2.CURTIME()返回“HH:MM:SS”格式的当前时间。
SELECT CURTIME();
-- 3.NOW()返回当前日期和时间（同时包含年月日和时分秒）。
SELECT NOW();
-- 4.YEAR(d)返回所给的日期d是哪一年。
SELECT YEAR('22-01-15'),YEAR(now());
-- 5.MONTH(d)返回所给的日期d是一年中的第几个月，函数WEEK(d)返回所给的日期d是一年中的第几周。
SELECT MONTH('22-01-15'),MONTH(NOW()),WEEK('22-01-15'),WEEK(NOW());
-- 6.HOUR(d)返回所给时间d的小时，函数MINUTE(d)返回所给时间d的分钟，函数SECOND(d)返回所给时间d的秒
SELECT HOUR('22-01-15 05:16:21') AS HOUR,MINUTE('22-01-15 05:16:21') AS MINUTE,SECOND('22-01-15 05:16:21') AS SECOND;3.2、DATE_FORMAT(d,format)
函数DATE_FORMAT(d,format)按字符串format格式化日期d的值，其中的format格式符及其作用如下表所示
参 数 值 | 意义 |
%Y | 四位数形式的年份 |
%y | 两位数形式的年份 |
%c | 数字形式（0～12）的月份 |
%M | 英文形式（January～December）的月份名 |
%m | 数字形式（00～12）的月份 |
%W | 一周中每天为周几，用英文表示（Sunday,Monday, …,Saturday） |
%D | 月中的第几天，英文后缀形式，如0th，1st，2nd，3rd… |
%d | 两位数字表示月中的第几天，形式为00～31 |
%j | 一年的第几日（001～366） |
%H | 24小时形式的小时（00～23） |
%h | 12小时形式的小时（01～12） |
%r | 12小时形式的小时，后缀为上午（AM）或下午（PM） |
%i | 两位数字形式的分（00～59） |
%S | 两位数字形式的秒（00～59） |
-- 获取时间'2023-01-05 14:30:30' 的年份，月份，日期，小时
SELECT DATE_FORMAT('2023-01-05 14:30:30','%y %M %D %r');3.3、日期时间计算
计算日期和时间的函数主要有ADDDATE()，SUBDATE()和DATEDIFF()
函数 | 功能 | 语法 |
ADDDATE() | 日期和时间加运算 | ADDDATE(date,INTERVAL 间隔期间和类型) |
SUBDATE() | 日期和时间减运算 | SUBDATE(date,INTERVAL 间隔期间和类型) |
DATEDIFF() | 计算两个日期之间相差的天数 | DATEDIFF(date1,data2) |
3.4、日期时间的间隔类型
间隔类型值 | 描述 | 格式 |
YEAR | 年 | YY |
MONTH | 月 | MM |
DAY | 日 | DD |
YEAR_MONTH | 年和月 | YY-MM |
DAY_HOUR | 日和小时 | DD hh |
DAY_MINUTE | 日和分钟 | DD hh:mm |
DAY_SECOND | 日和秒 | DD hh:mm:ss |
HOUR | 小时 | hh |
MINUTE | 分 | mm |
SECOND | 秒 | ss |
HOUR_MINUTE | 小时和分 | hh:mm |
HOUR_SECOND | 小时和秒 | hh:ss |
MINUTE_SECOND | 分钟和秒 | mm:ss |
3.5、使用实例
-- 使用ADDDATE()函数对日期执行加运算
SELECT ADDDATE('2023-01-01',INTERVAL 2 year) as date1,
ADDDATE('2023-01-01 06:20:20',INTERVAL 2 hour) as date2,
ADDDATE('2023-01-01 06:20:20',INTERVAL '10:10' minute_second) as date3;
-- 使用SUBDATE()函数对日期执行减运算
SELECT SUBDATE('2023-01-01',INTERVAL 2 year) as date1,
SUBDATE('2023-01-01 06:20:20',INTERVAL 7 hour) as date2;
-- 计算现在距离2008年8月8日的北京奥运会有多少天
SELECT DATEDIFF('2008-08-08',NOW());4.条件判断函数
函数 | 功能 |
IF(expr,v1,v2) | 如果expr为真，返回v1，否则返回v2 |
IFNULL(v1,v2) | 如果v1不为NULL，返回v1，否则返回v2 |
CASE WHEN expr1 THEN r1 [WHEN expr2 THEN r2] [ELSE rn] END | 根据条件将数据分为几个档次 |
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END | 根据条件将数据分为几个档次 |
4.1、使用示例
注：商品表请参考上一篇数据库查询练习题
-- 1.使用IF(expr,v1,v2)函数将商品价格分为高价和低价两类，此处认为价格在100元以上的属于高价格，用high表示；月薪在100元以下的属于低价格，用low表示。
SELECT name,price,IF(price>100,'high','low') FROM goods;
-- 2.将goods表中num库存字段的NULL值替换为0
SELECT IFNULL(num,0) FROM goods;
-- 3.CASE函数实现实例1的高低价格问题
SELECT CASE WHEN price>100 THEN 'high' ELSE 'low' END FROM goods;
-- 4.使用CASE函数将商品的价格分成高、中、低 三个档次
SELECT CASE price WHEN 1000 THEN 'high' WHEN 500 THEN 'mid' ELSE 'low' END FROM goods;5.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数组中给定数据的路径 |
6.其他函数
函数 | 功能 |
DATABASE() | 返回当前数据库名 |
VERSION() | 返回当前数据库版本 |
USER() | 返回当前登录用户名和主机名的组合 |
MD5(str) | 返回字符串str的MD5值 |
PASSWORD(str) | 返回字符串str的加密版本 |
CONV(val,from_base,to_base) | 不同进制之间相互转换用于不同进制数据之间的相互转换，其中，参数val为需要转换的数据，该函数的作用是将其由from_base进制转换为to_base进制 |
INET_ATON(IP)，INET_NTOA(val) | IP和数字之间相互转换 |
-- 使用INET_ATON(IP)函数可将参数IP转换为其网络字节序形式。
SELECT INET_ATON('192.168.220.110');
-- 使用函数INET_NTOA(val)可返回网络字节序val代表的IP地址。
SELECT INET_NTOA(3232291950);
