Mysql common function Summary (SHARE)

1. mathematical function ABS (x) returns the absolute value BIN (x) of x and returns the binary value of x (OCT returns octal, HEX returns hexadecimal) CEILING (x) returns the x power FLOOR (x) of the smallest integer value EXP (x) returned value e (base of the natural logarithm) greater than x, and returns the maximum integer GREATEST (x1, x2 ,..., xn) returns the maximum value of LEAST (x

1. mathematical function ABS (x) returns the absolute value BIN (x) of x and returns the binary value of x (OCT returns octal, HEX returns hexadecimal) CEILING (x) returns the x power FLOOR (x) of the smallest integer value EXP (x) returned value e (base of the natural logarithm) greater than x, and returns the maximum integer GREATEST (x1, x2 ,..., xn) returns the maximum value of LEAST (x

I. mathematical functions
ABS (x) returns the absolute value of x.
BIN (x) returns the binary value of x (OCT returns octal, HEX returns hexadecimal)
CEILING (x) returns the smallest integer greater than x.
EXP (x) returns the x power of e (the base of the natural logarithm ).
FLOOR (x) returns the largest integer less than x.
GREATEST (x1, x2,..., xn) returns the maximum value in the set.
LEAST (x1, x2,..., xn) returns the smallest value in the set.
LN (x) returns the natural logarithm of x.
LOG (x, y) returns the base y logarithm of x.
MOD (x, y) returns the modulus (remainder) of x/y)
PI () returns the pi value (circumference rate)
RAND () returns a random value between 0 and 1. You can provide a parameter (SEED) to generate a specified value for RAND () random number generator.
ROUND (x, y) returns the rounded y decimal value of parameter x.
SIGN (x) returns the value of the symbol representing the number x.
SQRT (x) returns the square root of a number.
TRUNCATE (x, y) returns the result of truncating x to y decimal places.

Ii. Aggregate functions (usually used in SELECT queries of group by clauses)
AVG (col) returns the average value of the specified Column
COUNT (col) returns the number of non-NULL values in the specified column.
MIN (col) returns the minimum value of the specified Column
MAX (col) returns the maximum value of the specified Column
SUM (col) returns the SUM of all values in the specified column.
GROUP_CONCAT (col) returns the result of a combination of column values.

Iii. String Functions
ASCII (char) returns the ASCII value of a character
BIT_LENGTH (str) returns the bit length of the string.
CONCAT (s1, s2. .., sn) concatenates s1, s2. .., and sn into a string.
CONCAT_WS (sep, s1, s2. .., sn) concatenates s1, s2. .., sn into a string, and uses the sep character interval.
INSERT (str, x, y, instr): Start from position x of string str. Replace substring with string instr with length y.
FIND_IN_SET (str, list) analyzes the list separated by commas (,). If str is found, the position of str in the list is returned.
LCASE (str) or LOWER (str) returns the result of changing all characters in the str string to lowercase.
LEFT (str, x) returns the leftmost x characters in the str string.
LENGTH (s) returns the number of characters in the str string.
LTRIM (str) removes the leading space from the str string.
POSITION (substr, str) returns the POSITION of the substring substr that appears for the first time in the str string.
QUOTE (str) escape single quotes in str with a backslash
REPEAT (str, srchstr, rplcstr) returns the string 'str' repeated x times.
REVERSE (str) returns the result of REVERSE string str
RIGHT (str, x) returns the rightmost x characters in the str string.
RTRIM (str) returns the space at the end of the str string.
STRCMP (s1, s2) compares strings s1 and s2
TRIM (str) removes all spaces at the beginning and end of the string.
UCASE (str) or UPPER (str) returns the result of converting all characters in the str string to uppercase.

Iv. Date and Time Functions
CURDATE () or CURRENT_DATE () returns the current date
CURTIME () or CURRENT_TIME () returns the current time
DATE_ADD (date, INTERVAL int keyword) returns the result of date plus the INTERVAL int (int must be formatted according to the keyword), such as: SELECTDATE_ADD (CURRENT_DATE, INTERVAL 6 MONTH );
DATE_FORMAT (date, fmt) format the date value according to the specified fmt Format
DATE_SUB (date, INTERVAL int keyword) returns the result of date plus the INTERVAL int (int must be formatted according to the keyword), such as: SELECTDATE_SUB (CURRENT_DATE, INTERVAL 6 MONTH );
DAYOFWEEK (date) returns the Day (1 ~ 7)
DAYOFMONTH (date) returns the Day (1 ~ 31)
DAYOFYEAR (date) returns the Day (1 ~ 366)
DAYNAME (date) returns the week name of date, for example, select dayname (CURRENT_DATE );
FROM_UNIXTIME (ts, fmt) format the UNIX timestamp ts Based on the specified fmt Format
HOUR (time) returns the HOUR value of time (0 ~ 23)
MINUTE (time) returns the MINUTE value of time (0 ~ 59)
MONTH (date) returns the MONTH value of date (1 ~ 12)
MONTHNAME (date) returns the month name of date, for example, select monthname (CURRENT_DATE );
NOW () returns the current date and time
QUARTER (date) returns the QUARTER (1 ~ 4), such as select quarter (CURRENT_DATE );
WEEK (date) returns the WEEK (0 ~ 53)
YEAR (date) returns the YEAR (1000 ~ 9999)
Some examples:

The Code is as follows:

Obtain the current system time: SELECT FROM_UNIXTIME (UNIX_TIMESTAMP ());
Select extract (YEAR_MONTH FROM CURRENT_DATE );
Select extract (DAY_SECOND FROM CURRENT_DATE );
Select extract (HOUR_MINUTE FROM CURRENT_DATE );

Returns the difference between two date values (number of months): SELECT PERIOD_DIFF (200302,199802 );

Age calculation in Mysql:
SELECT DATE_FORMAT (FROM_DAYS (TO_DAYS (NOW ()-TO_DAYS (birthday), '% y') + 0 AS age FROM employee;
In this way, if Brithday is the year, month, and day of the future, the calculation result is 0.
The following SQL statement calculates the absolute age of an employee, that is, when Birthday is a future date, a negative value is obtained.
SELECT DATE_FORMAT (NOW (), '% y')-DATE_FORMAT (birthday,' % y')-(DATE_FORMAT (NOW (), '00-% m-% D ')

V. encryption functions
AES_ENCRYPT (str, key) returns the encrypted result of using the key pair string str with the Advanced Encryption Standard algorithm. The result of calling AES_ENCRYPT is a binary string stored as BLOB.
AES_DECRYPT (str, key) returns the decryption result of the string str encrypted with the key using the Advanced Encryption Standard algorithm.
DECODE (str, key) uses the key as the key to decrypt the encrypted string str
ENCRYPT (str, salt) uses the UNIXcrypt () function and uses the keyword salt (a string that uniquely identifies a password, just like a key) to ENCRYPT the string str
ENCODE (str, key) uses key as the key to encrypt the string str. The result of calling ENCODE () is a binary string, which is stored as BLOB.
Calculate the MD5 checksum of the str string using MD5 ().
PASSWORD (str) returns the encrypted version of the string str, which is irreversible and uses different algorithms than the encryption process of UNIX passwords.
SHA () calculates the SHA checksum of the str string
Example:

The Code is as follows: