MySQL mathematical functions

All mathematical functions return NULL when an error occurs.

-
Subtract from a single object. Change the parameter symbol.
Mysql> select-2;

Note that if this operator is used with a BIGINT, the return value is a BIGINT! This means you should avoid using "-" On integers, which may have a value of-2 ^ 63!
ABS (X)
Returns the absolute value of X.
Mysql> select ABS (2 );
-> 2
Mysql> select ABS (-32 );
-> 32

This function can be safely used for BIGINT values.

SIGN (X)
The symbol of the returned parameter, which is-1, 0, or 1, depends on whether X is a negative, zero, or positive number.
Mysql> select SIGN (-32 );
->-1
Mysql> select SIGN (0 );
-> 0
Mysql> select SIGN (1, 234 );
-> 1

MOD (N, M)

%
Modulo (similar to the % operator in C ). Returns the remainder of N divided by M.
Mysql> select MOD (234, 10 );
-> 4
Mysql> select 253% 7;
-> 1
Mysql> select MOD (29,9 );
-> 2

This function can be safely used for BIGINT values.
FLOOR (X)
Returns the maximum integer not greater than X.

Mysql & gt; select FLOOR (1.23 );
-> 1
Mysql> select FLOOR (-1.23 );
->-2

Note that the returned value is converted into a BIGINT!
CEILING (X)
Returns the smallest integer value not less than X.
Mysql> select CEILING (1.23 );
-> 2
Mysql> select CEILING (-1.23 );
->-1

Note that the returned value is converted into a BIGINT!

ROUND (X)
Returns an integer rounded to X.
Mysql> select ROUND (-1.23 );
->-1
Mysql> select ROUND (-1.58 );
->-2
Mysql> select ROUND (1.58 );
-> 2

Note that the returned value is converted into a BIGINT!

ROUND (X, D)
Returns a number rounded to X with D as a decimal number. If D is 0, no decimal point or decimal point is returned.
Mysql> select ROUND (1.298, 1 );
-> 1.3
Mysql> select ROUND (1.298, 0 );
-> 1

Note that the returned value is converted into a BIGINT!

EXP (X)
Returns the X power of e (the base of the natural logarithm.
Mysql> select EXP (2 );
-> 7.389056
Mysql> select EXP (-2 );
-> 0.135335

LOG (X)
Returns the natural logarithm of X.
Mysql> select LOG (2 );
-> 0.693147
Mysql> select LOG (-2 );
-> NULL

If you want a base-B logarithm of number X, use the formula LOG (X)/LOG (B ).

LOG10 (X)
Returns the base-10 logarithm of X.
Mysql> select LOG10 (2 );
-> 0.301030
Mysql> select LOG10 (100 );
-> 2.000000
Mysql> select LOG10 (-100 );
-> NULL

POW (X, Y)

POWER (X, Y)
Returns the Power Y of X.
Mysql> select POW (2, 2 );
-> 4.000000
Mysql> select POW (2,-2 );
-> 0.250000
SQRT (X)
Returns the square root of non-negative X.
Mysql> select SQRT (4 );
-> 2.000000
Mysql> select SQRT (20 );
-> 4.472136

PI ()
Returns the PI value (circumference rate ).
Mysql> select PI ();
-> 3.141593

COS (X)
Returns the cosine of X. Here, X is given in radians.
Mysql> select COS (PI ());
->-1.000000

SIN (X)
Returns the sine of X, which is given in radians.
Mysql> select SIN (PI ());
-> 0.000000

TAN (X)
Returns the tangent of X, which is given in radians.
Mysql> select TAN (PI () + 1 );
-> 1.557408

ACOS (X)
Returns the arc cosine of X, that is, the cosine of X. If X is not in the range from-1 to 1, NULL is returned.
Mysql> select ACOS (1 );
-> 0.000000
Mysql> select ACOS (1.0001 );
-> NULL
Mysql> select ACOS (0 );
-> 1.570796

ASIN (X)
Returns the arc sine of X, that is, the sine of X. L if X is not in the range from-1 to 1, NULL is returned.
Mysql> select ASIN (0.2 );
-> 0.201358
Mysql> select ASIN ('foo ');
-> 0.000000

ATAN (X)
Returns the arc tangent value of X, that is, its positive tangent value is X.
Mysql> select ATAN (2 );
-> 1.107149
Mysql> select ATAN (-2 );
->-1.107149
ATAN2 (X, Y)
Returns the arc tangent of two variables X and Y. It is similar to calculating the arc tangent of Y/X, except that the two parameter symbols are used to determine the quadrant of the result.
Mysql> select ATAN (-2, 2 );
->-0.785398
Mysql> select ATAN (PI (), 0 );
-> 1.570796
COT (X)
Returns the tangent of X.
Mysql> select COT (12 );
->-1.57267341
Mysql> select COT (0 );
-> NULL

RAND ()

RAND (N)
Returns a random floating point value ranging from 0 to 1.0. If an integer parameter N is specified, it is used as a seed value.
Mysql> select RAND ();
-> 0.5925
Mysql> select RAND (20 );
-> 0.1811
Mysql> select RAND (20 );
-> 0.1811
Mysql> select RAND ();
-> 0.2079
Mysql> select RAND ();
-> 0.7888

You cannot use the column with the RAND () value in an order by clause, because order by will calculate the column multiple times. However, in MySQL3.23, You can do: SELECT * FROM table_name order by rand (), which is helpful for obtaining a data from select * FROM table1, table2 WHERE a = B AND c
LEAST (X, Y ,...)
If there are two or more parameters, the minimum (minimum) parameter is returned. Parameters are compared using the following rules:
If the return value is used in an INTEGER context, or all parameters are INTEGER values, they are compared as integers.
If the return value is used in a REAL context, or all parameters are REAL values, they are compared as REAL numbers.
If any parameter is a size-sensitive string, the parameter is compared as a case-sensitive string.
In other cases, parameters are compared as case-insensitive strings.
Mysql> select LEAST (2, 0 );
-> 0
Mysql> select LEAST (34.0, 3.0, 5.0, 767.0 );
-> 3.0
Mysql> select LEAST ("B", "A", "C ");
-> ""

In versions earlier than MySQL 3.22.5, you can use MIN () instead of LEAST.

GREATEST (X, Y ,...)
Returns the maximum value. Parameters are compared using the same rules as LEAST.
Mysql> select GREATEST (2, 0 );
-> 2
Mysql> select GREATEST (34.0, 3.0, 5.0, 767.0 );
-> 767.0
Mysql> select GREATEST ("B", "A", "C ");
-> "C"

In MySQL versions earlier than 3.22.5, you can use MAX () instead of GREATEST.
DEGREES (X)
Return parameter X, which is transformed from radian to angle.
Mysql> select DEGREES (PI ());
-> 180.000000
RADIANS (X)
Return parameter X, which is transformed from angle to radian.
Mysql> select RADIANS (90 );
-> 1.570796

TRUNCATE (X, D)
Returns X, truncates to a decimal number. If D is 0, no decimal point or decimal point is returned.
Mysql> select TRUNCATE (1.223, 1 );
-> 1.2
Mysql> select TRUNCATE (1.999, 1 );
-> 1.9
Mysql> select TRUNCATE (1.999, 0 );
-> 1