mysql命令行 - 数学函数

除了基本的算术运算象加减乘除(+-*/)，mysql还提供许多数学函数。在这一节里我们就要来学习：

mysql的自带数学函数

1.mysql - 求模 MOD(x,y)

select MOD(37, 13);
+-------------+
| MOD(37, 13) |
+-------------+
|          11 |
+-------------+
1 row in set (0.00 sec)

求模数也就求余数，这也可以用模数运算(% - Modulo)来完成，例如：

select 87 % 9;
+--------+
| 87 % 9 |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

2. mysql - 求绝对值 ABS(x)

select ABS(-4.05022);
+---------------+
| ABS(-4.05022) |
+---------------+
|       4.05022 |
+---------------+
1 row in set (0.00 sec)
select ABS(4.05022);
+--------------+
| ABS(4.05022) |
+--------------+
|      4.05022 |
+--------------+
1 row in set (0.00 sec)

3. mysql - 判断正负 SIGN(X)

select SIGN(-34.22);
+--------------+
| SIGN(-34.22) |
+--------------+
|           -1 |
+--------------+
1 row in set (0.00 sec)
select SIGN(54.6);
+------------+
| SIGN(54.6) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)
select SIGN(0);
+---------+
| SIGN(0) |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

根据上述例子，我们可以得知

1. 如果x是正数，SIGN(x)=1
2. 如果x是负数或者0， 那么SIGN(x)=0

4. mysql - 求乘方 POWER(x,y)

select POWER(4,3);
+------------+
| POWER(4,3) |
+------------+
|  64.000000 |
+------------+
1 row in set (0.00 sec)

5. mysql - 求平方根 SQRT(x)

select SQRT(3);
+----------+
| SQRT(3)  |
+----------+
| 1.732051 |
+----------+
1 row in set (0.00 sec)

6. mysql - 四舍五入 ROUND(x) 和 ROUND(x,y)

1. ROUND(x): 把小数四舍五入为整数
2. ROUND(x,y): 把小数四舍五入到小数点后y位

select ROUND(14.492);
+---------------+
| ROUND(14.492) |
+---------------+
|            14 |
+---------------+
1 row in set (0.00 sec)
select ROUND(4.5002);
+---------------+
| ROUND(4.5002) |
+---------------+
|             5 |
+---------------+
1 row in set (0.00 sec)
select ROUND(-12.773);
+----------------+
| ROUND(-12.773) |
+----------------+
|            -13 |
+----------------+
1 row in set (0.00 sec)
select ROUND(7.235651, 3);
+--------------------+
| ROUND(7.235651, 3) |
+--------------------+
|              7.236 |
+--------------------+
1 row in set (0.00 sec)

7. mysql - 求基值 FLOOR(x)

FLOOR(x)用来求小于或者等于x的最大整数值，例如：

select FLOOR(23.544);
+---------------+
| FLOOR(23.544) |
+---------------+
|            23 |
+---------------+
1 row in set (0.00 sec)
select FLOOR(-18.4);
+--------------+
| FLOOR(-18.4) |
+--------------+
|          -19 |
+--------------+
1 row in set (0.00 sec)

8. mysql - 求顶值 CEILING(x)

CEILING(x)用来求大于或者等于x的最小整数值，例如：

select CEILING(54.22);
+----------------+
| CEILING(54.22) |
+----------------+
|             55 |
+----------------+
1 row in set (0.00 sec)
select CEILING(-62.23);
+-----------------+
| CEILING(-62.23) |
+-----------------+
|             -62 |
+-----------------+
1 row in set (0.00 sec)

9. mysql - 求正弦、余弦、正切 SIN(x), COS(x), TAN(x)

mysql> select SIN(0);
+----------+
| SIN(0)   |
+----------+
| 0.000000 |
+----------+
1 row in set (0.00 sec)
mysql> select tan(90);
+------------------+
| tan(90)          |
+------------------+
| -1.9952004122082 |
+------------------+
1 row in set (0.00 sec)

注意：运用SIN(x), COS(x), TAN(x)的时候，x指的是弧度，而不度数。例如SIN(90) != 1, 因为90这里指的是弧度，而不是直角。