MYSQL常用数值，字符串，日期时间函数

时也-K

于 2023-12-13 17:07:33 发布

阅读量907

点赞数 23

分类专栏： mysql数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_40210639/article/details/134976785

版权

mysql数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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);