MySQL function learning Note one: Mathematical functions

1. ABS (x): Returns the absolute value of X

Mysql> Select ABS (1), ABS ( -1), ABS (0);
+--------+---------+--------+
| ABS (1) | ABS (-1) | ABS (0) |
+--------+---------+--------+
|       1 |      1 | 0 |
+--------+---------+--------+

2. Pi (): Return pi

Mysql> select PI ();
+----------+
| PI () |
+----------+
| 3.141593 |
+----------+

3. SQRT (x): Returns the square root of x, requires (X is non-negative, returns null)

Mysql> Select SQRT, SQRT (0), SQRT ( -49);
+----------+---------+-----------+
| SQRT (49) | SQRT (0) | SQRT (-49) |
+----------+---------+-----------+
|        7 |        0 |      NULL |
+----------+---------+-----------+


4. MOD (x, y): The remainder function that returns the remnant of x by Y, and for a data value with a fractional part, which returns the exact remainder after the division operation.

Mysql> Select mod (31,8), mod (21,-8), mod ( -7,2), mod ( -7,-2), mod (45.5,6);
+-----------+------------+-----------+------------+-------------+
| MOD (31,8) | MOD (21,-8) | MOD ( -7,2) | MOD ( -7,-2) | MOD (45.5,6) |
+-----------+------------+-----------+------------+-------------+
|          7 |        5 |         -1 |         -1 | 3.5 |
+-----------+------------+-----------+------------+-------------+

5. Ceil (x): Returns the smallest integer value of no small X, and the return value is converted to a bigint.

Mysql> Select Ceil ( -3.35), ceil (3.35);
+-------------+------------+
| Ceil (-3.35) | Ceil (3.35) |
+-------------+------------+
|          -3 | 4 |
+-------------+------------+

6. CEILING (x): Same as Ceil (x)

Mysql> Select CEILING ( -3.35), CEILING (3.35);
+----------------+---------------+
| CEILING (-3.35) | CEILING (3.35) |
+----------------+---------------+
|             -3 | 4 |
+----------------+---------------+

7. Floor (x): Returns the maximum integer value not greater than X, and the return value is converted to a bigint.

Mysql> Select Floor ( -3.35), floor (3.35);
+--------------+-------------+
| Floor (-3.35) | Floor (3.35) |
+--------------+-------------+
|           -4 | 3 |
+--------------+-------------+

8. Rand () and rand (X)

RAND (X) returns a random floating-point value, in the range between 0~1 and X as an integer, called the seed value, to produce a repeating sequence. That is, when the X value is the same, the random number produced is the same;

Mysql> Select rand (TEN), Rand (2), Rand (-2);
+--------------------+--------------------+--------------------+--------------------+
| RAND (          ) | RAND (          ) | RAND (2)             | RAND ( -2)            |
+--------------------+--------------------+--------------------+--------------------+
| 0.6570515219653505 | 0.6570515219653505 | 0.6555866465490187 | 0.6548542125661431 |
+--------------------+--------------------+--------------------+--------------------+

RAND (): Rand () with no parameters produces a random number between different 0~1 each time

Mysql> SELECT rand (), RAND (), Rand ();
+--------------------+--------------------+---------------------+
| RAND () | RAND () | RAND () |
+--------------------+--------------------+---------------------+
| 0.6931893636409094 | 0.5147262984092592 | 0.49406343185721285 |
+--------------------+--------------------+---------------------+

9. ROUND (x) and ROUND (y): rounding function, rounding X values according to Y, Y can be omitted, default value is 0, if Y is not 0, the y bit after the decimal point is reserved.

Mysql> Select ROUND ( -1.14), ROUND ( -1.9), ROUND (1.14), ROUND (1.9);
+--------------+-------------+-------------+------------+
| ROUND (-1.14) | ROUND (-1.9) | ROUND (1.14) | ROUND (1.9) |
+--------------+-------------+-------------+------------+
|          -1 |           -2 |          1 | 2 |
+--------------+-------------+-------------+------------+

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 |
+---------------+---------------+------------------+------------------+

TRUNCATE (x, y): similar to the round (x, y) function but not rounded, only intercepts.

Mysql> Select TRUNCATE (1.33,1), TRUNCATE (1.99,1), TRUNCATE (1.99,0), TRUNCATE (19.99,-1);
+------------------+------------------+------------------+--------------------+
| TRUNCATE (1.33,1) | TRUNCATE (1.99,1) | TRUNCATE (1.99,0) | TRUNCATE (19.99,-1) |
+------------------+------------------+------------------+--------------------+
|              1.3 |                1.9 |                 1 | 10 |
+------------------+------------------+------------------+--------------------+

Sign (x): Returns the symbol for parameter x, the value of x is negative, 0, or positive when the return result is -1,0 or 1mysql> Select sign ( -21), signs ( -0), signed (0), Mark (0.0), sign (21);
+-----------+----------+---------+-----------+----------+
| Sign (-21) | Sign (-0) | Sign (0) | Sign (0.0) | Sign (21) |
+-----------+----------+---------+-----------+----------+
|        -1 |       0 |         0 |        0 | 1 |
+-----------+----------+---------+-----------+----------+

POW (x, y), POWER (both x, y) and exp (×)

The POW (x, y) function is the same as power (x, y) to return the result value of the X's Y-time

Mysql> Select POW (2,2), pow (2,-2), pow ( -2,2), pow ( -2,-2);
+----------+-----------+-----------+------------+
| Pow (2,2) | Pow (2,-2) | Pow ( -2,2) | Pow ( -2,-2) |
+----------+-----------+-----------+------------+
|      4 |         0.25 |       4 | 0.25 |
+----------+-----------+-----------+------------+

Mysql> Select Power (2,2), Power (2,-2), Power ( -2,2), Power ( -2,-2);
+------------+-------------+-------------+--------------+
| Power (2,2) | Power (2,-2) | Power ( -2,2) | Power ( -2,-2) |
+------------+-------------+-------------+--------------+
|        4 |           0.25 |         4 | 0.25 |
+------------+-------------+-------------+--------------+

EXP (x): Returns the value after the X-exponentiation of E:

Mysql> Select exp (3), exp (0), exp (-3);
+-------------------+--------+---------------------+
| EXP (3) | EXP (0) | EXP (-3) |
+-------------------+--------+---------------------+
|      20.08553692318767 | 1 | 0.04978706836786393 |
+-------------------+--------+---------------------+

Log (x) and LOG10 (x): Logarithmic arithmetic function (x must be a positive number), log (x)-Returns the natural logarithm of x (the logarithm of x relative to Cardinal E) LOG10 (x)-Returns the logarithm of the cardinality of X of 10:

Mysql> Select log ( -3), log (0), log (3), LOG10 ( -100), LOG10 (0), LOG10 (100);
+---------+--------+--------------------+-------------+----------+------------+
| LOG (-3) | LOG (0) | LOG (3)              | LOG10 (-100) | LOG10 (0) | LOG10 (100) |
+---------+--------+--------------------+-------------+----------+------------+
|    NULL |   NULL | 1.0986122886681098 |        null |     null |           2 |
+---------+--------+--------------------+-------------+----------+------------+

14. RADIANS (x) and DEGREES (x): angle and radian conversion function

Mysql> Select RADIANS (RADIANS), DEGREES (Pi ()), DEGREES (Pi ()/2);
+--------------------+-------------------+---------------+-----------------+
| RADIANS (90) | RADIANS (180) | DEGREES (PI ()) | DEGREES (PI ()/2) |
+--------------------+-------------------+---------------+-----------------+
| 1.5707963267948966 |           3.141592653589793 |              180 | 90 |
+--------------------+-------------------+---------------+-----------------+

SIN (x), ASIN (x), COS (x), ACOS (x), TAN (x), ATAN (x), COT (x)

Sin (x): sine function, where X is the Radian value

ASIN (x): the inverse chord function where X must be between 1 and 1

cos (x): cosine function, where X is the Radian value

ACOS (x): the inverse cosine function where X must be between 1 and 1

TAN (x): Tangent function, where X is a radian value

ATAN (x): Inverse tangent function, ATAN (x) and tan (x) Reciprocal functions

Cot (X): cotangent function, function cot and tan reciprocal functions

Mysql> Select sign (PI ()/2), ASIN (1), COS (Pi ()), ACOS ( -1), TAN (Pi ()/4), ATAN (1), COT (0.5);
+--------------+--------------------+-----------+-------------------+--------------------+--------------------+ -------------------+
| Sign (PI ()/2) | ASIN (1) | COS (PI ()) | ACOS (-1) | TAN (PI ()/4) | ATAN (1) | COT (0.5) |
+--------------+--------------------+-----------+-------------------+--------------------+--------------------+ -------------------+
| 1 |        1.5707963267948966 | -1 | 3.141592653589793 | 0.9999999999999999 | 0.7853981633974483 | 1.830487721712452 |
+--------------+--------------------+-----------+-------------------+--------------------+--------------------+ -------------------+

MySQL function learning Note one: Mathematical functions