MySQL Common functions

Uppercase and lowercase

Add the keyword binary before the condition

Take an integer

Floor: The function returns only the integer part, and the fractional part discards

Round: Function rounding

Ceil: Rounding up

Conditional judgment

IF (EXP1,TRUE-EXP2,FALSE-EXP3)

is empty

ISNULL ()

Intercept string

1. Left (str,index) intercept from index

2. Right (Str,index) intercept from index

3, substring (str,index) when index>0 from the left to intercept until the end when the index<0 from the right to intercept until the end when the index=0 return empty

4, SUBSTRING (str,index,len) intercept str, starting from index, intercept len length

5, Substring_index (str,delim,count), Str is the string to intercept, Delim is the intercept of the field count is from where to start interception (for 0 is the No. 0 left, 1 left to start the first selection left,- 1 from the right, the first one, select the right

6, Subdate (date,day) intercept time, time minus the day after

7, Subtime (EXPR1,EXPR2) seconds expr1-expr2

Stitching strings

CONCAT (CAST (exp1 as CHAR), EXP2)

Time and String conversions

Time-to-timestamp Unix_timestamp (now ())

Time-to-character date_format (now (), '%y-%m-%d ')

Character Turn time str_to_date (' 2016-01-02 ', '%y-%m-%d%H ')

Character to timestamp unix_timestamp (' 2016-01-02 ')

Time Stamp Turn Time From_unixtime (1451997924)

Timestamp to String From_unixtime (1451997924,'%y-%d ')

Time Format schedule:

	value	meaning
Seconds	%s,%s	Two digits in the form of seconds (00, 01, ..., 59)
Score of	%I,%I	Two digits in the form of points (00, 01, ..., 59)
Hours	%H	24-hour system, two-digit hour (00,01, ..., 23)
	%h	12-hour system, two-digit hour (00,01, ..., 12)
	%k	24-hour system, number of forms hours (0,1, ..., 23)
	%l	12-hour system, number of forms hours (0,1, ..., 12)
	%T	24-hour system, time form (HH:MM:SS)
	%r	12-hour, time form (Hh:mm:ss AM or PM)
	%p	Am Morning or PM afternoon
Week	%W	The name of each day of the week (Sunday,monday, ..., Saturday)
	%a	Abbreviation for the name of each day of the week (Sun,mon, ..., Sat)
	%w	Identify weeks (0=sunday,1=monday, ..., 6=saturday) in digital form
	%u	The number represents the week, and Sunday is the first day of the week
	%u	The number represents the week, and Monday is the first day of the week
Days	%d	Two-digit number of days in the month (01,02, ..., 31)
	%e	Number indicates the number of days in the month (1, 2, ..., 31)
	%d	The English suffix indicates the number of days in the month (1st,2nd,3rd ... ）
	%j	Three-digit number of days in the year (001,002, ..., 366)
Month	%M	English month name (January,february, ..., December)
	%b	English abbreviation month name (Jan,feb, ..., DEC)
	%m	Two digits indicate month (01,02, ..., 12)
	%c	Numbers indicate the month (1, 2, ..., 12)
Years	%Y	Four-digit year (2015,2016 ...)
	%y	Two-digit year (15,16 ...)
Text output	% text	Direct output of text content

Data type conversions

CONVERT (XXX, type) or cast (xxx as type)

Binary, with binary prefix effect: binary
Character type, with parameters: CHAR ()
Date: Date
Time:
DateTime Date/Time type
Floating point number: DECIMAL
Integer: Signed
unsigned integer: UNSIGNED

Random value

Order by Rand () LIMIT 1 data volume is not recommended, it is recommended to write in the service

MySQL Common functions