第10章 SQL高级应用

第十章 SELECT高级查询
10.1.1 数据汇总查询
生成简单汇总报表的应用程序：
– CUBE或ROLLUP运算符。
– COMPUTE或COMPUTE BY运算符
1．聚合函数 聚合函数用来完成一定的统计功能。 •聚合函数仅在下列项中允许作为表达式使用： • SELECT语句的选择列表（子查询或外部查询）。 • COMPUTE或COMPUTE BY子句； • HAVING子句。
group by student.class,ame
with cube
使用rollup汇总
CUBE 和 ROLLUP 之间的区别： 1、CUBE 生成的结果集显示了所选列中值的所 有组合的聚合。 2、ROLLUP 生成的结果集显示了所选列中值的 某一层次结构的聚合。
使用rollup汇总
select student.class as '班号', ame as '课程', avg(score.degree) as '平均分'
from student,course,score
where student.sno=score.sno and o=o
else
begin rollback transaction
print '选修操作系统的人数已满，张三同学不能选修此课程！'
end
4．在事务内设置保存点 保存点定义如果有条件地取消事务的一部分，事务 可以返回的位置。 •语法格式为： SAVE TRAN[SACTION] {savepoint_name |
• 一致性（Consistency）——事务在完成时，必
须使所有的数据都保持一致状态。
•隔离性（Isolation）——由并发事务所作的修
改必须与任何其它并发事务所作的修改隔离。
• 持久性（Durability）——事务完成之后，它 对于系统的影响是永久性的。
10.3.1 事务分类
按事务的启动和执行方式，可以将事务分为
COMMIT
GO
3．回滚事务
将事务回滚到事务的起点或事务内的某个保存点。
•语法格式如下：
ROLLBACK [TRAN[SACTION] [transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable]]
•用来清除遇到错误的事务。该事务修改的所有数据 都返回到事务开始时的状态。事务占用的资源将被释 放。
insert into student values(200,'王浩','男','1976/10/05','95031')
rollback transaction mytran
GO select * from student
GO
delete student where sno=200 go
5．标记事务 使用 BEGIN TRANSACTION 语句和 WITH MARK [description] 子句在事务日志中插入标记。由于标记
例10.1：为所有选修编号为3-105的课程的 成绩增加 10%。
BEGIN TRANSACTION MyTransaction GO USE 学生 GO UPDATE score SET degree = degree * 1.1 WHERE cno LIKE ‘3105’ GO COMMIT TRANSACTION MyTransaction GO
例10.5 计算各班的平均分和所有平均分。
select student.class as '班号',avg(score.degree) as '平 均分'
from student,course,score
where student.sno=score.sno group by student.class with rollup
order by sno
compute sum(degree) by sno
COMPUTE 和 GROUP BY的比较
•GROUP BY 生成单个结果集。每个组都有一个只包含分组依
据列和显示该组子聚合的聚合函数的行。选择列表只能包含
分组依据列和聚合函数。 •COMPUTE 生成多个结果集。一类结果集包含每个组的明细 行，其中包含选择列表中的表达式。另一类结果集包含组的 子聚合，或 SELECT 语句的总聚合。选择列表可包含除分组 依据列或聚合函数之外的其它表达式。聚合函数在 COMPUTE 子句中指定，而不是在选择列表中。
| @tran_name_variable
[WITH MARK ['description']]]
2．结束事务
标志一个事务的结束。
•语法格式如下： COMMIT [TRAN[SACTION] [transaction_name | @tran_name_variable]]
•如果没有遇到错误，使用该语句成功地结束事 务。该事务中的所有数据修改在数据库中都将永 久有效。事务占用的资源ble}
例10.3
USE 学生
GO BEGIN TRANSACTION mytran
insert into student values(100,'陈浩','男','1976/03/05','95033')
SAVE TRANSACTION mytran - -设置保存点
例10.2
USE 学生 GO BEGIN TRANSACTION insert into student values(100,'陈浩','男','1976/03/05','95033') Rollback GO select * from student GO --回滚事务 --启动事务
例10.2.1 学生张三打算选修“操作系统”
要求：这两项操作要么同时成功(转账成功)，要么同 时失败(转账失败)。只有其中一项操作成功则是不可接受 的事情。如果确实发生了只有其中一项操作成功的话， 那么应该撤消所做的操作(回滚事务)，就好象什么操作都 没有发生一样。
事物的四个属性:
• 原子性（Atomicity）——事务必须是原子工作 单元；对于其数据修改，要么全都执行，要么全 都不执行。
例10.3
计算各班的各课程的平均分。
select student.class as '班号', ame as ' 课程', avg(score.degree) as '平均分' from student,course,score
where student.sno=score.sno and
课程，根据要求，此门课选修的人数最多
为5人，该生是否可以选修此门课程，给出
结果提示信息。
BEGIN TRANSACTION
USE 学生 GO --在student表中插入张三的信息。 insert into student values('119','张三','男','19789-10','95032') declare @personnum tinyint,@cno char(5),@sno char(8) --查询“操作系统”的课程编号、“张三”的学号 select @cno=cno from course where cname='操作系统' select @sno=sno from student where sname='张三'
例10.1.1 将课程操作系统与数字电路的课号 互换。
BEGIN TRANSACTION
declare @cno1 char(5),@cno2 char(5)
select @cno1=cno from course where cname='操作系统' select @cno2=cno from course where cname='数字电路'
UPDATE course SET cno ='888' WHERE cname='操作系统‘
--暂时把“操作系统”课程的课程编号改为‘888’， --以避免违反PRIMARY KEY约束。
UPDATE course SET cno =@cno1 WHERE cname='数字电路'
UPDATE course SET cno =@cno2 WHERE cname='操作系统' GO
3类：
•
显式事务——可以显式地定义事务的启动和结束，每个
事务均以 BEGIN TRANSACTION 语句显式开始，以 COMMIT
或 ROLLBACK 语句显式结束。 • 自动提交事务——自动提交模式是SQL Server的默认事
务管理模式。每个 T-SQL语句完成时，都被提交或回滚。
如果一个语句成功地完成，则提交该语句；如果遇到错误， 则回滚该语句。
的名称与其事务相同，因此必需要有事务名称。
description 是标记的文本描述，是可选的。 •事务日志记录标记名称、描述、数据库、用户、 datetime 信息和日志序列号 (LSN)。
恢复到日志中的某个标记：
使用 RESTORE LOG 和 WITH
COMPUTE 子句需要下列信息：
•可选的 BY 关键字，该关键字可按对一列计算指定的行 聚合。 •行聚合函数名称；例如，SUM、AVG、MIN、MAX 或 COUNT。 •要对其执行行聚合函数的列。