MySQL Math Functions

Updated May 25, 2023

Introduction to MySQL Math Functions

In MySQL, we can find several built-in command functions that include functions for string, date, numeric, and also another advanced types of MySQL functions. MySQL Math Functions are the MySQL built-in functions that refer to the numeric type functions and commands to operate the mathematical logic. The Math functions in MySQL are the numeric functions used in the SQL query commands mainly for the mathematical calculations and producing the numeric literals as results. These Math Functions perform numeric handling, but if it receives an error event during query implementation, it returns the NULL value as output. With the various MySQL Math Functions, we use arguments to execute different logical operations and display the numeric values in the MySQL server.

Various MySQL Math Functions with Examples

Given below are the various math functions along with their respective details for references and describing their utilities and function roles for the mathematical operations:

1. ABS() Function

This Math function is useful to return the universal or fixed value of a numeric expression provided as arguments.

Code:

SELECT ABS(4);

Output:

2. ACOS() Function

It returns the arccosine of a numeric value, but if the value is not provided in the range -1 to 1, then it returns NULL.

Code:

SELECT ACOS(2);

Output:

3. ASIN() Function

It gives the arcsine of a numeric value, but it returns NULL if it is not provided in the range -1 to 1.

Code:

SELECT ASIN(-1);

Output:

4. ATAN() Function

It gives an arctangent of numeric value.

Code:

SELECT ATAN(2);

Output:

5. ATAN2() Function

It gives the arctangent of the given two variables.

Code:

SELECT ATAN2(2,4);

Output:

6. BIT_AND() Function

It outputs the bitwise AND all the bits in a given expression.

Code:

SELECT BookName, BIT_AND(Price) BITS FROM Books GROUP BY BookName;

7. BIT_COUNT Function

It displays the string illustration of the specified binary value.

Code:

SELECT BIT_COUNT(3) AS Three, BIT_COUNT(5) AS FIVE;

Output:

8. BIT_OR() Function

It gives the bitwise OR of every bit provided in the expressions passed.

Code:

ELECT BookName, BIT_OR(Price) BITS FROM Books GROUP BY BookName;

9. CEIL() Function

It results in the minimum integer value that is not small than the provided numeric argument.

Code:

SELECT CEIL(-2.56);

Output:

10. CEILING() Function

It results in a minimum integer value that is not smaller than the provided numeric argument.

Code:

SELECT CEILING(4.37);

Output:

11. CONV() Function

Changing the numeric value from one base to the other is helpful.

Code:

SELECT CONV(4,12,3);

Output:

12. COS() Function

It provides the cosine of specified numeric values which need to be in radians.

Code:

SELECT COS(65);

Output:

13. COT() Function

It provides the cotangent of specified numeric value.

Code:

SELECT COT(180);

Output:

14. DEGREES() Function

It returns the values which are transformed from radians to degrees.

Code:

SELECT DEGREES(PI());

Output:

15. EXP() Function

It provides the base of the natural logarithm(i.e., ‘e’), raised to the power of a given argument.

Code:

SELECT EXP(5);

Output:

16. FLOOR() Function

This function gives the greatest integer value, which is not larger than the numeric values passed to it.

Code:

SELECT FLOOR(9.23);

Output:

17. FORMAT() Function

This function returns a numeric value rounded to a certain digit of decimal places.

Code:

SELECT FORMAT(582345.654324,2);

Output:

18. GREATEST() Function

It helps to determine the largest value among the arguments provided as inputs.

Code:

SELECT GREATEST(6,12,34,56,82,100);

Output:

19. INTERVAL() Function

In this function, if we pass multiple arguments such as Expr1, Expr2, Expr3, etc., then when Expr1 is less than Expr2, the function provides 0 as output. Similarly, the output will be one if Expr1 is less than Expr3.

Code:

SELECT INTERVAL(5,2,5,7,8,10);

Output:

20. LEAST() Function

The LEAST() function gives the lowest valued input expression from two or more arguments passed in the function.

Code:

SELECT LEAST(7,13,54,16,89,57);

Output:

21. LOG() Function

This function provides the natural logarithm of the implemented numeric value.

Code:

SELECT LOG(54);

Output:

22. LOG10() Function

This function provides the base-10 logarithm of the implemented numeric value.

Code:

SELECT LOG10(50);

Output:

23. MOD() Function

The MOD() function denotes the result value as the remainder of one argument value by dividing by the other provided in the query command.

Code:

SELECT MOD(25,2);

Output:

24. OCT() Function

This OCT() function in MySQL is useful for returning a string illustration of the given octal value of the implemented numeric expression. But if we specify a NULL value, it returns NULL as output.

Code:

SELECT OCT(21);

Output:

25. PI() Function

This is a math function that helps to provide the value of the pi expression.

Code:

SELECT PI();

Output:

26. POW() Function

This POW() function provides the value of one argument passed raised to the power of another argument with numeric values specified while executing in the server.

Code:

SELECT POW(2,5);

Output:

27. POWER() Function

Suppose this POWER() is passed with two arguments, Expr1 and Expr2. Then, on implementation, it outputs the value of Expr1, which is raised to the power of the Expr2 argument.

Code:

SELECT POWER(3,4);

Output:

28. RADIANS() Function

This Math Function helps produce the result value of implemented expression transformed from degrees to the form of radians.

Code:

SELECT RADIANS(45);

Output:

29. ROUND() Function

This ROUND() function provides a numeric value rounded to an integer or also can be applied to round any numeric expression to a certain digit of decimal points.

Code:

SELECT ROUND(88.604);

Output:

30. SIN() Function

The SIN() function is a math function in MySQL that returns the output as the sine of a given numeric value expressed in radians form.

Code:

SELECT SIN(45);

Output:

31. SQRT() Function

To fetch a non-negative or positive square root for a numeric value, we use this SQRT() math function in MySQL.

Code:

SELECT SQRT(121);

Output:

32. STD() Function

This math function provides the standard deviation value of any specific numeric expression.

Code:

SELECT STD(Price)Std_Deviation FROM Books;

Output:

33. STDDEV() Function

This is a standard deviation function as of the mathematical term. If a numeric argument is passed to it and executed, it returns the same standard deviation type value.

Code:

SELECT STDDEV(Price) Std_DeviationFROM Books;

Output:

34. TAN() Function

TAN() function produces the tangent of a given numeric value or expression represented in radians when executed in MySQL.

Code:

SELECT TAN(60);

Output:

35. TRUNCATE() Function

Suppose the TRUNCATE Math function takes two arguments, Expr1 and Expr2; then, on implementation, the process will return numeric Expr1 shortened to Expr2 decimal places. But when the argument Expr2 value is 0, there will be no decimal point in the result.

Code:

SELECT TRUNCATE(4.129065,3);

Output:

Conclusion

The Math functions return the non-relative value for the given variable arguments performing the numeric query operations. For these built-in numeric functions, we must consider arguments such as values from integer and float types or decimal values to conduct the query and execute the result of the Math function.

We hope that this EDUCBA information on “MySQL Math Functions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. Natural Join in MySQL
2. MySQL Index
3. Examples of MySQL ROW_NUMBER()
4. MySQL Root Password