Mathematical function explanation of MySQL notes
- 2020-05-17 06:41:00
- OfStack
The absolute value function ABS(x) and the PI function 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)
The square root function SQRT(x) and the remainder function 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)
Rounding functions CEIL(x), CEILING(x), and 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)
CEIL(x) and CEILING(x) return the smallest integer greater than or equal to x
FLOOR(x) returns the maximum integer less than or equal to x
Random number functions RAND() and 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)
The functions RAND() and RAND(x) are missing random Numbers that return 0 to 1
The difference is that RAND() returns a completely random number, while RAND(x) returns the same value at x
4 rounding 5 into the functions ROUND(x), ROUND(x,y) and 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) returns the nearest integer to x, which is x rounded by 4 and 5
ROUND(x,y) returns the value of x reserved to the y bit after the decimal point, and is rounded 4 and 5 when intercepting
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)
TRUNCATE(x,y) returns the value of x reserved to the y place after the decimal point, no rounding of 4 or 5
Sign function 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)
SIGN(x) returns the symbol x, minus 1 is a negative number, 0 is unchanged, and 1 is an integer
Power operation functions 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)