WHERE Clause Operators Operator Description
=
Equality
<>
Nonequality
!=
Nonequality
<
<=
Less than or
equal to
> Greater than
>=
Greater than
or equal to BETWEEN
Between two
specified values
BETWEEN AND
(jlive)[crashcourse]>SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
+----------------+------------+
| prod_name
+----------------+------------+
| .5 ton anvil
| 1 ton anvil
| Oil can
| TNT (5 sticks) |
| Bird seed
+----------------+------------+
5 rows in set (0.00 sec)
通配LIKE(_%)
(jlive)[crashcourse]>SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10 AND prod_name LIKE 'Oil%';
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Oil can
+-----------+------------+
1 row in set (0.00 sec)
(jlive)[crashcourse]>SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
+---------+-------------+
| prod_id | prod_name
+---------+-------------+
| ANV02
| ANV03
+---------+-------------+
2 rows in set (0.00 sec)
(AND) OR组合
(jlive)[crashcourse]>SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10 ORDER BY prod_price DESC;
+---------+----------------+------------+
| vend_id | prod_name
+---------+----------------+------------+
|
|
|
|
|
|
+---------+----------------+------------+
6 rows in set (0.00 sec)
(jlive)[crashcourse]>SELECT vend_id,prod_name,prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10 ORDER BY 3 DESC;
+---------+----------------+------------+
| vend_id | prod_name
+---------+----------------+------------+
|
|
|
|
+---------+----------------+------------+
4 rows in set (0.00 sec)
(NOT) IN
(jlive)[crashcourse]>SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY vend_id;
+---------+----------------+------------+
| vend_id | prod_name
+---------+----------------+------------+
|
|
|
|
|
|
|
|
|
+---------+----------------+------------+
9 rows in set (0.00 sec)
(jlive)[crashcourse]>SELECT
vend_id,prod_name,prod_price FROM products WHERE vend_id NOT
IN (1002,1003)
+---------+--------------+------------+
| vend_id | prod_name
+---------+--------------+------------+
|
|
|
|
|
+---------+--------------+------------+
5 rows in set (0.00 sec)
IS (NOT) NULL
(jlive)[crashcourse]>SELECT cust_id,cust_email FROM customers;
+---------+---------------------+
| cust_id | cust_email
+---------+---------------------+
|
|
|
|
|
+---------+---------------------+
5 rows in set (0.00 sec)
(jlive)[crashcourse]>SELECT cust_id,cust_email FROM customers WHERE cust_email IS NOT NULL;
+---------+---------------------+
| cust_id | cust_email
+---------+---------------------+
|
|
|
+---------+---------------------+
3 rows in set (0.00 sec)