A complete list of commonly used MySQL functions (classified and summarized)

1. 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 value greater than x.
• EXP(x) returns the value e (the base of the natural logarithm) raised to the power of x
• FLOOR(x) returns the largest integer value less than x.
• GREATEST(x1,x2,...,xn) returns the largest value in a set
• LEAST(x1,x2,...,xn) returns the smallest value in a set
• LN(x) returns the natural logarithm of x
• LOG(x,y) returns the logarithm of x to base y.
• MOD(x,y) returns the modulus (remainder) of x/y
• PI() returns the value of pi (pi)
• RAND() returns a random value between 0 and 1. You can provide a parameter (seed) to make the RAND() random number generator generate a specified value.
• ROUND(x,y) returns the value of parameter x rounded to y decimal places.
• SIGN(x) returns the value representing the sign of the number x
• SQRT(x) returns the square root of a number
• TRUNCATE(x,y) returns the result of truncating the number x to y decimal places.

2. Aggregate functions (commonly used in SELECT queries in 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 concatenating the values ​​of columns belonging to a group

String functions

• ASCII(char) returns the ASCII code value of a character
• BIT_LENGTH(str) Returns the bit length of the string
• CONCAT(s1,s2...,sn) concatenates s1,s2...,sn into a string
• CONCAT_WS(sep,s1,s2...,sn) concatenates s1,s2...,sn into a string and separates them with the sep character
• INSERT(str,x,y,instr) replaces the substring of string str starting from position x and y characters long with string instr and returns the result
• FIND_IN_SET(str,list) analyzes the comma-separated list list, and if str is found, returns the position of str in the list
• LCASE(str)或LOWER(str) returns the result of changing all characters in the string str to lowercase.
• LEFT(str,x) returns the leftmost x characters in the string str
• LENGTH(s) returns the number of characters in the string str
• LTRIM(str) removes leading spaces from string str
• POSITION(substr,str) returns the position of the first occurrence of the substring substr in the string str
• QUOTE(str) escapes the single quote in str with a backslash
• 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 trailing spaces of the string str
• STRCMP(s1,s2) compares strings s1 and s2
• TRIM(str) removes all spaces at the beginning and end of a string
• 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) returns the result of adding the date date to the interval time int (int must be formatted according to the keyword), such as: SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
• DATE_FORMAT(date,fmt) formats the date value according to the specified fmt format
• DATE_SUB(date,INTERVAL int keyword) returns the result of date plus 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 represented by date (1~7)
• DAYOFMONTH(date) returns the day of the month (1~31)
• DAYOFYEAR(date) returns the day of the year (1~366)
• DAYNAME(date) returns the day of the week of date, such as: SELECT DAYNAME(CURRENT_DATE);
• FROM_UNIXTIME(ts,fmt) formats the UNIX timestamp ts according to 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, such as: SELECT MONTHNAME(CURRENT_DATE);
• NOW() returns the current date and time
• QUARTER(date) returns the quarter (1~4) of date in a year, such as SELECT QUARTER(CURRENT_DATE);
• WEEK(date) returns the week of the year (0~53) of the date
• YEAR(date) returns 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 (in 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;

Thus, if Brithday is in the future, the 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 will be obtained.

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee

5. Encryption Function

• AES_ENCRYPT(str,key) returns the result of encrypting the string str using the Advanced Encryption Standard algorithm with the key key. The result of calling AES_ENCRYPT 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 with the key key
• DECODE(str,key) uses key as the key to decrypt the encrypted string str
• ENCRYPT(str,salt) uses the UNIXcrypt() function to encrypt the string str with the keyword salt (a string that can uniquely identify the password, like a key)
• 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 in BLOB type.
• MD5() Calculates 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 than 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');#Encrypt and decrypt 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 four functions for conditional operations. These functions can implement SQL conditional logic, allowing developers to convert some application business logic to the database backend.

MySQL control flow functions:

• CASE WHEN[test1] THEN [result1]...ELSE [default] END If testN is true, then return resultN, otherwise return default
• CASE [test] WHEN[val1] THEN [result]...ELSE [default]END If test and valN are equal, then resultN is returned, otherwise default is returned
• IF(test,t,f) If test is true, return t; otherwise return f
• IFNULL(arg1,arg2) If arg1 is not null, return arg1, otherwise return arg2
• NULLIF(arg1,arg2) returns NULL if arg1=arg2; otherwise returns arg1

The first of these functions is IFNULL() , which takes two arguments and performs a check on the first argument. If the first parameter is not NULL, the function will return the first parameter to the caller; if it is NULL, it will return the second parameter.

For example: SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,'false');

The NULLIF() function will test whether the two parameters provided are equal. If they are equal, it returns NULL. If they are not equal, it returns the first parameter.

For example: SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1);

Like the IF() function provided by many scripting languages, MySQL's IF() function can also establish 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 it is false, IF() will return the third parameter.

For example: SELECTIF(1<10,2,3),IF(56>100,'true','false');

IF() function is appropriate when there are only two possible outcomes. However, in the real world, we may find that we need multiple branches 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 complex and generally looks like this:

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, and if it is true, the result is returned. All WHEN-THEN blocks will end with the ELSE block. When END ends all the outer CASE blocks, if each of the previous blocks does not match, the default result specified by the ELSE block will be returned. If no ELSE block is specified and all WHEN-THEN comparisons are not true, MySQL returns NULL.

The CASE function has another syntax that 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

In this case, the result returned depends on whether the corresponding conditional 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. Formatting Function

• DATE_FORMAT(date,fmt) formats the date value according to the string fmt
• FORMAT(x,y) formats x as a comma-separated sequence of numbers, y is the number of decimal places in the result
• INET_ATON(ip) returns the digital representation of the IP address
• INET_NTOA(num) returns the IP address represented by the number
• TIME_FORMAT(time,fmt) formats the time value according to the string fmt

The simplest of these is the FORMAT() function, which formats large numbers 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

To perform data type conversion, MySQL provides the CAST() function, which can convert a value to a specified data type. Types: 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 name of the current database
• BENCHMARK(count,expr) repeats the expression expr count times
• CONNECTION_ID() returns the connection ID of the current client
• FOUND_ROWS() returns the total number of rows retrieved by the last SELECT query
• USER()或SYSTEM_USER() returns the current logged in user name
• 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.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links