Difference between revisions of "SQL Functions"
From AgileApps Support Wiki
imported>Aeric |
imported>Aeric |
||
| Line 8: | Line 8: | ||
| '''IFNULL(''expr1,expr2'')''' || If ''expr1'' is not NULL, return ''expr1''; Otherwise, return ''expr2''. | | '''IFNULL(''expr1,expr2'')''' || If ''expr1'' is not NULL, return ''expr1''; Otherwise, return ''expr2''. | ||
|} | |} | ||
:''Learn more:'' [ MySQL Logical Functions] | |||
===String Functions=== | ===String Functions=== | ||
| Line 15: | Line 16: | ||
| '''LENGTH(''str'')''' || Returns the length of the string, in bytes. (A mult-byte character counts as multiple bytes.) | | '''LENGTH(''str'')''' || Returns the length of the string, in bytes. (A mult-byte character counts as multiple bytes.) | ||
|} | |} | ||
:''Learn more:'' [ MySQL String Functions] | |||
===Mathematical Functions=== | ===Mathematical Functions=== | ||
| Line 23: | Line 25: | ||
|} | |} | ||
''Learn more:'' [http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html MySQL Mathematical Functions] | :''Learn more:'' [http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html MySQL Mathematical Functions] | ||
===Date Functions=== | ===Date Functions=== | ||
| Line 85: | Line 87: | ||
| '''YEAR''' || | | '''YEAR''' || | ||
|} | |} | ||
:''Learn more:'' [ MySQL Date Functions] | |||
===Aggregate Functions=== | ===Aggregate Functions=== | ||
| Line 106: | Line 109: | ||
| '''VARIANCE(''expr'')''' || Returns the standard variance of ''expr''. | | '''VARIANCE(''expr'')''' || Returns the standard variance of ''expr''. | ||
|} | |} | ||
:''Learn more:'' [http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html MySQL Aggregate Functions] | |||
Revision as of 20:42, 11 November 2011
These functions can be used in your SQL statements.
- Legend
-
- [ x ] - Optional (one or none)
- [, x ... ] - Optional additional values, in a comma-separated list
- x | y - Choose one. Curly braces are added when needed: { x | y } ...
- CAPITALIZED - SQL Keyword (case insensitive)
- italicized - Value you supply. (Table names and column names are case-sensitive)
Logical Functions
IF(expr1,expr2,expr3) If expr1 is not zero and not null, return expr2. Otherwise, return expr3. IFNULL(expr1,expr2) If expr1 is not NULL, return expr1; Otherwise, return expr2.
- Learn more: [ MySQL Logical Functions]
String Functions
CONCAT(str1,str2,...) Concatenates the strings. Returns the result. LENGTH(str) Returns the length of the string, in bytes. (A mult-byte character counts as multiple bytes.)
- Learn more: [ MySQL String Functions]
Mathematical Functions
ROUND(x) Round x up or down to the nearest integer value. ROUND(x, d) Round xto d decimal places.
- Learn more: MySQL Mathematical Functions
Date Functions
DATE_ADD DATEDIFF CURDATE NOW CURTIME DATE_FORMAT DATE_SUB DATE DAYOFMONTH DAYNAME DAYOFWEEK DAYOFYEAR EXTRACT FROM_UNIXTIME HOUR LAST_DAY MINUTE MONTH MONTHNAME SEC_TO_TIME SECOND STR_TO_DATE SUBTIME TIME_TO_SEC TIME TIMEDIFF TO_DAYS UNIX_TIMESTAMP YEAR
- Learn more: [ MySQL Date Functions]
Aggregate Functions
SUM(expr)
SUM(DISTINCT expr)Returns the sum of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values. AVG(expr)
AVG(DISTINCT expr)Returns the average of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values. COUNT(expr) Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement Returns 0 if there were no matching rows. COUNT(*) Returns a count of rows, whether or not they contain NULL values. COUNT(DISTINCT
expr [, expr ...])Returns a count of rows with different non-NULL expr values. MAX(expr)
MAX(DISTINCT expr)Returns the maximum of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values. MIN(expr)
MIN(DISTINCT expr)Returns the maximum of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values. STD(expr) Returns the standard deviation of expr. VARIANCE(expr) Returns the standard variance of expr.
- Learn more: MySQL Aggregate Functions