1. Introduction
MySQL is a popular open-source relational database management system that is widely used in web applications. In MySQL, CEILING(), FLOOR() and ROUND() are three of the most commonly used mathematical functions. These functions help database developers to manipulate numerical data in their queries. In this article, we will discuss the differences between these three functions in detail.
2. CEILING() Function
2.1 Definition
The CEILING() function is used to round the input value up to its nearest integer or specified decimal place. The returned value will always be greater than or equal to the input value.
2.2 Syntax
CEILING(number)2.3 Example
Suppose we have a table named "products" that contains the following data:
+----+-------+
| id | price |
+----+-------+
| 1 | 15.99 |
| 2 | 39.95 |
| 3 | 9.99 |
+----+-------+
If we want to round up all the prices to their nearest integer, we can use the CEILING() function as follows:
SELECT CEILING(price) FROM products;The output will be:
+--------------+
| CEILING(price) |
+--------------+
| 16 |
| 40 |
| 10 |
+--------------+
3. FLOOR() Function
3.1 Definition
The FLOOR() function is used to round the input value down to its nearest integer or specified decimal place. The returned value will always be less than or equal to the input value.
3.2 Syntax
FLOOR(number)3.3 Example
Suppose we have a table named "orders" that contains the following data:
+----+--------+
| id | amount |
+----+--------+
| 1 | 15.99 |
| 2 | 39.95 |
| 3 | 9.99 |
+----+--------+
If we want to round down all the order amounts to their nearest integer, we can use the FLOOR() function as follows:
SELECT FLOOR(amount) FROM orders;The output will be:
+-------------+
| FLOOR(amount) |
+-------------+
| 15 |
| 39 |
| 9 |
+-------------+
4. ROUND() Function
4.1 Definition
The ROUND() function is used to round the input value to its nearest integer or specified decimal place. If the input value is exactly halfway between two possible rounded values, the function will choose the value that is closer to the even number.
4.2 Syntax
ROUND(number,[decimal_places])4.3 Example
Suppose we have a table named "students" that contains the following data:
+----+---------+
| id | mark |
+----+---------+
| 1 | 79.67 |
| 2 | 89.32 |
| 3 | 93.45 |
+----+---------+
If we want to round all the marks to two decimal places, we can use the ROUND() function as follows:
SELECT ROUND(mark,2) FROM students;The output will be:
+---------------+
| ROUND(mark,2) |
+---------------+
| 79.67 |
| 89.32 |
| 93.45 |
+---------------+
If we want to round all the marks to the nearest integer, we can use the ROUND() function as follows:
SELECT ROUND(mark) FROM students;The output will be:
+--------------+
| ROUND(mark) |
+--------------+
| 80 |
| 89 |
| 93 |
+--------------+
5. Conclusion
In conclusion, CEILING(), FLOOR() and ROUND() functions are used to round numerical values in MySQL. CEILING() rounds the input value up to its nearest integer or decimal place, FLOOR() rounds it down to its nearest integer or decimal place, and ROUND() rounds it to the nearest integer or decimal place. Understanding these functions and their differences is important for database developers and analysts who work with numerical data in MySQL queries.