《SQL必知必会》--第5课高级数据过滤及课后练习
部分示例语句
where+and
①从products表中查询prod_id,prod_price,prod_name,仅返回vend_id为DLL01且prod_price小于等于4美元的数据,并按照prod_name排序。
select prod_id,prod_price,prod_name from Products
where vend_id ='DLL01' AND prod_price<=4
order by prod_name;
where+or
②or操作符
如:从products表中查询vend_id,prod_name,prod_price,仅返回vend_id为DLL01或BRS01的数据。
select vend_id,prod_name,prod_price from [dbo].[Products]
where vend_id ='DLL01' or vend_id ='BRS01';
where+and+or,sql会优先处理and
③从products表中查询prod_name和prod_price,仅返回价格大于等于10美元 并且 vend_id为DLL01或BRS01 的数据。
--and or 连用,SQL优先处理and
select prod_name,prod_price from [dbo].[Products]
where prod_price >=10 AND vend_id='DLL01'
OR prod_price >=10 and vend_id='BRS01';
--或者写成
select prod_name,prod_price from [dbo].[Products]
where prod_price>=10 and (vend_id='DLL01' or vend_id='BRS01');
in操作符,用于指定条件范围
④上面③也可以写成如下形式:
select prod_name,prod_price from [dbo].[Products]
where prod_price>=10 and vend_id in ('DLL01','BRS01');
in和or的用法是相同的,但in更为简洁直观
⑤从products表中查询vend_id,prod_name,prod_price,仅返回vend_id为DLL01或BRS01的数据,并按照prod_name排序。
select vend_id,prod_name,prod_price from [dbo].[Products]
where vend_id in ('DLL01','BRS01')
order by prod_name;
not 否定紧跟其后的语句
⑥从products表中查询prod_name,并返回vend_id不是DLL01的所有数据,并按照prod_name排序。
select prod_name from [dbo].[Products]
where not vend_id ='DLL01'
order by prod_name;
课后练习
①从vendors表中查询vend_name,仅返回vend_country为USA且vend_state为CA的数据。
select vend_name from [dbo].[Vendors]
where vend_country='USA' and vend_state='CA';
②从OrderItems表中查询订购了总量100个的BR01,BR02或BR03的订单。
select order_num,prod_id,quantity from [dbo].[OrderItems]
where quantity>=100 and prod_id in ('BR01','BR02','BR03');
③从products表中查询prod_name,prod_price,返回价格在3到6美元之间的产品,并按价格对结果进行排序。
SELECT prod_name, prod_price
FROM products
WHERE prod_price >= 3 AND prod_price <= 6
ORDER BY prod_price;