MySQL database-date and time functions

One, date and time functions

Concept of function: input parameters in the specified format, return the correct result of the unit of operation

1. Return the current date: Curdate ()

Current_date ()

Current_date () +0 to convert the current date to a numeric type

Example: Select Curdate (), Current_date (), curdate () +0

2015-09-22 2015-09-22 20150922

2. Return Current time: Curtime ()

Current_time ()

Current_time (+0)

Example: Select Curtime (), Current_time (), Curtime () +0

20:47:53 20:47:53 204753

3. Return the current date and time: Current_timestamp ()

LocalTime ()

Now ()

Sysdate ()

Example: Select Now () as ' at this moment ', sysdate () as ' at this moment 2 '

4. Get Month: month (date)

MonthName (date)

Example: Select month (' 2015-07-22 ')----> 7

Select MonthName (' 2015-07-22 ')---->july

5. Get Week: dayname (date)

DayOfWeek (date)

Weekday (date)

Week (date)

WeekOfYear (date)

Example: Select Dayname (' 2015-09-23 ')--->wednesday

Select DayOfWeek (' 2015-09-23 ')--->3 (with Sunday as the first day)

Select Weekday (' 2015-09-23 ')--->1 (No. 0 Day of Monday)

Select Week (' 2015-01-01 ')--->0 (which week falls in a year)

Select WeekOfYear (' 2015-01-01 ')--->1 (which week falls in a year)

6. Get Days: dayofyear (date)----> calculate the date specified in the parameter is the day of the year

DayOfMonth (date)----> calculation parameters The date specified in this month is the day of the week

Example: Select Week (' 2015-01-01 ')-->1

Select Week (' 2015-01-01 ')--->1

7. Get year, quarter, hour, minute, second

Year (date) Select year (' 2015-01-01 ')-->2015

Quarter (date) Select quarter (' 2015-09-22 ')--->3

Hour (time)

Minute (time)

Second (time)

Second, mathematical functions

1. Absolute value function->abs (X)

Example: Select ABS ( -8)-->8

2. Symbolic function sign (x): Determine if a number is positive or negative or zero? (Positive return value is 1, negative return value is-1, 0 return value is 0)

Example: Select sign ( -8)-->-1

3. Get the function of the random number: rand () Example: Select rand ()--(a new random number will be generated per run)

RAND (x) Example: Select rand (3) and the resulting random number is a fixed number

4. Functions to get integers: ceil (x) Ceilung (x) Example: Select Ceil (3.5)-->4 (take the smallest integer not less than 3.5)

Floor (x) Example: Select Floor (3.5)-->3 (maximum integer less than 3.5)

5. Rounding Function: round (x) Example: Select round (3.5)---> 4

Round (x, y) Example: Select Floor (3.14,1)--->3.1 (number of decimal digits reserved 1)

Example: Select Floor (3.14,-1)--->0 (reserved to 10 digits)

Truncate (x, y) Example: Select Truncate (3.15,1)--->3.1 (only one decimal place is left, and no rounding is directly truncated)

6. Find remainder function: mod (x, y) Example: Select mod (31,8)--->7

7. Power Operation function: Pow (x, y)/power (x, y) for x y Power Example: Select POW (2,3)--->8

EXP (x) The power of seeking natural logarithm

sqrt (x) to find the square root of a number: Select sqrt (2)----1.41421356237

Select sqrt (9)----3

8. Angle Radian Interchange function: radians (x) swaps angles into radians: Select radians--->3.1415926 ...

Degrees (x) transforms radians into angular examples: Select degrees (3.14)--->179.9.8 ...

9. Pi function: P ()

10. Trigonometric Functions: sin (x) sine

ASIN (x) anyway chord

cos (x) cosine

ACOs (x) Inverse cosine

Tan (x) tangent

Atan (x) anyway cut

Cot (x) cotangent

Three, String function

1. Functions for calculating the number of characters and the length of a string

Char_length (s): computes a few characters in a string

Example: Select Char_length (' abc ')--->3

Example: Select Char_length (' Hello ')--->3

Length (s): Computes a string that takes up a few bytes in memory

Example: Select Length (' abc ')--->3

Example: Select Length (' Hello ')--->9

2. Merging String functions: Concat (S1,s2 ...) concatenate the strings in the arguments into a new string example: Select Concat (' Hello ', ' abc ', ' word ')---> How are you, Abcword?

Concat_ws (X,s1,s2 ...) Example: Select Concat_ws (' # ', ' Hello ', ' abc ', ' word ')----> How are you #abc#word?

3. Replace the string function: INSERT (S1,X,LEN,S2) in S1 the string with the X position of the beginning of the length of a string such as Len to replace it with S2

Example: Select Insert (' ABCDEF ', ' 2 ', ' 3 ', ' # # ')--->a# #EF

Replace (S,S1,S2) replaces one of the substrings in the original string with another string

Example: Select Insert (' Abcdefab ', ' ab ', '% ')--->%cdef%

4. function to intercept string: Left (s,n) Example: Select Left (' Abcdefab ', ' 3 ')--->abc

Right (S,n) Example: Select Right (' Abcdefab ', ' 3 ')--->fab

5. Duplicate production string function: Repeat (s,n) Example: Select repeat (' ABC ', ' 3 ')--->abcabcabc

6. The function of the case conversion: lower (x, y)/lcase example: Select LCase (' ABC ')--->abc

Upper (x)/ucase (x) Example: Select UCase (' abc ')--->abc

7. The function to populate the string: Lpad (S1,LEN,S2) from the left to the fill example: Select Lpad (' ABCd ', ' # ')---->##### #ABCd

Rpad (S1,LEN,S2) Fill example from the right: Select Rpad (' ABCd ', Ten, ' # ')----->abcd######

8. Delete Space function: LTRIM (s)/rtrim (s) Delete left space/delete right space example: Select RTRIM (' ABCD ')---ABCd

TRIM (S) Remove both sides of the space example: Select TRIM (' ABCd ')----->ABCD

9. Delete the specified string: Trim (S1 from s) Example: Select Trim (' A ', from ' abcade ')--->bcade (delete a over the edges)

10. Get substring: SUBSTRING (S,n,len) Example: Select SUBSTRING (' Abcade ', 3,2)----->CA

MID (S,n,len) Example: Select substring (' Abcade ', 3,2)----->CA

11. Return the specified position string function: ELT (N,S1,S2 ...) Example: Select ELT (2, ' ABC ', ' DEF ', ' MYSQL ')---->def

12. Return the specified string location: FIELD (S,s1,s2 ...) Example: Select field (' Hi ', ' Hi ', ' HO ', ' HE ', ' HU ')---->1

Iv. system functions

1. function to get MySQL version number: version () Example: Select Version ()

2. View current number of user connections: connection_id () Example: Select connection_id

3. View the currently used database function: Datebase ()

SCHEMA9 ()

4. Get the user name function: User () Example: Select User (), Current_User (), System_user (), Session_user () [email protected]

Current_User ()

System_user ()

Session_user ()

MySQL database-date and time functions