读书笔记--SQL必知必会05--高级数据过滤

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)