This document introduces some commonly used MySQL functions and operators according to numeric functions, string functions, date and time functions, aggregate functions, and flow control functions. For more function usage, please refer to: MySQL Official Documentation
Function Name | Purpose |
ABS | Calculate absolute value |
SQRT | Calculate square root |
MOD | Calculate remainder |
CEIL and CEILING | Both functions have the same functionality, returning the smallest integer not less than the parameter, i.e., rounding up |
FLOOR | Round down, return value converted to a BIGINT |
RAND | Generate a random number between 0~1, when an integer parameter is passed, used to generate repeated sequences |
ROUND | Round the passed parameter |
SIGN | Return the sign of the parameter |
POW and POWER | Both functions have the same functionality, returning the result value of the specified power of the passed parameter |
SIN | Calculate sine value |
ASIN | Calculate arcsine value, inverse function of SIN |
COS | Calculate cosine value |
ACOS | Calculate arccosine value, inverse function of COS |
TAN | Calculate tangent value |
ATAN | Calculate arctangent value, inverse function of TAN |
COT | Calculate cotangent value |
Function Name | Purpose |
LENGTH | Calculate string length function, returns the byte length of the string |
CONCAT | Merge string function, returns the string produced by connecting parameters, parameters can be one or more |
INSERT | Replace string function |
LOWER | Convert letters in string to lowercase |
UPPER | Convert letters in string to uppercase |
LEFT | Extract string from left side, returns several characters on the left side of the string |
RIGHT | Extract string from right side, returns several characters on the right side of the string |
TRIM | Delete spaces on both sides of the string |
REPLACE | String replacement function, returns the new string after replacement |
SUBSTRING | Extract string, returns characters of specified length starting from specified position |
REVERSE | String reverse (inverse order) function, returns string with opposite order to original string |
Function Name | Purpose |
CURDATE and CURRENT_DATE | Both functions have the same effect, returning the current system date value |
CURTIME and CURRENT_TIME | Both functions have the same effect, returning the current system time value |
NOW and SYSDATE | Both functions have the same effect, returning the current system date and time value |
UNIX_TIMESTAMP | Get UNIX timestamp function, returns an unsigned integer based on UNIX timestamp |
FROM_UNIXTIME | Convert UNIX timestamp to time format, inverse function of UNIX_TIMESTAMP |
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 | Get the index position value of the week corresponding to the specified date |
WEEK | Get which week of the year the specified date is, whether the return value range is 052 or 153 |
DAYOFYEAR | Get which day of the year the specified date is, return value range is 1~366 |
DAYOFMONTH | Get which day of the month the specified date is, return value range is 1~31 |
YEAR | Get year, return value range is 1970~2069 |
TIME_TO_SEC | Convert time parameter to seconds |
SEC_TO_TIME | Convert seconds to time, inverse function of TIME_TO_SEC |
DATE_ADD and ADDDATE | Both functions have the same functionality, both add specified time interval to date |
DATE_SUB and SUBDATE | Both functions have the same functionality, both subtract specified time interval from date |
ADDTIME | Time addition operation, add specified time to original time |
SUBTIME | Time subtraction operation, subtract specified time from original time |
DATEDIFF | Get the interval between two dates, returns the value of parameter 1 minus parameter 2 |
DATE_FORMAT | Format the specified date, returns value in specified format according to parameters |
WEEKDAY | Get the corresponding workday index of the specified date within a week |