mysql复习第三天：单行函数

女爷i 2023-08-17 16:04 135阅读 0赞

### 1 、常用数学函数 ###

*  round 四写五入，小数点保留指定位数

SELECT ROUND(1.65,2) AS a;

![在这里插入图片描述][20190927212104482.png]

*  ceil 向上取整,返回>=该参数的最小整数

SELECT CEIL(1.002) AS a;

![在这里插入图片描述][20190927212112876.png]

*  floor 向下取整,返回<=该参数的最大整数

SELECT FLOOR(-9.99) AS a;

![在这里插入图片描述][20190927212121696.png]

*  truncate 截断

SELECT TRUNCATE(1.66666,1) AS a;

![在这里插入图片描述][20190927212131885.png]

*  mod取余和%符号一样(a-a/b\*b)

SELECT MOD(10,3) AS a;

![在这里插入图片描述][20190927212141828.png]

### 2、常用日期函数 ###

*  now返回当前日期+时间

SELECT NOW() AS a;

![在这里插入图片描述][20190927212305254.png]

*  curdate 返回当前日期

SELECT CURDATE() AS a;

![在这里插入图片描述][20190927212314259.png]

*  curtime 返回当前时间

SELECT CURTIME() AS a;

![在这里插入图片描述][20190927212322903.png]

*  获取指定年，月，日，小时，分钟，秒

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;

![在这里插入图片描述][201909272123369.png]![在这里插入图片描述][20190927212402564.png]![在这里插入图片描述][20190927212410123.png]![在这里插入图片描述][2019092721241941.png]![在这里插入图片描述][20190927212428975.png]![在这里插入图片描述][20190927212438276.png]

*  str\_to\_date 将日志格式的字符串转成指定格式的日期

SELECT STR_TO_DATE('2019-09-27','%Y-%m-%d %H:%i:%s') AS a;

![在这里插入图片描述][20190927212447373.png]

*  date\_format 将日期转成字符

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') AS a;

![在这里插入图片描述][20190927212457293.png]

[20190927212104482.png]: /images/20230810/769622f51f0345308873c304d5c6b97d.png
[20190927212112876.png]: /images/20230810/75eab85b380d437f9d356cb534408420.png
[20190927212121696.png]: /images/20230810/1b5d5bdd28084718aa2c2e0b85419ac2.png
[20190927212131885.png]: /images/20230810/09fefc9902db4f33a48965691d07ae96.png
[20190927212141828.png]: /images/20230810/3457462ac774499da292f0346844c436.png
[20190927212305254.png]: /images/20230810/31c054a8270a400d8211aad3a1c80a7e.png
[20190927212314259.png]: /images/20230810/913c4bb5d90d4cce861f066decf57427.png
[20190927212322903.png]: /images/20230810/b22f8c0abac94bd7bc553dc771018395.png
[201909272123369.png]: https://img-blog.csdnimg.cn/201909272123369.png
[20190927212402564.png]: https://img-blog.csdnimg.cn/20190927212402564.png
[20190927212410123.png]: https://img-blog.csdnimg.cn/20190927212410123.png
[2019092721241941.png]: https://img-blog.csdnimg.cn/2019092721241941.png
[20190927212428975.png]: https://img-blog.csdnimg.cn/20190927212428975.png
[20190927212438276.png]: /images/20230810/1c54505936ae45c1bbdd463bb1b4780b.png
[20190927212447373.png]: /images/20230810/afdc3e4f4af74af6a10b329d8c60660a.png
[20190927212457293.png]: /images/20230810/ff2259c1ec244ed5b147b14b533b69e3.png