# SQL学习(二)

1.  IN谓词

IN谓词的通用表达式：expr in (Subquery)｜expr in (val {,val...})

例： 检索由住在Duluth的顾客和住在New York的代理商组成的所有订货记录的ordno值

select ordno from orders

where (cid,aid) in

(select cid,aid from customers c,agents a where c.city='Duluth' and a.city='New York');

NOT IN谓词为当且仅当求解出的expr值不在子查询返回的集合中[expr NOT IN (Subquery)]

2. 量化比较谓词

通用形式 expr θ{SOME｜ANY｜ALL} (Subquery) where θ is some operator in the set {<,<=,=,<>,>,>=}

例：找出佣金百分率最小的代理商的aid值

select aid from agents where percent <=all (select percent from agents);

谓词<>all与NOT IN等价

谓词<>SOME不与 NOT IN等价

3. 谓词EXISTS

通用形式 [NOT] EXISTS (Subquery)  注：EXISTS (Subquery) 为真当且仅当子查询返回一个非空集合

例：求出订购了产品P01又订购了产品P07的顾客的cid值

select distinct cid from orders x

where pid='p01' and exists

(select * from orders where cid=x.cid and pid='p07');

NOT EXISTS 查询效果可用 NOT IN和等价谓词<>ALL替代

4. SQL查询会有很多等价形式

例：检索订购了产品p01的顾客所在的city名这一请求的表达式，有四种主要的Select语句表达式

select distinct city from customers where cid in

(select cid from orders where pid='p01');

select distinct city from customers where cid =any

(select cid from orders where pid='p01');

select distinct city from customers c where exists

(select * from oredrs o where c.cid=o.cid and o.pid='p01');

select  distinct city from customers c, orders o

where c.cid=o.cid and o.pid='p01';

posted @ 2011-03-04 19:47  一杯浓茶  阅读(394)  评论(0编辑  收藏  举报