数据库实验4-实验报告.doc

实验四 存储过程、触发器与索引 一、实验目的 1. 熟悉大型数据库实验环境，以MS SQL SERVER为例。 2. 掌握视图。 3. 掌握存储过程与触发器。 4. 掌握MS SQL SERVER的导入和导出。 5. 掌握MS SQL SERVER的索引。 二、实验内容 （1）使用"实验一"中的数据库"abc"，创建一个视图，生产厂家为"北京"且价格低于 北京生产的产品的平均价格，输出产品的名称、价格和生产厂家。 （2）使用"实验一"中的数据库"abc"，创建一个带有输入参数的存储过程proc_abc, 查询指定职工的销售记录，用户输入职工编号，存储过程返回职工名称、产品名称、 销售日期、销售数量，假如执行存储过程时所提供的"职工编号"不存在，存储过程应 给予一定的提示。 （3）使用"实验一"中的数据库"abc"，练习使用游标， 写出按如下报表形式显示结果的SQL语句，该报表查询每年每种产品总销售金额，（ 总销售金额＝价格*销量），报表显示格式如下所示： 年 产品号 产品名 销售总量 总销售金额（万元） 2001年 2 AAA 590 3.2 2001年 5 BBB 644 23.3 2002年 1 CCC 32 0.2 （4）使用"实验一"中的数据库"abc"，练习使用触发器，在销售表上创建触发器tr_ updateprice，每次新增销售记录时，自动更新产品表的单价，更新方法是：每增加 一笔销售记录，就将该产品的单价减去1块钱。 （5）将100万行网络连接监控数据Netflow导入数据库，创建多个索引，观察创建索 引对数据库文件大小的影响；并设计不同的查询语句来观察索引对查询效率的影响； 可以尝试将100万行记录扩展为1000万行，然后再做索引和查询的实验？文件见附件 。 三、实验结果和代码 use abc go create view abc as select CPM,JG,SCCJ from CPB where SCCJ like '@北京@' and JG<(select AVG(JG) from CPB where SCCJ like '@北京@' ); create procedure proc_abc @zgh_yh nchar(6) as if exists(select zgh from xsryb where zgh=@zgh_yh) select xsryb.xm,cpb.cpm,xsqkb.xsrq,xsqkb.xssl from xsryb,cpb,xsqkb where xsqkb.cph=cpb.cph and xsqkb.zgh=xsryb.zgh and xsryb.zgh=@zgh_yh print '存在' if not exists(select zgh from xsryb where zgh=@zgh_yh) print '提供的职工号不存在' exec proc_abc @zgh_yh='G11'; declare abc_cursor cursor for select year(xsrq) ,cpb.cph,cpb.cpm ,xsqkb.xssl ,cpb.jg*xsqkb.xssl from cpb,xsqkb where xsqkb.cph=cpb.cph open abc_cursor declare @year_abc int,@cph_abc nchar(6),@cpm_abc nvarchar(20),@xszl_abc int,@xsze_abc int fetch next from abc_cursor into @year_abc, @cph_abc,@cpm_abc ,@xszl_abc,@xsze_abc while @@FETCH_STATUS = 0 begin if(@year_abc in(select 年 from nxsb)and @cph_abc in (select 产品号 from nxsb where 年=@year_abc)) begin update nxsb set 销售总量=销售总量+@xszl_abc where 年=@year_abc and 产品号=@cph_abc update nxsb set 总销售金额=总销售金额+@xsze_abc where 年=@year_abc and 产品号=@cph_abc end if (@year_abc not in (select 年 from nxsb) ) begin insert into nxsb values(@year_abc,@cph_abc,@cpm_abc,@xszl_abc,@xsze_abc) end if(@year_abc in(select 年 实验报告涉及了数据库管理的关键概念，包括视图、存储过程、触发器以及索引的使用。以下是这些知识点的详细说明： 1. **视图（View）**：视图是数据库中一个虚拟表，其内容由SQL查询定义。在实验中，创建了一个名为`abc`的视图，该视图展示的是所有生产厂家为"北京"且价格低于北京平均价格的产品的名称、价格和生产厂家。视图简化了数据查询，并提供了安全性，因为可以限制用户只看到特定的列或满足特定条件的数据。 2. **存储过程（Stored Procedure）**：存储过程是一组预编译的SQL语句，可以在数据库中预先定义并保存。实验中创建了名为`proc_abc`的存储过程，它接受一个职工编号作为输入参数，返回该职工的销售记录。如果输入的职工编号不存在，存储过程会给出提示。存储过程提高了性能，减少了网络通信，并允许进行复杂的业务逻辑处理。 3. **触发器（Trigger）**：触发器是一种特殊类型的存储过程，会在特定数据库操作（如INSERT、UPDATE或DELETE）发生时自动执行。实验中创建了名为`tr_updateprice`的触发器，每当在销售表上新增销售记录时，它会自动更新对应产品的单价，每次减少1元。触发器用于实现数据完整性、审计跟踪和业务规则。 4. **索引（Index）**：索引是数据库管理系统中用于加速数据检索的数据结构。实验中，通过导入100万条网络连接监控数据并创建多个索引，研究了索引对数据库文件大小和查询效率的影响。索引可以显著提高查询速度，但也会占用额外的存储空间，并可能在插入、删除和更新操作时降低性能。 5. **数据导入与导出（Import/Export）**：实验提到了使用MS SQL SERVER的导入和导出功能。这是数据迁移或备份的重要步骤，可以通过工具如SQL Server Management Studio来实现，方便地将数据从一个源移动到另一个源，或者备份和恢复数据库。 6. **游标（Cursor）**：游标允许程序逐行处理结果集。在实验中，使用游标`abc_cursor`遍历销售记录，计算每年每种产品的总销售金额和销售总量，然后更新或插入到报表表`nxsb`中。游标在处理单个记录或执行复杂迭代操作时很有用，但在大规模数据处理中，通常尽量避免使用，因为它们可能影响性能。 7. **查询优化**：实验通过对比不同索引和查询语句的效率，展示了索引在提高查询性能上的作用。良好的索引策略可以显著提升数据库的查询速度，特别是在处理大量数据时。同时，索引过多或不合理的索引也可能导致写操作变慢，因此需要根据实际需求和查询模式来设计索引。 这个实验涵盖了数据库管理的关键方面，通过实践加深了对数据库操作的理解，包括数据的查询、处理和优化，这对于任何IT专业人士来说都是重要的技能。