数据库实验三 视图、存储过程、触发器等的建立与维护 实验报告

预习报告部分

一、实验目的

1．学会使用对象资源管理器建立视图，应用视图插入、删除、修改数据；

2．掌握存储过程的使用方法；

3．掌握触发器的使用方法。

二、实验内容

   此实验是综合视图、存储过程、触发器等知识应用的一个综合性实验。

实验要求：

1．利用所创建的数据库和数据表，综合应用视图、存储过程、触发器等知识完善数据库；

2．掌握应用更新视图数据可以修改基本表数据的方法；

3．熟练掌握添加、修改、删除记录的存储过程的定义及调用；

4．掌握通过触发器来实现数据的参照完整性。

实验报告部分

一、实验步骤：

1. 创建视图

利用员工管理数据库YGGL中 3个表：

Employees：员工自然信息表、Departments：部门信息表、Salary：员工薪水情况表。

利用YGGL各表建立视图实现各种连接查询。建立视图view1,查询所有职工的员工编号、姓名、部门名和收入，并按部门名顺序排列。建立视图view2，查询所有职工的员工编号、姓名和平均工资。建立视图view3，查询各部门名和该部门的所有职工平均工资。

①建立视图view1,查询所有职工的员工编号、姓名、部门名和收入，并按部门名顺序排列

CREATE VIEW view1

AS

   SELECT TOP 100 PERCENT dbo.Employees.EmployeeID,dbo.Employees.Name,dbo.Departments.DepartmentName,dbo.Salary.InCome

   FROM dbo.Employees,dbo.Departments,dbo.Salary

   WHERE dbo.Employees.DepartmentID = dbo.Departments.DepartmentID AND dbo.Employees.EmployeeID = dbo.Salary.EmployeeID

   ORDER BY Departments.DepartmentName

执行结果

                 

②建立视图view2，查询所有职工的员工编号、姓名和平均工资

CREATE VIEW dbo.view2(EmployeeID,name,Average)

AS

   SELECT dbo.Employees.EmployeeID, dbo.Employees.Name, AVG(InCome-OutCome)

   FROM dbo.Salary, dbo.Employees

   WHERE dbo.Employees.EmployeeID = dbo.Salary.EmployeeID

   GROUP BY dbo.Employees.EmployeeID,dbo.Employees.Name  

执行结果 

                     

③建立视图view3，查询各部门名和该部门的所有职工平均工资

CREATE VIEW dbo.view3(DepartmentName,Average)

AS

   SELECT dbo.Departments.DepartmentName,AVG(InCome-OutCome)

   FROM dbo.Departments,dbo.Employees,dbo.Salary

   WHERE dbo.Employees.DepartmentID = dbo.Departments.DepartmentID AND dbo.Employees.EmployeeID = dbo.Salary.EmployeeID

   GROUP BY dbo.Departments.DepartmentName

执行结果 

                    

1. 创建存储过程

①创建一个为Employees表添加员工记录的存储过程EmployeeAdd。

CREATE PROCEDURE EmployeeAdd

(@EmployeeID char(6),@Name char(10),@Birthday datetime,

@Sex bit,@Address char(20),@Zip char(6),@PhoneNumber char(12),

@EmailAddress char(30),@DepartmentID char(3)) 

AS

 BEGIN

    INSERT INTO Employees

VALUES(@EmployeeID,@Name,@Birthday,@Sex,@Address,@Zip,@PhoneNumber,@EmailAddress,@DepartmentID) 

END

执行结果

                          

②创建一个存储过程EmployeeDelete删除Employees表中指定员工编号的记录

 CREATE PROCEDURE EmployeeDelete(@employeeid char(6)) 

AS

 BEGIN

 DELETE FROM Employees

WHERE Employeeid=@employeeid

END

执行结果

                         

③创建修改职员记录的存储过程EmployeeUpdate

  CREATE  PROCEDURE EmployeeUpdate

  (@EmpID char(6),@EmployeeID char(6),@Name char(10),@Birthday datetime,

  @Sex bit,@Address char(20),@Zip char(6),@PhoneNumber char(12),

  @EmailAddress char(20),@DepartmentID char(3))

  AS

  BEGIN

    UPDATE Employees    

    SET EmployeeID=@EmployeeID,

    Name=@Name,

    Birthday=@Birthday,

    Sex=@Sex,

    Address=@Address,

    Zip=@Zip,

    PhoneNumber=@PhoneNumber,

    EmailAddress=@EmailAddress,

    DepartmentID=@DepartmentID

    WHERE EmployeeID=@EmpID

 END

执行结果

                          

1. 调用存储过程  

在查询分析器编辑窗口输入各存储过程的代码并执行以下程序。

1. 添加职员记录的存储过程EmployeeAdd：

职员记录：’990230’，’刘朝’,’890909’,1,’武汉小洪山5号’，”，”，”，’3’

EXEC EmployeeAdd  '990230','刘朝','1989-09-09',1,'武汉小洪山5号','210019','','',3

执行结果

  

1. 更新职员记录的存储过程EmployeeUpdate：

调用存储过程    

 EXEC EmployeeUpdate '990230','990232','刘平','1989-09-09' ,1,'武汉小洪山5号','','','',2

执行结果

1. 删除职员’990232’记录的存储过程EmployeeDelete

 EXEC EmployeeDelete '990232'

执行结果

4.创建触发器

对于YGGL数据库，表Employees的DepartmentID列与表Departments的DepartmentID列对应满足参照完整性规则，即：

1. 1. 向Employees表添加1条记录时，该记录的DepartmentID值在Departments表中应存在。
   2. 修改Departments表DepartmentID 字段值时，该字段在Employees表中的对应值也应修改。
   3. 删除Departments表中1条记录时，该记录DepartmentID字段值在Employees表中对应的记录也应删除。

对于上述参照完整性规则，在此通过触发器实现。

在查询分析器编辑窗口输入各触发器的代码并执行：

①向Employees表插入或修改1条记录时，通过触发器检查记录的DepartmentID值在Departments表是否存在，若不存在，则取消插入或修改操作。

CREATE TRIGGER Employees_Ins on dbo.Employees

FOR INSERT,UPDATE

AS

BEGIN

    IF((SELECT DepartmentID from inserted)NOT IN

    (SELECT DepartmentID FROM departments))

    ROLLBACK   

END

当修改部门号为8时的执行结果

  

②修改Departments表departmentID字段值时，该字段在Employees表中的对应值也进行相应修改。

    CREATE TRIGGER Departments_update on dbo.Departments

    FoR UPDATE

    AS

    BEGIN

       IF(COLUMNS_UPDATED()&01)>0

       UPDATE Employees

           SET DepartmentID=(SELECT ins.DepartmentID from INSERTED ins)

           WHERE DepartmentID=(SELECT DepartmentID FROM deleted)

    END   

执行结果

当要修改Departments表departmentID字段值时，该字段在Employees表中的对应值也进行相应修改，举例：修改市场部的部门号为8

                       

         

③删除Departments表中1条记录的同时删除该记录departmentlD字段值在Employees表中对应的记录。

    CREATE TRIGGER Departments_delete On dbo.Departments

    AFTER DELETE

    AS

    BEGIN

       DELETE FROM Employees

       WHERE DepartmentID=(SELECT DepartmentID FROM deleted)

    END

执行结果

当删除Departments表中第5条记录

                

     同时删除该记录departmentlD字段值’5’ 在Employees表中对应的记录。

    二、心得体会

       在SQL Server数据库实验的过程中，我也遇到的一些小问题，例如，知识点记得不够牢固，我会翻看课本教材，去寻找答案；又或者是在百度上寻找相应的博客学习、参考；和同学讨论也是解决问题的方法之一。最后，不断加深我对于数据库这门课程的理解，更加深入的了解这门课程，在以后对于学习计算机编程所具有的重要意义，SQL Server数据库的应用范围非常广泛，学好这门课程就显得尤为重要。

       总而言之，这次的实验让我受益匪浅，让我懂得了自主学习，和向他人学习的好处，加深了我对于SQL Server数据库的应用，能够更加熟练的掌握SQL Server数据库的基本语法和使用方法。