15.4 Functions
As
you've seen in many examples so far, functions and
operators can be used in SQL statements. In this section, we show you
selected functions and operators and provide more examples.
We've chosen to show you only those functions and
operators that we regularly use in web database applications, but
there are many others that you may find useful for mathematical
operations, date and time comparisons, and string processing. A full
list with examples is available in Section 6.3 of the MySQL manual.
15.4.1 Arithmetic and Comparison Operators
Table 15-1
shows examples of the
basic arithmetic and comparison operators and their output when
tested with a SELECT statement. The basic
arithmetic operators are *, +,
/, and -, as well as the
parentheses ( ) that are used to control the order
of evaluation of an expression.
Table 15-1. Using the arithmetic and comparison operators|
SELECT 8+3*2;
|
14
|
SELECT (8+3)*2;
|
22
|
SELECT 2=2;
|
1
|
SELECT 1!=2;
|
1
|
SELECT 2<=2;
|
1
|
SELECT 3<=2;
|
0
|
SELECT 'Apple' = 'Apple';
|
1
|
SELECT 'Apple' < 'Banana';
|
1
|
SELECT 'Banana' BETWEEN 'Apple' AND 'Carrot';
|
1
|
SELECT 7 NOT BETWEEN 2 AND 5;
|
1
|
SELECT 6 IN (6, 'cat', 3.14);
|
1
|
SELECT 6 NOT IN (6, 'cat', 3.14);
|
0
|
SELECT NULL IS NULL;
|
1
|
SELECT 0 IS NULL;
|
0
|
SELECT 0 IS NOT NULL;
|
1
|
SELECT NULL = 0;
|
NULL
|
SELECT NULL <=> NULL;
|
1
|
SELECT NULL <=> 0;
|
0
|
The
comparison operators include
=, !=, <,
>, <=,
>=, and <=>. If an
expression evaluates as true, the output is 1; if
an expression evaluates as false, the output is 0.
When you compare values of the same type, they are compared using
that type of comparison; for example, when you compare integers to
integers, they are compared numerically as integer values, and when
you compare two strings they're compared
alphabetically as strings. For string comparison, case is ignored and
so is any trailing whitespace.
To test for equality, a single
equals sign = is used; this is different from PHP,
where the double equals == is used for equality
tests, and a single equals sign = is used for
assignment. However, if you compare NULL to any
other value (including NULL) with the single
equals sign = then the result is
NULL. MySQL therefore includes a
NULL
-safe equality
comparison operator <=> that returns 1 when
two NULL values are compared and 0 when a
NULL value is compared to any other value. You can
also explicitly test whether a value is NULL by
using IS NULL, and not NULL
using IS NOT NULL. The other basic operators work
the same as in PHP as discussed in Chapter 2.
The BETWEEN
operator returns 1 if a value lies in
the range defined by the following two parameters, inclusive. The
NOT BETWEEN
operator does the opposite. The
IN operator returns 1 if a the value preceding the
operator is in the set that's listed after the
operator, and NOT IN does the opposite.
15.4.1.1 String functions
Table 15-2 shows examples using the
MySQL string functions. There are
also functions for converting integers to strings, strings to
integers, and integers to different numbering schemes such as octal,
hexadecimal, and binary; we've omitted these, but
you'll find more details in the MySQL manual.
Regular expressions can also be used through the function
regexp( ); for more on regular expressions, see
Chapter 3.
Table 15-2. Using string comparison functions and operators|
SELECT 'Apple' LIKE 'A%';
|
1
|
SELECT 'Apple' LIKE 'App%';
|
1
|
SELECT 'Apple' LIKE 'A%l%';
|
1
|
SELECT 'Apple' LIKE 'Appl_';
|
1
|
SELECT 'Apple' LIKE 'Appl_ _';
|
0
|
SELECT concat('con','cat');
|
concat
|
SELECT concat('con','c','at');
|
concat
|
SELECT concat_ws(",", "Williams", "Lucy");
|
Williams,Lucy
|
SELECT length('Apple');
|
5
|
SELECT locate('pp','Apple');
|
2
|
SELECT locate('pp','Apple',3);
|
0
|
SELECT lower('Apple');
|
apple
|
SELECT ltrim(' Apple');
|
Apple
|
SELECT rtrim('Apple ');
|
Apple
|
SELECT quote("Won't");
|
'Won\'t'
|
SELECT replace('The Web', 'Web', 'WWW');
|
The WWW
|
SELECT strcmp('a','a');
|
0
|
SELECT strcmp('a','b');
|
-1
|
SELECT strcmp('b','a');
|
1
|
SELECT strcmp('A','a');
|
0
|
SELECT substring('Apple',2,3);
|
ppl
|
SELECT trim(' Apple ');
|
Apple
|
SELECT upper('Apple');
|
APPLE
|
The string functions work as follows:
- LIKE
-
A useful way to compare a string with an approximate representation
of a string. For example, you can use it to find all rows that begin
with a character or prefix. The % character is a
wildcard that represents any number of unspecified characters. So,
for example, the comparison of the string 'Apple'
LIKE 'A%' returns 1, as does
the comparison of `Apple'
LIKE 'App%'. The underscore
character can be used to match a single wildcard character. For
example, `Apple'
LIKE `Appl_'
returns 1, while 'Appl' LIKE
'Appl_' returns 0.
- concat( )
-
Joins (concatenates) two or more strings together and returns a
single string consisting of the parameters.
- concat_ws( )
-
Joins two or more strings together using the first parameter as a
separator and returns a single string. It ignores any parameters that
are NULL.
- length( )
-
Returns the length of the string in characters.
- locate( )
-
Returns the location of the first string parameter in the second
string parameter. If the string doesn't occur, the
result is 0. If the optional third parameter is provided, the search
begins at that offset.
- replace( )
-
Replaces all occurrences of the second parameter in the first
parameter with the third parameter, and returns the modified string.
- substring( )
-
Returns part of the string passed as the first parameter. The string
that is returned begins at the offset supplied as the second
parameter and is of the length supplied as the third parameter.
- ltrim( )
-
Removes any left-padding space characters from the string parameter
and returns the left-trimmed string.
- rtrim( )
-
Removes any right-padding space characters from the string parameter
and returns the right-trimmed string.
- trim( )
-
Performs the function of both ltrim( ) and
rtrim( ). Any leading or trailing spaces are
removed, and the trimmed string is returned.
- quote( )
-
Puts quotation marks around a string, and escapes any characters that
need to be escaped. This is useful for preparing a string to be used
in an SQL statement.
- strcmp( )
-
Compares two string parameters, and returns a case-sensitive value
that indicates the alphabetic ordering of the strings. If they are
identical, it returns 0. If the first string is alphabetically less
than the second, it returns a negative number. If the first string is
alphabetically greater than the second, it returns a positive number.
Uppercase characters are less than lowercase characters.
- lower( )
-
Converts the string parameter to lowercase and returns the lowercase
string.
- upper( )
-
Converts the string parameter to uppercase and returns the uppercase
string.
15.4.1.2 Mathematical functions
We make little use of the mathematical functions provided by MySQL in
this book, and that's true of most web database
applications. However, Table 15-3 shows selected
key MySQL mathematical functions you can use and their output.
Table 15-3. Using the MySQL mathematical functions|
SELECT abs(-33);
|
33
|
SELECT abs(33);
|
33
|
SELECT ceiling(3.14159);
|
4
|
SELECT cos(pi( ));
|
-1.000000
|
SELECT floor(3.14159);
|
3
|
SELECT format(12345.23,0);
|
12,345
|
SELECT format(12345.23, 1);
|
12,345.2
|
SELECT ln(10);
|
2.302585
|
SELECT log(100,3);
|
0.238561
|
SELECT log10(100);
|
2
|
SELECT mod(10,3);
|
1
|
SELECT 10 % 3;
|
1
|
SELECT pow(4,2);
|
16.000000
|
SELECT rand( );
|
0.88605689619301
|
SELECT round(3.14159);
|
3
|
SELECT sin(pi( ));
|
0.000000
|
SELECT sqrt(36);
|
6.000000
|
SELECT tan(pi( ));
|
-0.000000
|
SELECT truncate(3.14159,3);
|
3.141
|
Several of the functions in Table 15-3 require some
explanation:
- abs( )
-
Returns the absolute value of a number: it removes the negative sign
from negative numbers.
- %
and mod( )
-
Modulo has two syntaxes with identical effects. These divide the
first number by the second number and output the remainder.
- floor( )
and ceiling( )
-
These are complementary: floor( ) returns the
largest integer not greater than the parameter, while
ceiling( ) returns the smallest integer not less
than the parameter.
- round( )
-
Rounds to the nearest integer and returns the result.
- ln( )
, log( )
, and log10( )
-
These are natural, parameterizable, and base-10 logarithm functions
respectively. The second parameter to the log( )
function is the base to use, and if the parameter is omitted it
behaves the same as ln( ). All return the result
of the operation.
- pow( )
-
Raises the first number to the power of the second and returns the
result.
- sqrt( )
-
Takes the square root of the parameter and returns the result.
- sin( )
, cos( )
, and tan( )
-
These trigonometry functions take values expressed in radians as
parameters, and return the sine, cosine, and tangent of the parameter
as a result. The complementary arc sine, arc cosine, and arc tangent
are available as asin(
)
, acos(
)
, and atan(
)
.
- pi( )
-
Returns the value of Pi.
- rand( )
-
Returns a pseudo-random number in the range 0 to 1.
- truncate( )
-
Removes decimal places without rounding and returns the result.
- format( )
-
This isn't really a mathematical function but is
instead used for returning numbers in a predefined format. The first
parameter is the number, and the second parameter is the number of
decimal places to return. The first parameter is rounded so that, for
example, 123.56 formatted to one decimal place is 123.6.
15.4.1.3 Date and time functions
Table 15-4
shows sample uses of selected time
and date functions available in MySQL. However,
you'll find that most of your date and time
manipulation in a web database application occurs in your PHP
scripts, and for that reason, we've kept this
section brief; we discuss PHP date and time manipulation in Chapters
Chapter 3 and Chapter 9.
MySQL functions for date and time manipulation are described in
detail in Section 6.3.4 of the MySQL manual.
Table 15-4. Using the date and time functions|
SELECT curdate( );
|
2002-01-01
|
SELECT curtime( );
|
11:27:20
|
SELECT date('2005-10-10 12:22:54');
|
2005-10-10
|
SELECT date_add('2005-05-03', INTERVAL 1 DAY);
|
2005-05-04
|
SELECT date_format(now( ), "%W, %e %M, %Y.");
|
Tuesday, 30 September, 2003.
|
SELECT dayofweek('2000-05-03');
|
3
|
SELECT dayname('2000-05-03');
|
Wednesday
|
SELECT dayofyear('2000-05-03');
|
124
|
SELECT monthname('2000-05-03');
|
May
|
SELECT extract(YEAR FROM '2005-01-01 11:27:20');
|
2005
|
SELECT now( );
|
2005-01-01 11:27:20
|
SELECT quarter('2000-05-03');
|
2
|
SELECT time('2005-10-10 12:22:54');
|
12:22:54
|
SELECT timestamp('2005-10-10');
|
2005-10-10 12:35:10
|
SELECT week('2000-05-03');
|
18
|
SELECT weekday('2000-05-03');
|
2
|
Here are some the key issues related to MySQL data and time functions:
- date_add( )
-
We've provided only one example of using this
function. This function can add over 20 different types of values
(including seconds, minutes, hours, days, months, years, and
combinations of these) to a variety of different date and time
formats. It's described in detail in the MySQL
manual, with many examples. There's also a
complementary date_sub( ) function for
subtraction.
- curdate( ),
curtime( )
, and now( )
-
The curdate( ) and curtime(
) functions return the current date and time respectively,
and now( ) returns both. These functions are
evaluated before a query begins, and so multiple calls to them in the
same query will return the same result.
- extract( )
-
We've provided only one example of using this
function, showing how it retrieves a component from a date or
combined date and time. It supports the same wide range of parameters
as date_add( ).
- date_format( )
-
This can take over 20 different parameters to control the output of a
date in almost any desired format. We've only shown
one example, but the parameters and many more examples are in the
MySQL manual.
- week( )
-
Returns the number of the week in the year in the range 0 to 53. You
can provide a second parameter that controls whether a week begins on
Sunday or Monday, and whether the function returns values from 0 to
53 or 1 to 53. With 0 (the default) you get a Sunday start, and 0 to
53 as a result. With 1, it's Monday and 0 to 53;
with 2, Sunday and 1 to 53; and, with 3, Monday and 1 to 53.
- timestamp( )
-
Converts a date into a timestamp that includes a date and time. You
can extract a Unix timestamp (the number of seconds since 1 January
1970) using unix_timestamp( ).
15.4.1.4 Miscellaneous operators and functions
Miscellaneous operators and functions are shown in Table 15-5.
Table 15-5. Miscellaneous functions|
Control flow
functions
| |
SELECT if(1<0,"yes","no")
|
no
|
Encryption
functions
| |
SELECT decode('"|2 1~','shhh')
|
secret
|
SELECT encode('secret','shhh')
|
"|2 1~
|
SELECT md5('secret');
|
5ebe2294ecd0e0f08eab7690d2a6ee69
|
SELECT password('secret')
|
*aace71a608b0b77c141250293c9f9b5b7ec75c970ea7
|
Other functions
| |
SELECT database( )
|
winestore
|
SELECT user( )
|
dimitria@localhost
|
Here's a short discussion of these functions:
- if
-
This conditional function outputs the first string if the expression
is true and the second if it is
false. This can be used in complex ways. For
example, it could be used in an UPDATE statement
for intelligent changes to an attribute:
UPDATE orders SET instructions =
if(trim(instructions)='','None specified',instructions);
In this case, the SQL statement replaces blank
instructions attributes with a string and leaves
already filled instructions unaltered.
- decode( )
and encode( )
-
These functions are related two way functions that can be used to
encrypt and decrypt data using a password. Encryption is discussed in
Chapter 11.
- password( )
-
This is a one way encryption function that converts a plain-text
string into an encoded string; it's also used
internally by MySQL to store passwords in the
users table that's discussed in
Section 15.8.
Encryption is discussed in Chapter 11.
- md5( )
-
This function produces an MD5 hash or digest of the string parameter.
Encryption is discussed in Chapter 11.
- database( )
and user( )
-
These functions provide the names of the current database and user,
respectively.
|