5.1 组合使用WHERE子句
操作符（operator）也称为逻辑操作符（logical operator），用来联结或改变WHERE子句中的过滤条件。
5.1.1 AND操作符
在WHERE子句中利用AND操作符可以对不止一个列进行过滤。
可以增加多个过滤条件，每个条件间都要使用AND关键字。
MariaDB [sqlbzbh]> SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <=4;
+---------+------------+---------------------+
| prod_id | prod_price | prod_name |
+---------+------------+---------------------+
| BNBG01 | 3.49 | Fish bean bag toy |
| BNBG02 | 3.49 | Bird bean bag toy |
| BNBG03 | 3.49 | Rabbit bean bag toy |
+---------+------------+---------------------+
3 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
5.1.2 OR操作符
在WHERE子句中利用OR操作符可以检索出匹配任意其中一个条件的行。
MariaDB [sqlbzbh]> SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
+---------------------+------------+
| prod_name | prod_price |
+---------------------+------------+
| Fish bean bag toy | 3.49 |
| Bird bean bag toy | 3.49 |
| Rabbit bean bag toy | 3.49 |
| 8 inch teddy bear | 5.99 |
| 12 inch teddy bear | 8.99 |
| 18 inch teddy bear | 11.99 |
| Raggedy Ann | 4.99 |
+---------------------+------------+
7 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
5.1.3 求值顺序
WHERE子句可以包含任意数目的AND和OR操作符，并且允许两者结合以进行复杂、高级的过滤。
求值顺序：圆括号 》 AND操作符 》 OR操作符
使用圆括号可以明确地分组操作符，消除歧义。
MariaDB [sqlbzbh]> SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;
+---------------------+------------+
| prod_name | prod_price |
+---------------------+------------+
| Fish bean bag toy | 3.49 |
| Bird bean bag toy | 3.49 |
| Rabbit bean bag toy | 3.49 |
| 18 inch teddy bear | 11.99 |
| Raggedy Ann | 4.99 |
+---------------------+------------+
5 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT prod_name, prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
+--------------------+------------+
| prod_name | prod_price |
+--------------------+------------+
| 18 inch teddy bear | 11.99 |
+--------------------+------------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
5.2 IN操作符
IN操作符用来指定条件范围，取一组由逗号分隔合法值，并且这些值必须括在圆括号中。
简而言之，IN操作符与OR操作符具有相同的功能， 但IN操作符的语法更清楚、更直观，而且相比OR操作符执行得更快。
最大的优点：还可以包含其他SELECT语句，能够更动态地建立WHERE子句。
MariaDB [sqlbzbh]> SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01', 'BRS01') ORDER BY prod_name;
+---------------------+------------+
| prod_name | prod_price |
+---------------------+------------+
| 12 inch teddy bear | 8.99 |
| 18 inch teddy bear | 11.99 |
| 8 inch teddy bear | 5.99 |
| Bird bean bag toy | 3.49 |
| Fish bean bag toy | 3.49 |
| Rabbit bean bag toy | 3.49 |
| Raggedy Ann | 4.99 |
+---------------------+------------+
7 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name;
+---------------------+------------+
| prod_name | prod_price |
+---------------------+------------+
| 12 inch teddy bear | 8.99 |
| 18 inch teddy bear | 11.99 |
| 8 inch teddy bear | 5.99 |
| Bird bean bag toy | 3.49 |
| Fish bean bag toy | 3.49 |
| Rabbit bean bag toy | 3.49 |
| Raggedy Ann | 4.99 |
+---------------------+------------+
7 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
5.3 NOT操作符
WHERE子句中的NOT操作符，用来否定其后所跟的任何条件。
某些条件下，NOT操作符等同于!=操作符或<>操作符。
MariaDB [sqlbzbh]> SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;
+--------------------+
| prod_name |
+--------------------+
| 12 inch teddy bear |
| 18 inch teddy bear |
| 8 inch teddy bear |
| King doll |
| Queen doll |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT prod_name FROM Products WHERE vend_id <> 'DLL01' ORDER BY prod_name;
+--------------------+
| prod_name |
+--------------------+
| 12 inch teddy bear |
| 18 inch teddy bear |
| 8 inch teddy bear |
| King doll |
| Queen doll |
+--------------------+
5 rows in set (0.00 sec)