| The rest of the chapter describes each function with examples.
This function returns the absolute value of a given number. SELECT ABS(-10); +----------+ | ABS(-10) | +----------+ | 10 | +----------+
This function returns the arc cosine of a given number. For numbers given that are greater than 1 or less than -1, NULL is returned. SELECT ACOS(.5), ACOS(1.5); +----------+-----------+ | ACOS(.5) | ACOS(1.5) | +----------+-----------+ | 1.047198 | NULL | +----------+-----------+
This function returns the arc sine of a given number. For numbers given that are greater than 1 or less than -1, NULL is returned. SELECT ASIN(1); +----------+ | ASIN(1) | +----------+ | 1.570796 | +----------+
This function returns the arc tangent of a given number. To determine the arc tangent of two numbers (Y and X), add the optional second argument to the function or use ATAN2( ). The value of Y for a Cartesian plane is given as the first argument and X as the second. SELECT ATAN(2); +----------+ | ATAN(2) | +----------+ | 1.107149 | +----------+
This function returns the arc tangent 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 TAN( ). SELECT ATAN2(10, 5); +--------------+ | ATAN2(10, 5) | +--------------+ | 1.107149 | +--------------+
This function returns the average or mean of a set of numbers given as the argument. Use it in conjunction with GROUP BY clause. SELECT col1, AVG(col2) FROM table1 GROUP BY col1;
This function returns the bitwise AND of all elements in column. Use this in conjunction with the GROUP BY clause. The function has a 64-bit precision. If there are no matching rows, before Version 4.0.17 of MySQL, a -1 is returned. Newer versions return 184467440737039551615, which is the maximum value for an unsigned BIGINT column.
This function returns the bitwise OR of all elements in column. It calculates with a 64-bit precision. It returns 0 if no matching rows are found. Use it in conjunction with GROUP BY clause.
This function returns the bitwise XOR of all elements in column. It calculates with a 64-bit precision. It returns 0 if no matching rows are found. Use it in conjunction with the GROUP BY clause. This function is available as of Version 4.1.1 of MySQL.
This function rounds a given floating-point number up to the next higher integer. It's an alias to CEILING(). 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 CEIL().
This function returns cosine of number, where number is expressed in radians. SELECT COS(2 * PI( )); +---------------+ | COS(2 * PI( )) | +---------------+ | 1 | +---------------+
This function returns the cotangent of a number. SELECT COT(1); +------------+ | COT(1) | +------------+ | 0.64209262 | +------------+
This function returns the number of rows retrieved in the SELECT statement for the column given. Rows in which the column given is NULL are not counted. If the wildcard * is used as the argument, the function counts all rows, including ones with NULL values. Use this in conjunction with the GROUP BY clause. The second syntax returns the number of unique non-NULL values found. This function is set to optimal performance for MyISAM tables if a WHERE clause is not included in a statement. SELECT col1, COUNT(col2), COUNT(DISTINCT col2) FROM table1 GROUP BY col1;
This function returns a 32-bit unsigned value for the given string's cyclic redundancy check value. This function is available as of Version 4.1 of MySQL. SELECT CRC32('test'); +---------------+ | CRC32('test') | +---------------+ | 3632233996 | +---------------+
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 CEILING().
This function returns the floating-point number given 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 rounded the number given to two decimal places.
GREATEST(value, value, . . . ) | |
Use this function to compare two or more values and return the greatest value. In an INTEGER datatype context, all values are treated as integers for comparison. In a REAL datatype context, all values are treated as REAL values for comparison. If any parameter contains a case-sensitive string (i.e., with a BINARY flag), all values are compared as case-sensitive strings. SELECT GREATEST(col1, col2, col3);
GROUP_CONCAT([DISTINCT] expression[, . . . ] [ORDER BY {unsigned_integer|column|expression} [ASC|DESC] [,column . . . ]] [SEPARATOR character]) | |
This function returns non-NULL values of a group concatenated together, separated by commas. It returns NULL if the group doesn't contain non-NULL values. Duplicates are omitted with the DISTINCT flag. The ORDER BY clause for the function instructs MySQL to sort values before concatenating. Ordering may be based on an integer value, column, or expression. The sort order can be set to ascending with the ASC flag (default), or to descending with the DESC flag. To change the default separator of a comma, use the SEPARATOR flag followed by the preferred separator. This function is available as of Version 4.1 of MySQL. SELECT item_nbr AS Item, GROUP_CONCAT(quantity) AS Quantities FROM orders GROUP BY item_nbr LIMIT 1; +------+------------+ | Item | Quantities | +------+------------+ | 100 | 7,12,4,8 | +------+------------+
The results here are limited only to one item by the LIMIT clause. Notice that the quantities aren't sorted it's the item numbers that are sorted by the GROUP BY clause. To sort the quantities within each field and to use a different separator, enter something like the following instead: SELECT item_nbr AS Item, GROUP_CONCAT(quantity ORDER BY quantity ASC SEPARATOR '|') AS Quantities FROM table3 GROUP BY item_nbr; +------+------------+ | Item | Quantities | +------+------------+ | 100 | 4|7|8|12 | +------+------------+
LEAST(value, value, . . . ) | |
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 flag), all values are compared as case-sensitive strings. SELECT LEAST(col1, col2, col3);
This function returns the natural logarithm of a given number. SELECT LN(5); +----------+ | LN(5) | +----------+ | 1.609438 | +----------+
This function returns the natural logarithm of a given number. If a second argument is given, a natural logarithm is returned for the first argument for the arbitrary base given in the second argument. This is the same as using LOG(number)/LOG(base). SELECT LOG(5,4); +------------+ | LOG(5,4) | +------------+ | 1.16096405 | +------------+
This function returns the base-two logarithm of a given number.
This function returns the base 10 logarithm of a given number.
This function returns the lowest number in the values for column. Use this in conjunction with the GROUP BY clause. SELECT col1, MAX(col2) FROM table1 GROUP BY col1;
This function returns the lowest number in the values for column. Used in conjunction with the GROUP BY clause. SELECT col1, MIN(col2) FROM table1 GROUP BY col1;
MOD(number, number) number MOD number | |
This function returns the remainder of a number given in the first argument divided evenly by the number given in the second argument, the modulo. It 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. 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 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. 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 for POWER( ). 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 POW().
This function converts degrees to radians. SELECT RADIANS(180); +-----------------+ | RADIANS(180) | +-----------------+ | 3.1415926535898 | +-----------------+
This function returns a random floating-point number from 0 to 1. A seed number may given with the function, but the results will be the same each time. SELECT RAND( ), RAND( ); +------------------+------------------+ | RAND( ) | RAND( ) | +------------------+------------------+ | 0.29085519843814 | 0.45449978900561 | +------------------+------------------+
Note that rerunning this statement with the same seeds will produce the same results.
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. 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 and 1 if it is positive. SELECT SIGN(-5); +----------+ | SIGN(-5) | +----------+ | -1 | +----------+
This function returns the sine of the number given, where number is expressed in radians. SELECT SIN(.5 * PI( )); +----------------+ | SIN(.5 * PI( )) | +----------------+ | 1 | +----------------+
This function returns the square root of a given, positive number. SELECT SQRT(25); +----------+ | SQRT(25) | +----------+ | 5.000000 | +----------+
This function returns the standard deviation of the column given. Use it in conjunction with the GROUP BY clause. It's an alias for STDDEV( ). SELECT col1, STD(col2) FROM table1 GROUP BY col1;
This function returns the standard deviation of the column given. Use it in conjunction with the GROUP BY clause. It's an alias for STD( ).
This function returns the sum of the value of the column given. Use it in conjunction with the GROUP BY clause. SELECT col1, SUM(col2) FROM table1 GROUP BY col1;
This function returns the tangent of an angle, of a given number in radians. It's the reverse of ATAN2(). SELECT ATAN2(1), TAN(0.785398); +----------+---------------+ | ATAN2(1) | TAN(0.785398) | +----------+---------------+ | 0.785398 | 1.000000 | +----------+---------------+
This function returns a number given in the first argument with the digits beyond the number of decimal places specified in the second argument, truncated. It does not round the number use the ROUND( ) function instead. If a 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 is dropped, and the number of positions given is zeroed out for the integer. 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, 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, the decimal point and the fractional value are dropped, and because the second parameter is -2, the first two numbers (starting from the right) of the integer are changed to zeros.
The function returns the standard variance of column, based on the rows selected as a whole population. Use it in conjunction with the GROUP BY clause. This function is available as of Version 4.1 of MySQL. SELECT col1, VARIANCE(col2) FROM table1
GROUP BY col1;
| 