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.
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.
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.
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
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.
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.
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
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.
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
Miscellaneous operators and functions are shown in Table 3-6.
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.148.103.210