实验三 视图

一、实验内容与步骤 内容：

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

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

（1） 利用YGGL各表建立视图实现各种连接查询。建立视图view1,查询所有职

工的员工编号、姓名、部门名和收入，并按部门名顺序排列。建立视图view2，查询所有职工的员工编号、姓名和平均工资。建立视图view3，查询各部门名和该部门的所有职工平均工资。 （2） 编写对YGGL各表进行插入、修改、删除操作的存储过程，然后编写程序，

调用这些存储过程。创建一个为Employees表添加员工记录的存储过程addEmployees。创建一个存储过程delEmployees删除Employees表中指定员工编号的记录。

（3） 对于YGGL数据库，请用触发器实现两个表间的参照完整性。在表

Departments上创建一个触发器Departments _update，当更改部门编号时同步更改Employees表中对应的部门编号。在表Employees上创建一个触发器Employees _delete，当删除员工记录时同步删除salary表中对应的工资收入记录。 步骤:

1、建立视图

（1）建立视图view1,查询所有职工的员工编号、姓名、部门名和收入，并按部门名顺序排列。 USE YGGL GO

CREATE VIEW View1 AS

SELECT TOP(100) Employees.EmployeeID,Employees.Nname, Departments.DepartmentName,Salary.InCome FROM Employees,Departments,Salary

WHERE Employees.DepartmentID=Departments.DepartmentID AND Employees.EmployeeID=Salary.EmployeeID ORDER BY Departments.DepartmentID GO

1

（2）建立视图view2，查询所有职工的员工编号、姓名和平均工资。 USE YGGL GO

CREATE VIEW View2 AS

SELECT EmployeeID,AVG(InCome)AS'平均收入' FROM Salary

Group BY EmployeeID

（3）建立视图view3，查询各部门名和该部门的所有职工平均工资。 CREATE VIEW View3 AS

SELECT Departments.DepartmentName,AVG(InCome)AS'平均收入' FROM Salary,Departments,Employees

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

GROUP BY Salary.EmployeeID,Departments.DepartmentName

2．创建存储过程

(1) 添加职员记录的存储过程EmployeeAdd： USE YGGL GO

CREATE PROCEDURE EmploreeAdd

(@employeeid char6)，@name char(10)，@birthday datetime，

@sex bit，@address char(20)，@zip char(6)，@phonenumber char(12)， @emailaddress char(20)，@departmenflD char(3)) AS BEGIN

INSERT INTO Employees

VALUES(@employeeid，@name，@birthday，@sex，@address，

@zip，@phonenumber,@emailaddress，@departmentlD)

2

END RETURN GO

(2)修改职员记录的存储过程EmployeeUpdate： USE YGGL GO

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, Nname=@name,

Birthday=@birthday, Sex=@sex,

Address=@address, Zip=@zip,

PhoneNumber=@phonenumber, EmailAddress=@emailaddress, DepartmentID=@departmentID WHERE EmployeeID=@empid END RETURN GO

(3)删除职员记录的存储过程EmployeeDelete： USE YGGL GO

CREATE PROCEDURE EmployeeDelete (@employeeid char(6)) AS

BEGIN

DELETE FROM Employees

WHERE EmployeeID=@employeeid END RETURN

3

3．调用存储过程

USE YGGL EXEC EmployeeAdd’990230’，’刘朝’,’0909’,1,’武汉小洪山5号’，”，”，”，’3’ GO

USE YGGL EXEC Employeeupdate’990230’，’990232’,’刘平’，’0909’ ,1,’武汉小洪山5号’，”，”，”，’2’ GO

USE YGGI，

EXEC EmployeeDelete’990232’ GO

【思考与练习】

编写如下T-SQL程序：

4

(1) 自定义1个数据类型，用于描述YGGL数据库中的DepartmentlD字段，然后编写代码重新定义数据库各表。 USE YGGL

EXEC sp_addtype'ID_type', 'char(6)','not null' GO

USE YGGL

IF EXISTS(SELECT name FROM sysobjects

WHERE type='U'and name='Employees') DROP table Employees

/*首先在系统表中查看EmployeeS表是否存在，若存在，删除该表*/ CREATE TABLE Employees (EmployeeID ID_type, Name CHAR(10) NOT NULL, Birthday DATETIME NOT NULL, Sex bit NOT NULL,

Address char(20) NULL, Zip char(6) NULL,

PhoneNumber char(12) NULL, EmailAddress char(20) NULL,

DepartmentID char(3) NOT NULL /*定义字段DepartmentID的类型为ID_type*/ ) GO

4、创建触发器

（1）向Employees表插入或修改1条记录时，通过触发器检查记录的

DepartmentID值在Departments表是否存在，若不存在，则取消插入或修改操作。

5

USE YGGL GO

CREATE TRIGGER EmployeesIns on dbo.Employees FOR INSRET,UPDATE AS BEGIN

IF((SELECT ins.departmentid from inserted ins)NOT IN (SELECT departmentid FROM departments))

ROLLBACK／*对当前事务回滚，即恢复到插入前的状态 END

(2)修改Departments表departmentID字段值时，该字段在Employees表中的

对应值也进行相应修改。 USE YGGL GO

CREATE TRIGGER DepartmentsUpdate on dbo．Departments FoR UPDATE AS BEGIN

IF(COLUMNS_UPDATED()&01)>0 UPDATE Employees

SET DepartmentlD=(SELECT ins.DepartmentlD from INSERTED ins) WHERE DepaxtmentlD=(SELECT DepartmentlD FROM deleted) END GO

(3)删除Departments表中1条记录的同时删除该记录departmentlD字段值

在Employees表中对应的记录。 USE YGGL GO

CREATE TRIGGER DepartmentsDelete On db.Departments FOR DELETE AS BEGIN

DELETE FROM Employees

WHERE DepartmentlD=(SELECT DepartmentlD FROM deleted) END

GO

思考与练习

6

（1）自定义1个数据类型，用于描述YGGL数据库中的DepartmentlD字段，然后编写代码重新定义数据库各表。 （2）对于YGGL数据库，表Employees的EmployeelD列与表Salary的EmployeelD列应满足参照完整性规则，请用触发器实现两个表间的参照完整性。

USE YGGL GO

CREATE TRIGGER SalaryIns on dbo.Salary FOR INSERT,UPDATE AS BEGIN

IF((SELECT EmployeeID from inserted ) NOT IN

(SELECT EmployeeID FROM Employees)) ROLLBACK

/*对当前事务回滚，即恢复到插入前的状态*/ END

USE YGGL GO

CREATE TRIGGER EmployeesUpdate on Employees FoR UPDATE AS BEGIN

IF(UPDATE(EmployeeID)) UPDATE Salary

SET EmployeeID=(SELECT EmployeeID FROM inserted) WHERE EmployeeID=(SELECT EmployeeID FROM deleted) END GO

USE YGGL GO

CREATE TRIGGER EmployeesDelete On Employees FOR DELETE AS BEGIN

DELETE FROM Salary

WHERE EmployeeID=(SELECT EmployeeID FROM deleted) END GO

7

二、实验问题

1、ORDER BY 必须要与TOP一起用。

2、每创建视图、存储过程和触发器或调用数据库时，都要对YGGL表进行刷新以得到最新的数据库数据。

3、在编写语句的时候要时刻记得个变量名与库中的命名一致。 三、实验心得

在这次的实验过程中我学会了使用企业管理器建立视图，应用视图插入、删除、修改数据，还掌握存储过程的使用方法与触发器的使用方法。通过实际的操作，我对所学的SQL 的内容有了更深刻的理解，从理论到实际应用这正是我们学习的最终目的。

8