Filter data
This chapter will teach you how to specify search criteria using the WHERE clause of a SELECT statement.
Use WHERE clause
Databases generally contain a large amount of data and rarely need to retrieve all rows in the table. Usually, only part of the table data will be extracted according to the requirements. To retrieve only the required data, you need to specify search criteria, which is also called filter condition.
In the SELECT statement, the data is filtered according to the search criteria specified in the WHERE clause. The WHERE keyword follows the FROM keyword.
mysql> SELECT prod_name, prod_price
-> FROM products
-> WHERE prod_price = 2.50;
+---------------+------------+
| prod_name | prod_price |
+---------------+------------+
| Carrots | 2.50 |
| TNT (1 stick) | 2.50 |
+---------------+------------+
2 rows in set (0.01 sec)
This statement retrieves two columns from the products table, but does not return all rows, only prod_ Lines with a price value of 2.50.
- SQL filtering and application filtering data can also be filtered at the application layer. Let the database return a large amount of original data, and then traverse and retrieve it in the client. However, this will greatly affect the client performance, and the client application will lack scalability. In addition, redundant data will lead to the waste of network broadband.
- When using ORDER BY and WHERE clauses at the same time, ORDER BY should be placed after WHERE, otherwise an error will occur (use of ORDER BY: MySQL must know and know -- Chapter 5 sorting and retrieving data).
WHERE clause operator
MySQL supports the following conditional operators:
| Operator | explain |
|---|---|
| = | be equal to |
| <>,!= | Not equal to |
| < | less than |
| <= | Less than or equal to |
| > | greater than |
| >= | Greater than or equal to |
| BETWEEN | Between two values specified |
Check individual values
Retrieve text value:
mysql> SELECT prod_name, prod_price
-> FROM products
-> WHERE prod_name = 'fuses';
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses | 3.42 |
+-----------+------------+
1 row in set (0.00 sec)
WHERE prod_name = 'fuses' statement, which returns prod_ The value of name is a row of fuses.
List all products with a price less than 10:
mysql> SELECT prod_name, prod_price
-> FROM products
-> WHERE prod_price < 10;
+---------------+------------+
| prod_name | prod_price |
+---------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Carrots | 2.50 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
+---------------+------------+
7 rows in set (0.00 sec)
List all products with a price less than or equal to 10:
mysql> SELECT prod_name, prod_price
-> FROM products
-> WHERE prod_price <= 10;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Bird seed | 10.00 |
| Carrots | 2.50 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
9 rows in set (0.00 sec)
Mismatch check
List all products not manufactured by supplier 1003:
mysql> SELECT vend_id, prod_name
-> FROM products
-> WHERE vend_id <> 1003;
+---------+--------------+
| vend_id | prod_name |
+---------+--------------+
| 1001 | .5 ton anvil |
| 1001 | 1 ton anvil |
| 1001 | 2 ton anvil |
| 1002 | Fuses |
| 1002 | Oil can |
| 1005 | JetPack 1000 |
| 1005 | JetPack 2000 |
+---------+--------------+
7 rows in set (0.01 sec)
The < > here can be replaced by! =.
- When you use quotation marks to observe the above example, you will see that the values of some filter conditions are enclosed in single quotation marks (such as' fuses'). Here, single quotation marks are used to qualify strings. In MySQL, quotation marks are required to compare a value with a column of string type. Values compared with numeric columns do not use quotation marks.
Range value check
To check the value of a range, you can use the BETWEEN operator. Its syntax is slightly different from other operators. It requires two values, the start value and the end value of the range.
Retrieve products with prices between 5 and 10:
mysql> SELECT prod_name, prod_price
-> FROM products
-> WHERE prod_price BETWEEN 5 AND 10;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Bird seed | 10.00 |
| Oil can | 8.99 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
5 rows in set (0.00 sec)
When using BETWEEN, you must specify its start AND end values. The two values must be separated by the AND keyword. During data filtering, the start value AND end value are also included in the result.
Null value check
When you create a table, you can specify whether its columns can contain no values. When a column does not contain a value, it is called NULL.
NULL has no value, which is different from a field containing 0, empty characters, or just spaces.
The SELECT statement has a special WHERE clause, the IS NULL clause, which is used to check columns with NULL values.
mysql> SELECT prod_name
-> FROM products
-> WHERE prod_price IS NULL;
Empty set (0.00 sec)
No data is returned when the column to be retrieved does not have a row with no value.
mysql> SELECT cust_id
-> FROM customers
-> WHERE cust_email IS NULL;
+---------+
| cust_id |
+---------+
| 10002 |
| 10005 |
+---------+
2 rows in set (0.01 sec)
This statement shows customers who have not filled in their email.
- NULL does not match. When filtering to select a row that does not have a specific value, you may want to return a row with a NULL value. However, unknown has special meaning. The database does not know whether they match. All rows with NULL values will not be returned during filtering.