Common functions include numeric functions, date and time functions, string functions, control flow functions, etc.
| Operator |
Functionality |
| ABS(x) |
Returns the absolute value of x |
| CEIL(x) |
Returns the smallest integer greater than or equal to x |
| FLOOR(x) |
Returns the largest integer less than or equal to x |
| MOD(x,y) |
Returns the remainder of x divided by y |
| RAND() |
Returns a random value between 0 and 1 |
| ROUND(x,y) |
Returns the value of parameter x rounded to y decimal places |
| TRUNCATE(x,y) |
Truncates number x to y decimal places |
Date and Time Functions
| Operator |
Functionality |
| CURDATE() |
Returns the current date |
| CURTIME() |
Returns the current time |
| NOW() |
Returns the current date and time |
| UNIX_TIMESTAMP(date) |
Returns the UNIX timestamp for date |
| FROM_UNIXTIME |
Returns the date value corresponding to a UNIX timestamp |
| WEEK(date) |
Returns the week number of year containing date |
| YEAR(date) |
Returns the year component of date |
| HOUR(time) |
Returns the hour component of time |
| MINUTE(time) |
Returns the minute component of time |
| MONTHNAME(date) |
Returns the month name for date |
| DATE_FORMAT(date,fmt) |
Returns a formatted value based on string fmt and date |
| DATE_ADD(date,INTERVAL expr type) |
Adds an interval to a date or datetime |
| DATEDIFF(expr,expr2) |
Returns the number of days between start time expr and end time expr2 |
String Functions
| Operator |
Functionality |
| CONCAT(S1,S2,...Sn) |
Concatenates S1, S2, ... Sn into a single string |
| INSERT(str,x,y,instr) |
Replaces the substring of str starting at position x with length y and containing instr characters |
| LOWER(str) |
Converts all characters in string str to lowercase |
| UPPER(str) |
Converts all characters in string str to uppercase |
| LEFT(str,x) |
Returns the leftmost x characters from string str |
| RIGHT(str,x) |
Returns the rightmost x characters from string str |
| LPAD(str,n,pad) |
Pads the left side of str with pad until it reaches length n |
| RPAD(str,n,pad) |
Pads the right side of str with pad until it reaches length n |
| LTRIM(str) |
Removes leading whitespace from string str |
| RTRIM(str) |
Removes trailing whitespace from string str |
| REPEAT(str,x) |
Returns a result where str is repeated x times |
| REPLACE(str,a,b) |
Replaces all occurrences of string a in string str with string b |
| STRCMP(s1,s2) |
Compares strings s1 and s2 |
| TRIM(str) |
Removes leading and trailing whitespace from the line |
| SUBSTRING(str,x,y) |
Returns a substring starting at position x with length y |
Control Flow Functions
| Operator |
Functionality |
| IF(value,t f) |
If value is true, returns t; otherwise returns f |
| IFNULL(value1, value2) |
Returns value1 if it is not null; otherwise returns value2 |
| CASE WHEN[value] THEN[result] ... ELSE[default] END |
If value is true, returns result; otherwise returns default |
| CASE[expr] WHEN[value1] THEN[result1]...ELSE[default]END |
Returns result if expr equals value; otherwise returns default |
Other Functions
| Operator |
Functionality |
| DATABASE() |
Returns the name of the current database |
| VERSION() |
Returns the version number of the current MySQL server |
| USER() |
Returns the username currently logged in |
| INET_ATON(IP) |
Converts an IP address to its numeric representation |
| INET_NTOA(num) |
Converts a numeric value back to its corresponding IP address |
| PASSWORD(str) |
Returns the encrypted version of string str |
| MD5(str) |
Returns the MD5 hash value for string str |