对于普通加减法运算,此处不予详细讲解,如下例:
mysql> SELECT 3+4;
+-----+
| 3+4 |
+-----+
| 7 |
+-----+
1 row in set (0.03 sec)
CEIL和FLOOR
CEIL为向上取整,只要有小数位,不管是几,直接入一位。例如:
mysql> SELECT CEIL(3.01);
+------------+
| CEIL(3.01) |
+------------+
| 4 |
+------------+
1 row in set (0.02 sec)
FLOOR与此
相反
mysql> SELECT FLOOR(3.99);
+-------------+
| FLOOR(3.99) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
DIV和MOD
DIV为取整,MOD为取余
mysql> SELECT 3/4;
+--------+
| 3/4 |
+--------+
| 0.7500 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT 3 DIV 4;
+---------+
| 3 DIV 4 |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)
mysql> SELECT 3 MOD 4;
+---------+
| 3 MOD 4 |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
注:MOD可以换成%
POWER幂运算
例如3的2次方
mysql> SELECT POWER(3,2);
+------------+
| POWER(3,2) |
+------------+
| 9 |
+------------+
1 row in set (0.14 sec)
ROUND四舍五入
例如3.652保留小数点后两位。
mysql> SELECT ROUND(3.652,2);
+----------------+
| ROUND(3.652,2) |
+----------------+
| 3.65 |
+----------------+
1 row in set (0.00 sec)
保留小数点后一位:
mysql> SELECT ROUND(3.652,1);
+----------------+
| ROUND(3.652,1) |
+----------------+
| 3.7 |
+----------------+
1 row in set (0.00 sec)
TRUNCATE
直接去掉某几位
mysql> SELECT TRUNCATE(125.89,2);
+--------------------+
| TRUNCATE(125.89,2) |
+--------------------+
| 125.89 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(125.89,1);
+--------------------+
| TRUNCATE(125.89,1) |
+--------------------+
| 125.8 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(125.89,0);
+--------------------+
| TRUNCATE(125.89,0) |
+--------------------+
| 125 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(125.89,-1);
+---------------------+
| TRUNCATE(125.89,-1) |
+---------------------+
| 120 |
+---------------------+
1 row in set (0.00 sec)