MySQL的数学函数

1、绝对值函数ABS和返回圆周率函数PI

MariaDB [vincen]> select ABS(2),ABS(-3.3),ABS(-33);
+--------+-----------+----------+
| ABS(2) | ABS(-3.3) | ABS(-33) |
+--------+-----------+----------+
|      2 |       3.3 |       33 |
+--------+-----------+----------+
1 row in set (0.00 sec)
MariaDB [vincen]> select PI();
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.01 sec)
#PI()返回圆周率π的值。默认显示7位有效数字

2、平方根函数SQRT

MariaDB [vincen]> select SQRT(9),SQRT(40),SQRT(-49);
+---------+-------------------+-----------+
| SQRT(9) | SQRT(40)          | SQRT(-49) |
+---------+-------------------+-----------+
|       3 | 6.324555320336759 |      NULL |
+---------+-------------------+-----------+
1 row in set (0.00 sec)
#9的平方根是9；40的平方是6.324555320336759；负数没有平方根
MariaDB [vincen]> select MOD(31,8),MOD(234,10);
+-----------+-------------+
| MOD(31,8) | MOD(234,10) |
+-----------+-------------+
|         7 |           4 |
+-----------+-------------+
1 row in set (0.00 sec)
#MOD(x,y)返回x被y除后的余数

3、获取整数函数CEIL、CEILING和FLOOR

MariaDB [vincen]> select CEIL(-3.35),CEILING(3.35);
+-------------+---------------+
| CEIL(-3.35) | CEILING(3.35) |
+-------------+---------------+
|          -3 |             4 |
+-------------+---------------+
1 row in set (0.00 sec)
#CEIL(x)和CEILING(x)一样，返回不小于x的最小整数
MariaDB [vincen]> select FLOOR(-3.35),FLOOR(3.35);
+--------------+-------------+
| FLOOR(-3.35) | FLOOR(3.35) |
+--------------+-------------+
|           -4 |           3 |
+--------------+-------------+
1 row in set (0.00 sec)
#FLOOR返回一个不大于x的最大整数

4、获取随机数的函数RAND

MariaDB [vincen]> select RAND(),RAND();
+---------------------+--------------------+
| RAND()              | RAND()             |
+---------------------+--------------------+
| 0.05926004616474737 | 0.6242725966724312 |
+---------------------+--------------------+
1 row in set (0.01 sec)
#RAND(x)返回一个随机浮点值，范围在0到1之间
MariaDB [vincen]> select rand(10),rand(10),rand(11);
+--------------------+--------------------+-------------------+
| RAND(10)           | RAND(10)           | RAND(11)          |
+--------------------+--------------------+-------------------+
| 0.6570515219653505 | 0.6570515219653505 | 0.907234631392392 |
+--------------------+--------------------+-------------------+
1 row in set (0.00 sec)
#RAND()指定相同参数后,将产生相同的值，不同的参数产生的值不同

5、函数ROUND和TRUNCATE

MariaDB [vincen]> select ROUND(-1.14),ROUND(-1.67),ROUND(1.14);
+--------------+--------------+-------------+
| ROUND(-1.14) | ROUND(-1.67) | ROUND(1.14) |
+--------------+--------------+-------------+
|           -1 |           -2 |           1 |
+--------------+--------------+-------------+
1 row in set (0.00 sec)
#ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入
MariaDB [vincen]> select ROUND(1.38,1),ROUND(1.38,0);
+---------------+---------------+
| ROUND(1.38,1) | ROUND(1.38,0) |
+---------------+---------------+
|           1.4 |             1 |
+---------------+---------------+
1 row in set (0.00 sec)
#ROUND(x,y)返回最接近于参数x的数，其值保留了各个值得整数部分
MariaDB [vincen]> select TRUNCATE(1.31,1),TRUNCATE(1.99,1);
+------------------+------------------+
| TRUNCATE(1.31,1) | TRUNCATE(1.99,1) |
+------------------+------------------+
|              1.3 |              1.9 |
+------------------+------------------+
1 row in set (0.00 sec)
#TRUNCATE(x,y)返回被舍去至小数点后y位的数字x

6、符号函数SIGN

MariaDB [vincen]> select SIGN(-21),SIGN(0);
+-----------+---------+
| SIGN(-21) | SIGN(0) |
+-----------+---------+
|        -1 |       0 |
+-----------+---------+
1 row in set (0.00 sec)
#SIGN(x)返回参数的符号，x的值为负、零或者正时返回结果依次为-1、0或1

7、幂运算函数POW、POWER和EXP

MariaDB [vincen]> select POW(2,2),POWER(2,2),POW(2,-2),POWER(2,-2);
+----------+------------+-----------+-------------+
| POW(2,2) | POWER(2,2) | POW(2,-2) | POWER(2,-2) |
+----------+------------+-----------+-------------+
|        4 |          4 |      0.25 |        0.25 |
+----------+------------+-----------+-------------+
1 row in set (0.00 sec)
#POW(x,y)或者POWER(x,y)函数返回x的y次乘方的结果值
MariaDB [vincen]> select EXP(3),EXP(0);
+--------------------+--------+
| EXP(3)             | EXP(0) |
+--------------------+--------+
| 20.085536923187668 |      1 |
+--------------------+--------+
1 row in set (0.00 sec)
#EXP(x)返回e的x乘方后的值

8、对数运算函数LOG和LOG10

MariaDB [vincen]> select LOG(3),LOG(-3);  #LOG(x)返回x的自然对数
+--------------------+---------+
| LOG(3)             | LOG(-3) |
+--------------------+---------+
| 1.0986122886681098 |    NULL |
+--------------------+---------+
1 row in set (0.01 sec)
MariaDB [vincen]> select LOG10(-100),LOG10(100); 
+-------------+------------+
| LOG10(-100) | LOG10(100) |
+-------------+------------+
|        NULL |          2 |
+-------------+------------+
1 row in set (0.00 sec)
#LOG10(x)返回x的基数为10的对数

9、正弦函数SIN和反正弦函数ASIN

MariaDB [vincen]> select SIN(0.5),ROUND(SIN(PI()));
+-------------------+------------------+
| SIN(0.5)          | ROUND(SIN(PI())) |
+-------------------+------------------+
| 0.479425538604203 |                0 |
+-------------------+------------------+
1 row in set (0.00 sec)
#SIN(x)返回x正弦，其中x为弧度值
MariaDB [vincen]> select ASIN(0.479425538604203),ASIN(0.5);
+-------------------------+--------------------+
| ASIN(0.479425538604203) | ASIN(0.5)          |
+-------------------------+--------------------+
|                     0.5 | 0.5235987755982989 |
+-------------------------+--------------------+
1 row in set (0.00 sec)
#ASIN(x)返回x的反正弦，即正弦为x的值。如果x不在-1到1之间，则返回NULL

10、余弦函数COS和反余弦函数ACOS

MariaDB [vincen]> select COS(0),COS(PI()),COS(1);
+--------+-----------+--------------------+
| COS(0) | COS(PI()) | COS(1)             |
+--------+-----------+--------------------+
|      1 |        -1 | 0.5403023058681398 |
+--------+-----------+--------------------+
1 row in set (0.00 sec)
#COS(x)返回x的余弦值
MariaDB [vincen]> select ACOS(1),ROUND(ACOS(0.5403023058681398));
+---------+---------------------------------+
| ACOS(1) | ROUND(ACOS(0.5403023058681398)) |
+---------+---------------------------------+
|       0 |                               1 |
+---------+---------------------------------+
1 row in set (0.01 sec)
#ACOS(x)返回x的反余弦，也就余弦是x的值