A concise summary of Mysql mathematical functions _mysql

Source: Internet
Author: User
Tags abs acos asin cos natural logarithm pow sin square root

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.