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
