Using functions on data

MySQL comes with a big list of functions to work with all the common data types. In addition to this, it also allows you to create your own functions in SQL, C, or C++. This can help you to filter data based on specific conditions and format it. The following sections will detail some of these functions.

Math functions

These are +, -, and / to add, subtract, and divide. In addition to that, there are also functions such as FLOOR(), CEILING(), POWER(), ROUND(), and quite a few more to help you do calculations on numerical data that you have in the database. Consider the following query:

SELECT 1 + 2, 10 - 11, 1 / 3, POW(2, 3), ROUND(1/3, 1), CEILING(0.9);

This query produces the following results:

Figure 4.11 – The SELECT output with a demonstration of mathematical functions in MySQL

In the figure, we see the following observations:

• 1 + 2 is an addition and will return 3.
• 10 - 11 is a subtraction and returns...