Summary of MYSQL database function collection

1. Mathematical function
ABS(x) Returns the absolute value of x

BIN(x) Returns the binary number of x (OCT returns octal, HEX returns hexadecimal)
CEILING(x) Returns the smallest integer value greater than x
EXP(x) Return value e (natural (the base of the number) raised to the power of 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 Causes the RAND() random number generator to generate a specified value.
ROUND(x,y) Returns the rounded value of parameter x with y decimal places
SIGN(x) Returns the value representing the sign of number x
SQRT(x) Returns the square root of a number
TRUNCATE(x,y) Returns the result of number x truncated to y decimal places


2. Aggregation function (commonly used in SELECT queries of GROUP BY clause)

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 ValueSUM(col) Returns the sum of all values ​​in the specified column
GROUP_CONCAT(col) Returns the result composed of the concatenation of column values ​​belonging to a group


3. String Function

ASCII(char) returns the ASCII code value of the character
BIT_LENGTH(str) returns the bit length of the string
CONCAT(s1,s2...,sn) converts s1,s2.. .,sn is concatenated into a stringCONCAT_WS(sep,s1,s2...,sn)Concatenates s1,s2...,sn into a string and separated by sep charactersINSERT(str,x, y,instr) Replace the y-character-long substring of string str starting at the 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 in the string str characters
LENGTH(s) returns the number of characters in the string str
LTRIM(str) cuts off the leading spaces from the string str
POSITION(substr,str) returns the substring substr in the string The first occurrence of str in str
QUOTE(str) Use backslash to escape the single quote in str
REPEAT(str, srchstr, rplcstr) Returns the result of string str repeated x times
REVERSE (str) Returns the result of reversing the string str
RIGHT(str,x) Returns the rightmost x characters in the string str
RTRIM(str) Returns the space at the end of the string str
STRCMP(s1 ,s2) Compare strings s1 and s2
TRIM(str)Remove all spaces at the beginning and end of the string
UCASE(str) or UPPER(str) Return all characters in the string str after converting them to uppercase Result


4. 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 interval time int (int must be formatted according to the keyword), such as: SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt) According to Format date value in the specified fmt format DATE_SUB(date,INTERVAL int keyword) returns the result of date plus interval time 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 represented by date (1~7)
DAYOFMONTH(date) Returns the day of the month represented by date (1~31)
DAYOFYEAR(date) Returns the day of the year (1~366) for date fmt format, format UNIX timestamp ts
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, such as: SELECT MONTHNAME(CURRENT_DATE);
NOW() Returns the current date and time
QUARTER(date ) Returns the date in the quarter of the year (1~4), such as SELECT QUARTER(CURRENT_DATE);
WEEK(date) Returns the date as the week of the year (0~53)
YEAR(date ) Return the year of date (1000~9999)
Some examples:
Get 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 (number of months) between two date values: SELECT PERIOD_DIFF(200302,199802);
Calculate age 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 in the future, the calculation result is 0.
The following SQL statement calculates the absolute age of an employee, that is, when Birthday is a date in the future, it will get a negative value.
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d')
5. Encryption function
AES_ENCRYPT(str,key) Returns the result of encrypting the string str using the Advanced Encryption Standard algorithm using the key key, and calls AES_ENCRYPT The result is a binary string, stored in BLOB type
AES_DECRYPT(str,key) Returns the result of decrypting the string str using the Advanced Encryption Standard algorithm using the key key
DECODE(str,key) Using key Decrypt the encrypted string str
ENCRYPT(str,salt) as a key. Use the UNIXcrypt() function to encrypt the string str
ENCODE with the keyword salt (a string that can uniquely determine the password, just like a key) (str,key) Use key as the key to encrypt the string str. The result of calling ENCODE() is a binary string, which is stored in the BLOB type.
MD5() Calculate the MD5 checksum of the string str
PASSWORD(str) Returns the encrypted version of the string str. This encryption process is irreversible and uses a different algorithm from the UNIX password encryption process.
SHA() Calculates the Secure Hash Algorithm (SHA) checksum of the string str
Example:
SELECT ENCRYPT('root','salt');
SELECT ENCODE('xufeng' ,'key');
SELECT DECODE(ENCODE('xufeng','key'),'key');#Encryption and decryption together
SELECT AES_ENCRYPT('root','key');
SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');
SELECT MD5('123456');
SELECT SHA('123456');

6. Control flow functions
MySQL has 4 functions used to perform conditional operations. These functions can implement SQL conditional logic and allow developers to convert some application business logic to the database background.
MySQL control flow function:
CASE WHEN[test1] THEN [result1]...ELSE [default] END If testN is true, return resultN, otherwise return default
CASE [test] WHEN[val1 ] THEN [result]...ELSE [default]END If test and valN are equal, return resultN, otherwise return default
IF(test,t,f) If test is true, return t; otherwise return f
IFNULL(arg1,arg2) If arg1 is not empty, return arg1, otherwise return arg2
NULLIF(arg1,arg2) If arg1=arg2, return NULL; otherwise return arg1
The first of these functions is IFNULL(), It has two parameters, and the first parameter is judged. If the first parameter is not NULL, the function will return the first parameter to the caller; if it is NULL, the second parameter will be returned.
For example: SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,'false');
The NULLIF() function will check whether the two parameters provided are equal. If If equal, NULL is returned. If not, the first parameter is returned.
Such as: SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1);
Same as the IF() function provided by many scripting languages, MySQL's IF() function can also create a simple conditional test. This function has three parameters. The first is the expression to be judged. If the expression is true, IF() will return the second parameter. If If false, IF() will return the third parameter.
For example: SELECTIF(1<10,2,3),IF(56>100,'true','false');
The IF() function is suitable to be used when there are only two possible results. However, in the real world, we may find that multiple branches are needed in a conditional test. In this case, MySQL provides the CASE function, which is the same as the switch-case conditional routine in PHP and Perl languages.
The format of the CASE function is somewhat complicated, usually as follows:
CASE [expression to be evaluated]
WHEN [val 1] THEN [result 1]
WHEN [val 2] THEN [result 2 ]
WHEN [val 3] THEN [result 3]
......
WHEN [val n] THEN [result n]
ELSE [default result]
END
Here, the first parameter is the value or expression to be judged, followed by a series of WHEN-THEN blocks. The first parameter of each block specifies the value to be compared. If it is true, the result is returned. all The WHEN-THEN block will end with an ELSE block. When END ends all outer CASE blocks, if each of the previous blocks does not match, the default result specified by the ELSE block will be returned. if not With the ELSE block specified, and all WHEN-THEN comparisons are false, MySQL will return NULL.
The CASE function has another syntax, which is sometimes very convenient to use, as follows:
CASE
WHEN [conditional test 1] THEN [result 1]
WHEN [conditional test 2] THEN [result 2]
ELSE [default result]
END
Under this condition, the result returned depends on whether the corresponding condition test is true.
Example:
mysql>SELECT CASE 'green'
WHEN 'red' THEN 'stop'
WHEN 'green' THEN 'go' END;
SELECT CASE 9 WHEN 1 THEN 'a' WHEN 2 THEN 'b' ELSE 'N/A' END;
SELECT CASE WHEN (2+2)=4 THEN 'OK' WHEN(2+2)<>4 THEN 'not OK' END ASSTATUS;
SELECT Name,IF((IsActive = 1),'Activated ','not activated') AS RESULT FROMUserLoginInfo;
SELECT fname,lname,(math+sci+lit) AS total,
CASE WHEN (math+sci+lit) < 50 THEN 'D'
WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN 'C'
WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN 'B'
ELSE 'A' END
AS grade FROM marks;
SELECT IF(ENCRYPT('sue','ts')=upass,'allow','deny') AS LoginResultFROM users WHERE uname = 'sue';#A login verification

7. Format function
DATE_FORMAT(date,fmt) Format the date value according to the string fmt
FORMAT(x,y) Format x into a sequence of numbers separated by commas, y is the number of decimal places in the result
INET_ATON(ip) Returns the numeric representation of the IP address
INET_NTOA(num) Returns the IP address represented by the number
TIME_FORMAT(time,fmt) Format the time according to the string fmt time value
The simplest of them is the FORMAT() function, which can format large numerical values ​​into an easy-to-read sequence separated by commas.
Example:
SELECT FORMAT(34234.34323432,3);
SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');
SELECT DATE_FORMAT(NOW() ,'%Y-%m-%d');
SELECT DATE_FORMAT(19990330,'%Y-%m-%d');
SELECT DATE_FORMAT(NOW(),'%h:%i % p');
SELECT INET_ATON('10.122.89.47');
SELECT INET_NTOA(175790383);

8. Type conversion function
In order to perform data type Conversion, MySQL provides the CAST() function, which can convert a value into a specified data type. Types are: BINARY, CHAR, DATE, TIME, DATETIME, SIGNED, UNSIGNED
Example:
SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;
SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY);

9. System information function
DATABASE() Returns the current database name
BENCHMARK(count , expr) Repeat the expression expr count times
CONNECTION_ID() Return the connection ID of the current customer
FOUND_ROWS() Return the total number of rows retrieved by the last SELECT query
USER() or SYSTEM_USER() Return Current login username
VERSION() Returns the version of the MySQL server
Example:
SELECT DATABASE(),VERSION(),USER();
SELECTBENCHMARK(9999999,LOG(RAND()*PI ()));#In this example, MySQL calculates the LOG(RAND()*PI()) expression 9999999 times

The above is the detailed content of Summary of MYSQL database function collection. For more information, please follow other related articles on the PHP Chinese website!