Web Database Applications with PHP and MySQL, 2nd Edition

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, ...