MySQL Common functions

1. Mathematical Functions

ABS (X) absolute value
Ceil (X) rounding up
Floor (X) rounding down
Random number of RAND (X) 0-1
ROUND (x) the nearest integer from X
ROUND (x, y) holds the decimal number of the X-bit and uses rounding when truncated
POW (x, y) quadratic square
SQRT (x, y) Open the square root,
2. Character functions

Char_length (X) Take the character length "I am L" =>3
Length (X) Take byte lengths "I am L" =>6
CONCAT (A1,A2) connection two characters
UPPER (x) LOWER (x) uppercase, lowercase
Left (X,n) right (X,n) starts with N characters
Replace (S,S1,S2) converts the S1 in the string S to S2
MID (S,N,LN) intercepts the substring of LN length from N in the string S
REVERSE (X) string s reverse over
3. Date function

Common functions

Curdate () returns the current date without time
Curtime () returns the current time without a date
Now () returns the current time + date
Functions for conversion

Returns the month in a given date
The day in month (d) d is a few months 1-12
MONTHNAME (d) d in the name of the month of the day in English
Returns the week of the given date
Dayname (d) d the day of the week is in English name
DAYOFWEEK (d) d the day of the week is 1 Sunday 2 week One
WEEKDAY (d) d the day of the week is 0 weeks 11 Tuesday
WEEK (d) d in this day is the first few weeks in the whole year
Returns details from a given date
Dayoryear (d) d is that year
Dayofmont (d) d is that one months
QUARTER (d) d is that quarter
HOUR (d) value in D
Minute value in MINUTE (d) d
Second value in SECOND (d) d
Calculation of Time
Time_to_sec (t) converts a given time t to seconds
Sec_to_time (T) converts the time in seconds to the last minute format
DATEDIFF (D1,D2) calculates the number of days between D1 and D2
Adddate (D,n) calculates the start date plus a time period after the date N the default is days also can interval 5 minute
Subdate (d,n) date minus N days
Subdate (D,interval expr type) date minus one time period after the date value can be INTERVAL minute
Data_format (d,f) Displays the date '%y-%m-%d%r ' as required by expression F
Time_format (d,f) displays time '%r ' as required by expression F
4. Conditional functions

if (expr,v1,v2) if the expression exper form is 3>2 satisfies the condition then returns the result v1 otherwise v2
if (1>0, correct, error) returns V2 If the expression is null otherwise v1
Ifnull (V1,V2) returns v2 if the value of V1 is empty, otherwise returns v2
Case
Case
When E1
Then V1
When E2
Then E2
...
ELSE VN
END
5. Cryptographic functions

PASSWORD (str) The function can encrypt the string STR, in general, PASSWORD (str) is used to encrypt the user's password.
The MD5 MD5 (str) function hashes the string str and can be used for some common data encryption that does not require decryption.
ENCODE (STR,PSWD_STR) and Decode (CRYPT,PSWD_STR) ENCODE functions can use encrypted password Pswd_str to encrypt the string STR, the result of which is a binary number, which needs to be saved with a field of blob type. This function is a pair with decode and requires the same password to decrypt it.

MySQL Common functions