MySQL Common functions

First, mathematical functions

1 ABS (X) absolute value

2 pi () π

3 floor (x) maximum integer value less than x

4 RAND ()| RAND (x) random number ; The X parameter produces the same random number at the same time.

5 ROUND (x)| ROUND (x, y) rounding ; Y specifies the number of digits reserved, which can be negative.

6 TRUNCATE (x, y) intercept , retain the Y-bit after the decimal point, can be negative.

7 POW (x, y) | The Y-square of POWER (x, y) X.

Second, String function

1 char_length (str) Number of characters ' a Chinese character is counted as a char '

2 Length (str) bytes Long ' A kanji is 3 bytes '

3 CONCAT (s1,s2 ...) concatenation , with NULL in the parameter, returns NULL.

4 Cuncat_ws (X,s1,s2 ...) There are split stitching, X is a delimiter, and the concatenation ignores the null parameter. If x is null, returns NULL.

5 INSERT (S1,X,LEN,S2) S1 is replaced by a string of (contiguous) , s2 the string to replace, x start position, len length. Any one parameter is null and returns NULL.

6 LCASE (str) or lower (str) | Convert UCASE (str) or upper (str) to lowercase | uppercase

7 Left (s,n) | Right (S,n) returns the string s left | n bits

8 Lpad (S1,LEN,S2) | Rpad (S1,LEN,S2) S1 left | Right is filled by S2 to Len length If Len is less than the length of the S1 itself, the Len length will be truncated from right

9 TRIM (s) | LTRIM (s) | RTRIM (s) go space | left space | right space

Replace (S,S1,S2) replaces the S1 in the string s (a single string) to S2

One STRCMP (s1,s2) compares the size , the same returns 0, the first small, returns-1, and other cases returns 1.

SUBSTRING (s,n,len) | MID (S,n,len) intercepts the string s, starting at the nth bit and Len in length.

LOCATE (str1,str) | POSITION (str1 in str) | INSTR (STR,STR1) The position where the str1 begins to appear in Str.

REVERSE (s) reverse order

Third, date and time functions

1 curdate (), current_date () | Curtime (), Current_time () returns the current date | time

2 Current_timestamp (), localtime (), now(), sysdate () Current date and time

3 Unix_timestamp (date) | From_unixtime (date) returns the timestamp of the date | convert timestamp to date time

4 Utc_date () | Utc_time () Date of the current time zone | time

5 MONTH (date) | MONTHNAME (date) return month | English month

6 Dayname (d) | DAYOFWEEK (d) | WEEKDAY (d) English week: Sunday, 2 means Sunday, 1 indicates Tuesday.

7 adddate (date,interval expr type), Date_add (date,interval expr type) Date Time addition expr is the time interval for addition or subtraction Type can be understood as a unit

8 subdate (date,interval expr type), date_sub (date,interval expr type) subtraction

9 addtime (date,expr) | Subtime (date,expr) time addition | subtraction

Ten DATEDIFF (date1,date2) date interval

date_format (date,format) formatting

MySQL Common functions