Mysql笔记三之数学函数_mysql根据三角形三条边,计算三角形周长。调用演示。-优快云博客

Mysql笔记三之数学函数

数学函数主要处理数值数据。主要有绝对值函数，三角函数，对数函数，随机数函数。在有错误产生时，数学函数将会返回空值NULL。

1. 绝对值函数ABS(x) 和返回圆周率函数PI()

mysql> SELECT ABS(2),ABS(-3.3),ABS(-33);
+--------+-----------+----------+
| ABS(2) | ABS(-3.3) | ABS(-33) |
+--------+-----------+----------+
|      2 |       3.3 |       33 |
+--------+-----------+----------+
1 row in set (0.07 sec)

mysql> SELECT pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

总结：pi()保留7位有效数字

2.平方根函数SQRT(x)和求余函数MOD(x,y)

mysql> SELECT SQRT(9),SQRT(40),SQRT(-49);
+---------+-----------------+-----------+
| SQRT(9) | SQRT(40)        | SQRT(-49) |
+---------+-----------------+-----------+
|       3 | 6.3245553203368 |      NULL |
+---------+-----------------+-----------+
1 row in set (0.00 sec)

总结：负数没有平方根，因此-49返回NULL

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 |
+-----------+-------------+-------------+
1 row in set (0.00 sec)

总结：MOD(x,y)返回x被y除后的余数，MOD()对带有小数部分的数值也起作用

3.获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)

mysql> 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的最小整数。返回值转换为一个BIGINT.

mysql> SELECT FLOOR(-3.35),FLOOR(3.35);
+--------------+-------------+
| FLOOR(-3.35) | FLOOR(3.35) |
+--------------+-------------+
|           -4 |           3 |
+--------------+-------------+
1 row in set (0.00 sec)

总结：FLOOR(x)返回不大于x的最大整数，返回值转化为BIGINT.

4.获取随机函数RAND()和RAND(x)

mysql> SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(11);
+------------------+------------------+------------------+------------------+------------------+
| RAND()           | RAND()           | RAND(10)         | RAND(10)         | RAND(11)         |
+------------------+------------------+------------------+------------------+------------------+
| 0.17441298922004 | 0.73337492508197 | 0.65705152196535 | 0.65705152196535 | 0.90723463139239 |
+------------------+------------------+------------------+------------------+------------------+
1 row in set (0.02 sec)

总结：
a.RAND(x)返回一个随机浮点值，范围在0到1之间.
b.不带参数的RAND()每次产生的随机数值是不同的。
c.带参数的RAND(x)，当参数相同时，将产生相同的随机数，不同的x产生的随机数值不同。

5.四舍五入函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)

mysql> SELECT ROUND(-1.14),ROUND(-1.67),ROUND(1.14),ROUND(1.66);
+--------------+--------------+-------------+-------------+
| ROUND(-1.14) | ROUND(-1.67) | ROUND(1.14) | ROUND(1.66) |
+--------------+--------------+-------------+-------------+
|           -1 |           -2 |           1 |           2 |
+--------------+--------------+-------------+-------------+
1 row in set (0.00 sec)

总结：ROUND(x)返回最接近参数x的整数，对x值进行四舍五入。只保留各值的整数部分。

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 |
+---------------+---------------+------------------+------------------+
1 row in set (0.00 sec)

总结：ROUND(x,y)返回最接近于参数x的数，其值保留小数点后y位，若y为负值，则将保留x值到小数点左边y位。

mysql> SELECT TRUNCATE(1.31,1),TRUNCATE(1.99,1),TRUNCATE(1.99,0),TRUNCATE(19.99,-1);
+------------------+------------------+------------------+--------------------+
| TRUNCATE(1.31,1) | TRUNCATE(1.99,1) | TRUNCATE(1.99,0) | TRUNCATE(19.99,-1) |
+------------------+------------------+------------------+--------------------+
|              1.3 |              1.9 |                1 |                 10 |
+------------------+------------------+------------------+--------------------+
1 row in set (0.00 sec)

总结:TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截去x小数点左起第y位开始后面所有低位的值。

6.符号函数SIGN(x)

mysql> SELECT SIGN(-21),SIGN(0),SIGN(21);
+-----------+---------+----------+
| SIGN(-21) | SIGN(0) | SIGN(21) |
+-----------+---------+----------+
|        -1 |       0 |        1 |
+-----------+---------+----------+
1 row in set (0.00 sec)

总结：SIGN(x)返回参数的符合，x的值为负、零或者正时返回结果依次为-1、0或1。

7.幂运算函数POW(x,y)、POWER(x,y)和EXP(x)

mysql> 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次乘方的结果值。

mysql> SELECT EXP(3),EXP(-3),EXP(0);
+-----------------+-------------------+--------+
| EXP(3)          | EXP(-3)           | EXP(0) |
+-----------------+-------------------+--------+
| 20.085536923188 | 0.049787068367864 |      1 |
+-----------------+-------------------+--------+
1 row in set (0.00 sec)

总结：EXP(x)返回e 的x乘方后的值。