MySQL function--mysql series (vi)

Source: Internet
Author: User
Tags comparison current time square root

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 ()
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.