MySQL笔记之数学函数详解

绝对值函数ABS(x)和圆周率函数PI()

复制代码 代码如下:

mysql> SELECT ABS(0.5), ABS(-0.5), PI();
+----------+-----------+----------+
| ABS(0.5) | ABS(-0.5) | PI()|
+----------+-----------+----------+
|0.5 |0.5 | 3.141593 |
+----------+-----------+----------+
row in set (0.00 sec)

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

复制代码 代码如下:

mysql> SELECT SQRT(16), SQRT(3), MOD(13,4);
+----------+--------------------+-----------+
| SQRT(16) | SQRT(3)| MOD(13,4) |
+----------+--------------------+-----------+
|4 | 1.7320508075688772 |1 |
+----------+--------------------+-----------+
row in set (0.00 sec)

取整函数CEIL(x)、CEILING(x)和FLOOR(x)

复制代码 代码如下:

mysql> SELECT CEIL(2.3), CEIL(-2.3), CEILING(2.3), CEILING(-2.3);
+-----------+------------+--------------+---------------+
| CEIL(2.3) | CEIL(-2.3) | CEILING(2.3) | CEILING(-2.3) |
+-----------+------------+--------------+---------------+
|3 |-2 |3 |-2 |
+-----------+------------+--------------+---------------+
row in set (0.00 sec)

mysql> SELECT FLOOR(2.3), FLOOR(-2.3);
+------------+-------------+
| FLOOR(2.3) | FLOOR(-2.3) |
+------------+-------------+
|2 |-3 |
+------------+-------------+
row in set (0.00 sec)

FLOOR(x)返回小于或等于x的最大整数

随机数函数RAND()和RAND(x)

复制代码 代码如下:

mysql> SELECT RAND(), RAND(2), RAND(2);
+--------------------+--------------------+--------------------+
| RAND()| RAND(2)| RAND(2)|
+--------------------+--------------------+--------------------+
| 0.8269294489425881 | 0.6555866465490187 | 0.6555866465490187 |
+--------------------+--------------------+--------------------+
row in set (0.00 sec)

区别在于，RAND()返回的数是完全随机的，而RAND(x)在x相同时返回的值相同

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

复制代码 代码如下:

mysql> SELECT ROUND(2.3), ROUND(2.5), ROUND(2.53,1), ROUND(2.55,1);
+------------+------------+---------------+---------------+
| ROUND(2.3) | ROUND(2.5) | ROUND(2.53,1) | ROUND(2.55,1) |
+------------+------------+---------------+---------------+
|2 |3 |2.5 |2.6 |
+------------+------------+---------------+---------------+
row in set (0.00 sec)

ROUND(x,y)返回x保留到小数点后y位的值，在截取时进行四舍五入处理

复制代码 代码如下:

mysql> SELECT TRUNCATE(2.53,1), TRUNCATE(2.55,1);
+------------------+------------------+
| TRUNCATE(2.53,1) | TRUNCATE(2.55,1) |
+------------------+------------------+
|2.5 |2.5 |
+------------------+------------------+
row in set (0.00 sec)

符号函数SIGN(x)

复制代码 代码如下:

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

幂运算函数POW(x,y)、POWER(x,y)

复制代码 代码如下:

mysql> SELECT POW(3,2), POWER(3,2);
+----------+------------+
| POW(3,2) | POWER(3,2) |
+----------+------------+
|9 |9 |
+----------+------------+
row in set (0.00 sec)