数据库实验指导书2009

Experiment Instruction Book

Of Database System

数据库系统原理实验指导书

段华斌

计算机与信息科学系

2009．8

第1部分 课程简介

一、本实验课的性质、任务与教学目标

《数据库原理》课程不仅要求学生掌握数据库技术的基本理论，更重要的是要培养学生的数据库技术实际应用能力，实验课的安排有利于帮助学生更好地掌握数据库技术的知识，培养学生利用数据库技术解决实际管理问题的能力。

本实验课的教学目的是使学生在正确理解数据库系统原理的基础上，熟练掌握主流数据库管理系统（SQL Server 2000）的应用技术进行数据库应用系统的设计与开发。 教学目标：通过上机操纵SQL语句，熟练掌握和深入理解SQL SERVER 2000环境下的基本使用知识。熟悉 SQL SERVER 2000的环境；熟练掌握SQL中数据库及表的定义功能；熟练掌握数据库表的操纵功能；理解视图、索引的创建和使用；了解存储过程和触发器的使用；能运用SQL SERVER 2000的完成后台数据库的设计。

二、本实验课的基本理论

该实验课是在理论思想指导下为达到某项目标而进行的实验，实验的语言是程序、实验的成功与失败必须用程序设计语句及实验得到的界面来说明。本课程的基本理论包括：SQL定义功能，熟练掌握SQL操纵功能，了解SQL数据控制功能，关系数据库的规范化理论，数据库设计的过程及方法等。

三、实验基本要求

通过本课程的学习，学生应达到下列基本要求：

1．了解数据库的基本概念，掌握数据库设计基本知识和技术。 2．熟悉SQL Server安装和配置。

3．熟练使用企业管理器、查询分析器创建、使用和管理数据库和数据库对象。 4．熟练掌握Transact-SQL语言的使用，并能进行编程。 5．掌握设计开发数据库系统后台数据库的基本过程和方法。 6．了解本课程的专业素质要求。 7．熟练掌握课程中涉及到的专业词汇。

四、实验项目的设置与内容提要 序号 实验项目名称 学时 每组人数 实验类型 实验要求 熟悉SQL SERVER 2000企业管理器、查询分熟悉 SQL SERVER 1 2000的环境及数据库的管理 2 1 验证 必修 析器的基本使用方法，掌握在企业管理器和使用T-SQL语句创建数据库。 了解SQL SERVER的基本数据类型及空值的2 表的创建和管理 2 1 综合 必修 概念，掌握在企业管理器中和使用T-SQL语句进行表的创建和管理 3 数据查询 4 1 综合 必修 重点掌握SELECT语句的使用方法。掌握子查询（嵌套查询）、连接查询的使用。 掌握在企业管理器中和使用T-SQL语句对表4 数据更新 4 1 验证 必修 进行插入、修改和删除数据的操作。 实验内容 1

了解视图和数据表之间的主要区别,掌握在5 视图的创建和使用 4 1 综合 必修 企业管理器中和使用T-SQL语句创建视图、查看视图修改数据表的方法。 了解索引的概念、优点及分类；掌握在企业6 索引的创建和使用 2 1 综合 必修 管理器中和使用T-SQL语句创建、修改和删除索引的操作。 了解存储过程的概念；了解使用存储过程的7 存储过程的创建和使用 4 1 综合 必修 特点及用途；掌握创建存储过程的方法；掌握执行存储过程的方法；了解查看、修改和删除存储过程的方法 了解触发器和一般存储过程的主要区别；了8 触发器的创建和使用 2 1 综合 必修 解使用触发器的优点；掌握创建触发器的方法；掌握查看触发器信息的方法；了解删除触发器的方法 9 数据库的安全性 2 1 验证 选修 掌握SQL Server 2000中用户管理、权限管理和角色管理。 了解游标的基本概念，掌握游标的声明、打开、读取、关闭、删除。 理解导入导出数据方法的概述，掌握DTS导入导出方法。 配置SQL Server代理服务。了解操作员管理、作业管理和警报管理。 10 游标管理 2 1 综合 选修 11 数据的导入导出 2 1 验证 选修 12 代理服务 2 1 综合 选修 五、实验环境介绍

本课程选用SQL Server 2000系统作为实验环境。SQL Server是使用客户机/服务器（C/S）体系结构的关系型数据库管理系统（RDBMS）。 （一）安装SQL Server 2000的环境需求

一、硬件和操作系统要求

下表说明安装 Microsoft SQL Server 2000 或 SQL Server 客户端管理工具和库的硬件要求。 硬件 计算机 内存 (RAM) 最低要求 Pentium 166 MHz 或更高。 至少 64 MB，建议 128 MB 或更多。根据笔者的经验，内存容量可以和数据容量保持1:1的比例，这样可以更好的发挥其效能。 需要约500MB的程序空间，以及预留500M的数据空间 硬盘空间 2

显示器 需要设置成800x600模式，才能使用其图形分析工具 下表说明为使用 Microsoft SQL Server 2000 各种版本或组件而必须安装的操作系统。 SQL Server 版本或组件 操作系统要求 企业版 Microsoft Windows NT Server 4.0、Microsoft Windows NT Server 4.0 企业版、Windows 2000 Server、Windows 2000 Advanced Server 和 Windows 2000 Data Center Server。(所有版本均需要安装IE5.0以上版本浏览器) 标准版 Microsoft Windows NT Server 4.0、Windows 2000 Server、Microsoft Windows NT Server 企业版、Windows 2000 Advanced Server 和 Windows 2000 Data Center Server。 个人版 Microsoft Windows Me、Windows 98、Windows NT Workstation 4.0、Windows 2000 Professional、Microsoft Windows NT Server 4.0、Windows 2000 Server 和所有更高级的 Windows 操作系统。 注：SQL Server 2000 的某些功能要求在Microsoft Windows 2000 Server以上的版本才能运行。因此大家安装Windows Server 2000(建议为Advanced版本)，可以学习和使用到SQL Server 2000的更多功能，以及享受更好的性能。

二、详细的安装过程

本文将在Windows 2000 Advanced Server操作系统作为示例，详细介绍安装SQL Server 2000企业版的过程。大家清首先安装Windows 2000 Advanced Server。如果手头上没有SQL Server 2000企业版，可以到微软公司的这个网址下载试用版： http://www.microsoft.com/china/sql/downloads/default.asp

或者到软件公司购买产品。

将企业版安装光盘插入光驱后，出现以下提示框。请选择 \安装 SQL Server 2000 组件\，出现下一个页面后，选择 \安装数据库服务器\。

图1

3

图2

选择 \下一步\，然后选择 \本地计算机\进行安装。

图3

图4

在 \安装选择\窗口，选择 \创建新的SQL Server实例...\。对于初次安装的用户，应选用这一安装模式，不需要使用 \高级选项\进行安装。 \高级选项\中的内容均可在安装完成后进行调整。

4

实 验 四

实验名称：数据更新 实验学时：2 实验目的：

1） 掌握在企业管理器中对表进行插入、修改和删除数据的操作。 2） 掌握使用T-SQL语句对表进行插入、修改和删除数据的操作。 3） 重点掌握带查询的更新方法。 实验内容及步骤：

（一） 在企业管理器中对数据库中的表进行插入、修改和删除数据 例1：在企业管理器中向数据库gzgl中的表输入数据

在企业管理器中向jbxx表插入记录，选择并用鼠标右击表jbxx →选择“返回所有行” →逐字段输入各记录值，输入完后关闭窗口。

例2：在企业管理器中将表jbxx中编号为020805的记录的部门号改为003

在企业管理器中选择表并用鼠标右击表jbxx →选择“返回所有行” →将光标定位至employee_id为020805的记录的department_id字段，改为003。

例3：在企业管理器中删除数据库gzgl表数据 在企业管理器中删除表jbxx的第3、6行操作步骤：在企业管理器中选择表并用鼠标右击表jbxx →选择“返回所有行” →选择要删除的行 →单击鼠标右键 →删除 →关闭表窗口。

（二） 使用T-SQL语句对表进行插入、修改和删除数据的操作 a. 使用INSERT语句插入数据 语法：INSERT [ INTO]

{ table_name | view_name

}

{[（ column_list ）]

{ VALUES

（ { DEFAULT | NULL | expression } [ ,...n] ）

| derived_table }

例1：向数据库gzgl中的表输入数据 use gzgl go

insert into jbxx

values(‘0111112’,’李子林’,’1973_5_3’,’1’,’310107196206088243’,’交通路5号’,’sql@126.com’)

go

单击快捷工具栏的执行图标或按F5，执行上述语句。

b. 使用UPDATE语句更新数据 语法：UPDATE

{ table_name | view_name

20

}

[ FROM { < table_source > } [ ,...n ] SET

column_name = { expression | DEFAULT | NULL }[ ,...n ] [ WHERE search_condition > ]

例2：将表jbxx中编号为020805的记录的部门号改为003 use gzgl go

update jbxx

set department_id=’003’ where employee_id=’020805’ go

例3：将表jbxx中的年龄增加1岁。 use gzgl go

update jbxx

set age=age+1

go

例4：将表jbxx中所有记录的党员属性改为“党员” use gzgl go

update jbxx

set polity=’ 党员’ &&如果没有where子句，则将修改表中的每一行数据

单击快捷工具栏的执行图标或按F5，执行上述语句。 c. 使用DELETE语句删除数据

语法：DELETE [ FROM ]

{ table_name WITH ( < table_hint_limited > [ ...n ] ) | view_name }

[ WHERE

< search_condition >

]

例4：删除jbxx表中编号为020805的记录。use gzgl

go

delete from jbxx

where employee_id=’020805’ 例5：删除数据库gzgl中jbxx表的所有数据

use gzgl

go

delete from jbxx

或者使用TRUNCATE TABLE name

use gzgl go

21

truncate table jbxx

实验习题：

对于“Student”数据库的三个基本表：

S(Sno,Sname,Sage,Ssex,Sdept),C(Cno,Cname,Teacher),SC(Sno,Cno,Grade) 完成如下更新：

(1) 往C表中插入一个课程元祖（’c8’,’VC++’,’BAO’）

(2) 查询平均成绩大于80分的课程名称，并把查询到结果送到另一个已存在的表tem_table(cname)。

(3) 在sc中删除尚无成绩的选课元组。

(4) 把选修LIU老师课程的女生选课元组全部删去。 (5) 把MATHS课不及格的成绩全改为60分。 (6) 把低于所有课程总平均成绩的女生成绩提高5%。

(7) 在表SC中修改C4课程的成绩，若成绩小于等于70分时提高5%，若成绩大于70

分时提高4%。 (8) 在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。 实验报告要求: ? ? ? ?

实验目的

实验习题的源程序清单

写出在实验过程中遇到的问题及解决方法 要求字迹端正、条理清晰、概念正确

22

实 验 五

实验名称：视图的创建和使用 实验学时：4

实验目的：

1） 了解视图和数据表之间的主要区别 2） 掌握在企业管理器中创建视图的方法。 3） 掌握使用T-SQL语句创建视图的方法。 4） 掌握查看视图修改数据表的方法。 实验内容及步骤：

（一） 在企业管理器中创建视图

①打开企业管理器窗口，打开新建视图对话框。

方法一：在企业管理器左边的“树”选项卡中选择指定的SQL SERVER组，展开指定的服务器，打开要创建视图的数据库文件夹，选中指定的数据库，右击该数据库图标，从弹出的快捷菜单中依次选择“新建”|“视图”选项，打开新建视图对话框。

方法二：在数据库文件夹中，用鼠标右击下一层的“视图”选项，在弹出的快捷菜单中选择“新建视图”选项。

②在新建视图对话框中，右击窗口上部的空白部分，从弹出的快捷菜单中选择“添加表”选项，或者单击工具栏中的按钮，出现“添加表”对话框，在该框中可以选择需要添加的基本表，单击“添加”按钮，就可以添加进去；也可以某个表名来添加表。使用同样的方法可以切换到“视图”或“函数”选项卡，从中选择需要的视图或函数，并依次创建新的视图。

③这里利用Ctrl键和鼠标配合，同时选前面建立的3个表S,SC,C，并单击“添加”按钮，即可将这3个表添加到创建视图对话框中。然后通过单击字段左边的复选框选择需要的字段，这里选择s表中sno,sname，C表中cno，SC表中sno,cno，属性设置如下：

△选中“输出”复选框，可以在输出结果中显示该字段。

△在“准则”复选框中输入限制条件，可以限制输出的记录。在定义视图的查询语句中该相知条件对应WHERE子句。

④右击字段定义对话框，从弹出的快捷菜单中选择“属性”选项，出现视图属性对话框。该对话框中，“distinct值”可以选择不输出重复的记录，“加密浏览”可以实现对视图定义加密，选中“顶端”复选框可以限制视图最多输出的记录条数。

⑤要运行并输出该视图结果，可以在视图设计窗口中单击工具栏中“！”按钮，或右击窗口空白区，在弹出的快捷菜单中选择“运行”选项，则可根据设置的查询语句，在本窗口最下面的数据结果区显示出生成的视图内容。

⑥可以单击工具栏中的“保存”按钮，或者在窗口上部显示数据表的窗格内单击鼠标右键，从弹出的快捷菜单中选择“保存”选项保存视图。 （二） 使用T-SQL语句创建视图

语法：CREATE VIEW

[ < database_name > .] [ < owner > .] view_name [ （ column [ ,...n ] ） ] [ WITH < view_attribute > [ ,...n ] ] AS

select_statement [ WITH CHECK OPTION ]

< view_attribute > ::=

{ ENCRYPTION | SCHEMABINDING |

23

VIEW_METADATA }

例１：建立显示年龄大于20岁的学生学号、姓名、性别等信息的V_S create view V_S as

select sno,sname,sex from s where age>20

例２：创建v_score1，要求基本表来源：S,C,SC；选择的字段为：S表中的sno、sname；C表中的cname及SC表中score；要求查询的数据为学号为20030001的学生的考试成绩。 Use s

create view v_grade As

Select s.sno,s.sname,c.cname,sc.grade From s,c,sc

Where s.sno=sc.sno and c.cno=sc.cno and sno=“20030001”

在查询分析器中执行上面的程序，会生成视图v_score1。为了查看视图中的数据，在查询分析器中输入语句：select * from v_score1。 （三） 使用企业管理器查看视图信息

在企业管理器左边的“树”选项卡中选择指定的SQL SERVER组，展开指定的服务器，打开要查看视图的数据库文件夹，选择数据库文件夹下的“视图”目录，在右边窗格中回列出当前数据库中的所有视图。 ① ② ③

若要查看视图的基本信息，右键单击要查看的视图，在弹出的快捷菜单中选择“属性”选项，打开视图属性对话框；

若要查看视图的相关性信息，右键单击要查看的视图，在弹出的快捷菜单中依次选择“所有任务”│“显示相关性”选项，打开视图相关性对话框。

若要查看视图的输出数据，可以在企业管理器中，右键单击要查看的视图，在弹出的快捷菜单中依次选择“打开视图”│“返回所有行”，在企业管理器中就会显示该视

图的输出数据。

（四） 使用T-SQL语句查看视图信息

c. sp_help 数据库对象名称 报告有关数据库对象（sysobjects 表中列出的任何对象）、用户定义数据类型或 Microsoft? SQL Server? 所提供的数据类型的信息

d. sp_helptext 视图（触发器、存储过程）

显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。

e. sp_depends 数据库对象名称

显示有关数据库对象相关性的信息（例如，依赖表或视图的视图和过程，以及视图或过程所依赖的表和视图）

（五） 使用企业管理器删除视图

在企业管理器打开要查看视图的数据库文件夹，选择数据库文件夹下的“视图”目录，，右键单击要删除的视图，在快捷菜单中选择“删除”命令，打开“出去对象”对话框 （六） 使用T-SQL语句删除视图

DROP VIEW {view_name} [,?n] 删除视图时，将从sysobjects、syscolumns、syscomments、sysdepends和sysprotects系统表中删除视图的定义及其他有关视图的信息。还将删除视图的所有权限。已删除的表上的任何视图必须通过使用DROP VIEW显示删除 （七） 使用视图

24

1. 使用视图插入表数据

例：通过视图v_s向学生表插入一行数据 Insert into v_s

Values (‘20050009’,’李华’，19，‘男’) 2. 使用视图修改表数据

例：update v_s

Set sex=’女’

Where sno=’20040001’

3. 使用视图删除表数据 例：delete v_s

Where sno=’20040001’

实验习题：

对于SQL Server 2000中的pubs数据库，完成如下操作：

(1) 创建视图view1,该视图中包含居住在加利福尼亚州（state字段值为“CA”）的作

者及图书信息，即包含pubs数据库的titles表、authors表中的作者姓、名、书

名、电话和地址（视图中的列名全部使用中文）。 (2) 显示第1题创建的仕途view1的所有数据。

(3) 列出第1题视图中所有作者姓或作者名以‘M’字母开头的所有信息。

(4) 修改第1题创建的视图,使其只包含所有作者的姓、名和数名三列（列名全部用中文）。

(5) 删除以上创建的视图view1。 实验报告要求: ? ? ? ? ?

实验目的 实验内容及步骤 实验习题的源程序清单

写出在实验过程中遇到的问题及解决方法 要求字迹端正、条理清晰、概念正确

25

实 验 六

实验名称：索引的创建和使用 实验学时：2

实验目的：

1） 了解索引的概念、优点及分类

2） 掌握在企业管理器中创建、修改和删除索引的操作。 3） 掌握使用T-SQL语句创建、修改和删除索引的操作。 实验内容及步骤：

(一) 索引的概念：

数据库中的索引是一个列表，在这个列表中包含了某个表中一列或者若干列值的集合，以及这些值的记录在数据表中的存储位置的物理地址。

索引的优点：

? ? ? ?

可以大大加快数据检索速度。

通过创建唯一索引，可以保证数据记录的唯一性。 在使用ORDER BY和GROUP BY子句进行检索数据时，可以显著减少查询中分组和排序的时间。

使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。

? 可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。 索引的分类：

1．聚集索引和非聚集索引 2．复合索引

3．唯一索引 (二) 在企业管理器中创建索引

在企业管理器中，展开指定的服务器和数据库，右击要创建索引的表，从弹出的快捷菜单中依次选择“所有任务|管理索引”选项。在出现的管理索引对话框中，可以选择要处理的数据库和表，然后单击“新建”按钮，出现“新建索引”对话框。在“索引名称”文本框中输入新建索引的名称，在下面的复选框中选择用于创建索引的字段。可以设定索引的属性，例如是否聚集、是否唯一，还可以建立复合索引，指定填充度属性，在选中字段后，可设置“排序次序”属性。最后单击“确定”按钮，即可生成新的索引。 (三) 使用T-SQL语句创建索引

语法：CREATE [UNIQUE] [CLUSTERED│NONCLUSTERED]INDEX index_name ON {table│view} (column [ASC│DESC] [,?n])

例1：为表jbxx创建一个非聚集索引，索引字段为employee_name，索引名为i_employeename create index i_employeename on jbxx(employee_name)

例2：新建一个表，名称为temp，为此表创建一个惟一聚集索引，索引字段为temp_number，索引名为i_temp_number。 use student

Create table t_temp (temp_number int, temp_name char(10), temp_age int)

create unique clustered index i_temp_number

on t_temp(temp_number)

例3：为表s创建一个复合索引，使用sex和birthday字段。

26

Use student

Create index i_s on s(sex,birthday) (四) 使用企业管理器查看、修改和删除索引的操作

在企业管理器中，展开指定的服务器和数据库，右击要创建索引的表，从弹出的快捷菜单中依次选择“所有任务|管理索引”选项，在出现的管理索引对话框中，选择要查看或修改的索引，单击“编辑”按钮，出现“编辑现有索引”对话框。在该对话框中，可以修改索引的大部分设置，还可以直接修改其SQL脚本，只需单击“编辑SQL”按钮，即可出现“编辑Transact_SQl脚本”对话框，在此可以编辑、分析、执行索引的Transact_SQl脚本。

要在企业管理器中修改索引的名称，需要在表的“属性”对话框中进行。在企业管理器中，右击要修改名称的表，从弹出的快捷菜单中选择“设计表”选项，在打开的设计表的窗口中，打开表的“属性”对话框，选择“索引/键”选项卡，在此对话框中，先选定要修改索引名称的索引，然后直接在“索引名”文本框中输入心得索引名称替换原来的索引名称。 要删除索引，可以在“管理索引”对话框中或表的“属性”对话框中，选择要删除的索引，单击“删除”按钮，即可删除索引。

(五) 使用T_SQL查看、修改和删除索引的操作

使用系统存储过程查看索引信息，语法如下： sp_helpindex [@objname=] ‘name’ 例1：查看jbxx表的索引信息 sp_helpindex jbxx

使用系统存储过程修改索引名称，语法如下:

sp_rename[@objname=] ‘object_name’,[@newname=] ‘new_name’ [,[@objtype=] ‘object_type’]

例2：将s表中的索引i_s的名称改为i_s_sexandbirth use student

sp_rename ‘s.i_s’,’i_s_ sexandbirth’,’index’

删除索引句法：

drop index ‘table.index│view.index’[,?n] 例3：删除表s中的索引i_s_sexandbirth drop index s. i_s_sexandbirth 实验习题：

(1) 为学生表创建一个复合索引i_sdept_sno，以院系升序、学号降序。 (2) 查看表s中的索引信息。

(3) 将索引i_sdept_sno的名称改为i_s。 (4) 删除索引i_s。

(5) 为课程表创建一个唯一聚集索引i_cname，以课程名升序。如果不能执行，请分析

说明原因。 实验报告要求: ? ? ? ? ?

实验目的 实验内容及步骤 实验习题的源程序清单

写出在实验过程中遇到的问题及解决方法 要求字迹端正、条理清晰、概念正确

27

实 验 七

实验名称：存储过程的创建和使用 实验学时：4

实验目的：

1） 了解存储过程的概念

2） 了解使用存储过程的特点及用途 3） 掌握创建存储过程的方法 4） 掌握执行存储过程的方法

5） 了解查看、修改和删除存储过程的方法 实验内容及步骤：

（一） 存储过程的概念

SQL Server的存储过程类似于编程语言中的过程。在使用Transact-SQL语言编程的过程中，我们可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。

存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。

（二） 存储过程的优点及分类

存储过程的优点 ：

? 实现了模块化编程。

? 存储过程具有对数据库立即访问的功能。

? ? ?

使用存储过程可以加快程序的运行速度。 使用存储过程可以减少网络流量。

使用存储过程可以提高数据库的安全性。

存储过程的分类

? 系统存储过程 ：系统自动创建，主要存储在master数据库中，一般以sp_为前缀。

?

用户自定义存储过程 ：由用户创建并能完成某一特定功能的存储过程。

（三） 创建存储过程的方法

? 使用企业管理器创建存储过程：

在SQL Server企业管理器中，选择指定的服务器和数据库，右击要创建存储过程的数据库，在弹出的快捷菜单中依次选择“新建|存储过程?”选项。在弹出的“新建存储过程”对话框中的文本框中输入创建存储过程的T-SQL语句。

例1：创建一个名为StuInfo的存储过程，完成的功能是在s表中查询200501班的学生的学号、姓名、性别、出生日期的内容。输入代码如下：

CREATE PROCEDURE StuInfo AS

SELECT SNO AS 学号， SNAME AS 姓名， SSEX AS 性别， SAGE AS 年龄 FROM S

WHERE LEFT(SNO,6)=’200501’

输入完毕单击“检查语法”按钮，进行语法检查，检查成功，系统弹出提示信息框，单击“确定”，保存该存储过程，并关闭该对话框。 ? 使用 T-SQL创建存储过程

28

使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。语法如下： CREATE PROC[EDURE] procedure-name[;number] [{@parameter data_type}

[varying][=default][outpur] ][,?n]

WITH

{RECOMPILE│ENCRYPTION│RECOMPILE，ENCRYPTION}] [FOR REPLICATION]AS sql_statement [?n] 说明：

procedure-name：存储过程的名称，必须唯一且符合标识符命名规则。

@parameter：过程中的参数，在CREATE PROCEDURE语句中可以申明一个或多个参数。 data_type：用于指定参数的数据类型。

AS：用于指定该存储过程要执行的操作。

例2：创建存储过程StuScoreInfo，完成的功能是在表s,c和sc中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。 程序清单：

打开student数据库 use student

--查询是否已存在此存储过程，如果存在，就删除它 if exists (select name from sysobjects

where name =’StuScoreInfo’ and type= ‘P’)

drop procedure StuScoreInfo go

--创建存储过程

creat proceure StuScoreInfo

as

select 班级=substring(s.sno,1,len(s.sno)-2), s.sno as 学号， sname as 姓名， sex as 性别,

c.cname as 课程名称, sc.score as 考试分数

from s,c,sc

where s.sno=sc.sno and c.cno=sc.cno

例3：创建一个带有参数的存储过程stu_info，该存储过程根据传入的学生编号，在t_student中查询此学生的信息。 程序清单：

--删除已存在的存储过程

use student

if exists (select name from sysobjects where name = ‘stu_info’ and type =’P’) drop procedure stu_info go

29

--创建存储过程

use student go

create procedure stu_info @sno varchar(8)

as

select 班级=substring(s.sno,1,len(s.sno)-2), s.sno as 学号, sname as 姓名, sex as 性别,

birthday as 出生日期 polity as 政治面貌 from s

where sno=@sno

（四） 执行存储过程的方法

存储过程创建成功后，保存在数据库中。在SQL Server中可以使用EXECUTE命令来直接执行存储过程。语法如下： [[EXEC[UTE]]

{

[@RETURN_STATUS=]

{procedure_name[;number]│@procedure_name_var} [[@parameter=]{value│@variable[OUTPUT]│[DEFAULT]} [,?n]

[WITH RECOMPILE]

说明：

EXECUTE：执行存储过程的命令关键字，若此语句是批处理中的第一条语句，可以省略此关键字。

@RETURN_STATUS：是一个可选的整型变量，保存存储过程的返回状态。这个变量在使用前，必须在批处理、存储过程或函数中申明过。

procedure_name：指定执行的存储过程名称。

@procedure_name_var：局部定义变量名，代表存储过程名称。

@parameter：在创建存储过程时定义的过程参数。调用时向存储过程所传递的参数值由value参数或@varible变量提供，或者使用DEFAULT关键字指定使用该参数的默认值，OUTPUT参数说明指定参数为返回参数。

例1：执行前面例1中创建的StuInfo存储过程。 Use student

Exec StuInfo /*或者直接写存储过程的名称StuInfo*/

注意：如果省略Exec关键字，则存储过程必须是批处理中的第一条语句，否则会出错。 例2：执行前面例2中创建的StuScoreInfo存储过程。 Use student

Exec StuScoreInfo

例3：执行前面例3中创建的Stu_Info存储过程，该存储过程有一个输入参数“学号”，在执行时要传入一个学号值。

30

Use student

Exec Stu_Info ‘20050001’ 或：

Use student

Exec Stu_Info @sno=‘20050001’

（五） 查看存储过程

1. 使用企业管理器查看用户创建的存储过程：在企业管理器中，选择指定的服务器和数据库，单击“存储过程”文件夹，在右边的页框中右击要查看的存储过程，在弹出的快捷菜单中选择“属性”选项，弹出“存储过程属性”对话框，在此对话框中可看到存储过程的源代码。

2. 使用系统存储过程查看用户创建的存储过程： sp_help [[@objname=]name]

用于显示存储过程的参数及其数据类型。 sp_helptext [[@objname=]name] 用于显示存储过程的代码。

sp_depends [@objname=]’object’

用于显示和存储过程相关的数据库对象。 （六） 修改存储过程

1. 使用企业管理器修改存储过程在企业管理器中，选择指定的服务器和数据库，单击“存储过程”文件夹，在右边的页框中右击要查看的存储过程，在弹出的快捷菜单中选择“属性”选项，弹出“存储过程属性”对话框，在此对话框中可直接修改存储过程的代码。 2. 使用T_SQl修改存储过程

ALTER PROC[EDURE] procedure-name[;number] [{@parameter data_type}

[varying][=default][outpur]][,?n]

WITH

{RECOMPILE│ENCRYPTION│RECOMPILE，ENCRYPTION}]

[FOR REPLICATION]AS sql_statement [?n] 例1：修改前面创建的stu_info存储过程，使之完成以下功能：根据传入的学号，在表s,c,sc中查询此学生的班级、姓名、性别、考试课程名称和考试分数。 Use student

Alter procedure stu_info @sno varchar(10) as

select班级=substring(s.sno,1,len(s.sno)-2), sname as 姓名， sex as 性别,

c.cname as 课程名称, score as 考试成绩

from s,c,sc

where s.sno=@sno and s.sno=sc.sno and c.cno=sc.cno exec stu_info ‘20050101’ （七） 重命名存储过程

31

3. 使用企业管理器修改存储过程在企业管理器中，右击要操作的存储过程，在弹出的快捷菜单中选择“重命名”选项。 4. 使用T_SQl修改存储过程

sp_rename 原存储过程名称，新存储过程名称 （八） 删除存储过程

5. 使用企业管理器修改存储过程在企业管理器中，右击要操作的存储过程，在弹出的快捷菜单中选择“删除”选项。

6. 使用T_SQl修改存储过程

drop procedure {procedure} [,?n] 实验习题：

说明：学号以四位年级，两位班级，两位编号的形式编码。例如“20070101”表示2007级01班的01号同学。

1. 创建一个无参存储过程，返回200502班的学生信息。

2. 创建一个带参数的存储过程，输入参数为课程名称，查询有哪些班级的哪些学生参加了这门课程的考试及学生的考试成绩。 3. 对于pubs数据库完成如下操作：

(1) 创建存储过程avg_price,用于求所有出版图书的平均单价。并通过输出参数返回

该平均单价。要求在创建存储过程前要先判断该存储过程是否已存在，如果存在，则将其删除。 (2) 执行第（1）题创建的存储过程，打印图书平均单价。

(3) 在pubs数据库中创建存储过程max_price，根据指定的图书类型（输入参数）返回该类图书的最高单价（输出参数）。要求在创建存储过程前要先判断该存储过程是否已存在，如果存在，则将其删除。

(4) 执行第（3）题创建的存储过程，指定图书类型那个为“mod_cook”，打印该类图书

的最高单价。 (5) 删除存储过程avg_price和max_price。 实验报告要求: ? ? ? ? ?

实验目的 实验内容及步骤 实验习题的源程序清单

写出在实验过程中遇到的问题及解决方法 要求字迹端正、条理清晰、概念正确

32

实 验 八

实验名称：触发器的创建和使用 实验学时：2

实验目的：

1） 了解触发器和一般存储过程的主要区别 2） 了解使用触发器的优点 3） 掌握创建触发器的方法 4） 掌握查看触发器信息的方法 5） 了解删除触发器的方法 实验内容及步骤：

（一） 触发器的概念

触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行触发器是一个功能强大的工具，它与表格紧密相连，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。

（二） 触发器的优点 ? ? ? ? ?

触发器是自动的执行的。当对表中的数据做了任何修改之后立即被激活。 触发器可以通过数据库中的相关表进行层叠更改。 触发器可以强制限制。

AFTER触发器：这种类型的触发器将在数据变动完成后才被触发，AFTER触发器只能在表上定义。在同一个数据表中可以创建多个AFTER触发器。

INSTEAD OF触发器：SQL Server 2000新增功能。这种类型的触发器将在数据变动以前被触发，并取代变动数据的操作，而去执行触发器定义的操作。INSTEAD OF触发器可以在表或视图上定义。在表或视图上，每个INSERT、UPDATE和DELETE语句最多可以定义一个INSTEAD OF触发器。 （四） 触发器的创建

? 使用企业管理器创建：在企业管理器中，展开指定的服务器和数据库，右击某个表，从弹出的快捷菜单中依次选择“所有任务|管理触发器”选项，会出现触发器属性对话框。在该对话框的“名称”文本框中选择“新建”，然后在文本框中输入创建触发器的文本。

当创建一个触发器时必须指定以下几项内容：

? ? ?

触发器的名称 在其定义触发器的表 触发器将何时激发

（三） 触发器的类型

? 执行触发操作的编程语句 例如：创建一个INSERT触发器，当在表s中插入一条新记录时，触发该触发器，并给出“你插入了一条新记录！”的提示信息。在文本框中输入以下文本： CREATE TRIGGER Stu_Insert on [dbo].[s] FOR INSERT AS

DECLARE @msg char(30)

SET @msg=”你插入了一条新记录！”print @msg

33

单击“检查语法”按钮，可以检查语法是否正确。然后单击“应用”按钮，在名称下拉列表中出现新创建的Stu_Insert触发器的名称，单击“确定”按钮，即可关闭该对话框，成功创建触发器。

创建该触发器后，查看向t_student表中插入数据时此触发器所完成的功能。在查询分析器中输入以下SQL语句： use student go

insert into t_student (sno,sname,sex)

values(‘20030125’,’王帆’，‘男’) 执行后结果如下：

你插入了一条新记录！

（所影响的行数为1行）

? 使用T-SQL创建：使用Transact-SQL语言中的CREATE TRIGGER命令可以创建触发器，其中需要指定定义触发器的基表、触发器执行的事件和触发器的所有指令。创建触发器类似创建存储过程，语法形式如下： CREATE TRIGGER trigger-name ON{table│view} [WITH ENCRYPTION]

{

{{FOR│AFTER│INSTEAD OF}{[DELETE][,][INSERT][,][UPDATE]}

[WITH APPEND]

[NOT FOR REPLICATION] AS

[{IF UPDATE(column)

[{AND│OR}UPDATE(column)] [?N

│IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask)

{comparison_operator}column_bitmask[..n]

}]

sql_statement [?n] }

}

说明：

trigger -name：触发器的名称，必须唯一且符合标识符命名规则。

AFTER：用于规定此触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。若仅指定FOR关键字，则AFTER是默认设置。注意该类型触发器仅能在表上创建，而不能在视图上定义该触发器。

INSTEAD OF：用于规定执行的是触发器而不是执行触发SQL语句，从而用触发器替代触发语句的操作。每个INSERT、UPDATE、DELETE语句最多可以定义一个INSTEAD OF触发器。INSTEAD OF触发器不能在WITH CHECK OPTION 的可更新视图上定义。

{[DELETE][,][INSERT][,][UPDATE]}：用于指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。

34

例1：创建一个AFTER触发器，要求实现以下功能：在sc表上创建一个插入、更新类型的触发器scoreCheck，当在grade字段中插入或修改考试分数后，触发该触发器，检查分数是否在0~100之间。 Use student

if exists (select name from sysobjects where name = ‘scoreCheck’ and type =’TR’)

drop trigger scoreCheck /*创建触发器*/

create trigger scoreCheck on sc

for insert,update as

if update(score)

print ‘AFTER触发器开始执行??’ begin

declare @ScoreValue real

select @ScoreValue=(select score from inserted) if @ScoreValue>100 or @ScoreValue<0

print ‘输入的分数有误，请确认输入的考试分数！’ end

创建了scoreCheck触发器之后，在查询分析器中输入以下SQL语句：

use student

print ‘在sc中插入记录时触发器执行结果：’ /*在屏幕上显示引号中内容*/ print ‘’ /*在屏幕上显示一空行*/ insert into sc

values(‘20030156’,’01’,-40) /*在屏幕上显示输入错误信息*/ update sc

set score=123

where sno=’20030101’and cno=’1’ /*在屏幕上显示输入错误信息*/

例2：创建一个INSTEAD OF触发器，要求实现以下功能：在c表上创建一个删除类型的触发器NotAllowDelete，当在c表中删除记录时，触发该触发器，显示不允许删除表中数据的提示信息。

Use student

if exists (select name from sysobjects

where name = ‘NotAllowDelete’ and type =’TR’) drop trigger NotAllowDelete /*创建触发器*/

create triggerNotAllowDelete on c

instead of delete as

print ‘INSTEAD OF触发器开始执行??’

print ‘本表中的数据不允许被删除！不能执行删除操作！’ NotAllowDelete触发器后，在查询分析器中输入以下SQL语句：

35

use student

delete from c where cno=20030101’

/*屏幕上显示NSTEAD OF触发器开始执行??*/

（五） 触发器的查看

? 使用企业管理器查看查看触发器

在企业管理器中，展开指定的服务器和数据库，选择指定的数据库和表，并右击要查看的表，从弹出的快捷菜单中选择“所有任务”│“管理触发器”选项，在“触发器属性”对话框中，从名称下拉列表框中选择要查看的触发器名称，在下面的文本框中就会显示该触发器的定义语句。

? 使用T_SQl查看触发器： sp_help ‘触发器名称’ 用于查看触发器的一般信息 sp_helptext ‘触发器名称’ 用于查看触发器的正文信息

sp_depends ‘触发器名称’

用于查看指定触发器所引用的表或指定的表涉及到的所有触发器。

（六） 触发器的删除

? 使用企业管理器删除存储过程

在企业管理器中，右击要删除的触发器，从弹出的快捷菜单中选择“所有任务”│“管理触发器”选项，在“触发器属性”对话框中，从名称下拉列表框中选择要删除的触发器，然后单击“删除”按钮，即可删除该触发器。 ? 使用T_SQl删除

drop TRIGGER {触发器名} [,?n] 实验习题：

实验报告要求: ? ? ?

实验目的 实验内容及步骤 实验习题的源程序清单

? 写出在实验过程中遇到的问题及解决方法 要求字迹端正、条理清晰、概念正确

36

实 验 九

实验名称：数据库的安全性 实验学时：2

实验目的：

1） 了解登录账户的管理理念与具体方法。

2） 了解数据库用户的管理的要则。 3） 了解用户权限管理的内涵与方法。 实验内容:

（一） 认证模式浏览与设置

在企业管理器中选中展开一个服务器组，右击某个服务器实例，再单击“属性”，启动企业管理器下的SQL SERVER属性对话框，通过SQL SERVER属性下的安全选项卡浏览与设置认证模式。SQL Server提供以下两种身份验证模式。

?

Windows 身份验证模式

SQL Server数据库系统通常运行在Windows NT服务器平台上，而NT作为网络操作系统，本身就具备管理登录、验证用户合法性的能力，Windows身份验证模式正是利用了这一用户安全性和账户管理的机制，允许SQL Server也可以使用Windows的用户名和密码。在这种模式下，用户只需要通过Windows的身份验证，就可以连接到SQL Server，而SQL Server本身也就不需要管理一套登录数据。

? 混合模式（Windows身份验证和SQL Server身份验证）

SQL Server可以设置其自己的SQL Server登录账户。用户登录时，SQL Server将对用户的账户进行验证。如果SQL Server未设置该登录账户，或该账户的用户名、密码不正确，则身份验证将失败，而且用户将收到错误信息。混合模式允许用户使用Windows NT安全性或SQL Server安全性连接到SQL Server，这就意味着用户可以使用他的Windows账户，或使用他的SQL Server账户登录到SQL Server系统。

? 身份验证模式的选择

对于Windows NT的用户，既可以使用Windows身份验证模式，也可以使用SQL Server的身份验证模式。而对于Windows 9.x的用户只能使用SQL Server的身份验证模式。应用程序开发人员和数据库用户也许更喜欢“SQL Server身份验证”模式，因为他们可以通过对登录账户和密码的管理实现权限控制。 （二） 新建登录账户

SQL Server有以下两个默认的登录账户：

37

sa：即系统管理员（system administrator）账户，该账户在SQL Server系统和所有数据库中拥有所有的权限。

BUILTIN\\Administrators：该账户为Windows NT系统管理员账户，具有与sa有相同的权限。

1．在企业管理器中创建登录账户 展开指定服务器实例下的“安全性”文件夹，用鼠标右击“登录”项，在弹出菜单中选择“新建登录”命令，打开并设置“新建登录”对话框。

2．使用企业管理器修改账户

展开指定服务器实例下的“安全性”文件夹，用鼠标单击“登录”项，可以在右侧窗格中查看到已经存在的SQL Server登录账户。用鼠标右键单击登录账户名，在弹出的快捷菜单中选择“属性”命令，打开“登录属性”对话框，在该对话框中可以对账户信息进行修改。

3．使用企业管理器删除账户

在企业管理器中，用鼠标右键单击SQL Server账户，在弹出的快捷菜单中选择“删除”命令，在弹出的确认对话框中单击“是”按钮，可以删除该账户。 （三） 创建、查看、删除数据库用户

38

拥有登录账户的用户才能通过SQL Server身份验证，从而获得对SQL Server实例的访问权限。但通过SQL Server的身份验证并不代表用户就能够访问SQL Server中的数据，要访问某个具体的数据库，还必须使登录账户成为某数据库的用户。

两个特殊的数据库用户：

? Dbo：数据库所有者，是具有在数据库中执行所有活动的权限的用户，它与登录账户

sa相对应。

? Guest：允许没有用户账户的登录访问数据库。当满足下列所有条件时，登录采用

guest用户的标识。

? 登录有访问SQL Server实例的权限，但没有通过自己的用户账户访问数据

库的权限。

? 数据库中含有guest用户账户。

（1）使用企业管理器创建数据库用户

展开指定的数据库，选择“用户”项，可以查看该数据库中用户的信息。默认情况下，用户创建的数据库中只有一个用户，即dbo。用鼠标右击“用户”项，从快捷菜单中选择“新建数据库用户”命令，打开“新建用户”对话框，如下图。

（2）使用企业管理器修改数据库用户

右击数据库用户名，从弹出菜单中选择“属性”命令，打开“用户属性”对话框。（与“新建用户”对话框类似，但其“权限”按钮变为有效）可以在此对话框中修改用户信息。 （3）使用企业管理器删除数据库用户 右击数据库用户名，从弹出菜单中选择“删除”命令。 （四） 权限管理

用鼠标右击一个表、视图或存储过程，在弹出菜单中选择“所有任务→管理权限”如图：

39

本文来源：https://www.bwwdw.com/article/k6p3.html