Specifications

LIKE uses simple SQL pattern matching. Patterns can consist of regular text plus the % (per-
cent) character to indicate a wildcard match to any number of characters and the _ (underscore)
character to wildcard match a single character. In MySQL, these matches are not case sensi-
tive. For example, ‘Fred %’ will match any value beginning with ‘fred ‘.
The REGEXP keyword is used for regular expression matching. MySQL uses POSIX regular
expressions. Instead of REGEXP, you can also use RLIKE, which is a synonym. POSIX regular
expressions are also used in PHP. You can read more about them in Chapter 4, “String
Manipulation and Regular Expressions.”
You can test multiple criteria in this way and join them with AND and OR. For example,
select *
from orders
where customerid = 3 or customerid = 4;
Retrieving Data from Multiple Tables
Often, to answer a question from the database, you will need to use data from more than table.
For example, if you wanted to know which customers placed orders this month, you would
need to look at the Customers table and the Orders table. If you also wanted to know what,
specifically, they ordered, you would also need to look at the Order_Items table.
These items are in separate tables because they relate to separate real-world objects. This is
one of the principles of good database design that we talked about in Chapter 7, “Designing
Your Web Database.”
To put this information together in SQL, you must perform an operation called a join. This
simply means joining two or more tables together to follow the relationships between the data.
For example, if we want to see the orders that customer Julie Smith has placed, we will need to
look at the Customers table to find Julie’s CustomerID, and then at the Orders table for orders
with that CustomerID.
Although joins are conceptually simple, they are one of the more subtle and complex parts of
SQL. Several different types of join are implemented in MySQL, and each is used for a differ-
ent purpose.
Simple Two-Table Joins
Let’s begin by looking at some SQL for the query about Julie Smith we just talked about:
select orders.orderid, orders.amount, orders.date
from customers, orders
where customers.name = ‘Julie Smith’
and customers.customerid = orders.customerid;
Using MySQL
P
ART II
214
12 7842 CH09 3/6/01 3:36 PM Page 214