- Working with Query Expressions
- Placing Conditions on Queries
- Learning How to Use Operators
- Summary
- Q&A
This chapter is from the book
This chapter is from the book
This chapter is from the book
Placing Conditions on Queries
If you ever want to find a particular item or group of items in your database, you need one or more conditions. Conditions are contained in the WHERE clause. In the preceding example, the condition is
NAME = 'BROWN'
To find everyone in your organization who worked more than 100 hours last month, your condition would be
NUMBEROFHOURS > 100
Conditions enable you to make selective queries. In their most common form, conditions comprise a variable, a constant, and a comparison operator. In the first example, the variable is NAME, the constant is 'BROWN', and the comparison operator is =. In the second example, the variable is NUMBEROFHOURS, the constant is 100, and the comparison operator is >. You need to know about two more elements before you can write conditional queries: the WHERE clause and operators.
Using the WHERE Clause
The syntax of the WHERE clause is
WHERE <SEARCH CONDITION>
SELECT, FROM, and WHERE are the three most frequently used clauses in SQL. WHERE simply causes your queries to be more selective. Without the WHERE clause, the most useful thing you could do with a query is display all records in the selected table(s)—for example,
SQL> SELECT * FROM BIKES;
or
mysql> select * from bikes;
lists all rows of data in the table BIKES.
NAME |
FRAMESIZE |
COMPOSITION |
MILESRIDDEN |
TYPE |
----------- |
--------- |
------------ |
----------- |
-------- |
TREK 2300 |
22.5 |
CARBON FIBER |
3500 |
RACING |
BURLEY |
22 |
STEEL |
2000 |
TANDEM |
GIANT |
19 |
STEEL |
1500 |
COMMUTER |
FUJI |
20 |
STEEL |
500 |
TOURING |
SPECIALIZED |
16 |
STEEL |
100 |
MOUNTAIN |
CANNONDALE |
22.5 |
ALUMINUM |
3000 |
RACING |
6 rows selected.
If you wanted a particular bike, you could type
SQL> SELECT * FROM BIKES WHERE NAME = 'BURLEY'; mysql> select * -> from bikes -> where name = 'BURLEY';
which would yield only one record:
NAME FRAMESIZE COMPOSITION MILESRIDDEN TYPE ------------- --------- ----------- ----------- ------ BURLEY 22 STEEL 2000 TANDEM +--------+-----------+-------------+-------------+--------+ | name | framesize | composition | milesridden | type | +--------+-----------+-------------+-------------+--------+ | BURLEY | 22.00 | STEEL | 2000 | TANDEM | +--------+-----------+-------------+-------------+--------+ 1 row in set (0.27 sec)
These simple examples show how you can place a condition on the data that you want to retrieve.