MySQL Common functions

first, mathematical functions

ABS (x) returns the absolute value of X
BIN (x) returns the binary of X (Oct returns octal, HEX returns hex)
CEILING (x) returns the smallest integer value greater than X
EXP (x) return value E (base of natural logarithm) x Times Square
Floor (x) returns the maximum integer value less than x
Greatest (x1,x2,..., xn) returns the largest value in the collection
LEAST (x1,x2,..., xn) returns the smallest value in the collection
LN (x) returns the natural logarithm of X
Log (x, y) returns the X-base logarithm
MOD (x, y) returns the modulo (remainder) of x/y
Pi () returns the value of Pi (pi)
RAND () returns a random value from 0 to 1, which enables the rand () random number generator to generate a specified value by providing a parameter (seed).
ROUND (x, y) returns the rounding of parameter x with a value of y decimal place
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 a number x truncated to Y decimal places

Second, aggregation function (commonly used in the GROUP BY clause in the Select query)

Avg (COL) returns the average 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 of the specified column
Group_concat (COL) Returns the result of a combination of column value connections belonging to a group

three, String function

ASCII (char) returns the ASCII code value of a character
Bit_length (str) returns the bit length of a string
CONCAT (S1,S2...,SN) to concatenate s1,s2...,sn into a string
Concat_ws (SEP,S1,S2...,SN) joins the S1,S2...,SN into a string and uses the Sep character interval
Insert (STR,X,Y,INSTR) starts the string str from the x position, and the Y-character substring is replaced with a string instr, returning the result
Find_in_set (str,list) parses a comma-delimited list of lists, and if STR is found, returns the position of STR in the list
LCASE (str) or lower (str) Returns the result of changing all characters in the string str to lowercase
Left (str,x) returns the leftmost X character in a string str
LENGTH (s) returns the number of characters in the string str
LTRIM (str) cuts the opening space from the string str
POSITION (SUBSTR,STR) returns the position of the substring substr the first occurrence in the string str
QUOTE (str) escapes single quotation marks in Str with a backslash
REPEAT (STR,SRCHSTR,RPLCSTR) Returns the result of a string str repeating x times
REVERSE (str) Returns the result of reversing the string str
Right (STR,X) returns the rightmost X character in a string str
RTRIM (str) returns a space at the trailing end of a string str
STRCMP (S1,S2) comparing strings S1 and S2
TRIM (str) removes all whitespace from string header and trailing
UCASE (str) or upper (str) returns the result of converting all characters in the string str 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) Date dates plus the result of the interval int (int must be formatted according to the keyword), such as: Selectdate_add (current_date,interval 6 MONTH); The
Date_format (DATE,FMT) formats the date value
date_sub (date,interval int keyword) According to the specified FMT format, returning the result of the 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 of the Week (1~7)
DayOfMonth (date) represented by date returns date is the day of the one month (1~31)
DayOfYear (date) Return date is one year　　The day of the Week (1~366)
Dayname (date) returns the weekday name of date, such as: SELECT Dayname (current_date);
　　From_unixtime (TS,FMT) Formats the UNIX timestamp TS
HOUR (time) from the specified FMT format to return the hour value (0~23)
MINUTE (times) to return time minutes (0~59)
　　Month value of month (date) returns date (1~12)
MONTHNAME (date) returns the month name of date, such as: SELECT MONTHNAME (current_date);
　　Now () returns the current date and time
QUARTER (date) Returns a date in the quarter of the year (1~4), such as Select QUARTER (current_date);
WEEK (date) Returns the date of the week (0~53)
Year (date) returns the year of date (1000~9999)

Five, encryption function

Aes_encrypt (Str,key) Returns the result of encrypting the string str with the key key using the Advanced Encryption Standard algorithm, the result of calling Aes_encrypt is a binary string, stored as a BLOB type
Aes_decrypt (Str,key) Returns the result of decrypting the string str with the key key using the Advanced Encryption Standard algorithm
DECODE (Str,key) uses key as key to decrypt encrypted string str
ENCRYPT (Str,salt) uses the Unixcrypt () function, with the keyword salt (a string that uniquely determines the password, 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 a BLOB type
MD5 () computes the MD5 checksum of the string str
PASSWORD (str) returns the encrypted version of the string str, the encryption process is irreversible, and the UNIX password encryption process uses a different algorithm.
SHA () computes the Secure Hash Algorithm (SHA) checksum of the string str

VI. Control Flow function

MySQL has 4 functions for conditional operation, which can implement the conditional logic of SQL, allowing the developer to transform some application business logic into the database background;

Case When[test1] then [RESULT1] ... ELSE [Default] END

Returns RESULTN if TESTN is true, otherwise returns the default
case [Test] when[val1) Then [result] ... ELSE [Default]end

Returns RESULTN if test and valn are equal, otherwise returns the default
IF (TEST,T,F)

Returns t if test is true, otherwise returns F
Ifnull (ARG1,ARG2)

If Arg1 is not empty, return arg1, otherwise return arg2
Nullif (ARG1,ARG2)

Returns ARG1 if ARG1=ARG2 returns null;

vii. Formatting Functions

Date_format (DATE,FMT) formatting date dates values according to the string FMT
Format (x, y) formats x as a comma-separated sequence of numbers, and Y is the number of decimal digits of the result
Inet_aton (IP) Returns the number representation of an IP address
Inet_ntoa (num) returns the IP address represented by the number
Time_format (TIME,FMT) format time value according to the string FMT
The simplest of these is the format () function, which formats large numeric values into a readable sequence of comma intervals.

viii. type conversion function

For data type conversions, MySQL provides the cast () function, which converts a value to the specified data type. Types are: binary,char,date,time,datetime,signed,unsigned

Nine, System Information function

Database () returns the current DB name
BENCHMARK (count,expr) repeatedly runs the expression expr count times
CONNECTION_ID () Returns the connection ID of the current customer
Found_rows () returns the total number of rows retrieved by the last select query
User () or system_user () returns the current login username
Version () returns the versions of the MySQL server

PS: Due to the limited ability of the author, such as Error please understand

MySQL Common functions