MySQL Mathematical Functions

Revision as of 17:11, 29 October 2007 by Neil (Talk | contribs)

Revision as of 17:11, 29 October 2007 by Neil (Talk | contribs)

PreviousTable of ContentsNext
Manipulating Text in MySQLWorking with Dates and Times in MySQL


MySQL provides a a number of functions used for performing algebraic, geometric and trigonometric calculations on database data. In this chapter the basics of using this functions will be covered.

MySQL Arithmetic Functions

MySQL provides a wide range of functions designed to serve a range of arithmetical purposes. The following table lists each of the common functions together with a brief description (although most are self-explanatory):

Name Description
ABS() Returns the absolute value
ACOS() Returns the arc cosine
ASIN() Returns the arc sine

ATAN2(), ATAN()

Returns the arc tangent of the two arguments
ATAN() Returns the arc tangent
/ Division operator

CEILING(), CEIL()

Returns the smallest integer value not less than the argument
COS() Returns the cosine
COT() Returns the cotangent
CRC32()() Compute a cyclic redundancy check value
DEGREES() Convert radians to degrees
DIV() Integer division
EXP() Raise to the power of of the argument
FLOOR() Returns the largest integer value not greater than the argument
MOD() Returns the remainder
LN() Returns the natural logarithm of the argument
LOG10() Returns the base-10 logarithm of the argument
LOG2() Returns the base-2 logarithm of the argument
LOG() Returns the natural logarithm of the first argument
- Minus operator
% Modulo operator
PI() Returns the value of pi
+ Addition operator

POW(), POWER()

Returns the argument raised to the specified power
RADIANS() Returns argument converted to radians
RAND() Returns a random floating-point value
ROUND() Rounds the argument
SIGN() Returns the sign of the argument
SIN() Returns the sine of the argument
SQRT() Returns the square root of the argument
TAN() Returns the tangent of the argument
* Times operator
TRUNCATE() Truncates to specified number of decimal places specified in the argument
- Change the sign of the value passed as an argument

As with most MySQL functions it is easy to experimenbt with them without having to access data in a table simply using the SELECT statement. For example, to see the PI() function in action:

mysql> SELECT PI();
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

To perform a modulo operation:

mysql> SELECT 4 % 3;
+-------+
| 4 % 3 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

Or, to raise 2 to the power 3:

mysql> SELECT POW(2, 3);
+-----------+
| POW(2, 3) |
+-----------+
|         8 |
+-----------+
1 row in set (0.00 sec)

Summary

Given the scientific nature of many of the numeric functions provided with MySQL it is unlikely that many of them get used very much. That said, it is reassuring to know that the capabilities are there if you ever need them.

Now that we have looked at text and number manipulation functions we will now move on to look at working with dates and times in MySQL.



PreviousTable of ContentsNext
Manipulating Text in MySQLWorking with Dates and Times in MySQL