MySQL Math Functions

Introduction to MySQL Math Functions

In MySQL, we can find several built-in command functions that include functions for string, date, numeric, and also other advanced type of MySQL functions. MySQL Math Functions are the MySQL built-in functions which refer the numeric type functions and commands to operate the mathematical logics. The Math functions in MySQL are the numeric functions used in the SQL query commands mainly for the mathematical calculations and produce the numeric literals as results. These Math Functions perform numeric handling but if receives an error event during query implementation then, it returns the NULL value as output. With the various MySQL Math Functions, we use arguments for executing different logical operations and displaying the numeric values in 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, returns NULL.

Code:

SELECT ACOS(2);

Output:

3. ASIN() Function

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

Code:

SELECT ASIN(-1);

Output:

4. ATAN() Function

It gives arctangent of numeric value.

Code:

SELECT ATAN(2);

Output:

5. ATAN2() Function

It gives 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 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 bits provided in the expressions passed.

Code:

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

9. CEIL() Function

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

Code:

SELECT CEIL(-2.56);

Output:

10. CEILING() Function

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

Code:

SELECT CEILING(4.37);

Output:

11. CONV() Function

It is helpful to change numeric value from one base to the other one.

Code:

SELECT CONV(4,12,3);

Output:

12. COS() Function

It provides the cosine of specified numeric value 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 natural logarithm(i.e. ‘e’),raised to power of 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 which is rounded to a certain digit of decimal places.

Code:

SELECT FORMAT(582345.654324,2);

Output:

18. GREATEST() Function

It helps to find out 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, if Expr1 is less than Expr3 then the output will be 1 and so on.

Code:

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

Output:

20. LEAST() Function

The LEAST() function is responsible to give 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 remainder of one argument value by dividing by other one provided as stated in the query command.

Code:

SELECT MOD(25,2);

Output:

24. OCT() Function

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

Code:

SELECT OCT(21);

Output:

25. PI() Function

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

Code:

SELECT PI();

Output:

26. POW() Function

This POW() function provides the value of one argument passed that is raised to the power of other 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 Expr2 argument.

Code:

SELECT POWER(3,4);

Output:

28. RADIANS() Function

This Math Function is helpful to 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 which returns output as the sine of given numeric value expressed in radians form.

Code:

SELECT SIN(45);

Output:

31. SQRT() Function

To fetch a non-negative or say 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 and if a numeric argument is passed to it and executed the it returns the same standard deviation type value.

Code:

SELECT STDDEV(Price) Std_DeviationFROM Books;

Output:

34. TAN() Function

TAN() function is responsible to produce the tangent of 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 function will return numeric Expr1 shortened to Expr2 decimal places. But when the argument Expr2 value is 0, then, there will be no decimal point in the result.

Code:

SELECT TRUNCATE(4.129065,3);

Output:

Conclusion

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

This is a guide to MySQL Math Functions. Here we discuss the introduction to MySQL Math Functions and various math functions with respective query examples. You may also have a look at the following articles to learn more –

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