《关系数据库标准语言SQL4》由会员分享，可在线阅读，更多相关《关系数据库标准语言SQL4（41页珍藏版）》请在金锄头文库上搜索。
1、1职工仓库号职工号工资仓库仓库号城市面积供应商供应商号供应商名地址订购单职工号供应商号订购单号订购日期2本章首页本节首页上一页（六）分组及计算查询 SQL语言是完备的，也就是说，只要数据按关系方式存入数据库 ，就能有构造合适的SQL命令把它检索出来。事实上，SQL不仅具有 一般的检索能力，而且还有计算方式的检索，比如检索职工的平均工 资、检索某个仓库中职工的最高工资值等。用于计算检索的函数有：1COUNT计数 2SUM 求和 3AVG 计算平均值 4MAX 求最大值 5MIN 求最小值 这些函数可以用在SELECT短语中对查询结果进行计算。 3本章首页本节首页上一页例20找出供应商所在地的数目
2、。 SELECT COUNT（DISTINCT 地址） FROM 供应商； 供应商关系共有三个地址：北京、西安和郑州，所以结果为三。注意，除 非对关系中的元组个数进行计数，一般 COUNT函数应该使用DISTINCT 。 比 如： SELECT COUNT（*）FROM 供应商； 将给出供应商关系中的记录数。 供应商(供应商号, 单位, 地址)例21求支付的工资总数。 SELECT SUM（工资）FROM 职工； 结果是： 6160 这个结果是职工关系中工资值 的总和，它并不管是否有重复值。 这时若使用命令： SELECT SUM（DISTINCT 工资）FROM 职工； 将得出错误的结果49
3、10 职工仓库号职工号工资供应商供应商号供应商名地址4例22求北京和上海的仓库职工的工资总和。 SELECT SUM（工资） FROM 职工； WHERE 仓库号 IN（SELECT 仓库号FROM 仓库WHERE 城市=“北京” OR 城市=“上海”）； 结果是: 4930 例23求所有职工的工资都多于1210元的仓库的平均面积。 SELECT AVF（面积） FROM 仓库 WHERE 仓库号 NOT IN（SELECT 仓库号 FROM 职工WHERE 工资=2； WH1 2 1230 WH2 2 1235 结果是:HAVING子句总是跟在GROUP BY子句之后，不可以单独使用。HAV
4、ING子句和WHERE子句不 矛盾，在查询中是先用WHERE子句 限定元组，然后进行分组，最后再用 HAVING子句限定分组。 职工仓库号职工号工资7本章首页本节首页上一页COMPUTE子句的格式 GROUP BY子句能完成汇总，但是却不能显示细节。当我们希望在分组 的细节后显示汇总结果时，GROUP BY子句就无能为力了。这时我们可以使用COMPUTE子句，该子句的功能和GROUP BY子句 有相似之处，即可以完成分组汇总的功能；不同之处是利用COMPUTE子句 ，汇总结果是附加在细节之后显示的，这样用户既能看到细节，又能看到汇 总行。COMPUTE(),()BY, 这里只能是用于SELEC
5、T计算查询的函数 SUM、AVG、MIN、MAX和COUNT。 COMPUTE子句中的BY子句是用来说明分组的，如果在COMPUTE子句中不 使用BY子句，则是对整个表进行汇总。需要强调的是如果这里使用BY子句，也必须使用ORDER BY子句，BY 子句指出的列必须和ORDER BY 子句指出的列顺序相同，但BY子句的列数 可以少于ORDER BY子句的列数。8SELECTDISTINCT* *, ,FROM, ,WHEREBYGROUP, ,HAVINGBYORDERASCDESC；9本章首页本节首页上一页比如，ORDER BY子句是：ORDER BY a,b,c COMPUTEBy子句的三
6、种形式： COMPUTEBy a,b,c COMPUTEBy a,b COMPUTEBy a例27列出职工全部记录并计算各仓库的平均工资小计，最后给出全体职工 的平均工资和工资总和（使用COMPUTEBY）。 SELECT 仓库号，职工号，工资 FROM 职工 ORDER BY 仓库号 COMPUTE AVG（工资），SUM（工资）BY 仓库号 COMPUTE AVG（工资），SUM（工资）； 职工仓库号职工号工资10sum=1230avg=1232sum=6160结果是： WH1 E3 1210WH1 E7 1250avg=1230sum=2460WH2 E1 1220WH2 E4 1250
7、 avg=1235sum=2470WH3 E6 1230avg=1230例28列出职工全部记录并计算全体职工的平均工资和工资总和（使用COMPUTE）。 SELECT 仓库号，职工号，工资 FROM 职工 COMPUTE AVG（工资），SUM（工资）； 结果是：WH2 E1 1220 WH1 E3 1210 WH2 E4 1250 WH3 E6 1230 WH1 E7 1250avg=1232sum=6160 职工仓库号职工号工资11职工号供应商号订购单号订购日期 E3 E1 E7 E6 E3 E1 E3 E3OR67 OR73 OR76 OR77 OR79 OR80 OR90 OR91S7
8、 S4 S4S4S31998/06/23 1998/07/28 1998/05/251998/06/131998/07/13(七) 利用空值查询 假设在订购单关系中，一名职工正在准备订购单，但尚未选定供应商，这 样若把信息存入数据库，则供应商号和订购日期两个属性均为空值，如下表所 示。 E6 OR77 E1 OR80 E3 OR90 注意，查询空值时要使用IS NULL； 而=NULL是无效的，因为空值不是一 个确定的值，所以不能用“=”这样的运 算符进行比较。 SELECT * FROM 订购单 WHERE 供应商号 IS NULL； 结果是(参见上表):例29找尚未确定供应商的订购单。 订
9、购单12表4-4 常用的查询条件查询条件谓 词比 较=、=、!、! 比如，刚才的连接 语句可以写为： SELECT 供应商名 FROM 供应商 S，订购单 P，职工 E，仓库 W WHERE 地址=“北京”AND W.城市= “北京”AND S.供应商号=P.供应商号AND P.职工号=E.职工号AND E.仓库号=W.仓库号 职工仓库号职工号工资仓库仓库号城市面积供应商供应商号供应商名地址订购单职工号供应商号订购单号订购日期19在这个例子中，别名并不是必须的，但是在关系的自连接操作中，别名就 是必不可少的了。SQL不仅可以对多个关系实行连接操作，也可以将同一关系 与其自身进行连接，这种连接就
10、称为自连接。在可以进行这种自连接操作的关 系上，实际存在着一种特殊的递归联系，即关系中的一些元组，根据出自同一 个值域的两个不同的属性，可以与另外一些元组有一种对应关系（一对多的联 系）。 本章首页本节首页上一页为了说明自连接，我们假设有一个雇员关系：雇员（雇员号，雇员姓名，经理） 其中雇员号和经理两个属性出自同一个值域，同一元组的这两个属性值是 “上、下级”关系。侧图说明了雇员关系和它的一个实例。 20雇员姓名雇员号经理1：n雇员雇员号 雇员姓名经理 E3 E4 E6 E8赵涌 钱潮 孙洁 李渌E3 E3 E3 E6例34根据雇员关系列出上一级经理及其职员（被其领导）的清单。 SELECT
11、S.雇员姓名，“领导”，E.雇员姓名； FROM 雇员 S，雇员 E WHERE S.雇员号= E.经理； 结果是：赵涌 领导 钱潮赵涌 领导 孙洁孙洁 领导 李渌 这里通过定义别名形成了两个逻辑关系，一个是经理关系S，一个是职员 关系E，结果在关系S和E上的连接实现了我们的检索要求。另外，在SELECT 短语中可以有常量，如这里的 “领导”。 本章首页本节首页上一页21（十）内、外层互相关嵌套查询 事实上，有时也需要内、外层互相关的查询。 比如，我们在订购单关系中加入一个新字段总金额，说明完成该订购单所 应付出的总金额数，新的订购单关系如下表所示。 职工号 供应商号 订购单号 订购日期总金额
12、 E3 E1 E7 E6 E3 E1 E3 E3S7 S4 S4 S6 S4 S6 S6 S3OR67 OR73 OR76 OR77 OR79 OR80 OR90 OR911998/06/23 1998/07/28 1998/05/25 1998/06/29 1998/06/13 1998/07/29 1998/06/22 1998/07/13 35000 12000 7250 6000 30050 25600 7690 12560订购单例35列出每个职工经手的具有最高总金额的订购单信息。 SELECT out.职工号,out.供应商号,out.订购单号,out.订购日期,out.总金额 FR
13、OM 订购单 out WHERE 总金额=（SELECT MAX（总金额）FROM 订购单 innerWHERE out.职工号= inner.职工号）；22在这个查询中，外层查询和内层查询使用同一个关系，给它们分别指定 别名out和inner。外层查询提供out关系中的每个元组的职工号值给内层查询 使用；内层查询利用这个职工号值，确定该职工经手的具的最高总金额的订 购单的总金额；随后外层查询再根据out关系的同一元组的总金额值与该总金 额值进行比较，如果相等，则该元组被选择。 （十一）超（OUT）连接查询 在新的SQL标准中还支持两个新的关系连接运算符，它们与原来我们所 了解的等值连接和自然
14、连接不同。原来的连接是只有满足连接条件，相应的 结果才会出现在结果表中；而这两个新的连接运算是，首先保证一个表中满 足条件的元组都在结果表中，然后将满足连接条件的元组与另一表的元组进 行连接，不满足连接条件的则将来自另一表的属性值置为空值。两个超连接 运算符的含义见下表。 运算符含义*=在结果表中包含第一个表中满足条件的所有记录；如果是在连接条件 上匹配的元组，则第二个表返回相应值，否则第二个表返回空值。 =*在结果表中包含第二个表中满足条件的所有记录；如果是在连条件上 匹配的元组，则第一个表返回相应值，否则第一个表返回空值。 23则结果是：WH1 北京 370 E3 1210WH1 北京 370 E7 1250WH2 上海 500 E1 1220WH2 上海 500 E4 1250WH3 广州 200 E6 1230 如果是左部超连接： SELECT 仓库.仓库号, 城市, 面积, 职工号, 工资FROM 仓库, 职工WHERE 仓库.仓库号*=职工.仓库号； 则结果是：WH1 北京 370 E3 1210WH1 北
