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.
Recommended Articles
We hope that this EDUCBA information on “MySQL Math Functions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
