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

小结： 在关系数据库中，要实现对一个问题的查询，一般首先写出对问题查询的关系代数式,然后再将关系代数式转换成计算机可以接受的SQL语言，以实现在具体数据库管理系统（DBMS）上的数据查询 。 关系代数的八种操作：INTERSECTION（交?）, UNION（并?）, DIFFERENCE（差?）, PRODUCT（笛卡尔积X）, SELECTION（选择?）, PROJECTION（投影? ）, JOIN（连接 ）, and DIVIDSION（除?） 可以完成基本的查询要求。 请举例：用SQL语句描述： SELECT （where…）、 PROJECT([ ] )、 PRODUCT （X）、 JOIN （ ）操作。 用带 exists 的子查询语句可以完成 INTERSECTION（?）运 算 EXAMPLE 3.4.11 To set cid values of customers who order both products p01 and p07. The query used was: (Orders 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. 用带not exists的子查询语句可以完成DIFFERENCE（差?） EXAMPLE 3.4.12 ? To find cid values of customers who do not place any order through agent a03.  3.5 UNION Operators 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,=some,…) 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 capabilities). 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 .  The UNION Operator To provide the ? (UNION operator) of relational algebra, SQL requires a new type of Select syntax. Any number of Subq