Summary of MySQL mathematical functions, mysql mathematical functions

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>