MySQL function learning notes 1: mathematical functions and mysql learning notes

MySQL function learning notes 1: mathematical functions and mysql learning notes

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 the parameter X. If the value of X is negative, zero, or positive, the return result is-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). 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 |
+ -------------- + -------------------- + ----------- + ------------------- + -------------------- + ------------------- +

Senior One, compulsory one, math study notes, and summary

Chapter 1 set and function concepts
1. concepts related to collections
1. Meaning of a set: some specified object sets become a set, and each object is called an element.
2. Three features of elements in a set:
1. Element certainty; 2. Interaction of elements; 3. Element disorder
Note: (1) for a given set, the elements in the set are definite. Any object or element that is not the given set.
(2) Any two elements in a given set are different objects. When the same object falls into a set, only one element is involved.
(3) The elements in the set are equal and there is no sequential order. Therefore, to determine whether the two sets are the same, you only need to compare whether their elements are the same. You do not need to check whether the order is the same.
(4) The three features of a set element make the set itself deterministic and integral.
3. Representation of the set :{... } For example, {basketball players in our school}, {Pacific, Atlantic, Indian Ocean, and Arctic Ocean}
1. Use A Latin letter to indicate the set: A = {basketball players in our school}, B = {1, 2, 4, 5}
2. The expression of the set: the column method and the description method.
Note: Common number sets and their recording methods:
A non-negative integer set (that is, a natural number set) is recorded as N.
Positive Integer Set N * or N + Integer Set Z rational number set Q real number set R
The concept of "belonging"
The elements of a set are usually represented by lowercase Latin letters. For example, if A is an element of a, A belongs to a of the set and is recorded as A, a, on the contrary, a does not belong to set.
Column partitioning: lists the elements in the set one by one, and then enclose them in braces.
Description Method: describes the common attributes of elements in a set and describes the set in braces. Use a definite condition to indicate whether some objects belong to this set.
① Language description: Example: {triangle not a right triangle}
② Mathematical formula subdescription method: for example, the solution set of inequality X-3> 2 is {x  R | X-3> 2} or {x | X-3> 2}
4. Classification of sets:
1. a finite set contains a finite element.
2. An infinite set contains a set of infinite elements.
3. Examples of empty sets without any elements: {x | x2 =-5}
2. Basic Relationship between Sets
1. "include" link-subset
Note: There are two possibilities: (1) A is part of B; (2) A and B are the same set.
Conversely, set A does not contain Set B, or set B does not contain Set A, which is recorded as a B or B.
2. "equal" relationship (5 ≥ 5, and 5 ≤ 5, then 5 = 5)
Instance: Set A = {x | x2-1 = 0} B = {-} "same element"
Conclusion: For two sets A and B, if any element of Set A is an element of Set B, and any element of Set B is an element of set, let's say that set A is equal to Set B, that is, A = B.
① Any set is a subset of itself. A branch
② True subset: If A contains B and A contains B, it means that set A is the true subset of Set B and is recorded as a B (or B)
③ If A between B and B between C, then A between C
④ If A between B and B between A, then A = B
3. A set without any elements is called an empty set, which is recorded
Rule: An empty set is a subset of any set, and an empty set is a real subset of any non-empty set.
Iii. Set Operations
1. Intersection definition: Generally, A set composed of all elements A and B is called the intersection of A and B.
It is written as A transaction B (read as "A to B"), that is, A transaction B = {x | x, A, and x, B }.
2. Definition of Union: Generally, A union of all elements of A or B is called A and B. As A member B (read as "A and B"), that is, A member B = {x | x, A, or x, B }.
3. The nature of intersection and Union: A between A = A, A between Phi = Phi, A between B ...... the remaining full text>

How to Create a UDF in mysql and return the maximum value of a table ID plus 1

Check the results and add them to the outside. Is that all right?