MySQL数学函数简明总结

1. abs(x): 返回x的绝对值

mysql> select abs(1), abs(-1), abs(0);
+--------+---------+--------+
| abs(1) | abs(-1) | abs(0) |
+--------+---------+--------+
|      1 |       1 |      0 |
+--------+---------+--------+

2. pi(): 返回圆周率

mysql> select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+

3. sqrt(x): 返回x的平方根，要求（x为非负数，返回null）

mysql> select sqrt(49), sqrt(0), sqrt(-49);
+----------+---------+-----------+
| sqrt(49) | sqrt(0) | sqrt(-49) |
+----------+---------+-----------+
|        7 |       0 |      null |
+----------+---------+-----------+

4. mod(x,y): 求余函数，返回x被y除后的余数；对于带有小数部分的数据值也起作用，它返回除法运算后的精确余数。

mysql> select mod(31,8), mod(21,-8), mod(-7,2), mod(-7,-2), mod(45.5,6);
+-----------+------------+-----------+------------+-------------+
| mod(31,8) | mod(21,-8) | mod(-7,2) | mod(-7,-2) | mod(45.5,6) |
+-----------+------------+-----------+------------+-------------+
|         7 |          5 |        -1 |         -1 |         3.5 |
+-----------+------------+-----------+------------+-------------+

5. ceil(x): 返回不小x的最小整数值，返回值转为一个bigint.

mysql> select ceil(-3.35), ceil(3.35);
+-------------+------------+
| ceil(-3.35) | ceil(3.35) |
+-------------+------------+
|          -3 |          4 |
+-------------+------------+

6. ceiling(x): 同ceil(x)

mysql> select ceiling(-3.35), ceiling(3.35);
+----------------+---------------+
| ceiling(-3.35) | ceiling(3.35) |
+----------------+---------------+
|             -3 |             4 |
+----------------+---------------+

7. floor(x):返回不大于x的最大整数值，返回值转为一个bigint.

mysql> select floor(-3.35), floor(3.35);
+--------------+-------------+
| floor(-3.35) | floor(3.35) |
+--------------+-------------+
|           -4 |           3 |
+--------------+-------------+

8. rand()和rand(x)

rand(x) 返回一个随机浮点值，范围在０～１之间，x为整数，它被称作种子值，用来产生重复序列。即当ｘ值相同时，产生的随机数也相同；

mysql> select rand(10), rand(10), rand(2), rand(-2);
+--------------------+--------------------+--------------------+--------------------+
| rand(10)           | rand(10)           | rand(2)            | rand(-2)           |
+--------------------+--------------------+--------------------+--------------------+
| 0.6570515219653505 | 0.6570515219653505 | 0.6555866465490187 | 0.6548542125661431 |
+--------------------+--------------------+--------------------+--------------------+

rand(): 不带参数的rand()每次产生不同０～１之间的随机数

mysql> select rand(), rand(), rand();
+--------------------+--------------------+---------------------+
| rand()             | rand()             | rand()              |
+--------------------+--------------------+---------------------+
| 0.6931893636409094 | 0.5147262984092592 | 0.49406343185721285 |
+--------------------+--------------------+---------------------+

9. round(x)和round(x,y): 四舍五入函数，对ｘ值按照ｙ进行四舍五入，ｙ可以省略，默认值为０;若ｙ不为０，则保留小数点后面指定ｙ位。

mysql> select round(-1.14), round(-1.9), round(1.14), round(1.9);
+--------------+-------------+-------------+------------+
| round(-1.14) | round(-1.9) | round(1.14) | round(1.9) |
+--------------+-------------+-------------+------------+
|           -1 |          -2 |           1 |          2 |
+--------------+-------------+-------------+------------+

mysql> select round(1.38,1), round(1.38,0), round(232.38,-1), round(232.38,-2);
+---------------+---------------+------------------+------------------+
| round(1.38,1) | round(1.38,0) | round(232.38,-1) | round(232.38,-2) |
+---------------+---------------+------------------+------------------+
|           1.4 |             1 |              230 |              200 |
+---------------+---------------+------------------+------------------+

10. truncate(x,y): 与round(x,y)功能类似，但不进行四舍五入，只进行截取。

mysql> select truncate(1.33,1), truncate(1.99,1), truncate(1.99,0), truncate(19.99,-1);
+------------------+------------------+------------------+--------------------+
| truncate(1.33,1) | truncate(1.99,1) | truncate(1.99,0) | truncate(19.99,-1) |
+------------------+------------------+------------------+--------------------+
|              1.3 |              1.9 |                1 |                 10 |
+------------------+------------------+------------------+--------------------+

11. sign(x): 返回参数ｘ的符号，ｘ的值为负、零或正数时返回结果依次为－１，０或１

mysql> select sign(-21), sign(-0),sign(0), sign(0.0), sign(21);
+-----------+----------+---------+-----------+----------+
| sign(-21) | sign(-0) | sign(0) | sign(0.0) | sign(21) |
+-----------+----------+---------+-----------+----------+
|        -1 |        0 |       0 |         0 |        1 |
+-----------+----------+---------+-----------+----------+

12. pow(x,y), power(x,y)和exp(x)

pow(x,y)与power(x,y)功能相同，用于返回ｘ的ｙ次乘方的结果值

mysql> select pow(2,2), pow(2,-2), pow(-2,2), pow(-2,-2);
+----------+-----------+-----------+------------+
| pow(2,2) | pow(2,-2) | pow(-2,2) | pow(-2,-2) |
+----------+-----------+-----------+------------+
|        4 |      0.25 |         4 |       0.25 |
+----------+-----------+-----------+------------+

mysql> select power(2,2), power(2,-2), power(-2,2), power(-2,-2);
+------------+-------------+-------------+--------------+
| power(2,2) | power(2,-2) | power(-2,2) | power(-2,-2) |
+------------+-------------+-------------+--------------+
|          4 |        0.25 |           4 |         0.25 |
+------------+-------------+-------------+--------------+

exp(x): 返回e的ｘ乘方后的值:

mysql> select exp(3), exp(0), exp(-3);
+-------------------+--------+---------------------+
| exp(3)            | exp(0) | exp(-3)             |
+-------------------+--------+---------------------+
| 20.08553692318767 |      1 | 0.04978706836786393 |
+-------------------+--------+---------------------+

13. log(x)和log10(x): 对数运算函数（ｘ必须为正数），log(x)-返回ｘ的自然对数(x相对于基数e的对数)　log10(x)-返回x的基数为10的对数：

mysql> select log(-3), log(0), log(3), log10(-100), log10(0), log10(100);
+---------+--------+--------------------+-------------+----------+------------+
| log(-3) | log(0) | log(3)             | log10(-100) | log10(0) | log10(100) |
+---------+--------+--------------------+-------------+----------+------------+
|    null |   null | 1.0986122886681098 |        null |     null |          2 |
+---------+--------+--------------------+-------------+----------+------------+

14. radians(x) 和 degrees(x): 角度与弧度转换函数

mysql> select radians(90), radians(180), degrees(pi()), degrees(pi()/2);
+--------------------+-------------------+---------------+-----------------+
| radians(90)        | radians(180)      | degrees(pi()) | degrees(pi()/2) |
+--------------------+-------------------+---------------+-----------------+
| 1.5707963267948966 | 3.141592653589793 |           180 |              90 |
+--------------------+-------------------+---------------+-----------------+

15. sin(x), asin(x), cos(x), acos(x), tan(x), atan(x), cot(x)
sin(x): 正弦函数，其中ｘ为弧度值
asin(x): 反正弦函数　其中ｘ必须在－１到１之间

cos(x): 余弦函数，其中ｘ为弧度值
acos(x): 反余弦函数　其中ｘ必须在－１到１之间
tan(x): 正切函数，其中ｘ为弧度值
atan(x): 反正切函数，atan(x)与tan(x)互为反函数

cot(x):　余切函数，函数cot和tan互为倒函数

mysql> select sign(pi()/2),asin(1),cos(pi()), acos(-1), tan(pi()/4), atan(1), cot(0.5);
+--------------+--------------------+-----------+-------------------+--------------------+--------------------+-------------------+
| sign(pi()/2) | asin(1)            | cos(pi()) | acos(-1)          | tan(pi()/4)        | atan(1)            | cot(0.5)          |
+--------------+--------------------+-----------+-------------------+--------------------+--------------------+-------------------+
|            1 | 1.5707963267948966 |        -1 | 3.141592653589793 | 0.9999999999999999 | 0.7853981633974483 | 1.830487721712452 |
+--------------+--------------------+-----------+-------------------+--------------------+--------------------+-------------------+