第-10章-SQL高级应用

第1章网站与网页概述1第10章SQL高级应用CHAPTER10第10章SQL高级应用练习题10参考答案1．数据检索时使用COMPUTE和COMPUTEBY产生的结果有何不同？答使用COMPUTE子句和COMPUTEBY子句都能既浏览明细数据，又看到统计的结果。只使用COMPUTE子句时，其查询的结果类似于总计；而使用COMPUTEBY子句时，其查询的结果将为带具体内容的分类进行统计。2．进行连接查询时应注意什么？答连接查询是指以指定表中的某个列或某些列作为连接条件，从两个或更多的表中查询关联数据的查询。进行连接查询时应注意以下几点：一般而言，基于主键和外键指定查询条件，连接条件可使用“主键=外键”。如果一个表有复合关键字，在连接表时，必须引用整个关键字。应尽可能限制连接语句中表的数目，连接的表越多，查询处理的时间越长。对于连接表的两个列应有相同或类似的数据类型。不要使用空值作为连接条件，因为空值计算不会和其他任何值相等。3．什么是交叉连接？答交叉连接是两个表的笛卡尔积，即两个表的记录进行交叉组合。4．内连接、外连接有什么区别？答内连接是从结果中删除与其他被连接表中没有匹配行的所有行，因此内连接可能会丢失信息。外连接会把内连接中删除原表中的一些行保留下来，保留哪些行由外连接的类型决定。5．外连接分为左外连接、右外连接和全外连接，它们有什么区别？答左外连接从结果中保留第一个表的所有行，但只包含第二个表中与第一个表匹配的行，第二个表相应的空行被放入NULL值。右外连接从结果中保留第二个表的所有行，但只包含第一个表中与第二个表匹配的行，第一个表相应的空行被放入NULL值。全外连接会把两个表所有行都显示在结果中，并尽可能多地匹配数据和连接条件。6．什么是事务？事务的特点是什么？答事务是指一个操作序列，这些操作序列要么都被执行，要么都不被执行，它是一个第1章网站与网页概述2第10章SQL高级应用不可分割的工作单元。事务中任何一个语句执行时出错，系统都会返回到事务开始前的状态。事务是并发控制的基本单元，是数据库维护数据一致性的单位。在每个事务结束时，都能保持数据一致性。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．什么是锁定？第1章网站与网页概述3第10章SQL高级应用答在SQLServer2005中，锁定就是给数据库对象加锁。使用锁定能确保事务完整性和数据库一致性。锁定可以防止用户读取正在由其他用户更改的数据，并可以防止多个用户同时更改相同数据。如果不使用锁定，则数据库中的数据可能在逻辑上不正确，并且对数据的查询可能会产生意想不到的结果。12．什么是死锁？答死锁是一种条件，不仅仅是在关系数据库管理系统(RDBMS)中发生，在任何多用户系统中都可以发生。当两个用户(或会话)具有不同对象的锁，并且每个用户需要另一个对象的锁时，就会出现死锁。每个用户都等待另一个用户释放他的锁。当两个连接陷入死锁时，SQLServer会进行检测，其中一个连接被选做死锁牺牲品，该连接的事务回滚，同时应用程序收到错误。13．简述游标的概念。答关系数据库中的操作会对整个行集产生影响。由SELECT语句返回的行集包括所有满足该语句WHERE子句中条件的行。由语句所返回的这一完整的行集被称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。游标通过以下方式扩展结果处理：允许定位在结果集的特定行。从结果集的当前位置检索一行或多行。支持对结果集中当前位置的行进行数据修改。为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。提供脚本、存储过程和触发器中使用的访问结果集中的数据的T-SQL语句。14．给出以下程序的执行结果。USEschoolSELECTsno,cno,degreeFROMscoreWHEREsnoIN(103,105)ORDERBYsnoCOMPUTEAVG(degree)BYsnoGO解：结果如下：15．给出以下程序的执行结果。USEschoolGOSELECTteacher.tnameAS'教师',student.sclassAS'班号',AVG(score.degree)AS'平均分'FROMstudent,course,score,teacherWHEREstudent.sno=score.snoANDcourse.cno=score.cnoANDcourse.tno=teacher.tno第1章网站与网页概述4第10章SQL高级应用GROUPBYteacher.tname,student.sclassWITHCUBEGO解：结果如下：16．给出以下程序的执行结果。USEschoolGOBEGINTRANSACTIONMytran--启动事务INSERTINTOteacherVALUES(999,'张英','男','1960/03/05','教授','计算机系')--插入一个教师记录SAVETRANSACTIONMytran--保存点INSERTINTOteacherVALUES(888,'胡丽','男','1982/8/04','副教授','电子工程系')--插入一个教师记录ROLLBACKTRANSACTIONMytranCOMMITTRANSACTIONGOSELECT*FROMteacher--查询teacher表的记录GODELETEteacherWHEREtno='999'--删除插入的记录GO解：结果如下：17．编写一个程序，查询最高分的课程名。解采用子查询方式。程序如下：USEschoolGOSELECTcnameFROMcourseWHEREcno=(SELECTcnoFROMscoreWHEREdegree=(SELECTMAX(degree)FROMscore))GO18．编写一个程序，查询95033班的最高分的学生的学号、姓名、班号、课程号和分数。解：程序如下：USEschoolGOSELECTs.sno,s.sname,s.sclass,sc.cno,sc.degreeFROMstudents,scorescWHEREs.sno=sc.snoANDs.sclass='95033'ANDsc.degree=(SELECTMAX(degree)FROMstudent,score第1章网站与网页概述5第10章SQL高级应用WHEREstudent.sno=score.snoANDstudent.sclass='95033')GO19．编写一个程序，查询平均分高于所有平均分的课程号。解：程序如下：USEschoolGOSELECTcno,AVG(degree)FROMscoreGROUPBYcnoHAVINGAVG(degree)(SELECTAVG(degree)FROMscore)GO20．编写一个程序，创建一个新表stud，包含所有学生的姓名、课程名和分数，并以姓名排序。解：程序如下：USEschoolGOSELECTs.sname,c.cname,sc.degreeINTOstudFROMstudents,coursec,scorescWHEREs.sno=sc.snoANDc.cno=sc.cnoANDsc.degreeISNOTNULLORDERBYs.snameSELECT*FROMstudGO21．编写一个程序，输出每个班最高分的课程名和分数。解：采用数据来源为SELECT查询结果的方法。程序如下：USEschoolGOSELECTsclass,cname,MAX(degree)degreeFROM(SELECTs.sno,s.sname,s.sclass,c.cname,sc.degreeFROMstudents,coursec,scorescWHEREs.sno=sc.snoANDc.cno=sc.cnoANDdegreeISNOTNULL)TGROUPBYsclass,cnameORDERBYsclassGO执行结果如下：22．编写一个程序，采用游标方式输出所有课程的平均分。解：程序如下：USEschoolGO--声明变量DECLARE@c_namevarchar(8),@s_avgfloat--声明游标DECLAREst_cursorCURSORFORSELECTcourse.cname,AVG(score.degree)FROMcourse,scoreWHEREcourse.cno=score.cnoANDscore.degreeISNOTNULLGROUPBYcourse.cname--打开游标OPENst_cursor--提取第一行数据第1章网站与网页概述6第10章SQL高级应用FETCHNEXTFROMst_cursorINTO@c_name,@s_avg--打印表标题PRINT'课程平均分'PRINT'-----------------'WHILE@@FETCH_STATUS=0BEGIN--打印一行数据PRINT@c_name+''+CAST(@s_avgASchar(10))--提取下一行数据FETCHNEXTFROMst_cursorINTO@c_name,@s_avgEND--关闭游标CLOSEst_cursor--释放游标DEALLOCATEst_cursorGO其执行结果如下：23．编写一个程序，采用游标方式输出所有学号、课程号和成绩等级。解：程序如下：USEschoolGO--声明变量DECL