MySQL 常用函数 视图

常用函数：

替换用户名：

函数：REPLACE(A,B)，A替换成B

        把2002-06-17 00:00:00 改成 2022-06-17 00:00:00
例：select REPLACE(A,B) from table
    update table set create_time = REPLACE(create_time,'2002','2022')

保留两位小数：

函数：ROUND(val,2)
例：select ROUND(val,2) val from table

转类型

String 转成 Double
函数：cast(value as type) 、 convert(value,type),
        type支持类型：
                DATE    -    将值转换为DATE。格式:"YYYY-MM-DD"
                DATETIME  -  将值转换为DATETIME。格式:"YYYY-MM-DD HH:MM:SS"
                TIME  -        将值转换为TIME。格式:"HH:MM:SS"
                CHAR  -  将值转换为CHAR（固定长度的字符串）
                SIGNED  -  将值转换为SIGNED（带符号的64位整数）
                UNSIGNED  -  将值转换为UNSIGNED（无符号的64位整数）
                BINARY  -  将值转换为BINARY（二进制字符串）

例：select convert(val,CHAR) from table 

 

视图：

当月时间的同月12月

select date_format((curdate() - interval 12 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 13 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 14 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 15 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 16 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 17 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 18 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 19 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 20 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 21 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 22 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 22 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 23 month),'%Y-%m') AS `date`

当日小时前推24小时

select date_format((now() - interval 0 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 0 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 1 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 1 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 2 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 2 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 3 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 3 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 4 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 4 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 5 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 5 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 6 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 6 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 7 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 7 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 8 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 8 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 9 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 9 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 10 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 10 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 11 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 11 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 12 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 12 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 13 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 13 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 14 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 14 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 15 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 15 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 16 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 16 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 17 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 17 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 18 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 18 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 19 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 19 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 20 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 20 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 21 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 21 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 22 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 22 hour),'%Y-%m-%d %H:59:59') AS `endTime` union select date_format((now() - interval 23 hour),'%Y-%m-%d %H:00:00') AS `startTime`,date_format((now() - interval 23 hour),'%Y-%m-%d %H:59:59') AS `endTime`

当日天前推30天

select date_format((curdate() - interval 0 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 1 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 2 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 3 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 4 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 5 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 6 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 7 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 8 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 9 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 10 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 11 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 12 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 13 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 14 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 15 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 16 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 17 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 18 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 19 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 20 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 21 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 22 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 23 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 24 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 25 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 26 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 27 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 28 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 29 day),'%Y-%m-%d') AS `date`

当月前推12月

select date_format((curdate() - interval 0 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 1 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 2 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 3 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 4 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 5 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 6 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 7 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 8 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 9 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 10 month),'%Y-%m') AS `date` union select date_format((curdate() - interval 11 month),'%Y-%m') AS `date`

当日前推7天

select date_format((curdate() - interval 0 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 1 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 2 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 3 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 4 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 5 day),'%Y-%m-%d') AS `date` union select date_format((curdate() - interval 6 day),'%Y-%m-%d') AS `date`

有常用的函数或者视图欢迎补充！！！！

会持续更新此文字