Summary of MySQL mathematical functions, mysql mathematical functions

Source: Internet
Author: User
Tags acos asin base 10 logarithm mathematical functions natural logarithm

Summary of MySQL mathematical functions, mysql mathematical functions

1. ABS (x): returns the absolute value of x.
Copy codeThe Code is as follows:
Mysql> select ABS (1), ABS (-1), ABS (0 );
+ -------- + --------- + -------- +
| ABS (1) | ABS (-1) | ABS (0) |
+ -------- + --------- + -------- +
| 1 | 1 | 0 |
+ -------- + --------- + -------- +

2. PI (): returns the circumference Rate
Copy codeThe Code is as follows:
Mysql> select PI ();
+ ---------- +
| PI () |
+ ---------- +
| 1, 3.141593 |
+ ---------- +

3. SQRT (x): returns the square root of x, required (x is not negative, return NULL)
Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
Mysql> select CEIL (-3.35), CEIL (3.35 );
+ ------------- + ------------ +
| CEIL (-3.35) | CEIL (3.35) |
+ ------------- + ------------ +
|-3 | 4 |
+ ------------- + ------------ +

6. CEILING (X): Same as CEIL (X)
Copy codeThe Code is 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 not greater than X. The return value is converted into a BIGINT.
Copy codeThe Code is 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 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;
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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.

Copy codeThe Code is as follows: mysql> select ROUND (-1.14), ROUND (-1.9), ROUND (1.14), and 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.

Copy codeThe Code is 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 |
+ ------------------ + -------------------- +

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.

Copy codeThe Code is 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 |
+ ----------- + ---------- + --------- + ----------- + ---------- +

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.
Copy codeThe Code is as follows: mysql> select pow (2, 2), pow (2,-2), pow (-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:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is 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 | 1.0986122886681098 | NULL | 2 |
+ --------- + -------- + -------------------- + ------------- + ---------- + ------------ +

14. RADIANS (X) and DEGREES (X): Angle and RADIANS conversion functions
Copy codeThe Code is as follows:
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.
Copy codeThe Code is 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 |
+ -------------- + -------------------- + ----------- + ------------------- + -------------------- + ------------------- +


What are the statistical functions of mysql?

Count () counts the number of records, such as select count (*) from stu;
Sum () calculates the sum of the record fields, such as select sum (salary) from emp;
Avg () calculates the average value of record fields, for example, select avg (salary) from emp;
Max () queries the maximum value in a field, for example, select max (age) from stu;
Min () queries the minimum value of a field, for example, select min (age) from stu;

I wrote a mysql function, but an error occurred while running. Which one can help me to see if it is urgent?

Because you do not have your table structure, I have created another table.
Create table test_tree (test_id int not null, pid INT, test_val VARCHAR (10), primary key (test_id); insert into test_tree VALUES (1, NULL ,'. NET '); insert into test_tree VALUES (2, 1, 'c #'); insert into test_tree VALUES (3, 1, 'J #'); insert into test_tree VALUES (4, 1, 'asp. NET '); insert into test_tree VALUES (5, 1, 'vb. NET '); insert into test_tree VALUES (6, NULL, 'j2ee'); insert into test_tree VALUES (7, 6, 'ejb '); insert into test_tree VALUES (8, 6, 'servlet '); insert into test_tree VALUES (9, 6, 'jsp'); insert into test_tree VALUES (10, NULL, 'database '); insert into test_tree VALUES (11, 10, 'db2 '); insert into test_tree VALUES (12, 10, 'mysql'); insert into test_tree VALUES (13, 10, 'oracle '); insert into test_tree VALUES (14, 10,' SQL Server'); insert into test_tree VALUES (15, 13, 'pl/SQL '); insert into test_tree VALUES (16, 15, 'function'); insert into test_tree VALUES (17, 15, 'Procedure '); insert into test_tree VALUES (18, 15, 'package'); insert into test_tree VALUES (19, 15, 'cursor '); insert into test_tree VALUES (20, 14,'t-SQL ');

Modified Function Code
DELIMITER // create function groupLst (rootid varchar (25) returns varchar (1000) begin declare idStr VARCHAR (1000); declare chID VARCHAR (1000); set max_sp_recursion_depth = 225; set idSt ...... remaining full text>
 

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.