Common functions in Mysql: common functions in Mysql:

Common functions in Mysql: common functions in Mysql:
Common functions in Mysql: 1. string functions: (1 ). concat (): // concat ('M', 'y', "SQL", '5. 5 '); = MySQL5.5 // when the input parameter has a value of NULL, the returned result value is NULLconcat_ws (); // Insert the specified separator into the string: concat_ws (": ", '20140901', '09'); =, but if the separator is NULL, the result is NULL, the parameter is NULL, and the result is not affected (2 ). function for comparing the string size: select strcmp (string 1, string 2); returns 1 if the value is greater than 1, returns-1 if the value is smaller than 0 (3 ). function used to obtain the length of a string: length (); // calculates the space. It refers to the length of the input parameter (in bytes). A Chinese character is two bytes. char_length (); // number of characters (character length) of the input parameter (4 ). uppercase/lowercase Conversion Function: Convert UPPER (); or UCASE () to uppercase LOWER (); (5 ). query string: select FIND_IN_SET (query string, target string set); // return the function of string position. The target string is a comma-separated string set, subscript select FIELD starting from 1 (search string, target string 1, Target string 2 ,...........); // return the string location function LOCATE (str1, str); // return the start POSITION (str1 IN str) of the string str1 ); // returns the start position of str1 in str (str, str1); // returns the start position of str1 in str (ELT (n, str1, str2 ......, strn); // returns the nth string MAKE_SET (num, str1, str2 ,... strn); // first convert the value num to binary, and then extract the value from the str1, str2 ,...., example of selecting the corresponding string in strn: select BIN (5) as binary number, MAKE_SET (5, 'mysql', 'oracle ',' SQL Server', 'postgresql ') as selected string; // The result is MYSQL, SQL SERVER (6 ). string truncation function: LEFT (str, num); // extract from the LEFT, string is str, length is numRIGHT (str, num); // extract from the right, string is str, the length is numSUBSTRING (str, num, len); // the string is str, starting from the num position, and the length is len, including the num position MID (str, num, len ); // The string is str, starting from the num position. The length is len and contains the num position (7). Remove the leading and trailing spaces of the string: LTRIM (str ); // remove the space at the start of the string RTRIM (str); // remove the space at the end of the string TRIM (str); // remove the space at the beginning and end of the string (8 ). string replacement function: INSERT (str, pos, len, newstr); // str is the original string, and pos is the start position to be replaced, len is the length of the replacement string (as needed), newstr is the new string REPLACE (str, substr, newstr); // str is the original string, substr IS the STRING to be replaced, newstr IS the STRING to replace substr, replace all matched strings Note: Compare the following two results: select replace ("this is test string", "IS ", "is not"); // this not is not test string, which does NOT distinguish words. Remember to select replace ("this is test string", "IS", "is not "); // this is not test STRING2. numeric function :( 1 ). obtain a random number: RAND (); // The returned number is completely random RAND (x); // x indicates a number, and the returned random value is the same (definite) (2 ). function for obtaining integers: CEIL (x); // The upper part is the integer FLOOR (x); // The lower part is the integer (3 ). TRUNCATE (x, y); // x indicates the value to be truncated, and y indicates the number of digits after the decimal point, that is, the number of decimal places are retained, instead of rounding. (4 ). rounding function: ROUND (x); ROUND (x, y); // returns the value x, which is retained to the y digit after the decimal point. date functions: (1 ). get the current date and time functions: now (); // the current date and time locatime (); // the current date and time current_timestamp (); // current date and time SYSDTAE (); // current date and time (2 ). current date: curdate (); // current date current_date (); // current date (3 ). current Time: curtime (); current_time (); (4 ). display date and time in various ways: unix_timestamp (date-type string); // convert a date-type string to an integer (in seconds). The UNIX format is from_unixtime ("integer "); // convert an integer string to a date. The common format is UTC_DATE (); // utc date, only the date UTC_TIME (); // utc time, only the time (5 ). obtain the score of each part of the date and time: select now () current date and time, YEAR (NOW () YEAR, QUARTER (NOW () QUARTER, MONTH (NOW () MONTH, WEEK (NOW () WEEK, DAYOFMONTH (NOW () day, HOUR (NOW () HOUR, MINUTE (NOW () MINUTE, SECOND (NOW () SECOND; (6 ). MONTH (the parameters are strings of the date type): MONTH (date); MONTHNAME (date); (7 ). week: week (date); // weekofyear (date) in the week of the year; // dayname (date) in the week of the year; // day of the week, which is displayed in English as dayofweek (date ); // Week (do not understand, use the following) weekday (date); // week, number (8 ). day dayofyear (date); // The day of the year dayofmonth (date); // The day of the month (9 ). EXTRACT function EXTRACT (YEAR from NOW (); // year extract (month from NOW (); // month EXTRACT (day from NOW ()); // daily EXTRACT (hour from NOW (); // hour EXTRACT (minutefrom NOW (); // minute EXTRACT (second from NOW ()); // seconds (10 ). function for calculating the date and time: select now () current date and time, TO_DAYS (NOW () are separated by the number of days, // FROM_DAYS (TO_DAYS (NOW () from January 1, 0000 ())) the date and time after a period of time, DATEDIFF (NOW (), '2017-12-01 ') are separated by days; (11 ). operation with specified date and time: ADDDATE (date, n); // calculate the date plus the date SUBDATE (date, n) after n days ); // calculate date minus n days after the date ADDDATE (d, INTERVAL expr type); // type view http://www.w3school.com.cn/ SQL /func_date_add.aspSUBDATE (d, INTERVAL expr type); example: select adddate (now (), interval '2, 3 'year_month) 'date after 2 years and 3 months'; 4. system information function (1 ). obtain MySQL system information: Use \ hSELECT VERSION (), DATABASE () DATABASE Name, USER () USER name; (2 ). obtain the final ID value of the auto-increment constraint SELECT LAST_INSERT_ID (); (3 ). other functions:

IF (value, t f)	If the value is true, t is returned; otherwise, f is returned.
IFNULL (value1, value2)	If value1 is not null, value1 is returned; otherwise, value2 is returned.
Case when [value1] THEN [result1]… ELSE [default] END	If value1 is true, result1 is returned; otherwise, default is returned.
CASE [expr] WHEN [value1] THEN [result1]… ELSE [default] END	If expr is equal to value1, result1 is returned; otherwise, default is returned.

 

Letter count	For use
PASSWORD (str)	Implement string str Encryption
FORMAT (x, n)	To format the number x and retain n decimal places
INET_ATON (ip)	Converts an IP address to a number.
INET_NTOA (x)	Converts a number to an IP address.
GET_LOCT (name, time)	Create a lock named name with a duration of time
RELEASE_LOCT (name)	Unlock a lock named name
BENCHMARK (count, expr)	The expression is repeated for count times.
CONVERT (s USING cs)	Converts the character set of string s to cs.
CONVERT (x, type)	Convert x to type