mysql过滤数据_MySQL过滤数据

1. MySQL过滤数据

使用where子句

select prod_name, prod_price from products where prod_price = 2.50;

检查单个值

select prod_name, prod_price from products where prod_name = 'fuses';

select prod_name, prod_price from products where prod_price < 10;

select prod_name, prod_price from products where prod_price <= 10;

不匹配检查

select vend_id, prod_name from products where vend_id <> 1003;

select vend_id, prod_name from products where vend_id != 1003;

范围值检查

select prod_name, prod_price from products where prod_price between 5 and 10;

空值检查

select cust_id from customers where cust_email is null;

1. where 执行匹配时默认不区分大小写

2. is null 判断空值

2. MySQL数据过滤

组合where子句

AND操作符

select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <= 10;

OR操作符

select prod_name, prod_price, vend_id from products where vend_id = 1002 or vend_id = 1003;

计算次序

select prod_name, prod_price, vend_id from products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;

IN操作符

select prod_name, prod_price, vend_id from products where vend_id in (1002, 1003) order by prod_name;

NOT操作符

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操作符

百分号(%)通配符

%表示任何字符出现任意次数

select prod_id, prod_name from products where prod_name like '%anvil%';

select prod_id, prod_name from products where prod_name like 's%e';

下划线(_)通配符

_表示任何字符出现一次

select prod_id, prod_name from products where prod_name like '_ ton anvil';