SQL数据过滤

使用where句子

select prod_name, prod_price
from products
where prod_price = 2.5;

检查单个值

匹配名字叫‘fuses’的值
select prod_name，prod_price
from products
where prod_name = 'fuses' ;

价格小于等于10
select prod_name，prod_price
from products
where prod_price <= 10;

不匹配检查

不匹配1003
select prod_id，prod_name
from products
where vend_id <> 1003;

不等于1003
select prod_id，prod_name
from products
where vend_id != 1003;

范围值检查

select prod_name，prod_price
from products
where prod_price BETWEEN 5 AND 10 ;

空值检查

select prod_name
from products
where prod_price IS NULL ;

注意
在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行

AND操作符

select prod_id ，prod_price， prod_name
from products
where vend_id = 1003 AND prod_price <=10 ;

OR操作符

select prod_name ，prod_price
from products
where vend_id = 1002 AND vend_price = 1003;

注意
and是两个条件都得满足
or是满足其中一个条件就行

计算次序

select prod_name ，prod_price
from products
where   (vend_id = 1002 OR vend_price = 1003 )  AND prod_price >=10;

IN操作符

select prod_name ，prod_price
from products
where vend_id  IN （1002，1003）
ORDER BY prod_name;

select prod_name ，prod_price
from products
where vend_id =1002 OR vend_id = 1003 
ORDER BY prod_name;

NOT 操作符

select prod_name ，prod_price
from products
where vend_id  NOT  IN （1002，1003）
ORDER BY prod_name;