MySQL notes-mathematical functions-MySQL

Explanation of mathematical functions of MySQL notes

Absolute value functions ABS (x) and circumference rate functions 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)

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 |
+ ---------- + ------------------ + ----------- +
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 |
+ ----------- + ------------ + -------------- + --------------- +
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 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 |
+ -------------------- +
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 | 2 | 2.5 | 2.6 |
+ ------------ + --------------- +
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 |
+ ------------------ +
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 |
+ ---------- + --------- +
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 |
+ ---------- + ------------ +
Row in set (0.00 sec)

BitsCN.com