String functions
(1)
Syntax: CONCAT (str1,str2,..., strn)
Parameter one: string to be spliced
Parameter two: string to be spliced
Returns the structure: a complete string
SELECT
CONCAT (' MY ', ' S ', ' QL ')
(2)
Syntax: INSERT (STR,POS,LEN,NEWSTR)
Parameter one: string
Parameter two: Where to start
Parameter three: Length intercepted from the start position
Parameter four: A new string in place of the intercepted part
Returns the structure: a new string
SELECT
INSERT (' This is SQL Server database ', 3,10, ' MYSQL ')
(3)
Syntax: LOWER (str)
Parameter one: an ordinary and ordinary string
Return structure: Converts all letters in a string to lowercase
SELECT
LOWER (' ABC ')
(4)
Syntax: UPPER (str)
Parameter one: an ordinary and ordinary string
Return structure: Capitalize all letters in a string
SELECT
UPPER (' ABC ')
(5)
Syntax:SUBSTRING (Str,num,len)
Parameter one: an ordinary and ordinary string
Parameter two: Starting position (starting from 1)
Parameter three: length of intercept starting from start position
Return structure: Returns a string that intercepts Len length from num position
SELECT
SUBSTRING (' 123456789 ', 5, 3)
Time-Date function
(1)
Syntax: Curdate ()
Return structure: Get the current date
SELECT
Curdate ()
(2)
Syntax: Curtime ()
Return structure: Get current time
SELECT
Curtime ()
(3)
Syntax: Now ()
Return structure: Gets the current date and time
SELECT
Now ()
(4)
Syntax: WEEK (DATE1)
Parameter one: an ordinary and ordinary date
Return structure: Gets the date date1 is the week ordinal of the year
SELECT
WEEK (now ())
(5)
Syntax: Year (DATE1)
Parameter one: an ordinary and ordinary date
Return structure: Gets the year of the date date1
SELECT
Year (now ())
(6)
Syntax: HOUR (_time)
Parameter one: an ordinary and ordinary time
Return structure: Gets the time value of _time
SELECT
HOUR (now ())
(7)
Syntax: MINUTE (_time)
Parameter one: an ordinary and ordinary time
Return structure: Gets the minute value of the time _time
SELECT
MINUTE (now ())
(8)
Syntax: DATEDIFF (DATE1,DATE2)
Parameter one: an ordinary and ordinary date
Parameter two: Another ordinary and ordinary date
Return structure: Number of days separated by two dates
SELECT
DATEDIFF (now (), ' 2017-02-02 ')
(9)
Adddate (Date1,n)
Parameter one: an ordinary and ordinary date
Parameter two: A mysterious and good-hiding figure
Return structure: Date date1 plus n days after date
SELECT
Adddate (now (), 20)
numeric functions
(1)
Syntax: Ceil (X)
Parameters: Data to be processed
Return value: Returns the smallest integer greater than or equal to the number X
For example:
SELECT Ceil (2.1)
Return value: 3
(2)
Syntax: Floor (X)
Parameters: Data to be processed
Return value: Returns the smallest integer less than or equal to X
For example:
SELECT Floor (2.1)
return Value: 2
(3)
Syntax: RAND ()
Return value: Random number from 0 to 1
For example:
SELECT RAND ()
Return value: Random number from 0 to 1
MySQL Common functions