【MySQL】超详细MySQL常用日期格式转换函数、字符串函数、聚合函数（最新版）

文章目录

一、MySQL常用日期格式转换函数

1、查看当前日期时间

-- 查看当前日期时间
select NOW() from dual;-- 这里面的dual为虚表，MYSQL也可以不写。 2024-03-0723:25:10
select SYSDATE();--2024-03-0723:25:10
select CURRENT_TIMESTAMP();--2024-03-0723:25:10
select LOCALTIME();--2024-03-0723:25:10
select LOCALTIMESTAMP();--2024-03-0723:25:10

注：NOW()与SYSDATE()区别
select NOW(),sleep(1),NOW();--2024-03-0723:25:1002024-03-0723:25:10
select SYSDATE(),sleep(1),SYSDATE();--2024-03-0723:25:1102024-03-0723:25:12-- 查看当前日期
select CURDATE();--2024-03-07
select CURRENT_DATE();--2024-03-07-- 查看当前时间
select CURTIME();--23:25:12
select CURRENT_TIME();--23:25:12-- 查看当前时间戳
SELECTUNIX_TIMESTAMP();--1709825116

2、日期函数

select date(NOW());--2024-03-07(获取日期)
select time(NOW());--23:20:09(获取时间) 
select year(NOW());--2024(获取年份) 
select month(NOW());--3(获取月份) 
select day(NOW());--7(获取日) 
select hour(NOW());--23(获取时) 
select minute(NOW());--20(获取分) 
select second(NOW());--9(获取秒)
select quarter(NOW());--1(获取季度)
select week(NOW());--9(获取周) 
select weekofyear(NOW());--10(日期在年度中第几周) 
select dayofyear(NOW());--67(日期在年度中第几天) 
select dayofmonth(NOW());--7(日期在月度中第几天)
select dayofweek(NOW());--5(日期在周中第几天1-7，周日为第1天) 
select weekday(NOW());--3(日期在本周的星期几0-6，0为星期一)
select yearweek(NOW());--202409(年和周)
select dayname(CURRENT_TIMESTAMP);--Thursday(英文星期)
select monthname(CURRENT_TIMESTAMP);--March(英文月份)
select last_day(CURRENT_TIMESTAMP);--2024-03-31(月份中最后一天)

3、日期格式转换

select DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');--2024-03-0723:43:31(日期格式转换，格式可自定义)
select TIME_FORMAT(NOW(), '%H:%i:%s');--23:43:31(时间格式转换)

4、字符串日期转换

select STR_TO_DATE('2024-03-0723:45:20', '%Y-%m-%d %H:%i:%s');--2024-03-0723:45:20

5、时间单位转换

select TIME_TO_SEC(NOW());--85520(将时间值转换为秒数)
select TIME_TO_SEC('2024-03-0723:45:20');--85520(将时间值转换为秒数)
select SEC_TO_TIME(85520);--23:45:20(将秒数转换为时间格式)

6、DATE_ADD(date,interval expr type) 从日期加上指定的时间间隔 [expr为正数是往后加，为负数是往前减]

select NOW();--2024-03-0723:39:14
select DATE_ADD(NOW(),interval 1 year);--2025-03-0723:39:14
select DATE_ADD(NOW(),interval 1 quarter);--2024-06-0723:39:14
select DATE_ADD(NOW(),interval 1 month);--2024-04-0723:39:14
select DATE_ADD(NOW(),interval 1 week);--2024-03-1423:39:14
select DATE_ADD(NOW(),interval 1 day);--2024-03-0823:39:14
select DATE_ADD(NOW(),interval 1 hour);--2024-03-0800:39:14
select DATE_ADD(NOW(),interval 1 minute);--2024-03-0723:40:14
select DATE_ADD(NOW(),interval 1 second);--2024-03-0723:39:15
select DATE_ADD(NOW(),interval -1 day);--2024-03-0623:39:14

7、DATE_SUB(date,interval expr type) 从日期减去指定的时间间隔 [expr为正数是往前减，为负数是往后加]

select NOW();--2024-03-0723:41:47
select DATE_SUB(NOW(),interval 1 year);--2023-03-0723:41:47
select DATE_SUB(NOW(),interval 1 quarter);--2023-12-0723:41:47
select DATE_SUB(NOW(),interval 1 month);--2024-02-0723:41:47
select DATE_SUB(NOW(),interval 1 week);--2024-02-2923:41:47
select DATE_SUB(NOW(),interval 1 day);--2024-03-0623:41:47
select DATE_SUB(NOW(),interval 1 hour);--2024-03-0722:41:47
select DATE_SUB(NOW(),interval 1 minute);--2024-03-0723:40:47
select DATE_SUB(NOW(),interval 1 second);--2024-03-0723:41:46
select DATE_SUB(NOW(),interval -1 day);--2024-03-0823:41:47

8、PERIOD_ADD(p,n) 函数参数“p” 的格式为“yyyymm” 或者 “yymm”，第二个参数“n” 表示增加或减去 n month（月）

select PERIOD_ADD(202403,2);--202405(加两个月)
select PERIOD_ADD(202403,-2);--202401(减两个月)

9、计算两个日期相差天数

select PERIOD_DIFF(202403,202404);---1(第一个参数减第二个参数,单位月份)
select DATEDIFF('2024-03-07','2024-03-05');--2
select TIMEDIFF('2024-03-0723:58:37', '2024-03-0722:58:37');--01:00:00(第一个参数减第二个参数,%H:%i:%s)

10、计算相差时间

select NOW();--2024-03-0800:14:17(当前日期时间)
select TIMESTAMPDIFF(year, '2023-03-07',CURRENT_DATE);--1(差几年)
select TIMESTAMPDIFF(month, '2024-02-07',CURRENT_DATE);--1(差多少月)
select TIMESTAMPDIFF(day, '2024-02-07',CURRENT_DATE);--30(差多少天)
select TIMESTAMPDIFF(hour, '2024-03-0800:01:25',NOW());--0(差多少小时)
select TIMESTAMPDIFF(minute, '2024-03-0800:01:25',NOW());--12(差多少分钟)
select TIMESTAMPDIFF(second, '2024-03-0800:01:25',NOW());--772(差多少秒)

11、unix 时间戳、日期）转换函数

-- 将日期转为时间戳
select UNIX_TIMESTAMP();--1709827653(获取当前时间戳)
select UNIX_TIMESTAMP('2024-03-08');--1709827200(具体日期转为时间戳)
select UNIX_TIMESTAMP('2022-03-0800:26:30');--1646670390(具体时间日期转为时间戳)-- 将时间戳转为具体时间
select FROM_UNIXTIME(1646670390);--2022-03-0800:26:30(时间戳转化成日期)
select FROM_UNIXTIME(1646670390, '%y-%m-%d %H:%i:%s');--22-03-0800:26:30(时间戳转化成指定格式日期)

附录：日期格式表格

格式描述%a缩写星期名(如：Fri)%b缩写月名(如：Mar)%c月，数值(1-12)%D带有英文前缀的月中的天(如：7th)%d月的天，数值(00-31)%e月的天，数值(0-31)%f微秒%H小时 (00-23)%h小时 (01-12)%I小时 (01-12)%i分钟，数值(00-59)%j年的天 (001-366)%k小时 (0-23)%l小时 (1-12)%M月名(如：March)%m月，数值(00-12)%pAM 或 PM%r时间，12-小时（hh:mm:ss AM 或 PM）%S秒(00-59)%s秒(00-59)%T时间, 24-小时 (hh:mm:ss)%U周 (00-53) 星期日是一周的第一天%u周 (00-53) 星期一是一周的第一天%V周 (01-53) 星期日是一周的第一天，与 %X 使用%v周 (01-53) 星期一是一周的第一天，与 %x 使用%W星期名(如：Friday)%w周的天 （0=星期日, 6=星期六）%X年，其中的星期日是周的第一天，4 位，与 %V 使用%x年，其中的星期一是周的第一天，4 位，与 %v 使用%Y年，4 位(如：2024)%y年，2 位(如：24)

二、MySQL常用字符串处理函数

1、字符长度和子串提取

select LENGTH('你好mysql');--11(返回字符串str的长度[字节数，一个英文字符通常占用1个字节，一个汉字占用3个字节])

select CHAR_LENGTH('你好mysql');--7(返回字符串str的长度[字符数，忽视字符集]) 

select CHARACTER_LENGTH('你好mysql');--7(同 CHAR_LENGTH(str))

select SUBSTRING('你好mysql',1,2);-- 你好(从str中提取从pos位置开始的length个字符的子串,下标从1开始)

select SUBSTRING_INDEX('你1好1mysql1', '1',2);-- 你1好(返回str被delimiter分割后的子串，count决定返回的分隔片数)

2、拼接字符串

select CONCAT('你','好','mysql');-- 你好mysql(连接多个字符串为一个字符串)
 
select CONCAT_WS(',','你','好','mysql');-- 你,好,mysql(用separator作为分隔符连接多个字符串)

3、查找和替换

select FIND_IN_SET('好','你,好,mysql');--2(函数返回字符串 str 在列表字符串 strlist[逗号分隔] 中出现的位置,下标从1开始)

select INSTR('你,好,mysql', ',');--2(返回substr在str中首次出现的位置索引)

select REPLACE('你,好,mysql', ',', '|');-- 你|好|mysql(在str中将所有from_str替换为to_str)

select REPLACE(REPLACE('你,好,mysql', ',', '|'),'|','+');-- 你+好+mysql(可以连续调用多次，实现多重替换)

4、大小写转换

select LOWER('HELLO');--hello(将字符串转换为小写)
select UPPER('hello');--HELLO(将字符串转换为大写)

5、去除空格

select TRIM(' hello mysql ');-- hello mysql(删除str两边或指定一边的空白字符或其他指定字符)

select TRIM(LEADING'0'FROM '013312345678');--13312345678移除字符串前面的零

select TRIM(TRAILING'0'FROM '0133123456780');--013312345678移除字符串最好面的零

select LTRIM(' hello mysql ');-- hello mysql (删除str左侧的空白字符)

select RTRIM(' hello mysql ');--  hello mysql(删除str右侧的空白字符)

6、格式化和提取

select FORMAT(11,2);--11.00(将数字[字符串]x格式化为带有d位小数的字符串)
select LPAD('11',4,'0');--0011(在str左侧填充padstr至总长度len)
select RPAD('11',6,'0');--110000(在str右侧填充padstr至总长度len)
select LEFT('123456',3);--123(返回str左边len个字符)
select RIGHT('123456',3);--456(返回str右边len个字符)

7、字段判空

select ISNULL('1');--0(字符串判空，非空为0，空为1)
select IFNULL(null,'2');--2(字符串判空取值，非空取第一个数，为空取第二个)

8、字符串转数字

SELECTCAST('123'ASSIGNED);--123(将字符串转换为整数)SELECTCAST('123.45'ASDECIMAL(10,2));--123.45(将字符串转换为浮点数)SELECTCONVERT('123',SIGNED);--123(使用CONVERT函数将字符串转换为整数)SELECT'123'+0;--123(使用加法操作符将字符串当作数字处理)

9、数字转字符串

SELECTCONCAT(123, '');--123(将数字与空字符串连接以转换为字符串)SELECTCAST(123ASCHAR(10));--123(使用CAST函数将数字转换为字符串)SELECTCONVERT(123,CHAR);--123(使用CONVERT函数将数字转换为字符串)SELECT123+ '';--123(直接与空字符串相加也可以达到转换目的)

三、MySQL常用聚合函数

select COUNT(*)-- 计算表中的行数，包括NULL值的行。
select COUNT(column -- 计算指定列中非NULL值的数量。
select SUM(column)-- 对指定列的所有数值进行求和，只适用于数值类型列。若列中有NULL值，则忽略NULL值进行求和。
select AVG(column)-- 计算指定列中数值的平均值，同样忽略NULL值。
select MAX(column)-- 找出指定列中的最大值，可用于数值、字符串、日期和时间类型的数据。
select MIN(column)-- 找出指定列中的最小值，可用于数值、字符串、日期和时间类型的数据。
select GROUP_CONCAT(column)-- 将属于同一组的列值连接成一个字符串，可设置分隔符，返回一个字符串结果。

希望对小伙伴们有所帮助，完结，撒花❀❀