34,333
edits
Changes
→Comparison Operators
<td>>=<td>Greater than or equal to</td>
<tr>
<td>BETWEEN''x'' AND ''y''<td>Falls between the two values''x'' and ''y''</td>
</table>
We have already used the equality (=) operator to check for numerical equality. We can also use this oprator to evaluate other data types, for example we can check for equality of strings. Suppose we need to find the price and product description of an 8Gb iPhone:
<pre>
SELECT prod_desc, prod_price FROM products WHERE prod_name = 'Apple iPhone 8Gb';
</pre>
The above statement would generate the following output on execution:
<pre>
+-------------+------------+
| prod_desc | prod_price |
+-------------+------------+
| Smart Phone | 399 |
+-------------+------------+
1 row in set (0.00 sec)
</pre>
Similarly, it is possible to list all products below a specified price threshold using the less than (<) operator:
<pre>
mysql> SELECT * from products WHERE prod_price < 200;
+-----------+----------------------------+-----------------------------+------------+
| prod_code | prod_name | prod_desc | prod_price |
+-----------+----------------------------+-----------------------------+------------+
| 1 | WildTech 250Gb 1700 | SATA Disk Drive | 120 |
| 3 | Microsoft 10-20 Keyboard | Ergonmoc Keyboard | 49 |
| 4 | EasyTech Mouse 7632 | Cordless Mouse | 49 |
| 6 | Buffalo AirStation Turbo G | Wireless Ethernet Bridge | 60 |
| 7 | Apple iPod Touch | Portable Music/Movie Player | 199 |
+-----------+----------------------------+-----------------------------+------------+
5 rows in set (0.00 sec)
</pre>
We can also retrieve data rows where a value does not match certain criteria. The following example will retrieve all rows where the price is not equal to (!=) 49:
<pre>
SELECT * from products WHERE prod_price != 49;
</pre>
== Searching within Range Values ==
In the table above the ''BETWEEN...AND'' operator was listed. The purpose of this operator is to select rows whcih fall between specified upper and lower ranges. For example, we can list all products in our table with a price BETWEEN 100 AND 200:
<pre>
mysql> SELECT * from products WHERE prod_price BETWEEN 100 AND 200;
+-----------+---------------------+-----------------------------+------------+
| prod_code | prod_name | prod_desc | prod_price |
+-----------+---------------------+-----------------------------+------------+
| 1 | WildTech 250Gb 1700 | SATA Disk Drive | 120 |
| 2 | Moto Razr | Mobile Phone | 200 |
| 7 | Apple iPod Touch | Portable Music/Movie Player | 199 |
+-----------+---------------------+-----------------------------+------------+
3 rows in set (0.02 sec)
</pre>
