View source for MySQL Mathematical Functions

<table border="0" cellspacing="0" width="100%">
<tr>
<td width="20%">[[Manipulating Text in MySQL|Previous]]<td align="center">[[MySQL Essentials|Table of Contents]]<td width="20%" align="right">[[Working with Dates and Times in MySQL|Next]]</td>
<tr>
<td width="20%">Manipulating Text in MySQL<td align="center"><td width="20%" align="right">Working with Dates and Times in MySQL</td>
</table>
<hr>


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):

<google>ADSDAQBOX_FLOW</google>
<table border="1" cellspacing="0">
<tr style="background:#efefef;">
<th><span class="bold"><strong>Name</strong></span></th>
<th><span class="bold"><strong>Description</strong></span></th>
</tr>
<tr>
<td>ABS()</td>
<td>Returns the absolute value</td>

</tr>
<tr>
<td>ACOS()</td>
<td>Returns the arc cosine</td>
</tr>
<tr>
<td>ASIN()</td>
<td>Returns the arc sine</td>
</tr>
<tr>
<td>
ATAN2(), ATAN()

</td>
<td>Returns the arc tangent of the two arguments</td>
</tr>
<tr>
<td>ATAN()</td>
<td>Returns the arc tangent</td>
</tr>
<tr>
<td>/</td>
<td>Division operator</td>
</tr>
<tr>

<td>
CEILING(), CEIL()
</td>
<td>Returns the smallest integer value not less than the argument</td>
</tr>
<tr>
<td>COS()</td>
<td>Returns the cosine</td>
</tr>
<tr>
<td>COT()</td>

<td>Returns the cotangent</td>
</tr>
<tr>
<td>
CRC32()()</td>
<td>Compute a cyclic redundancy check value</td>
</tr>
<tr>
<td>DEGREES()</td>
<td>Convert radians to degrees</td>
</tr>

<tr>
<td>
DIV()</td>
<td>Integer division</td>
</tr>
<tr>
<td>EXP()</td>
<td>Raise to the power of of the argument</td>
</tr>
<tr>
<td>FLOOR()</td>

<td>Returns the largest integer value not greater than the argument</td>
</tr>
<tr>
<td>MOD()</td>
<td>Returns the remainder</td>
</tr>
<tr>
<td>LN()</td>
<td>Returns the natural logarithm of the argument</td>
</tr>
<tr>
<td>LOG10()</td>

<td>Returns the base-10 logarithm of the argument</td>
</tr>
<tr>
<td>LOG2()</td>
<td>Returns the base-2 logarithm of the argument</td>
</tr>
<tr>
<td>LOG()</td>
<td> Returns the natural logarithm of the first argument  </td>
</tr>
<tr>

<td>-</td>
<td>Minus operator</td>
</tr>
<tr>
<td>%</td>
<td>Modulo operator</td>
</tr>
<tr>
<td>PI()</td>
<td>Returns the value of pi</td>
</tr>

<tr>
<td>+</td>
<td>Addition operator</td>
</tr>
<tr>
<td>
POW(), POWER()
</td>
<td>Returns the argument raised to the specified power</td>
</tr>
<tr>

<td>RADIANS()</td>
<td>Returns argument converted to radians</td>
</tr>
<tr>
<td>RAND()</td>
<td>Returns a random floating-point value</td>
</tr>
<tr>
<td>ROUND()</td>
<td>Rounds the argument</td>
</tr>

<tr>
<td>SIGN()</td>
<td>Returns the sign of the argument</td>
</tr>
<tr>
<td>SIN()</td>
<td>Returns the sine of the argument</td>
</tr>
<tr>
<td>SQRT()</td>
<td>Returns the square root of the argument</td>

</tr>
<tr>
<td>TAN()</td>
<td>Returns the tangent of the argument</td>
</tr>
<tr>
<td>*</td>
<td>Times operator</td>
</tr>
<tr>
<td>TRUNCATE()</td>
<td>Truncates to specified number of decimal places specified in the argument</td>

</tr>
<tr>
<td>-</td>
<td>Change the sign of the value passed as an argument</td>
</tr>
</table>

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:

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

To perform a modulo operation:

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

Or, to raise 2 to the power 3:

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

== 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|working with dates and times in MySQL]].


<hr>
<table border="0" cellspacing="0" width="100%">
<tr>
<td width="20%">[[Manipulating Text in MySQL|Previous]]<td align="center">[[MySQL Essentials|Table of Contents]]<td width="20%" align="right">[[Working with Dates and Times in MySQL|Next]]</td>
<tr>
<td width="20%">Manipulating Text in MySQL<td align="center"><td width="20%" align="right">Working with Dates and Times in MySQL</td>
</table>