第 10章 SQL高级应用

第10第1章章 SQL高级应用 网站与网页概述

1

第

10章

CHAPTER 10 SQL高级应用

练习题10参考答案

1．数据检索时使用COMPUTE和COMPUTE BY产生的结果有何不同？

答使用COMPUTE子句和COMPUTE BY子句都能既浏览明细数据，又看到统计的结果。只使用COMPUTE子句时，其查询的结果类似于总计；而使用COMPUTE BY子句时，其查询的结果将为带具体内容的分类进行统计。

2．进行连接查询时应注意什么？

答连接查询是指以指定表中的某个列或某些列作为连接条件，从两个或更多的表中查询关联数据的查询。进行连接查询时应注意以下几点：

? ? ? ? ?

一般而言，基于主键和外键指定查询条件，连接条件可使用“主键=外键”。 如果一个表有复合关键字，在连接表时，必须引用整个关键字。

应尽可能限制连接语句中表的数目，连接的表越多，查询处理的时间越长。 对于连接表的两个列应有相同或类似的数据类型。

不要使用空值作为连接条件，因为空值计算不会和其他任何值相等。

3．什么是交叉连接？

答交叉连接是两个表的笛卡尔积，即两个表的记录进行交叉组合。

4．内连接、外连接有什么区别？

答内连接是从结果中删除与其他被连接表中没有匹配行的所有行，因此内连接可能会丢失信息。外连接会把内连接中删除原表中的一些行保留下来，保留哪些行由外连接的类型决定。

5．外连接分为左外连接、右外连接和全外连接，它们有什么区别？ 答左外连接从结果中保留第一个表的所有行，但只包含第二个表中与第一个表匹配的行，第二个表相应的空行被放入NULL值。

右外连接从结果中保留第二个表的所有行，但只包含第一个表中与第二个表匹配的行，第一个表相应的空行被放入NULL值。

全外连接会把两个表所有行都显示在结果中，并尽可能多地匹配数据和连接条件。

6．什么是事务？事务的特点是什么？

答事务是指一个操作序列，这些操作序列要么都被执行，要么都不被执行，它是一个不

第10第1章章 SQL高级应用 网站与网页概述

2

可分割的工作单元。事务中任何一个语句执行时出错，系统都会返回到事务开始前的状态。事务是并发控制的基本单元，是数据库维护数据一致性的单位。在每个事务结束时，都能保持数据一致性。

7．对事务的管理包括哪几方面？

答在SQL Server中，对事务的管理包含3个方面。

? 事务控制语句：控制事务执行的语句。包括将一系列操作定义为一个工作单元来处

理。

? 锁机制：封锁正被一个事务修改的数据，防止其他用户访问到“不一致”的数据。 ? 事务日志：使事务具有可恢复性。

8．事务中能否包含CREATE DATABASE语句？ 答事务中不能包含CREATE DATABASE语句。

9．简述事务保存点的概念。

答保存点提供了一种机制，用于回滚部分事务。可以使用 SAVE TRANSACTION savepoint_name语句创建一个保存点，然后再执行ROLLBACK TRANSACTIONsavepoint_name语句回滚到该保存点，从而无须回滚到事务的开始。

在不可能发生错误的情况下，保存点很有用。在很少出现错误的情况下使用保存点回滚部分事务，比让每个事务在更新之前测试更新的有效性更为有效。更新和回滚操作代价很大，因此只有在遇到错误的可能性很小，而且预先检查更新的有效性的代价相对很高的情况下，使用保存点才会非常有效。

10．在应用程序中如何控制事务？

答应用程序主要通过指定事务启动和结束的时间来控制事务。主要使用Transact-SQL语句。系统还必须能够正确处理那些在事务完成之前便终止事务的错误。事务是在连接层进行管理。当事务在一个连接上启动时，在该连接上执行的所有的T-SQL语句在该事务结束之前都是该事务的一部分。

（1）启动事务

在SQL Server中，可以按显式、自动提交或隐性模式启动事务。

? 显式事务：通过发出BEGIN TRANSACTION语句显式启动事务。

? 自动提交事务：这是SQL Server的默认模式。每个单独的T-SQL语句都在其完成

后提交，不必指定任何语句控制事务。

? 隐性事务：通过T-SQL SET IMPLICIT_TRANSACTIONS ON语句，将隐性事务模

式设置为打开，下一个语句自动启动一个新事务，当该事务完成时，再下一个 T-SQL 语句又将启动一个新事务。 （2）结束事务

可以使用 COMMIT 或 ROLLBACK 语句结束事务。

? COMMIT：如果事务成功，则提交。COMMIT 语句保证事务的所有修改在数据库

中都永久有效。COMMIT 语句还释放资源，如事务使用的锁。

? ROLLBACK：如果事务中出现错误，或者用户决定取消事务，可回滚该事务。

ROLLBACK 语句通过将数据返回到它在事务开始时所处的状态，来恢复在该事务中所做的所有修改。ROLLBACK 还会释放由事务占用的资源。 11．什么是锁定？

第10第1章章 SQL高级应用 网站与网页概述

3

答在SQL Server 2005中，锁定就是给数据库对象加锁。使用锁定能确保事务完整性和数据库一致性。锁定可以防止用户读取正在由其他用户更改的数据，并可以防止多个用户同时更改相同数据。如果不使用锁定，则数据库中的数据可能在逻辑上不正确，并且对数据的查询可能会产生意想不到的结果。

12．什么是死锁？

答死锁是一种条件，不仅仅是在关系数据库管理系统 (RDBMS) 中发生，在任何多用户系统中都可以发生。当两个用户(或会话)具有不同对象的锁，并且每个用户需要另一个对象的锁时，就会出现死锁。每个用户都等待另一个用户释放他的锁。当两个连接陷入死锁时，SQL Server会进行检测，其中一个连接被选做死锁牺牲品，该连接的事务回滚，同时应用程序收到错误。

13．简述游标的概念。

答关系数据库中的操作会对整个行集产生影响。由SELECT语句返回的行集包括所有满足该语句WHERE子句中条件的行。由语句所返回的这一完整的行集被称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。游标通过以下方式扩展结果处理：

? ? ? ? ?

允许定位在结果集的特定行。

从结果集的当前位置检索一行或多行。

支持对结果集中当前位置的行进行数据修改。

为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。 提供脚本、存储过程和触发器中使用的访问结果集中的数据的T-SQL语句。

14．给出以下程序的执行结果。

USE school

SELECT sno,cno,degree FROM score

WHERE sno IN (103,105) ORDER BY sno

COMPUTE AVG(degree) BY sno GO

解：结果如下：

15．给出以下程序的执行结果。

USE school GO

SELECT teacher.tname AS '教师',student.sclass AS '班号', AVG(score.degree) AS '平均分' FROM student,course,score,teacher WHERE student.sno=score.sno AND course.cno=score.cno course.tno=teacher.tno

AND

GROUP BY teacher.tname,student.sclass WITH CUBE GO

第10第1章章 SQL高级应用 网站与网页概述

4

解：结果如下：

16．给出以下程序的执行结果。

USE school GO

BEGIN TRANSACTION Mytran --启动事务 INSERT INTO teacher

VALUES(999,'张英','男','1960/03/05','教授','计算机系') --插入一个教师记录 SAVE TRANSACTION Mytran --保存点 INSERT INTO teacher

VALUES(888,'胡丽','男','1982/8/04','副教授','电子工程系') --插入一个教师记录 ROLLBACK TRANSACTION Mytran COMMIT TRANSACTION GO

SELECT * FROM teacher --查询teacher表的记录 GO

DELETE teacher WHERE tno='999' --删除插入的记录 GO

解：结果如下：

17．编写一个程序，查询最高分的课程名。 解 采用子查询方式。程序如下：

USE school GO

SELECT cname FROM course WHERE cno=( SELECT cno FROM score WHERE degree=(SELECT MAX(degree) FROM score)) GO

18．编写一个程序，查询95033班的最高分的学生的学号、姓名、班号、课程号和分数。 解：程序如下：

USE school GO

SELECT s.sno,s.sname,s.sclass,sc.cno,sc.degree FROM student s,score sc

WHERE s.sno=sc.sno AND s.sclass='95033' AND sc.degree= (SELECT MAX(degree) FROM student,score

第10第1章章 SQL高级应用 网站与网页概述

WHERE student.sno=score.sno AND student.sclass='95033' ) GO

5

19．编写一个程序，查询平均分高于所有平均分的课程号。 解：程序如下：

USE school GO

SELECT cno,AVG(degree) FROM score GROUP BY cno

HAVING AVG(degree)>(SELECT AVG(degree) FROM score) GO

20．编写一个程序，创建一个新表stud，包含所有学生的姓名、课程名和分数，并以姓名排序。

解：程序如下：

USE school GO

SELECT s.sname,c.cname,sc.degree INTO stud

FROM student s,course c,score sc

WHERE s.sno=sc.sno AND c.cno=sc.cno AND sc.degree IS NOT NULL ORDER BY s.sname SELECT * FROM stud GO

21．编写一个程序，输出每个班最高分的课程名和分数。 解：采用数据来源为SELECT查询结果的方法。程序如下：

USE school GO

SELECT sclass,cname,MAX(degree) degree

FROM (SELECT s.sno,s.sname,s.sclass,c.cname,sc.degree FROM student s,course c,score sc

WHERE s.sno=sc.sno AND c.cno=sc.cno AND degree IS NOT NULL) T GROUP BY sclass,cname ORDER BY sclass GO

执行结果如下：

22．编写一个程序，采用游标方式输出所有课程的平均分。 解：程序如下：

USE school GO

--声明变量

DECLARE @c_name varchar(8),@s_avg float --声明游标

DECLARE st_cursor CURSOR

FOR SELECT course.cname,AVG(score.degree) FROM course,score

WHERE course.cno=score.cno AND score.degree IS NOT NULL GROUP BY course.cname --打开游标

OPEN st_cursor --提取第一行数据

FETCH NEXT FROM st_cursor INTO @c_name,@s_avg --打印表标题

PRINT '课程 平均分'

PRINT '-----------------' WHILE @@FETCH_STATUS = 0 BEGIN

--打印一行数据

PRINT @c_name+' '+CAST(@s_avg AS char(10)) --提取下一行数据

FETCH NEXT FROM st_cursor INTO @c_name,@s_avg END

--关闭游标

CLOSE st_cursor --释放游标

DEALLOCATE st_cursor GO

第10第1章章 SQL高级应用 网站与网页概述

6

其执行结果如下：

23．编写一个程序，采用游标方式输出所有学号、课程号和成绩等级。 解：程序如下：

USE school GO

--声明变量

DECLARE @no1 char(5),@no2 char(6),@fs char(2) --声明游标

DECLARE fs_cursor CURSOR FOR SELECT sno,cno, CASE

WHEN degree>=90 THEN 'A' WHEN degree>=80 THEN 'B' WHEN degree>=70 THEN 'C' WHEN degree>=60 THEN 'D' WHEN degree<60 THEN 'E' END

FROM score WHERE degree IS NOT NULL ORDER BY sno --打开游标

OPEN fs_cursor --提取第一行数据

FETCH NEXT FROM fs_cursor INTO @no1,@no2,@fs --打印表标题

PRINT '学号 课程号 等级' PRINT '-----------------' WHILE @@FETCH_STATUS = 0 BEGIN

--打印一行数据

PRINT @no1+' '+@no2+' '+@fs --提取下一行数据

FETCH NEXT FROM fs_cursor INTO @no1,@no2,@fs END

--关闭游标

CLOSE fs_cursor --释放游标

DEALLOCATE fs_cursor GO

其执行结果如下：

第10第1章章 SQL高级应用 网站与网页概述

7

24．编写一个程序，采用游标方式输出各班各课程的平均分。 解：程序如下：

USE school GO

--声明变量

DECLARE @bh char(5),@kc char(10),@fs float --声明游标

DECLARE fs_cursor CURSOR

FOR SELECT s.sclass,c.cname,AVG(sc.degree) FROM student s,course c,score sc

WHERE s.sno=sc.sno AND c.cno=sc.cno AND sc.degree IS NOT NULL GROUP BY s.sclass,c.cname --打开游标

OPEN fs_cursor --提取第一行数据

FETCH NEXT FROM fs_cursor INTO @bh,@kc,@fs --打印表标题

PRINT '班号 课程 平均分'

PRINT '---------------------------' WHILE @@FETCH_STATUS = 0 BEGIN

--打印一行数据

PRINT @bh+' '+@kc+' '+CAST(@fs AS varchar(10)) --提取下一行数据

FETCH NEXT FROM fs_cursor INTO @bh,@kc,@fs END

--关闭游标

CLOSE fs_cursor --释放游标

DEALLOCATE fs_cursor GO

其执行结果如下：

上机实验题5参考答案

在上机实验题4建立的factory数据库上，完成如下各题（所有SELECT语句的查询结果以文本格式显示）。 （1）删除factory数据库上各个表之间建立的关系。 （2）显示各职工的工资记录和相应的工资小计。

（3）按性别和部门名的所有组合方式列出相应的平均工资。

第10第1章章 SQL高级应用 网站与网页概述

8

（4）在worker表中使用以下语句插入一个职工记录：

INSERT INTO worker VALUES(20,'陈立','女','55/03/08',1,'75/10/10',4)

在depart表中使用以下语句插入一个部门记录：

INSERT INTO depart VALUES(5,'设备处')

对worker和depart表进行全外连接显示职工的职工号、姓名和部门名，然后删除这两个插入的记录。

（5）显示最高工资的职工的职工号、姓名、部门名、工资发放日期和工资。 （6）显示最高工资的职工所在的部门名。

（7）显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。 （8）采用游标方式实现（6）小题的功能。 （9）采用游标方式实现（7）小题的功能。 （10）先显示worker表中的职工人数，开始一个事务，插入一个职工记录，再显示worker表中的职工人数，回滚该事务，最后显示worker表中的职工人数。

操作过程

（1）删除factory数据库上各个表之间建立的关系的操作步骤如下：

①启动SQL Server管理控制器。

②在“对象资源管理器”中展开LCB-PC服务器节点。 ③展开“数据库”节点。 ④选中school，将其展开。

⑤展开“数据库关系图”节点。

⑥选中dbo.Diagram_1，右击，在出现的快捷菜单中选择“修改”命令，如图10.1所示。 ⑦在数据库关系图中，选择表示要从关系图中删除的关系的连接线（对于两条连线均进行⑦～⑨的操作）。

⑧右击关系线，从快捷菜单中选择“从数据库中删除关系”命令。 ⑨出现一个消息框，提示确认删除。单击“是”按钮。 ⑩在出现的对话框中单击“是”按钮保存所做的修改。

这样就将worker表和depart表以及worker表和salary表之间的关系删除了。

图10.1 factory数据库关系图

第10第1章章 SQL高级应用 网站与网页概述

9

（2）对应的程序如下：

USE factory GO

SELECT worker.职工号,worker.姓名,salary.工资 FROM worker,salary

WHERE worker.职工号=salary.职工号 ORDER BY worker.职工号,worker.姓名

COMPUTE SUM(salary.工资) BY worker.职工号 GO

执行结果如下：

职工号姓名工资

---------- ---------- ---------------------- 1 孙华 1201.5 1 孙华 12010.5 sum

---------------------- 2408

职工号姓名工资

---------- ---------- ---------------------- 10 陈涛 1245.8 10 陈涛 1250.8 sum

---------------------- 24910.6

职工号姓名工资

---------- ---------- ---------------------- 11 刘欣 1255 11 刘欣 1250 sum

---------------------- 2505

职工号姓名工资

---------- ---------- ---------------------- 12 李涵 1345 12 李涵 1350 sum

---------------------- 2695

职工号姓名工资

---------- ---------- ---------------------- 13 王小燕 1205 13 王小燕 1200 sum

---------------------- 2405

职工号姓名工资

---------- ---------- ---------------------- 14 李艺 1000.6 14 李艺 1005.6 sum

---------------------- 20010.2

职工号姓名工资

---------- ---------- ---------------------- 15 魏君 1105 15 魏君 1100 sum

---------------------- 2205

职工号姓名工资

---------- ---------- ---------------------- 2 孙天奇 905 2 孙天奇 900 sum

---------------------- 1805

职工号姓名工资

---------- ---------- ---------------------- 3 陈明 1350.6 3 陈明 1355.6 sum

---------------------- 27010.2

职工号姓名工资

---------- ---------- ---------------------- 4 李华 1500.5 4 李华 1505.5 sum

---------------------- 3006

职工号姓名工资

---------- ---------- ---------------------- 5 余慧 730 5 余慧 725 sum

---------------------- 1455

职工号姓名工资

---------- ---------- ---------------------- 6 欧阳少兵 1085 6 欧阳少兵 1085 sum

---------------------- 2170

职工号姓名工资

---------- ---------- ---------------------- 7 程西 755.8 7 程西 750.8 sum

---------------------- 15010.6

职工号姓名工资

---------- ---------- ---------------------- 8 张旗 728 8 张旗 733 sum

第10第1章章 SQL高级应用 网站与网页概述

10

本文来源：https://www.bwwdw.com/article/ecn6.html