To specify which rows to return, add a WHERE
clause to identify the rows that you
want to see, such as customers that live in a particular city or tasks
that have a status of “finished.”
Unless you qualify or restrict a SELECT
query in some way, it retrieves every
row in your table, which in many cases is a lot more information than
you really want to see. To be more precise about which rows to select,
provide a WHERE
clause that
specifies one or more conditions that rows must match.
Conditions can perform tests for equality, inequality, or
relative ordering. For some types of data, such as strings, you can
use pattern matches. The following statements select columns from rows
from the mail
table containing
srchost
values that are exactly
equal to the string 'venus'
or that
begin with the letter 's'
:
mysql>SELECT t, srcuser, srchost FROM mail WHERE srchost = 'venus';
+---------------------+---------+---------+ | t | srcuser | srchost | +---------------------+---------+---------+ | 2006-05-14 09:31:37 | gene | venus | | 2006-05-14 14:42:21 | barb | venus | | 2006-05-15 08:50:57 | phil | venus | | 2006-05-16 09:00:28 | gene | venus | | 2006-05-16 23:04:19 | phil | venus | +---------------------+---------+---------+ mysql>SELECT t, srcuser, srchost FROM mail WHERE srchost LIKE 's%';
+---------------------+---------+---------+ | t | srcuser | srchost | +---------------------+---------+---------+ | 2006-05-11 10:15:08 | barb | saturn | | 2006-05-13 13:59:18 | barb | saturn | | 2006-05-14 17:03:01 | tricia | saturn | | 2006-05-15 17:35:31 | gene | saturn | | 2006-05-19 22:21:51 | gene | saturn | +---------------------+---------+---------+
The LIKE
operator in the
previous query performs a pattern match, where %
acts as a wildcard that matches any
string. Pattern Matching with SQL Patterns discusses
pattern matching further.
A WHERE
clause can test
multiple conditions and different conditions can test different
columns. The following statement finds messages sent by barb
to tricia
:
mysql>SELECT * FROM mail WHERE srcuser = 'barb' AND dstuser = 'tricia';
+---------------------+---------+---------+---------+---------+-------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+-------+
| 2006-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2006-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |
+---------------------+---------+---------+---------+---------+-------+
3.138.135.80