MySQL provides various built-in Mathematical or Numeric and trigonometric functions to perform different operations on numerical data. In this study, we show you the list of MySQL Numeric and Trigonometric functions with an example of each one of them.
MySQL Numeric Functions
The following is the list of MySQL Mathematical and Trigonometric functions that are available to work with numerical data.
| Functions | Description |
|---|---|
| ABS() | This returns the absolute positive value |
| ACOS() | This returns the Arc cosine value. |
| ASIN() | This returns the Arc Sine |
| ATAN() | It returns the Arc Tangent value. |
| ATAN2() | Return the Arctangent value of two arguments. |
| CEIL() | It returns the smallest integer value, which is not less than the given value. |
| CEILING() | Return the smallest integer not less than a given value. |
| CONV() | Convert the given number between different base numbers. |
| COS() | This MySQL Numeric function returns the Cosine of a given value. |
| COT() | Returns the Cotangent value |
| CRC32() | Cyclic redundancy check value. |
| DEGREES() | It converts the radians value to Degrees |
| EXP() | Raise the power of E |
| FLOOR() | It returns the largest integer value, which is not greater than the given value. |
| LN() | Synonym for the MySQL LOG. |
| LOG() | Returns the natural logarithmic value. |
| LOG10() | This MySQL Numeric function returns the logarithmic base 10 value of the given argument. |
| LOG2() | Returns the logarithmic base 2 value |
| MOD() | Returns the remainder. |
| PI | Use this to get PI (3.14) |
| POW() | It returns the argument raised to the specified power. |
| POWER() | Finds the given value raised to the specified power. |
| RADIANS() | It converts the Degrees value to radians |
| RAND() | It retunes the random float. |
| ROUND() | This function rounds the given one. |
| SIGN() | It returns the sign (positive, negative) of the argument |
| SIN() | Returns the Sine Value of the given argument. |
| SQRT() | Finds the square root of a given one. |
| TAN() | Returns the Tangent Value |
| TRUNCATE() | It truncates the given value to user-specified decimal places. |
MySQL Numeric Functions Examples
The following examples help you to understand these mathematical functions in MySQL.
MySQL Trigonometric Functions Example
In this example, we are going to use the ABS to find the absolute positive value. Next, we used the ACOS and ASIN methods to find the Arc cosine and Arc Sine values.
SELECT ABS(10), ABS(-32), ABS(-44), ABS(-23.32);
SELECT ACOS(1), ACOS(0), ACOS(0.5), ACOS(0.75);
SELECT ASIN(0), ASIN(0.25), ASIN(0.50), ASIN(1);
Trigonometric Functions Example 2
In this example, we are going to use the ATAN and ATAN2 to find the Arctangent value of single and double arguments. Next, we used the COS to find the Cosine value.
SELECT ATAN(4), ATAN(24), ATAN(-32);
SELECT ATAN2(1, 5), ATAN2(-2, 3), ATAN(3.5, 0);
SELECT COS(0), COS(1), COS(2.5), COS(0.25);
MySQL CEIL, CEILING, CONV Example 3
In this example, we are going to use CEIL, CEILING, to find the closest value. Next, we applied the CONV method to convert the given value based on the given base.
SELECT CEIL(1.25), CEIL(1.95), CEIL(-2.3), CEIL(-2.90);
SELECT CEILING(7.98), CEILING(6.01), CEILING(-22.93);
SELECT CONV('A', 16, 2), CONV('G', 18, 8);
Numeric Functions Example 4
In this instance, we are going to use the MySQL COT, DEGREES, and EXP mathematical functions.
SELECT COT(5), COT(1), COT(15);
SELECT DEGREES(3.14), DEGREES(PI() / 2), DEGREES(2);
SELECT EXP(0), EXP(2), EXP(-2);
MySQL Mathematical Functions Example 5
In this example, we are going to apply the EXP, FLOOR, and FORMAT.
SELECT EXP(0), EXP(2), EXP(-2);
SELECT FLOOR(2.89), FLOOR(2.05), FLOOR(-2.25), FLOOR(-2.97);
SELECT FORMAT(543212.1245, 2), FORMAT(1121343.87965, 4);
MySQL LN, LOG, and LOG2 Example 6
In this example, we are going to practice the LN, LOG, and LOG2 to find the Natural Logarithmic Value and Log base 2 value.
SELECT LN(2), LN(5), LN(-5);
SELECT LOG(2), LOG(2, 250), LOG(10, 250);
SELECT LOG2(250), LOG2(24567), LOG2(-23234);
Numeric Functions Example 7
Here, we will use the LOG10 function to find the Logarithmic value of base 10. Next, the MOD finds the remainder and the PI method to get the PI value.
SELECT LOG10(250), LOG10(1000), LOG10(2500);
SELECT MOD(250, 7), MOD(59, 9), 600 MOD 16;
SELECT PI(), PI() MOD 2;
MySQL POW, POWER, RADIANS Example 8
In this MySQL Mathematical functions example, we use the POW and POWER to find the Power. Next, we applied the RADIANS to convert Degrees to radians.
SELECT POW(3, 2), POW(25, 5), POW(16, 2);
SELECT POWER(3, 2), POWER(25, 5), POWER(16, 2);
SELECT RADIANS(90), RADIANS(180), RADIANS(360);
MySQL Mathematical Functions Example 9
In this example, we will use the RAND to return a random value SIGN method to find the sign. Next, we used the ROUND method to round the decimals.
SELECT RAND(), RAND(5);
SELECT ROUND(-12.25), ROUND(-12.67), ROUND(14.268, 1);
SELECT SIGN(-12), SIGN(12), SIGN(0);
MySQL Numeric Functions Example 10
In this instance, we will use the SIN and TAN methods to find the Sine Value and Tangent Value. Next, we used the SQRT to find the square root of a given value.
SELECT SIN(0), SIN(2.5), SIN(1);
SELECT TAN(3.14), TAN(1), TAN(0);
SELECT SQRT(16), SQRT(25), SQRT(894);
MySQL TRUNCATE and HEX Example 11
We are going to use TRUNCATE and HEX mathematical methods.
SELECT TRUNCATE(10.9876, 1), TRUNCATE(10.9876, 0);
SELECT HEX(255), HEX('Hi'), HEX('Hello');
