Introduction
In this article, we learn how to use mathematical functions in MySQL. It provides the fast results that we expect and easy calculation. MySQL is the most used database management system supporting various mathematical functions. Mathematical functions in MySQL can be used to perform basic arithmetic operations, trigonometric calculations, logarithmic calculations, and more. The following functions are,
- ABS()
- ACOS()
- ASIN()
- MOD()
- PI()
- POW()
- LOG()
- RAND()
- SQRT()
ABS() Function in MySql
In this type of Function, ABS() returns the absolute value of a given number. The absolute value of a number is its value without considering its sign.
Syntax
SELECT ABS(NUMBER):
The ABS() function can be used with positive and negative numbers. The Function will return the same value if the input value is positive. If the input value is negative, the Function will return the value with the sign inverted.
Example
SELECT ABS(5);
This query returns 5 because the absolute value of 5 is 5.
ACOS() Function in MySql
In this Function, the ACOS() returns the arccosine (inverse cosine) of a given number, which is the angle whose cosine is equal to the input number. Whose range is -1 to 1?
Syntax
SELECT ACOS(NUMBER);
Example
SELECT ACOS(!);
This query will return 0.
ASIN() Function in MySql
In this Function, the ASIN() returns the arcsine (inverse sine) of a given number, which is the angle whose sine is equal to the input number. Whose -1 to 1.
Syntax
SELECT ASIN(NUMBER);
Example
SELECT ASIN(0.5);
This query will return approx 0.5236
MOD() Function in MySql
In this Function, the MOD() returns the remainder of a division operation.
Syntax
SELECT MOD(DIVIDEND, DIVISOR);
Example
SELECT MOD(5,2);
This query will return 1 because when 2 divides 5, then the remainder is 1. So the output is 1.
PI() function in MySql
In this Function, the PI() is used to return the mathematical constant pi (π) value, which is approximately equal to 3.141592653589793. The PI() function does not require any input parameters. It simply returns the value of pi.
Example
SELECT PI();
This query will return approximately 3.141592653589793.
POW function in MySql
In this Function, the POW() returns the result of a number raised to a specified power.
Syntax
SELECT POW(NUMBER,POWER);
Example
SELECT POW(2,3);
This query will return 6.
LOG() function in MySql
the log() function calculates the logarithm of a given number with a specified base. Here, the "base" parameter is the base of the logarithm, and the "number" parameter is the number for which you want to calculate the logarithm.
Syntax
SELECT LOG(BASE,NUMMBER);
Example
SELECT LOG(2,10);
This query will return approximately 3.32193.
RAND() Function in MySql
In MySQL, the RAND() function is used to generate a random number. The RAND() function generates a random number between 0 and 1 when we use it without argument.
Syntax
SELECT RAND();
Example
SELECT FLOOR(RAND() * 10) + 1;
In this query, the FLOOR() function is used to round the multiplication result down to the nearest integer. Adding 1 ensures that the result is at least 1, and the maximum value is 10.
SQRT() Function in MySql
In MySQL, the SQRT() function calculates a given number's square root. The syntax for the SQRT() function is as follows.
Syntax
SELECT SQRT(NUMBER);
Example
SELECT SQRT(25);
In this query will return 5. Because the square root of 25 is 5, so the output of this query is 5.
Conclusion
MySQL provides a wide range of mathematical functions that can be used to perform various calculations and analyses of numerical data. These functions include basic arithmetic operators such as addition, subtraction, multiplication, and division and more advanced functions such as trigonometric, logarithmic, and statistical functions.
Overall, using mathematical functions in MySQL provides a powerful tool for working with numerical data and performing complex calculations and analyses. By leveraging these functions effectively, you can gain valuable insights and make informed decisions based on your data.