Chapter 13. Mathematical Functions
MySQL has many built-in mathematical functions that you can use in SQL
statements for performing calculations on values in databases. Each function
accepts either numbers or numeric columns for parameter values. All
mathematical functions return NULL on error.
The following functions are covered in this chapter:
[click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here].
13.1. Functions in Alphabetical Order
The following is a list of MySQL mathematical functions in
alphabetical order, along with descriptions of each and examples of their
use.
This function returns the absolute value of a given number.
Here is an example:
SELECT ABS(-10);
+----------+
| ABS(-10) |
+----------+
| 10 |
+----------+
This function returns the arc cosine, in radians, of a given
number. For input greater than 1 or less than –1, NULL is returned.
Here is an example:
SELECT ACOS(.5), ACOS(1.5);
+----------+-----------+
| ACOS(.5) | ACOS(1.5) |
+----------+-----------+
| 1.047198 | NULL |
+----------+-----------+
This function returns the arcsine, in radians, of a given
number. For input greater than 1 or less than –1, NULL is returned.
Here is an example:
SELECT ASIN(1);
+----------+
| ASIN(1) |
+----------+
| 1.570796 |
+----------+
This function returns the arctangent, in radians, of a given
number. To determine the arctangent of two numbers (Y and X), add the
optional second argument to the function or use [click here]. The value of Y for a Cartesian plane is given
as the first argument and X as the second. Here is an example:
SELECT ATAN(2);
+----------+
| ATAN(2) |
+----------+
| 1.107149 |
+----------+
This function returns the arctangent, in radians, of X and Y
for a point on a Cartesian plane. The value for Y is given as the
first argument and X as the second. The reverse function is [click here]. Here is an example:
SELECT ATAN2(10, 5);
+--------------+
| ATAN2(10, 5) |
+--------------+
| 1.107149 |
+--------------+
This function returns the number of bits set in the argument,
which is an integer that the function treats as a binary
number.
SELECT BIT_COUNT(10), BIT_COUNT(11);
+---------------+---------------+
| BIT_COUNT(10) | BIT_COUNT(11) |
+---------------+---------------+
| 2 | 3 |
+---------------+---------------+
This function rounds a given floating-point number up to the
next higher integer. It's an alias to [click here].
SELECT CEIL(1), CEIL(1.1);
+---------+-----------+
| CEIL(1) | CEIL(1.1) |
+---------+-----------+
| 1 | 2 |
+---------+-----------+
This function rounds a given floating-point number up to the
next higher integer. It's an alias to [click here].
This function can be particularly useful when you want a numeric value
for a time function, but without the decimal places (the microseconds)
in the results:
SELECT NOW(), NOW() + 0, CEILING(NOW() + 0);
+---------------------+-----------------------+--------------------+
| NOW() | NOW() + 0 | CEILING(NOW() + 0) |
+---------------------+-----------------------+--------------------+
| 2007-07-16 00:07:14 | 20070716000714.000000 | 20070716000714 |
+---------------------+-----------------------+--------------------+
CONV(number, from_base, to_base) | |
This function converts a number from one numeric base system
to another. The number to convert is given in the first argument, the
base from which to convert the number in the second, and the base to
which to convert the number in the third. The minimum base allowed is
2 and the maximum is 36. Here is an example:
SELECT CONV(4, 10, 2) AS 'Base-10 4 Converted',
CONV(100, 2, 10) AS 'Binary 100 Converted';
+---------------------+----------------------+
| Base-10 4 Converted | Binary 100 Converted |
+---------------------+----------------------+
| 100 | 4 |
+---------------------+----------------------+
Here, the number 4 under the base 10 system is converted to the
base 2 or binary equivalent and back again.
This function returns the cosine of
number, where
number is expressed in radians. Here is an
example:
SELECT COS(2 * PI( ));
+----------------+
| COS(2 * PI( )) |
+----------------+
| 1 |
+----------------+
This function returns the cotangent of a number. Here is an
example:
SELECT COT(1);
+------------+
| COT(1) |
+------------+
| 0.64209262 |
+------------+
This function converts radians to degrees.
SELECT DEGREES(PI( ));
+------------------+
| DEGREES(PI( )) |
+------------------+
| 180.000000 |
+------------------+
This function returns the value of the natural logarithm base
number e to the power of the given number.
SELECT EXP(1);
+----------+
| EXP(1) |
+----------+
| 2.718282 |
+----------+
This function rounds a given floating-point number down to
the next lower integer. It's a counterpart to [click here].
SELECT CEILING(1.1), FLOOR(1.1);
+--------------+------------+
| CEILING(1.1) | FLOOR(1.1) |
+--------------+------------+
| 2 | 1 |
+--------------+------------+
This function returns the given floating-point
number with a comma inserted between every
three digits and a period before the number of decimal places
specified in the second argument.
SELECT FORMAT(1000.375, 2)
AS Amount;
+----------+
| Amount |
+----------+
| 1,000.38 |
+----------+
Notice that the function rounds the number given to two decimal
places.
GREATEST(value, value, ...) | |
This function compares two or more values, returning the
greatest value. In an INTEGER data type context,
all values are treated as integers for comparison. In a
REAL data type context, all values are treated as
REAL values for comparison. If any parameter
contains a case-sensitive string
(i.e., with a BINARY keyword), all values are
compared as case-sensitive strings. Here is an example:
SELECT GREATEST(col1, col2, col3);
This function converts an Internet Protocol (IP) address in
dot-quad notation to its numeric equivalent. The function [click here] can be used to reverse the results. Here is an
example:
SELECT INET_ATON('12.127.17.72')
AS 'AT&T';
+-----------+
| AT&T |
+-----------+
| 209654088 |
+-----------+This function is useful in sorting IP addresses that lexically
might not sort properly. For instance, an address of 10.0.11.1 would
come after 10.0.1.1 and before 10.0.2.1 under normal sort conditions
in an ORDER BY clause.
This function converts the numeric equivalent of an IP
address to its dot-quad notation. The function [click here] can be used to reverse the results.
SELECT INET_NTOA('209654088')
AS 'AT&T';
+--------------+
| AT&T |
+--------------+
| 12.127.17.72 |
+--------------+
Use this function to compare two or more values and return the
smallest value. In an INTEGER datatype context, all
values are treated as integers for comparison. In a
REAL data type context, all values are treated as
REAL values for comparison. If any parameter
contains a case-sensitive string (i.e., with a
BINARY keyword), all values are compared as
case-sensitive strings. Here is an example:
SELECT LEAST(col1, col2, col3);
This function returns the natural logarithm of its input.
Here is an example:
SELECT LN(5);
+----------+
| LN(5) |
+----------+
| 1.609438 |
+----------+
This function returns the logarithm of the first argument to
the base indicated by the second argument. This is the same as using
LOG(number)/LOG(base).
If the function is called with only the first argument, its natural
logarithm is returned; the function is equivalent to
LN in that case. Here is an example:
SELECT LOG(5,4);
+------------+
| LOG(5,4) |
+------------+
| 1.16096405 |
+------------+
This function returns the base 2 logarithm of a given
number.
This function returns the base 10 logarithm of a given
number.
MOD(number, number)
number MOD number | |
This function returns the remainder of a number given in the
first argument when divided by the number given in the second
argument, the modulo. The function works the same as using the
% operator between two given numbers. The second
syntax shown is available as of version 4.1 of MySQL. Starting with
version 4.1.7, fractional values may be given. Here is an
example:
SELECT MOD(10, 3);
+------------+
| MOD(10, 3) |
+------------+
| 1 |
+------------+
Here's an example of the alternate syntax:
SELECT 10 MOD 3;
+----------+
| 10 MOD 3 |
+----------+
| 1 |
+----------+
This function returns the octal, or base 8, numeric system
value of the given number. It returns NULL if the argument is NULL.
Here is an example:
SELECT OCT(1), OCT(9), OCT(16);
+--------+--------+---------+
| OCT(8) | OCT(9) | OCT(16) |
+--------+--------+---------+
| 10 | 11 | 20 |
+--------+--------+---------+
This function returns by default the first five decimal
places of the number pi. You can adjust it to
include more decimal places by adding a mask to the end of the
function. There is no argument within the parentheses of the function.
Here is an example:
SELECT PI( ), PI( ) + 0.0000000000;
+----------+----------------------+
| PI( ) | PI( ) + 0.0000000000 |
+----------+----------------------+
| 3.141593 | 3.1415926536 |
+----------+----------------------+
This function returns the result of raising the number given
in the first argument to the exponent given in the second argument.
It's an alias of [click here]. Here is an
example:
SELECT POW(2, 4);
+-----------+
| POW(2, 4) |
+-----------+
| 16.000000 |
+-----------+
This function returns the result of raising the number given
in the first argument to the power of the number given in the second
argument. It's an alias for [click here].
This function converts degrees to radians. Here is an
example:
SELECT RADIANS(180);
+-----------------+
| RADIANS(180) |
+-----------------+
| 3.1415926535898 |
+-----------------+
This function returns a random floating-point number from 0
to 1. A seed number may be passed as an argument to start the sequence
of random numbers at a different point. Here is an example:
SELECT RAND( ), RAND( );
+------------------+------------------+
| RAND( ) | RAND( ) |
+------------------+------------------+
| 0.29085519843814 | 0.45449978900561 |
+------------------+------------------+
Note that rerunning this statement with the same seed will
produce the same results. This type of sequence is properly known as a
pseudorandom number generator, and is generally not considered strong
enough for security purposes, but it is adequate for making random
choices among a set of alternatives.
ROUND(number[, precision]) | |
This function rounds a number given in the first argument to
the nearest integer. The number may be rounded to the number of
decimal places given in the second argument. Here is an
example:
SELECT ROUND(2.875), ROUND(2.875, 2);
+--------------+-----------------+
| ROUND(2.875) | ROUND(2.875, 2) |
+--------------+-----------------+
| 3 | 2.88 |
+--------------+-----------------+
This function returns –1 if the given number is a negative, 0
if it is zero, and 1 if it is positive. Here is an example:
SELECT SIGN(-5);
+----------+
| SIGN(-5) |
+----------+
| -1 |
+----------+
This function returns the sine of the number given, where
number is expressed in radians. Here is an
example:
SELECT SIN(.5 * PI( ));
+-----------------+
| SIN(.5 * PI( )) |
+-----------------+
| 1 |
+-----------------+
This function returns the square root of its input, which
must be a positive number. Here is an example:
SELECT SQRT(25);
+----------+
| SQRT(25) |
+----------+
| 5.000000 |
+----------+
This function returns the tangent of an angle, where
number is expressed in radians. It's the
reverse of [click here]. Here is an example:
SELECT ATAN2(1), TAN(0.785398);
+----------+---------------+
| ATAN2(1) | TAN(0.785398) |
+----------+---------------+
| 0.785398 | 1.000000 |
+----------+---------------+
This function returns a number equivalent to its first
argument, removing any digits beyond the number of decimal places
specified in the second argument. The function does not round the
number; use the [click here] function instead. If 0
is given for the second argument, the decimal point and the fractional
value are dropped. If a negative number is given as the second
argument, the decimal point and the fractional value are dropped, and
the number of positions given is zeroed out in the remaining integer.
Here is an example:
SELECT TRUNCATE(321.1234, 2) AS '+2',
TRUNCATE(321.1234, 0) AS '0',
TRUNCATE(321.1234, -2) AS '-2';
+--------+-----+-----+
| +2 | 0 | -2 |
+--------+-----+-----+
| 321.12 | 321 | 300 |
+--------+-----+-----+
Notice that for the first field in the results, the last two
decimal places are dropped. For the second field, the decimal point
and all of the fractional value are dropped. For the third field, the
decimal point and the fractional value are dropped, and because the
second parameter is –2, the two least significant digits (starting
from the right) of the integer are changed to zeros.
No comments:
Post a Comment