MySQL Numeric Functions
MySQL ABS() Function
Main Article :- MySql ABS() Function
The ABS()
function calculates the absolute (positive) value of an specified integer.
If the input parameter is NULL, the absolute value will also be NULL.
Because the result cannot be saved in a signed BIGINT integer, ABS(-9223372036854775808) throws an error.
Syntax:
Here is the syntax of MySQL ABS() function:
ABS(numeric value)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. An specific number. |
Return Value:
- Convert the leading numbers to a number if it starts with a number.
- Convert it to 0 if it can't be converted to a number.
- ABS() returns NULL if the number parameter is NULL.
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: The example to Find the absolute value of a Positive and Negative number:
SELECT ABS(-143.6) AS AbsoluteValueOfNegativeNumber,
ABS(143.6) AS AbsoluteValueOfPositiveNumber;
Result:
AbsoluteValueOfNegativeNumber | AbsoluteValueofPositiveNumber |
---|---|
143.6 | 143.6 |
This result calculates the absolute value of a positive number (143.6) and a negative number (-143.6), so the absolute value of the result is 143.6.
Example 2: Let's see how to find the absolute value of various parameters:
SELECT ABS('420'),
ABS('-420'),
ABS('-420J'),
ABS('J420'),
ABS(NULL);
Result:
ABS('420') | ABS('-420') | ABS('-420J') | ABS('J420') | ABS('020'), | ABS(NULL) |
---|---|---|---|---|---|
420 | 420 | 420 | 0 | 20 | NULL |
If the parameter value starts with a number like '420', '-420', '-420J', or '020', the absolute number '420' will be the output. If the value starts with the text 'J420', the output will be 0. and if the input value is NULL, the result is also NULL.
MySQL ACOS() Function
The ACOS() function takes a value whose cosine is X and returns it as the arc cosine of specific value.
If the given value is not between -1 and 1, this function will return NULL.
Syntax:
Here is the syntax of MySQL ACOS() function:
ACOS(numeric value)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. A number. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: This example gets the numbers arc cosine:
SELECT ACOS(0.3),
ACOS(0.75);
Result:
ACOS(0.3) | ACOS(0.75) |
---|---|
1.2661036727795 | 0.722734247813416 |
As per the result, the specified values 0.3, and 0.75 return its arc cosine value.
Example 2: Let's implement different values in this example:
SELECT ACOS(-0.7),
ACOS(-0.3),
ACOS(1),
ACOS(0),
ACOS(-1);
Result:
ACOS(-0.7) | ACOS(-0.3) | ACOS(1) | ACOS(0) | ACOS(-1) |
---|---|---|---|---|
2.34619382340565 | 1.87548898081029 | 0 | 1.5707963267949 | 3.14159265358979 |
From the result, you can see that if you gave negative numbers like -0.7, -0.3, and -1 will return the corresponding arc cosine value.
Example 3: Let's give range above or less than range -1 to 1 in this example:
SELECT ACOS(3),
ACOS(-3),
ACOS(NULL);
Result:
ACOS(3) | ACOS(-3) | ACOS(NULL) |
---|---|---|
NULL | NULL | NULL |
When you give the input value outside the range of -1 to 1. it will return NULL, as well as if the specified value is null, the output will be NULL.
MySQL ASIN() Function
The ASIN() function takes a value whose sine is X and returns it as the arc sine of specific value.
If the given value is not between -1 and 1, this function will return NULL.
Syntax:
Here is the syntax of MySQL ASIN() function:
ASIN(number)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. A numer. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: This example gets the numbers arc sine:
SELECT ASIN(0.3),
ASIN(0.75);
Result:
ASIN(0.3) | ASIN(0.75) |
---|---|
0.304692654015398 | 0.848062078981481 |
As per the result, the specified values 0.3, and 0.75 return its arc sine value.
Example 2: Let's implement different values in this example:
SELECT ASIN(-0.7),
ASIN(-0.3),
ASIN(1),
ASIN(0),
ASIN(-1);
Result:
ASIN(-0.7) | ASIN(-0.3) | ASIN(1) | ASIN(0) | ASIN(-1) |
---|---|---|---|---|
-0.775397496610753 | -0.304692654015398 | 1.5707963267949 | 0 | -1.5707963267949 |
From the result, you can see that if you gave negative numbers like -0.7, -0.3, and -1 will return the corresponding arc sine value. When you give the input value outside the range of -1 to 1, it will return NULL, as well as if the specified value is null, the output will be NULL.
Example 3: Let's give range above or less than range -1 to 1 in this example:
SELECT ASIN(3),
ASIN(-3),
ASIN(NULL);
Result:
ASIN(3) | ASIN(-3) | ASIN(NULL) |
---|---|---|
NULL | NULL | NULL |
When you give the input value outside the range of -1 to 1. it will return NULL, as well as if the specified value is null, the output will be NULL.
MySQL ATAN() Function
The ATAN()
function returns the arc tangent of one or more integers.
If any parameter is NULL, the ATAN() function will return NULL.
Syntax:
Here is the syntax of MySQL ATAN() function:
ATAN(numeric value)
or
ATAN(x, y)
Parameter Values
Parameter | Description |
---|---|
number | A numeric value. |
x, y | You'll need two numbers to compute the arc tangent of a circle. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: This example gets the numbers arc tangent:
SELECT ATAN(3.3);
Result:
As per the result, the specified value 3.3 return its arc tangent value 1.27656176168371.
Example 2: In this example let's check the tangent value of the negative number:
SELECT ATAN(0.3),
ATAN(-0.7),
ATAN(-0.3);
Result:
ATAN(0.3) | ATAN(-0.7) | ATAN(-0.3) |
---|---|---|
0.291456794477867 | -0.610725964389209 | -0.291456794477867 |
From the result, you can see that if you gave negative numbers like -0.3, -0.7 will return the corresponding arc tangent value.
Example 2: The arc tangent of two numbers is returned in this example:
SELECT ATAN(-0.7, 3);
Result:
The example returns -0.229231933276995 as an arc tangent of the two variables -0.7 and 3.
Example 3: The arc tangent of pi function and variable returned in this example:
SELECT ATAN(PI(), 2);
Result:
The example returns 1.003882185389 as an arc tangent of the two variables PI() value and 2.
Example 4: The arc tangent of NULL value returned in this example
SELECT ATAN(NULL);
Result:
As this function works, if the any parameter is null, a ATAN() will return null.
MySQL ATAN2() Function
The arc tangent of two integers is returned by the ATAN2()
function.
Calculating the arc tangent of x / y is similar, only the quadrant of the result is determined by the signs of both variables.
Syntax:
Here is the syntax of MySQL ATAN2() function:
ATAN2(x, y)
Parameter Values
Parameter | Description |
---|---|
x, y | Required. You'll need two numbers to compute the arc tangent of a circle. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: The arc tangent of two values is returned:
SELECT ATAN2(2.5, 1),
ATAN2(0.25, 1);
Result:
ATAN2(2.5, 1) | ATAN2(0.25, 1) |
---|---|
1.19028994968253 | 0.244978663126864 |
The output returns the quadrant of the two variables 2.5, 1, and 0.25, 1.
Example 2: The arc tangent of two numbers is returned, let's use one negative number in this example:
SELECT ATAN(-3,3);
Result:
The result will return -0.785398163397448 as an tangent value of negative number -2 and positive value 2.
Example 3: Let's use Null value in ATAN2() function:
SELECT ATAN(NULL);
Result:
Whenever you use null in ATAN2() it will return NULL.
MySQL CEIL() Function
The CEIL()
function in MySQL returns the lowest integer value that is greater than or equal to the given number.
Note: The CEIL() function is the same as the CEILING()
function.
Syntax:
Here is the syntax of MySQL CEIL() function:
CEIL(numeric value)
Parameter Values
Parameter | Description |
---|---|
number | Required. A number. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: The lowest integer number greater than or equal to 47.89 is found in this example:
SELECT CEIL(47.89), CEIL(47.22);
Result:
CEIL(47.89) | CEIL(47.22) |
---|---|
48 | 48 |
Through the result, the function returns greater than the specified Numeric value of 47.89.
Example 2: The lowest integer number greater than or equal to 75 is found in this example:
SELECT CEIL(75);
Result:
Through the result, the function returns an equal value to the specified Numeric value of 75.
Example 3: Let's use some more example:
CEIL(-143.321),
CEIL(-143.567),
CEIL(-143),
CEIL(-400),
CEIL(NULL);
Result:
CEIL(-143.321) | CEIL(-143.567) | CEIL(-143) | CEIL(-400) | CEIL(NULL) |
---|---|---|---|---|
-143 | -143 | -143 | -400 | NULL |
You can see from the output that if we provide negative values, it returns the typical greater than or equal values. If a NULL exists, it is always NULL.
MySQL CEILING() Function
Main Article :- MySql CEILING() Function
The CEILING()
function in MySQL returns the lowest integer value that is larger than or equal to the supplied number.
If the parameter number is NULL, the CEILING() function will return NULL.
Note: This method is the same as CEIL().
Syntax:
Here is the syntax of MySQL CEILING() function:
CEILING(numeric value)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. A number. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Example
Example 1: The lowest integer number greater than or equal to 45.89 is found in this example:
SELECT CEILING(45.89), CEILING(45.22);
Result:
CEILING(45.89) | CEILING(45.22) |
---|---|
46 | 46 |
Through the result, the function returns greater than the specified Numeric value of 45.89.
Example 2: The lowest integer number greater than or equal to 55 is found in this example:
SELECT CEILING(55);
Result:
Through the result, the function returns an equal value to the specified Numeric value of 55.
Example 3: Let's use some more example:
SELECT CEILING(-143.321),
CEILING(-143.567),
CEILING(-143),
CEILING(-400),
CEILING(NULL);
Result:
CEILING(-143.321) | CEILING(-143.567) | CEILING(-143) | CEILING(-400) | CEILING(NULL) |
---|---|---|---|---|
-143 | -143 | -143 | -400 | NULL |
You can see from the output that if we provide negative values, it returns the typical greater than or equal values. If a NULL exists, it is always NULL.
MySQL COS() Function
The COS()
method returns the cosine of an integer.
If the parameter number is NULL, the COS() function will return NULL.
Syntax:
Here is the syntax of MySQL COS() function:
COS(number)
Parameter Values
Parameter | Description |
---|---|
number | Required. A numeric value |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: The example to return cosine value:
SELECT COS(2),
COS(2.5),
COS(0.2);
Result:
COS(2) | COS(2.5) | COS(0.2) |
---|---|---|
-0.416146836547142 | -0.801143615546934 | 0.980066577841242 |
Via the result, you can see we give a positive value and returns cosine corresponding value.
Example 2: let's use negative number, pi(), 0, and NUll values to check the cosine value:
SELECT COS(-0.6),
COS(-0.3),
COS(0),
COS(PI()),
COS(NULL);
Result:
COS(-0.6) | COS(-0.3) | COS(0) | COS(pi()) | COS(NULL) |
---|---|---|---|---|
0.825335614909678 | 0.955336489125606 | 1 | -1 | NULL |
You can see we give a negative value and return the cosine corresponding value. If we give 0 its cosine value, 1 is returned, the pi() function value is -1. As mentioned above, if any of the parameters is null in the cos, it will return null.
MySQL COT() Function
The COT()
method returns the cotangent of an integer.
Syntax:
Here is the syntax of MySQL COT() function:
COT(numeric value)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. It's a number. If the number is zero, the result is either an error or NULL. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: Returns the cotangent of an integer, for example:
SELECT COT(5);
Result:
The value of contangent 5 is -0.295812915532746 as a consequence of the calculation.
Example 2: Let's use some negative number, postive number, and pi for example::
SELECT COT(2.5),
COT(0.2),
COT(-0.5),
COT(-0.2),
COT(PI());
Output:
COT(2.5) | COT(0.2) | COT(-0.5) | COT(-0.2) | COT(PI()) |
---|---|---|---|---|
0.5984721441039565 | 0.19866933079506122 | -0.479425538604203 | -0.19866933079506122 | 1.2246467991473532e-16 |
Via the result, you can see we give a positive value and returns contagent corresponding value.
Example 3: Check the 0 and NULL values in the example:
SELECT COT(0),
COT(NULL);
Result:
COT(0) | COT(NULL) |
---|---|
0 | NULL |
The COT() function returns NULL if the parameter number is NULL.
MySQL DEGREES() Function
The DEGREES()
function in MySQL translates a radian number to a degree and returns the result.
The DEGREES() function returns NULL if the number parameter is NULL.
Note: RADIANS() and PI() are also available.
Syntax:
Here is the syntax of MySQL DEGREES() function:
DEGREES(numeric value)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. A number. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: The following formula can be used to convert radian values to degrees:
SELECT DEGREES(2.8);
Result:
Convert radian value 2.8 to degrees 160.42 using the result.
Example 2: Let's use the radian Convert the pi() function's radian value to degrees:
SELECT DEGREES(PI()*2);
Result:
From the result, the pi function as a radian value convert to degrees.
MySQL DIV Function
The DIV
function in MySQL divides two numbers and produces a result that is an integer.
Syntax:
Here is the syntax of MySQL DIV() function:
a DIV b
Parameter Values
Parameter | Description |
---|---|
a | Required. The result of dividing a value by b. |
b | Required. The divisor. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: Example for Integer division (15/3):
SELECT 15 DIV 3;
Result:
15 div 3 |
---|
5 |
From the result, two values are 15/3. The value will be 5. It only returns cosent.
Example 2: Example for Integer division (9/2):
SELECT 9 DIV 2;
Result:
The result will be cosent 4.
MySQL EXP() Function
EXP()
delivers the value of the natural constant e raised to the power of the provided value in MySQL.
for example, EXP(2) yields e2. The natural logarithm's base, often known as the natural base, is e.
The constant e is used in natural logarithms (2.718281...).
Tip: Examine the LOG()
and LN()
methods, as well.
Syntax:
Here is the syntax of MySQL EXP() function:
EXP(numeric value)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. A number. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Example
Example 1: Return e raised to the power of 1 example:
SELECT EXP(1);
Result:
The result is given that power of 1.
Example 2: Let's try some new expression values:
SELECT EXP(0),
EXP(2),
EXP(-1),
EXP(-2),
EXP(NULL);
Result:
EXP(0) | EXP(2) | EXP(-1) | EXP(-2) | EXP(NULL) |
---|---|---|---|---|
1 | 7.38905609893065 | 0.367879441171442 | 0.135335283236613 | NULL |
Through the result, you can use the exp() function to raise the power of the negative number, 0, NULL.
MySQL FLOOR() Function
Main Article :- MySql FLOOR() Function
The FLOOR()
method returns the largest integer value that is less than or equal to the provided integer.
The return value for exact-value numeric parameters is of the exact-value numeric type. The return value for string or floating-point parameters is of the floating-point type.
Note: Also look at the ROUND()
, CEIL()
, CEILING()
, TRUNCATE()
, and DIV
functions.
Syntax:
Here is the syntax of MySQL FLOOR() function:
FLOOR(numeric value)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. A number. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: Return the largest integer number that is less than or equal to 143.89 and 145.22:
SELECT FLOOR(145.89), FLOOR(145.22);
Result:
FLOOR(145.89) | FLOOR(145.22) |
---|---|
145 | 145 |
The result will give less than or equal to 145.89 and 145.22.
Example 2: Let's use different numeric values in this function:
SELECT FLOOR(143),
FLOOR(-143.123),
FLOOR(-143.789),
FLOOR(-143),
FLOOR(-200),
FLOOR(NULL);
Result:
FLOOR(143) | FLOOR(-143.123) | FLOOR(-143.789) | FLOOR(-143) | FLOOR(-200) | FLOOR(NULL) |
---|---|---|---|---|---|
143 | -144 | -144 | -143 | -200 | NULL |
We returned the floor values using negative integers -143.123, -143.789, -200, and whole numbers 143. It will return NULL if we use a NULL value.
MySQL GREATEST() Function
The GREAEST() function in MySQL returns the highest value in the parameter list.
Note: The LEAST()
method can be used to locate the smallest value in a parameter list.
Syntax:
Here is the syntax of MySQL GREATEST() function:
GREATEST(param1, param2, param3, ...)
Parameter Values
Parameter | Description |
---|---|
param1, param2, param3, ... | Required. The set of parameters to be evaluated. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: In this case, the highest-valued parameters in the list should be returned:
SELECT GREATEST(4, 22, 64, 9, 55);
Result:
The greatest function takes the highest value 64 from the list as a result.
Example 2: The most relevant argument in the list is returned in this example:
SELECT GREATEST("simmanchith.com", "jp.com", "developer.com");
Result:
In this result, you can see that this function returns the most significant parameter in the list.
Example 3: Let's use NULL values in GREATEST function:
GREATEST('j', 'p', NULL);
Result:
This result demonstrates that if the list contains a NULL, the result will be NULL.
MySQL LEAST() Function
The LEAST() function in MySQL returns the smallest value in the parameter list.
Note: The LEAST()
method can be used to locate the smallest value in a parameter list.
Syntax:
Here is the syntax of MySQL LEAST() function:
LEAST(param1, param2, param3, ...)
Parameter Values
Parameter | Description |
---|---|
param1, param2, param3, ... | Required. The set of parameters. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: In this case, the smallest-valued parameters in the list should be returned:
SELECT LEAST(4, 22, 64, 9, 55);
Result:
The least function takes the lowest value 4 from the list as a result.
Example 2: The most relevant argument in the list is returned in this example:
SELECT LEAST("simmanchith.com", "jp.com", "developer.com");
Result:
In this result, you can see that this function returns the least significant parameter in the list.
Example 3: Let's use NULL values in LEAST function:
GREATEST('j', 'p', NULL);
Result:
This result demonstrates that if the list contains a NULL, the result will be NULL.
MySQL LN() Function
LN()
returns the natural logarithm of an Integer.
The function returns NULL, with the error "Invalid parameter for logarithm".
Note: The EXP()
and LOG()
methods are also useful.
Syntax:
Here is the syntax of MySQL LN() function:
LN(numeric value)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. A numeric value. value greater than 0. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: The natural logarithm of 5 is returned in this example:
SELECT LN(5);
Result:
The natural logarithm of 5 is returned as 1.6094379124341.
Example 2:
The natural logarithm of 1 is returned in this example:
SELECT LN(1);
Result:
The natural logarithm of 1 is returned as 0.
Exmaple 3: Let's find out logarithm value of 0, -1, NULL from this example:
SELECT LN(0),
LN(-2),
LN(NULL);
Result:
LN(0) | LN(-2) | LN(NULL) |
---|---|---|
NULL | NULL | NULL |
As a result, when you use 0, -1, and NULL values inside the LN() function, it will return NULL.
MySQL LOG() Function
The LOG()
returns the natural logarithm of an integer with only one parameter.
You can calculate LOG()
function in MySQL calculate the logarithm of an integer in a given base also with base parameter.
The function returns NULL if number is less than or equal to 0.0E0, and an error "Invalid parameter for logarithm" is reported.
Note: The EXP()
function is the inverse of this function (when used with a single argument).
Syntax:
Here is the syntax of MySQL LOG() function:
LOG(numeric value)
or
LOG(base, numeric value)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. A number. should be greater than 0. |
base | The base of number. should be greater than 1. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: The natural logarithm of 5 is returned in this example:
SELECT LOG(1),
LOG(5);
Result:
LOG(1) | LOG(5) |
---|---|
0 | 1.6094379124341 |
The natural logarithm of 1 is 0, and the natural logarithm of 5 is returned as 1.6094379124341.
Example 2: To a given base, convert the natural logarithm of specified value:
SELECT LOG(EXP(1), 1),
LOG(EXP(1), 2),
LOG(4, 64),
LOG(100, 1000);
Result:
LOG(exp(1), 1) | LOG(exp(1), 2) | LOG(4, 64) | LOG(100, 1000) |
---|---|---|---|
0 | 0.693147180559945 | 3 | 1.5 |
The result converts the natural logarithm of a specified value into a given base.
Example 3: This example to return NULL value using LOG() function:
SELECT LOG(0),
LOG(-1),
LOG(1, 10),
LOG(NULL);
Result:
LOG(0) | LOG(-1) | LOG(1, 10) | LOG(NULL) |
---|---|---|---|
NULL | NULL | NULL | NULL |
There is no valid specified value in this result, so the result will be NULL. also if there is null in the parameter output is NULL.
MySQL MOD() Function
The MOD()
method returns the residual of an integer divided by another number.
Note: The function is simple representation of number1 % number2.
Syntax:
Here is the syntax of MySQL MOD() function:
MOD(number1, number2)
or
number1 MOD number2
Parameter Values
Parameter | Description |
---|---|
number1 | Required. denominator. |
number2 | Required. The divisor |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: Example to return the remainder of 23 % 5:
SELECT MOD(23, 5);
Otherwise, You can use without MOD function:
SELECT 23 MOD 5;
Result:
The function returns the modulus of specific value.
Example 2: Example to return the remainder of 100 % 10:
SELECT MOD(100, 10);
Otherwise, You can use without MOD function:
SELECT 100 MOD 10;
Result:
The function returns the modulus of specific value.
Example 3:
SELECT MOD(1, 0),
MOD(NULL, 1);
Result:
The function returns null, because parameter used 0 or NULL.
MySQL PI() Function
The PI()
function returns the value of PI.
Although MySQL uses the full double-precision value internally, the default number of decimal places displayed is seven.
Note: The RADIANS()
and DEGREES()
methods are also useful.
Syntax:
Here is the syntax of MySQL PI() function:
PI()
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: Example to return the value of PI:
SELECT PI();
Result:
The result give value of pi value.
Example 2: If you want more decimal places:
SELECT PI()+0.000000000000000000;
Result:
From the result, you can see that the user can specify decimal places. It will return the corresponding decimal spaces what you specified.
MySQL POW() Function
POW()
returns the result of multiplying one number by the power of another number.
Note: This method is the same as POWER()
.
Syntax:
Here is the syntax of MySQL POW() function:
POW(x, y)
Parameter Values
Parameter | Description |
---|---|
x | Required. The base. |
y | Required. The exponent. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: Return 6 to the second power as an example:
SELECT POW(6, 2);
Result:
As per result the second power of 6 is 36.
Example 2: Return 4 to the third power as an example:
SELECT POW(4, 3);
Result:
As per result the third power of 4 is 64.
Example 3: Let's use fractional value and negative values in this example:
SELECT POW(2.5, 2),
POW(2, -2),
POW(2, -4);
Result:
POW(3.5, 2) | POW(2, -3) | POW(2, -4) |
---|---|---|
12.25 | 0.125 | 0.0625 |
From the result, find the power of the fractional value. and also using a negative number as a power to multiply one number.
Example 4: Using NULL value:
SELECT POW(2, NULL),
POW(NULL, 2),
POW(NULL, NULL);
Result:
POW(2, NULL) | POW(NULL, 2) | POW(NULL, NULL) |
---|---|---|
NULL | NULL | NULL |
If there is a NULL in the parameter, it will return NULL.
MySQL POWER() Function
Main Article :- MySql POWER() Function
POWER()
returns the result of multiplying one number by the power of another number.
Syntax:
Here is the syntax of MySQL POWER() function:
POWER(x, y)
Parameter Values
Parameter | Description |
---|---|
x | Required. The base. |
y | Required. The exponent. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: Return 6 to the second power as an example:
SELECT POWER(6, 2);
Result:
As per result the second power of 6 is 36.
Example 2: Return 4 to the third power as an example:
SELECT POWER(4, 3);
Result:
As per result the third power of 4 is 64.
Example 3: Let's use fractional value and negative values in this example:
SELECT POWER(2.5, 2),
POWER(2, -2),
POWER(2, -4);
Result:
POWER(3.5, 2) | POWER(2, -3) | POWER(2, -4) |
---|---|---|
12.25 | 0.125 | 0.0625 |
From the result, find the power of the fractional value. and also using a negative number as a power to multiply one number.
Example 4: Using NULL value:
SELECT POWER(2, NULL),
POWER(NULL, 2),
POWER(NULL, NULL);
Result:
POWER(2, NULL) | POWER(NULL, 2) | POWER(NULL, NULL) |
---|---|---|
NULL | NULL | NULL |
If there is a NULL in the parameter, it will return NULL.
MySQL RADIANS() Function
The RADIANS()
function in MySQL turns a degree angle into a radian angle that is substantially equal.
Note: The DEGREES()
and PI()
methods are also useful.
Remember: π radians equals 180 degrees.
Syntax:
Here is the syntax of MySQL RADIANS() function:
RADIANS(numeric value)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. A numeric value in degrees. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: To convert a degree value to radians, use the following formula:
SELECT RADIANS(0),
RADIANS(90),
RADIANS(180),
RADIANS(360);
Result:
RADIANS(0) | RADIANS(90) | RADIANS(180) | RADIANS(360) |
---|---|---|---|
0 | 1.5707963267949 | 3.14159265358979 | 6.28318530717959 |
Within the function, the degree value will be transformed to radians.
Example 2: Let's use negative degree value and null :
SELECT RADIANS(-90),
RADIANS(-180),
RADIANS(-360),
RADIANS(NULL);
Result:
RADIANS(-90) | RADIANS(-180) | RADIANS(-360) | RADIANS(NULL) |
---|---|---|---|
-1.5707963267949 | -3.14159265358979 | -6.28318530717959 | NULL |
The result shows that the function contains a negative degree value and NULL.
MySQL RAND() Function
Main Article :- MySql RAND() Function
The RAND()
function in MySQL returns a positive-sign random decimal value larger than or equal to 0.0 (inclusive) and less than 1.0. (exclusive).
Refer the following formulas to return random numbers in other ranges (such as a and b):
- A number between a and b at random: RAND() * (b - a) + a.
- A integer between a and b at random: FLOOR(RAND() * (b - a + 1) + a).
Syntax:
Here is the syntax of MySQL RAND() function:
RAND(seed)
Parameter Values
Parameter | Description |
---|---|
seed | Optional. If seed is specified, It generates a sequence of random integers that can be repeated. It returns a completely random number if no seed is supplied. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: If there is no seed value, it returns a fully random decimal number between 0 and 1:
SELECT RAND() AS FirstRandomValue,
RAND() AS SecondRandomValue,
RAND() AS ThirdRandomValue;
Result:
FirstRandomValue | SecondRandomValue | ThirdRandomValue |
---|---|---|
0.497530815654929 | 0.504734902181416 | 0.0310820946759378 |
This function generates a random decimal value between 0 and 1. As you can see, the result contains random values.
Example 2: It returns a fully random decimal number with the seed value specified:
SELECT RAND(4);
Result:
The random value will be generated based on seed value 4.
Example 3: Use first formula to get a random number between greater than or equal to 20 and less than 30:
SELECT RAND() * (30 - 20) + 20,
RAND() * (30 - 20) + 20,
RAND() * (30 - 20) + 20,
RAND() * (30 - 20) + 20;
Result:
RAND() * (30 - 20) + 20 | RAND() * (30 - 20) + 20 | RAND() * (30 - 20) + 20 | RAND() * (30 - 20) + 20 |
---|---|---|---|
23.6562976088899 | 20.2016154585421 | 20.0391847733773 | 29.5910777985967 |
This result used to generate random decimal number between 20 to 30.
Example 4: Use second formula to get a random number between greater than or equal to 7 and less than 15:
SELECT FLOOR(RAND()*(15-7+1)+7),
FLOOR(RAND()*(15-7+1)+7),
FLOOR(RAND()*(15-7+1)+7);
Result:
FLOOR(RAND()*(15-7+1)+7) | FLOOR(RAND()*(15-7+1)+7)1 | FLOOR(RAND()*(15-7+1)+7)2 |
---|---|---|
15 | 12 | 7 |
This result used to generate random whole number between 7 to 15.
MySQL ROUND() Function
Main Article :- MySql ROUND() Function
The ROUND()
method rounds a number to a specified number of decimal places.
ROUND() applies the following rules vary according to the type of the first argument:
The "round half away from zero" or "round toward nearest" rule is applied to exact-value numbers by ROUND():
If a value has a fractional part of .5, it is rounded up to the next integer if it is positive, or down to the next integer if it is negative. (In other words, it is not zero.) If the fractional part is less than .5, the value is rounded down to the next integer if positive, and up to the next integer if negative.
ROUND() follows the "round to closest even" rule on many systems, which means that a number with a fractional portion nearly halfway between two integers is rounded to the nearest even number.
Syntax:
Here is the syntax of MySQL ROUND() function:
ROUND(numeric value, fractions)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. The number that will be rounded. |
fractions | Optional. How many decimal places should a number be rounded to? If no value is specified, it returns an integer (no decimals). |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: Round the figure to two decimal places in this example:
SELECT ROUND(143.355, 2);
Result:
The result is to round the numeric value based on two decimal places.
Example 2: Round the figure to zero decimal places in this example:
SELECT ROUND(669.2706, 0);
Result:
The result is to round the numeric value based on 0, so that all decimal places will be removed.
Example 3: This example round numeric value based on negative fraction number:
SELECT ROUND(143.179, -1),
ROUND(157.179, -2);
Result:
ROUND(143.179, -1) | ROUND(143.179, -2) |
---|---|
140 | 200 |
The function will return a whole number when you use -1. It takes 10 as a base, so it will be based on the last two values before the point. And like that, when you use -2, it takes 100 as a base, so it will be based on the last three values before the point.
Example 4: used to Round the Price column (to 1 decimal):
Let's took BOOKSIMPLE table to working on following query:
BookName | Price |
---|---|
Getting Started With SQL | 99.99 |
MySql Query Performance Tuning | 175.132 |
The query will be like this,
SELECT BookName,
Price, ROUND(Price, 1) AS RoundedPrice
FROM BOOKSIMPLE;
Result:
BookName | Price | RoundedPrice |
---|---|---|
Getting Started With SQL | 99.99 | 100 |
MySql Query Performance Tuning | 175.132 | 175 |
The result is used to round the nearest whole number based on 1 decimal in the table column.
MySQL SIGN() Function
Depending on whether the argument is positive, zero, or negative, MySQL's SIGN()
method returns 1, 0, or -1 as the sign of the parameter.
One of the following values will be returned by this function:
- If number greater than 0, it returns 1
- If number equal to 0, it returns 0
- If number less than 0, it returns -1
- If number is NULL, the function will return NULL
Syntax:
Here is the syntax of MySQL SIGN() function:
SIGN(numeric value)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. Return the sign to this number. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example: Return an example of how to find the sign of a number:
SELECT SIGN(123),
SIGN(123.123),
SIGN(-123),
SIGN(-123.123),
SIGN(0),
SIGN(NULL);
Result:
SIGN(143) | SIGN(143.143) | SIGN(-143) | SIGN(-143.123) | SIGN(0) | SIGN(NULL) |
---|---|---|---|---|---|
1 | 1 | -1 | -1 | 0 | NULL |
From the above result, you can see that the signs of positive, negative, zero, and null values are returned.
MySQL SIN() Function
The SIN()
function in MySQL returns the sine of the given integer.
If number is NULL, the function will return NULL.
Syntax:
Here is the syntax of MySQL SIN() function:
SIN(numeric value)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. A number. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: To find the sine of a number, use the following formula:
SELECT SIN(3),
SIN(-2),
SIN(0);
Result:
SIN(3) | SIN(-2) | |
---|---|---|
0.141120008059867 | -0.909297426825682 | 0 |
From the result, find the sine value of whole negative, postive number, and zero.
Example 2: Let's use positive and negative decimal value in this example:
SELECT SIN(2.5),
SIN(0.2),
SIN(-0.5),
SIN(-0.2);
Result:
SIN(2.5) | SIN(0.2) | SIN(-0.5) | SIN(-0.2) |
---|---|---|---|
0.598472144103957 | 0.198669330795061 | -0.479425538604203 | -0.198669330795061 |
This result provides the sine value of a decimal number.
Example 3: Using NULL and PI() in SIN() function:
SELECT SIN(PI()),
SIN(NULL);
Result:
SIN(PI()) | SIN(NULL) |
---|---|
1.22464679914735E-16 | NULL |
The result will produce the pi() function's corresponding sine value. If there is a null value in the function, the output will be NULL.
MySQL SQRT() Function
Main Article :- MySql SQRT() Function
The square root of an integer is returned by the SQRT()
function.
Note: The number should be positive. If you use a negative value, it will return NULL.
Syntax:
Here is the syntax of MySQL SQRT() function:
SQRT(numeric number)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. A nonnegative number. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: This example returns a number's square root:
SELECT SQRT(36),
SELECT SQRT(100),
SELECT SQRT(105);
Result:
SQRT(36) | SQRT(100) | SQRT(105) |
---|---|---|
6 | 10 | 10.2469507659596 |
The result is used to calculate the square root of a given number.
Example 2: Let's try to use negative number and NULL:
SELECT SQRT(-5),
SQRT(NULL);
Result:
SQRT(-5) | SQRT(NULL) |
---|---|
NULL | NULL |
You can see that if you give a negative or NULL value as a parameter, it will return NULL.
MySQL TAN() Function
The tangent of an integer is returned by the TAN()
function.
Syntax:
Here is the syntax of MySQL TAN() function:
TAN(numeric value)
Parameter Values
Parameter | Description |
---|---|
number | Required. A numeric value |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: In this example used to calculate tangent value:
SELECT TAN(2.5),
TAN(0.2),
TAN(-0.5),
TAN(-0.2),
TAN(0);
Result:
TAN(2.5) | TAN(0.2) | TAN(-0.5) | TAN(-0.2) | TAN(0) |
---|---|---|---|---|
-0.74702229723866 | 0.202710035508673 | -0.54630248984379 | -0.202710035508673 | 0 |
As per the result, the TAN function was used to calculate tangent value of positive number, negative number, and 0.
Example 3: Using NULL and PI() in TAN() function:
SELECT TAN(PI()),
TAN(NULL);
Result:
TAN(PI()) | TAN(NULL) |
---|---|
-1.22464679914735E-16 | NULL |
The result will produce the pi() function's corresponding tangent value. If there is a null value in the function, the output will be NULL.
MySQL TRUNCATE() Function
TRUNCATE()
is a MySQL function that truncates numbers to a specified number of decimal places.
If decimal is zero, the result lacks a decimal point and a fractional part.
decimal can be negative, causing the value number's decimal digits left of the decimal point to become zero.
Note: This function also familiar to FLOOR()
, CEIL()
, CEILING()
, and ROUND()
functions.
Syntax:
Here is the syntax of MySQL TRUNCATE() function:
TRUNCATE(numeric value, fractions)
Parameter Values
Parameter | Description |
---|---|
numeric value | Required. The number to truncate. |
fractions | Required. The decimal value to truncate. |
Technical Details
Works in: | From MySQL 4.0 |
---|
Examples:-
Example 1: Return a number with 2 decimal places truncated:
SELECT TRUNCATE(143.197, 1),
TRUNCATE(143.197, 4),
TRUNCATE(143.197, 0);
Result:
TRUNCATE(143.197, 1) | TRUNCATE(143.197, 4) | TRUNCATE(143.197, 0) |
---|---|---|
143.1 | 143.197 | 143 |
As per result truncate will return specified number of decimal places.
Example 2: Negative number inside TRUNCATE function:
SELECT TRUNCATE(143.197, -1),
TRUNCATE(143.197, -2);
Result:
truncate(143.197, -1) | truncate(143.197, -2) |
---|---|
140 | 100 |
The function will return a whole number when you use -1. It takes 10 as a base, so it will be based on the last two values before the point. And like that, when you use -2, it takes 100 as a base, so it will be based on the last three values before the point.
Example 3: When the function use NUll value:
TRUNCATE(123.179, NULL);
Result:
If there is null value in TAN() function it will return NULL.