第 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 否定跟在它之后的条件
