数学函数
(1) 绝对值函数:ABS(x) ABS(x) 用于返回 x 的绝对值 (0的绝对值都是0,其他绝对值都是正)
mysql> SELECT ABS(2), ABS(-2.3), ABS(-33);
+--------+-----------+----------+
| ABS(2) | ABS(-2.3) | ABS(-33) |
+--------+-----------+----------+
| 2 | 2.3 | 33 |
+--------+-----------+----------+
(2) 返回圆周率的函数:PI() PI() 返回圆周率值
mysql> SELECT PI();
+----------+
| PI() |
+----------+
| 3.141593 |
+----------+
(3) 平方根函数:SQRT(x) SQRT(x) 返回非负数 x 的二次方根
mysql> SELECT SQRT(9), SQRT(40), SQRT(-49);
+---------+-------------------+-----------+
| SQRT(9) | SQRT(40) | SQRT(-49) |
+---------+-------------------+-----------+
| 3 | 6.324555320336759 | NULL |
+---------+-------------------+-----------+
(4) 求余函数:MOD(x,y) MOD(x,y) 返回 x 被 y 除后的余数,MOD(x,y) 对于带有小数部分的数值也起作用,它返回除法运算后的精确余数
mysql> SELECT MOD(31,8), MOD(234,10), MOD(45.5,6);
+-----------+-------------+-------------+
| MOD(31,8) | MOD(234,10) | MOD(45.5,6) |
+-----------+-------------+-------------+
| 7 | 4 | 3.5 |
+-----------+-------------+-------------+
(5) 获取整数的函数:CEIL(x) 、CEILING(x) 、FLOOR(x) CEIL(x) 用于返回不小于 x 的最小整数值
mysql> SELECT CEIL(-3.35), CEIL(3.35);
+-------------+------------+
| CEIL(-3.35) | CEIL(3.35) |
+-------------+------------+
| -3 | 4 |
+-------------+------------+
CEILING(x) 用于返回不小于 x 的最小整数
mysql> SELECT CEILING(-3.35), CEILING(3.35);
+----------------+---------------+
| CEILING(-3.35) | CEILING(3.35) |
+----------------+---------------+
| -3 | 4 |
+----------------+---------------+
1 row in set (0.00 sec)
FLOOR(x) 返回不大于 x 的最大整数值
mysql> SELECT FLOOR(-3.35), FLOOR(3.35);
+--------------+-------------+
| FLOOR(-3.35) | FLOOR(3.35) |
+--------------+-------------+
| -4 | 3 |
+--------------+-------------+
(6) 获取随机数的函数:RAND() 、RAND(x) RAND() 用于返回一个随机浮点值,范围在 0 ~ 1 之间
mysql> SELECT RAND(), RAND(), RAND();
+--------------------+--------------------+---------------------+
| RAND() | RAND() | RAND() |
+--------------------+--------------------+---------------------+
| 0.7393965169222994 | 0.5185907432051289 | 0.37476419599239175 |
+--------------------+--------------------+---------------------+
RAND(x) 用于返回一个随机浮点值,范围在 0~1 之间,x 被用作种子值,用来产生重复序列
mysql> SELECT RAND(5), RAND(5), RAND(8);
+---------------------+---------------------+---------------------+
| RAND(5) | RAND(5) | RAND(8) |
+---------------------+---------------------+---------------------+
| 0.40613597483014313 | 0.40613597483014313 | 0.15668530311126755 |
+---------------------+---------------------+---------------------+
(7) 四舍五入的函数:ROUND(x) 、ROUND(x,y) ROUND(x) 用于对 x 进行四舍五入
mysql> SELECT ROUND(-1.14), ROUND(1.14);
+--------------+-------------+
| ROUND(-1.14) | ROUND(1.14) |
+--------------+-------------+
| -1 | 1 |
+--------------+-------------+
ROUND(x,y) 用于对 x 进行四舍五入,并保留小数点后 y 位
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 |
+---------------+---------------+------------------+------------------+
(8) 截取数值的函数:TRUNCATE(x,y) TRUNCATE(x,y) 用于对 x 进行截取,结果保留小数点后 y 位
mysql> SELECT TRUNCATE(1.31,1), TRUNCATE(1.99,1), TRUNCATE(1.99,0);
+------------------+------------------+------------------+
| TRUNCATE(1.31,1) | TRUNCATE(1.99,1) | TRUNCATE(1.99,0) |
+------------------+------------------+------------------+
| 1.3 | 1.9 | 1 |
+------------------+------------------+------------------+
(9) 符号函数:SIGN(x) SIGN(x) 用于返回参数 x 的符号,当 x 为负数时返回 -1 ,当 x 为正数时返回 1 , 当 x 为零时返回 0
mysql> SELECT SIGN(-21), SIGN(0), SIGN(21);
+-----------+---------+----------+
| SIGN(-21) | SIGN(0) | SIGN(21) |
+-----------+---------+----------+
| -1 | 0 | 1 |
+-----------+---------+----------+
(10) 幂运算函数:POW(x,y) 、POWER(x,y) 、EXP(x) POW(x,y) 用于返回 x 的 y 次方的结果
mysql> SELECT POW(2,4), POW(2,-4);
+----------+-----------+
| POW(2,4) | POW(2,-4) |
+----------+-----------+
| 16 | 0.0625 |
+----------+-----------+
(11) 对数运算函数:LOG(x) 、LOG10(x) LOG(x) 返回 x 的自然对数
mysql> SELECT LOG(3), LOG(-3);
+--------------------+---------+
| LOG(3) | LOG(-3) |
+--------------------+---------+
| 1.0986122886681098 | NULL |
+--------------------+---------+
使用 LOG10(x) 计算以 10 为基数的对数
mysql> SELECT LOG10(2), LOG10(100), LOG10(-100);
+--------------------+------------+-------------+
| LOG10(2) | LOG10(100) | LOG10(-100) |
+--------------------+------------+-------------+
| 0.3010299956639812 | 2 | NULL |
+--------------------+------------+-------------+
(12) 角度与弧度相互转换的函数:RADIANS(x) 、DEGREES(x) RADIANS(x) 用于将参数 x 由角度转化为弧度
+--------------------+-------------------+
| RADIANS(90) | RADIANS(180) |
+--------------------+-------------------+
| 1.5707963267948966 | 3.141592653589793 |
+--------------------+-------------------+
DEGREES(x) 用于将参数 x 由弧度转化为角度
mysql> SELECT DEGREES(PI()), DEGREES(PI()/2);
+---------------+-----------------+
| DEGREES(PI()) | DEGREES(PI()/2) |
+---------------+-----------------+
| 180 | 90 |
+---------------+-----------------+
(13) 正弦函数和反正弦函数:SIN(x) 、ASIN(x) SIN(x) 用于返回 x 的正弦值,其中 x 为弧度值
mysql> SELECT SIN(1), ROUND(SIN(PI()));
+--------------------+------------------+
| SIN(1) | ROUND(SIN(PI())) |
+--------------------+------------------+
| 0.8414709848078965 | 0 |
+--------------------+------------------+
ASIN(x) 用于返回 x 的反正弦,即正弦为 x 的值
mysql> SELECT ASIN(0.8414709848078965), ASIN(3);
+--------------------------+---------+
| ASIN(0.8414709848078965) | ASIN(3) |
+--------------------------+---------+
| 1 | NULL |
+--------------------------+---------+
(14) 余弦函数和反余弦函数:COS(x) 、ACOS(x) COS(x) 用于返回 x 的余弦,其中 x 为弧度值
mysql> SELECT COS(0), COS(PI()), COS(1);
+--------+-----------+--------------------+
| COS(0) | COS(PI()) | COS(1) |
+--------+-----------+--------------------+
| 1 | -1 | 0.5403023058681397 |
+--------+-----------+--------------------+
ACOS(x) 用于返回 x 的反余弦,即余弦是 x 的值
mysql> SELECT ACOS(1), ACOS(0), ROUND(ACOS(0.5403023058681397));
+---------+--------------------+---------------------------------+
| ACOS(1) | ACOS(0) | ROUND(ACOS(0.5403023058681397)) |
+---------+--------------------+---------------------------------+
| 0 | 1.5707963267948966 | 1 |
+---------+--------------------+---------------------------------+
(15) 正切函数、反正切函数、余切函数:TAN(x) 、ATAN(x) 、COT(x) TAN(x) 返回 x 的正切,其中 x 为给定的弧度值
mysql> SELECT TAN(0.3), ROUND(TAN(PI()/4));
+---------------------+--------------------+
| TAN(0.3) | ROUND(TAN(PI()/4)) |
+---------------------+--------------------+
| 0.30933624960962325 | 1 |
+---------------------+--------------------+
ATAN(x) 用于返回 x 的反正切,即正切为 x 的值