第10章sql高级应用.docx
《第10章sql高级应用.docx》由会员分享，可在线阅读，更多相关《第10章sql高级应用.docx（27页珍藏版）》请在冰豆网上搜索。
第10章sql高级应用
第10章
SQL高级应用
练习题10参考答案
1．数据检索时使用COMPUTE和COMPUTEBY产生的结果有何不同？
答使用COMPUTE子句和COMPUTEBY子句都能既浏览明细数据，又看到统计的结果。
只使用COMPUTE子句时，其查询的结果类似于总计；而使用COMPUTEBY子句时，其查询的结果将为带具体内容的分类进行统计。
2．进行连接查询时应注意什么？
答连接查询是指以指定表中的某个列或某些列作为连接条件，从两个或更多的表中查询关联数据的查询。
进行连接查询时应注意以下几点：
一般而言，基于主键和外键指定查询条件，连接条件可使用“主键=外键”。
如果一个表有复合关键字，在连接表时，必须引用整个关键字。
应尽可能限制连接语句中表的数目，连接的表越多，查询处理的时间越长。
对于连接表的两个列应有相同或类似的数据类型。
不要使用空值作为连接条件，因为空值计算不会和其他任何值相等。
3．什么是交叉连接？
答交叉连接是两个表的笛卡尔积，即两个表的记录进行交叉组合。
4．内连接、外连接有什么区别？
答内连接是从结果中删除与其他被连接表中没有匹配行的所有行，因此内连接可能会丢失信息。
外连接会把内连接中删除原表中的一些行保留下来，保留哪些行由外连接的类型决定。
5．外连接分为左外连接、右外连接和全外连接，它们有什么区别？
答左外连接从结果中保留第一个表的所有行，但只包含第二个表中与第一个表匹配的行，第二个表相应的空行被放入NULL值。
右外连接从结果中保留第二个表的所有行，但只包含第一个表中与第二个表匹配的行，第一个表相应的空行被放入NULL值。
全外连接会把两个表所有行都显示在结果中，并尽可能多地匹配数据和连接条件。
6．什么是事务？
事务的特点是什么？
答事务是指一个操作序列，这些操作序列要么都被执行，要么都不被执行，它是一个不可分割的工作单元。
事务中任何一个语句执行时出错，系统都会返回到事务开始前的状态。
事务是并发控制的基本单元，是数据库维护数据一致性的单位。
在每个事务结束时，都能保持数据一致性。
7．对事务的管理包括哪几方面？
答在SQLServer中，对事务的管理包含3个方面。
事务控制语句：
控制事务执行的语句。
包括将一系列操作定义为一个工作单元来处理。
锁机制：
封锁正被一个事务修改的数据，防止其他用户访问到“不一致”的数据。
事务日志：
使事务具有可恢复性。
8．事务中能否包含CREATEDATABASE语句？
答事务中不能包含CREATEDATABASE语句。
9．简述事务保存点的概念。
答保存点提供了一种机制，用于回滚部分事务。
可以使用SAVETRANSACTIONsavepoint_name语句创建一个保存点，然后再执行ROLLBACKTRANSACTIONsavepoint_name语句回滚到该保存点，从而无须回滚到事务的开始。
在不可能发生错误的情况下，保存点很有用。
在很少出现错误的情况下使用保存点回滚部分事务，比让每个事务在更新之前测试更新的有效性更为有效。
更新和回滚操作代价很大，因此只有在遇到错误的可能性很小，而且预先检查更新的有效性的代价相对很高的情况下，使用保存点才会非常有效。
10．在应用程序中如何控制事务？
答应用程序主要通过指定事务启动和结束的时间来控制事务。
主要使用Transact-SQL语句。
系统还必须能够正确处理那些在事务完成之前便终止事务的错误。
事务是在连接层进行管理。
当事务在一个连接上启动时，在该连接上执行的所有的T-SQL语句在该事务结束之前都是该事务的一部分。
（1）启动事务
在SQLServer中，可以按显式、自动提交或隐性模式启动事务。
显式事务：
通过发出BEGINTRANSACTION语句显式启动事务。
自动提交事务：
这是SQLServer的默认模式。
每个单独的T-SQL语句都在其完成后提交，不必指定任何语句控制事务。
隐性事务：
通过T-SQLSETIMPLICIT_TRANSACTIONSON语句，将隐性事务模式设置为打开，下一个语句自动启动一个新事务，当该事务完成时，再下一个T-SQL语句又将启动一个新事务。
（2）结束事务
可以使用COMMIT或ROLLBACK语句结束事务。
COMMIT：
如果事务成功，则提交。
COMMIT语句保证事务的所有修改在数据库中都永久有效。
COMMIT语句还释放资源，如事务使用的锁。
ROLLBACK：
如果事务中出现错误，或者用户决定取消事务，可回滚该事务。
ROLLBACK语句通过将数据返回到它在事务开始时所处的状态，来恢复在该事务中所做的所有修改。
ROLLBACK还会释放由事务占用的资源。
11．什么是锁定？
答在SQLServer2005中，锁定就是给数据库对象加锁。
使用锁定能确保事务完整性和数据库一致性。
锁定可以防止用户读取正在由其他用户更改的数据，并可以防止多个用户同时更改相同数据。
如果不使用锁定，则数据库中的数据可能在逻辑上不正确，并且对数据的查询可能会产生意想不到的结果。
12．什么是死锁？
答死锁是一种条件，不仅仅是在关系数据库管理系统（RDBMS）中发生，在任何多用户系统中都可以发生。
当两个用户（或会话）具有不同对象的锁，并且每个用户需要另一个对象的锁时，就会出现死锁。
每个用户都等待另一个用户释放他的锁。
当两个连接陷入死锁时，SQLServer会进行检测，其中一个连接被选做死锁牺牲品，该连接的事务回滚，同时应用程序收到错误。
13．简述游标的概念。
答关系数据库中的操作会对整个行集产生影响。
由SELECT语句返回的行集包括所有满足该语句WHERE子句中条件的行。
由语句所返回的这一完整的行集被称为结果集。
应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。
这些应用程序需要一种机制以便每次处理一行或一部分行。
游标就是提供这种机制的结果集扩展。
游标通过以下方式扩展结果处理：
允许定位在结果集的特定行。
从结果集的当前位置检索一行或多行。
支持对结果集中当前位置的行进行数据修改。
为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。
提供脚本、存储过程和触发器中使用的访问结果集中的数据的T-SQL语句。
14．给出以下程序的执行结果。
USEschool
SELECTsno,cno,degree
FROMscore
WHEREsnoIN（103,105）
ORDERBYsno
COMPUTEAVG（degree）BYsno
GO
解：
结果如下：
15．给出以下程序的执行结果。
USEschool
GO
SELECTAS'教师',AS'班号',
AVGAS'平均分'
FROMstudent,course,score,teacher
WHERE=AND=AND=
GROUPBY,WITHCUBE
GO
解：
结果如下：
16．给出以下程序的执行结果。
USEschool
GO
BEGINTRANSACTIONMytran--启动事务
INSERTINTOteacher
VALUES（999,'张英','男','1960/03/05','教授','计算机系'）
--插入一个教师记录
SAVETRANSACTIONMytran--保存点
INSERTINTOteacher
VALUES（888,'胡丽','男','1982/8/04','副教授','电子工程系'）
--插入一个教师记录
ROLLBACKTRANSACTIONMytran
COMMITTRANSACTION
GO
SELECT*FROMteacher--查询teacher表的记录
GO
DELETEteacherWHEREtno='999'--删除插入的记录
GO
解：
结果如下：
17．编写一个程序，查询最高分的课程名。
解采用子查询方式。
程序如下：
USEschool
GO
SELECTcname
FROMcourse
WHEREcno=（SELECTcno
FROMscore
WHEREdegree=（SELECTMAX（degree）
FROMscore））
GO
18．编写一个程序，查询95033班的最高分的学生的学号、姓名、班号、课程号和分数。
解：
程序如下：
USEschool
GO
SELECT,,,,
FROMstudents,scoresc
WHERE=AND='95033'AND=
（SELECTMAX（degree）
FROMstudent,score
WHERE=AND='95033'
）
GO
19．编写一个程序，查询平均分高于所有平均分的课程号。
解：
程序如下：
USEschool
GO
SELECTcno,AVG（degree）
FROMscore
GROUPBYcno
HAVINGAVG（degree）>（SELECTAVG（degree）FROMscore）
GO
20．编写一个程序，创建一个新表stud，包含所有学生的姓名、课程名和分数，并以姓名排序。
解：
程序如下：
USEschool
GO
SELECT,,
INTOstud
FROMstudents,coursec,scoresc
WHERE=AND=ANDISNOTNULL
ORDERBY
SELECT*FROMstud
GO
21．编写一个程序，输出每个班最高分的课程名和分数。
解：
采用数据来源为SELECT查询结果的方法。
程序如下：
USEschool
GO
SELECTsclass,cname,MAX（degree）degree
FROM（SELECT,,,,
FROMstudents,coursec,scoresc
WHERE=AND=ANDdegreeISNOTNULL）T
GROUPBYsclass,cname
ORDERBYsclass
GO
执行结果如下：
22．编写一个程序，采用游标方式输出所有课程的平均分。
解：
程序如下：
USEschool
GO
--声明变量
DECLARE@c_namevarchar（8）,@s_avgfloat
--声明游标
DECLAREst_cursorCURSOR
FORSELECT,AVG
FROMcourse,score
WHERE=ANDISNOTNULL
GROUPBY
--打开游标
OPENst_cursor
--提取第一行数据
FETCHNEXTFROMst_cursorINTO@c_name,@s_avg
--打印表标题
PRINT'课程 平均分'
PRINT'-----------------'
WHILE@@FETCH_STATUS=0
BEGIN
--打印一行数据
PRINT@c_name+''+CAST（@s_avgASchar（10））
--提取下一行数据
FETCHNEXTFROMst_cursorINTO@c_name,@s_avg
END
--关闭游标
CLOSEst_cursor
--释放游标
DEALLOCATEst_cursor
GO
其执行结果如下：
23．编写一个程序，采用游标方式输出所有学号、课程号和成绩等级。
解：
程序如下：
USEschool
GO
--声明变量
DECLARE@no1char（5）,@no2char（6）,@fschar
（2）
--声明游标
DECLAREfs_cursorCURSOR
FORSELECTsno,cno,
CASE
WHENdegree>=90THEN'A'
WHENdegree>=80THEN'B'
WHENdegree>=70THEN'C'
WHENdegree>=60THEN'D'
WHENdegree<60THEN'E'
END
FROMscoreWHEREdegreeISNOTNULL
ORDERBYsno
--打开游标
OPENfs_cursor
--提取第一行数据
FETCHNEXTFROMfs_cursorINTO@no1,@no2,@fs
--打印表标题
PRINT'学号 课程号 等级'
PRINT'-----------------'
WHILE@@FETCH_STATUS=0
BEGIN
--打印一行数据
PRINT@no1+''+@no2+''+@fs
--提取下一行数据
FETCHNEXTFROMfs_cursorINTO@no1,@no2,@fs
END
--关闭游标
CLOSEfs_cursor
--释放游标
DEALLOCATEfs_cursor
GO
其执行结果如下：
24．编写一个程序，采用游标方式输出各班各课程的平均分。
解：
程序如下：
USEschool
GO
--声明变量
DECLARE@bhchar（5）,@kcchar（10）,@fsfloat
--声明游标
DECLAREfs_cursorCURSOR
FORSELECT,,AVG
FROMstudents,coursec,scoresc
WHERE=AND=ANDISNOTNULL
GROUPBY,
--打开游标
OPENfs_cursor
--提取第一行数据
FETCHNEXTFROMfs_cursorINTO@bh,@kc,@fs
--打印表标题
PRINT'班号 课程 平均分'
PRINT'---------------------------'
WHILE@@FETCH_STATUS=0
BEGIN
--打印一行数据
PRINT@bh+''+@kc+''+CAST（@fsASvarchar（10））
--提取下一行数据
FETCHNEXTFROMfs_cursorINTO@bh,@kc,@fs
END
--关闭游标
CLOSEfs_cursor
--释放游标
DEALLOCATEfs_cursor
GO
其执行结果如下：
上机实验题5参考答案
在上机实验题4建立的factory数据库上，完成如下各题（所有SELECT语句的查询结果以文本格式显示）。
（1）删除factory数据库上各个表之间建立的关系。
（2）显示各职工的工资记录和相应的工资小计。
（3）按性别和部门名的所有组合方式列出相应的平均工资。
（4）在worker表中使用以下语句插入一个职工记录：
INSERTINTOworkerVALUES（20,'陈立','女','55/03/08',1,'75/10/10',4）
在depart表中使用以下语句插入一个部门记录：
INSERTINTOdepartVALUES（5,'设备处'）
对worker和depart表进行全外连接显示职工的职工号、姓名和部门名，然后删除这两个插入的记录。
（5）显示最高工资的职工的职工号、姓名、部门名、工资发放日期和工资。
（6）显示最高工资的职工所在的部门名。
（7）显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。
（8）采用游标方式实现（6）小题的功能。
（9）采用游标方式实现（7）小题的功能。
（10）先显示worker表中的职工人数，开始一个事务，插入一个职工记录，再显示worker表中的职工人数，回滚该事务，最后显示worker表中的职工人数。
操作过程
（1）删除factory数据库上各个表之间建立的关系的操作步骤如下：
①启动SQLServer管理控制器。
②在“对象资源管理器”中展开LCB-PC服务器节点。
③展开“数据库”节点。
④选中school，将其展开。
⑤展开“数据库关系图”节点。
⑥选中，右击，在出现的快捷菜单中选择“修改”命令，如图所示。
⑦在数据库关系图中，选择表示要从关系图中删除的关系的连接线（对于两条连线均进行⑦～⑨的操作）。
⑧右击关系线，从快捷菜单中选择“从数据库中删除关系”命令。
⑨出现一个消息框，提示确认删除。
单击“是”按钮。
⑩在出现的对话框中单击“是”按钮保存所做的修改。
这样就将worker表和depart表以及worker表和salary表之间的关系删除了。
图factory数据库关系图
（2）对应的程序如下：
USEfactory
GO
SELECTworker.职工号,worker.姓名,salary.工资
FROMworker,salary
WHEREworker.职工号=salary.职工号
ORDERBYworker.职工号,worker.姓名
COMPUTESUM（salary.工资）BYworker.职工号
GO
执行结果如下：
职工号姓名工资
------------------------------------------
1孙华
1孙华
sum
----------------------
2408
职工号姓名工资
------------------------------------------
10陈涛
10陈涛
sum
----------------------
职工号姓名工资
------------------------------------------
11刘欣1255
11刘欣1250
sum
----------------------
2505
职工号姓名工资
------------------------------------------
12李涵1345
12李涵1350
sum
----------------------
2695
职工号姓名工资
------------------------------------------
13王小燕1205
13王小燕1200
sum
----------------------
2405
职工号姓名工资
------------------------------------------
14李艺
14李艺
sum
----------------------
职工号姓名工资
------------------------------------------
15魏君1105
15魏君1100
sum
----------------------
2205
职工号姓名工资
------------------------------------------
2孙天奇905
2孙天奇900
sum
----------------------
1805
职工号姓名工资
------------------------------------------
3陈明
3陈明
sum
----------------------
职工号姓名工资
------------------------------------------
4李华
4李华
sum
----------------------
3006
职工号姓名工资
------------------------------------------
5余慧730
5余慧725
sum
----------------------
1455
职工号姓名工资
------------------------------------------
6欧阳少兵1085
6欧阳少兵1085
sum
----------------------
2170
职工号姓名工资
------------------------------------------
7程西
7程西
sum
----------------------
职工号姓名工资
------------------------------------------
8张旗728
8张旗733
sum
----------------------
1461
职工号姓名工资
------------------------------------------
9刘夫文
9刘夫文
sum
----------------------
（3）对应的程序如下：
USEfactory
GO
SELECTworker.性别,depart.部门名,AVG（salary.工资）AS'平均工资'
FROMworker,depart,salary
WHEREworker.职工号=salary.职工号ANDworker.部门号=depart.部门号
GROUPBYworker.性别,depart.部门名WITHCUBE
GO
执行结果如下：
性别部门名平均工资
------------------------------------------
男财务部
男人事部
男市场部
男NULL
女财务部
女人事部
女市场部
女NULL
NULLNULL
NULL财务部
NULL人事部
NULL市场部
（4）对应的程序如下：
USEfactory
GO
INSERTINTOworkerVALUES（'20','陈立','女','55/03/08',1,'75/10/10',4）
GO
INSERTINTOdepartVALUES（5,'设备处'）
GO
SELECTworker.职工号,worker.姓名,depart.部门名
FROMworkerFULLJOINdepart
ON（worker.部门号=depart.部门号）
ORDERBYworker.职工号
GO
DELETEFROMworkerWHERE职工号='20'
GO
DELETEFROMdepartWHERE部门号=5
GO
执行结果如下：
职工号姓名部门名
------------------------------------
NULLNULL设备处
1孙华财务部
10陈涛
升级会员 