MySQL functions-MySQL series (6)

1. Functions

Function portability is not very strong. Different DBMS has different functions.

2. Text processing functions

Function	Description
Left ()	Returns the character on the left of the string.
Length ()	Returns the length of a string.
Locate ()	Find a substring of a string
Lower ()	Convert string to lowercase
LTrim ()	Remove spaces on the left of the string
Right ()	Remove spaces on the right of the string
RTrim ()	Remove spaces on the right
Soundex ()	Returns the SOUNDEX value of the string.
SubString ()	Returns the character of a substring.
Upper ()	Convert string to uppercase

Soundex () Description

Consider similar pronunciation and similar bytes to make the strings more pronounced than letters

?

1 2	SELECT * FROM 'user' Where SOUNDEX ('name') = SOUNDEX ('lil ');

Return Value

Lie

Lee

3. Date Processing functions

(1) Description

Function	Description
AddDate ()	Add a date (days, weeks, etc)
AddTime ()	Add a time (hour, grade)
CurDate ()	Returns the current date.
CurTime ()	Returns the current time.
Date ()	Returns the date part of the date and time.
Datediff (expr1, expr2)	Calculate the difference between two dates
Date_Add ()	Highly Flexible date operation functions
Date_Format ()	Returns a formatted date or time string.
Day ()	Returns the number of days of a date.
DayofWeek ()	Returns the day of the week for a date.
Hour ()	Returns the hour of a date.
Minute ()	Returns the minute of a date.
Month ()	Returns the month of a date.
Now ()	Returns the current date and time.
Second ()	Returns the second part of a date.
Time ()	Returns the time part of a date and time.
Year ()	Returns the year of a date.

(2) query data of a day

?

1 2	Select order_id FROM orders Where Date (order_date) = '2017-04-01'

Or

?

1 2	SELECT order_id FROM orders Where order_date BETWEEN '2017-01-01 23:59:59 'AND '2017-01-01. 000'

Return the order on February 1,. Note that the incorrect syntax is as follows. The error is caused by the fact that all dates in the database have time, minute, and second, and cannot be exactly the same as a date.

?

1 2	SELECT order_id FROM orders Where order_date = '2017-04-01'

(3) query data for a month

?

1 2 3

SELECT order_id FROM orders Where Date (order_date) '2017-01-01 'AND '2017-01-31'

Or

?

1 2 3

SELECT order_id FROM orders Where Year (order_date) = '20140901' AND Month (order_date) = '01'

4. Numeric processing functions

Function	Description
Abs ()	Returns the absolute value of a number.
Cos ()	Returns the cosine of an angle.
Exp ()	Returns the exponential value of a number.
Mod ()	Returns the remainder of the division operation.
Pi ()	Returns the circumference rate.
Rand ()	Returns a random number.
Sin ()	Returns the sine of an angle.
Sqrt ()	Returns the square root of a number.
Tan ()	Returns the tangent of an angle.