A brief summary of MySQL math functions
- 怪我咯Original
- 2017-04-01 10:29:051471browse
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(): Returns pi
mysql> select PI(); +----------+ | PI() | +----------+ | 3.141593 | +----------+
3. SQRT(x): Returns the square root of x, required (x is a non-negative number, returns NULL)
mysql> select SQRT(49), SQRT(0), SQRT(-49); +----------+---------+-----------+ | SQRT(49) | SQRT(0) | SQRT(-49) | +----------+---------+-----------+ | 7 | 0 | NULL | +----------+---------+-----------+
4. MOD(x,y): Find remainderFunction, returns the remainder after x is divided by y; it also works for data values with decimal parts, it returns The exact remainder after division.
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 that is not less than X, and the return value is converted into 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 no greater than X The maximum integer value, 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, ranging from 0 to 1, X is an integer, which is called the seed value and is used to generate a repeating sequence. That is, when the X values are the same, the random numbers generated are also the same;
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 generates different random numbers between 0 and 1 each time
mysql> SELECT RAND(), RAND(), RAND(); +--------------------+--------------------+---------------------+ | RAND() | RAND() | RAND() | +--------------------+--------------------+---------------------+ | 0.6931893636409094 | 0.5147262984092592 | 0.49406343185721285 | +--------------------+--------------------+---------------------+
9. ROUND(X) and ROUND(X,Y): Rounding function, the X value is rounded according to Y, Y can be omitted, the default value is 0; if Y is not 0, Then keep designated Y digits after the decimal point.
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): Functionally similar to ROUND(X,Y), but No rounding is performed, only truncation is performed.
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): Returns the sign of parameter X. When the value of X is negative, zero or positive, the return result is -1, 0 or 1.
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), used to return the result value of X raised to the Y power
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 of e raised to the
#
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): logarithmic operation function (X must be a positive number), LOG(X)-returns the natural logarithm of Logarithm of base e) LOG10(X) - Returns the base 10 logarithm of x:
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): angles Conversion function with radians
mysql> select RADIANS(90), RADIANS(180), DEGREES(PI()), DEGREES(PI()/2); +--------------------+-------------------+---------------+-----------------+ | RADIANS(90) | RADIANS(180) | DEGREES(PI()) | 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): Arcsine function where X must be between -1 and 1 Between
COS(X): Cosine function, where X is the radian value
ACOS(X): Inverse cosine function where X): tangent function, where , the functions COT and TAN are inverse functions of each other
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 | +--------------+--------------------+-----------+-------------------+--------------------+--------------------+-------------------+
The above is the detailed content of A brief summary of MySQL math functions. For more information, please follow other related articles on the PHP Chinese website!