MySQL Quick Reference – Functions

Tested on MySQL 5.5

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.


/* SELECT AVG(column_name) FROM table_name */
SELECT AVG(column_name) FROM table_name

/* The COUNT() function returns the number of rows that matches a specified criteria */
SELECT COUNT(column_name) FROM table_name;

/* The COUNT(*) function returns the number of records in a table */
SELECT COUNT(*) FROM table_name;

/* The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column */
SELECT COUNT(DISTINCT column_name) FROM table_name;

/* The FIRST() function returns the first value of the selected column */
/* MySQL not support FIRST() but you can use: */
SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;

/* The LAST() function returns the first value of the selected column */
/* MySQL not support LAST() but you can use: */
SELECT column_name FROM table_name
ORDER BY column_name DESC
LIMIT 1;

/* The MAX() function returns the largest value of the selected column */
SELECT MAX(column_name) FROM table_name;

/* The MIN() function returns the smallest value of the selected column */
SELECT MIN(column_name) FROM table_name;

/* The SUM() function returns the total sum of a numeric column */
SELECT SUM(column_name) FROM table_name;

/* The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns */
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

/* The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions */
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.


/* The UCASE() function converts the value of a field to uppercase */
SELECT UCASE(column_name) FROM table_name;

/* The LCASE() function converts the value of a field to lowercase */
SELECT LCASE(column_name) FROM table_name;

/* The MID() function is used to extract characters from a text field */
SELECT MID(column_name,start[,length]) AS some_name FROM table_name;

/* The LEN() function returns the length of the value in a text field */
SELECT LEN(column_name) FROM table_name;

/* The FORMAT() function is used to format how a field is to be displayed */
SELECT FORMAT(column_name,format) FROM table_name;