Common functions in MySQL: 1. String functions: (1). Merge string Concat ()://Concat (' M ', ' y ', ' SQL ', ' 5.5 '); = = mysql5.5//When the parameter passed in has a value of NULL, The returned result value is Nullconcat_ws ();//The specified delimiter is inserted into the string: Concat_ws (":", ' 2017 ', ' 09 '), ==2017:09, but if the delimiter is null, the result is null, the argument is null, Does not affect the result (2). Functions that Compare string sizes: Select strcmp (String 1, String 2), greater than return 1, less than return-1, or 0 (3). Gets the string length function: Length (); Calculates a space, which is the length of the passed parameter (in bytes), and a Chinese character is two bytes. Char_length ();//The number of characters (character length) passed in the parameter (4). Letter Case Conversion Function: UPPER (), or UCase () converted to uppercase lower ();( 5). Find string: Select Find_in_set (Find string, target string collection);//function that returns the string position, the target string is a comma-delimited collection of strings, subscript starting from 1 Select FIELD (Find string, target string 1, target string 2 ,...........);/ /returns the string position of the function locate (STR1,STR),//returns the starting position of the string str1 in str position (str1 in str),//returns the starting position of string str1 in str instr (STR,STR1); Returns the starting position of the string str1 in Str (N,STR1,STR2......,STRN);//returns the Nth string Make_set (num,str1,str2,... strn);//First converts the value num to binary, Then follow the binary number from the parameter str1,str2,...., strn to select the appropriate string example: Select BIN (5) As binary number, Make_set (5, ' MYSQL ', ' ORACLE ', ' SQL SERVER ', ' POSTGRESQL ') as the selected string;//The result is Mysql,sql SERVER (6). Intercept the String function: Left (str,num),//intercept from the Ieft, the string is str, the length is numright (str,num);// Intercept from the right, the string is str, the length is NumsubstrinG (Str,num,len);//The string is str, starting at num position, Len, containing num position mid (Str,num,len);//string is str, starting at num position, Len, containing num position (7), Remove the trailing space of the string: LTRIM (str);//Remove the space at the beginning of the string RTrim (str);//Remove the space at the end of the string trim (str);//Remove the string from the trailing space (8). Replace string function: INSERT (Str,pos,len, NEWSTR);//STR is the original string, POS is the starting position to replace, Len is the length of the replacement (as required), NEWSTR is the replacement of the new string replace (STR,SUBSTR,NEWSTR);//STR is the original string, SUBSTR is a string that needs to be replaced, NEWSTR is a string that replaces the substr, replacing all matching strings Note: Compare the following two results: Select replace ("This is TEST string", "is", "is");// This isn't the not-test string, which does not differentiate between words, remember Select replace ("This is TEST string", "is", "was not");//this are not test STRING2. Value Letters Number: (1). Get random Number: RAND (); Returns the number of completely random rand (x); The x represents the number, and the returned random number is the same (OK) (2). The function that gets the integer: Ceil (x),//Take the whole floor (x),//Take down the whole (3). Intercept the numeric function: TRUNCATE (x, y),/× represents the number to intercept, and y for several decimal places That is to keep a few decimals, is to go directly, rather than rounding. (4). Rounding Function: ROUND (x); ROUND (x, y);//Returns the value x is reserved to the Y-bit after the decimal point 3. Date function: (1). Gets the current date and time function: Now ();//Current date and time Locatime ();//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 (string of date type),//Convert the string of date type to shaping (seconds), Unix format from_unixtime ("shaping"),//Convert the shaping string to a date, normal format utc_date ();//UTC date, only date utc_ Time ();//UTC, only time (5). Gets the date and time for each part of the value:SELECT Now () current date and time , years (now ()) , QUARTER (now ())Quarter,MONTH (now ())months,WEEK (now ())Week,DayOfMonth (now ())days,HOUR (now ())hours,MINUTE (now ())points,SECOND (now ())seconds;(6). Month (parameters are string of date type): month (date); MONTHNAME (date);(7). Week: Week (date),//The first few weeks of the year weekofyear (date),//The first few weeks of the year dayname (date);//week, English display DayOfWeek (date);// Week (not understood, with the following) weekday (date);//week, numbers indicate (8). Days DayOfYear (date),//The day of the Year DayOfMonth (date);//day of the month (9). Extract function extract (year from now ()); Year extract (month from now ()); Month extract (day from now ()); Day extract (hour from now ()); Time Extract (Minutefrom now ()); Sub-extract (second from now ()); Seconds (10). function to calculate date and time: SELECT now ()the current date and time,To_days (now ())days apart,//distance January 1, 00From_days (To_days (now ()))after a period of time, date and time,DATEDIFF (now (), ' 2000-12-01 ')number of days apart;(11). Operation with specified date and time: Adddate (date,n); Calculates date subdate (date,n) of day plus n days; Calculates date minus n days adddate (D,interval expr type);//Type view of typehttp://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 3 months '; 4. System Information function (1). Get MySQL System Information : Using \hselect VERSION ()version number,DATABASE ()database name,USER ()user name;(2). Gets the last ID value of the self-growth constraint Select last_insert_id ();(3). Other functions:
| if (value,t f) |
If value is true, return T, otherwise return F |
| ifnull (value1,value2) |
If value1 is not empty, return value1, otherwise value2 |
| case when [value1] then[result1] ... Else[default] end |
If value1 is true, return RESULT1, otherwise return default |
| Case [expr]when[value1] then[result1] ... Else[default]END |
Returns RESULT1 if expr equals value1, otherwise returns the default |
| Number of functions |
with |
| password (str) |
implements encryption of string str |
| format (x,n) |
|
| inet_aton (IP) |
|
| inet_ntoa (x) |
|
| get_loct (name,time) |
Create a lock named name with duration of time |
| RELEASE_LOCT (name) |
|
| benchmark (count,expr) |
implementation repeats an expression count times |
| convert (S USING cs) |
|
| CONVERT (X,type) |
Implementation converts X into type |
Common functions in MySQL:
