第 6 章 过滤数据
6.1 使用 where 子句
输入: SELECT global_grants.USER,WITH_GRANT_OPTION FROM global_grants WHERE WITH_GRANT_OPTION = 'N'; 输出: +------------------+-------------------+ | USER | WITH_GRANT_OPTION | +------------------+-------------------+ | mysql.infoschema | N | | mysql.session | N | | mysql.session | N | | mysql.session | N | | mysql.session | N | | mysql.session | N | | mysql.session | N | | mysql.session | N | | mysql.sys | N | +------------------+-------------------+ 分析: 这条语句从 global_grants 表中检索两个列，只返回 WITH_GRANT_OPTION 为 N 的行 注意: 同时使用 order by 和 where 语句时，应该让 order by 位于 where 之后，否则报错
6.2 WHERE 子句操作符
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN AND 在指定的两个值之间
IS NULL 空值
6.2.1 检查单个值
输入: SELECT global_grants.USER,global_grants.PRIV FROM global_grants WHERE global_grants.USER = 'mysql.sys'; 输出: +-----------+-------------+ | USER | PRIV | +-----------+-------------+ | mysql.sys | SYSTEM_USER | +-----------+-------------+ 分析: 检查 where global_grants.USER = 'mysql.sys' 语句，它返回 global_grants.USER 值为 mysql.sys 的行
6.2.2 不匹配检查
输入: SELECT global_grants.USER,global_grants.PRIV FROM global_grants WHERE global_grants.USER != 'root'; 输出: +------------------+----------------------------+ | USER | PRIV | +------------------+----------------------------+ | mysql.infoschema | SYSTEM_USER | | mysql.session | BACKUP_ADMIN | | mysql.session | CLONE_ADMIN | | mysql.session | CONNECTION_ADMIN | | mysql.session | PERSIST_RO_VARIABLES_ADMIN | | mysql.session | SESSION_VARIABLES_ADMIN | | mysql.session | SYSTEM_USER | | mysql.session | SYSTEM_VARIABLES_ADMIN | | mysql.sys | SYSTEM_USER | +------------------+----------------------------+ 分析: 检查 where global_grants.USER != 'root' 语句，它返回 global_grants.USER 值不为 root 的行
6.2.3 范围值检查
输入: SELECT server_cost.cost_name,server_cost.default_value FROM server_cost WHERE server_cost.default_value BETWEEN 0 AND 10; 输出: +------------------------------+---------------+ | cost_name | default_value | +------------------------------+---------------+ | disk_temptable_row_cost | 0.5 | | key_compare_cost | 0.05 | | memory_temptable_create_cost | 1 | | memory_temptable_row_cost | 0.1 | | row_evaluate_cost | 0.1 | +------------------------------+---------------+ 分析: 在使用 BETWEEN 时，必须指定两个值——所需范围的最低值和最高值。这两个值必须用 AND 关键字分隔
6.2.4 空值检查
输入: SELECT server_cost.cost_name,server_cost.cost_value FROM server_cost WHERE server_cost.cost_value IS NULL; 输出: +------------------------------+------------+ | cost_name | cost_value | +------------------------------+------------+ | disk_temptable_create_cost | NULL | | disk_temptable_row_cost | NULL | | key_compare_cost | NULL | | memory_temptable_create_cost | NULL | | memory_temptable_row_cost | NULL | | row_evaluate_cost | NULL | +------------------------------+------------+ 分析: IS NULL 语句可用来检查具有 NULL 值的列
