《数据库第三章5-9节.ppt》由会员分享，可在线阅读，更多相关《数据库第三章5-9节.ppt（122页珍藏版）》请在三一办公上搜索。
1、小结：在关系数据库中，要实现对一个问题的查询，一般首先写出对问题查询的关系代数式,然后再将关系代数式转换成计算机可以接受的SQL语言，以实现在具体数据库管理系统（DBMS）上的数据查询。关系代数的八种操作：INTERSECTION（交）,UNION（并）,DIFFERENCE（差）,PRODUCT（笛卡尔积X）,SELECTION（选择）,PROJECTION（投影）,JOIN（连接）,and DIVIDSION（除）可以完成基本的查询要求。,请举例：用SQL语句描述：SELECT（where）、PROJECT()、PRODUCT（X）、JOIN（）操作。,含义？,所有订了货的cname,(c
2、ustomers 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.11To set cid values of customers who order both products p01 and p07.,The query used was:(Orders where pid=p01)cid(Orders where
3、 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（差）EXAMPLE 3.4.12 To find cid values of custom
4、ers 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 x.cid=c.cid and x.aid=a03);,select c.cid from custome
5、rs 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 Operators and FOR ALL ConditionsIn Section 3.3 we saw how the SQL
6、 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
7、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 no
8、w 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),PRODUCT（from）,SELECTION（where）,and JOIN,UNION（并）and DIVIDSION（除）,现在再来看看如何用SQL描述：,我们已经可以很方便地用S
9、QL描述如下关系代数操作：,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 produce compatible tables can be combined with repeated use of the UNION syntax below:Subquery UNIO
10、N ALL Subquery,（并）运算,EXAMPLE 3.5.1We 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 Kyoto New York Newark Tokyo,Customerscity,agents city,select city from customers union select city from
11、 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 statementselect city from customers union all select city from agents;,Dallas Dallas Duluth Duluth Kyoto,New York Newark Tokyo
12、 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 Subqueries or more are involved.For example consider the query(select city from customers union select city from agen
13、ts)union all select city from products;,DallasNewark Duluth Duluth Dallas Dallas Newark,Dallas Duluth Kyoto New York Newark Tokyo,Dallas Duluth Kyoto New York Newark Tokyo,(,),CUSTOMERS,AGENTS,products,In this case,if the city Chicago appears in customers and products but not in agents,it will appea
14、r 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 from products;,Chicago Dallas Duluth Kyoto New York Newark Tokyo,Chicago Dallas Duluth Kyoto New York Newark Tokyo,Dalla
15、sNewark Duluth Duluth Dallas Dallas Newark Chicago,（,）,(,),(,),(,),INTERSECTION（交）,DIFFERENCE（差）,PRODUCT（笛卡尔积X）,SELECTION（选择）,PROJECTION（投影）,and JOIN（连接）,UNION（并）.,and DIVIDSION（除）,现在再来看看如何用SQL描述：,我们已经可以很方便地用SQL描述如下关系代数操作：,Division:SQL FOR ALL.Condition.Suppose that we need to find the cid values of
16、 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:ORDERScid,aid(AGENTS where city=NewYork)aid cid,aid(ORDERS)aid(City=New York(AGENTS)Unfortunately,there is no equivalent DIVIDEBY o
17、perator in SQL.,交（）,并（）,差（）,笛卡尔积（X）,选择（where）,投影（）和 JOIN 关系代数运算能很容易地转换成 SQL命令，只有除（）运算，转换成 SQL命令比较困难。因为SQL没有提供专门描述除（）运算的谓词，所以，必须用其它的SQL谓词来表示除（）运算。,ORDERS,C001 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,a0
18、4,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:(
19、1)for all z,there exists a y such that the statement p(z,y)depending on z and y is true,and(2)it is false there exists a z such that no y exists with p(z,y)true.,同义反复,负负得正命题,We must find ways to emulate the relational operators of DIVIDEBY.,数理逻辑,z(y p(z,y)z(y p(z,y)把带有全称量词（For all）的谓词转换为等价的带有存在量词(ex
20、ists)的谓词，来模拟除（）运算。,SQL 中用exists 来描述存在量词，但没有提供描述全称量词的谓词。,In Example 3.5.2,z is a row in the agents table,with city New York;y is a row in the orders table;and p(z,y)says the agent in row z is connected by the order in row y to the customer c.,FOR ALL predicate we wish to create,non-existence of a cou
21、nterexample,幻灯片17,AGENTS,Percent：commission,佣金,ORDERS,EXAMPLE 3.5.2 Get the cid values of customers who place orders with all agents based in New York.,c001,select c.cid from customers c where not exists(select*from agents a where a.city=New York and not exists(select*from orders x where x.cid=c.cid
22、 and x.aid=a.aid);,/*select c.cid if.there is no agent a.id living in New York where no order row connects c.cid and a.aid*/,z(y p(z,y)z(y p(z,y),To find the cid values of customers who place orders with all agents based in New York.The approach we use is based on mathematical logic we mentioned bef
23、ore.First，we could disprove this by finding a counterexample:an agent based in New York that does not take an order for customer c.cid.（cond1）Then，we need to guarantee that there is no agent a.aid that makes cond1 TRUE.,反例,counterexample:an agent based in New York that does not take an order for c.c
24、id.If we designate this agent by a.aid,we can represent this counterexample as an SQL search-condition cond1:a.city=New York and not exists(select*from orders x where x.cid=c.cid and x.aid=a.aid),This states that the agent represented by a.aid is in New York and that no row in orders connects c.cid
25、to a.aid;that is,c.cid does not place an order through a.aid.,Now to prove that the specific customer represented by c.cid does place orders with all agents based in New York,we would have to come up with a condition guaranteeing that no counterexample exists of the kind we have just constructed.Tha
26、t is,we need to guarantee that there is no agent a.aid that makes cond1 TRUE.We can state this as a search-condition also,designated by cond2:cond2:not exists(select*from agents a where cond1),or writing it out in full:cond2:not exists(select*from agents a where a.city=New York and not exists(select
27、*from orders x where x.cid=c.cid and x.aid=a.aid).This is a very difficult condition to grasp,so we need to think about cond2 for a moment.The logic says that there does not exist an agent a.aid in New York that fails to place an order for c.cid(the range variable in c.cid is still unspecified).Cert
28、ainly this means that all agents in New York do place an order for c.cid.If you agree that cond2 has this meaning,then we are almost home,because all we need now is to retrieve all cid values that have the property of Cond2.we bring this all together in the following example.,EXAMPLE 3.5.2Get the ci
29、d values of customers who place orders with all agents based in New York.writing it out in full:select c.cid from customers c where/*select c.cid if.not exists(select*from agents a there is no agent a.id where a.city=New York and living in New York not exists(select*from orders x where no order row
30、where x.cid=c.cid and x.aid=a.aid);connects c.cid and a.aid*/,查询步骤类似于高级语言的循环嵌套。首先在customers中取第一条记录、agents中取第一条记录,看orders表,如果找到了满足条件的行，则false。false(false)=true。customers中的第一条记录就是要找的结果之一。再取customers中第二条记录，agents中取第一n条记录看是否有满足条件的行，。,c001,幻灯片 67,很多数据库方面的书中对于这类查询问题都是直接给出查询的SQL 语句，让人无法理解。,3.6 Some Advance
31、d SQL Syntax In Sections 3.1 through 3.5,we introduced the necessary SQL power to perform all relational algebra queries.In the current section we introduce some advanced SQL operators that are not uniformly available on all database systems,and are not part of Entry SQL-92.On the other hand,almost
32、all of them are in Core SQL-99,so they probably will be adopted in the future.,We start by explaining SQL operators duplicating the relational operations of intersection and difference.Later,well introduce a new syntax for what can occur in the FROM clause of a Select statement,including a wide rang
33、e of join operators.,The INTERSECT and EXCEPT Operators In Advanced SQL To emulate the(INTERSECT)and-(DIFFERENCE)operations of relational algebra,Full SQL-92 and SQL-99 provide two new operators,INTERSECT and EXCEPT These operators are used in the same way as the UNION operator diagrammed in Figure
34、3.9.Subquery union all|intersect all|except all subquery,EXAMPLE 3.6.1 Requesting cid values of customers who order both products p01 and p07.,There are a number of possible query forms that will solve this problem:select distinct cid from orders x where pid=p01 and exists(select*from orders where c
35、id=x.cid and pid=p07);,However,it is also possible to achieve this without a Subquery:select distinct x.cid from orders x,orders y where x.pid=p01 and x.cid=y.cid and y.pid=p07,The new INTERSECT operator gives us another solution:select cid from orders where pid=p01 intersect select cid from orders
36、where pid=p07,SQL server 2000:no intersect,except 不介绍,OUTER JOIN We are now going to discuss some syntax from Figure 3.11 that actually provides new power to SQL,because the operations provided by the syntax are not offered by classical relational algebra.These operations are the different forms of
37、OUTER JOIN.LEFT|RIGHT|FULL OUTER JOIN,Figure 3.11,As a first example,assume that we have two tables,S and T with the following content:,S,T,select*from s full outer join T using(A),select aname,aid,total from sales full outer Join agents using(aid);,Agents,Sale,A LEFT OUTER JOIN Left outer join will
38、 only preserve rows in the left-hand table in the result,even if there is no join with a row of the right-hand table,but will not do this for unmatched rows in the right-hand table.select aname,aid,total from sales left outer join agents using(aid);In this case,the row(null,a07,650.00)will appear in
39、 the result,but not(Gray,a04,null).,select aname,aid,total from sales left outer Join agents using(aid);,Agents,Sales,A RIGHT OUTER JOIN Right outer join will only preserve rows in the right-hand table in the result when there is no join with a row of the left-hand table.select aname,aid,total from
40、sales right outer join agents using(aid);Here,the row(Gray,a04,null)will appear in the result,but not(null,a07,650.00).,select aname,aid,total from sales right outer join agents using(aid);,Agents,Sales,作业：3.1 Create queries in SQL to answer the requests in Exercise 2.4(a),(c),(e),(g)and 2.5(a),(c),
41、(e),(g),(k),(o),(s),Summaries：,(customers orders)where c.cid=o.cid)cname,ROJECT PSELECT where.PRODUCT x JOIN DIFFERENCE INTERSECTION UNION,(customers orders)cname,Select cname from customers c,orders o where c.cid=o.cid,not exists,Subquery UNION ALL Subquery,Subquery intersect all subquery,Subquery
42、except all subquery,1.关系运算,exists,DIVIDEBY,not exists not exists,2.The Quantified Comparison Predicate,expr SOME|ANY|ALL(subquery),select aid from agents where commission=all(select commission from agents);,3.7 Set Functions in SQL We return now to features that have been implemented for some time b
43、y all serious database vendors.SQL provides five built-in functions that operate on sets of column values in tables:COUNT,MAX,MIN,SUM,and AVG.With the exception of COUNT,these set functions must operate on sets that consist of simple values-that is,sets of numbers or sets of character strings,rather
44、 than sets of rows with multiple column values.,EXAM.LE 3.7.1 Suppose that we wish to determine the total dollar amount of all orders.,ORDERS,select sum(dollars)as total from orders The answer printed for our example database will be the one-entry table Total 9802.00,9802.00,EXAMPLE 3.7.2To determin
45、e the total quantityof product p03 that has beenordered.,ORDERS,We can use the function SUM and restrict its application to the set of rows satisfying the appropriate restriction:select Sum(qty)as Total from orders where pid=p03;The answer is the table consisting of a single row Total 2400,EXAMPLE 3
46、.7.3 The query to find the total number of customers.,Uses the COUNT function and need not confine itself to a set of simple values.Either of the following forms is valid:select count(cid)from customers;5or select count(*)from customers;5,The first Select statement counts the number of values that o
47、ccur under the column cid.Note carefully that null values in a column are not counted.the two statements give the same answer in our case,because the create table statement did not allow null values in the cid column of the customers table.The set functions can be required to act on distinct values
48、fitting some description.,EXAMPLE 3.7.4 select count(distinct city)from customers;Get the number of cities where customers are based.The Select statement produces the number of distinct cities(3)where customers are based;once again,null values are not counted.,CUSTOMERS discount,幻灯片 56,select count(
49、city)from customers;5 The result in this second case is five.Note that the English-language request to gat the number of cities in which customers are based would normally be interpreted as meaning the number of distinct cities.So the second form is somewhat misleading.,Since two customers exist in
50、Dallas as well as two in Duluth in the CAP database the same query without the DISTINCT keyword:,EXAMPLE 3.7.5 List the cid values of all customers who have a discount less than the maximum discount.,select cid from customers where discnt(select max(discnt)from customers);,c001c003c004c006,EXAMPLE 3
