数值运算符和函数
CEIL() 进一取整
DIV 整数除法
FLOOR() 舍一取整
MOD 取余数(取模)
POWER() 幂运算
POUND() 四舍五入
TRUNCATE() 数字截取
例1；简单的加减乘除
mysql> SELECT 3+4;
+-----+
| 3+4 |
+-----+
| 7 |
+-----+
1 row in set (0.01 sec)
例2:进一取整
mysql> SELECT CEIL(3.01);
+------------+
| CEIL(3.01) |
+------------+
| 4 |
+------------+
1 row in set (0.01 sec)
例3:向下取整
mysql> SELECT FLOOR(3.01);
+-------------+
| FLOOR(3.01) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
例:4 整数除法，忽略掉小数部分
mysql> SELECT 3 DIV 4;
+---------+
| 3 DIV 4 |
+---------+
| 0 |
+---------+
1 row in set (0.01 sec)
如果直接除
mysql> SELECT 3 / 4;
+--------+
| 3 / 4 |
+--------+
| 0.7500 |
+--------+
1 row in set (0.00 sec)
例5:MOD取余数
mysql> SELECT 5 MOD 3;
+---------+
| 5 MOD 3 |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
mysql> SELECT 5.33 MOD 3;
+------------+
| 5.33 MOD 3 |
+------------+
| 2.33 |
+------------+
1 row in set (0.00 sec)
例6:幂运算
mysql> SELECT POWER(2,4);
+------------+
| POWER(2,4) |
+------------+
| 16 |
+------------+
1 row in set (0.00 sec)
例7:四舍五入 ，保留小数点后N位，我们这里保留了小数点后面三位
mysql> SELECT ROUND(3.6567,3);
+-----------------+
| ROUND(3.6567,3) |
+-----------------+
| 3.657 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT ROUND(3.6560,3);
+-----------------+
| ROUND(3.6560,3) |
+-----------------+
| 3.656 |
+-----------------+
1 row in set (0.00 sec)
例8:数字截取，截取小数点后面N位
mysql> SELECT TRUNCATE(125.786,2);
+---------------------+
| TRUNCATE(125.786,2) |
+---------------------+
| 125.78 |
+---------------------+
1 row in set (0.00 sec)
注意数字截取和四舍五入不一样
例9:截取的第二个参数为负值，负值时几，就变成几个0
mysql> SELECT TRUNCATE(125.786,-2);
+----------------------+
| TRUNCATE(125.786,-2) |
+----------------------+
| 100 |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(125.786,-1);
+----------------------+
| TRUNCATE(125.786,-1) |
+----------------------+
| 120 |
+----------------------+
1 row in set (0.00 sec)