关系数据库标准语言SQL11.pptVIP

1,2,本章首页,本节首页,上一页,（六）分组及计算查询,SQL语言是完备的，也就是说，只要数据按关系方式存入数据库，就能有构造合适的SQL命令把它检索出来。事实上，SQL不仅具有一般的检索能力，而且还有计算方式的检索，比如检索职工的平均工资、检索某个仓库中职工的最高工资值等。用于计算检索的函数有：,1COUNT计数 2SUM 求和 3AVG 计算平均值 4MAX 求最大值 5MIN 求最小值,这些函数可以用在SELECT短语中对查询结果进行计算。,3,本章首页,本节首页,上一页,例20找出供应商所在地的数目。,SELECT COUNT（DISTINCT 地址） FROM 供应商；,供应商关系共有三个地址：北京、西安和郑州，所以结果为三。注意，除非对关系中的元组个数进行计数，一般 COUNT函数应该使用DISTINCT 。 比如：,SELECT COUNT（*） FROM 供应商；,将给出供应商关系中的记录数。 供应商(供应商号, 单位, 地址),例21求支付的工资总数。,SELECT SUM（工资） FROM 职工；,这个结果是职工关系中工资值的总和，它并不管是否有重复值。这时若使用命令：,SELECT SUM（DISTINCT 工资） FROM 职工；,将得出错误的结果4910,4,例22求北京和上海的仓库职工的工资总和。,SELECT SUM（工资） FROM 职工； WHERE 仓库号 IN（SELECT 仓库号 FROM 仓库 WHERE 城市=“北京” OR 城市=“上海”）；,例23求所有职工的工资都多于1210元的仓库的平均面积。,SELECT AVF（面积） FROM 仓库 WHERE 仓库号 NOT IN （SELECT 仓库号 FROM 职工 WHERE 工资=1210);,本章首页,本节首页,上一页,5,本章首页,本节首页,上一页,这里要注意，以上结果的运算包含了尚没有职工的WH4仓库。如果要排除此仓库，语句应该改为：,SELECT AVG（面积） FROM 仓库 WHERE 仓库号 NOT IN （SELECT 仓库号 FROM 职工 WHERE 工资 =1210）AND 仓库号 IN （SELECT 仓库号 FROM 职工）；,例24求在WH2仓库工作的职工的 最高工资值。,SELECT MAX（工资） FROM 职工； WHERE 仓库号=“WH2”；,与MAX函数相对就的是MIN函数（求最小值）。比如，求最低工资值可以有如下命令：,SELECT MIN（工资） FROM 职工； WHERE 仓库号=“WH2”；,上面几个例子是对整个关系的计算查询，而利用GROUP BY子句进行分组计算查询使用的更加广泛。,6,本章首页,本节首页,上一页,例25求每个仓库的职工平均工资。,SELECT 仓库号，AVG（工资） FROM 职工 GROUP BY 仓库号；,在这个查询中，首先按仓库号属性进行分组，然后再计算每个仓库的平均工资。GROUP BY子句一般跟在WHERE 子句之后，没有 WHERE子句时，跟在FROM子句之后。另外，还可以根据多个属性进行分组。,在分组查询时，有时要求分组满足某个条件时才检索，这时可以用HAVING子句来限定分组。,例26求至少有两个职工的以上仓 库的平均工资。 !,SELECT 仓库号, COUNT(*), AVG(工资) FROM 职工； GROUP BY 仓库号 HAVING COUNT（*）=2；,HAVING子句总是跟在GROUP BY子句之后，不可以单独使用。 HAVING子句和WHERE子句不矛盾，在查询中是先用WHERE子句限定元组，然后进行分组，最后再用HAVING子句限定分组。,7,本章首页,本节首页,上一页,COMPUTE子句的格式,GROUP BY子句能完成汇总，但是却不能显示细节。当我们希望在分组的细节后显示汇总结果时，GROUP BY子句就无能为力了。,这时我们可以使用COMPUTE子句，该子句的功能和GROUP BY子句有相似之处，即可以完成分组汇总的功能；不同之处是利用COMPUTE子句，汇总结果是附加在细节之后显示的，这样用户既能看到细节，又能看到汇总行。,COMPUTE(),()BY,这里只能是用于SELECT计算查询的函数SUM、AVG、MIN、MAX和COUNT。,COMPUTE子句中的BY子句是用来说明分组的，如果在COMPUTE子句中不使用BY子句，则是对整个表进行汇总。,需要强调的是如果这里使用BY子句，也必须使用ORDER BY子句，BY 子句指出的列必须和ORDER BY 子句指出的列顺序相同，但BY子句的列数可以少于ORDER BY子句的列数。,8,9,本章首页,本节首页,上一页,比如，ORDER BY子句是：ORDER BY a,b,c,例27列出职工全部记录并计算各仓库的平均工资小计，最后给出全体职工的平均工资和工资总和（使用COMPUTEBY）。,SELECT 仓库号，职工号，工资 FROM 职工 ORDER BY 仓库号 COMPUTE AVG（工资），SUM（工资）BY 仓库号 COMPUTE AVG（工资），SUM（工资）；,10,sum = 1230 avg = 1232 sum = 6160,结果是： WH1 E3 1210 WH1 E7 1250 avg = 1230 sum = 2460 WH2 E1 1220 WH2 E4 1250,avg = 1235 sum = 2470 WH3 E6 1230 avg = 1230,例28列出职工全部记录并计算全体职工 的平均工资和工资总和（使用 COMPUTE）。,SELECT 仓库号，职工号，工资 FROM 职工 COMPUTE AVG（工资），SUM（工资）；,结果是：,WH2 E1 1220 WH1 E3 1210 WH2 E4 1250 WH3 E6 1230 WH1 E7 1250 avg = 1232 sum = 6160,11,(七) 利用空值查询,假设在订购单关系中，一名职工正在准备订购单，但尚未选定供应商，这样若把信息存入数据库，则供应商号和订购日期两个属性均为空值，如下表所示。,E6 OR77 E1 OR80 E3 OR90,注意，查询空值时要使用IS NULL；而=NULL是无效的，因为空值不是一个确定的值，所以不能用“=”这样的运算符进行比较。,例29找尚未确定供应商的订购单。,订购单,12,表4-4 常用的查询条件,EXISTS、NOT EXISTS UNIQUE ALL、ANY,13,例30列出已经确定了供应商的订购单信息。,E3 S7 OR67 1998/06/28 E1 S4 OR73 1998/07/28 E7 S4 OR76 1998/05/25 E3 S4 OR79 1998/06/13 E3 S3 OR91 1998/07/13,本章首页,本节首页,上一页,14,（一）简单查询,（二）连接查询,（三）嵌套查询,（四）几个特殊算符,（六）分组及计算查询,（七）利用空值查询,（五）排序查询,SQL查询语句,15,SELECT 城市 FROM 仓库 WHERE 仓库号 IN （SELECT 仓库号 FROM 职工 WHERE 职工号 IN （SELECT 职工号 FROM订 订购单 WHERE 供应商号 IN （SELECT 供应商号 FROM 供应商 WHERE 地址= “北京”）；,结果是: 北京 上海,（八）多层嵌套查询,例31找出哪些城市的仓库向北京的供应商发出了订购单。,这里我们所要求的信息出自仓库关系，但相关条件与订购单关系有关，为此又要涉及到职工关系和订购单关系和供应商关系，也就是说这个查询将涉及到四个关系。,事实上，我们希望检索的是仓库元组，最后这个订购单元组要对应一个地址属性值为“北京”的供应商元组。完成这个查询的SQL命令是：,16,例32给出有北京仓库订购单的北京供应商的名称。,SELECT 供应商名 FROM 供应商 WHERE 地址=“北京” AND 供应商号 IN （SELECT 供应商号 FROM 订购单 WHERE 职工号 IN （SELECT 职工号 FROM 职工 WHERE 仓库号 IN （SELECT 仓库号 FROM 仓库 WHERE 城市= “北京”）；,17,（九）别名和自连接查询,SELECT 供应商名 FROM 供应商，订购单，职工，仓库 WHERE 地址= “北京” AND 仓库.城市= “北京” AND 供应商.供应商号=订购单.供应商号 AND 订购单.职工号=职工.职工号 AND 订购单.仓库号=仓库.仓库号；,例33用连接操作完成上例的查询。,这是一个基于多个关系的连接操作。一般能用嵌套查询完成的操作，都能用等价的连接查询完成；但是，反之却不一定。一般当查询的数据出自多个关系时，就不适合用嵌套查询, 能用连接查询。,18,（九）别名和自连接查询,例33用连接操作完成上例的查询。,在连接操作中，经常需要使用关系名作前缀，有时这样显得很麻烦。因此，SQL允许在FROM短语中为关系名定义别名，格式为： ,比如，刚才的连接语句可以写为：,SELECT 供应商名 FROM 供应商 S，订购单 P，职工 E，仓库 W WHERE 地址=“北京” AND W.城市= “北京” AND S.供应商号=P.供应商号 AND P.职工号=E.职工号 AND E.仓库号=W.仓库号,19,在这个例子中，别名并不是必须的，但是在关系的自连接操作中，别名就是必不可少的了。SQL不仅可以对多个关系实行连接操作，也可以将同一关系与其自身进行连接，这种连接就称为自连接。在可以进行这种自连接操作的关系上，实际存在着一种特殊的递归联系，即关系中的一些元组，根据出自同一个值域的两个不同的属性，可以与另外一些元组有一种对应关系（一对多的联系）。,本章首页,本节首页,上一页,为了说明自连接，我们假设有一个雇员关系： 雇员（雇员号，雇员姓名，经理）,其中雇员号和经理两个属性出自同一个值域，同一元组的这两个属性值是“上、下级”关系。侧图说明了雇员关系和它的一个实例。,20,例34根据雇员关系列出上一级经理及其职员（被其领导）的清单。,SELECT S.雇员姓名，“领导”，E.雇员姓名； FROM 雇员 S，雇员 E WHERE S.雇员号= E.经理；,结果是： 赵涌 领导 钱潮 赵涌 领导 孙洁 孙洁 领导 李渌,这里通过定义别名形成了两个逻辑关系，一个是经理关系S，一个是职员关系E，结果在关系S和E上的连接实现了我们的检索要求。另外，在SELECT短语中可以有常量，如这里的 “领导”。,本章首页,本节首页,上一页,21,（十）内、外层互相关嵌套查询,事实上，有时也需要内、外层互相关的查询。,比如，我们在订购单关系中加入一个新字段总金额，说明完成该订购单所应付出的总金额数，新的订购单关系如下表所示。,订购单,例35列出每个职工经手的具有最高总金额的订购单信息。,SELECT out.职工号,out.供应商号,out.订购单号,out.订购日期,out.总金额 FROM 订购单 out WHERE 总金额=（SELECT MAX（总金额） FROM 订购单 inner WHERE out.职工号= inner.职工号）；,22,在这个查询中，外层查询和内层查询使用同一个关系，给它们分别指定别名out和inner。外层查询提供out关系中的每个元组的职工号值给内层查询使用；内层查询利用这个职工号值，确定该职工经手的具的最高总金额的订购单的总金额；随后外层查询再根据out关系的同一元组的总金额值与该总金额值进行比较，如果相等，则该元组被选择。,（十一）超（OUT）连接查询,在新的SQL标准中还支持两个新的关系连接运算符，它们与原来我们所了解的等值连接和自然连接不同。原来的连接是只有满足连接条件，相应的结果才会出现在结果表中；而这两个新的连接运算是，首先保证一个表中满足条件的元组都在结果表中，然后将满足连接条件的元组与另一表的元组进行连接，不满足连接条件的则将来自另一表的属性值置为空值。两个超连接运算符的含义见下表。,23,则结果是： WH1 北京 370 E3 1210 WH1 北京 370 E7 1250 WH2 上海 500 E1 1220 WH2 上海 500 E4 1250 WH3 广州 200 E6 1230,如果是左部超连接： SELECT 仓库.仓库号, 城市, 面积, 职工号, 工资 FROM 仓库, 职工 WHERE 仓库.仓库号*=职工.仓库号；,则结果是： WH1 北京 370 E3 1210 WH1 北京 370 E7 1250 WH2 上海 500 E1 1220 WH2 上海 500 E4 1250 WH3 广州 200 E6 1230 WH4 武汉 400,如果是自然连接： SELECT 仓库.仓库号, 城市, 面积, 职工号, 工资 FROM 仓库, 职工 WHERE 仓库.仓库号=职工.仓库号；,设有仓库和职工两个关系,24,作业 4.4 p103 学生（学号，姓名，性别，年龄，所在系） 课程（课程号，课程名，学分） 选课（学号，课程号，成绩） UPDATE 选课 SET 成绩 0 FROM 学生，课程，选课 WHERE (选课.学号 学生.学号 AND 选课.课程号课程.课程号) AND (姓名LIKE 刘* AND 课程名高等数学) UPDATE 学生信息 INNER JOIN (课程 INNER JOIN 成绩 ON 课程.课程号=成绩.课程号) ON 学生信息.学号=成绩.学号 SET 成绩.期末成绩 = 0 WHERE ( 学生信息.姓名 Like “刘*”) AND ( 课程.课程名 Like “高等数学”);,25,4.7 嵌入式 SQL,SQL语言提供了两种工作方式：交互式SQL、嵌入式 SQL 前面介绍的SQL，是作为独立的数据语言可以直接以交互的方式使用的，除此之外，SQL 还可以作为子语言嵌入在宿主语言中使用，这里所说的宿主语言就是指我们常见的高级程序设计语言，如C、C+、Java等等。下边主要介绍SQL作为子语言嵌入在宿主语言中使用的一般方法。,把SQL嵌入到宿主语言中使用必须要解决以下三个方面的问题：,1. 嵌入识别问题。宿主语言的编译程序不能识别SQL语句，所以首要的问 题就是要解决如何区分宿主语言的语句和SQL语句。 2. 宿主语言与SQL语言的数据交互问题。SQL语句的查询结果必须能够交 给宿主语言处理，宿主语言的数据也要能够交给SQL语句使用。 3. 语言的单记录与SQL的多记录的问题。宿主语言一般一次处理一条记录， 而SQL常常处理的是记录（元组）的集合，这个矛盾必须解决。,26,宿主语言的编译系统不能识别SQL语句。解决这个问题的一般方法是，为SQL语句加一个特殊的前缀，在用宿主语言的编译系统编译源程序之前，首先由预编译系统将SQL语句转换为宿主语言的合法函数调用。,常用的前缀格式是： EXEC SQL,如: EXEC SQL INSERT INTO 职工 VALUES（“WH8”，“E20”，1560）；,下图示意了嵌入了SQL的应用程序的执行过程。嵌入了SQL语句的应用程序，首先要经过预编译转换成宿主语言源程序，接着由宿主语言的编译系统产生中间代码，然后根据中间代码、宿主语言函数库和SQL函数据库连接产生可执行程序，执行程序时由SQL语句通过DBMS访问数据库。,应用程序,预编译,编 译,连 接,执行 应用程序,DBMS,源程序,SQL库函数,宿主语言 库函数,中间代码,可执行程序,数据库,（一）嵌入识别与预编译,27,（二）数据通信区与主变量,在嵌入使用SQL语句的程序中，一般在程序的前部都要有一条： INCLUDE SQLCA,这里的SQLCA即是SQL与宿主语言的通信区，它类似于结构变量，各个分量分别反映SQL语句的各种执行状态。,负责 SQL 语句与宿主语言语句数据交换的是主（Host）变量，这种变量既可以用在SQL语句中，又可以用在宿主语言语句中。,本章首页,本节首页,上一页,这种变量必须在DECLARE SECTION中说明，说明的格式是：,BEGIN DECLARE SECTION 主变量说明 END DECLARE SECTION,28,经以上方式说明的主变量可以在SQL语句中使用，只是用在SQL语句中时，必须冠以冒号前缀，如：,UPDATE 仓库 SET 面积= : wh_area WHERE 仓库号= : whnumb;,主变量用在宿主语言语句中时，和一般程序变量的使用方法是一样的。,本章首页,本节首页,上一页,下面是在C语言程序中说明主变量的例子：,EXEC SQL BEGIN DECLARE SECTION char whnumb5 char city12 int wh_area EXEC SQL END DECLARE SECTION;,29,（三）游标（Cursor）,宿主语言一般只能在单记录方式下工作，即一次处理一个记录。而SQL语句的查询结果常常是一张表，它包含多个记录，为此需要用 游标（Cursor）作为桥梁做一些特殊处理。与游标有关的命令共有四条：,DECLARE CURSOR OPEN FETCH CLOSE,通过下面的介绍我们会发现游标类似于文件的概念，游标有时的含义是整个“文件”，有时的含义是指向某个记录的指针。下面我们通过具体的几条语句来理解游标的概念。,定义游标的DECLARE CURSOR语句的格式是： EXEC SQL DECLARE CURSOR FOR ；,该语句用定义一个游标（文件），它的内容是: 的查询结果（多个记录组成的表）。,30,启动或打开游标（文件）的语句是OPEN，其格式是： EXEC SQL OPEN ；,该语句的功能是打开或启动指出的游标。 该游标名是用DECLAER CURSOR语句已经定义好的。,执行该语句意味着执行在DECLARE CURSOR语句中定义的SELECT查询，并使游标（指针）指向查询结果的第一条记录。,31,读记录的FETCH语句的格式是： EXEC SQL FETCH INTO :, :；,该语句的功能是取出游标所指记录并送入主变量，同时向前拨动游标，使游标指向下一条记录。这里的游标必须是已经说明并打开了的， INTO 后的主变量要与在DECLARE CURSOR中SELECT的字段相对应。当查询结果为空，或游标已经指向查询结果集的结尾时，SQLCA.sqlcode的代码将为-1，说明游标没有指向新记录。,关闭游标（文件）的CLOSE语句的格式是： EXEC SQL CLOSE ； 该语句的功能是关闭或停止指出的游标。,与程序设计语言中的文件相对照，DECLARE CURSOR 相当于说明了一个文件，OPEN相当于打开文件，FETCH相当于读一条记录，CLOSE相当于关闭文件。,EXEC SQL DECLARE CURSOR FOR ；,32,4.7.3 嵌入式SQL的使用技术,1. 不涉及游标的SQL DML 语句 【例4.43】给出在C语言中不涉及游标的嵌入式SQL DML 语句的使用 例子。 （1）在学生表中，根据共享变量sno的值查询学生的姓名、性别和年龄。 EXEC SQL SELECT 姓名， 性别， 年龄 INTO :name, :sex, :age FROM 学生 WHERE 学号= :sno; 这里，sno、name、sex、age都是共享变量，已在主程序中定义，并用SQL的DECLARE语句说明，在使用时加上 “：” 作为标识，以区别数据库中的变量。主程序中已先赋值给sno，SELECT的查询结果（一条记录）送到共享变量name、sex、age中。,33,（2）在学生表中插入一条新纪录，数据已在相关的共享变量中。 EXEC SQL INSERT 学生（学号，姓名，性别，年龄） VALUES ( :sno, :name, :sex, :age ); 如果某个属性值未给出，则自动置为空值。 （3）从选课表中删除一个学生的各个成绩，学号在共享变量sno中。 EXEC SQL DELETE FROM 选课 WHERE 学号= :sno; （4）将所有学生的“MATHS”课程成绩增加某个值（在共享变量a中）。 EXEC SQL UPDATE 选课 SET 成绩=成绩+ :a WHERE 课程号 IN (SELECT 课程号 FROM 课程 WHERE 课程名= MATHS );,34,2. 涉及游标的SQL DML 语句 当SELECT语句的查询结果为多个元组时，宿主语言程序无法使用，所以一定要有游标机制将多个元组一次一个地传送给宿主语言程序进行处理。具体有以下步骤： 先用游标定义语句定义一个游标和某个SELECT语句对应。 游标打开后，处于活动状态，此时游标指向查询结果集的第一个元组前。 每执行一次FETCH语句，游标指向下一个元组，并把其值送到共享变量，供程序处理，如此反复，直到所有查询结果处理完毕。 最后关闭游标；关闭的游标也可以重新打开，与新的查询结果集相对应，在没有打开前不能使用。,35,【例4.44】在教学数据库中检索成绩不及格的学生信息（学号，姓名， 课程，成绩），下面是该查询的一个C函数。 #define NO_MORE_TUPLES !(strcmp(SQLSTATE, “02000”) Void sel( ) EXEC SQL BEGIN DECLARE SECTION Char sno5, sname9, cname11; Int g; Char SQLSTATE6; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE scx CURSOR FOR SELECT 学生.学号，姓名，课程名，成绩 FROM 学生，课程，选课 WHERE 学生.学号=选课.学号 and 课程.课程号=选课.课程号 and 成绩60；,36,EXEC SQL OPEN scx; While(