SQL 操作练习题

SQL Server（三级）操作练习题1数据库运行监控1.1使用SQL语句实现DMV查询，查找当前挂起的I/O 请求，将结果保存在“c:\gatDoc\SQL3J2.1.1\2.1.1.doc”文档中。

（提示：查询数据库master中的系统视图sys.dm_io_pending_io_requests和系统函数sys.dm_io_virtual_file_stats）1.2以SA身份登录数据库，在Master数据库上执行SQL语句：Select * fromsysobjects 。

利用SQL Server Manangement Studio监控数据库服务器，观察每个登录用户在数据库执行的命令，请在“c:\gatDoc\SQL3J2.1.1\2.1.1.doc”文档内写出操作步骤，并在该文档内将下列操作界面截屏后保存：阅读进程信息，sa执行的命令。

（提示：启动活动监视器）1.3打开SQL Server Profiler，建立跟踪，对数据库活动进行监视，同时打开性能监视器，利用SQL Server: Memory对象监视数据库的一般活动，运行3分钟后，观察重合时段内SQL Serve的活动和上述性能计数器的值，请在“2.1.1.doc”文档内写出操作步骤，并在该文档内将下列三个操作界面截屏后依次保存：1）在SQL Server Profiler中新建跟踪mytrace。

2）在性能监视器中新建SQL Server: Memory计数器。

3）在SQL Server Profiler中查看指定性能计数器的情况。

2数据库备份计划2.1利用维护计划功能，设置每天1：00自动执行增量备份（差异备份）数据库model到文件夹C:\Backup，并将操作步骤写在c:\gatDoc\SQL3J2.2.1\2.2.1.doc文档里面，将下面两个操作界面截屏后依次保存在同一个文档里：1）设置备份数据库任务的界面2）维护计划设置完成的界面2.2利用SQL Server Agent功能（作业功能），创建作业job1，设置每天4：00完成以下步骤：先删除备份文件c:\gatDoc\SQL3J2.2.1\model.bak，然后对数据库model进行完全备份，保存为c:\gatDoc\SQL3J2.2.1\model.bak，请在2.2.1.doc文档里写出操作步骤，并将下面三个操作界面截屏后依次保存：1）设置删除备份文件的界面2）设置对数据库model进行完全备份的界面3）设置作业计划属性的界面3数据库编程* 准备：假设目前有一个数据库demo，里面有两个表Supplier、Production，结构如下：Supplier :ID int, --供应商编号Name varchar(25), --姓名hireDate datetime, --供应日期type varchar(10), --供应类型sal numeric(8,2), --补助MGR int, --所属类型编号productionID int, --商品编码Production :ID int, --商品编码Name varchar(25), --商品名price float, --商品价格number float, --商品种类练习时请同学自行创建以上数据库和数据表（考试时系统中已经创建好了）。

将下面操作的SQL脚本保存到c:\gatDoc\SQL3J3.1.1\3.1.1.doc文档中。

3.1建立存储过程add_supplier，输入供应商编号、姓名、-供应日期、供应类型、补助、所属类型编号，向表Supplier插入数据。

3.2建立函数valid_id，根据输入的供应商编号，检查该供应商供应的商品是否少于10种，如果少于，则返回1，否则返回0。

（标量函数）3.3建立函数get_sal，根据输入的商品名，返回该商品的平均价格。

（标量函数）3.4建立函数get_tab，根据输入的所属类型编号返回该类型的所有供应商信息。

（内联表值）3.5编写存储过程disp_ supplier，根据输入的所属类型编号，采用游标方式按照下列格式输出该所有该类型的供应商姓名、供应类型、供应日期。

供应商姓名供应类型供应日期-------------------------------------------------------------------（此处为数据）参考答案：1.1select database_id,file_id,io_stall,io_pending_ms_ticks,scheduler_addressfrom sys.dm_io_virtual_file_stats(null,null) t1,sys.dm_io_pending_io_requests t2where t1.file_handle=t2.io_handle* 分析：在sys.dm_io_pending_io_requests视图中，记录了挂起的I/O进程情况。

这个视图在master数据库中的系统视图中可以找到。

另外还用到一个系统函数sys.dm_io_virtual_file_stats()，在下面图中的位置可以找到。

（本图为操作参考，考试时不需要给出）1.2步骤：(考试时不需要写步骤)1．在SQL Server Manangement Studio打开新查询。

2．选择Master数据库，点击工具栏中的“新建查询”。

3．在查询中执行下面的语句：Seelct * from sysobjects4．在“管理 活动监视器”中，右键选择“查看进程”，弹出活动监视器窗口，有多个进程，其中有一个SA帐户在访问数据库实例，双击该进程后弹出一个进程的详细信息窗口。

(提示：使用Alt + PrtSc 组合键，可以复制当前活动窗口图象)1.3步骤：(考试时不需要写步骤)1． 在SQL Server Manangement Studio 的“工具”菜单中打开SQL Server Profiler 。

2．点击“新建跟踪”。

3．填写跟踪名称myTrace，选中“保存到文件”，指定文件夹C:\和文件名myTrace.trc，单击“运行”。

4．在SQL Server Profiler 的“工具”菜单中打开性能监视器。

5．展开“性能日志和警报”，在“计数器日志”上右键选择“新建日志设置”，填写日志名称myLog。

6．点击“添加计数器”，在“性能对象”中选择“SQL Server: Memory”，再从列表中选择一个计数器“pages/sec”(根据题目要求，如果没有要求，任意选一个)，然后点击“添加”，再点击“关闭”。

7．打开“日志文件”选项卡，在“日志文件类型”中选择“文本文件（用逗号分隔）”。

点击“配置”按钮，指定文件夹C:\和文件名myLog.csv，然后按“确定”返回。

8．在查询工具中任意使用SQL语句执行一些数据库操作。

9．3分钟后，分别停止SQL Server Profiler和性能监视器的所有运行的任务。

10．关闭SQL Server Profiler的当前跟踪，在“文件”菜单中打开所保存的跟踪文件C:\myTrace.trc，然后在“文件”菜单中选择“导入性能数据”，选择性能计数器保存的文件C:\myLog.csv，并在“性能计数器限制对话框”中选中相应的计数器（见后面的参考图）。

（本图为操作参考，考试时不需要给出，所有选项都打勾）2.1步骤：(考试时不需要写步骤)1．在SQL Server Manangement Studio的管理->维护计划中，右键点击“新建维护计划”，填写名称。

2．设置计划，选择“重复执行”，频率为每天一次，时间设定为3:00。

3．在左边任务列表中将“备份数据库”任务拖曳到右边窗口，然后双击他。

4．选择数据库“model”，备份类型选“完整”，文件夹选“C:\Backup”(直接打字，不要打开对话框进行选择)。

5．保存，退出。

2.2步骤：(考试时不需要写步骤)1．在SQL Server Manangement Studio的SQL Server代理->作业中，右键点击“新建作业”，填写名称。

2．点击“步骤”选项卡，新建一个步骤，命名为step1，类型选择“操作系统（cmdExec）”,命令栏目中填写：del c:\backup\model.bak，单击“确定”。

3．新建一个步骤，命名为step2，类型选择“Transact-SQL脚本（T-SQL）”,数据库选择“master”，命令栏目中填写：backup database model to disk='c:\backup\model.bak'，单击“确定”。

4．点击“计划”选项卡，新建一个计划，命名为plan1，选择“重复执行”，频率为每天一次，时间设定为5:00，单击“确定”。

5．单击“确定”，退出。

3.1 （提示：以下各题可以利用SQL Server Management Studio中的相关生成工具搭好框架）create procedure add_supplier@id int,@name varchar(25),@hiredate datetime,@type varchar(10),@sal numeric(8,2),@MGR floatasbegininsert into supplier values(@id,@name,@hiredate,@type,@sal,@MGR,null) end* 分析：本题使用存储过程模板（找到指定数据库> 可编程性> 存储过程，右键“新建存储过程”）。

由于没有给出productionID字段的值，所以要使用null补足。

3.2create function valid_id(@id int)returns intasbegindeclare @result intselect @result=count(*) from production where ID=(select productionID from supplier where id=@id)if @result<10set @result = 1elseset @result = 0return @resultendcreate function valid_id(@id int)returns intasbegindeclare @result intif (select count(*) from production where ID=(select productionID from supplier where id=@id))<10set @result = 1elseset @result = 0return @resultend* 分析：本题使用标量函数模板（找到指定数据库> 可编程性> 函数，右键“新建> 标量值函数”）。