MySql实验3——高级查询语句

1. IN嵌套查询

查询订购了“牡丹汽车股份有限公司”制造“放像机”的顾客。

Select * from customer where c_custkey in 
(select o_custkey from orders,supplier,part,lineitem,partsupp 
where s_name=”牡丹汽车股份有限公司” 
and p_name=”放像机” 
and p_partkey=l_partkey 
and l_suppkey=s_suppkey 
and o_orderkey=l_orderkey);

 2.单层EXISTS嵌套查询

查询没有购买过“牡丹汽车股份有限公司”制造“放像机”的顾客

Select * from c_ where not exists
(select * from orders,supplier,part ,lineitem ,partsupp 
where s_name=”牡丹汽车股份有限公司” 
and p_name=”放像机” 
and p_partkey=l_partkey 
and l_suppkey=s_suppkey 
and o_orderkey=l_orderkey 
and c_custkey=o.custkey);

3. 双层EXISTS嵌套查询

查询至少购买过顾客“王雅奇”购买过的全部零件的顾客姓名(不存在王雅奇购买过而C客户没有买过的零件)(注意别名的前缀）

Select distinct c_name from customer c 
where not exists
 (select * from customer c1,lineitem l,orders o 
where c1.c_name=”王雅奇” 
and c1.c_custkey=o.o_custkey 
and o.o_orderkey=l.l_orderkey 
and not exists 
(select * from lineitem l1,orders o1 
where c.c_custkey=o1.o_custkey 
and l1.l_partkey=l.l_partkey 
and l1.l_orderkey=o1.o_orderkey));

4. 集合查询

查询顾客“曹康”和“阿迪”都订购过的全部零件的信息

Select * from part ,customer,orders,lineitem ,partsupp ,supplier 
where c_name=”曹康” 
and c_custkey=o.custkey 
and l_partkey=ps_partkey 
and o_orderkey=l_orderkey 
and l_suppkey=s_suppkey 
and ps_partkey=p_partkey 
INTERSECT 
(Select * from part ,customer,orders,lineitem ,partsupp ,supplier 
where c_name=”阿迪” 
and c_custkey=o.custkey 
and l_partkey=ps_partkey 
and o_orderkey=l_orderkey 
and l_suppkey=s_suppkey 
and ps_partkey=p_partkey);