【MySQL 09】Commonly used functions

1. Mathematical function

• ABS(x) returns the absolute value of x

• BIN(x) returns the binary (OCT) of x Returns octal, HEX returns hexadecimal)

• CEILING(x) Returns the smallest integer value greater than x

• EXP(x) Returns Value e (the base of natural logarithms) raised to the power x

• FLOOR(x) Returns the largest integer value less than x

• ##GREATEST( x1,x2,…,xn) Returns the largest 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 x /Module of y (remainder) PI() returns the value of pi (pi)

• RAND() returns a random value between 0 and 1, which can be used by providing a parameter (seed) RAND() random number generator generates a specified value.

• ROUND(x,y) returns the rounded value of parameter x with y decimal places

• SIGN(x) returns the representative number x The value of the sign

• SQRT(x) Returns the square root of a number

• TRUNCATE(x,y) Returns the number x truncated to y Result with decimal places

2. Aggregation function

• AVG(col) returns the average value of 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 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) concatenates s1,s2…,sn into a string

• CONCAT_WS(sep,s1,s2…,sn) concatenates s1,s2…, sn is concatenated into a string, and separated by sep characters

• INSERT(str,x,y,instr) Replace the string str with a substring that is y characters long starting from the x-th position For the string instr, return the result

• FIND_IN_SET(str,list) analyzes the comma-separated list list. If str is found, return 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 string str The leftmost x characters in

• LENGTH(s) returns the number of characters in string str

• LTRIM(str) from string Cut off the leading spaces in str

• POSITION(substr,str) Returns the position where the substring substr first appears in the string str

• QUOTE(str) Use backslash to escape single quotes 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) compares the strings s1 and s2

• TRIM(str) removes all spaces at the beginning and end of the string

• UCASE(str) or UPPER(str) returns all characters in the string str The result after capitalization

4, date and time function

##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 date plus interval time int ( int must be formatted according to keywords), such as: SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt) Format date value according to 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 (1~7) represented by date
DAYOFMONTH(date) Returns the day of the month (1~31) for date.
DAYOFYEAR(date) Returns the day of the year for date (1~366)
DAYNAME(date) Returns the day of the week name of date, such as: SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) Format according to the specified fmt format UNIX timestamp ts
HOUR(time) returns the hour value of time (0~23)
MINUTE(time) returns the minute of time Value (0~59)
MONTH(date) Returns the month value of date (1~12)
MONTHNAME(date) Returns date Month name, such as: SELECT

• ##MONTHNAME(CURRENT_DATE);

• ##NOW() returns the current date and time

• QUARTER(date) Returns the quarter (1~4) of date in the year, such as SELECT QUARTER(CURRENT_DATE);

• WEEK(date) Returns the date date is the week of the year (0~53)

• YEAR(date) Returns the year of date (1000~9999) Some examples: Get the current system time:

• SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());

• SELEC TEXTRACT(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, a negative value will be obtained.

• SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d ')

5. Encryption function

• AES_ENCRYPT(str,key): Returns the key key to the string str using the Advanced Encryption Standard The result of algorithm encryption, the result of calling AES_ENCRYPT is a binary string, stored in BLOB type

• AES_DECRYPT(str,key) returns the key key to use advanced encryption on the string str The result after standard algorithm decryption

• DECODE(str,key): Use key as the key to decrypt the encrypted string str

• ENCRYPT(str , salt): Use the UNIXcrypt() function to encrypt the string str

• ENCODE(str,key) with the keyword salt (a string that can uniquely determine the password, just like a 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 of the string str Checksum

• ##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 function

MySQL has 4 functions used to perform conditional operations. These functions can implement SQL Conditional logic allows developers to convert some application business logic to the database backend.

MySQL control flow function:
CASE WHEN[test1] THEN [result1]…ELSE [default] END
• If testN is If 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(), which has two parameters and evaluates the first parameter. 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 the two provided Whether the parameters are equal. If they are equal, NULL is returned. If they are not equal, the first parameter is returned.

• 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 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 it is false, IF() will return the third parameter.

• For example: SELECTIF(1100,'true','false');

• 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 to be judged or expression, followed by a series of WHEN-THEN blocks, the first parameter of each block specifies the value to be compared, and if true, the result is returned. All WHEN-THEN blocks will end with an ELSE block. When END ends all outer CASE blocks, the default result specified by the ELSE block will be returned if each of the preceding blocks does not match. If no ELSE block is 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),'has Activated','Inactivated') AS RESULT FROMUserLoginInfo; SELECT fname,lname,(math+sci+lit) AS total, CASE WHEN (math+sci+lit)

7. Formatting function

• DATE_FORMAT(date,fmt) Format the date value according to the string fmt

• FORMAT(x,y) Format x into a comma-separated sequence of numbers, y is The number of decimal places in the result

• INET_ATON(ip) Returns the numerical representation of the IP address

• INET_NTOA(num) Returns the IP address represented by the number

• TIME_FORMAT(time,fmt) Format the time value according to the string fmt

  The simplest one is the FORMAT() function, which can format large values ​​into A comma-separated human-readable sequence.
  

• 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 convert data types, MySQL provides the CAST() function, which can convert Converts a value to the 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) expr runs count times repeatedly
CONNECTION_ID() Returns the connection ID of the current customer
FOUND_ROWS() Returns the total rows retrieved by the last SELECT query Number
USER() or SYSTEM_USER() returns the current login username
VERSION() returns the version of the MySQL server
Example:
• SELECT DATABASE(),VERSION(),USER();

  SELECT BENCHMARK(9999999,LOG(RAND()*PI()));

  #The In the example, MySQL calculates the LOG(RAND()*PI()) expression 9999999 times.

• 
  The above is the content of [MySQL 09] commonly used functions. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!