MySQL过滤数据where

1. MySQL过滤数据

使用where子句
SQL> select prod_name, prod_price from products where prod_price = 2.50;

检查单个值
SQL> select prod_name, prod_price from products where prod_name = 'fuses';
SQL> select prod_name, prod_price from products where prod_price < 10;
SQL> select prod_name, prod_price from products where prod_price <= 10;

不匹配检查
SQL> select vend_id, prod_name from products where vend_id <> 1003;
SQL> select vend_id, prod_name from products where vend_id != 1003;

范围值检查
SQL> select prod_name, prod_price from products where prod_price between 5 and 10;

空值检查
SQL> select cust_id from customers where cust_email is null;

1. where 执行匹配时默认不区分大小写
2. is null 判断空值

2. MySQL数据过滤

组合where子句

AND操作符
SQL> select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <= 10;

OR操作符
SQL> select prod_name, prod_price, vend_id from products where vend_id = 1002 or vend_id = 1003;

计算次序
SQL> select prod_name, prod_price, vend_id from products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;

IN操作符
SQL> select prod_name, prod_price, vend_id from products where vend_id in (1002, 1003) order by prod_name;

NOT操作符
SQL> select prod_name, prod_price, vend_id from products where vend_id not in (1002, 1003) order by prod_name;

1. AND在计算次序中优先级高于OR
2. IN使用的好处
    1) 语法清楚更直观
    2) 计算次序更容易管理
    3) 比or操作符执行快
    4) 可以包含其他select语句，可以更动态地建立where子句
3. NOT关键字
    not支持in、between、exists子句取反

3. MySQL用通配符进行过滤

LIKE操作符

百分号(%)通配符
%表示任何字符出现任意次数
SQL> select prod_id, prod_name from products where prod_name like '%anvil%';
SQL> select prod_id, prod_name from products where prod_name like 's%e';

下划线(_)通配符
_表示任何字符出现一次
SQL> select prod_id, prod_name from products where prod_name like '_ ton anvil';