A function is a MySQL command used in an SQL statement that takes one or more arguments and returns a value based on the values supplied. Just as with expressions, you can use a function anywhere a fixed value could otherwise be used, or to manipulate the value returned in a database column.
## Random NumbersTo generate a random number, use the ` mysql> SELECT RAND(), RAND(); +------------------+------------------+ | RAND() | RAND() | +------------------+------------------+ | 0.96684006784748 | 0.27750918760664 | +------------------+------------------+ 1 row in set (0.00 sec) ` In the previous example, two different calls to
An optional argument to
## Rounding FunctionsThis set of functions enables you to round decimal numbers in a particular way. Each of these functions takes a single decimal number argument and returns an integer result.
The ` mysql> SELECT ROUND(29.21), ROUND(32.76); +--------------+--------------+ | ROUND(29.21) | ROUND(32.76) | +--------------+--------------+ | 29 | 33 | +--------------+--------------+ 1 row in set (0.00 sec) ` If you supply a second argument, the number will be rounded to that amount of decimal places instead of an integer. ` mysql> SELECT ROUND(29.21, 1), ROUND(32.76, 1); +-----------------+-----------------+ | ROUND(29.21, 1) | ROUND(32.76, 1) | +-----------------+-----------------+ | 29.2 | 32.8 | +-----------------+-----------------+ 1 row in set (0.00 sec) ` The ` mysql> SELECT CEILING(29.21), FLOOR(29.21); +----------------+--------------+ | CEILING(29.21) | FLOOR(29.21) | +----------------+--------------+ | 30 | 29 | +----------------+--------------+ 1 row in set (0.00 sec) `
The ` mysql> SELECT TRUNCATE(12.34567, 3); +-----------------------+ | TRUNCATE(12.34567, 3) | +-----------------------+ | 12.345 | +-----------------------+ 1 row in set (0.00 sec) ` ## Mathematical FunctionsThis set of functions enables you to perform mathematical operations in a query. To raise a number to a power, use the ` mysql> SELECT POW(2,2), POW(2,3), POW(2,4); +----------+----------+----------+ | POW(2,2) | POW(2,3) | POW(2,4) | +----------+----------+----------+ | 4 | 8 | 16 | +----------+----------+----------+ 1 row in set (0.00 sec) ` To find the square root of a number, use ` mysql> SELECT SQRT(POW(8,2)); +-----------------+ | SQRT(POW(8, 2)) | +-----------------+ | 8 | +-----------------+ 1 row in set (0.00 sec) ` To find the logarithm of a number, use the In the following example, the first column shows the value of e. The second column performs a natural logarithm on this value. mysql> SELECT EXP(1), LOG(EXP(1)); +----------------+-------------+ | EXP(1) | LOG(EXP(1)) | +----------------+-------------+ | 2.718281828459 | 1 | +----------------+-------------+ 1 row in set (0.00 sec) When called with two arguments, the ## Trigonometry FunctionsMySQL supports trigonometry functions to calculate the sine, cosine, and tangent of a value using The argument to these functions should be a value in radians. The following examples use ` mysql> SELECT SIN(PI()/2), COS(2*PI()), TAN(PI()/4); +-------------+-------------+-------------+ | SIN(PI()/2) | COS(2*PI()) | TAN(PI()/4) | +-------------+-------------+-------------+ | 1 | 1 | 1 | +-------------+-------------+-------------+ 1 row in set (0.00 sec) `
To convert a value in degrees to radians, use ` mysql> SELECT DEGREES(PI()/2), RADIANS(180); +-----------------+-----------------+ | DEGREES(PI()/2) | RADIANS(180) | +-----------------+-----------------+ | 90 | 3.1415926535898 | +-----------------+-----------------+ 1 row in set (0.00 sec) ` |

Sams Teach Yourself MySQL in 10 Minutes

ISBN: 0672328631

EAN: 2147483647

EAN: 2147483647

Year: 2006

Pages: 165

Pages: 165

Authors: Chris Newman

Similar book on Amazon

flylib.com © 2008-2017.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net