Summary of MySQL mathematical functions

Source: Internet
Author: User
Tags acos asin base 10 logarithm natural logarithm
1. ABS (x): returns the absolute values of x: mysqlselectABS (1), ABS (-1), ABS (0); + -------- + --------- + -------- + | ABS (1) | ABS (-1) | ABS (0) | + -------- + --------- + -------- + | 1 | 1 | 0 | + -------- + --------- + -------- + 2.PI (): returns the circumference rate mysqlselectPI (); + --

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 the circumference of mysql select PI (); + --

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 the circumference Rate


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

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


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.


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.


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 the maximum integer not greater than X. The return value is converted into 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 ~ 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;


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


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.

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.


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.

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.

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:


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:


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


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.


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

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.