
summarize
MySQL commonly used function summary, hoping to help you, nothing to see when reviewing
string processing function
函数 |
功能 |
CONCAT(s1,s2,….) |
concatenation of string |
INSERT(str,x,y,instr) |
Replaces the string from the specified start mark to the end with the specified string |
LOWER(str) |
Converts all characters of a string to lowercase |
UPPER(str) |
Converts all strings of a string to uppercase |
LEFT(str,x) |
Returns the leftmost x characters of string str |
RIGHT(str,x) |
Returns the rightmost x characters of string str |
LPAD(str,n,pad) |
Fill n pads on the leftmost side of str. |
RPAD(str,n,pad) |
Fill n pads at the far right of str. |
LTRIM(str) |
Remove the space to the left of string str |
RTRIM(str) |
Remove spaces to the right of string str |
REPEAT(str,x) |
Returns the result of str repeating x times |
STRCMP(s1,s2) |
Compare strings s1 and s2 |
REPLACE(str,a,b) |
Replace all occurrences of string a in string str with string b |
TRIM(str) |
Remove the spaces at the end of the string line and the line head. |
SUBSTRING(str,x,y) |
Returns a string of y characters from the str x position |
Mathematical function
函数 |
功能 |
ABS(x) |
Returns the absolute value of x |
CEIL(x) |
Returns the smallest integer value greater than x |
FLOOR(x) |
Returns the maximum integer value less than x |
MOD(x,y) |
Returns the modulus of x/y |
RAND() |
Returns a random value from 0 to 1 |
ROUND(x,y) |
Returns the rounded value of parameter x with y decimal places |
TRUNCATE(x,y) |
Returns the result of truncating the number x to the y decimal |
Date and time function
函数 |
功能 |
CURDATE() |
Returns the current date |
CURTIME() |
Returns the current time |
NOW() |
Returns the current date and time |
UNIX_TIMESTAMP(date) |
UNIX timestamp returning date |
FROM_UNIXTIME |
Returns the date value of the UNIX timestamp |
WEEK(date) |
The return date date is the week of the year |
YEAR(date) |
Returns the year of the date |
HOUR(time) |
Returns the hour value of time |
MINUTE(time) |
Returns the minute value of time |
MONTHNAME(date) |
Returns the month name of date |
DATE_FORMAT(date,fmt) |
Returns date value in string fmt format |
DATE_ADD(date,interval expr type) |
Returns a date or time value plus a time value for an interval |
DATEDIFF(expr,expr2) |
Returns the number of days between the start time expr and the end time expr2 |
Process function
函数 |
功能 |
IF(value,t f) |
If value is true, return t; Otherwise return f |
IFNULL(value1,value2) |
If value1 is not empty, value1 is returned; otherwise, value2 is returned |
CASE WHEN [value1] THEN[result1]…ELSE[default]END |
If value1 is true, result1 is returned; otherwise, result1 is returned. |
CASE[expr] WHEN [value1]THEN[result1]…ELSE[default]END |
If expr equals value1, result1 is returned; otherwise, default is returned. |
Other commonly used functions
函数 |
功能 |
DATEBASE() |
Returns the current database name |
VERSION() |
Returns the current database version |
USER() |
Returns the current login user name |
INET_ATON(ip) |
Returns a numeric representation of an ip address |
INET_NTOA(num) |
Returns the ip address represented by the number |
PASSWORD(str) |
Returns the encrypted version of string str |
MD5() |
Returns the md5 value of string str |
Other articles
Another article I have compiled
MySQL Common System Tables Summary:https://blog.fastrun.cn/2016/ …
Thank you
Thank you for reading this article. I believe junior high school coders are exposed to SQL every day. memorizing mysql functions is just as important as memorizing php functions. Thank you