3.9. Functions
Functions
and operators can be used in SQL statements. This section lists these
functions and operators and provides examples. A full list of
functions with examples is available in Section 7.4 of the
manual.html file distributed with MySQL.
3.9.1. Arithmetic and comparison operators
Table 3-2 shows examples of the basic
arithmetic and
comparison operators in
SELECT statements. The basic arithmetic operators
are *, +, /,
and -, as well as the parentheses (
) to control the order of evaluation of an expression.
Table 3-2. Using the arithmetic and comparison operators
Statement
|
Output
|
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
|
The comparison operators include =,
!=, <,
>, <=, and
>=. Four examples are shown in Table 3-2. If an expression evaluates as
true, the output is 1; if an expression evaluates
as false, the output is 0. To test for equality, a
single equals sign is used; this contrasts with PHP, where the double
equals (==) is used for equality tests, and a
single equals sign is used for assignment.
To test whether two items are equal, the !=
operator is provided. Less-than-or-equal-to is represented by
<=, and greater-than-or-equal-to is represented
by >=. Parentheses can explicitly express the
evaluation order.
3.9.1.1. String-comparison operators and functions
Table 3-3 shows examples of the MySQL
string-comparison operators
and functions. Many of the MySQL string functions shown here are
similar to PHP functions, which were introduced in Chapter 2.
Table 3-3. Using string comparison functions and operators
Statement
|
Output
|
SELECT 'Apple' LIKE 'A%';
|
1
|
SELECT 'Apple' LIKE 'App%';
|
1
|
SELECT 'Apple' LIKE 'A%l%';
|
1
|
SELECT concat('con','cat');
|
'concat'
|
SELECT length('Apple');
|
5
|
SELECT locate('pp','Apple');
|
2
|
SELECT substring('Apple',2,3);
|
'ppl'
|
SELECT ltrim(' Apple');
|
'Apple'
|
SELECT rtrim('Apple ');
|
'Apple'
|
SELECT trim(' Apple ');
|
'Apple'
|
SELECT space(3);
|
' '
|
SELECT strcmp('a','a');
|
0
|
SELECT strcmp('a','b');
|
-1
|
SELECT strcmp('b','a');
|
1
|
SELECT lower('Apple');
|
'apple'
|
SELECT upper('Apple');
|
'APPLE'
|
The string functions work as follows:
-
The string-comparison function LIKE is useful. The
% character represents any number of unspecified
characters, are generally known as wildcards. So, for example, the
comparison of the string 'Apple'
LIKE 'A%' is 1
(true), as is the comparison of
'Apple' LIKE
'App%'. The underscore character can be used to
match a single unspecified, wildcard character; for example,
'Apple' LIKE
'Appl_' is true, while
'Appl' LIKE
'Appl_' is false.
-
concat( ) joins or concatenates two strings together,
so the result of calling concat( ) with two string
parameters is a single string consisting of the parameters.
-
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.
-
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( ); that is, any leading or
trailing spaces are removed, and the trimmed string is returned.
-
space( ) returns a string consisting of spaces of the
length of the integer parameter.
-
strcmp( ) compares two string parameters. 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.
While not detailed in Table 3-3, regular
expressions can be used through the function regexp(
). For more on regular expressions in PHP, see Chapter 2.
3.9.1.2. Mathematical functions
We make little use of the mathematical
functions provided by MySQL in this book. However, Table 3-4 shows selected MySQL mathematical functions
and their output.
Table 3-4. Using the MySQL mathematical functions
Statement
|
Output
|
SELECT abs(-33);
|
33
|
SELECT abs(33);
|
33
|
SELECT mod(10,3);
|
1
|
SELECT 10 % 3;
|
1
|
SELECT floor(3.14159);
|
3
|
SELECT ceiling(3.14159);
|
4
|
SELECT round(3.14159);
|
3
|
SELECT log(100);
|
4.605170
|
SELECT log10(100);
|
2
|
SELECT pow(2,3);
|
8
|
SELECT sqrt(36);
|
6
|
SELECT sin(pi( ));
|
0.000000
|
SELECT cos(pi( ));
|
-1.000000
|
SELECT tan(pi( ));
|
-0.000000
|
SELECT rand( );
|
0.8536
|
SELECT truncate(3.14159,3);
|
3.141
|
SELECT format(12345.23,0);
|
12,345
|
SELECT format(12345.23, 1);
|
12,345.2
|
Several of the functions in Table 3-4 require some
explanation:
-
The abs( ) operator returns the absolute value of
a number; that is, it removes the negative sign from negative
numbers.
-
The modulo operator—which has two identical
variants, % and mod(
)—divides the first number by the second number and
outputs the remainder.
-
The floor( ) and ceiling( )
functions are complementary: floor( ) returns the
largest integer not greater than the parameter; ceiling(
) returns the smallest integer not less than the parameter.
-
The round( ) function rounds to the nearest
integer.
-
Both the natural logarithm, log( ), and base-10
logarithm, log10( ), are available.
-
The pow( ) function raises the first number to the
power of the second.
-
sqrt( ) takes the square root of the parameter.
-
The trigonometry functions sin( ), cos(
), and tan( ) take values expressed in
radians as parameters. The complementary arc sin, arc cos, and arc
tan are available as asin( ), acos(
), and atan( ).
-
The rand( ) function returns a pseudorandom number
in the range 0 to 1.
-
The truncate( ) function removes decimal places
without rounding.
-
The format( ) function 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. This function is seldom used in web
database applications, because formatting is usually performed in PHP
scripts.
3.9.1.3. Date and time functions
Table 3-5 shows sample uses of selected
time and date functions available in MySQL. The
date_add( ) function can be used to add and
subtract times and dates; more details can be found in Section 7.4.11
of the manual.html file distributed with MySQL.
Table 3-5. Using the date and time functions
Statement
|
Output
|
SELECT dayofweek('2000-05-03');
|
3
|
SELECT dayname('2000-05-03');
|
Wednesday
|
SELECT monthname('2000-05-03');
|
May
|
SELECT week('2000-05-03');
|
18
|
SELECT date_add("2000-05-03", INTERVAL 1 DAY);
|
2000-05-04
|
SELECT curdate( );
|
2002-01-01
|
SELECT curtime( );
|
11:27:20
|
SELECT now( );
|
2002-01-01 11:27:20
|
3.9.1.4. Miscellaneous operators and functions
Miscellaneous operators and functions are shown in Table 3-6.
Table 3-6. Miscellaneous operators and functions
Statement
|
Output
|
Control flow functions
|
|
SELECT if(1<0,"yes","no")
|
no
|
Encryption functions
|
|
SELECT password('secret')
|
428567f408994404
|
SELECT encode('secret','shhh')
|
"|ï ¨~
|
SELECT decode('"|ï ¨~','shhh')
|
secret
|
Other functions
|
|
SELECT database( )
|
winestore
|
SELECT user( )
|
dimitria@localhost
|
The conditional function if 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 customer SET country =
if(trim(country)='','Australia',country);
In this case, the SQL statement replaces blank
country attributes with Australia and leaves
already filled country attributes unaltered.
Authentication and securing data using password(
), encode( ), and decode(
) are discussed in Chapter 9. The
functions database( ) and user(
) provide the names of the current database and
user.
| | | 3.8. Modifying the Database | | 3.10. More on SQL and MySQL |
Copyright © 2003 O'Reilly & Associates. All rights reserved.
|