MySQL provides a variety of math functions that allow users to perform mathematical operations on numeric data stored in a database. These functions can be useful for performing calculations, aggregations, and transformations on numerical values within SQL queries. Here are some commonly used MySQL math functions:
ABS: This function returns the absolute value of a numeric expression. For example:
SELECT ABS(-10); -- Result: 10
CEIL: This function return the smallest integer greater than or equal to a given numeric expression.
SELECT CEIL(5.3); -- Result: 6
FLOOR: This function returns the largest integer less than or equal to a given numeric expression.
SELECT FLOOR(5.8); -- Result: 5
ROUND: This function rounds a numeric expression to the nearest integer or to the specified number of decimal places.
SELECT ROUND(5.75); -- Result: 6 SELECT ROUND(5.754, 2); -- Result: 5.75
MOD: This function returns the remainder of a division operation.
SELECT MOD(10, 3); -- Result: 1 (because 10 divided by 3 is 3 with a remainder of 1)
POWER: Power function raise a number to the power of another.
SELECT POWER(4, 0.5); -- Result: 2 (square root of 4)
SQRT: This function returns the square root of a numeric expression.
SELECT SQRT(25); -- Result: 5
RAND: This function generates a random floating-point value between 0 and 1.
SELECT RAND(); -- Result: Random value between 0 and 1
TRUNCATE(): Truncates a number, removing decimal places, without rounding.
These functions can be combined and used in various ways within SQL queries to perform complex mathematical calculations and manipulations on data stored in MySQL databases.