当前位置：网站首页>MySQL advanced common functions

MySQL advanced common functions

2022-04-23 20:45:00 【Jan York】

occasionally , In addition to simple data query , We also have some advanced functions .

MySQL Contains a large number and rich functions , This set of MySQL The complete collection of functions only collects dozens of commonly used , The rest of the relatively rare functions will not be sorted out , Readers can go to MySQL Official website Inquire about .

source ：C Chinese language network

`MySQL` Functions of numerical type

The name of the function	do use
`ABS`	Find the absolute value
`SQRT`	Find the quadratic root
`MOD`	Mod
`CEIL and CEILING`	The two functions have the same function , All return the minimum integer no less than the parameter , That is, round up
`FLOOR`	Rounding down , The return value is converted to a `BIGINT`
`RAND`	Generate a 0~1 Random number between , The input integer parameter is , Used to generate repeat sequences
`ROUND`	Round the parameters passed
`SIGN`	Returns the symbol of the parameter
`POW` and `POWER`	The functions of the two functions are the same , Are the result values of the power of the passed parameters
`kSIN`	Find the sine
`ASIN`	Find the inverse sine , And functions `SIN` They're inverse functions to each other
`COS`	Find the cosine
`ACOS`	Find the inverse cosine , And functions `COS` They're inverse functions to each other
`TAN`	Find tangent
`ATAN`	Find the arctangent , And functions `TAN` They're inverse functions to each other
`COT`	Find the cotangent value

`MySQL` String function

The name of the function	do use
`LENGTH`	Evaluate string length function , Returns the byte length of the string
`CONCAT`	Merge string functions , The return result is the string generated by the connection parameter , Parameters can make one or more
`INSERT`	Replace string functions
`LOWER`	Convert the letters in the string to lowercase
`UPPER`	Convert the letters in the string to uppercase
`LEFT`	Take the string from the left side of the word , Returns several characters to the left of the string
`RIGHT`	Take the string from the right word , Returns several characters to the right of the string
`TRIM`	Delete the spaces on the left and right of the string
`REPLACE`	String replacement function , Return the new string after replacement
`SUBSTRING`	Intercepting string , Returns a character change of a specified length starting at a specified location
`REVERSE`	String inversion （ The reverse ） function , Returns the string in reverse order to the original string

`MySQL` Date and time functions

The name of the function	do use
`CURDATE and CURRENT_DATE`	The two functions work the same , Returns the date value of the current system
`CURTIME and CURRENT_TIME`	The two functions work the same , Returns the time value of the current system
`NOW and SYSDATE`	The two functions work the same , Returns the date and time values of the current system
`UNIX_TIMESTAMP`	obtain UNIX Time stamp function , Return one to `UNIX` A timestamp based unsigned integer
`FROM_UNIXTIME`	take UNIX Time stamp converted to time format , And `UNIX_TIMESTAMP` They're inverse functions to each other
`MONTH`	Get the month in the specified date
`MONTHNAME`	Get the English name of the month in the specified date
`DAYNAME`	Get the English name of the day of the week corresponding to the specified date
`DAYOFWEEK`	Gets the index location value of the week corresponding to the specified date
`WEEK`	Get the specified date is the week of the year , Whether the return value range is `0〜52` or `1〜53`
`DAYOFYEAR`	The date of acquisition is the day of the year , The return value range is `1~366`
`DAYOFMONTH`	Get the specified date is the day of the month , The return value range is `1~31`
`YEAR`	Get year , The return value range is `1970〜2069`
`TIME_TO_SEC`	Convert the time parameter to seconds
`SEC_TO_TIME`	Convert seconds to time , And `TIME_TO_SEC` They're inverse functions to each other
`DATE_ADD` and `ADDDATE`	The two functions have the same function , All add the specified time interval to the date
`DATE_SUB` and `SUBDATE`	The two functions have the same function , It's all subtracting the specified time interval from the date
`ADDTIME`	Time addition operation , Add the specified time... To the original time
`SUBTIME`	Time subtraction , Subtract the specified time from the original time
`DATEDIFF`	Get the interval between two dates , Returns the parameter `1` Subtract the parameter `2` Value
`DATE_FORMAT`	Format the specified date , Returns the value of the specified format according to the parameter
`WEEKDAY`	Get the corresponding working day index of the specified date in a week

`MySQL` Aggregate functions

The name of the function	effect
`MAX`	Query the maximum value of the specified column
`MIN`	Query the minimum value of the specified column
`COUNT`	Count the number of rows in the query result
`SUM`	Sum up , Returns the sum of the specified columns
`AVG`	averaging , Returns the average of the specified column data