《SQL SERVER 数据库应用》实验指导书-2015版 下载本文

内容发布更新时间 : 2024/12/23 14:47:21星期一 下面是文章的全部内容请认真阅读。

GO

【思考与练习】

查询财务部雇员的最高和最低实际收入。 (4) 求财务部雇员的总人数。

在查询分析器的窗口输入如下的语句并执行: USE YGGL

SELECT COUNT(EmployeelD) FROM Employees

WHERE DepartmentlD= (SELECT DepartmentlD

FROM Departments

WHERE DepartmentName=’财务部’) GO

【思考与练习】统计财务部收入在2500元以上雇员的人数。

5. GROUP BY,ORDER BY子句的使用 (1)求各部门的雇员数。

在查询分析器的窗口输入如卜^的语句并执行: USE YGGL

SELECT COUNT(EmployeelD) FROM Employees

GROUP BY DepartmentID Go

【思考与练习】统计各部门收入在2000元以上雇员的人数。 (2)将各雇员的情况按收入由低到高排列。 在查询分析器的窗口输入如下的语句并执行: USE YGGL

SELECT Employees.*,Salary.* FROM Employees,Salary

WHERE Employees.EmployeeID=Salary.EmployeeID ORDER BY InCome G0

【思考与练习】将各雇员的情况按出生时间先后排列。

13

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

一、实验目的

1.学会使用企业管理器建立视图,应用视图插入、删除、修改数据; 2.掌握存储过程的使用方法; 3.掌握触发器的使用方法。

二、实验内容

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

实验要求:

1.利用所创建的数据库和数据表,综合应用视图、存储过程、触发器等知识完善数据库; 2.掌握应用更新视图数据可以修改基本表数据的方法; 3.熟练掌握添加、修改、删除记录的存储过程的定义及调用; 4.掌握通过触发器来实现数据的参照完整性。 实验内容要求:

利用员工管理数据库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)班级表(U_CLASSES ):ID含义为\班号\含义为\班名\含义为所在 系,各字段类型按需要设置是否允许为空,ID字段被设置为主键。 (2)成绩表(U_SCORES ):STUDENT_ID含义为学号,COURSE_ID含义为课程号,SCORE为成绩,各字段类型按需要设置是否允许为空,STUDENT_ID 、COURSE_ID字段被设置为主键。 (3)课程表(U_COURSES):COURSE含义为课程名称, ID含义为课程编号,CREDIT含义为课程学分。

(4)学生表(U_STUDENTS),表结构如下:

序号 字段 1 ID 类型(长度) 主键 含义 char(10) 是 学号 14

2 3 NAME varchar(16) 姓名 所在班级编号 CLASS_ID tinyint 1. 用企业管理器建立一个基于学生表、课程表、成绩表的视图,要求该视图显示学号、

姓名、课程、成绩。

1) 启动企业管理器、注册、连接

2) 展开服务器、数据库、在视图上右击,在快捷菜单中执行\新建视图(V)...\3) 在新视图窗口内的关系图窗格内右击鼠标,弹出的菜单即为视图设计菜单,执行\

添加表(B)...\

4) 再在添加表对话框中选择U_SCORES表,再单击添加按钮。

5) 依此操作,分别添加U_STUDENTS、U_COURSES表,单击关闭按钮。

6) 再在关系窗格内,拖动U_STUDENTS表的\至U_SCORES的STUDENT_ID,拖动

U_COURSES表的\至U_SCORES的COURSE_ID,再分别选中U_STUDENTS表的\列(列前的复选框),U_COURSES表的\列以及U_SCORES表的\列,然后单击\按钮,显示视图结果。

7) 单击保存按钮,将视图保存为V_SCORES,单击确定。

2. 用查询分析器建立一个基于学生表、班级表的学生视图(V_STUDENTS),包括学号、姓

名、班级、系,SQL语句如下: 1) CREATE VIEW dbo.V_STUDENTS

AS

SELECT dbo.U_STUDENTS.ID,dbo.U_STUDENTS.NAME, dbo.U_CLASSES.CLASS, dbo.U_CLASSES.DEPARTMENT

FROM dbo.U_STUDENTS INNER JOIN

dbo.U_CLASSES ON dbo.U_STUDENTS.CLASS_ID = dbo.U_CLASSES.ID

3. 自己写一个SQL语句建立一个基于课程表的视图(V_COURSES),要求显示课程编号、

课程名、学分。 2.创建存储过程

在查询分析器编辑窗口输入各存储过程的代码并执行以下程序。 (1) 添加职员记录的存储过程EmployeeAdd: USE YGGL GO

CREATE PROCEDURE Emplo)reeAdd

(@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) END RETURN GO

(2) 修改职员记录的存储过程EmployeeUpdate:

15

USE YGGL GO

CREATE PROCEDURE EmployeeUpdate

(@empid char(6),@employeeid char(6),@name char(10),@birthday datetime, @sex bit,@address char(20),@zip chat(6),@phonenumber char(12), @emailaddress char(20),@departmentlD char(3)) AS BEGIN

UPDATE Employees ‘

SET Employeeid=@employeeid, Name=@name.

Birthday=@birthday, Sex=@sex,

Address=@address. Zip=@zip,

Phonenumber=-@phonenumber, Emailaddree=@emailaddress. DepartmentD=@departmenflD 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 G0

3.调用存储过程 USE YGGL

EXEC EmployeeAdd’990230’,’刘朝’,’890909’,1,’武汉小洪山5号’,”,”,”,’3’ GO

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

USE YGGI,

EXEC EmployeeDelete’990232’ GO

16