Specifications

which matches all the columns in the specified table or tables. For example, to retrieve all

columns and all rows from the order_items table, we would use

select *

from order_items;

which will give the following output:

+---------+---------------+----------+

| orderid | isbn | quantity |

+---------+---------------+----------+

| 1 | 0-672-31697-8 | 2 |

| 2 | 0-672-31769-9 | 1 |

| 3 | 0-672-31769-9 | 1 |

| 3 | 0-672-31509-2 | 1 |

| 4 | 0-672-31745-1 | 3 |

+---------+---------------+----------+

Retrieving Data with Specific Criteria

In order to access a subset of the rows in a table, we need to specify some selection criteria.

You can do this with a WHERE clause. For example,

select *

from orders

where customerid = 3;

will select all the columns from the orders table, but only the rows with a customerid of 3.

Here’s the output:

+---------+------------+--------+------------+

+---------+------------+--------+------------+

| 1 | 3 | 69.98 | 0000-00-00 |

| 4 | 3 | 24.99 | 0000-00-00 |

+---------+------------+--------+------------+

The WHERE clause specifies the criteria used to select particular rows. In this case, we have

selected rows with a customerid of 3. The single equal sign is used to test equality—note that

this is different from PHP, and it’s easy to become confused when you’re using them together.

In addition to equality, MySQL supports a full set of operators and regular expressions. The

ones you will most commonly use in WHERE clauses are listed in Table 9.1. Note that this is not

a complete list—if you need something not listed here, check the MySQL manual.

Using MySQL

ART II

212

12 7842 CH09 3/6/01 3:36 PM Page 212