mysql复习第三天：单行函数

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;