SQL Functions

These functions can be used in your SQL statements.

Legend

• [ x ] - Optional (one or none)
• [, x ... ] - Optional additional values, in a comma-separated list
• x | y - Choose one. Curly braces are added when needed: { x | y } ...
• CAPITALIZED - SQL Keyword (case insensitive)
• italicized - Value you supply. (Table names and column names are case-sensitive)

Returning Function Values in a SELECT Statement

When returning a function value in a SQL SELECT Statement, :

• It's necessary to specify a table to select from.
• It's generally necessary to include an AS clause, so the value has a handle.
• It's a good idea to include a LIMIT clause, as well

SELECT CURDATE() AS Date FROM Customer

- returns the current date, under the heading "Date", once for each record in the Customer object.

SELECT CURDATE() AS Date FROM Customer LIMIT 1

- returns the current date, under the heading "Date", one time.

Logical Functions

IF(expr1,expr2,expr3)	If expr1 is not zero and not null, return expr2. Otherwise, return expr3.
IFNULL(expr1,expr2)	If expr1 is not NULL, return expr1; Otherwise, return expr2.

Learn more: MySQL Control Flow Functions

String Functions

CONCAT(str1,str2,...)	Concatenates the strings. Returns the result.
LENGTH(str)	Returns the length of the string, in bytes. (A mult-byte character counts as multiple bytes.)

Learn more: MySQL String Functions

Mathematical Functions

ROUND(x)	Round x up or down to the nearest integer value.
ROUND(x, d)	Round xto d decimal places.

Learn more: MySQL Mathematical Functions

Date and Time Functions

CURDATE()	eturns current date as a string or number, depending on current context. String format: 'YYYY-MM-DD'. Numeric format: YYYYMMDD.
CURTIME()	Returns current time as a string or number, depending on current context. String format: 'HH:MM:SS'. Numeric format: HHMMSS.uuuuuu, where uuuuuu is the number of microseconds.
DATE_ADD(date,   INTERVAL expr unit) DATE_SUB(date,   INTERVAL expr unit)	Add expr units to a time value (or subtract from it). Where: expr can be a negative number, and where: unit Expression Type Expression Format MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEARS Number An integer specifying the number of units. SECOND_MICROSECOND String 'SECONDS.MICROSECONDS' MINUTE_MICROSECOND String 'MINUTES:SECONDS.MICROSECONDS' MINUTE_SECOND String 'MINUTES:SECONDS' HOUR_MICROSECOND String 'HOURS:MINUTES:SECONDS.MICROSECONDS' HOUR_SECOND String 'HOURS:MINUTES:SECONDS' HOUR_MINUTE String 'HOURS:MINUTES' DAY_MICROSECOND String 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' DAY_SECOND String 'DAYS HOURS:MINUTES:SECONDS' DAY_MINUTE String 'DAYS HOURS:MINUTES' DAY_HOUR String 'DAYS HOURS' YEAR_MONTH String 'YEARS-MONTHS'
DATE
DATE_FORMAT
DATEDIFF
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_UNIXTIME
HOUR
LAST_DAY
MINUTE
MONTH
MONTHNAME
NOW
SEC_TO_TIME
SECOND
STR_TO_DATE
SUBTIME
TIME
TIME_TO_SEC
TIMEDIFF
TO_DAYS
UNIX_TIMESTAMP
YEAR

Learn more: MySQL Date and Time Functions

Aggregate Functions

SUM(expr) SUM(DISTINCT expr)	Returns the sum of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values.
AVG(expr) AVG(DISTINCT expr)	Returns the average of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values.
COUNT(expr)	Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement Returns 0 if there were no matching rows.
COUNT(*)	Returns a count of rows, whether or not they contain NULL values.
COUNT(DISTINCT   expr [, expr ...])	Returns a count of rows with different non-NULL expr values.
MAX(expr) MAX(DISTINCT expr)	Returns the maximum of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values.
MIN(expr) MIN(DISTINCT expr)	Returns the maximum of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values.
STD(expr)	Returns the standard deviation of expr.
VARIANCE(expr)	Returns the standard variance of expr.

Learn more: MySQL Aggregate Functions