Summary of MySQL mathematical functions _ MySQL

This article mainly introduces a concise summary of MySQL mathematical functions. This article summarizes most of the commonly used MySQL mathematical functions and provides examples for use. For more information, see 1. ABS (x): returns the absolute value of x.

The code is as follows:

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

2. PI (): returns the circumference rate

The code is as follows:

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

3. SQRT (x): returns the square root of x, required (x is not negative, return NULL)

The code is as follows:

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

4. MOD (x, y): returns the remainder of x after division by y. It also applies to the data values with decimals. it returns the exact remainder after division.

The code is as follows:

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

5. CEIL (X): returns the smallest integer of X, and converts the returned value to a BIGINT.

The code is as follows:

Mysql> select CEIL (-3.35), CEIL (3.35 );
+ ------------- + ------------ +
| CEIL (-3.35) | CEIL (3.35) |
+ ------------- + ------------ +
|-3 | 4 |
+ ------------- + ------------ +

6. CEILING (X): Same as CEIL (X)

The code is as follows:

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

7. FLOOR (X): returns the maximum integer not greater than X. The return value is converted into a BIGINT.

The code is as follows:

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 ranging from 0 ~ Between 1, X is an integer, which is called a seed value to generate a recurring series. That is, when the X value is the same, the random number is also generated;

The code is as follows:

Mysql> select RAND (10), RAND (10), RAND (2), RAND (-2 );
+ -------------------- + ---------------------- + -------------------- +
| RAND (10) | RAND (10) | RAND (2) | RAND (-2) |
+ -------------------- + ---------------------- + -------------------- +
| 0.6570515219653505 | 0.6570515219653505 | 0.6555866465490187 | 0.6548542125661431 |
+ -------------------- + ---------------------- + -------------------- +

RAND (): RAND () without parameters each time produces different 0 ~ Random number between 1

The code is as follows:

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

9. ROUND (X) and ROUND (X, Y): returns X rounded to Y. Y can be omitted. the default value is 0. if Y is not 0, the specified Y digits after the decimal point are retained.

The code is as follows:

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

10. TRUNCATE (X, Y): similar to the ROUND (X, Y) function, but it is not rounded down and only intercepted.

The code is as follows:

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

11. SIGN (X): return the symbol of parameter X. If the value of X is negative, zero, or positive, the returned result is-or 1 in sequence.

The code is as follows:

Mysql> select SIGN (-21), SIGN (-0), SIGN (0), SIGN (0.0), SIGN (21 );
+ ----------- + ---------- + --------- + ----------- + ---------- +
| SIGN (-21) | SIGN (-0) | SIGN (0) | SIGN (0.0) | SIGN (21) |
+ ----------- + ---------- + --------- + ----------- + ---------- +
|-1 | 0 | 0 | 0 | 1 |
+ ----------- + ---------- + --------- + ----------- + ---------- +

12. POW (X, Y), POWER (X, Y), and EXP (X)

POW (X, Y) has the same function as POWER (X, Y). it is used to return the result value of X's Y multiplication.

The code is as follows:

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

Mysql> select power (2, 2), power (2,-2), power (-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 of X after multiplication of e:

The code is as follows:

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

13. LOG (X) and LOG10 (X): logarithm operation function (X must be a positive number), LOG (X)-returns the natural logarithm of X (X relative to the logarithm of base e) LOG10 (X)-returns the base 10 logarithm of x:

The code is as follows:

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 | 1.0986122886681098 | NULL | 2 |
+ --------- + -------- + -------------------- + ------------- + ---------- + ------------ +

14. RADIANS (X) and DEGREES (X): Angle and RADIANS conversion functions

The code is as follows:

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

15. 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): arcsin function where X must be between-1 and 1

COS (X): cosine function, where X is the radian value
ACOS (X): arccosine function where X must be between-1 and 1
TAN (X): tangent function, where X is the radian value
ATAN (X): returns the arc tangent function. ATAN (X) and TAN (X) are inverse functions.

COT (X): cotangent function. the COT and TAN functions are reciprocal functions.

The code is as follows:

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