SQL has a lot of built-in functions for counting and calculations.
The syntax for built-in SQL functions is:
|
SELECT function(columnName) FROM tableName; |
There are several basic types and categories of functions in SQL. The basic types of functions are:
Aggregate Functions
Scalar functions
Aggregate functions operate against a collection of values, but return a single value.
Note: If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause!!
The "Books" table:
|
Title |
Author |
Publisher |
Year |
Price |
|
الدورة الدموية |
إبن النفيس |
دار العلم |
1650 |
230.50 |
|
Java 2 |
L. Johnston |
Fast Press |
2002 |
120.99 |
|
Linux and Unix |
J. Sam |
Fast Press |
2004 |
210.99 |
|
Operating Systems |
M. Stone |
Coriolis |
2005 |
167.00 |
|
Web Programming |
K. Yariv |
East Edition |
2005 |
140.60 |
|
XML Language |
M. Salim |
Knowledge Press |
2000 |
90.45 |
|
Function |
Description |
Example |
| AVG(columnName) | Returns the average value of columnName |
SELECT AVG(Price) FROM Books; |
| COUNT(columnName) | Returns the number of rows having non-NULL values of columnName | SELECT COUNT(Publisher) FROM Books; |
| COUNT(*) | Returns the number of rows | SELECT COUNT(*) FROM Books; |
| COUNT(DISTINCT columnName) | Returns the number of rows having distinct non-NULL values of columnName | SELECT COUNT(DISTINCT Publisher) FROM Books; |
| MAX(columnName) | Returns the highest value of columnName | SELECT MAX(Price) FROM Books; |
| MIN(columnName) | Returns the lowest value of a columnName | SELECT MIN(Price) FROM Books; |
| SUM(columnName) | Returns the total sum of columnName | SELECT SUM(Price) FROM Books; |
| VARIANCE(columnName) | Returns the standard variance of columnName | SELECT VARIANCE(Price) FROM Books; |
| STD(columnName) | Returns the standard deviation of columnName (the square root of VARIANCE()) | SELECT STD(Price) FROM Books; |
| STDDEV(columnName) | Is the same as STD(columnName) | SELECT STDDEV(Price) FROM Books; |
Scalar functions operate against a single value, and return a single value based on the input value.
|
Function |
Description |
Example |
| ASCII(str) | Returns the ASCII code value of the leftmost character of the string str | SELECT ASCII('Morocco'); |
| CONCAT(str1, str2, ...) | Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL | SELECT CONCAT('I love ', 'Morocco'); |
| CONCAT_WS(separator, str1, str2, ...) | CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments | SELECT CONCAT_WS(' ', 'I love', 'Morocco'); |
| LOCATE(substr, str) | Returns the position of the first occurrence of substring substr in string str | SELECT LOCATE('ro', 'Morocco'); |
| LOCATE(substr, str, pos) | Returns the position of the first occurrence of substring substr in string str, starting at position pos | SELECT LOCATE('o', 'Morocco', 5); |
| SUBSTRING(str, pos) | Returns a substring from string str starting at position pos | SELECT SUBSTRING('Morocco', 3); |
| SUBSTRING(str, pos, len) | Returns a substring len characters long from string str, starting at position pos | SELECT SUBSTRING('Morocco', 3, 3); |
| TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) | Returns the string str with all remstr prefixes and/or suffixes removed. If none of the specifiers BOTH, LEADING or TRAILING are given, BOTH is assumed. If remstr is not specified, spaces are removed |
SELECT
TRIM(TRAILING
'cco'
FROM 'Morocco'); SELECT TRIM(BOTH 'w' FROM 'wwwEqualswww'); |
| REPLACE(str, from_str, to_str) | Returns the string str with all occurrences of the string from_str replaced by the string to_str | SELECT REPLACE('zone', 'z', 'd'); |
| REPEAT(str, count) | Returns a string consisting of the string str repeated count times | SELECT REPEAT('Morocco', 10); |
| REVERSE(str) | Returns the string str with the order of the characters reversed | SELECT REVERSE('Morocco'); |
| INSERT(str, pos, len, newstr) | Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr | SELECT INSERT('Come here', 4, 6, 'puter.'); |
|
LCASE(str) LOWER(str) |
Returns the string str with all characters changed to lowercase according to the current character set mapping (the default is ISO-8859-1 Latin1) | SELECT LCASE('Morocco'); |
|
UCASE(str) UPPER(str) |
Returns the string str with all characters changed to uppercase according to the current character set mapping (the default is ISO-8859-1 Latin1) | SELECT UCASE('Morocco'); |
| LENGTH(str) | Returns the length of str | SELECT LENGTH('Morocco'); |
| NOW() | Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context |
SELECT
NOW(); SELECT NOW()+0; |
| CURDATE() | Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context |
SELECT
CURDATE(); SELECT CURDATE()+0; |
| CURTIME() | Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context |
SELECT
CURTIME(); SELECT CURTIME()+0; |
|
Function |
Description |
Example |
|
DATABASE() |
Returns the current database name | SELECT DATABASE(); |
|
USER() |
Returns the current MySQL username and hostname | SELECT USER(); |
|
SYSTEM_USER() |
Is the same as USER() | SELECT SYSTEM_USER(); |
|
SESSION_USER() |
Is the same as USER() |
SELECT SESSION_USER(); |
|
CURRENT_USER() |
Returns the username and hostname that the current session was authenticated as. This value corresponds to the account that is used for assessing your access privileges. It may be different than the value of USER() |
SELECT CURRENT_USER(); |
|
PASSWORD(str) |
Calculates a password string from the plaintext password str |
SELECT PASSWORD('Morocco'); |
|
OLD_PASSWORD(str) |
Calculates a password string from the plaintext password str. It is different than the value of PASSWORD(str) |
SELECT OLD_PASSWORD('Morocco'); |
|
ENCODE(str, pass_str) |
Encrypt str using pass_str as the password. To decrypt the result, use DECODE(). The results is a binary string of the same length as string. If you want to save it in a column, use a BLOB column type |
SELECT ENCODE('Morocco', 'password'); |
|
DECODE(crypt_str, pass_str) |
Descrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE() |
SELECT DECODE(ENCODE('Morocco', 'password'), 'password'); |
|
MD5(str) |
Calculates an MD5 128-bit checksum for str. The value is returned as a 32-digit hex number |
SELECT MD5('Morocco'); |
|
COMPRESS(str) |
Compresses str |
SELECT COMPRESS('Morocco'); |
|
UNCOMPRESS(str) |
Uncompresses a string compressed by the COMPRESS() function |
SELECT UNCOMPRESS(COMPRESS('Morocco')); |
|
FORMAT(X, D) |
Formats the number X to a format like '#,###,###.##', rounded to D decimals, and returns the result as a string. If D is 0, the result will have no decimal point or fractional part |
SELECT FORMAT(123456.123456, 2); |
|
VERSION() |
Returns a string indicating the MySQL server version |
SELECT VERSION(); |
To test your SQL skills click here.