Mysql built-in functions

Mysql built-in functions

Online by JSC0d3
June 16, 2014 | | 1536 recognitions

MySQL comes with more than 100 built-in functions to help perform calculations and process the records in a result set. These functions can be used in a SELECT statement, either to manipulate field values or in the WHERE clause. The following example illustrates by using MySQL’s COUNT() function to return the total number of airport records:

Mysql built-in functions

You can calculate string length with the LENGTH() function, as in the following:

mysql> SELECT DISTINCT CityName, LENGTH(CityName)
> FROM airport LIMIT 0,5;
+-----------+------------------+
| CityName | LENGTH(CityName)  |
+-----------+------------------+
| Paris | 5 |
| London | 6 |
| Rome | 4 |
| Amsterdam | 9 |
| Barcelona | 9 |
+-----------+------------------+
5 rows in set (0.00 sec)

You can use the DATE() function to format date and time values into a human readable form, as illustrated in the following:

mysql> SELECT DATE_FORMAT(NOW(), '%W %d %M %Y');
+-----------------------------------+
| DATE_FORMAT(NOW(), '%W %d %M %Y') |
+-----------------------------------+
| Thursday 12 June 2014 |
+-----------------------------------+
1 row in set (0.03 sec)

Grouping Records

To group records on the basis of a specific field, use MySQL’s GROUP BY clause. Each group created in this manner is treated as a single row, even though it internally contains multiple records. The COUNT() function can be used in this context to count the number of records in each group. Consider the following example, which groups and counts airports by country:

mysql> SELECT CountryCode, COUNT(AirportID) AS NumAirports
-> FROM airport GROUP BY CountryCode;
+-------------+-------------+
| CountryCode | NumAirports |
+-------------+-------------+
| CH | 1 |
| DE | 1 |
| ES | 2 |
| FR | 2 |
+-------------+-------------+
4 rows in set (0.02 sec)

To further filter the groups, add a HAVING clause to the GROUP BY clause. This HAVING clause works much like a regular WHERE clause, making it possible to filter the grouped data by a specific condition. The following example revises the previous one to only return those countries having two or more airports:

mysql> SELECT CountryCode, COUNT(AirportID) AS NumAirports
-> FROM airport GROUP BY CountryCode
-> HAVING NumAirports >= 2;
+-------------+-------------+
| CountryCode | NumAirports |
+-------------+-------------+
| ES | 2 |
| FR | 2 |
| UK | 3 |
+-------------+-------------+
3 rows in set (0.00 sec)

Using Variables

MySQL supports user-defined variables, which come in handy when you need to pass values from one SQL statement to another. These variables are session variables—they remain extant for the duration of the client session, and are automatically destroyed once the client disconnects—and are defined using the SET statement. Note that variable names are case-insensitive and must be prefixed with the @ symbol. Here’s an example:

Mysql built-in functions

Another way to define a variable is to write the result of a SELECT statement into it using the SELECT INTO statement. Here’s an example, which finds the airport with the maximum number of inward routes, stores the airport identifier into the @aid variable, and then uses the variable to retrieve the airport name:

mysql> SELECT `to` INTO @aid
-> FROM route
-> GROUP BY `to`
-> ORDER BY COUNT(`to`)
-> DESC LIMIT 1;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT AirportName
-> FROM airport
-> WHERE AirportID = @aid;
+------------------+
| AirportName |
+------------------+
| Heathrow Airport |
+------------------+
1 row in set (0.09 sec)
JSC0d3's Logo
About JSC0d3

JSC0d3 is an entrepreneur, online marketer, and an employee of an IT company. When not building websites, creating content, or helping customers improve their online business, spend time with their wife and two beautiful children. Although he still feels new in WordPress, he enjoys sharing what he has learned with all of you! If you want to get in touch with him, you can do this through this website.

On the same idea

Posted by | June 22, 2014

The purpose of nomalization is to reduce redundancy in a database and also to make it more flexible Database normalization is a database schema...

Posted by | June 16, 2014

MySQL select random records using RAND function
MySQL does not have any built-in statement to select random records from a database table In order to...

Posted by | June 16, 2014

MySQL also comes with a full-featured list of SHOW statements to obtain information about all aspects of the server, its databases, and its tables...

Previous PostBackNext Post

Leave here an impression