第 7 章 数据过滤
7.1 组合 WHERE 子句
MySQL 允许给出多个 WHERE 子句，这些子句可用用两种方式使用：AND 或 OR 操作符
7.1.1 AND 操作符
输入: SELECT server_cost.cost_name,server_cost.cost_value,server_cost.default_value FROM server_cost WHERE server_cost.cost_value IS NULL AND server_cost.default_value < 1;
输出:
+---------------------------+------------+---------------+
| cost_name | cost_value | default_value |
+---------------------------+------------+---------------+
| disk_temptable_row_cost | NULL | 0.5 |
| key_compare_cost | NULL | 0.05 |
| memory_temptable_row_cost | NULL | 0.1 |
| row_evaluate_cost | NULL | 0.1 |
+---------------------------+------------+---------------+
分析: 这条 SELECT 语句包含两个条件，并且用 AND 关键字连接它们，用来指示检索满足所有给定条件的行
7.1.2 OR 操作符
输入: SELECT server_cost.cost_name,server_cost.cost_value,server_cost.default_value FROM server_cost WHERE server_cost.default_value = 666 OR server_cost.default_value = 0.5;
输出:
+-------------------------+------------+---------------+
| cost_name | cost_value | default_value |
+-------------------------+------------+---------------+
| disk_temptable_row_cost | NULL | 0.5 |
+-------------------------+------------+---------------+
分析: OR 操作符告诉 DBMS 匹配任一条件而不是同时匹配两个条件
7.1.3 计算次序
输入: SELECT server_cost.cost_name,server_cost.cost_value,server_cost.default_value FROM server_cost WHERE server_cost.default_value = 666 OR server_cost.default_value = 0.5 AND server_cost.cost_value IS NULL;
输出: 结果有偏差
分析: AND 在计算次序中优先级更高，所以会优先处理 AND 操作符，所以会按照 WHERE server_cost.default_value = 666 AND server_cost.cost_value IS NULL 顺序来处理，应该使用圆括号明确地分组相应的操作符，如下
输入: SELECT server_cost.cost_name,server_cost.cost_value,server_cost.default_value FROM server_cost WHERE (server_cost.default_value = 666 OR server_cost.default_value = 0.5) AND server_cost.cost_value IS NULL;
输出:
+-------------------------+------------+---------------+
| cost_name | cost_value | default_value |
+-------------------------+------------+---------------+
| disk_temptable_row_cost | NULL | 0.5 |
+-------------------------+------------+---------------+
7.2 IN 操作符
输入: SELECT server_cost.cost_name,server_cost.cost_value,server_cost.default_value FROM server_cost WHERE server_cost.default_value IN (0.5,1);
输出:
+------------------------------+------------+---------------+
| cost_name | cost_value | default_value |
+------------------------------+------------+---------------+
| disk_temptable_row_cost | NULL | 0.5 |
| memory_temptable_create_cost | NULL | 1 |
+------------------------------+------------+---------------+
分析: IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN 取合法值由逗号分隔，全都包括在圆括号中
优点: 1. IN 的语法更清楚且更直观2. 计算的次序更容易管理3. IN 操作符比 OR 更快4. 可以包含其他 SELECT 语句
7.3 NOT 操作符
输入: SELECT server_cost.cost_name,server_cost.cost_value,server_cost.default_value FROM server_cost WHERE server_cost.default_value NOT IN (0.5,1);
输入:
+----------------------------+------------+---------------+
| cost_name | cost_value | default_value |
+----------------------------+------------+---------------+
| disk_temptable_create_cost | NULL | 20 |
| key_compare_cost | NULL | 0.05 |
| memory_temptable_row_cost | NULL | 0.1 |
| row_evaluate_cost | NULL | 0.1 |
+----------------------------+------------+---------------+
分析: NOT 否定跟在它之后的条件
