:: Intro :: Datatypes :: DDL :: DML :: Operators :: Built-ins ::

Column Functions

These used in Select or Having clauses. Arguments in brackets, arguments can be col_names or other expressions that can be resolved ordinally. NO Nesting allowed !

     COUNT( )           the number of rows
     SUM( )             the sum of the values of the named column
     AVG( )             the average of the values of the named column
     MAX( )             the largest/latest value in the named column
     MIN( )             the smallest/earliest value in the named column
     SDEV( )            the standard deviation of values in named column

A full list of Maths functions available in MySQL is available in the MySQL manual

Scalar Functions

These functions allow you to manipulate the data that appears on each row of the answer table

     CAST(colName/expression AS datatype[:n][L/R][fillCharacter])
                        allows you to convert, specify a field width (n),
                        specify justification (L/R) and a fill character

     CHAR_LENGTH(charExpression)
                        returns the number of characters minus 
                        trailing spaces

     LOWER(charExpression)
     UPPER(charExpression)
                        returns either the capitalized or lowercase
                        version of the expression or character column

     POSITION(substring IN charExpression)
                        returns the character number of the starting
                        position of the substring, or 0 if absent

     SUBSTRING(charExpression FROM x [FOR n])
                        returns a substring of charExpression starting
                        at character x, going for n characters.           
                        If the FOR n is omitted, all trailing 
                        chars are assumed.
     TRIM(charExpression)
     STRIP(charExpression)
                        returns the chars without any trailing spaces

     CHR(numExpression) converts decimal information into character

     DEC(charExpression)converts character information into decimal

     INT(numExpression) returns the whole number part of a decimal

     FRAC(numExpression)returns the fractional part of a decimal

Date Variables/Functions

These functions are reliable assuming the computers clock is set correctly

     curdate()+0 returns date in form char   yyyymmdd   e.g. 20040131
     year(now()) returns year in form dec    yyyy       e.g. 2005
     curtime()   returns time in form char   hh:mm:ss   e.g. 03:10:35
     date_format(date, format) allows you to specify how you want to see
                               the date - there are many format codes - see
                               them all in the MySQL manual

Using dates in a MySQl database are a powerful way to store information - there are many builtin-s that allow us to extract data from a date, and operate on the date in a calendar-aware way (knowing that not all months have 31 days in them) - It is worth exploring the date/time built-ins.

:: Intro :: Datatypes :: DDL :: DML :: Operators :: Built-ins ::