MySQL common functions

The string function CONCAT (str1, str2,...) returns the string generated by the connection parameter. If any parameter is NULL, the return value is NULL.[SQL]View plaincopy
Mysql> select concat ('My, s', 'ql ');-> 'mysql'

Mysql> select concat ('My, NULL, 'ql ');
-> NULLmysql> select concat (14.3);-> '14. 3'
The GROUP_CONCAT function Concatenates the obtained values with commas.[SQL]View plaincopy
Select group_concat (id) from table_name; the result is (1, 2, 3, 4, 5)
LEFT, RIGHT functions left (str, n) or right (str, n) return n characters at the leftmost/rightmost of the string.
LENGTH function, CHAR_LENGTH function length (str) char_length (str) length: Calculate the LENGTH of a field. A Chinese character is counted as two characters. A number or letter is counted as one character char_length: A Chinese character, number, or letter is a single character.
SUBSTRING () SUBSTRING (str, pos, len) SUBSTRING (str FROM pos FOR len) SUBSTRING (str, pos) SUBSTRING (str FROM pos)[SQL]View plaincopymysql> select substring ('quadratically ', 5);-> 'ratically 'mysql> select substring ('foobarbar' FROM 4);-> 'barbarbar'
Mysql> select substring ('quadratically ', 5, 6);-> 'ratica' mysql> select substring ('sakila',-3);-> 'ila'
Mysql> select substring ('sakila ',-5, 3);-> 'aki' mysql> select substring ('sakila' FROM-4 FOR 2);-> 'ki'
SUBSTRING_INDEX (str, delim, count) returns the substring before the delimiter delim that appears at the count in the str string. If count is a positive number, return all characters from the last (count from the left) separator to the left. If count is a negative number, return all characters from the last (count from the right) separator to the right. Mysql> SELECT SUBSTRING_INDEX ('www .baidu.com ','. ', 2);->' www. baidu 'mysql> SELECT SUBSTRING_INDEX ('www .baidu.com ','. ',-2);->' baidu. com'
Control Flow Function CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result...] [ELSE result] end case when [condition] THEN result [WHEN [condition] THEN result...] [ELSE result] In the return result of the first scheme, value = compare-value. The returned results of the second solution are the real results of the first case. If no matching result value exists, the result after ELSE is returned. If no ELSE part exists, the return value is NULL.
IF (expr1, expr2, expr3) IF expr1 is True, expr2 is returned; otherwise, expr3 is returned. Expr1 is calculated as an integer. That is to say, if you are verifying a floating point or string value, you should use a comparison operation for testing.[SQL]View plaincopy
Mysql> select if (1> 2, 2, 3);-> 3

Mysql> select if (1 <2, 'yes', 'no ');
-> 'Yes'
Mysql> select if (STRCMP ('test', 'test1'), 'No', 'yes');-> 'no'
IFNULL (expr1, expr2) If expr1 is not NULL, the returned value of IFNULL () is expr1; otherwise, the returned value is expr2. The returned value of IFNULL () is a number or string, depending on the context in which it is used. This function is generally used to replace NULL values. Because NULL values cannot be involved in numerical operations, the following statement can replace NULL values with 0.[SQL]View plaincopy
Mysql> select ifnull (1, 0);-> 1

Mysql> select ifnull (NULL, 10 );
-> 10
Mysql> select ifnull (1/0, 10);-> 10

Mysql> select ifnull (1/0, 'yes ');
-> 'Yes'
NULLIF (expr1, expr2) If expr1 = expr2 is true, the return value is NULL; otherwise, the return value is expr1. This is the same as case when expr1 = expr2 then null else expr1 END.[SQL]View plaincopymysql> select nullif (1, 1);-> NULL

Mysql> select nullif (1, 2 );
-> 1
Coalesce function, returns the first non-null value in the parameter.[SQL]View plaincopyselect coalesce (a, B, c) from table_name; if a is not null, select a; if a is null, select B; if B is null, select c. If a, B, and c are both null, null is returned.
GREATEST (value1, value2,...) When there are two or more parameters, the return value is the maximum (maximum) parameter. The comparison parameters are based on the same rule as LEAST.[SQL]View plaincopymysql> select greatest (2, 0);-> 2

Mysql> select greatest (34.0, 3.0, 5.0, 767.0 );
-> 767.0
Mysql> select greatest ('B', 'A', 'C');-> 'C' time function CURDATE () returns the current date, which only contains the year, month, and day.
UNIX_TIMESTAMP (), UNIX_TIMESTAMP (date) If no parameter is called, a Unix timestamp ('2017-01-01 00:00:00 'seconds after GMT) is returned as an unsigned integer. If you use date to call UNIX_TIMESTAMP (), it will return the parameter value in the form of the number of seconds after '2017-01-01 00:00:00 'GMT. Date can be a DATE string, a datetime string, a TIMESTAMP, or a number in the YYMMDD or YYYMMDD format of the local time.[SQL]View plaincopy
Mysql> SELECT UNIX_TIMESTAMP ();-& gt; 882226357

Mysql> SELECT UNIX_TIMESTAMP ('2017-10-04 22:23:00 ');
-> 875996580
FROM_UNIXTIME () returns the date value of the unix timestamp.
TO_DAYS (date) specifies a date, and returns a number of days (days starting from 0 in the year ).[SQL]View plaincopymysql> SELECT TO_DAYS (950501);-> 728779

Mysql> SELECT TO_DAYS ('2017-10-07 ');
-> 729669
The DATEDIFF function datediff (date1, date2) is used to calculate the number of days between two dates.
The EXTRACT () function is used to return a separate part of a date or time, such as year, month, day, hour, or minute.

ROUND (x) returns the integer nearest to x, that is, returns x rounded to ROUND (x, y) and returns x rounded to y after the decimal point, TRUNCATE (x, y) returns the value of x which is retained to the y digit after the decimal point during rounding.
The SIGN Function SIGN (x) returns the SIGN of x. The positive number is 1, and the negative number is-1, and 0 is 0.
CEIL (x) and CEILING (x) return the minimum integer FLOOR (x) greater than or equal to x and the maximum integer less than or equal to x.

MD5 (str), returns the MD5 value of str. It is often used to encrypt data in applications. Select MD5 ('20140901 ')
INET_ATON (IP address), returns the network byte order of the IP address to indicate INET_NTOA (num), and returns the IP address of the network byte code.