数据库原理及应用6

1、量化比较谓词 expr some | any | all| (subquery) | (常数集) 为（,=,）等比较符 其中 some和any含义相同。但是大多数数据库产品支持some形式。例3.4.7 检索出佣金百分率最小的代理商的pidSelect aid from agents where percent=all(select percent from agents)例3.4.8 检索出与住在Dallas或Boston的顾客拥有相同折扣率的所有的顾客编号及其姓名。 （请大家思考！） Select cid,cname from customers where discnt in (sele

2、ct discnt from customers where city=Dallasor city=Boston)当然也等价于 Select cid,cname from customers where discnt=some (select discnt from customers where city=Dallasor city=Boston)实际上 expr in 与 expr =some 或 any 等价考虑 ： expr not in 与 exprsome 或any 等价吗？ 例3.4.9 检索出所有满足以下条件的顾客cid： 该顾客的discnt小于任一住在Duluth的顾客的

3、discnt 。 select cid from customers where discntany (select discnt from customers where city=Duluth) 对吗？问题在哪？正确SQL解法为： select cid from customers where discntall (select discnt from customers where city=Duluth) 故，expr not in 不等价于expr some 或any应，expr not in 等价于expr all EXISTS谓词表示为：not exists (Subquery)

4、 exists(subquery) 为真当且仅当子查询返回一个非空的集合。 not exists(subquery) 为真当且仅当子查询返回一个空的集合。 例3.4.10 检索通过代理商a05订货的顾客的名字。 方法一： select distinct C.cname from customers C where exists(select * from orders O where C.cid=O.cid and O.aid=a05)还有其他解法吗？请大家思考！譬如 select C.cname from customers C,orders O where C.cid=O.cid and

5、O.aid=a05Select distinct cname from customers C where cid in (select O.cid from orders O where O.aid=a05)例3.4.11 检索出既订购了产品p01又订购了产品p07的顾客的cid 。 （orders where pid=p01）cid intersect (orders where pid=p07)cid考虑如何使用基础SQL解决上述问题？ 方法一： Select distinct O1.cid from orders O1,orders O2 where O1.cid=O2.cid and

6、 O1.pid=p01and O2.pid=p07方法二：Select distinct cid from orders O where pid=p01 and exists( select * from orders where cid=O.cid and pid=p07) 回顾 上一节：检索没有通过代理商a05订货的所有顾客的名字。 （在此我们使用exists谓词来完成）Select distinct C.cname from customers C where not exists (select * from orders O where C.cid=O.cid and O.aid=a

7、05)当然我们也可以使用all谓词Select distinct C.cname from customers C where C.cid all (select cid from orders where aid=a05)说明：差运算minus，现在许多数据库产品还不支持这样的运算符，但是高级SQL中加入了这样的运算符。一般来说，当R和S是兼容表Head( R )=Head( S )=A1.An ，R-S 可以通过SQL语句表示为： select A1.Ai from R where not exists (select * from S where S.A1=R.A1 and and S.

8、An=R.An)例3.4.15 检索订购了产品p01的顾客所在的city 。 请大家考虑至少用三种以上的方法来实现！方法一： select distinct city from customers where cid in (select cid from orders where pid=p01)方法二： 可以使用与in 等价的=some 或any方法三： select distinct city from customers C where exists (select * from orders where cid=C.cid and pid=p01)方法四：Select distinc

9、t city from customers C,orders O where O.cid=C.cid and O.pid=p01 ；方法五： select distinct city from customers C where p01 in (select pid from orders where cid=C.cid)综上分析：SQL的一大特点就是灵活，但缺点是对一个需求存在过多的等价方法。课后作业：书后习题 3.2 (a) 、(b)、(c) 3.3 (a)、(b)、(c) 3.4 (a)3.5 SQL的并运算与除运算 前面用select语句模拟了关系运算符“减”、“交”、“选择”、“自然

10、连接”、“乘”等基本运算。我们这里来讨论如何实现“并”和“除”运算，这样Select就完成了对整个关系运算集合的模拟。 SQL语句使用union来实现“并”运算 Subquery unionall Subquery 但是子查询产生的表一定要是兼容的。（注：当前一些数据库系统允许select使用union， 但不允许在包含子查询的谓词中使用 。） 例3.5.1 建立一个包含了顾客所在的或者代理商所在或者两者皆在的城市名单。 select city from customers union select city from agents; 可以看到，若一个城市同时满足了上述条件，我们可能希望它在结

11、果表中显示两次，则可以： select city from customers union all select city from agents;考虑：如果有一个城市在customers和agents、 products中各出现了一次，那么： select city from customers union all (select city from agents union select city from products)该城市在结果集中会出现两次，若改为：select city from customers union (select city from agents union a

12、ll select city from products)那该城市在结果集中会出现几次？除法运算假定，我们需要检索出通过住在NewYork的所有代理商订货的顾客的cid 。关系代数表示为：O:=orders ；A:=agents；Ocid,aid (A where city=NewYork )aid那么，如何用SQL语句来实现呢？由于在SQL中没有与“除”等价的运算符，故，用SQL语句来实现“除”运算难度教大。首先，如何证明所有住在NewYork的代理商都为每个范围变量c所指定的行上的特定顾客c.cid订了货？显然，我们可以通过找出反例来反驳，即假定有一个住在NewYork的代理商没有为c.c

13、id订货。如果我们把该代理商命名为a.aid，那么我们将该条件表述为： cond1: a.city=NewYork and not exists (select * from orders x where x.cid=c.cid and x.aid=a.aid )该条件说明a.aid所代表的代理商住在NewYork，但是在orders表中由该代理商代理过的顾客cid中却没有一个是c.cid 。现在来证明所有住在New York的代理商确实都为c.cid所代表的顾客订了货，也就是说，我们要确保没有代理商的a.aid能使cond1为真，我们将该条件称为cond2 。cond2： not exist

14、s (select * from agents a where cond1 )最后得到该SQL的实现： select c.cid from customers c where cond2；那么，完整的形式为： select c.cid from customers c where not exists( select * from agents a where a.city=NewYork and not exists (select * from orders x where x.cid=c.cid and x.aid=a.aid )；分析：若当被检索的对象集合必须符合某个带有“所有”这类关

15、键词的条件，我们就按照如下步骤来执行：1、首先构造要检索的候选对象的一个反例。2、建立select 语句的搜索条件以选出步骤1 所创建的所有反例。3、建立包含步骤2所创建的语句的搜索条件， 说明不存在上面定义的那种反例。4、利用步骤3的搜索条件来建立最终的select例3.5.3 求出在NewYork或Duluth并订购了价格超过一美元的所有产品的代理商的aid。1、构造一个反例，存在一个价格超过一美元的产品没有被a.aid订购过。注：这个a.aid显然是住在NewYork或Duluth的。 cond1：p.price1.00 and not exists(select * from orde

16、rs x where x.pid=p.pid and x.aid=a.aid )；2、检索出满足cond1的所有产品pid select pid from products where cond1 ;3、建立表示这类反例不存在的条件：cond2： not exists (select pid from products p where p.price1.00 and not exists(select * from orders x where x.pid=p.pid and x.aid=a.aid )4、建立最终的select select a.aid from agents a where

17、 (a.city=NewYork or a.city=Duluth and not exists (select pid from products p where p.price1.00 and not exists(select * from orders x where x.pid=p.pid and x.aid=a.aid )练习 3.5.4 检索出订购了产品p01和价格超过一美元的所有产品的代理商的aid思考：1、构造一个反例，存在一个价格超过一美元的 产品没有被a.aid订购过。注：这个a.aid显然是订 购了p01的。cond1：p.price1.00 and not exists(select * from orders x where x.pid=p.pid and x.aid=a.aid )；2、检索出满足cond1的所有产品pid select pid from products where cond1 ;3、建立表示这类反例不存在的条件：cond2： not exists (select pid from products p where p.price1.00 and not exists(select * from orders x where x.pid=p.pid