1. ABS (x): Returns the absolute value of X
Copy Code code as follows:
Mysql> Select ABS (1), ABS ( -1), ABS (0);
+--------+---------+--------+
| ABS (1) | ABS (-1) | ABS (0) |
+--------+---------+--------+
| 1 | 1 | 0 |
+--------+---------+--------+
2. Pi (): Return pi
Copy Code code as follows:
Mysql> select PI ();
+----------+
| PI () |
+----------+
| 3.141593 |
+----------+
3. SQRT (x): Returns the square root of x, required (x is non-negative, returns null)
Copy Code code as follows:
Mysql> Select SQRT (a), SQRT (0), SQRT (-49);
+----------+---------+-----------+
| SQRT (49) | SQRT (0) | SQRT (-49) |
+----------+---------+-----------+
| 9 2 0 | NULL |
+----------+---------+-----------+
4. MOD (x,y): The remainder function, which returns x to be removed by y; for a data value with a decimal part, it returns the exact remainder of the division operation.
Copy Code code as follows:
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 minimum integer value of a small X, and the return value is converted to a bigint.
Copy Code code as follows:
Mysql> Select Ceil ( -3.35), ceil (3.35);
+-------------+------------+
| Ceil (-3.35) | Ceil (3.35) |
+-------------+------------+
| -3 | 4 |
+-------------+------------+
6. CEILING (x): Same ceil (x)
Copy Code code 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 value that is not greater than X, and the return value is converted to a bigint.
Copy Code code 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 between 0~1 and X as an integer, called the seed value, used to produce a repeating sequence. That is, when the X value is the same, the random number produced is the same;
Copy Code code as follows:
Mysql> Select Rand, rand (a), Rand (2), Rand (-2);
+--------------------+--------------------+--------------------+--------------------+
| RAND (10) | RAND (10) | RAND (2) | RAND (-2) |
+--------------------+--------------------+--------------------+--------------------+
| 0.6570515219653505 | 0.6570515219653505 | 0.6555866465490187 | 0.6548542125661431 |
+--------------------+--------------------+--------------------+--------------------+
RAND (): rand () with no parameters produces random numbers between different 0~1 each time
Copy Code code as follows:
Mysql> SELECT rand (), RAND (), Rand ();
+--------------------+--------------------+---------------------+
| RAND () | RAND () | RAND () |
+--------------------+--------------------+---------------------+
| 0.6931893636409094 | 0.5147262984092592 | 0.49406343185721285 |
+--------------------+--------------------+---------------------+
9. ROUND (x) and ROUND (x,y): Rounding function, rounding X values by Y, Y can omit, the default value is 0, if Y is not 0, keep the y-bit after the decimal point.
Copy Code code 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 |
+---------------+---------------+------------------+------------------+
TRUNCatE (x,y): Similar to round (x,y) function, but not rounded, only interception.
Copy Code code 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 |
+------------------+------------------+------------------+--------------------+
SIGN (x): Returns the symbol for the parameter x, which returns the result of -1,0 or 1 if the value of x is negative, 0, or positive
Copy Code code 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 |
+-----------+----------+---------+-----------+----------+
POW (X,y), Power (X,y) and exp (X)
POW (x,y) is the same as the Power (x,y) function, which returns the result value of the Y-exponentiation of X
Copy Code code as follows:
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 X of e:
Copy Code code as follows:
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 operational function (x must be positive), log (x)-Returns the natural logarithm of x (x relative to the logarithm of base e) LOG10 (x)-Returns the logarithm of x with a base of 10:
Copy Code code 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 | NULL | 1.0986122886681098 | NULL | NULL | 2 |
+---------+--------+--------------------+-------------+----------+------------+
RADIANS (x) and DEGREES (x): Angle and Radian Conversion functions
Copy Code code as follows:
Mysql> Select RADIANS (M), RADIANS (180), 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): Inverse cosine function where X must be between-1 to 1
TAN (x): Tangent function, where X is the Radian value
Atan (x): Inverse tangent function, atan (x) and tan (x) Reciprocal functions
COT (X): cotangent function, function COT and tan reciprocal functions
Copy Code code 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 |
+--------------+--------------------+-----------+-------------------+--------------------+--------------------+ -------------------+