MySQL Common Functions Summary

  Function, mysql



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: …

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