读书笔记--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)
行动是绝望的解药!
欢迎转载和引用,但请在明显处保留原文链接和原作者信息!
本博客内容多为个人工作与学习的记录,少数内容来自于网络并略有修改,已尽力标明原文链接和转载说明。如有冒犯,即刻删除!
以所舍,求所得,有所获,方所成。