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;