4_Functions

SQL Syntax II: Functions
Peter Komisar © Conestoga College version 1.0

reference: MySQL Reference Manual. MySQL Structured Query
Language(SQL) ,D.Geller, R. Roselius, Danielle Waleri, DDC
Publishing, Core MySQL, Leon Atkinson, Prentice-Hall Publishing,

Escape Sequences

Sometimes we want to include in our data characters
that have a significance to the database. Within strings
they are able to 'escape' this meaning using escape
sequences. Each of these sequences begins with a
backslash (‘\’) and is known as an escape character.
Escapes are case sensitive. That is, for example,
\B does not stand for a backspace character.

MySQL Escape Sequences

\0 // ASCII 0 (NUL) character
\' // single quote (‘'’) character
\" // double quote (‘"’) character
\b // backspace character
\n // newline (linefeed) character
\r // carriage return character
\t // tab character
\Z // ASCII 26 (Control-Z) *
\\ // backslash (‘\’) character
\% // ‘%’ character
\_ // ‘_’ character

"For all other escape sequences, backslash is ignored.
That is, the escaped character is interpreted as if it was
not escaped. For example, ‘\x’ is just ‘x’. " - MySQL Manual

The backslash is ignored, therefore ‘\B’ is interpreted as ‘B’.

The following example from the MySQL manual shows
several ways to escape quotes.

Example

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
1 row in set (0.00 sec)

The following example from the MySQL manual shows
how the backslash is ignored. Notice the backslash is
before a space, not before the single comma.

mysql> select 'Invisible\ ';
+------------+
| Invisible |
+------------+
| Invisible |
+------------+
1 row in set (0.01 sec)

Operators

We have seen a number of operators in use in
last sessions study using SELECT statements. For
completeness we list all the operators used in
MySQL below.

MySQL Operators // from the MySQL Manual

Name	Description
`AND`, `&&`	Logical AND
`BINARY`	Cast a string to a binary string
`&`	Bitwise AND
`\|`	Bitwise OR
`^`	Bitwise XOR
`/`	Division operator
`DIV` (v4.1.0)	Integer division
`<=>`	NULL-safe equal to operator
`=`	Equal operator
`>=`	Greater than or equal operator
`>`	Greater than operator
`IS NULL`	NULL value test
`IS`	Test a value against a boolean
`<<`	Left shift
`<=`	Less than or equal operator
`<`	Less than operator
`LIKE`	Simple pattern matching
`-`	Minus operator
`%`	Modulo operator
`!=`, `<>`	Not equal operator
`NOT LIKE`	Negation of simple pattern matching
`NOT REGEXP`	Negation of REGEXP
`NOT`, `!`	Negates value
`\|\|`, `OR`	Logical OR
`+`	Addition operator
`REGEXP`	Pattern matching using regular expressions
`>>`	Right shift
`RLIKE`	Synonym for REGEXP
`SOUNDS LIKE` (v4.1.0)	Compare sounds
`~`	Invert bits
`*`	Times operator
`-`	Change the sign of the argument
`XOR`	Logical XOR

Operator Precedence

It often happens that operators are used together with other
operators. The issue of operator precedence comes up.
Which operator gets to go first? Like other programming
languages, SQL defines a table that controls order of
precedence.

You are familiar with this idea in a simple Math expression.
You know the following expression evaluates to 12 times
plus 8 or 20, because multiplication takes precedence
over addition.

Example

8 + 6 * 2. // evaluates to 20

// SQL uses the asterisk as the symbol for multiplication

We can use a round brackets to allow the addition to go
first.

Example

(8 +6) * 2 // evaluates to 14 times 2 or 28

The MySQL (version 5.0.2) operators are shown below
listed from lowest to highest priority. .Operator precedences
are shown here, from lowest to highest precedence.
Operators with the same precedence are shown on the
same line.

MySQL Precedence Lowest to Highest // version 5.0.2

:=
||, OR, XOR
&&, AND
NOT // as of 5.0.2
BETWEEN, CASE, WHEN, THEN, ELSE
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
&
<<, >>
-, +
*, /, DIV, %, MOD
^
- (unary minus), ~ (unary bit inversion)
!
BINARY, COLLATE
( ) *

* Round braces were not on MySQL manual list but
in other languages they usually are shown having the
highest priority.

Example

mysql> select (8+3)*2;
+---------+
| (8+3)*2 |
+---------+
| 22 |
+---------+
1 row in set (0.00 sec)

mysql> select 8+3*2;
+-------+
| 8+3*2 |
+-------+
| 14 |
+-------+
1 row in set (0.00 sec)

Operator Details

Explicit Assignment

the = symbol can be used for both assignment and
equality in which case it has different precedence.
the := form is an explicit form of assignment, useful
to remove ambiguity in an expression.

XOR

XOR or Exclusive or is a boolean operator we didn't
cover in the last note. It serves to exclude the case
in an OR comparison where both conditions are true.

Example

mysql> select TRUE XOR TRUE;
+---------------+
| TRUE XOR TRUE |
+---------------+
| 0 |
+---------------+
1 row in set (0.02 sec)

Note in the above example we supplied the data for
the select to work on at the command line and are
not even referencing a table. This is handy for to
demonstrate the functions and operators of a
database.

Type Conversion

Operands are the values operators work on. If these
operands are different types, say a string and an
number types are converted as are appropriate.
The following examples show this.

In the first case a number is added to the string
version of a number. Here the string is converted
to a number so that it can be added.

Example

mysql> Select 7 + '7';
+---------+
| 7 + '7' |
+---------+
| 14 |
+---------+
1 row in set (0.00 sec)

In the second case a string is concatenated to
a number value. Here the number value is converted
to a string.

Example

mysql> Select CONCAT('Agent 00',7);
+----------------------+
| CONCAT('Agent 00',7) |
+----------------------+
| Agent 007 |
+----------------------+
1 row in set (0.00 sec)

A conversion on number can be made using a
concatenation of the number with an empty string.

Example

mysql> select concat('',3456);
+-----------------+
| concat('',3456) |
+-----------------+
| 3456 |
+-----------------+
1 row in set (0.00 sec)

The NULL-Safe Operator

As mentioned in the last note, If one or both arguments
are NULL, the result of the comparison is

NULL. The 

NULL-safe operator
is the exception and can be used 

to compare for null.

For NULL <=> NULL, the result is true.

In the first of the following examples, a regular
equality test is made and the result is NULL as
stated above.

Example

mysql> select NULL=NULL;
+-----------+
| NULL=NULL |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)

The NULL-safe operator <=> makes comparison
for NULL values possible.

Example

mysql> select NULL<=>NULL;
+-------------+
| NULL<=>NULL |
+-------------+
| 1 |
+-------------+
1 row in set (0.01 sec)

A Couple More Conversion Rules

If both arguments are integers,
they are compared as integers.

Hexadecimal values are treated as binary
strings if not compared to a number.

The second point is interesting. MySQL supports
hexadecimal values. These are numbers that
count from zero to 15 before starting another
column. ( 10, 11, 12, 13,14 and 15 are A,B,C,D,E
and F respectively.)

MySQL supports hexadecimal values. Consider
the value '4F3B2A'. If we select for this value as
is, it returns itself.

Example

mysql> select '4F3B2A';
+--------+
| 4F3B2A |
+--------+
| 4F3B2A |
+--------+
1 row in set (0.00 sec)

Using the x symbol before the string causes the
string to be interpreted as 2 byte binary characters.
That is the characters corresponding with 4F, 3B
and 2A.

Example

mysql> select x'4F3B2A';
+-----------+
| x'4F3B2A' |
+-----------+
| O;* |
+-----------+
1 row in set (0.00 sec)

// same result using select 0x4FB2A;

Placing the value in a numeric context, by adding
it to 0 makes string act as a true hexadecimal
number.

Example

mysql> select 0x4F3B2A+0; // 0x or x interchangeable
+------------+
| 0x4F3B2A+0 |
+------------+
| 5192490 |
+------------+
1 row in set (0.00 sec)

Adding a number to a zero has the same
effect as casting the value to a number.
Following is a functional equivalent of adding
a zero. First the hexadecimal value, 4D
is added to zero.

Example

mysql> select x'4D'+0;
+---------+
| x'4D'+0 |
+---------+
| 77 |
+---------+
1 row in set (0.00 sec)

Following is the equivalent form using a CAST
method.

Example

mysql> select x'4D', CAST(0x4D AS UNSIGNED);
+-------+------------------------+
| x'4D' | CAST(0x4D AS UNSIGNED) |
+-------+------------------------+
| M | 77 |
+-------+------------------------+
1 row in set (0.01 sec)

CAST is an example of a built-in database function.
We now survey a set of database functions.

Functions

Functions are routines that are built into databases
that can be invoked by users to manipulate data
retrieved from database tables. Functions return
values which are based on input parameters. The
usual form is to pass parameters into functions
inside the round braces which follow the function's
name. The is no space between the function name
and the round braces.

Example of a Function

name( ) // no space between name and round braces

Functions are often used inside the context of
SELECT statements so we see them in examples
most often associated with selects.

Classifying Functions

Functions may be classified as falling into different
categories such as character manipulation, numeric
calculations, date processing, data conversion and
substitution functions, ( where values are replaced
with other values.) Other categories include debugging
and configuration functions. Further there are unique
functions which we can refer to as miscellaneous
functions.

Database Function Support is Generally the Same
However Specifics between Databases Vary Greatly

While all databases support generally the same kinds
of functions, their names and forms will vary between
databases. We default to the function set supported
by MySQL.

While we survey the use of functions in isolation
following is at least one hypothetical example
of a function used with a query on a table.

Example of a Function Used in Conjunction with
a Select Query on a Table

SELECT CONCAT(last_name,', ',first_name) 
AS full_name  FROM mytable ORDER BY full_name;

MySQL Function Survey

MySQL Functions cover many areas. Following off
of the operators are operator like functions.

Comparison Functions

COALESCE( values )

check for null values.

GREATEST( value1, value2. . .)

checks for greatest value

Example

mysql> select greatest(1,3,5,23,6,85,3);
+---------------------------+
| greatest(1,3,5,23,6,85,3) |
+---------------------------+
| 85 |
+---------------------------+
1 row in set (0.00 sec)

expression IN(value1, value2 . . . )

if value stated in expression is in argument list
a 1 is returned otherwise a 0 is returned
NOT can precede the function negating the action

Example

mysql> select 3 IN(1,3,5,23);
+----------------+
| 3 IN(1,3,5,23) |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)

ISNULL(value)

if value is not null returns 0
if value is null returns 1

Example

mysql> select ISNULL(1);
+-----------+
| ISNULL(1) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)

mysql> select ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
| 1 |
+-------------+
1 row in set (0.01 sec)

// left out INTERVAL( )

LEAST( value1,value2 ...)

Example

mysql> select least('X','Y','Z');
+--------------------+
| least('X','Y','Z') |
+--------------------+
| X |
+--------------------+
1 row in set (0.00 sec)

Control Flow Functions

There is a small set of flow control functions
in MySQL which are a little complex to use.
To keep from getting bogged down in our
survey, we pass on looking at them for now.
Just for the record they are listed below.

Name	Description
`CASE`	Case statement
`IF()`	If/else construct
`IFNULL()`	Null if/else construct
`NULLIF()`	Return NULL if expr1 = expr2

String Functions

There are a large number of String Functions.
Just to scope out what's available they are listed
below. ( Some functions listed which do not have
argument are omitted and are considered
operators.)

MySQL String Function Summary

ASCII()         BIN()         BIT_LENGTH()
CHAR_LENGTH()   CHAR()    CHARACTER_LENGTH()
CONCAT_WS()     CONCAT()    CONV()
ELT()           EXPORT_SET() FIELD()
FIND_IN_SET()   FORMAT()      HEX()
INSERT()        INSTR()       IS NULL( )
LCASE()/LOWER() LEFT()        LENGTH()/OCTET_LENGTH()
LIKE *          LOAD_FILE()   LOCATE()
LOWER() LPAD()        LTRIM()
MAKE_SET()      MID()         OCT()
ORD()           QUOTE        POSITION()/LOCATE()
REGEXP *        REPEAT()      REPLACE()
REVERSE()       RIGHT()       RLIKE // same as REGEXP
RPAD()          RTRIM()       SOUNDEX()
SPACE()         STRCMP()      SUBSTRING_INDEX()
TRIM()    RIM()         SUBSTRING()/SUBSTR()
UNHEX()         UPPER()/UCASE()

Following we do a selection of examples.

BIN(number )

BIN( ) returns a binary representation of a number
provided as a string argument.

Example

mysql> select BIN('33');
+-----------+
| BIN('33') |
+-----------+
| 100001    |
+-----------+
1 row in set (0.01 sec)

CHAR_LENGTH( )

CHAR_LENGTH( ) returns the number of characters
in a string.

Example

mysql> select CHAR_LENGTH('MISSISSIPPI');
+----------------------------+
| CHAR_LENGTH('MISSISSIPPI') |
+----------------------------+
|                         11 |
+----------------------------+
1 row in set (0.00 sec)

CONCAT(string1, string2 . . . )

CONCAT( ) concatenates the strings supplied as
arguments.

Example

mysql> select concat('Mr','. ','Higgins');
+-----------------------------+
| concat('Mr','. ','Higgins') |
+-----------------------------+
| Mr. Higgins                 |
+-----------------------------+
1 row in set (0.02 sec)

HEX(number or string )

HEX when used with a number returns the the
hexadecimal representation of that number. In
the following example 255 is in hex 'FF'.

Example

mysql> select HEX(255);
+----------+
| HEX(255) |
+----------+
| FF       |
+----------+
1 row in set (0.00 sec)

INSERT(string,position,length,target_string)

INSERT can be used to place a string into
another string, controlling where it is put and
to which extent it adds to or replaces characters
in the target string.

In the following example, at the 10th character,
the underscore, the string ' Initial ', carrying it's
own leading and trailing space is inserted. The
length argument here set to '1' specifies that one
character that is the underscore is replaced.

Example

mysql> SELECT INSERT('FirstName_LastName',10,1,' Initial ');
+-----------------------------------------------+
| INSERT('FirstName_LastName',10,1,' Initial ') |
+-----------------------------------------------+
| FirstName Initial LastName                    |
+-----------------------------------------------+
1 row in set (0.00 sec)

LOWER( string ) & UPPER(string) // same as LCASE & UCASE

LOWER is the same as LCASE and puts a string into
lower case letters.

Example

mysql> SELECT LOWER('STOP');
+---------------+
| LOWER('STOP') |
+---------------+
| stop          |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT UPPER('go');
+-------------+
| UPPER('go') |
+-------------+
| GO          |
+-------------+
1 row in set (0.00 sec)

LPAD(string, length, pad_string)

LPAD left-pads a string with the selected
pad string. The number is the length of the
target string plus the number of characters
to the left. ( Vacant is 6 characters. 9 - 6
is 3 which is how many times the pad is
used to fill the string length.)

Example

mysql> Select LPAD('VACANT',9,'|');
+----------------------+
| LPAD('VACANT',9,'|') |
+----------------------+
| |||VACANT            |
+----------------------+
1 row in set (0.00 sec)

LTRIM(string) // RTRIM ( ) trims trailing spaces

The LTRIM( ) function trims leading spaces.

Example

mysql> SELECT LTRIM('      shorter');
+------------------------+
| LTRIM('      shorter') |
+------------------------+
| shorter                |
+------------------------+
1 row in set (0.00 sec)

REVERSE(string)

The REVERSE function returns a string
with characters in reverse order.

Example

mysql> SELECT REVERSE('123456789');
+----------------------+
| REVERSE('123456789') |
+----------------------+
| 987654321 |
+----------------------+
1 row in set (0.00 sec)

SPACE( number )

SPACE( ) returns a string with the specified
number of spaces.

Example

mysql> select space(9);
+-----------+
| space(9) |
+-----------+
|           |
+-----------+
1 row in set (0.00 sec)

TRIM( )

TRIM offers elaborate space removal. In it's
simple form it trims both leading and trailing
spaces.

Example

mysql> select trim('      xyz     ');
+------------------------+
| trim('      xyz     ') |
+------------------------+
| xyz                    |
+------------------------+
1 row in set (0.00 sec)

MySQL Numeric Functions

Following are a list of the numeric functions offered
in MySQL 5. It is basically the stuff you would get
from a typical Math calculator.

List of MySQL Numeric Functions

ABS()        ACOS()         ASIN()
ATAN2()      ATAN()         ATAN()
CEILING() CEIL() COS()
COT()        CRC32()        DEGREES()
EXP()    FLOOR()        LN()
LOG10()    LOG2()         LOG()
MOD()    PI()           POW()
POWER()    RADIANS()      RAND()
ROUND()    SIGN()         SIN()
SQRT()       TAN()          TRUNCATE()

We can look at a few examples that we might
commonly use.

FLOOR( number )

FLOOR( ) supplies the non-fractional part of a
value that is lower than the value supplied as an
argument. Notice the floor works on negative
numbers. You can think of floor( ) as always
rounding down.

Example

mysql> select floor(1.25);
+-------------+
| floor(1.25) |
+-------------+
|           1 |
+-------------+
1 row in set (0.02 sec)

mysql> select floor(1.9);
+------------+
| floor(1.9) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> select floor(-2.6);
+-------------+
| floor(-2.6) |
+-------------+
|          -3 |
+-------------+
1 row in set (0.00 sec)

MOD(integer, divisor)

MOD returns the remainder of a division.
MOD is usually used with integral numbers.
In the following example 4 divided by one
leaves a remainder of 1.

Example

mysql> select mod(4,3);
+----------+
| mod(4,3) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

MySQL's MOD( ) function also works
with fractions.

Example

mysql> select mod(114.22,3.2);
+-----------------+
| mod(114.22,3.2) |
+-----------------+
|            2.22 |
+-----------------+
1 row in set (0.00 sec)

PI( )

PI( ) returns PI with 6 decimals
of accuracy.

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

POW(number,power_value)

POW( )has as a synonym POWER( ) takes
a value and raises it to that power. Two bytes
have 16 values, each which can have 2 values
0 or 1. To express the number of possible
combinations we can raise 2 to the power of 16.

Example

mysql> SELECT POW(2,16);
+-----------+
| POW(2,16) |
+-----------+
|     65536 |
+-----------+
1 row in set (0.00 sec)

RAND() , RAND(number)

"RAND( ) returns a random floating-point value, v in the
range 0 <= v < 1.0. If a constant integer argument N is
specified, it is used as the seed value, which produces
a repeatable sequence of column values." - MySQL Manual

In the following example a fraction in the range
provided is generated.

Example

mysql> SELECT RAND();
+------------------+
| RAND() |
+------------------+
| 0.44339807465989 |
+------------------+
1 row in set (0.00 sec)

To obtain a range say 1 to 100, we can multiply
the number by 100. Because the upper range is
always less than 1, we will never get 100. We
can use floor to get discrete values 0 to 99 and
add 1 to get a range 1 to 100.

Example

mysql> SELECT (FLOOR((RAND()*100))+1);
+-------------------------+
| (FLOOR((RAND()*100))+1) |
+-------------------------+
| 24 |
+-------------------------+
1 row in set (0.00 sec)

After many tries, the following example was obtained.

Example

mysql> SELECT (FLOOR((RAND()*100))+1);
+-------------------------+
| (FLOOR((RAND()*100))+1) |
+-------------------------+
| 100 |
+-------------------------+
1 row in set (0.00 sec)

MySQL Note

The MySQL Manual offers the following explanation
which might be easier to understand.

"To obtain a random integer R in the range i <= R < j,
use the expression FLOOR(i + RAND() * (j – i)).
For example, to obtain a random integer in the range
the range 7 <= R < 12, you could use the following
statement: SELECT FLOOR(7 + (RAND( ) * 5)); "

Another Example of a Function Use in a Table Select

The MySQL manual shows the following statement
where the RAND( ) function is used to randomize
the return on a set of table values.

Example

SELECT * FROM tbl_name ORDER BY RAND( );

Applying this example to our store table yields the
following. ( The number of IDs was limited to keep
the example short. )

Example

mysql> select Manager_ID from store
    -> where manager_id < 1040 ORDER BY RAND();
+------------+
| Manager_ID |
+------------+
|       1012 |
|       1011 |
|       1029 |
+------------+
3 rows in set (0.00 sec)

A better way to limit sample size is to use the LIMIT
operator.

ORDER BY RAND() combined with LIMIT

The LIMIT operator can be used to limit a sample.
This can be useful for selecting random samples of
a particular sized set of rows:

mysql> select Manager_ID from store
    -> ORDER BY RAND() LIMIT 10;
+------------+
| Manager_ID |
+------------+
|       1212 |
|       2143 |
|       1029 |
|       9991 |
|       1105 |
|       1239 |
|       9387 |
|       2435 |
|       1234 |
|       1012 |
+------------+
10 rows in set (0.00 sec)

ROUND(X), ROUND(X,D)

"Rounds the argument X to D decimal places. The
rounding algorithm depends on the data type of X.
D defaults to 0 if not specified. D can be negative to
cause D digits left of the decimal point of the value
X to become zero. "

We will take the simple version.

Example

mysql> SELECT ROUND(1.5);
+------------+
| ROUND(1.5) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

// round up
Example

mysql> SELECT ROUND(1.4);
+------------+
| ROUND(1.4) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

// round down

The fancier version, dictates which point in
the number is rounded.

Example

mysql> SELECT ROUND(1.12346,4);
+------------------+
| ROUND(1.12346,4) |
+------------------+
|           1.1235 |
+------------------+
1 row in set (0.00 sec)

// fourth decimal point is rounded

Example

mysql> SELECT ROUND(1.12346,3);
+------------------+
| ROUND(1.12346,3) |
+------------------+
|            1.123 |
+------------------+
1 row in set (0.00 sec)

// third decimal point is rounded

SQRT( number )

A final Number function example, that is commonly
used is the square root function.

Example

mysql> SELECT SQRT(2);
+-----------------+
| SQRT(2)         |
+-----------------+
| 1.4142135623731 |
+-----------------+
1 row in set (0.00 sec)

Date and Time Functions

There are a large number of date and time functions
which should not be surprising as so much of what
we do might record in a database may have a date
and or time dimension. Consider contract dates,
birthdays billing dates etc.

Several of the functions may be used without the
round braces. These are omitted from the list below
where they are redundant, coming in both forms.
Some functions have abbreviated names which are
omitted. There are many synonyms for the NOW( )
function which are left in the list.

MySQL 5 Date and Time Functions

ADDDATE()     ADDTIME()         CONVERT_TZ()
CURDATE() CURRENT_DATE()    CURRENT_TIME()
NOW() -same as->    CURRENT_TIMESTAMP() DATE_ADD()
DATE_FORMAT()       DATE_SUB()    DATE()
DATEDIFF()          DAY() -same as-> DAYOFMONTH()
DAYNAME()           DAYOFMONTH()      DAYOFWEEK()
DAYOFYEAR() EXTRACT FROM_DAYS()
FROM_UNIXTIME()     GET_FORMAT()      HOUR()
LAST_DAY            LOCALTIME()/ NOW()LOCALTIME / NOW()
LOCALTIMESTAMP      LOCALTIMESTAMP()/NOW()
MAKEDATE()          MAKETIME()        MICROSECOND()
MINUTE()            MONTH()           MONTHNAME()
PERIOD_ADD()        PERIOD_DIFF() QUARTER()
SEC_TO_TIME() SECOND()    STR_TO_DATE()
SUBDATE()           SUBTIME()         SYSDATE()
TIME_FORMAT() TIME_TO_SEC() TIME()
TIMEDIFF()          TIMESTAMP()       TIMESTAMPADD()
TIMESTAMPDIFF()     TO_DAYS() UNIX_TIMESTAMP()
UTC_TIME()          UTC_TIMESTAMP()   WEEK() Return the week number
WEEKDAY()           WEEKOFYEAR()      YEAR()
YEARWEEK()

Some Examples

NOW( )

Quite a few of the MySQL functions are synonyms for
NOW ( ) which returns the day, month year and the time
in 24 hour format along with minutes and seconds.

Example

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2008-01-30 19:32:27 |
+---------------------+
1 row in set (0.00 sec)

Example

mysql> SELECT LOCALTIME();
+---------------------+
| LOCALTIME()         |
+---------------------+
| 2008-01-30 19:33:59 |
+---------------------+
1 row in set (0.00 sec)

DATE_FORMAT(date,format)

DATE_FORMAT is an important function as it
permits formatting dates into a large number of
different formats which are used throughout the
world.

See the MySQL Manual for a complete list of
specifiers. Some are shown in the following
examples.

Example

// specifiers are for weekday, month and year

mysql> SELECT DATE_FORMAT('2007-07-02 18:33:57', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2007-07-02 18:33:57', '%W %M %Y') |
+------------------------------------------------+
| Monday July 2007                               |
+------------------------------------------------+
1 row in set (0.00 sec)

Notice the spaces between the format specifiers are
included in the presentation of the time aspects.

Example

// specifiers are for month, day of year 1 to 366, and am or pm

mysql> SELECT DATE_FORMAT(NOW(),'%M %j %p');
+-------------------------------+
| DATE_FORMAT(NOW(),'%M %j %p') |
+-------------------------------+
| January 030 PM                |
+-------------------------------+
1 row in set (0.00 sec)

QUARTER( date)

QUARTER returns what quarter we are in. It takes
a date object. The NOW( ) function can be used to
obtain this date.

Example

mysql> SELECT QUARTER(NOW());
+----------------+
| QUARTER(NOW()) |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

Assignment

1 ) Operator Priorities are often listed in tables listing
the operators highest to lowest priority. Create a table
which re-organizes the operator table showing
precedence provided in this note from highest to
lowest priority.

Title the table 'MySQL Operator Precedence'.

2) Show the city name with escape sequences
needed to produce the output below.

mysql> select ________;
+------------+
| St. John's |
+------------+
| St. John's |
+------------+
1 row in set (0.00 sec)

3) Use the GREATEST( ) function along with a
SELECT statement to select the greatest of the
following values. 2009, 2001, 2003, 2100.
Include a screenshot of your SQL output.

4) Use the CONCAT( ) function in a SELECT
statement concatenate the words 'National ', 'Space '
and 'Agency'. Note the first two have built in spaces.
(Show the output). Then use the CHAR_LENGTH( )
function to output the length of the newly created
string. You can do this all in one statement if you
wish by nesting the CONCAT function.

5) Using an INSERT( ) function with a SELECT
statement, and the arguments 'Alpha Omega' and
' and ' what would the statement be that resulted
in the following output.

MySQL Output

+------------------------------------+
| INSERT('Alpha Omega ',6,1,' and ') |
+------------------------------------+
| Alpha and Omega |
+------------------------------------+
1 row in set (0.00 sec)

// If using Oracle or MSSQL show any differences
// in method or syntax that was necessary

6 ) Use the pow( ) or power( ) function with a SELECT
to raise 2 to the power 32. This is the size of a four byte
data type. Your number will show it's unsigned upper
limit.

7) Using the QUARTER function with a date specified
as '2005-09-03', show what quarter of the year this was.
the function statement and the output.