MySQL function--mysql series (vi)

1. function

The portability of functions is not very strong, and different DBMS have different functions

2. Text Processing function

/tr>

function	description
left ()
length ()
locate ()
lower ()
ltrim ()
right ()	Remove the space to the right of the string
rtrim ()	Remove the space on the right
soundex ()	Returns the Soundex value of the string
substring ()
upper ()

About Soundex () description

Consider similar sounds and similar bytes to make a string sound comparison rather than a letter comparison

?

1 2	SELECT * from ' user ' Where SOUNDEX (' name ') = SOUNDEX (' Lee ');

return value

Lie

Lee

3, date processing function

(1) Description

function	Description
Adddate ()	Add a date (days, weeks, etc.)
Addtime ()	Add a time (time, minutes, etc)
Curdate ()	Returns the current date
Curtime ()	Returns the current time
Date ()	Returns the date part of a date time
Datediff (EXPR1,EXPR2)	Calculate the difference between two dates
Date_add ()	A highly flexible date operation function
Date_format ()	Returns a formatted date or time string
Day ()	Returns the number of days in a date section
DayOfWeek ()	Returns the day of the week for a date
Hour ()	Returns the hour portion of a date
Minute ()	Returns the minute portion of a date
Month ()	Returns the month portion of a date
Now ()	Returns the current date and time
Second ()	Returns the second part of a date
Time ()	Returns the time portion of a datetime
Year ()	Returns the year portion of a date

(2) Check the data of one day

?

1 2	Select order_id from Orders Where Date (order_date) = ' 2014-04-01 '

Or

?

1 2	SELECT order_id from Orders Where order_date BETWEEN ' 2014-01-01 00:00:000 ' and ' 2014-01-01 23:59:59.000 '

Return 2014-04-01 orders, note that the error is written as follows, the error is due to the average date in the database is sometimes minutes, can not be completely equal to a date

?

1 2	SELECT order_id from Orders Where order_date= ' 2014-04-01 '

(3) Check a month's data

?

1 2 3

SELECT order_id from Orders Where Date (order_date) BETWEEN ' 2014-01-01 ' and ' 2014-01-31 '

Or

?

1 2 3

SELECT order_id from the orders Where year (order_date) = ' 2014 ' and Month (order_date) = ' 01 '

4. Numerical processing function

function	description
abs ()	Returns the absolute value of a number
cos ()
exp ()	Returns the exponential value of a number
mod ()	Returns the remainder of the operation
pi ()	return pi
rand ()	Returns a random number
sin ()	Returns the sine of an angle
sqrt ()
tan ()