MySQL common function bitsCN.com 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> % 20 SELECT % 20 CONCAT ('My, % 20's, % 20 'ql ');
-> % 20 'mysql' Mysql> % 20 SELECT % 20 CONCAT ('My ', % 20 NULL, % 20 'ql '); -> % 20 NULL
Mysql> % 20 SELECT % 20 CONCAT (14.3 );
-> % 20 '14. 3' GROUP_CONCAT function % 20 concatenates the obtained values with commas. % 20
[SQL]% 20 view % 20 plaincopy Select % 20group_concat (id) % 20 from % 20table_name; % 20, the result is (, 5) LEFT, RIGHT function % 20 left (str, n) or right (str, n) % 20 returns n characters to the leftmost/rightmost of the string. LENGTH function, CHAR_LENGTH function % 20 length (str) % 20char_length (str) % 20 length: % 20 is the LENGTH of the calculated field. one Chinese character is counted as two characters, A number or letter is counted as one character % 20char_length: a Chinese character, number, or letter is considered a character. SUBSTRING () % 20 SUBSTRING (str, pos, len) % 20 SUBSTRING (str % 20 FROM % 20pos % 20FOR % 20len) % 20 SUBSTRING (str, pos) % 20 SUBSTRING (str % 20 FROM % 20pos) % 20
[SQL]% 20 view % 20 plaincopy
Mysql> % 20 SELECT % 20 SUBSTRING ('quadratically ', 5);-> % 20' ratically'
Mysql> % 20 SELECT % 20 SUBSTRING ('foobarbarbar' % 20 FROM % 204);-> % 20' barbarbar' Mysql> % 20 SELECT % 20 SUBSTRING ('quadratically ', 5, 6);-> % 20 'ratica'
Mysql> % 20 SELECT % 20 SUBSTRING ('sakila ', % 20-3);-> % 20 'Ila' Mysql> % 20 SELECT % 20 SUBSTRING ('sakila ', % 20-5, % 203);-> % 20 'AK'
Mysql> % 20 SELECT % 20 SUBSTRING ('sakila' % 20 FROM % 20-4% 20FOR % 202);-> % 20 'ki' SUBSTRING_INDEX (str, delim, count) % 20 returns the substring between the separator % 20 delim % 20 In % 20 count % 20. % 20 If % 20 count % 20 is a positive number, return all characters from the last (count from left) separator to the left. % 20 If % 20 count % 20 is negative, return all characters from the last (count from the right) separator to the right. % 20 mysql> SELECT % 20SUBSTRING_INDEX ('www .baidu.com ', % 20 '. ', % 202); % 20-> % 20' www. baidu '% 20 mysql> % 20 SELECT % 20SUBSTRING_INDEX ('www .baidu.com', % 20 '. ', % 20-2); % 20-> % 20' baidu. com' Control flow function CASE % 20 value % 20 WHEN % 20 [compare-value] % 20 THEN % 20 result % 20 [WHEN % 20 [compare-value] % 20 THEN % 20 result % 20...] % 20 [ELSE % 20 result] % 20END % 20 CASE % 20 WHEN % 20 [condition] % 20 THEN % 20 result % 20 [WHEN % 20 [condition] % 20 THEN % 20 result % 20...] % 20 [ELSE % 20 result] % 20END % 20 in the return result of the first scheme, % 20 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 ELSE % 20 is not found, the return value is % 20 NULL. IF (expr1, expr2, expr3) function % 20 IF expr1 is True, expr2 is returned; otherwise, expr3 is returned. % 20expr1% 20 is calculated as an integer. that is to say, if you are verifying a floating point value or a string value, % 20 should be verified using a comparison operation. % 20
[SQL]% 20 view % 20 plaincopy Mysql> % 20 SELECT % 20IF (1> 2, 2, 3 );
-> % 203 Mysql> % 20 SELECT % 20IF (1 <2, 'Yes % 20', 'no '); -> % 20 'yes'
Mysql> % 20 SELECT % 20IF (STRCMP ('test', 'test1'), 'no', 'yes ');
-> % 20 'no' IFNULL (expr1, expr2) % 20 if expr1 % 20 is not % 20 NULL, then % 20 IFNULL () % 20 returns % 20expr1; % 20 otherwise, the returned value is % 20expr2. 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. % 20
[SQL]% 20 view % 20 plaincopy Mysql> % 20 SELECT % 20 IFNULL (1, 0 );
-> % 201 Mysql> % 20 SELECT % 20 IFNULL (NULL, 10 ); -> % 2010
Mysql> % 20 SELECT % 20 IFNULL (1/0, 10 );
-> % 2010 Mysql> % 20 SELECT % 20 IFNULL (1/0, 'yes '); -> % 20 'yes' NULLIF (expr1, expr2) % 20 if expr1 % 20 = % 20expr2% 20 is true, the return value is NULL; otherwise, the return value is % 20expr1. % 20 This is the same as CASE % 20 WHEN % 20expr1% 20 = % 20expr2% 20 THEN % 20 NULL % 20 ELSE % 20expr1% 20END. % 20
[SQL]% 20 view % 20 plaincopy
Mysql> % 20 SELECT % 20 NULLIF (1, 1 );
-> % 20 NULL Mysql> % 20 SELECT % 20 NULLIF (1, 2 ); -> % 201 Coalesce function, return the first non-null value in the parameter % 20
[SQL]% 20 view % 20 plaincopy
Select % 20 coalesce (a, B, c) % 20 from % 20table_name; % 20 if a is not null, select a; if a is null % 20, select B; if B is null, select c. If a, B, and c are both null, return null. GREATEST (value1, value2,...) % 20 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. % 20
[SQL]% 20 view % 20 plaincopy
Mysql> % 20 SELECT % 20 GREATEST (2, 0 );
-> % 202 Mysql> % 20 SELECT % 20 GREATEST (34.0, 3.0, 5.0, 767.0 ); -> % 20767.0
Mysql> % 20 SELECT % 20 GREATEST ('B', 'A', 'C ');
-> % 20 'C' time function CURDATE () % 20 returns the current date, only including the year, month, and day UNIX_TIMESTAMP (), % 20UNIX_TIMESTAMP (date) % 20 If no parameter is called, returns a Unix % 20 timestamp % 20 ('1970-01-1970 '% 20GMT % 20 in seconds) % 20 as an unsigned integer. If you use date % 20 to call UNIX_TIMESTAMP (), it returns the parameter value in the format of '2017-01-1970 '% 20GMT. Date % 20 can be a DATE % 20 string, a % 20DATETIME string, a % 20TIMESTAMP, or a number in the YYMMDD % 20 or YYYMMDD format of the local time. % 20
[SQL]% 20 view % 20 plaincopy Mysql> % 20 SELECT % 20UNIX_TIMESTAMP ();
-> % 20882226357 Mysql> % 20 SELECT % 20UNIX_TIMESTAMP ('2017-10-1997 '); -> % 20875996580 FROM_UNIXTIME () % 20 returns the date value of the unix timestamp. TO_DAYS (date) % 20 given a date, % 20 returns a day % 20 (from the year 0 days % 20 ). % 20
[SQL]% 20 view % 20 plaincopy
Mysql> 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.
BitsCN.com
