SQL 必知必会第五课 高级数据过滤

SQL 必知必会第五课 高级数据过滤

select prod_id, prod_price, prod_name from Products where vend_id = 'DLL01' and prod_price <= 4;

select prod_id, prod_price, prod_name from Products where vend_id = 'DLL01' or vend_id = 'BRS01';

select prod_name, prod_price from Products where vend_id = 'DLL01' or vend_id = 'BRS01' and prod_price >= 10;
## 上一句求职语句出错，是因为and的运算优先级更高，结果是DLL01产品以及BRS01 超过十块的产品
select prod_name, prod_price from Products where (vend_id = 'DLL01' or vend_id = 'BRS01') and prod_price >= 10;

select prod_name, prod_price from Products where vend_id in ('DLL01', 'BRS01') order by prod_name;

select prod_name from Products where not vend_id = 'DLL01' order by prod_name;
select prod_name from Products where not vend_id <> 'DLL01' order by prod_name;


## Challenges 
select vend_name from Vendors where vend_country = 'USA' and vend_state = 'CA'; -- 1 

select order_num, prod_id, quantity from OrderItems where quantity >= 100 and prod_id in ('BR01','BR02','BR03'); -- 2

select prod_name, prod_price from Products where prod_price between 3 and 6 order by prod_price; -- 3

注意点

1. 若增加多个过滤条件，每个条件之间都要加一个and

2. and 的运算优先级比or高

in 的优点

1. 在有很多合法选项时，in的语法更清晰直观
2. 在与其他and和or操作符组合使用in时，求值顺序容易管理
3. in 操作符比or操作符执行更快
4. in 可以包含其他select语句，动态简历where子句