exerciseAnswerRef1.docx

1、exerciseAnswerRef1SQL Server（三级）操作练习题1下列内容中的“提示”、“分析”在考试中不提供。练习或考试中都要严格按照题目指定的名称、类型等书写，没有指定的自行命名。1 数据库运行监控1.1 使用SQL语句实现DMV查询，显示当前缓存占用了大部分CPU时间的前20个批处理或过程，按照各批处理或过程的CPU执行时间降序排列，将结果保存在“c:gatDocSQL3J2.1.12.1.1.doc”文档中。（提示：查询数据库master中的系统视图 sys.dm_exec_query_stats）1.2 以SA身份登录数据库，在Master数据库上执行SQL语句： Sele

2、ct * from sysobjects 。利用SQL Server Manangement Studio监控数据库服务器，观察当前按照进程分类的锁有哪些，请在“c:gatDocSQL3J2.1.12.1.1.doc”文档内写出操作步骤，并在该文档内将下列操作界面截屏后保存：阅读锁的信息，登录帐户信息。（提示：启动活动监视器）1.3 打开SQL Server Profiler，建立跟踪，对数据库活动进行监视，同时打开性能监视器，利用SQL Server: SQL Statistics对象监视数据库服务器每秒的编译次数，运行3分钟后，观察重合时段内SQL Serve的活动和上述性能计数器的值，请

3、在“2.1.1.doc”文档内写出操作步骤，并在该文档内将下列三个操作界面截屏后依次保存：1） 在SQL Server Profiler中新建跟踪mytrace。2） 在性能监视器中新建SQL Server: SQL Statistics计数器。3） 在SQL Server Profiler中查看指定性能计数器的情况。2 数据库备份计划2.1 利用维护计划功能，设置每天3：00自动执行增量备份（差异备份）数据库model到文件夹C:Backup，并将操作步骤写在c:gatDocSQL3J2.2.12.2.1.doc文档里面，将下面两个操作界面截屏后依次保存在同一个文档里：1）设置备份数据库任务

4、的界面2）维护计划设置完成的界面2.2 利用SQL Server Agent功能（作业功能），创建作业job1，设置每天5：00完成以下步骤：先删除备份文件c:gatDocSQL3J2.2.1model.bak，然后对数据库model进行完全备份，保存为c:gatDocSQL3J2.2.1model.bak，请在2.2.1.doc文档里写出操作步骤，并将下面三个操作界面截屏后依次保存：1） 设置删除备份文件的界面2） 设置对数据库model进行完全备份的界面3） 设置作业计划属性的界面3 数据库编程* 准备：假设目前有一个数据库demo，里面有一个表Student，结构如下：ID int, -

5、学号Name varchar(20), -姓名enterDate datetime, -入学日期job varchar(10), -职位high numeric(8,2), -身高teacherID int, -班主任代号classID int, -班级编号练习时请同学自行创建以上数据库和数据表（考试时系统中已经创建好了）。将下面操作的SQL脚本保存到c:gatDocSQL3J3.1.13.1.1.doc文档中。3.1 建立存储过程add_stu，输入学号、姓名、入学日期、职位、身高、班主任代号、班级编号，向表student插入数据。3.2 建立函数valid_id，根据输入的学号，检查学生是

6、否存在，如果存在，则返回1，否则返回0。（标量函数）3.3 建立函数get_high，根据输入的学号，返回姓名和身高，其中要求先调用函数valid_id确定该学生是否存在，不存在则显示消息“该学生不存在”。（多语句表值）3.4 建立函数get_tab，根据输入的班级编号返回该班级所有学生信息。（内联表值）3.5 编写存储过程disp_stu，根据输入的班级编号，采用游标方式按照下列格式输出该班级所有学生的姓名、职位、身高。姓名 职位 身高 -参考答案：1.1 select top 20 sql_handle,sum(total_worker_time) as cpu_time from sys

7、.dm_exec_query_statsgroup by sql_handleorder by cpu_time desc* 分析：在sys.dm_exec_query_stats视图中，total_worker_time字段用来保存某个进程占用的cup时间，sql_handle表示某个进程编号。这个视图在master数据库中的系统视图中可以找到。（本图为操作参考，考试时不需要给出）1.2 步骤：(考试时不需要写步骤)1 在SQL Server Manangement Studio打开新查询。2 选择Master数据库，点击工具栏中的“新建查询”。3 在查询中执行下面的语句：Seelct *

8、from sysobjects4 在“管理 活动监视器”中，右键选择“查看进程”，弹出活动监视器窗口，有多个进程，其中有一个帐户在访问数据库实例，双击该进程后弹出一个进程的详细信息窗口。(提示：使用Alt + PrtSc组合键，可以复制当前活动窗口图象)1.3 步骤：(考试时不需要写步骤)1 在SQL Server Manangement Studio的“工具”菜单中打开SQL Server Profiler。2 点击“新建跟踪”。3 填写跟踪名称myTrace，选中“保存到文件”，指定文件夹C:和文件名myTrace.trc，单击“运行”。4 在SQL Server Profiler 的“工

9、具”菜单中打开性能监视器。5 展开“性能日志和警报”，在“计数器日志”上右键选择“新建日志设置”，填写日志名称myLog。6 点击“添加计数器”，在 “性能对象”中选择“SQL Server: SQL Statistics”，再从列表中选择一个计数器“SQL Compilations/sec”，然后点击“添加”，再点击“关闭”。7 打开“日志文件”选项卡，在“日志文件类型”中选择“文本文件（用逗号分隔）”。点击“配置”按钮，指定文件夹C:和文件名myLog.csv，然后按“确定”返回。8 在查询工具中任意使用SQL语句执行一些数据库操作。9 3分钟后，分别停止SQL Server Profil

10、er和性能监视器的所有运行的任务。10 关闭SQL Server Profiler的当前跟踪，在“文件”菜单中打开所保存的跟踪文件C:myTrace.trc，然后在“文件”菜单中选择“导入性能数据”，选择性能计数器保存的文件C:myLog.csv，并在“性能计数器限制对话框”中选中相应的计数器（见后面的参考图）。（本图为操作参考，考试时不需要给出，所有选项都打勾）2.1 步骤：(考试时不需要写步骤)1 在SQL Server Manangement Studio的管理-维护计划中，右键点击“新建维护计划”，填写名称。2 设置计划，选择“重复执行”，频率为每天一次，时间设定为3:00。3 在左边

11、任务列表中将“备份数据库”任务拖曳到右边窗口，然后双击他。4 选择数据库“model”，备份类型选“完整”，文件夹选“C:Backup”(直接打字，不要打开对话框进行选择)。5 保存，退出。2.2 步骤：(考试时不需要写步骤)1 在SQL Server Manangement Studio的SQL Server代理-作业中，右键点击“新建作业”，填写名称。2 点击“步骤”选项卡，新建一个步骤，命名为step1，类型选择“操作系统（cmdExec）”,命令栏目中填写：del c:backupmodel.bak，单击“确定”。3 新建一个步骤，命名为step2，类型选择“Transact-SQL脚

12、本（T-SQL）”,数据库选择“master”，命令栏目中填写：backup database model to disk=c:backupmodel.bak，单击“确定”。4 点击“计划”选项卡，新建一个计划，命名为plan1，选择“重复执行”，频率为每天一次，时间设定为5:00，单击“确定”。5 单击“确定”，退出。3.1 （提示：以下各题可以利用SQL Server Management Studio中的相关生成工具搭好框架）create procedure add_stuid int,name varchar(20),enterdate datetime,job varchar(20)

13、,high numeric(8,2),tID int,cID intasbegin insert into student values(id,name,enterdate,job,high,tID,cID)end* 分析：本题使用存储过程模板（找到指定数据库 可编程性 存储过程，右键“新建存储过程”）。3.2 create function valid_id(id int)returns intasbegin declare result int if exists(select * from student where id=id) set result = 1 else set resu

14、lt = 0 return resultend* 分析：本题使用标量函数模板（找到指定数据库 可编程性 函数，右键“新建 标量值函数”）。3.3 create function get_high(id int)returns tab table(name varchar(20),high numeric(8,2),msg varchar(20)asbegin if dbo.valid_id(id) = 1 insert into tab(name,high) select name,high from student where id=id else insert into tab(msg)

15、values(该学生不存在) returnend* 分析：本题使用多语句表值函数模板（找到指定数据库 可编程性 函数，右键“新建 多语句表值函数”）。3.4 create function get_table(classID int)returns tableasreturn (select * from student where classID=classID)* 分析：本题使用内联表值函数模板（找到指定数据库 可编程性 函数，右键“新建 内联表值函数”）。3.5 create procedure disp_stu classID intasbegin declare name varch

16、ar(20),job varchar(20),high numeric(8,2) declare c_rs cursor for select name,job,high from student where classID=classID open c_rs print 姓名 职位 身高 print - fetch next from c_rs into name,job,high while fetch_status = 0 begin print name + + job + + cast(high as varchar) fetch next from c_rs into name,job,high end close c_rs deallocate c_rsend* 分析：本题使用存储过程模板（找到指定数据库 可编程性 存储过程，右键“新建存储过程”）。