SQL_Server_2016数据库系统实验指导书

实验十二使用视图

一．实验目的

1.熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；

2.熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更

新的区别与联系

3.学习灵活熟练的进行视图的操作，认识视图的作用

二．实验准备

1．熟悉SQL SERVER 工作环境。

2．能连接到EDUC数据库。

3．复习有关视图操作的SQL语言命令。

三．实验要求

1.在实验开始之前做好准备工作。

2.实验之后提交实验报告，思考视图和基本表的区别

四．实验内容

一．定义视图

在EDUC数据库中，以Student、Course 和Sc 表为基础完成以下视图定义

１．定义计算机系学生基本情况视图V_Computer；

create view V_Computer as select*from student

where sdept='计算机学院’

２．将Student、 Course 和SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G

create view V_S_C_G as select s.sno,sname,https://www.wendangku.net/doc/b015739513.html,o,cname,score from student s,course c,sc where s.sno=sc.sno and https://www.wendangku.net/doc/b015739513.html,o=https://www.wendangku.net/doc/b015739513.html,o ３．将各系学生人数，平均年龄定义为视图V_NUM_AVG

create view V_NUM_AVG as select sdept,count(*),avg(Sage)as Avgage from student group by sdept

４．定义一个反映学生出生年份的视图V_YEAR

create view V_YEAR as select year(getdate())-sage from student ５．将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G

create view V_AVG_S_G as select sno,count(*)as cnum avg(score) as avgscore from sc group by sno

６．将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G

create view V_AVG_C_G as select cno,count(*)as snum,avg(score)as avgscore from sc group by cno

二．使用视图

1.查询以上所建的视图结果。

2.查询平均成绩为90分以上的学生学号、姓名和成绩；

select s.sno,sname,avgscore from V_AVG_S_G v,student s where s.sno=v.sno and avgscore>90

3.查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩；

select SC.*,Student.sname from SC join Student on

SC.SNO=Student.SNO

where not exists(

select a.SNO,b.SNO from SC a left join

(

select a.*from SC a join V_AVG_C_G b

on https://www.wendangku.net/doc/b015739513.html,O=https://www.wendangku.net/doc/b015739513.html,O and a.score>=b.avgscore

)b on a.SNO=b.SNO and https://www.wendangku.net/doc/b015739513.html,O=https://www.wendangku.net/doc/b015739513.html,O where

SC.SNO=a.SNO and b.SNO is null)

4.按系统计各系平均成绩在80分以上的人数，结果降序排列；

select sdept,count(*)as cnum,avg(score)as score from sc join student s on sc.sno=s.sno group by sdept

having avg(score)>80 order by score desc

三．修改视图

create view V_IS as select*from student

where sdept='IS'

1.通过视图V_计算机系，分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,”

S4_MMM”并查询结果;

update V_计算机系set sno='S1_MMM'where sno='S1'

update V_计算机系set sno='S4_MMM'where sno='S4'

2.通过视图V_IS，新增加一个学生记录 ('S12','YAN XI',19,'IS')，并查询结果

insert into V_IS系(Sno,Sname,Sage,Sdept)values('S12','YAN XI',19,'IS')

3.通过视图V_IS，新增加一个学生记录 ('S13','YAN XI',19,'MA')，并查询结果

4.通过视图V_IS，删除学号为“S12”和“S3”的学生信息，并查询结果

delete V_IS where sno='S12'or sno='s3'

5.要通过视图V_S_C_GRADE，将学号为“S12”的姓名改为“S12_MMM”，是否可以实

现？并说明原因

可以update V_S_C_G set sname='S12_MMM'where sno='S12'

6.要通过视，图V_AVG_S_G，将学号为“S1”的平均成绩改为90分，是否可以实现？

并说明原因

不可以

实验十三触发器、存储过程操作

一、实验目的

1.掌握 SQL Server中的触发器的使用方法；

2.掌握存储过程的操作方法。

二、实验准备

1、了解触发器

2、了解存储过程

三、实验要求

1. 在实验之前做好准备

2. 试验之后提交实验报告，并验收实验结果

四、实验内容

1.在班级表class中增加总人数（stotal）和班长两个字段；

2.建立不带参数的存储过程p1（选择所有年龄<21岁的男同学）；

CREATE PROCEDURE p1

AS SELECT * FROM student where sage<21 and ssex='男'

3.建立带有参数的存储过程p2（根据用户传递的参数只显示与指定学号的学生同龄的所有同

学）；

CREATE PROCEDURE p2

@xh char(8)

AS select*from student where sage=(select sage from student where sno=rtrim(@xh))and sno<>rtrim(@xh)

4.在SSMS中使用Transact-SQL分别调用p1、p2，并将结果显示出来；

EXEC p1

EXEC p2 '20090501'

5.创建INSERT触发器instrg1：在学生表中每新增一名学生，将在班级表class表的总人数自

动增加1。

CREATE TRIGGER instrg

ON student

AFTER INSERT

AS

BEGIN

update class

set stotal=stotal+1

where ClsNo in(select clsno from inserted)

END

6.创建UPDATE触发器update1:当某学生所在班号发生变化时（即调到另一班级后），

将其原先所在班级总人数减1，将新调入的班级表class表的总人数增加1。

create trigger update1

on student

for update

as

if update(clsNo)

begin

update class set stotal=stotal+1 where clsNo=(select clsno from inserted)

update class set stotal=stotal-1where clsNo=(select clsno from deleted) end

实验十三触发器、存储过程操作

●实验指导

一．利用SSMS创建和执行存储过程的存储过程

1.在SSMS中，选择指定的服务器和数据库，单击“可编程性”，在弹出的快捷菜单中选择“新建存储过程”选项，如图13-1所示；出现创建存储过程对话框，如图13-2所示。

图13-1

（２）在编辑框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“保存”按钮，即可保存该存储过程。

（3）在查询编辑器里执行存储过程

T_Sql语句为：Exec p1

图13-2

2.利用SSMS创建和执行触发器的过程如下：

（1）在SSMS中，展开指定的服务器和数据库项，展开要在其上创建触发器的表所在的数据库，展开要在其上创建触发器的表，则会出现触发器菜单项，如图13-3所示，用右键单击“触发器”，在弹出的快捷菜单中选择“新建存储过程”选项，如图13-4所示；

在文本框中输入触发器文本。单击“检查语法”按钮，则可以检查语法是否正确。最后，单击“执行”按钮，即可关闭该对话框，成功创建触发器。

图13-3 选择管理触发器对话框

（２）在文本框中可以输入创建除法器的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“执行”按钮，即可保存该触发器。

实验十四 SQL Server 2005 的安全性、备份与恢复

一、实验目的

1.掌握 SQL Server中的触发器的使用方法；

2.掌握存储过程的操作方法。

二、实验准备

了解数据库的安全性、备份与恢复

三、实验要求

1. 在实验之前做好准备

2.利用SSMS可视化设置数据库用户及操作权限；进行数据库的备份和恢复；

3.利用Transact-SQL语句进行备份和恢复操作。

4. 试验之后提交实验报告，并验收实验结果

四、实验内容

3.实验内容

①管理和设计SQL Server登录信息，实现服务器级的安全控制。

②设计和实现数据库级的安全保护机制。

③设计和实现数据库对象级的安全保护机制

①使用SSMS规划和执行备份操作

②使用Transact-SQL语句执行备份操作

backup database educ to DISK='h:\database\educ2.bak'

⑥使用SSMS执行恢复操作

⑦使用Transact-SQL语句执行恢复操作

RESTORE DATABASE tt FROM DISK='h:\database\educ.bak'with MOVE'educ'TO'D:\tt.mdf',

MOVE'educ_log'TO'd:\tt_log.ldf'

⑧创建维护数据库的备份设备

实验十四 SQL Server 2005 的安全性、备份与恢复

实验指导

一．在SSMS中为所属的SQL 服务器设置为Windows身份验证模式、SQL Server和Windows 混合安全认证模式。其步骤如下：

1．在SSMS中为所属的SQL 服务器设置为SQL Server和Windows 混合安全认证模式。其步骤如下：

1）在SSMS窗口中展开服务器组，用鼠标右击需要设置的SQL服务器，在弹出的菜单中选择“属性”项，在出现的对话框中选择“安全性”，则出现如图14-1所示的对话

框。

图14-1 SQL Server属性对话框

2）服务器身份验证选择“SQL Server和Windows”单选项。

2．在 SSMS中为自己建立一个服务器用户、数据库用户和数据库角色。

1）在SSMS窗口中展开服务器组，展开服务器，用鼠标单击“安全性”文件夹右侧的‘+’，用鼠标右击“登录名”，在弹出的菜单中选择“新建登录名”项，则出现新建登录

对话框，如图14-2所示。

图14-2 新建登录对话框

2）在新建登录对话框中有常规，服务器角色、用户映射、安全对象和状态五个选项卡。

在常规选项卡中，输入用户名，选择SQL Server安全验证，输入用户密码。

在服务器角色选项卡中，需要确定用户所属的服务器角色，在本例中采用缺省值即可。

在用户映射选项卡中，需要指定此登录可以访问的数据库（本例选中EDUC）、数据库角色（在本例中采用缺省值即可）。

单击“确定”按钮，既完成了创建登录用户的工作。

3．新建数据库引擎查询，如图14-3所示。在连接到数据库引擎窗口（图14-4）选择SQL Server安全验证，用户名为humin，输入用户口令，连接到SQL Server。在“查询”

窗口中可以输入SQL语句。如“SELECT * FROM student”。运行后，得到消息“拒绝了对对象‘student’（数据库‘EDUC’，所有者‘dbo’）的SELECT权限。可见用户humin没有对学生表的SELECT权限。

图14-3

图14-4

4．要想将EDUC数据库的操作权限赋予数据库用户humin，有两种方法。

方法一：通过SSMS

1）在SSMS中展开服务器组，展开服务器，用鼠标单击“数据库”文件夹左侧的‘+’，用鼠标单击EDUC数据库文件夹左侧的‘+’，用鼠标单击安全性文件夹左侧的‘+’2）用鼠标右击“用户”。选择“humin”项，用鼠标右击，在弹出的菜单中选择“属性”

项，则出现数据库用户属性对话框，如图14-5所示。

图14-5 数据库用户属性对话框

3）如图14-6所示，安全对象对话框下面是有关数据库用户和角色所对应的权限表。这些权限均以复选框的形式表示。复选框有三种状态：“√”为授权；“×”为废除权；空为撤权。在表中可以对用户或角色的各种对象操作权（SELECT，INSERT，UPDATE，DELETE，EXEC和DRI）进行授予或撤消。

图14-6

二．在SSMS中创建一个备份设备

1）在对象资源管理器中展开服务器对象节点

2）右击该“备份设备”，在弹出的菜单中选择“新建备份设备”项，则出现“备份设备属性”对话框，如图图14-6所示。在名称文本框中输入设备名（本例为BK_EDUC）,在“目标”选项组中的“文件”文本框中添加新设备的路径和文件名“d:\EDUC\Backup\BK_EDUC.bak”

图14-6

图14-7 备份设备对话框

3）在备份设备文件夹下面出现该设备。选中该设备，用鼠标右键单击，在弹出的菜单中选择“属性”项，则出现“备份设备”对话框。

三、SSMS中备份EDUC数据库

在SSMS中扩展服务器，在数据库文件夹中选中要备份的数据库文件夹（EDUC数据库）。单击右键，在弹出的菜单中选择“备份”，出现图14-8所示的对话框，点击“添加”按钮，出现

“选择备份目标对话框”，如图14-9所示。选择备份设备后，在图14-8中点击“确定”按钮

即可完成备份工作。

图14-8

图14-9

四、SSMS中恢复学生选课数据库

1）打开EDUC数据库，删除其中一个表（如SC表），即当前的学生选课数据库EDUC中没有选课表。

2）恢复EDUC

步骤如下：在对象资源管理器中扩展服务器，在数据库文件夹中选中要备份的数据库文件夹（EDUC数据库）。用鼠标右键单击，在弹出的菜单中选择“任务”项，在随之出现的级联菜单中选择“还原”-“数据库”项，则出现还原数据库对话框。在此例中按“确定”

按钮即可完成恢复工作。

3）再打开学生选课数据库，看一看当前的学生选课数据库中有没有选课表。

五、数据库定期备份

修改EDUC数据库备份计划，要求每月1号进行数据库备份，重复步骤2。

SQLServer2005的定期备份是通过创建“维护计划”来实现的。主要有两种方式：1、维护计划向导。

2、新建维护计划（用户手工创建）。

在SQLServer2005中使用维护计划来设计维护备份数据库任务时，有一个前提条件：“SQL Server 代理”服务必须是启用状态首先介绍使用维护计划向导来执行数据库的定期备份。

操作过程：1、打开SQL Server Management Studio”，如图14-10所示。

图14-10

2、右键单击：SQL Server 代理→启动（注：要执行维护计划，SQL Server 代理必须启动！）

右键单击：管理→维护计划→维护计划向导，单击“下一步”（图14-11）

图14-11

4、在“选择目标服务器”窗口，依次设置“名称”、“服务器”、“身份验证”等信息，单击“下一步”（图14-12），

图14-12

5、在“选择维护任务”窗口，选择准备执行的任务，单击“下一步”（图11-13）

图14-13

6、在“选择维护任务顺序”窗口，可以调整每项任务的执行顺序，单击“下一步”（图14-14）

图14-14

7、在“定义备份数据库（完整）任务”窗口，勾选需要备份的数据库名称，单击下一步（图14-15）

图14-15

图14-16

8、在“选择计划属性”窗口，单击“更改”按钮打开“新建作业计划窗口”，设置好各项参数后单击“确定”按钮（图14-17）

图14-17

9、再次回到“选择计划属性”窗口，单击下一步（图14-18）

图14-18

10、在“选择报告选项”窗口，设置报告的保存或分发方式，单击下一步（图14-19）

图14-20

11、显示本次维护计划的详细信息，单击完成（图14-21）

图14-21

图14-22

至此，SQL Server 2005采用维护计划即可以达到定期备份数据库的目的。