数据库系统原理实验报告册（带答案） - 图文

河南工程学院 计算机科学与工程系

数据库系统原理 实验报告册

学 期: 课 程: 专 业: 班 级: 学 号: 姓 名: 指导教师:

目 录

实验一 SQL Server 2000安装、数据库创建及管理............................................... 1

一、实验学时......................................................................................................... 1 二、实验目的......................................................................................................... 1 三、实验内容......................................................................................................... 1 四、实验要求....................................................................................................... 13 五、代码清单....................................................................................................... 13 六、实验中出现的问题及解决的方法............................................................... 15 七、思考讨论题或体会或对改进实验的建议................................................... 16 实验二 表和表数据的操作........................................................................................ 17

一、实验学时....................................................................................................... 17 二、实验目的....................................................................................................... 17 三、实验内容....................................................................................................... 17 四、实验要求....................................................................................................... 22 五、代码清单....................................................................................................... 22 六、实验中出现的问题及解决的方法............................................................... 24 七、思考讨论题或体会或对改进实验的建议................................................... 25 实验三 索引和视图.................................................................................................... 26

一、实验学时....................................................................................................... 26 二、实验目的....................................................................................................... 26 三、实验内容....................................................................................................... 26 四、实验要求....................................................................................................... 31 五、代码清单....................................................................................................... 31 六、实验中出现的问题及解决的方法............................................................... 33 七、思考讨论题或体会或对改进实验的建议................................................... 34 实验四 T-SQL程序设计............................................................................................. 35

一、实验学时....................................................................................................... 35

I

二、实验目的....................................................................................................... 35 三、实验内容....................................................................................................... 35 四、实验要求....................................................................................................... 39 五、代码清单....................................................................................................... 39 六、实验中出现的问题及解决的方法............................................................... 41 七、思考讨论题或体会或对改进实验的建议................................................... 42 实验五 数据库安全性................................................................................................ 43

一、实验学时....................................................................................................... 43 二、实验目的....................................................................................................... 43 三、实验内容....................................................................................................... 43 四、实验要求....................................................................................................... 46 五、代码清单....................................................................................................... 47 六、实验中出现的问题及解决的方法............................................................... 49 七、思考讨论题或体会或对改进实验的建议................................................... 49 实验六 数据库完整性................................................................................................ 51

一、实验学时....................................................................................................... 51 二、实验目的....................................................................................................... 51 三、实验内容....................................................................................................... 51 四、实验要求....................................................................................................... 54 五、代码清单....................................................................................................... 54 六、实验中出现的问题及解决的方法............................................................... 56 七、思考讨论题或体会或对改进实验的建议................................................... 57

II

实验一 SQL Server 2000安装、数据库创建及管理

一、实验学时：2学时 二、实验目的

（1）了解安装SQL Server2000的硬件和软件环境。 （2）掌握SQL Server 2000的安装方法。

（3）掌握SQL Server 2000服务器工具的使用方法。 （4）灵活运用SQL Server数据库的创建方法。

（5）掌握数据库与物理文件的结构关系，理解数据文件分组的作用。 （6）掌握SQL Server中使用企业管理器和查询分析器创建数据库备份和恢复的方法。

三、实验内容

（一）熟悉SQL Server 2000环境

1．安装SQL Server 2000的硬件要求、软件环境

（1）硬件要求

①CPU：Intel Pentium或与兼容，主频166MHz以上，DEC Alpha及其兼容系统也可以。

②内存（RAM）：企业版、标准版、开发版至少64MB内存，个人版在Windows 2000上至少64MB，在其他操作系统上至少32MB。其他版本最少需要32MB内存，建议使用更多的内存。

③硬盘空间：

SQL Server数据库组件：95MB~270MB，一般为250MB。 Analysis Services：至少50MB，一般为130MB。 English Query：80MB。 （2）软件要求

表1-1 安装SQL Server 2000的操作系统要求

版本或组件 企业版 标准版 个人版 操作系统要求 Windows NT SERVER 4.0、Windows 2000 Server、Windows 2000 Advanced Server、Windows 2000 Data Center Server Windows NT SERVER 4.0、Windows 2000 Server、Windows 2000 Advanced Server、Windows 2000 Data Center Server Windows 98、Windows Me、Windows 2000 Professional、Windows NT SERVER 4.0、Windows 2000 Server和所有更高级的Windows操作系统。 1

开发板 Windows 98、Windows Me、Windows NT SERVER 4.0、Windows 2000 Professional、Windows 2000 Server和所有更高级的Windows操作系统。 说明： ☆在Microsoft Windows NT Server 4.0上，必须安装Service Pack5 (SP5)或更高版本。这是SQL Server 2000所有版本的最低要求。SQL SERVER 2000中文版不支持英文版的NT 4.0企业版。

☆SQL SERVER 2000在Windows NT 4.0终端服务器上不受支持。在不带网卡的Windows 98计算机上安装SQL SERVER 2000个人版，需要Windows 98第二版。

☆SQL SERVER 2000的某些功能要求在Microsoft Windows 2000 SERVER以上的版本才能运行。因此大家安装Windows SERVER 2000（建议为Advanced版本）。 2．SQL Server 2000的安装

SQL Server 2000的安装过程与其它Microsoft Windows系列产品类似。用户可根据向导提示，选择需要的选项一步一步地完成。安装步骤如下：

以标准版的安装为例。开始安装时，插入SQL SERVER光盘，系统会自动运行Autorun.exe文件，并显示初始安装界面。如果没有显示初始安装界面，你需要运行安装光盘上的Setup.exe程序来启动安装。安装主界面如图1-1所示。

图1-1 安装SQL Server 2000组件界面

选择安装SQL Server 2000组件，出现组件安装界面，如图1-2所示。

图1-2 安装SQL Server 2000组件选项界面

2

选择安装数据库服务器，进入SQL Server 2000的安装向导，如图1-3所示。

图1-3 安装向导界面

点击“下一步”，到计算机名称对话框，如图1-4所示。

用户可以选择把数据库安装在本地计算机或网络上的远程计算机。对本地计算机和远程计算机，可用的选项是相同的。但进行远程安装时，本地计算机和远程计算机必须运行WINNT/2000并且必须使用远程计算机的帐号登录到该计算机。

图1-4 计算机名称界面

“本地计算机”是默认选项，本地计算机的名称就显示在上面，点击“下一步”，出现安装选择对话框，如图1-5所示。

图1-5 安装选择界面

3

默认选择“创建新的SQL Server实例，或安装客户端工具”，点击“下一步”，出现用户信息对话框，如图1-6所示。

图1-6 用户信息界面

输入姓名和公司名称，点击“下一步”，出现软件许可协议，如图1-7所示。

图1-7 软件许可证协议界面

点“是”按钮，出现安装定义对话框，如图1-8所示。

图1-8 安装定义界面

选择“服务器和客户端工具”，点击下一步，出现实例名对话框，如图1-9所示。

4

图1-9 实例名称界面

选择默认实例或输入一个实例名。SQL SERVER支持多实例，不同的实例有自己的一套数据库对象，相互间不共享。系统提供了“默认”复选框，点击下一步，出现安装类型选择框，如图1-10所示。

图1-10 安装类型界面

典型安装使用了默认安装选项，包括SQL SERVER、客户管理工具和在线文档。最小安装只安装了能够允许运行和使用SQL SERVER的必须和最小配置。自定义安装可以增加、改变或删减不同的安装组件。选择典型，点击“下一步”，出现服务帐户对话框，如图1-11所示。

图1-11 服务帐户界面

分配一个WINDOWS用户帐号给SQL SERVER和SQL SERVER Agent。使用域帐号可以允许系统通过网络与不同的服务器进行交互。在XP中安装，请选择“使用本地系统帐户”这一项。接受系统的缺省值，点击“下一步”，出现身份验证模式窗口，如图1-12所示。

5

图1-12 身份验证界面

接受系统的缺省值，点击“下一步”，出现开始拷贝文件窗口，如图1-13所示。

图1-13 开始文件复制界面

点击“下一步”，开始将文件拷贝到选择的目录中，如图1-14所示。

图1-14文件复制界面

文件拷贝完成后，出现安装结束界面，如图1-15所示。

6

图1-15 安装完毕界面

点击“完成”按钮，完成SQL Server 2000的安装。 3．SQL Server 2000服务的启动和停止

启动和停止SQL Server服务器实例，有下列方法： ① 在操作系统启动时自动启动每个服务。

开始?设置?控制面板?管理工具?服务，系统打开Windows XP服务窗口，如图所示。

图1-16 系统服务界面

选择名称为“MSSQLSERVER”的服务，单击“操作”菜单?选“启动”菜单项。

② 用SQL Server服务管理器启动或停止服务。

开始?所有程序?Microsoft SQL Server?服务管理器，则进入服务管理器界面，如图所示。

7

图1-17 服务管理器界面

③ 在Windows NT或Windows 2000及以后各Windows系统中，使用net start 和net stop命令启动或停止SQL Server服务器服务，如图所示。

图1-18 命令提示符界面

④ 用SQL Server企业管理器启动或停止服务。

第1步 选择开始?所有程序?Microsoft SQL Server?企业管理器，进入企业管理器界面，右边为系统安装时建立的默认SQL Server组的图标。

第2步 双击SQL Server组图标，右边显示的是系统安装时建立的默认服务器实例的图标，图中的服务器实例处于停止状态。

图1-19 启动企业管理器后的界面 图1-20 未启动服务器服务时的企业管理器

第3步 双击该图标，系统将启动SQL Server服务器的服务，并建立企业管

8

理器与该实例的连接，如图所示。

图1-21 展开服务器后的界面 图1-22 服务管理器属性界面

⑤ 使用SHUTDOWN语句停止服务。

在查询分析器中使用SHUTDOWN语句命令停止服务。

4．熟悉SQL Server 2000中的企业管理器、查询分析器等工具的启动与使用。

SQL Server 2000主菜单如图1-23所示：

图1-23 SQL Server 2000主菜单

（二）数据库的创建及管理 1．用企业管理器创建数据库

创建一个名称为学生课程Stu_Course数据库。数据文件的逻辑文件名为“Stu_Course_data”，磁盘文件名为“Stu_Course_data.mdf”，初始大小设为1MB，文件增长增量设为10%，文件增长方式设为自动增长，文件的增长上限设为10MB；事务日志文件的逻辑文件名为“Stu_Course_log”，磁盘文件名为“Stu_Course_log.ldf”，初始大小为1MB，文件增长增量设为1MB，文件的增长限制设为5MB。

具体创建步骤记录如下：

2．用T-SQL语言创建数据库

（1）创建数据库Stu_Course1的具体参数如下： 参数名称 参考参数 数据库名称 Stu_Course1 数据逻辑文件名称 Stu_Course1_data 数据物理文件名称 Stu_Course1_data.mdf 数据文件初始大小 1MB 数据文件大小最大值 10MB 数据文件增长增量 10% 9

日志逻辑文件名称 Stu_Course1_log 日志物理文件名称 Stu_Course1_log.ldf 日志文件初始大小 1MB 日志文件大小最大值 5MB 日志文件增长增量 1MB 在查询分析器中输入如下所示的创建数据库的T-SQL语句： CREATE DATABASE Stu_Course1 ON (

NAME = 'Stu_Course1_data',

FILENAME = 'c:\\program files\\microsoft sql server\\mssql\\data\\Stu_Course1_data.mdf', SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10% ) LOG ON (

NAME = 'Stu_Course1_log',

FILENAME = 'c:\\program files\\microsoft sql server\\mssql\\data\\Stu_Course1_log.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB )

（2）查看、验证创建的数据库

方法1：执行sp_helpdb系统存储过程查看Stu_Course数据库的信息。 命令：

sp_helpdb Stu_Course

方法2：在企业管理器中查看。 具体操作方法：

3．修改数据库的属性

创建完一个数据库后，可以用企业管理和T-SQL语句查看数据库，修改数据库属性，改变数据库大小。

（1）用企业管理器修改数据库属性

将数据库Stu_Course1数据文件的初始大小改为3MB，最大值改为30MB，数据增长改为5%，日志文件的初始大小改为2MB，最大值改为10MB，数据增长改为10%。

操作步骤记录如下：

（2）用T-SQL语句修改数据库的属性 用T-SQL语言可以全面管理数据库。

10

1）修改Stu_Course1_log日志文件的最大值。

将Stu_Course1日志文件的最大值由10MB更改为20MB，T-SQL语句：

alter database Stu_Course1 modify file (

name='Stu_Course1_log', maxsize=20MB )

2）修改Stu_Course1_log日志文件的初始值。

将Stu_Course1日志文件的初始值由2MB更改为10MB，T-SQL语句：

alter database Stu_Course1 modify file (

name='Stu_Course1_log', size=10MB )

3）将数据库Stu_Course1数据文件的初始大小改为30MB，最大值改为300MB，数据增长改为10%。

用T-SQL语句：

alter database Stu_Course1 modify file (

name='Stu_Course1_data', size=30MB, maxsize=300MB, filegrowth=10% )

4．数据库的更名及删除

（1）使用企业管理器进行Stu_Course1数据库的更名及删除 1）将Stu_Course1数据库更名为Stu_Course2。（提示：使用分离数据及附加数据，再加入时更名即可）。

操作步骤：

2）数据库删除。 操作步骤：

（2）使用T-SQL语句进行数据库的更名及删除 1）将数据库Stu_Course更名为Stu_Course2。

T-SQL命令为（提示：存储过程sp_renamedb或用alter database命令）：

sp_renamedb Stu_Course,Stu_Course2 或

alter database Stu_Course

11

modify name = Stu_Course2

2）删除数据库Stu_Course2。 drop database Stu_Course2 （三）数据库的备份和恢复

1．在企业管理器中创建一个备份设备。

① 在企业管理器中扩展服务器，在管理文件夹中找到备份文件夹。用鼠标右键单击该文件夹，在弹出的菜单中选择“新建备份设备”项，则出现“备份设备属性”对话框，如图所示。在名称文本框中输入设备名（本例为aa）。单击“确定”按钮，备份设备就创建成功了。

图1-24 “备份设备属性”对话框

② 在备份文件夹的窗口中出现了新创建的设备。选中该设备，用鼠标右键单击，在弹出的菜单中选择“属性”项，则出现“备份设备属性”对话框。

2．利用向导为Stu_Course数据库生成一个完全备份。

① 在企业管理器中扩展服务器，在数据库文件夹中选中要备份的数据库文件夹（Stu_Course数据库）。选择菜单[工具]|[向导]，则出现向导选择对话框。在向导选择对话框中，单击管理左边的“+”号，使之展开。选择“备份向导”项，单击“确定”按键。

② 进入创建备份向导后，首先出现的是欢迎使用创建数据库备份向导对话框，其中简单介绍了该向导的功能。单击“下一步”后，就会出现“选择要备份数据库”对话框。选择好要备份数据库后，单击“下一步”，就会出现“键入备份的名称和描述”对话框。键入备份名称为“学生课程备份”，单击“下一步”，就会出现“选择备份类型”对话框。这里我们选择“数据库备份”，单击“下一步”，就会出现“备份验证和调度”对话框。这里我们选择默认状态，单击“下一步”，就会出现“正在完成备份数据库向导”对话框。单击“完成”按钮，完成备份数据库向导，生成数据库备份Stu_Course.bak文件。

③ 用其他方法生成备份。

（完成Stu_Course数据库备份后，请将Stu_Course.bak保存起来以备后用）

3．在企业管理器中恢复Stu_Course数据库。

① 打开Stu_Course数据库，删除其中一个表（如SC表），即当前的Stu_Course数据库中没有SC表。

② 恢复Stu_Course数据库。步骤如下：在企业管理器中扩展服务器，在数

12

据库文件夹中选中要恢复的数据库文件夹（Stu_Course数据库）。用鼠标右键单击，在弹出的菜单中选择“所有任务”项，在随之出现的级联菜单中选择“还原数据库”项，则出现还原数据库对话框。在此例中按“确定”按钮即可完成恢复工作。

③ 再打开Stu_Course数据库，看一看当前的Stu_Course数据库中有没有SC表。

④ 用其他方法进行恢复。

4．使用T-SQL语句对Stu_Course数据库进行备份与恢复。

（1）使用T-SQL语句对Stu_Course数据库进行备份 用T-SQL语句：

BACKUP DATABASE Stu_Course TO DISK='c:\\Stu_Course.bak' （2）使用T-SQL语句对Stu_Course数据库进行恢复 用T-SQL语句：

RESTORE DATABASE Stu_Course FROM DISK='c:\\Stu_Course.bak'

四、实验要求

（1）硬件设备：奔腾II或奔腾II以上计算机，局域网。 （2）软件环境：WINDOWS 9X/NT、WINDOWS SERVER、WINDOWS XP、SQL SERVER 2000中文版企业版或标准版。

（3）实验课前预习，课后及时完成实验内容。

（4）实验过程及记录按题目格式要求填写在代码清单中。

五、代码清单

13

14

六、实验中出现的问题及解决的方法

15

七、思考讨论题或体会或对改进实验的建议

16

实验二 表和表数据的操作

一、实验学时：2学时 二、实验目的

（1）掌握表的创建方法。

（2）掌握修改表结构的方法。

（3）掌握添加、修改、删除表数据的方法。

三、实验内容

1．在数据库Stu_Course中创建表

（1）使用企业管理器创建Student表 Student表结构及其约束为：

表2-1 Student表结构和约束 列名 数据类型 Sno char Sname char Ssex char Sage smallint Sdept char 操作步骤如下： )

宽度 9 20 2 20 允许空值 否 是 是 是 是 缺省值 主键 是 说明 学号 姓名 性别 年龄 所在系 CREATE TABLE Student (

Sno char(9) NOT NULL PRIMARY KEY, Sname char(20) NULL, Ssex char(2) NULL, Sage smallint NULL, Sdept char(20) NULL

（2）使用T-SQL语句创建表 1）创建Course表

Course表结构及其约束为：

表2-2 Course表结构和约束

列名 Cno Cname Cpno Ccredit 数据类型 char char char smallint 宽度 4 40 4 允许空值 否 是 是 是 17

缺省值 主键 是 外键 是 说明 课程号 课程名 先行课 学分

创建Course表T-SQL语句如下：

（执行正确后请将以Course.sql保存起来以备后用）

CREATE TABLE Course ( )

Cno char(4) NOT NULL PRIMARY KEY, Cname char(40) NULL, Cpno char(4) NULL, Ccredit smallint NULL,

Constraint FK_Course FOREIGN KEY(Cpno) REFERENCES Course(Cno)

2）创建表SC表

SC表结构及其约束为：

表2-3 SC表结构和约束 列名 数据类型 宽度 允许空值 缺省值 主键 Sno char 9 否 是 Cno char 4 否 是 Grade smallint 是 其中成绩为百分制。 创建SC表T-SQL语句如下：

（执行正确后请将以SC.sql保存起来以备后用）

CREATE TABLE SC ( )

Sno char(9) NOT NULL, Cno char(4) NOT NULL, Grade smallint NULL,

Constraint PK_SC PRIMARY KEY(Sno,Cno),

外键 是 是 说明 学号 课程号 成绩 Constraint FK_SC_Sno FOREIGN KEY(Sno) REFERENCES Student(Sno), Constraint FK_SC_Cno FOREIGN KEY(Cno) REFERENCES Course(Cno), Constraint CH_SC CHECK(Grade BETWEEN 0 AND 100)

注意：因为有外键的存在，三张表的创建有先后顺序限制！ 2．管理表

（1）添加和删除列

给Course表增加一列，字段名为Ctype(课程类型)，类型为char，长度为10,允许为空值。

1）使用企业管理器进行。 操作步骤如下：

插入该列后，在企业管理器中删除该列的操作步骤。

2）使用T-SQL语句进行。

添加Ctype字段的T-SQL语句。

ALTER TABLE Course

ADD Ctype char(10) NULL

18

删除Ctype字段的T-SQL语句。

ALTER TABLE Course

DROP COLUMN Ctype

（2）表的重命名及删除

1）使用企业管理器重命名及删除表。 重命名操作步骤：

(如将Course表重命名为Course1表)

删除Course1表的命令：

2）使用T-SQL语句实现

在查询分析器中，将已保存的Course.sql打开，运行重建Course表。 重命名Course表为Course1表的T-SQL语句。（提示：用存储过程sp_rename） sp_rename Course,Course1

删除Course1表的T-SQL语句。 DROP TABLE Course1

3．对表添加、修改、删除数据

（1）插入数据简单操作

1）使用企业管理器向Student表中插入数据。

表2-4 Student表 Sno 200215121 200215122 200215123 200215125 200215126 操作步骤： insert into Student

Sname 李勇 刘晨 王敏 张立 张红 Ssex 男 女 女 男 女 Sage 20 19 18 19 19 Sdept CS CS MA IS CS values ('200215121','李勇','男',20,'CS') insert into Student

values ('200215122','刘晨','女',19,'CS') insert into Student

values ('200215123','王敏','女',18,'MA') insert into Student

values ('200215125','张立','男',19,'IS') insert into Student

values ('200215126','张红','女',19,'CS')

其它数据可自行添加。

2）使用T-SQL语句向Course表中插入数据。

表2-5 Course表 Cno 1 Cname 数据库 19

Cpno 5 Ccredit 4 2 数学 2 3 信息系统 1 4 4 操作系统 6 3 5 数据结构 7 4 6 数据处理 2 7 PASCAL语言 6 4 在查询分析器中，将已保存的Course.sql打开，运行重建Course表。 T-SQL命令为：

insert into Course

values ('1','数据库','5',4) insert into Course

values ('2','数学',NULL,2) insert into Course

values ('3','信息系统','1',4) insert into Course

values ('4','操作系统','6',3) insert into Course

values ('5','数据结构','7',4) insert into Course

values ('6','数据处理',NULL,2) insert into Course

values ('7','PASCAL语言','6',4)

其它数据可自行添加。

注意：因为有外键的存在，字段值的添加有先后顺序限制！ 3）使用T-SQL语句向SC表中插入数据。

表2-6 SC表 Sno Cno Grade 200215121 1 92 200215121 2 85 200215121 3 88 200215122 2 90 200215122 3 80 T-SQL命令为：（保存为insert_SC.sql已备后用）。其它数据可自行添加。

insert into SC

values ('200215121','1',92) insert into SC

values ('200215121','2',85) insert into SC

values ('200215121','3',88) insert into SC

values ('200215122','2',90) insert into SC

values ('200215122','3',80)

（完成以上操作后，请将Stu_Course数据库备份，得到Stu_Course.bak备份

20

文件，保存起来以备后面实验用）

（2）修改数据简单操作。

1）在企业管理器中，将Student表中的学号为“200215125”的同学的姓名改为“李双”。

操作步骤：

2）使用T-SQL语句，将Course表中的课程号为“2”的学分改为4。 T-SQL语句为：

update Course set Ccredit=4 where Cno='2'

3）使用T-SQL语句，将SC表中的选修了“2”课程的同学的成绩*80%。 T-SQL语句为：

update SC

set Grade=Grade*0.8 where Cno='2'

（3）删除数据简单操作（请注意约束的限制）

1）假设学号为“200215123”的同学已离开学校，则需要删除该记录，请在企业管理器中将Student表中将该记录删除。

操作步骤：

2）删除选修了“信息系统”的学生的记录。 T-SQL语句：

DELETE SC where Cno in(

select Cno from Course where Cname='信息系统')

3）使用T-SQL语句，删除所有的学生选课记录。 T-SQL语句： DELETE SC

4．复制表中的数据创建新表

（1）将Student表中的数据复制到一个新表Student1中。 T-SQL语句：

select * into Student1 from Student

注意：上面的语句在复制时自动生成新表Student1，而下面的语句要求表Student1必先存在，才能插入！！

insert into student1 select * from student

（2）新建库XUE，将Stu_Course库中的Student表中的数据复制到XUE库中，名称仍为Student。

T-SQL语句：

create database XUE go

select * into XUE.dbo.Student from Stu_Course.dbo.Student

21

（3）复制Stu_Course库中SC表与XUE库中的Student表的部分内容，以创建一新表Student2，其内容包括学生的学号、姓名、课程名、成绩。

T-SQL语句：

select XUE.dbo.Student.Sno 学号,XUE.dbo.Student.Sname 姓

名,Stu_Course.dbo.Course.Cname 课程名,Stu_Course.dbo.SC.Grade 成绩

into Stu_Course.dbo.Student2

from Stu_Course.dbo.SC,Stu_Course.dbo.Course,XUE.dbo.Student where Stu_Course.dbo.SC.Sno=XUE.dbo.Student.Sno and Stu_Course.dbo.SC.Cno=Stu_Course.dbo.Course.Cno

四、实验要求

（1）硬件设备：奔腾II或奔腾II以上计算机，局域网。 （2）软件环境：WINDOWS 9X/NT、WINDOWS SERVER、WINDOWS XP、SQL SERVER 2000中文版企业版或标准版。

（3）实验课前预习，课后及时完成实验内容。

（4）实验过程及记录按题目格式要求填写在代码清单中。

五、代码清单

22

23

六、实验中出现的问题及解决的方法

24

七、思考讨论题或体会或对改进实验的建议

25

实验三 索引和视图

一、实验学时：2学时 二、实验目的

（1）理解索引的概念与类型。

（2）掌握使用企业管理器创建与维护索引的方法。 （3）掌握T-SQL语句创建与维护索引的方法。 （4）理解视图的概念。

（5）掌握视图创建、更改的方法。 （6）掌握用视图管理数据的方法。

三、实验内容

（一）索引

索引分为两种：聚簇索引和非聚簇索引。每张表只能有一个聚簇索引，并应该。创建索引时的缺省设置是非聚簇索引。 1．使用企业管理器创建、管理索引

（1）创建索引

1）为Student表创建一个以Sno为索引关键字的惟一聚簇索引。（若原已有请删除，索引名为Sno_index）

方法1：（提示：选择表后点击鼠标右键，选“所有任务”->“管理索引”） 基本步骤：

方法2：（提示：选择表后点击鼠标右键，选“设计表”->“索引/键”） 基本步骤：

26

2）为Student表创建以Sname，Sex为索引关键字的非聚簇索引（对Sname以升序来排列，Sex以降序排列，并设置填充因子为70%）。索引名为：SS_index。

基本步骤：

（2）重命名索引

将索引文件Sno_index重新命名为Sno_index1。 基本步骤：

（3）删除索引

将索引文件Sno_index1删除。 基本步骤：

2．使用T-SQL语句创建、管理索引

（1）创建索引

1）为SC表创建一个非聚集索引Grade_index，索引关键字为Grade，升序，填充因子为80%。（提示：with fillfactor=）

T-SQL语句：

create nonclustered index Grade_index on SC(Grade ASC) with fillfactor=80

2）为SC表创建一个唯一性聚集索引SC_index，索引关键字为Sno，Cno。 用create index命令创建SC_index索引。 T-SQL语句：

create unique clustered index SC_index on SC(Sno,Cno) 注意：创建聚集索引前应先去除因为主键而存在的聚集索引！

3）为Course表创建一个非聚集索引CC_index，索引关键字为Ccredit、Cname，前者按降序，后者按升序进行索引。

T-SQL语句：

create nonclustered index CC_index on Course(Ccredit DESC,Cname ASC) 写出一个select语句，点击“显示估计的执行计划”按钮，查看该语句的预执行计划，看一看在条件语句中Cname与Ccredit顺序不同时，预执行计划有何不同，写出心得。

27

（2）重命名索引

将CC_index索引改名为CC_index1。 提示：使用sp_rename存储过程 T-SQL语句：

sp_rename 'Course.CC_index','CC_index1'

（3）删除索引

将Grade_index索引删除。 T-SQL语句：

drop index SC.Grade_index 注：在SQL SERVER 2000中，对于使用sp_rename重新命名的索引文件不能用DROP INDEX句来删除，可以用企业管理器删除。

drop index Course.CC_index1 （二）视图

创建视图是数据库应中的常见需求，可以使用企业管理创建、管理视图，也可以用T-SQL语言创建、管理视图。 1．用创建视图向导创建视图

使用视图向导创建一个名为stuview的投影视图，该视图从Student表中查询出所在系为“CS”的所有学生的学号，姓名，性别，年龄，所在系资料。

（提示：分别进入企业管理器，再分别单击“工具”、“向导”、“数据库”、“创建视图向导”，然后一步一步地创建视图。）

基本步骤：

2．用企业管理器创建、管理视图

（1）创建视图

使用企业管理器在表Student上创建一个能查询所在系为“IS”的学生学号、姓名、性别信息的投影视图。

1）依次进入企业管理器、数据库、Stu_Course库，并用鼠标右键单击“视图”。

2）在系统弹出的快捷菜单中单击“新建视图”，出现“新建视图”窗口。 3）最上面的一个窗格用于添加创建视图的基础对象，在其上按鼠标右键，单击快捷菜单中的“添加表”，将表Student添加到此窗格中，上面第二个窗格用于选择进入视图的列，分别选择相应字段：学号、姓名、性别。在查询条件窗格中输入查询条件：where Sdept='IS'，最后一个窗格为视图结果窗口。

4）单击工具栏，执行按钮“！”，观察执行结果是否正确。若正确，则单击“保存”按钮，输入文件名：stuview1，再单击“确定”按钮，视图创建完成。

（2）修改视图stuview1

将视图stuview1的定义修改为查询所在系为“MA”的学生学号、姓名、性别信息的投影视图。

（提示：在企业管理器中选中视图后->单击右键->设计视图->进行修改。） 基本操作步骤：

28

（3）查看视图stuview1信息

双击要查看的视图stuview1，可以看到视图的各种信息，可做语法检查，也可修改视图的定义，查看、设置视图的许可权限。

基本操作步骤：

（4）管理视图中的数据

1）查看视图stuview1中的数据。用鼠标右键单击要管理的视图stuview1，单击“打开视图”，再单击“返回所有行”。（试运行即可，不写步骤。）

2）将视图stuview1中学号为“200215125”的学生姓名由“张立”改为“张华”。

（注：须点击按钮“！”进行修改确认。） 基本步骤：

3．用T-SQL语言创建、管理视图

（1）创建视图

1）创建一个名为stuview2的水平视图，从数据库Stu_Course的Student表中查询出性别为“男”的所有学生的资料。并在创建视图时使用with check option。（注：该子句用于强制视图上执行的所有修改语句必须符合由Select语句where中的条件。）

T-SQL语句为：

create view stuview2 as

select * from Student where Ssex='男' with check option

2）创建一个名为stuview3的投影视图，从数据库Stu_Course的Course表中查询学分大于3的所有课程的课程号、课程名、学分。并在创建时对该视图加密。（提示：用WITH ENCRYPTION关键子句，加在as与视图名称之间，且sp_helptext无法看到该视图的定义脚本。）

T-SQL语句为：

create view stuview3 with encryption as

select Cno,Cname,Ccredit from Course where Ccredit>3

3）创建一个名为stuview4的视图，能检索出每位选课学生的学号、姓名、课程名、成绩。

T-SQL语句为：

create view stuview4 as

select Student.Sno,Sname,Cname,Grade from Student,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno

（2）查询视图的创建信息及视图中的数据

29

查看视图stuview2的定义脚本。（提示：sp_helptext） 结果为：

sp_helptext stuview2 （3）修改视图的定义

修改视图stuview3使其从数据库Stu_Course的Course表中查询学分大于3的所有课程的课程号、课程名、学分。（提示：若视图原具有加密保护，修改视图时若未加WITH ENCRYPTION子句，则修改后的视图不再加密。修改视图请查阅帮助alter view关键字。）

T-SQL语句：

alter view stuview3 as

select Cno,Cname,Ccredit from Course where Ccredit>3

（4）视图的更名与删除

1）将视图stuview4更名为stuv4。 方法：（提示：sp_rename） sp_rename stuview4,stuv4 2）将视图stuv4删除。 T-SQL语句： drop view stuv4

（5）管理视图中的数据

1）从视图stuview2查询出姓名为“李勇”的学生资料。 T-SQL语句：

select * from stuview2 where Sname='李勇'

2）向视图stuview2中插入一行数据。学号：200200001，姓名：赵军，性别：男，年龄：20，所在系：MA。

T-SQL语句：

insert into stuview2

values ('200200001','赵军','男',20,'MA')

原student表中的内容有何变化？ 成功添加到Student表中。

思考：如向视图stuview2中插入一行数据。学号：200200002，姓名：赵静，性别：女，年龄：20，所在系：MA。会出现什么样的结果？

insert into stuview2

values ('200200002','赵静','女',20,'MA')

原Student表中的内容有何变化？ 因为有with check option的约束条件，而该操作不符合此约束，所以插入不成功。

3）修改视图stuview2中的数据。

将stuview2中姓名为“赵军”同学的所在系改为“IS”。 T-SQL语句：

update stuview2 set Sdept='IS'

30

where Sname='赵军'

原student表中的内容有何变化？

4）从视图中stuview2将姓名为“赵军”同学删除。 T-SQL语句：

delete from stuview2 where Sname='赵军'

原student表中的内容有何变化？

四、实验要求

（1）硬件设备：奔腾II或奔腾II以上计算机，局域网。 （2）软件环境：WINDOWS 9X/NT、WINDOWS SERVER、WINDOWS XP、SQL SERVER 2000中文版企业版或标准版。

（3）实验课前预习，课后及时完成实验内容。

（4）实验过程及记录按题目格式要求填写在代码清单中。

五、代码清单

31

32

六、实验中出现的问题及解决的方法

33

七、思考讨论题或体会或对改进实验的建议

34

实验四 T-SQL程序设计

一、实验学时：2学时 二、实验目的

（1）掌握查询的概念和方法。 （2）掌握查询分析器的使用方法。

（3）熟练掌握单表查询的select语句。 （4）熟练掌握聚合函数的使用。

（5）熟练掌握复杂查询的select语句。 （6）熟练掌握连接查询方法。 （7）熟练掌握嵌套查询方法。

三、实验内容

（一）单表查询 1．基本查询

（1）查询学生表中全体学生的所有信息。 T-SQL语句：

select * from Student

（2）检索全体学生的学号、姓名。 T-SQL语句：

select Sno,Sname from Student 2．查询时改变列标题的显示

检索全体学生的学号、姓名、性别信息，并分别加上“学号”、“姓名”、“性别”的别名信息。

select Sno '学号',Sname '姓名',Ssex '性别' from Student 3．条件查询

（1）查询成绩大于90分的学生的学号及课程号、成绩。 T-SQL语句：

select Sno,Cno,Grade from SC where Grade>90

（2）查询成绩介于85~90分的学生的学号及课程号、成绩。 T-SQL语句：

select Sno,Cno,Grade from SC where Grade>=85 and Grade<=90

（3）查询选修了课程号为“2”，且成绩大于88的学生的学号。 T-SQL语句：

35

select Sno from SC

where Cno='2' and Grade>88

4．基于IN子句的数据查询

从课程表中查询出“数学”、“PASCAL语言”的所有信息。 T-SQL语句：

select * from Course

where Cname IN('数学','PASCAL语言')

5．基于Like子句的查询

（1）从学生表中分别检索出姓张的所有同学的资料；检索名字的第二个字是“红”或“虹”的所有同学的资料。

T-SQL语句：

select * from Student where Sname Like '张%' select * from Student where Sname Like '_[红,虹]%'

（2）如果用户查询的匹配字符串本身就含有%或_，比如在课程表中，添加一门课程为Visual_Basic，其它相关信息自取。若要查询名为Visual_Basic的课程的学分，则T-SQL语句为：

（提示：使用ESCAPE短语）

insert into Course

values ('8','Visual_Basic',NULL,3)

select * from Course where Cname Like 'Visual/_Basic' escape '/'

6．使用top关键字查询

分别从课程表中检索出前5个的课程信息。 T-SQL语句：

select top 5 * from Course 7．消除重复行

检索出学生已选课程的课程号，要求显示的课程号不重复。 T-SQL语句：

select distinct Cno from SC 8．查询经过计算的值。

查询全体学生的姓名及其出生年份，要求为计算值取别名“出生年份”。（提示：当前的年份减去年龄即为出生年份。）

T-SQL语句：

select Sname '姓名',year(getdate())-Sage '出生年份' from Student 9．使用ORDER BY语句对查询的结果进行排序

升序的关键字是ASC，降序的关键字是DESC，系统默认的是升序排列。 显示所有学生的基本信息，并按学生的年龄的升序排列。 T-SQL语句：

select * from Student order by Sage 10．使用Group子句进行查询

如果要在数据检索时对表中数据按照一定条件进行分组汇总或求平均值，就要使用Group BY子句并使用集合函数。

（1）汇总总分大于200分的学生的学号及总成绩。 T-SQL语句：

select Sno,sum(Grade) '总成绩' from SC

36

group by Sno having sum(Grade)>200

（2）查询各个课程号相应的选课人数。 T-SQL语句：

select Cno,count(*) '选课人数' from SC group by Cno

11．使用聚合函数

（1）查询学生总人数。 T-SQL语句：

select count(*) '总人数' from Student

（2）计算“2”号课程的学生平均成绩、最高分、最低分。 T-SQL语句：

select Cno,avg(Grade) '平均成绩',max(Grade) '最高分',min(Grade)分' from SC

group by Cno having Cno='2'

（二）复杂查询

1．查询比“王敏”年纪大的男学生信息。 T-SQL语句：

select * from Student where Ssex='男' and Sage>( select Sage from Student where Sname='王敏')

2．检索所有学生的选课信息。（提示：使用外连接） T-SQL语句：

select Student.Sno,Sname,Cno,Grade from Student left outer join SC on Student.Sno=SC.Sno

3．查询已选课学生的学号、姓名、课程名、成绩。（提示：连接查询） T-SQL语句：

select Student.Sno,Sname,Cname,Grade from Student,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno

4．查询选修了“信息系统”的学生的学号和姓名。 T-SQL语句：

select SC.Sno,Sname from Student,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Cname='信息系统'

5．查询与“刘晨”在同一个系的学生学号、姓名、性别。 子查询T-SQL语句：

select Sno,Sname,Ssex from Student where Sdept=( select Sdept from Student where Sname='刘晨') and Sname!='刘晨'

连接查询T-SQL语句：

select a.Sno,a.Sname,a.Ssex from Student a,Student b

where a.Sdept=b.Sdept and b.Sname='刘晨' and a.Sname!='刘晨'

6．查询其他系中比计算机科学系任一学生年龄大的学生的学号、姓名。 带有ANY或ALL谓词的子查询语句：

select Sno,Sname from Student where Sage>ANY( select Sage from Student where Sdept='CS')

37

最低 '

and Sdept!='CS'

用聚合函数实现：

select Sno,Sname from Student where Sage>( select min(Sage) from Student where Sdept='CS') and Sdept!='CS'

7．检索学生的学号、姓名、学习课程名及课程成绩。 T-SQL语句：

8．检索选修3门以上课程的学生的学号、总成绩。 T-SQL语句：

select Sno,sum(Grade) '总成绩' from SC group by Sno having Count(*)>3

9．检索多于2名学生选修的课程号及平均成绩。 T-SQL语句：

select Cno,avg(Grade) '平均成绩' from SC group by Cno having Count(*)>2

（三）流程控制语句

1．如果Student表中有20岁的学生，把该学生的学号，姓名和性别查询出来，否则输出“没有20岁的学生”。写出T-SQL语句：（使用if...else语句）

if exists(select * from Student where Sage=20) select Sno,Sname,Sage from Student where Sage=20 else

print '没有20岁的学生！'

2．使用While语句求1到100之间的累加和，输出结果。写出T-SQL语句：

declare @sum int,@count int select @sum=0,@count=1 while @count<=100 begin

set @sum=@sum+@count set @count=@count+1 end

print @sum

（四）用户自定义函数的应用

定义一个用户自定义函数Score_Rechange,将成绩从百分制转化为五级记分制。将该用户定义函数用在查询每个学生的成绩中，给出五级记分制的成绩。写出T-SQL语句：

CREATE FUNCTION Score_ReChange(@score tinyint) RETURNS char(10) AS BEGIN RETURN CASE

WHEN @score>=90 THEN '优秀'

38

WHEN @score>=80 AND @score<90 THEN '良好' WHEN @score>=70 AND @score<80 THEN '中等' WHEN @score>=60 AND @score<70 THEN '及格' ELSE '不及格' END End

执行自定义函数：

SELECT Student.Sno,Sname,dbo.Score_ReChange(Grade) '五级成绩' FROM Student,SC

WHERE Student.Sno=SC.Sno

四、实验要求

（1）硬件设备：奔腾II或奔腾II以上计算机，局域网。 （2）软件环境：WINDOWS 9X/NT、WINDOWS SERVER、WINDOWS XP、SQL SERVER 2000中文版企业版或标准版。

（3）实验课前预习，课后及时完成实验内容。

（4）实验过程及记录按题目格式要求填写在代码清单中。

五、代码清单

39

40

六、实验中出现的问题及解决的方法

41

七、思考讨论题或体会或对改进实验的建议

42

实验五 数据库安全性

一、实验学时：2学时 二、实验目的

（1）理解数据库安全的概念。

（2）掌握使用企业管理器维护数据库安全的方法。 （3）掌握T-SQL语句创建与维护数据库安全的方法。

三、实验内容

1．通过企业管理器

（1）注册一个“登录”（loginin） 登录名为自己的学号，并将该登录加入服务器角色“system administrators”：

再注册一个“登录”（loginin），登录名为自己的姓名，并且将该登录不属于任何服务器角色：

（2）在Stu_Course数据库下创建一个用户，用户名为自己的学号，并将它和登录名为自己学号的登录连在一起，察看该用户属于哪个数据库角色：

修改该用户的属性，在类似于下图的窗口中单击“权限”按钮，观察该用户对数据库中各对象的操作权限：

43

（3）在自己创建的数据库下创建一个用户，用户名为自己的姓名，并将它和登录名为自己姓名的登录连在一起，察看该用户属于哪个数据库角色：

编辑修改该用户属性（如上图），单击“权限”按钮，出现下图：

44

为该用户分配数据库中各对象的操作权限，要分配到列：

（4）编辑当前登录企业管理的注册属性，用SQL SERVER身份验证：

（5）断开原来的连接，用学号登录企业管理器，进入学生成绩数据库，测试用学号登录后，是否拥有对数据库的全部操作权限（注意：该登录属于system

45

administrators组）：

（6）断开原来的连接，用姓名登录企业管理器，进入学生成绩数据库，测试用姓名登录后，拥有对数据库的哪些操作权限：

2．用SQL语句完成上述过程：

Use Stu_Course

Exec sp_addlogin '000000'

Exec sp_addsrvrolemember '000000','sysadmin' Go

Exec sp_addlogin '某某' Go

Exec sp_grantdbaccess '000000','000000' Go

Exec sp_grantdbaccess '某某','某某' Go

GRANT SELECT(Cname,Ccredit) ON Course TO '某某' Go

--撤销所有用户上述权利，删除登录帐号

REVOKE SELECT(Cname,Ccredit) ON Course TO '某某' Exec sp_revokedbaccess '某某' Exec sp_revokedbaccess '000000' Exec sp_droplogin '某某' Exec sp_droplogin '000000' Go

3．总结登录、用户、服务器角色、数据库角色之间的关系及权限：

四、实验要求

（1）硬件设备：奔腾II或奔腾II以上计算机，局域网。

46

（2）软件环境：WINDOWS 9X/NT、WINDOWS SERVER、WINDOWS XP、SQL SERVER 2000中文版企业版或标准版。

（3）实验课前预习，课后及时完成实验内容。

（4）实验过程及记录按题目格式要求填写在代码清单中。

五、代码清单

47

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