Mysql Common functions

String functions

CONCAT (str1, str2 ...)----concatenate the incoming string into a new string, and any string and NULL join result is nullinsert (str, index, size, str1)----starting from index, altogether Si  Ze characters, replaced by str1 SELECT INSERT (' abcdef ', 3, 2, ' * ')//Ab*eflower (str), UPPER (str)----alphabetic character case conversion left (str, size), right (str, Size)----Returns the left/right size characters SELECT left (' Chengdu ', 3)    //Che    ltrin (str), TRIM (str), RTRIM (str)----go to/from/to the right of the space rep Lace (str, str1, STR2)----to replace the str1 in Str with the STR2SUBSTR (str, start, size)----intercept the size characters in Str starting with the start of the first SELECT SUBSTR (' ABCdef ', 3, 2)    Cdstr_to_date (str, format)----string STR is converted to a date type in format form SELECT str_to_date (' 2018-08-16 ',%y-%m-%d%h:%i:%s)  // 2018-08-16 of the DateTime type

numeric functions

ABS (x)----Take absolute value ceil (x)----up to the floor (x)----down to the whole mod (y)----modulo, return the remainder of x/y rand ()----random number between 0~1 SELECT Ceil (rand () * )  //0-10 integer

Date and Time functions

Curdate ()  current date curtime () Current  time now ()  current datetime unix_timestamp timestamp  of current datetime time Form_unixtime (timestamp)   Convert the timestamp to datetime date_add (date, INTERVAL num type)    The incoming date is added Select_add (now (), INTERVAL 3    days)//returns the current datetime plus 3 day select_ Add (now (), INTERVAL 3 years)    //Return current DateTime plus 3 year Select_add (now (), INTERVAL-3 SECOND)    //return current date minus 3 seconds

Process functions

  if value is true, returns T, and vice versa returns F # If the total number of students is greater than 3, query the male student information, whether the female classmate SELECT * from students WHERE gender = if (SEL  ECT COUNT (*) from students) > 3, ' Male ', ' female '); Ifnull (value1, value2)  if value1 is not empty return value1, the person returns Value2case when then End SELECT case when 2>3 and ' right ' ELSE ' wrong ' END;  If 2>3, return right, no return wrong

Other

Database () returns current database name version () returns the current user () returns the current  password (str)  for STR encryption MD5 (STR)  returns STR MD5 value

Mysql Common functions