Question

In this part of the MySQL tutorial, we will cover MySQL built-in functions.

MySQL built-in functions can be categorised into several groups.

• Mathematical functions
• Aggregate functions
• String functions
• Date and time functions
• System Functions

Here we show only a portion of all MySQL functions. To get the full list of available functions, consult the MySQL reference manual.

Mathematical functions

MySQL supports multiple mathematical functions.

mysql> SELECT RAND();
+-------------------+
| RAND()      |
+-------------------+
| 0.786536605829873 |
+-------------------+

The RAND() function returns a random number from the <0, 1> interval.

mysql> SELECT ABS(-3), PI(), SIN(0.5);
+---------+----------+-------------------+
| ABS(-3) | PI()   | SIN(0.5)      |
+---------+----------+-------------------+
|     3 | 3.141593 | 0.479425538604203 |
+---------+----------+-------------------+

The ABS() function returns the absolute value of a number. The PI() function gives the value of PI. And the SIN() function computes the sine of an argument.

mysql> SELECT BIN(22), OCT(22), HEX(22);
+---------+---------+---------+
| BIN(22) | OCT(22) | HEX(22) |
+---------+---------+---------+
| 10110   | 26    | 16    |
+---------+---------+---------+

We use functions to give binary, octal and hexadecimal representation of decimal 22.

mysql> SELECT CEIL(11.256), FLOOR(11.256), ROUND(11.256, 2);
+--------------+---------------+------------------+
| CEIL(11.256) | FLOOR(11.256) | ROUND(11.256, 2) |
+--------------+---------------+------------------+
|       12 |      11 |      11.26 |
+--------------+---------------+------------------+

The CEIL() function rounds the value to the smallest following integer. The FLOOR() function rounds the value to the largest previous integer. The ROUND() returns a number rounded to a specified number of decimal places.

mysql> SELECT POW(3, 3), SQRT(9);
+-----------+---------+
| POW(3, 3) | SQRT(9) |
+-----------+---------+
|    27 |     3 |
+-----------+---------+

The power and the square root functions.

mysql> SELECT DEGREES(2*PI());
+-----------------+
| DEGREES(2*PI()) |
+-----------------+
|       360 |
+-----------------+

The DEGREES() function computes degrees from radians.

Aggregate functions

Aggregate functions operate on sets of values.

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name     | Cost   |
+----+------------+--------+
|  1 | Audi     |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda    |   9000 |
|  4 | Volvo    |  29000 |
|  5 | Bentley  | 350000 |
|  6 | Citroen  |  21000 |
|  7 | Hummer   |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

We have the Cars table.

mysql> SELECT MIN(Cost), MAX(Cost), AVG(Cost)
  -> FROM Cars;
+-----------+-----------+------------+
| MIN(Cost) | MAX(Cost) | AVG(Cost)  |
+-----------+-----------+------------+
|    9000 |  350000 | 72721.1250 |
+-----------+-----------+------------+

We use the MIN() , MAX() and AVG() aggregate functions to compute the minimal price, maximal price and the average price of cars in the table.

mysql> SELECT SUM(Cost), COUNT(Id), STD(Cost), 
  -> VARIANCE(Cost) FROM Cars;
+-----------+-----------+-------------+------------------+
| SUM(Cost) | COUNT(Id) | STD(Cost)   | VARIANCE(Cost)   |
+-----------+-----------+-------------+------------------+
|  581769 |     8 | 105931.1676 | 11221412265.3594 |
+-----------+-----------+-------------+------------------+

We use the SUM() function to get the sum of all values in the Cost column. We count the number of cars in the table with the COUNT() function. Finally, we get the standard deviation and variance using the STD() and VARIANCE() functions.

String functions

In this group we have various strings related functions.

mysql> SELECT LENGTH('ZetCode'), UPPER('ZetCode'), LOWER('ZetCode');
+-------------------+------------------+------------------+
| LENGTH('ZetCode') | UPPER('ZetCode') | LOWER('ZetCode') |
+-------------------+------------------+------------------+
|         7 | ZETCODE      | zetcode      |
+-------------------+------------------+------------------+

The LENGTH() function returns the length of a string. The UPPER() function converts characters into upper-case letters. The LOWER() function converts characters into lower-case letters.

ysql> SELECT LPAD(RPAD("ZetCode", 10, "*"), 13, "*");
+-----------------------------------------+
| LPAD(RPAD("ZetCode", 10, "*"), 13, "*") |
+-----------------------------------------+
| ***ZetCode***               |
+-----------------------------------------+

We use the LPAD() and RPAD() functions to append and prepend characters to a specified string. The "ZetCode" string has 7 characters. The RPAD() function appends 3 '*' characters to the string, which will be now 10 characters long.

mysql> SELECT REVERSE('ZetCode'), REPEAT('*', 6);
+--------------------+----------------+
| REVERSE('ZetCode') | REPEAT('*', 6) |
+--------------------+----------------+
| edoCteZ      | ******     |
+--------------------+----------------+

The REVERSE() function reverses the characters in a string. The REPEAT() function repeats a string specified number of times.

mysql> SELECT LEFT('ZetCode', 3), RIGHT('ZetCode', 3), 
  -> SUBSTRING('ZetCode', 3, 3);
+--------------------+---------------------+----------------------------+
| LEFT('ZetCode', 3) | RIGHT('ZetCode', 3) | SUBSTRING('ZetCode', 3, 3) |
+--------------------+---------------------+----------------------------+
| Zet        | ode         | tCo            |
+--------------------+---------------------+----------------------------+

The LEFT() function returns 3 leftmost characters, the RIGHT() function returns 3 characters from the right. The SUBSTRING() function returns three characters from the third position of the string.

mysql> SELECT STRCMP('byte', 'byte'), CONCAT('three', ' apples');
+------------------------+----------------------------+
| STRCMP('byte', 'byte') | CONCAT('three', ' apples') |
+------------------------+----------------------------+
|            0 | three apples         |
+------------------------+----------------------------+

The STRCMP() compares two strings and returns 0 if they are the same. The CONCAT() function concatenates two strings.

mysql> SELECT REPLACE('basketball', 'basket', 'foot');
+-----------------------------------------+
| REPLACE('basketball', 'basket', 'foot') |
+-----------------------------------------+
| football                |
+-----------------------------------------+

The REPLACE() function returns a string, in which we have replaced some text. The first parameter is the original string. The second parameter is a string, we want to replace. And the last parameter is the new replacing string.

Date & time functions

In this group we have various date and time functions.

mysql> SELECT DAYNAME('2011-01-23'), YEAR('2011/01/23'),
  -> MONTHNAME('110123');
+-----------------------+--------------------+---------------------+
| DAYNAME('2011-01-23') | YEAR('2011/01/23') | MONTHNAME('110123') |
+-----------------------+--------------------+---------------------+
| Sunday        |         2011 | January       |
+-----------------------+--------------------+---------------------+

In MySQL, date is written in the format YYYY-MM-DD . Year is followed by month and day. They can be separated by slash or by hyphen. MySQL also supports a shortened date format, without separators. Time is written in a standard form, HH:MM:SS . Hours followed by minutes and seconds.

mysql> SELECT NOW();
+---------------------+
| NOW()         |
+---------------------+
| 2011-01-22 00:24:49 |
+---------------------+

The NOW() function returns the current date and time.

mysql> SELECT CURTIME(), CURDATE();
+-----------+------------+
| CURTIME() | CURDATE()  |
+-----------+------------+
| 00:25:03  | 2011-01-22 |
+-----------+------------+

The CURTIME() returns the current time and the CURDATE() returns the current date.

mysql> SELECT DATEDIFF('2011-3-12', '2011-1-12');
+------------------------------------+
| DATEDIFF('2011-3-12', '2011-1-12') |
+------------------------------------+
|                 59 |
+------------------------------------+

With the DATEDIFF() we get the number of days between two dates.

mysql> SELECT DAYNAME('1982-4-12'), MONTHNAME('1982-4-12') ;
+----------------------+------------------------+
| DAYNAME('1982-4-12') | MONTHNAME('1982-4-12') |
+----------------------+------------------------+
| Monday         | April          |
+----------------------+------------------------+

The DAYNAME() function returns the day name of a date. The MONTHNAME() function returns a month name of a date.

mysql> SELECT WEEKOFYEAR('110123'), WEEKDAY('110123'),
  -> QUARTER('110123');
+----------------------+-------------------+-------------------+
| WEEKOFYEAR('110123') | WEEKDAY('110123') | QUARTER('110123') |
+----------------------+-------------------+-------------------+
|          3 |         6 |         1 |
+----------------------+-------------------+-------------------+

January 23, 2011 can be written in a shortened date format, 110123. We use the WEEKOFYEAR() to find out the week of the year. The WEEKDAY() returns 6, which is Sunday. And the QUARTER() function returns the quarter of the year.

mysql> SELECT DATE_FORMAT('110123', '%d-%m-%Y');
+-----------------------------------+
| DATE_FORMAT('110123', '%d-%m-%Y') |
+-----------------------------------+
| 23-01-2011            |
+-----------------------------------+

To display date in a different format, we use the DATE_FORMAT() .

mysql> SELECT DATE_ADD('110123', INTERVAL 45 DAY), 
  -> SUBDATE('110309', INTERVAL 45 DAY);
+-------------------------------------+------------------------------------+
| DATE_ADD('110123', INTERVAL 45 DAY) | SUBDATE('110309', INTERVAL 45 DAY) |
+-------------------------------------+------------------------------------+
| 2011-03-09              | 2011-01-23             |
+-------------------------------------+------------------------------------+

We can use DATE_ADD() to add time intervals to a date and SUBDATE() to subtract time intervals from a date.

System functions

System functions provide some system information about MySQL database.

mysql> SELECT VERSION(), DATABASE();
+--------------------+------------+
| VERSION()      | DATABASE() |
+--------------------+------------+
| 5.1.41-3ubuntu12.8 | mydb     |
+--------------------+------------+

We get the version of the MySQL database and the current database name.

mysql> SELECT USER();
+----------------+
| USER()     |
+----------------+
| root@localhost |
+----------------+

The USER() function returns the user name and the host name provided by the client.

mysql> SELECT CHARSET('ZetCode'), COLLATION('ZetCode');
+--------------------+----------------------+
| CHARSET('ZetCode') | COLLATION('ZetCode') |
+--------------------+----------------------+
| utf8         | utf8_general_ci    |
+--------------------+----------------------+

The CHARSET() function returns the character set of the argument. The COLLATION() returns the collation of the current string argument. They depend on the charset and collation of the client in use.

In this part of the MySQL tutorial, we worked with the built-in MySQL functions.