MySQL Common functions

Tag: Style color strong for line time

I. Introduction to Character types

1, CHAR, VARCHAR, TEXT is called a non-binary string;

2, binary, VARBINARY, BLOB called binary string

3. Column type Storage Space  

CHAR (M) m bytes, 0 <= m <= 255 (l is fixed = 255, not enough space)
VARCHAR (M) l+1 bytes, where L <= m and 0 <= m <= 65535 (see note below) (MySQL5.0 is the largest 255 before)
 BINARY (M)m bytes, 0 <= m <= 255  
VARBINARY (M) l+1 bytes, where L <= m and 0 <= m <= 255
 Tinyblob, Tinytextl+1 bytes, where L < (n) 

BLOB, TEXTl+2 bytes, where L < 216 (65536)
Mediumblob, Mediumtextl+3 bytes, where L < 224 (167772150)
 Longblob, Longtextl+4 bytes, where L < 232 (4294967295)
 ENUM (' value1 ', ' value2 ',...)1 bytes (less than 256 members) or 2 bytes (greater than 255 members), depending on the number of enumeration values (up to 65,535 values)
 SET (' value1 ', ' value2 ',...)1 (0~8成), 2 (9~16成), 3 (17~24成), 4 (25~32成), 8 (33~64成) bytes, up to 64 members

Second, commonly used functions

1. String functions

Cancat (str1,str2,... strn) connect str1,str2,... strn to a string

Insert (str, x, Y, instr) starts the string str from position x, and a substring of y characters is replaced with a string instr

lower/upper (str) returns the lowercase/uppercase of the string str

left/right (str,x) returns the leftmost/right x character of the string str

Lpad/rpad (str, n,pad) fills str leftmost/right with a string pad, and STR is n characters long

Ltrim/rtrim/trim (str) Remove string str header/Line end/line-ending spaces

REPEAT (str, x) Returns the result of repeated x times of STR

Replace (STR,A,B) replaces all occurrences of string A in str with string b

STRCMP (STR1,STR2) comparing strings str1 and STR2

SUBSTRING (str,x,y) returns a substring of Y-character length from the x position of the string str

2. Numerical functions

ABS (x) returns the absolute value of X

Ceil (x) returns the maximum integer value greater than X

Floor (x) returns the maximum integer value less than x

MOD (x, y) returns the modulo of X/y

RAND () returns the random value within the 0~1

ROUND (x, y) returns the rounding of parameter x with a value of y decimal place

TRUNCATE (x, y) Returns the result of a number x truncated to a Y decimal place

3. Date Time function

curdate ()/curtime ()/Now() Returns the current date/time/date and time

Unix_timestamp (date) returns the UNIX timestamp for date

From_unixtime(unix_timestampe,format) returns the date value of the Unix timestamp

======================== Time String Formatting format=========================

%s,%s seconds in two-digit form (00,01,..., 59)

%i Two-bit digital form (00,01,..., 59)

%H Two-digit hours, 24 hours (00,01,..., 23)

%h,%i Two-digit hours, 12 hours (01,02,..., 12)

%k Digital form of hour, 24 hours (0,1,..., 23)

%lHours in digital form, 12 hours (,..., 12)

%T24-hour time form (HH:MM:SS)

%r12-hour time form (Hh:mm:ssAM or Hh:mm:ssPM)

%pAM or PM

%W The name of each day of the week (Sunday,monday,..., Saturday)

%a name abbreviation for each day of the week (Sun,mon,..., Sat)
%w Number of days in the week (0=sunday,1=monday,..., 6=saturday)
%d Two-digit table indicates the number of days in the month (00,01,...,)

%e Number representation of days in the month (,..., 31)

%d English suffix indicates the number of days in the month (1st,2nd,3rd,...)

%j represents the number of days in the year in 3 digits (001,002,... 366)

%u Week (0,1,52) takes Sunday as the first day of the week

%u Week (0,1,52) takes Monday as the first day of the week

%M month name (january,feburary,..., December)

%b abbreviated month name (Jan,feb,..., Dec)

%ma two-digit number indicatesMonth (01,02,..., 12)

The month of%c numbers (,...,)

%Y4-digit year

%y2-digit year

Percent Direct value "%"

========================end========================

Year (date) returns the date of day

MONTHNAME (date) returns the month name of the date

WEEK (date) Returns the date of the week of the year

HOUR (time) returns the hour value of time

MINUTE (time) returns the minute value of time

Date_format (DATE,FMT) returns the date value formatted by the note string FMT

Date_add (Date,interval expr type) returns a time value with a date or time value plus a time interval

DATEDIFF (EXPR1,EXPR2) Returns the number of days between the start time Expr1 and the end time EXPR2