常用函数:
替换用户名:
函数: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`
有常用的函数或者视图欢迎补充!!!!
会持续更新此文字