MySQL study notes 18: mathematical functions _ MySQL

MySQL study note 18: mathematical functions bitsCN.com absolute value functions ABS (x) and circumference ratio functions PI ()

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

Square root function SQRT (x) and evaluate 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 |+----------+--------------------+-----------+1 row in set (0.00 sec)

Take the entire function 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 |+-----------+------------+--------------+---------------+1 row in set (0.00 sec)mysql> SELECT FLOOR(2.3), FLOOR(-2.3);+------------+-------------+| FLOOR(2.3) | FLOOR(-2.3) |+------------+-------------+|          2 |          -3 |+------------+-------------+1 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 largest 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 |+--------------------+--------------------+--------------------+1 row in set (0.00 sec)

The loss of the two functions RAND () and RAND (x) returns 0 ~ Random number of 1

The difference is that the number returned by RAND () is completely random, while the value returned by RAND (x) is the same when x is the same.

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

ROUND (x) returns the integer nearest to x, that is, rounding x.

ROUND (x, y) returns the value of x rounded to the y digit after the decimal point.

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

TRUNCATE (x, y) returns the value of x which is retained to the y-digit after the decimal point. The rounding operation is not performed.

SIGN function SIGN (x)

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

SIGN (x) returns the x symbol.-1 is a negative number, 0 is not changed, and 1 is an integer.

POWER functions POW (x, y), POWER (x, y)

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

BitsCN.com