数据库原理与应用(10)第10章 SQL高级应用

【例10.5】 给出以下程序的执行结果。
USE school SELECT allocate.班号,allocate.课程号,teacher.姓名 FROM allocate JOIN teacher ON (allocate.教师编号=teacher.编号) ORDER BY allocate.班号

【例10.3】 给出以下程序的执行结果。
USE school SELECT student.班号,course.课程名,AVG(score.分数) AS '平均分' FROM student,course,score图10.3 程序执行结果 WHERE student.学号=score.学号 AND course.课程号=score.课程号 GROUP BY student.班号,course.课程名 WITH CUBE
COMPUTE BY子句可以用同一SELECT语句既查看明 细行，又查看汇总行。可以计算子组的汇总值，也可以计算 整个结果集的汇总值。COMPUTE 子句需要以下信息：
可选的BY关键字。该关键字可按对一列计算指定的 行聚合。 行聚合函数名称。例如，SUM、AVG、MIN、 MAX或COUNT。 要对其执行行聚合函数的列。
【例10.1】 给出以下程序的执行结果。
USE school SELECT 学号,课程号,分数 FROM score WHERE 学号 IN (103,105) ORDER BY 学号 COMPUTE SUM(分数)
解：该程序中SELECT语句使用简单COMPUTE子句生 成score表中分数列的求和总计。执行结果如下图所示。
（1）COMPUTE生成的结果集 COMPUTE所生成的汇总值在查询结果中显示为分离的结 果集。包括COMPUTE子句的查询的结果类似于控制中断报表， 即汇总值由指定的组来控制的报表。可以为各组生成汇总值， 也可以对同一组计算多个聚合函数。 当COMPUTE带有可选的BY子句时，符合SELECT条件的 每个组都有两个结果集：
第10章 SQL高级应用
10.1 SELECT高级查询
10.1.1 数据汇总 为决策支持系统生成聚合事务的汇总报表是一项复杂并 且相当消耗资源的工作。SQL Server 2005提供两个灵活且强 大的工具，即SQL Server 2005分析服务和报表服务。 但是对于生成简单汇总报表的应用程序，可使用以下运 算符： CUBE或ROLLUP运算符。均为GROUP BY子句 的一部分。 COMPUTE或COMPUTE BY运算符。均与 GROUP BY子句相关联。
在SQL Server 2005中，子查询还要受下面的条件限制： 通过比较运算符引入的子查询的选择列表只能包括一个 表达式或列名称（分别对SELECT *或列表进行EXISTS 和IN操作除外）。 如果外部查询的WHERE子句包括某个列名，则该子句 必须与子查询选择列表中的该列在连接上兼容。 子查询的选择列表中不允许出现ntext、text和image数据 类型。 由于不修改数据的比较运算符（指其后未接关键字IN、 ANY或ALL等）的引入，这类子查询必须返回单个值， 而且子查询中不能包括GROUP BY和HAVING子句。 包括GROUP BY的子查询不能使用DISTINCT关键字。 不能指定COMPUTE和INTO子句。
USE school
SELECT allocate.班号,allocate.课程号,teacher.姓名 FROM allocate INNER JOIN teacher ON (allocate.教师编号=teacher.编号) ORDER BY allocate.班号
2．外连接 仅当至少有一个同属于两表的行符合连接条件时，内连接 才返回行。内连接消除与另一个表中的任何行不匹配的行。而 外连接会返回FROM子句中提到的至少一个表或视图的所有行， 只要这些行符合任何WHERE或HAVING搜索条件。 将检索通过左外连接引用的左表的所有行，以及通过右外 连接引用的右表的所有行。全外连接中两个表的所有行都将返 回。 SQL Server 2005对在FROM子句中指定的外连接使用以下 关键字： LEFT OUTER JOIN或LEFT JOIN（左外连接） RIGHT OUTER JOIN或RIGHT JOIN（右外连接） FULL OUTER JOIN或FULL JOIN（全外连接）
（3）全外连接 若要通过在连接结果中包括不匹配的行保留不匹配信息， 可以使用全外连接。SQL Server 2005提供全外连接运算符 FULL OUTER JOIN，不管另一个表是否有匹配的值，此运算 符都包括两个表中的所有行。
【例10.8】 给出以下程序的执行结果。
USE school INSERT INTO allocate(班号,课程号) VALUES('1031','9-888') SELECT allocate.班号,allocate.课程号,teacher.姓名 FROM allocate FULL OUTER JOIN teacher ON (allocate.教师编号=teacher.编号) ORDER BY allocate.班号 DELETE allocate WHERE 班号='1031' AND 课程号='9-888'
【例10.2】 给出以下程序的执行结果。
USE school SELECT 学号,课程号,分数 FROM score WHERE 学号 IN (103,105) ORDER BY 学号 COMPUTE SUM(分数) BY 学号
解：该程序中的查询在COMPUTE子句中加入可选的BY 关键字，以生成每个组的小计。执行结果如下图所示。
10.1.2 复杂连接查询 通过连接，可以根据各个表之间的逻辑关系从两个或多个 表中检索数据。连接表示SQL Server 2005应如何使用一个表 中的数据来选择另一个表中的行。 可在FROM或WHERE子句中指定连接。连接条件与 WHERE和HAVING搜索条件组合，用于控制FROM子句引用 的基表中所选定的行。 在FROM子句中指定连接条件，有助于将这些连接条件与 WHERE子句中可能指定的其他搜索条件分开，指定连接时建 议使用这种方法。简单的子句连接语法如下： FROM 第一个表名 连接类型 第二个表名 [ON (连接条件)] 其中，连接类型有内连接、外连接或交叉连接。
（2）比较COMPUTE和GROUP BY的功能 COMPUTE和GROUP BY之间的区别汇总如下： GROUP BY生成单个结果集。每个组都有一个只包含分组 依据列和显示该组子聚合的聚合函数的行。选择列表只能 包含分组依据列和聚合函数。 COMPUTE生成多个结果集。一类结果集包含每个组的明 细行，其中包含选择列表中的表达式。另一类结果集包含 组的子聚合，或SELECT语句的总聚合。选择列表可包含 除分组依据列或聚合函数之外的其他表达式。聚合函数在 COMPUTE子句中指定，而不是在选择列表中。
（2）右外连接 右外连接简称为右连接，其结果中包括第二个命名表 （“右”表，出现在JOIN子句的最右边）中的所有行，不包括 左表中的不匹配行。
【例10.7】 给出以下程序的执行结果。
USE school INSERT INTO allocate(班号,课程号) VALUES('1031','9-888') SELECT allocate.班号,allocate.课程号,teacher.姓名 FROM allocate RIGHT JOIN teacher ON (allocate.教师编号=teacher.编号) ORDER BY allocate.班号 DELETE allocate WHERE 班号='1031' AND 课程号='9-888'
带ROLLUP参数会依据GROUP BY后面所列第一个字段 做汇总运算。 【例10.4】 给出以下程序的执行结果。图10.4 程序执行 结果
USE school SELECT student.班号,AVG(score.分数) AS '平均分' FROM student,course,score WHERE student.学号=score.学号 GROUP BY student.班号 WITH ROLLUP
（1）左外连接 左外连接简称为左连接，其结果包括第一个命名表（“左” 表，出现在JOIN子句的最左边）中的所有行，不包括右表中 的不匹配行。
【例10.6】 给出以下程序的执行结果。
USE school INSERT INTO allocate(班号,课程号) VALUES('1031','9-888') SELECT allocate.班号,allocate.课程号,teacher.姓名 FROM allocate LEFT JOIN teacher ON (allocate.教师编号=teacher.编号) ORDER BY allocate.班号 DELETE allocate WHERE 班号='1031' AND 课程号='9-888'
2．GROUP BY子句 GROUP BY子句的语法格式为：
[GROUP BY [ALL] 分组表达式 [,…n] [WITH {CUBE | ROLLUP }] ]
ALL。包含所有组和结果集，甚至包含那些任何行都不满 足WHERE子句指定的搜索条件的组和结果集。如果指定了 ALL，将对组中不满足搜索条件的汇总列返回空值。 CUBE。指定在结果集内，不仅包含由GROUP BY提供的 正常行，还包含汇总行。在结果集内返回每个可能的组和 子组组合的GROUP BY汇总行。GROUP BY汇总行在结果 中显示为NULL，但可用来表示所有值。 ROLLUP。指定在结果集内不仅包含由GROUP BY提供的 正常行，还包含汇总行。按层次结构顺序，从组内的最低 级别到最高级别汇总组。