MySQL数值运算符和函数

来源：互联网发布：svg.js 编辑：程序博客网时间：2024/05/05 14:45

对于普通加减法运算，此处不予详细讲解，如下例：

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)

2 0