1 、常用数学函数
- round 四写五入,小数点保留指定位数
SELECT ROUND(1.65,2) AS a;
- ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(1.002) AS a;
- floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99) AS a;
- truncate 截断
SELECT TRUNCATE(1.66666,1) AS a;
- mod取余和%符号一样(a-a/b*b)
SELECT MOD(10,3) AS a;
2、常用日期函数
- now返回当前日期+时间
SELECT NOW() AS a;
- curdate 返回当前日期
SELECT CURDATE() AS a;
- curtime 返回当前时间
SELECT CURTIME() AS a;
- 获取指定年,月,日,小时,分钟,秒
SELECT YEAR(NOW()) AS a;
SELECT MONTH(NOW()) AS b;
SELECT DAY(NOW()) AS c;
SELECT HOUR(NOW()) AS d;
SELECT MINUTE(NOW()) AS e;
SELECT SECOND(NOW()) AS f;
- str_to_date 将日志格式的字符串转成指定格式的日期
SELECT STR_TO_DATE('2019-09-27','%Y-%m-%d %H:%i:%s') AS a;
- date_format 将日期转成字符
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') AS a;