数据库第三章5-9节.ppt

1、小结： 在关系数据库中，要实现对一个问题的查询，一般首先写出对问题查询的关系代数式,然后再将关系代数式转换成计算机可以接受的SQL语言，以实现在具体数据库管理系统（DBMS）上的数据查询 。 关系代数的八种操作：INTERSECTION（交）, UNION（并）, DIFFERENCE（差）, PRODUCT（笛卡尔积X）, SELECTION（选择）, PROJECTION（投影 ）, JOIN（连接 ）, and DIVIDSION（除） 可以完成基本的查询要求。,请举例：用SQL语句描述： SELECT （where）、 PROJECT( )、 PRODUCT （X）、 JOIN （ ）

2、操作。,含义？,所有订了货的cname,(customers orders) cname,Select cname from customers c, orders o where c.cid=o.cid,10,(customers orders) where c.cid=o.cid ) cname,用带 exists 的子查询语句可以完成 INTERSECTION（）运 算 EXAMPLE 3.4.11 To set cid values of customers who order both products p01 and p07.,The query used was: (Orders

3、 where pid=p01)cid(Orders where pid=p07) cid select distinct cid from orders x where pid =p01 and exists (select * from orders where cid=x.cid and pid = p07 ); select distinct x.cid from orders x, orders y where x.pid =p01 and x.cid =y.cid and y.pid=P07.,or,幻灯片 67,用带not exists的子查询语句可以完成DIFFERENCE（差）

4、 EXAMPLE 3.4.12 To find cid values of customers who do not place any order through agent a03.,CUSTMERScid - (ORDERS where aid= a03 ) cid,select c.cid from customers c where c.cid all (select cid from orders where aid=a03 );,Select c.cid from customers c where not exists (select * from orders x where

5、 x.cid=c.cid and x.aid=a03);,select c.cid from customers c where c.cid not in (select cid from orders where aid=a03 );,幻灯片 67,INTERSECTION（交）, DIFFERENCE（差）, PRODUCT（笛卡尔积X）, SELECTION（选择）, PROJECTION（投影 ）, and JOIN（连接 ）.,UNION（并） and DIVIDSION（除）,现在再来看看如何用SQL描述：,我们已经可以很方便地用SQL描述如下关系代数操作：,3.5 UNION O

6、perators and FOR ALL Conditions In Section 3.3 we saw how the SQL Select statement is capable of implementing the relational algebra operations of projection, selection, and product(join). In Section 3.4, we developed the power of the search condition, providing a number of new predicate (in,=all,=s

7、ome,) tests to create search-conditions involving Subqueries, and adding ways to emulate the relational operators of difference(not exist) and intersection (exist) to our bag of tricks for the Select statement.,In the current section, we will see how to perform union and division (which are new capa

8、bilities). This completes the set of relational operators, so it seems we can now express any relational algebra query in SQL form. However, in the next section we will introduce some new SQL operators for performing intersection, difference, and special join operations .,PROJECTION（select ), PRODUC

9、T（from）, SELECTION（where）, and JOIN,UNION（并） and DIVIDSION（除）,现在再来看看如何用SQL描述：,我们已经可以很方便地用SQL描述如下关系代数操作：,INTERSECTION（ exists ）, DIFFERENCE（ not exists ）.,The UNION Operator To provide the (UNION operator) of relational algebra, SQL requires a new type of Select syntax. Any number of Subqueries that

10、produce compatible tables can be combined with repeated use of the UNION syntax below: Subquery UNION ALL Subquery,（并）运算,EXAMPLE 3.5.1 We wish to create a list of cities where either a customer or an agent, or both, is based.,This can be accomplished by the following Select Statement:,Dallas Duluth

11、Kyoto New York Newark Tokyo,Customerscity,agents city,select city from customers union select city from agents;,It is conceivable that we would want to see the same city named twice if it fills both roles (more information is in such a result). and in that case we would use the statement select city

12、 from customers union all select city from agents;,Dallas Dallas Duluth Duluth Kyoto,New York Newark Tokyo New York Duluth Dallas,CUSTOMERS,AGENTS,As usual, we allow parenthesis around any expressions, and parentheses might be needed to differentiate between UNION and UNION ALL where three Subquerie

13、s or more are involved. For example consider the query (select city from customers union select city from agents) union all select city from products;,Dallas Newark Duluth Duluth Dallas Dallas Newark,Dallas Duluth Kyoto New York Newark Tokyo,Dallas Duluth Kyoto New York Newark Tokyo,(,),CUSTOMERS,AG

14、ENTS,products,In this case, if the city Chicago appears in customers and products but not in agents, it will appear twice in the result. But it would not appear twice in a Select statement, with the parentheses moved. select city from customers Union select city from agents union all select city fro

15、m products ;,Chicago Dallas Duluth Kyoto New York Newark Tokyo,Chicago Dallas Duluth Kyoto New York Newark Tokyo,Dallas Newark Duluth Duluth Dallas Dallas Newark Chicago,（,）,(,),(,),(,),INTERSECTION（交）, DIFFERENCE（差）, PRODUCT（笛卡尔积X）, SELECTION（选择）, PROJECTION（投影 ）, and JOIN（连接 ）, UNION（并）.,and DIVID

16、SION（除）,现在再来看看如何用SQL描述：,我们已经可以很方便地用SQL描述如下关系代数操作：,Division: SQL FOR ALL. Condition. Suppose that we need to find the cid values of customers who place orders with all agents based in New York.,请些出关系代数式,幻灯片 20,In relational algebra this query is solved by the following expression involving division:

17、ORDERScid,aid(AGENTS where city=NewYork )aid cid,aid(ORDERS) aid(City=New York (AGENTS) Unfortunately, there is no equivalent DIVIDEBY operator in SQL.,交（）, 并（）, 差（）, 笛卡尔积（X）, 选择（where）, 投影（ ）和 JOIN 关系代数运算能很容易地转换成 SQL命令，只有除（）运算，转换成 SQL命令比较困难。因为SQL没有提供专门描述除（）运算的谓词，所以，必须用其它的SQL谓词来表示除（）运算。,ORDERS,C001

18、a01, a02,a03, a04,a05, a06 C002 a03, a05 C003 a03 C004 a06 C006 a01, a03,a06,ORDERScid,aid(AGENTS where city=NewYork )aid,AGENTS,C001 a01,a02,a03,a04,a05,a06 C002 a03,a05 C003 a03 C004 a06 C006 a01,a03,a06,all agents based in New York,For readers who are familiar with mathematical logic, the approach we are taking, is based on the following tautology: 3.5.1 z(y p(z,y) z(y p(z,y) To restate 3.5.l in words, the following two statements are equivalent: (1 ) for all z, there exists a y such that the statement p(