34,333
edits
Changes
New page: In previous chapters we have performed various data retrievals using the SELECT statement and also looked at how to sort the results of a retrieval. One of the most important requirements ...
In previous chapters we have performed various data retrievals using the SELECT statement and also looked at how to sort the results of a retrieval. One of the most important requirements when retrieving data is to be able to filter that data so that the only rows returned are those which match specified search criteria. This is achieved in MySQL using the ''WHERE'' clause in conjunction with the ''SELECT'' statement.
== The Basics of the WHERE Clause ==
The syntax of the ''WHERE'' clause is as follows:
SELECT ''column(s)'' FROM ''table'' WHERE ''column'' = ''value''
The first part of the statement looks very much like any regular SELECT statement. The ''WHERE'' clause changes things, however. After the ''WHERE'' we need to specify the couln on which the search criteria are to based, followed by an operator which specifies the type of comparison to perform (in this case we are looking for equality) and finally the value to which the column must match.
Assuming we have a database table containing prod_code, prod_name, prod_desc and prod_price columns, a standard ''SELECT'' statement might generate following output:
<pre>
mysql> SELECT * FROM products;
+-----------+----------------------------+-----------------------------+------------+
| prod_code | prod_name | prod_desc | prod_price |
+-----------+----------------------------+-----------------------------+------------+
| 1 | WildTech 250Gb 1700 | SATA Disk Drive | 120 |
| 2 | Moto Razr | Mobile Phone | 200 |
| 3 | Microsoft 10-20 Keyboard | Ergonmoc Keyboard | 49 |
| 4 | EasyTech Mouse 7632 | Cordless Mouse | 49 |
| 5 | Dell XPS 400 | Desktop PC | 999 |
| 6 | Buffalo AirStation Turbo G | Wireless Ethernet Bridge | 60 |
| 7 | Apple iPod Touch | Portable Music/Movie Player | 199 |
| 8 | Apple iPhone 8Gb | Smart Phone | 399 |
+-----------+----------------------------+-----------------------------+------------+
8 rows in set (0.00 sec)
</pre>
This is fine if we actually want a list of every row in our table. It is more likely, however, that we really want a subset of the r
== The Basics of the WHERE Clause ==
The syntax of the ''WHERE'' clause is as follows:
SELECT ''column(s)'' FROM ''table'' WHERE ''column'' = ''value''
The first part of the statement looks very much like any regular SELECT statement. The ''WHERE'' clause changes things, however. After the ''WHERE'' we need to specify the couln on which the search criteria are to based, followed by an operator which specifies the type of comparison to perform (in this case we are looking for equality) and finally the value to which the column must match.
Assuming we have a database table containing prod_code, prod_name, prod_desc and prod_price columns, a standard ''SELECT'' statement might generate following output:
<pre>
mysql> SELECT * FROM products;
+-----------+----------------------------+-----------------------------+------------+
| prod_code | prod_name | prod_desc | prod_price |
+-----------+----------------------------+-----------------------------+------------+
| 1 | WildTech 250Gb 1700 | SATA Disk Drive | 120 |
| 2 | Moto Razr | Mobile Phone | 200 |
| 3 | Microsoft 10-20 Keyboard | Ergonmoc Keyboard | 49 |
| 4 | EasyTech Mouse 7632 | Cordless Mouse | 49 |
| 5 | Dell XPS 400 | Desktop PC | 999 |
| 6 | Buffalo AirStation Turbo G | Wireless Ethernet Bridge | 60 |
| 7 | Apple iPod Touch | Portable Music/Movie Player | 199 |
| 8 | Apple iPhone 8Gb | Smart Phone | 399 |
+-----------+----------------------------+-----------------------------+------------+
8 rows in set (0.00 sec)
</pre>
This is fine if we actually want a list of every row in our table. It is more likely, however, that we really want a subset of the r
