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.
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
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 |
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, ...
Get Web Database Applications with PHP and MySQL, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.