数据库系统原理实验

Hao 实验一 认识数据库管理系统(2学时)

【实验目的】

1. 了解关系型数据库；了解实用数据库管理系统SQL Server 2000的架构。 2. 了解表结构及SQL Server2000的基本数据类型。

3. 明确本课程的实验任务总目标和各子任务目标及实验任务进度。 【实验要求】

1.了解安装SQL Server 2000的软硬件要求及安装过程。 2. 掌握SQL Server 2000企业管理器和查询分析器的使用。

【实验内容】

1. 选择一个实用的DBMS系统（SQL Server 2000），完成该系统的安装、启动和登录。 2. 分别使用SQL Server 2000企业管理器和SQL语句，完成学生管理系统数据库的创建、数据库属性的修改操作。

（1）创建一个数据库，要求如下。 ? 数据库名“stu_DB”。

? 数据库中包含一个数据文件，逻辑文件名为stu_DB_data，磁盘文件名为

stu_DB_data.mdf，文件初始容量为10MB，最大容量为100MB，文件容量递增值为5％。

? 事务日志文件，逻辑文件名为stu_DB_log，磁盘文件名为stu_DB_log.ldf，文件初始

容量为5MB，最大容量为30MB，文件容量递增值为2MB。 （2）对该数据库做如下修改。

? 添加一个数据文件．逻辑文件名为stu_DB2_data，磁盘文件名为stu_DB2_data.ndf，文件初始容量为5MB，最大容量为50MB，文件容量递增值为5MB。 ? 将日志文件的最大容量增加为50MB．递增值改为3MB。 【实验步骤】

一、SQL Server的安装

1．根据安装机器软硬件的要求，选择一个合适的版本，以下以开发版为例。 2．将 SQL Server 2000 DVD 插入 DVD 驱动器。

3．在自动运行的对话框中，单击“运行 SQL Server 安装向导”。

4．在“最终用户许可协议”页上，阅读许可协议，再选中相应的复选框以接受许可条款和条件。接受许可协议后即可激活“下一步”按钮。若要继续，请单击“下一步” 若要结束安装程序，请单击“取消”。

5．在“SQL Server 组件更新”页上，安装程序将安装 SQL Server 2000 的必需软件。有关组件要求的详细信息，请单击该页底部的“帮助”按钮。若要开始执行组件更新，请单击“安装”。更新完成之后若要继续，请单击“完成”。

6．在 SQL Server 安装向导的“欢迎”页上，单击“下一步”以继续安装。

7．在“系统配置检查 (SCC)”页上，将扫描安装计算机，以检查是否存在可能妨碍安装程序的条件。

8．在“注册信息”页上的“姓名”和“公司”文本框中，输入相应的信息。若要继续，请单击“下一步”。

9．在“要安装的组件”页上，请选择要安装的组件。选择各个组件组时，“要安装的组件”

1

窗格中会显示相应的说明。您可以选中任意一些复选框。建议全选。若要安装单个组件，请单击“高级”。否则，请单击“下一步”继续。

10．在“实例名”页上，请为安装的软件选择默认实例或已命名的实例。计算机上必须没有默认实例，才可以安装新的默认实例。若要安装新的命名实例，请单击“命名实例”，然后在提供的空白处键入一个唯一的实例名。

11．在“服务帐户”页上，为 SQL Server 服务帐户指定用户名、密码和域名。您可以对所有服务使用一个帐户。

12．在“身份验证模式”页上，选择要用于 SQL Server 安装的身份验证模式。如果选择 Windows 身份验证，安装程序会创建一个 sa 帐户，该帐户在默认情况下是被禁用的。选择“混合模式身份验证”时，请输入并确认系统管理员 (sa) 登录名。建议选择混合模式，并输入安全的密码。

13．如果选择 Reporting Services 作为要安装的功能，将显示“报表服务器安装选项”页。使用单选按钮选择是否使用默认值配置报表服务器。如果没有满足在默认配置中安装 Reporting Services 的要求，则必须选择“安装但不配置服务器”安装选项。若要继续安装，请单击“下一步”。

14．在“错误报告”页上，可以清除复选框以禁用错误报告。有关错误报告功能的详细信息，请单击该页底部的“帮助”。若要继续安装，请单击“下一步”。

15．在“准备安装”页上，查看要安装的 SQL Server 功能和组件的摘要。若要继续安装，请单击“安装”。

16．在“安装进度”页上，可以在安装过程中监视安装进度。若要在安装期间查看某个组件的日志文件，请单击“安装进度”页上的产品或状态名称。

17．在“完成 Microsoft SQL Server 安装向导”页上，可以通过单击此页上提供的链接查看安装摘要日志。若要退出 SQL Server 安装向导，请单击“完成”。

18．如果提示您重新启动计算机，请立即重新启动。

19．如果成功安装了SQL Server 2000，则在开始菜单中添加了如下程序和相应的服务。

【注意】详细安装注意事项，参见微软网站知识库文章：

http://support.microsoft.com/default.aspx?scid=kb;zh-cn;257716 二、创建数据库

在SQL Server2000 下创建数据库有两种方法：

1．使用企业管理器直接创建数据库 （1）打开企业管理器。

（2）左键双击“数据库”后，右键单击空白区并选择【新建数据库】。 （3）会弹出窗体如图1-1所示，在常规标签中输入数据库名称。

2

图1-1 新建数据库

（4）然后单击【数据文件】标签，可以输入文件名、文件位置、文件大小以及文件组。在这个对话框中还提供了在数据文件需要更多空间时自动扩展的选项以及数据文件大小的最大值。

（5）单击【事务日志】标签，在此键入日志文件的名字、位置、大小。在这个标签中还提供了让日志文件在日志需要更多空间时自动扩展选项和日志文件大小的最大值。

2．使用SQL语句创建数据库

使用SQL语句创建数据库的语法如下： CREATE DATABASE database_name [ ON

[PRIMARY]

( [NAME = logical_file_name,] FILENAME = ?os_file_name? [,SIZE = size]

[,MAXSIZE = {max_size|UNLIMITED}] [,FILEGROWTH = growth_increment])[,…n] ::=

FILEGROUP filegroup_name [,…n] LOG ON

(NAME = logical_file_name, FILENAME = ?os_file_name? SIZE = size,

MAXSIZE = {max_size | UNLIMITED}, FILEGROWTH = growth_increment)]

创建一个STU_DB数据库，其中主文件组包含主要数据文件STU_DB1_dat和次要数据文件STU_DB2_dat。

有2个次要文件组：STU_DBGroup1包含2个次要数据文件STU_DB1Fi1_dat 和 STU_DBFi2_dat, STU_DBGroup2包含STU_DB2Fi1_dat和STU_DB2Fi2_dat两个次要数据文件。

日志的逻辑文件名为STU_DB_log，此日志文件存储在c:\\mssql\\data\\STU_DBlog.ldf

3

中。

CREATE DATABASE STU_DB

ON PRIMARY

( NAME = STU_DB1_dat,

FILENAME = 'F:\\STU_DB1_dat.mdf',

SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15%),

(NAME = STU_DB2_dat,

FILENAME = 'F:\\STU_DB2_dat.ndf',

SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15%),

FILEGROUP STU_DBGroup1

( NAME = stu_DB1Fi1_dat,

FILENAME = 'F:\\stu_DB1Fildt.ndf',

SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5) ,

( NAME = stu_DB1Fi2_dat,

FILENAME = 'F:\\stu_DB2Fildt.ndf',

SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5),

FILEGROUP stu_DBGroup2

(NAME = stu_DB2Fil_dat,

FILENAME = 'F:\\SG2Fi1dt.ndf',

SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5),

(NAME = stu_DB2Fi2_dat,

FILENAME = 'F:\\SG2Fi2dt.ndf',

SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5)

LOG ON

(NAME = 'stu_DB_log',

FILENAME = 'F:\\stu_DBlog.ldf',

SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB)

三、查看、修改数据库属性

4

1．利用企业管理器

（1）SQL Server2000 有许多选项可以改变数据库的行为。因此，在使用数据库之前，可以考虑其中一些选项的设置。

进入企业管理器，用鼠标右键单击数据库stu_DB，再单击“属性”，然后选择“选项”标签即可进入如图1-2所示stu_DB数据库的选项窗体。

图1-2 stu_DB数据库的选项窗体

属性说明如下：

? 访问控制：用于控制哪些用户能访问该数据库。 ? 只读：这个选项使数据库只能读取不能写入。 ? 模型：指定数据库的恢复模型类型。

? ANSI NULL默认设置：指定将数据库列默认定义为NULL还是NOT NULL。 ? 递归触发器：启用触发器的递归触发。

? 自动更新统计信息：指定在优化期间自动生成查询优化所需的过时统计信息。 ? 残缺页检测：指定可以检测不完整页。

? 自动关闭：指定数据库资源被释放以及所有用户退出之后关闭数据库。 ? 自动收缩：指定数据库文件可以周期性自动收缩。

? 自动创建统计信息：指定在优化期间自动生成优化查询所需的任何缺少的统计信

息。

? 使用引用的标识符：指定SQL Server2000将强制执行关于引号的ANSI规则。 ? 级别：指定数据库兼容性级别。

（2）调整数据库大小

点击“数据文件”或“事物日志”标签，可以得到如图1-3所示stu_DB数据库的窗体。

5

图1-3 stu_DB数据库的数据文件窗体

属性说明：

? 文件自动增长：选中表示数据库自动，这时可以选择按兆增长还是按百分比增长。 ? 文件增长不受限制：意味着数据库没有最大限制，可以一直充满磁盘 ? 将文件增长限制为，为数据库选择一个最大限制。 ? 分配空间：用户可以直接增加数据库文件大小 ? 扩大数据库的另外一个方法是增加辅助数据文件。

（3）加文件组

生成一辅助文件后，可将其逻辑组织到文件组中，帮助管理磁盘空间分配。增加文件组的方法是：在文件组列中输入一个新的文件组名称，如“next”，单击“确定”，然后右击数据库，选择“属性”，选择“文件组”标签会看到增加的文件组。如图1-4所示：

6

图1-4 stu_DB数据库的文件组窗体

（4）缩小数据文件

右击数据库，单击“所有任务”，选择收缩数据库，会弹出图1-5所示窗体，利用该窗体可以输入数据库大小，并且可以定制任务，让SQL Server2000自动定期收缩数据库大小。

图1-5 收缩数据库窗体

2．使用SQL语句查看、修改数据库属性

使用Transact-SQL语句查看、修改数据库属性的语法为： sp_dboption [[@dbname = ] ?database?]

[,[@optname = ] ?option_name?] [,[@optname = ] ?option_name? ]

7

[@dbname = ] ?database? 设置指定选项的数据库名称

[@optname = ] ?option_name? 设置的选项名称

[@optname = ] ?option_name? 可以示ture、false、on或off

⑴ 修改日志文件的最大值

将stu_DB1_log日志文件的最大值更改为150M，SQL脚本程序如下： USE stu_DB GO

ALTER DATABASE stu_DB MODIFY FILE(NAME=stu_DB1_log,MAXSIZE=150MB) GO

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

将stu_DB1_log日志文件的初始值由5M更改为20M，SQL脚本程序如下： USE stu_DB GO

ALTER DATABASE stu_DB MODIFY FILE(NAME=stu_DB1_log,SIZE=20MB) GO

（3）查看数据库信息

执行系统存储过程sp_helpdb,可以查看stu_DB数据库信息，并验证数据库是否已经修改成功。

Sp_helpdb stu_DB

（4）更改数据库

更改数据库stu_DB时为stu_DB添加一个辅助文件，文件名为stu_DBfz，存储在c:\\ stu_DBfz.ndf中，初始大小为3MB，最大值为50MB，增长量为3MB。

USE stu_DB

ALTER DATABASE stu_DB ADD FILE

(NAME = stu_DBfz,

FILENAME='f:\\stu_DBfz.ndf', SIZE=3MB, MAXSIZE=50, FILEGROWTH=3)

? 向数据库stu_DB中添加一个含有2个文件的文件组 USE stu_DB GO

ALTER DATABASE stu_DB ADD FILEGROUP stu_DBfg1 GO

ALTER DATABASE stu_DB ADD FILE

(NAME=stu_DBfz1,

FILENAME='C:\\stu_DBfz1.ndf', SIZE =5MB,

MAXSIZE=50MB, FILEGROWTH =5), (NAME=stu_DBfz2,

FILENAME='C:\\stu_DBfz2.ndf', SIZE =5MB,

MAXSIZE=50MB, FILEGROWTH =5)

8

TO FILEGROUP stu_DBfg1

? 向stu_DB数据库中添加2个5MB的日志文件 USE stu_DB GO

ALTER DATABASE stu_DB ADD LOG FILE

(NAME =stu_DBlog2,

FILENAME='c:\\stu_DBlog2.ldf', SIZE =5MB,

MAXSIZE = 10MB, FILEGROWTH =5), (NAME =stu_DBlog3,

FILENAME='c:\\stu_DBlog3.ldf', SIZE =5MB,

MAXSIZE = 10MB, FILEGROWTH =5)

? 删除数据库stu_DB中的一个文件stu_DBfz.ndf USE stu_DB GO

ALTER DATABASE stu_DB REMOVE FILE stu_DBfz DROP DATABASE stu_DB --删除stu_DB数据库

【实验报告】

1.写出SQL Server 2000的功能与特点, 使用方法, 应用状况的报告。 2.记录实验过程中所出现的问题及解决方案。 【思考题】

1.SQL Server 2000安装过程中应该注意哪些事项？

2.SQL Serve中数据库由哪两类文件组成？这些文件的扩展名分别是什么？ 3.数据文件和日志文件的作用是什么？

9

实验二 SQL语言的基础应用(6学时)

【实验目的】

了解SQL语言的使用，进一步理解关系运算，巩固数据库的基础知识。

【实验要求】

利用SQL语言进行数据库表的各种操作： 1．数据库表的创建、修改和删除操作。

2．向表中进行数据的插入、删除和修改操作。

3. 掌握利用Select语句进行各种查询操作：单表查询、多表连接及查询、嵌套查询、集合查询等。 【实验内容】

1. 利用数据定义语句完成学生管理系统中有关表的创建、修改和删除操作。

2．利用INSERT、UPDATE和DELETE语句对学生管理系统中的表中的数据进行插入、更新和删除操作。

3．利用SELECT语句对学生管理系统中的表中的数据进行各种查询操作。 4. 使用SQL Server 2000企业管理器完成上述操作。 【实验步骤】 一、数据定义

(一)表的创建

在实验一创建的数据库stu_DB中分别用企业管理器和查询分析器，按下面的表结构创建学生管理系统的表。 表2-1 Student表结构

列名

Sno Sname Ssex Sage Sdept

说明

学号 姓名 性别 年龄 所在系

数据类型

字符串，长度为8 字符串，长度为10 字符串，长度为2

整数

字符串，长度为20

约束

主码 非空 ’男’或 ’女’

15~45

默认为’计算机系’

表2-2 Course表结构

列名

Cno Cname Cpno Ccredit Semster Period

说明

课程号 课程名 先修课程号 学分 学期 学时

数据类型

字符串，长度为8 字符串，长度为20 字符串，长度为8

整数 整数

字符串，长度为20

约束

主码 非空 取值：1~6 取值：1~8 取值：≥0

表2-3 SC表结构

列名

Sno Cno

说明

学号 课程号

数据类型

字符串，长度为8 字符串，长度为8

10

约束

主码，引用student的外码 主码，引用course的外码

Grade 成绩 整数 取值：0~100

1．利用企业管理器创建表 （1）打开企业管理器。

（2）选中实验一创建好的数据库stu_DB，单击数据库中的表对象，然后右击窗口右侧选择新建表，弹出如图所示窗体。

图2-1 新建表窗体

（3）在这个窗体中，列名列就示表的字段名，可以在这个窗体中为字段选择数据类型和长度以及是否可以为空值。

（4）在此处可以为表选择文件组，在表中单击右键，选择属性，如果已经为数据库创建了一个次要组，可以在属性窗体的表文件组中为此表选择文件组。应该把一些竞争使用的表放于不同的文件组中，并且让文件组属于不同磁盘，这样可以在表竞争读写时提高并发性能。

（5）创建完成后，单击磁盘图标，并为表取一个名字。注意，应该为表取一个有意义的名字。

2．用Transact_SQL语句创建表 语法为：

CREATE TABLE

[ database_name.[owner].] table_name

( {column_name data_type [DEFAULT ?default_value?]| [CONSTRAINT CONSTRAINT_name]

}, […n] [IDENTITY [(seed, increment )]] )

[ON { filegroup | DEFAULT }]

[TEXTIMAGE_ON {filegroup | DEFAULT }]

【例1】生成一个表名为student的表 create table student (

sno CHAR(8) primary key, sname CHAR(10) not null,

ssex CHAR (2) check (ssex='男'or ssex='女'),

11

sage smallint,

sdept CHAR (20) default ('计算机系'), )

图2-2查询分析器窗体——创建表

【例2】 创建一张名为Ta1的表，此表中有3列，第一列Pid定义为主键，并且自动增长。第二列Name默认值为Unknown，第三列定义一个约束(日期不能大于输入当天的日期)，插入一条记录并进行查询，显示如下图。

CREATE TABLE ta1 (

pid int identity(1,1) primary key, [name] CHAR(10) default ('unknow'),

birthday datetime CHECK( birthday

注意该列有什么特殊性？

图2-3 查询分析器窗体——创建表

请参考例1和例2，完成以下操作：

(1)按前面所给的表结构创建表Course和SC。

(2)自行设计表的结构，用SQL语句建立第二章习题5中的4个表。

(二)表的修改

1． 用企业管理器修改表

12

⑴ 右键点击所要修改的学生表，选择“设计表”，会弹出如图2-4所示的窗体，在这个窗体中可以更改数据表的字段。在空白处点右键，选择“属性”，弹出另一窗体，在此可以更改约束，也可以增加约束。

图2-4 修改表窗体

（2）利用Transact-SQL语句修改表

? 向表中添加新的字段：在学生表中添加一个“班级”字段，数据类型为字符型。

ALTER TABLE student ADD class CHAR(6)

? 删除表中的旧列：将学生表中的“Sdept”字段删除。

ALTER TABLE student DROP COLUMN Sdept

? 更改表以添加具有约束的列。

给学生表的增加“grade”字段并加上CHECK约束，让其不可以大于100。

ALTER TABLE student ADD grade int CONSTRAINT ch_grade CHECK(grade<100) EXEC sp_help ch_grade

给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。 ALTER TABLE student ADD birthday DATETIME NULL CONSTRAINT ch_birthday CHECK(birthday

? 添加具有默认值的可为空的列：

在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。 ALTER TABLE student ADD matriculationday smalldatetime NULL CONSTRAINT adddateflt DEFAULT getdate()

请参考上面所给的例子：

13

(1) 自行设计表结构修改操作（不少于8个语句），并进行实验验证。 (2) 修改表student，结果如表2-4。

(3) 创建表2-5、表2-6，为后续实验建立基础。 表2-4 更新后的student表结构

列名

说明

Sno 学号 Sname 姓名 Ssex 性别 Sdept 所在系 Sbirth 出生日期 sclass 班级 sremark 评语 saddress 家庭住址 szipcode 邮编 sphone 电话 semail

电子邮箱

表2-5 Teacher表结构

列名

说明

tno 工号 tname 姓名 tsex 性别 tdept 所在系 tbirth 出生日期 tposition 职称 tsalary 工资 texperience 简历 tamount 工作量 tphone 电话 temail 电子邮箱 taddress

家庭住址

表2-6 TC表结构

列名

说明

Tno 工号 Cno 课程号 Classroom

教室

(三)表的删除

数据类型

字符串，长度为8 字符串，长度为10 字符串，长度为2 字符串，长度为20

日期

字符串，长度为20 变长字符串，长度为100 变长字符串，长度为40 字符串，长度为6 字符串，长度为15 变长字符串，长度为40

数据类型

字符串，长度为8 字符串，长度为10 字符串，长度为2 字符串，长度为20

日期

字符串，长度为12

整数

变长字符串，长度为200

整数

字符串，长度为15 变长字符串，长度为40 变长字符串，长度为40

数据类型

字符串，长度为8 字符串，长度为8 变长字符串，长度为30

14

约束

主码 非空 ’男’或 ’女’ 默认为’计算机系’

自定义

约束

主码 非空 ’男’或 ’女’ 默认为’计算机系’

自定义

取值：0~10000

取值：0~1000

约束

主码，引用Teacher的外码主码，引用Course的外码

可以在企业管理器中选择要删除的表直接删除，也可以通过Transact-SQL语句DROP 删除表的定义及表中的所有数据、索引、出发器、约束和权限规范。

如要删除ta1表，语句如下： DROP TABLE ta1 请参考上面的例子完成对表Student 、Course 、SC的删除操作，它们删除的次序如何？并进行实验验证。 二、数据操纵

（一）查看、重命名及删除用户定义的数据类型

1．使用企业管理器创建一个名为newtype1、长度为6、可变长字符、允许为空的自定义数据类型。

进入企业管理器，进入stu_DB数据库，用鼠标右键单击“用户定义的数据类型”后选择“操作”菜单中的“新建用户定义数据类型”或点击“新建”按扭，将弹出下图所示窗体：

图2-4 数据类型定义窗体

输入要定义的数据名称newtype1，选择数据类型varCHAR，输入长度为6，在“允许NULL值”复选框中打个“√”，单击“确定”按钮即可。

2.使用T-SQL语句创建一个名为newtype2，数据长度为6，定长字符型，不允许为空的自定义数据类型。

USE stu_DB

EXEC sp_addtype newtype2, ?CHAR(6)?, ?not null? 3.命名用户自定义的数据类型 ⑴ 使用系统存储过程sp_rename将自定义数据类型newtype1重新命名为a1。 EXEC sp_rename newtype1, a1

⑵ 使用企业管理器再将自定义数据类型a1重新命名为newtype1。 4．删除用户自定义数据类型 ⑴ 使用系统存储过程sp_droptype来删除用户自定义的数据类型。 EXEC sp_droptype newtype1

⑵ 使用企业管理器删除用户自定义数据类型

15

进入企业管理器，进入stu_DB数据库，单击“用户定义的数据类型”后，右边窗口将出现所有用户定义的数据类型，再用鼠标右键单击newtype2，在弹出的快捷菜单上单击“删除”按钮即可。

注意：正在被表或其他数据库对象使用的用户定义类型能不能删除？请进行实验验证。

（二）数据的更新

1. 利用企业管理器更新数据

打开企业管理器，右键点击所要修改的表，选择“打开表”，单击“返回所有行”，会弹出如图2-5所示的窗体，在该窗体中可以修改表中的数据内容。这里的修改内容是自动存储的，修改完之后，直接关闭该窗体，数据内容即修改成功。

图2-5数据修改窗体

2.利用查询分析器更新数据

在关系数据库中，常见的数据更新的语句有三条：INSERT、UPDATE和DELETE ⑴INSERT语句

向student表中插入一行数据，具体数据如下：

学号：04265005, 姓名：刘辉, 性别：男,年龄：21, 系别：计算机系 USE stu_DB

INSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) VALUES (?04265005?, ?刘辉?, ?男?, 21, ?计算机系?) INSERT INTO student (Sno, Sname, Ssex, Sage) VALUES (?04265006?, ?李慧?, ?女?, 21)

向ta1表中插入数据如下：

INSERT ta1([name],birthday) values (?lan?,?1977-03-02?) INSERT ta1 values (?lan?,?1977-03-02?) INSERT ta1(birthday) values (?1977-03-02?)

请根据上述例子，自行设计不同情况下的数据插入语句（不少于10个语句），并进行实验验证，验证要求如下：

? ? ? ? ?

不满足唯一性约束的数据的插入。 不满足用户自定义约束的数据的插入。 不满足外键约束的数据的插入。 利用缺省值的数据的插入。

不合理的数据是否能进入数据库。

⑵UPDATE语句

更改（学生）表数据，将学号为?04265005?的学生的所在系改成?机械工程系?。 UPDATE student SET Sdept=?机械系? WHERE Sno=?04265005?

16

请根据上述例子，自行设计数据修改语句（不少于10个语句），并进行实验验证，验证要求同INSERT语句。

⑶DELETE语句删除行

删除student表中的学号为?04265005?的记录。 DELETE FROM student WHERE Sno =?04265005?

如果想清除表中的所有数据但不删除这个表，可以使用TRUNCATE TABLE语句。该语句相当于没有条件的DELETE语句，并且该语句不记录日志。

请根据上述例子，自行设计不同情况的数据删除语句（不少于10条语句），并进行实验验证不满足外键约束的数据删除操作。

三、数据查询

对数据定义中创建的学生管理系统（Student， Course，SC，Teacher，TC表）和SPJ系统中的每个表添加充足的数据(每个表不少于20条)，以便完成本实验的后继操作。具体表信息可参考如下：

Student表： Sno sname Sbirthday Sdept 04261001 1982.02.07 王冬梅 计算机系 ?? 04261002 1982.03.04 王田田 计算机系 ?? 04261003 1982.06.06 吴畏 计算机系 ?? 04261004 04261005 04262001 04262002 04262003 04561001 04563001 马丽 杨乾坤 刘乐 夏天 杨致远 陶然 姚远 1982.04.03 1982.03.23 1983.01.16 1983.02.08 1982.07.22 1982.01.12 1982.03.21 ?? Cpno C005 C001 C006 C007 C006 ?? Cno C001 C001 C001 Score 90 85 73 17

计算机系 计算机系 计算机系 计算机系 计算机系 数学系 数学系 ?? Credit ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? course表： Cno Cname C001 数据库原理 C002 高等数学 C003 管理信息系统 C004 操作系统 C005 数据结构 C006 计算机文化基础 C007 C语言程序设计 ?? ?? score表： Sno 04261001 04261002 04261003

3 4 2 3 3 2 3 ?? 04261004 04261005 04262001 04262002 04262003 04561001 04563001 ?? teacher表： Tno T002 T004 Tname 罗莉 吴栋 C001 C001 C001 C001 C001 C007 C007 ?? 98 96 97 83 85 92 97 ?? Tsex 女 女 男 男 男 男 Department 计算机系 计算机系 计算机系 计算机系 数学系 数学系 数学系 物电学院 ?? ?? ?? ?? ?? ?? ?? ?? ?? T001 石云丹 T003 王国强 T005 高鸿轩 T006 张怀良 T007 刘晓伟 男 T108 马莉莲 女 ?? ?? ?? TC表： Tno T001 T002 T003 T004 T005 T006 ??

（一）单表查询

cno C005 C007 C001 C006 C004 C002 ?? classroom 22号楼405 7号楼406 17号楼504 17号楼401 8号楼201 8号楼202 ??

1．简单查询

打开查询分析器，根建立teacher表，并加入数据。从teacher表中分别检索出教师的所有信息，以及仅查询教工号、姓名和职称。语句如下：

select * from teacher

select tno, tname from teacher

如要查询时改变列标题的显示，则从teacher表中分别检索出教师教工号、姓名、电子邮箱信息并分别加上?教师姓名?、?教工号?、?电子邮箱?等标题信息。

select tno 工号, tname 姓名, temail 电子邮箱 from teacher

使用TOP关键字：分别从teacher中检索出前2条及前面67%的教师的信息。 select top 2 * from teacher

select top 67 percent * from teacher 使用DISTINCT关键字：从teacher表中检索出教师的职称并且要求显示的职称不重复。select distinct tposition from teacher

18

2．用计算列：将teacher表中各教师的姓名、教工号及工资按95%发放的信息，第2条语句将工资按95%发放后列名该为?预发工资?。语句如下：

select tno tname ,tsalary*0.95 from teacher

select tno工号, tname姓名, tsalary*0.95 AS 预发工资 from teacher 3．使用ORDER BY子句对查询的结果进行排序

使用ORDER BY语句可以对查询的结果进行排序，ASC、DESC分别是升序和降序排列的关键字，系统默认的是升序排列。从teacher表中查询工资大于2800的教师的教工号、姓名，并按升序排列，语句如下：

select tno, tname from teacher

WHERE tsalary>2800 order by tsalary ASC 4．条件查询 ⑴ 使用关系运算符：从teacher表中查询出工作量大于288的教师资料，语句如下： select * from teacher

WHERE tamount>288 order by tamount DESC ⑵ 使用BETWEEN AND谓词：从teacher表中查询出工作量界于144和288之间的教师资料，语句如下：

select * from teacher WHERE tamount between 144 and 288 ⑶ 使用IN谓词：从teacher表中查询出职称为“教授”或“副教授”的教师的教工号、教师姓名、职称及家庭住址，语句如下：

select tno,tname,tposition, taddress from teacher WHERE tposition in ('教授','副教授') ⑷ 使用LIKE谓词：从teacher表中分别检索出姓?王?的教师的资料，或者姓名的第2个字是?莉?或?轩?的教师的资料，语句如下：

select * from teacher WHERE tname like '王%'

select * from teacher WHERE tname like '_[莉，轩]%' （二）多表查询

数据库各表中存放着不同的数据，用户经常需要用多个表中的数据来组合提炼出所需要的信息，如果一个查询需要对多个表进行操作，就称为关联查询，关联查询的结果集或结果表称为表之间的连接。关联查询实际上是通过各各表之间共同列的关联来查询数据的，它是关系数据库查询最基本的特征。

1．SQL 2000兼容2种连接形式：用于FROM子句的ANSI连接语法和用于WHERE子句的SQL SERVER连接语法形式。

从student、course和SC三张表中检索学生的学号、姓名、学习课程号、学习课程名及课程成绩，语句如下：

select student.sno, sname, cno, grade

from student inner join SC on student.sno=SC.sno

select student.sname,sc.grade from student,sc

WHERE student.sno=sc.sno

select student.sno,student.sname,sc.cno,course.cname,sc.grade from student,sc,course

WHERE student.sno=sc.sno and sc.cno=course.cno

２．使用UNION子句进行查询

使用UNION子句可以将一个或者多个表的某些数据类型相同的列显示在同一列上。如从teacher表中列出教工号、姓名并从student表中列出学号及学生姓名，语句及查询结果如

19

下：

select sno AS 学号或工号, sname AS 姓名 from student union

select tno, tname from teacher

3．用GROUP子句进行查询

如果要在数据检索时对表中数据按照一定条件进行分组汇总或求平均值，就要在SELECT语句中与GROUP BY子句一起使用集合函数。使用GROUP BY子句进行数据检索可得到数据分类的汇总统计、平均值或其他统计信息。

⑴ 使用不带HAVING的GROUP BY子句。

使用不带HAVING 的GROUP BY子句汇总出SC表中的学生的学号及总成绩，语句如下：

select '学号'=sno,'总成绩'=SUM(Grade) from SC group by Sno ⑵ 使用带HAVING 的GROUP BY子句。

使用带HAVING 的GROUP BY子句汇总出SC表中总分大于450分的学生的学号及总成绩，语句如下：

select '学号'=sno,'总成绩'=SUM(Grade) from SC group by Sno

Having SUM(Grade)>160

4．用COMPUTE和COMPUTE BY子句进行查询

使用COMPUTE和COMPUTE BY 既能浏览数据又能看到统计的结果。 ⑴ 用COMPUTE子句汇总出SC表中每个学生的学号及总成绩，语句如下： select '学号'=sno,'成绩'=Grade from SC order by sno COMPUTE SUM(Grade) ⑵ 用COMPUTE BY 子句按学号汇总出SC表中每个学生的学号及总成绩，语句如下： select '学号'=sno,'成绩'=Grade from SC

ORDER BY Sno COMPUTE SUM(Grade) by sno

观察执行COMPUTE和COMPUTE BY子句的结果有什么不同？

5．嵌套查询

(1) 使用IN或NOT IN关键字

使用IN关键字查询出j10011班所有男生的学号、课程号及相应的成绩，语句如下： select SC.sno , SC.cno,SC.grade FROM SC WHERE sno IN

( SELECT sno FROM student

WHERE Sclass=?j10011? AND Ssex='男')

使用IN关键字查询出与教师不同名的学生(学号，姓名)，语句如下： select sno 学号, sname 姓名 from student

where sname not in (select tname from teacher)

(2) 使用EXISTS 或NOT EXISTS关键字。使用EXISTS关键字查询出?j10011?班的学生的学号、课程号及相应的成绩，语句如下：

20

姓名 性别 出生日期 家庭地址 学号 学 生 班级 图4-2 学生实体E-R图

课 程 课程简介 课程编号 名 称 学时数 学 分 图4-3 课程实体E-R图

教师 教师号 姓名 性别 家庭住址 出生日期 图4-4 教师实体E-R图

选课 学号 课程编号 图4-5 选课实体E-R图

分数段 成绩

31

教课 教师号 课程编号 课程序号 图4-6 教课实体E-R图

学生转出 转出备注 原因 学号 时间 新转入的学校名 图4-7 学生转出实体E-R图

休学 备注 学号 时间 原因 图4-8 学生休学实体E-R图

复学 备注 学号 新学号 新的班级号 学期 时间 原因 图4-9学生复学实体E-R图

32

退学 备注 学号 时间 原因 图4-10 学生退学实体E-R图

4.学生根据以上的系统需求分析及E-R图设计，自行完成学生管理系统的数据库设计。在前面3个实验的基础上，完成学生管理系统表结构的建立，用SQL实现学生管理系统学生、老师、课程等表的相关操作。

5.学生自行选择熟悉的可视化程序设计语言编程实现学生管理系统。 6.准备测试数据，测试、调试系统，并完善。 7.撰写系统文档。 【思考题】

1. 在E-R图中实体间联系类型应如何表示？

2. 数据库设计时应如何避免数据冗余（实体集的冗余和属性冗余）？

3. 用关系数据库理论对自己设计的数据模型进行评价，属于哪种范式？指出合理和不足之处，提出改进的方案。

33

SELECT SC.sno，SC.cno，SC.grade FROM SC

WHERE EXISTS

（ SELECT * FROM student

WHERE SC.sno=student.sno AND student.sclass=?j10011? ）

请根据上述例子，可参考以下所给的操作要求自行设计不同情况的DML语句（不少于100条语句），并进行实验验证。

（1）将教师‘罗莉’的名字改为‘罗莉莉’。

（2）将两个同学（数据自己临时设置，用后即删除）的两门课程的成绩以运行sql程序文件的形式插入score表中。该题用以验证、理解和掌握关系模型的完整性规则；

（3）求每门课的平均成绩，并把结果存入average表（自行设计并创建）； （4）将学生“马丽”的出生日期改为“1982.8.20”； （5）将所有学生的zipcode属性列值填补上；

（6）将average表中的所有课程的平均成绩置零；

（7）删除average表中的课程号为‘c007’的平均成绩记录； （8）删除所有average表中平均成绩记录； （9）建立一个临时学生信息表（tstudent），删除该表中的学号含‘101’的所有学生记录。

（10）查询全体学生的学号与姓名；

（11）查询全体学生的学号、姓名、所属班级； （12）查询全体学生的详细记录； （13）查询全体学生的姓名及其年龄； （14）查询全体学生的姓名、出生年份； （15）查询所有修过课的学生的学号； （16）查询“计61”班全体学生名单；

（17）查询查询所有年龄在23岁以下的学生姓名及其年龄； （18）查询考试成绩有不及格的学生的学号；

（19）查询年龄在20至22岁之间的学生姓名、班级和年龄； （20）查询年龄不在20至22岁之间的学生姓名、班级和年龄； （21）查询“计11”和“计61”班的学生的姓名和班级信息；

（22）查询既不是“计11”也不是“计61”班的学生的姓名和班级信息； （23）查询学号为“04262002”的学生的详细情况； （24）查询学号以“04262”打头的学生信息；

（25）查询所有姓“张”学生的学号、姓名、性别、年龄；

（26）查询名字中第二个字有“海”字的学生的学号、姓名、性别、年龄； （27）查询所有不姓“刘”学生的姓名；

（28）查询课程号以“C”开头的最后两个字母为“05”的课程号和课程名；

（29）某些学生选修某门课程后没有参加考试，所以有选修课记录，但没有考试成绩，试查找缺少考试成绩的学生和相应的课程号；

（30）查找全部有成绩记录的学生学号、课程号；

（31）查找“计91”班年龄在22岁以下的学生学号、姓名；

（32）查找选修了“C001”号课程的学生学号及其成绩，查询结果按分数降序排序； （33）查询全体学生情况，查询结果按所在班级升序排列，对同一班级中的学生按年龄降序排列；

（34）查询学生总人数；

（35）查询选修了课程的学生人数；

（36）在所有课程中查询最高分的学生学号和成绩； （37）查询学习“C001”课程的学生最高分数； （38）计算各个课程号与相应的选课人数；

21

（39）查询“计91”班选修了两门课程以上的学生学号、姓名； （40）自然连接student和score表；

（41）使用自身连接查询每一门课程的间接先行课（即先行课的先行课）

（42）使用复合条件连接查询选修“c001”号课程且成绩在90分以上的所有同学； （43）使用复合条件连接查询每个学生选修的课程名及其成绩； （44）查询选修了全部课程的学生；

（45）查询至少选修课程C001的学生选修的全部课程的学号、姓名； （46）查询所有选修了C001号课程的学生学号、姓名； （47）查询选修了课程C001或c007的学生学号、姓名；

（48）查询“计91”班的学生及年龄不大于23岁（现有年龄）的学生； （49）查询既选修了课程C001又选修了课程c007的所有学生学号、姓名； （50）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄； （51）查询其他班中比“计91”班所有学生年龄都小的学生名单；

（52）查询与“夏天”在同一个班学习的学生学号、姓名、性别、年龄； （53）建立“计91”班学生的视图；

（54）建立“计911”班学生的视图，并要求进行修改与插入时，仍须保证该视图只有“计91”班学生；

（55）建立“计91”班选修了“C001”课程的学生的视图，定义视图名为“v_info_C001_student1”；

（56）建立“计91”班选修了“C001”课程且成绩在90分以上的学生的视图，定义视图名为“info_c001_student2”；

（57）定义一个反映学生年龄的视图，定义视图名为“v_birth_student”；

（58）将学生表中所有女生记录定义为一个视图，视图名为“v_female_student”； （59）将学生的学号及其平均成绩定义为一个视图，视图名为“v_average_student”； （60）删除表student后，重新建student表，再建视图“V_CS_student1”，结果如何？ （61）在“计91”班学生视图中找出年龄小于22岁（现在的年龄）的学生； （62）利用视图查询“计91”班选修了“C001”课程的学生； （63）通过（53）中的“计91”班视图修改某个学生的名字； （64）通过（54）中的“计91”班视图，插入一个新学生记录。 （65）通过（54）中的“计91”班视图，删除一个学生记录。 【思考题】

1. 如何提高数据查询和连接速度。

2. 对于常用的查询形式或查询结果，怎样处理好？

3..组合查询语句是否可以用其他语句代替，有什么不同？

4.使用 GROUP BY <分组条件>子句后，语句中的统计函数的运行结果有什么不同？ 5.数据库中一般不允许更改主码数据。如果需要更改主码数据时，怎样处理？ 6.为什么不能随意删除被参照表中的主码？

22

实验三 视图操作与安全性控制(2学时)

【实验目的】

1. 学会使用SQL语句创建和使用视图，加深对视图和SQL Server图表作用的理解。 2. 了解数据库安全控制的机制，以及基本的安全控制方法。 【实验要求】

1．掌握创建、修改视图的SQL语句的用法。

2．掌握使用企业管管理器创建、修改视图的方法。 3．掌握查看视图的系统存储过程的用法。 4．掌握视图的使用方法。

5．掌握SQL有关用户、角色及操作权限的管理方法。 【实验内容】 一、视图操作 （一）定义视图

以Student 、Course表为基础完成以下视图定义。 1.定义计算机系学生基本情况视图V_Computer；

2.将Student Course 和Student_course表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G

3.将各系学生人数，平均年龄定义为视图V_NUM_AVG 4.定义一个反映学生出生年份的视图V_YEAR

5.将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G 6.将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G （二）使用视图

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

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

3. 查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩。 4. 按系统计各系平均成绩在80分以上的人数，结果按降序排列。 （三）修改视图

1. 通过视图V_IS，分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,”S4_MMM” 并查询结果;

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

3. 通过视图V_IS，新增加一个学生记录 ('S13','YAN XI',19,'MA')，并查询结果 4. 通过视图V_IS，删除学号为“S12”和“S3”的学生信息，并查询结果

5. 要通过视图V_S_C_G，将学号为“S12”的姓名改为“S12_MMM”，是否可以实现？并说明原因

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

二、数据库安全性控制

（1）在企业管理器中为所属的SQL服务器设置Windows NT安全认证模式。

（2）在企业管理器中为自己建立一个服务器用户、数据库用户和数据库角色。并将自己创建的学生库所有操作权赋予它们，将自设计的数据库的操作权赋予新建的数据库用户。

23

【实验步骤】 一、视图操作 （一）定义视图

定义计算机系学生基本情况视图V_Computer； 步骤：新建查询-输入代码： CREATE VIEW V_COMPUTER AS

SELECT *

FROM student

WHERE dno='计算机'

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

步骤：新建查询-输入代码： CREATE VIEW V_S_C_G AS

SELECT student.sno,student.sname,course.cno,course.cname,student_course.score FROM student,course,student_course WHERE student.sno=student_course.sno

将各系学生人数，平均年龄定义为视图V_NUM_AVG 定义一个反映学生出生年份的视图V_YEAR

将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G 将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G （二）使用视图

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

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

3查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩； 4按系统计各系平均成绩在80分以上的人数，结果按降序排列； （三）修改视图

通过视图V_IS，分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,”S4_MMM” 并查询结果;

步骤：新建查询-输入代码： update S_C_GRADE set sname='S1_MMM' WHERE sno like 'S1' GO

update S_C_GRADE set sname='S4_MMM' WHERE sno like 'S4'

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

values ('S12','YAN XI',19,'IS') go

通过视图V_IS，新增加一个学生记录 ('S13','YAN XI',19,'MA')，并查询结果 通过视图V_IS，删除学号为“S12”和“S3”的学生信息，并查询结果

24

步骤：新建查询-输入代码： delete from V_IS

WHERE sno='S12' AND sno='S3' 二、数据库安全性控制

（一）设置SQL Server的安全认证模式

1.在企业管理器中展开SQL服务器组。用鼠标右键单击需要设置的 SQL服务器，在弹出的菜单中选择“属性”项，如图3-1所示。 图3-1 SQL服务器的弹出菜单 图3-2 SQL Server属性的安全性页面 2.在弹出的SQL服务器属性对话框中，选择“安全性”选项卡，如图3-2所示。 3.在安全性选项卡中有一个安全性栏，它包括两个单选钮：单击“SQL Server和Windows(S)为选择混合安全认证模式；单击“仅Windows(W)”则为选择集成安全认证模式。 （二）登录的管理

1.查看安全性文件夹的内容

使用企业管理器可以创建、查看和管理登陆，登录文件夹存放在SQL服务器的安全性文件夹中。当执行了进入企业管理器，打开指定的SQL服务器组和SQL服务器，并选择安全性文件夹的系列操作后，就会出现该窗口。

通过该窗口可以看出，安全性文件夹包括4个文件夹；登录文件夹。服务器角色文件夹、链接服务器文件夹和远程服务器文件夹。其中：登录文件夹用于存储和管理登录用户，服务器角色文件夹用于存储和管理角色：链接服务器文件夹用于存储和管理连接的服务器．远程服务器文件夹用于存储管理远程服务器信息。

2.创建一个登录用户

（1）用鼠标右键单击登录文件夹，在弹出的菜单中选择“新建登录”选项后就会出现一个登录属性对话框。

在对话框中有常规选项卡、服务器角色选项卡和数据库访问选项卡。

（2）选择常规选项卡输入用户的一般特征、用户名，并选择该用户的安全认证模式，选择默认数据库和默认语言。如果选择 Windows身份验证，需要单击名称右边的“….”按钮，调出windows已有的登录用户如图3-4所示，从中选择新建的登录名称，并在下面的栏目中输入新的登录密码。

25

图3-4 Windows系统具有的默认登录用户

（3）选择服务器角色选项卡，确定用户所属服务器角色。服务器角色选项卡的服务器角色列表中列出了系统的固定服务器角色，在这些固定服务器角色的左端有相应的复选框 ，该登录用户就成为相应的服务器角色成员了。

（4）选择数据库访问选项卡，确定用户能访问的数据库，并确定用户所属的数据库角色。在数据库访问选项卡中有两个列表框，上面的列表框中列出了SQL服务器全部的数据库，单击某个数据库左端的复选框，表示允许该登录用户访问相应的数据库。

（5）操作完成后，单击“确定”按钮，即完成了创建登录用户的工作。 3.数据库用户的管理

登录用户只有成为数据库用户（Database User）后才能访问数据库。每个数据库的用户信息都存放在系统表Sysusers中，通过查看Sysusers表可以看到该数据库所有用户的情况。 SQLServer的人以数据库中都有两个默认用户：dbo(数据库拥有者用户)和（客户用户）。通过系统存储过程或企业管理器可以创建新的数据库用户。

要在stu_DB数据库中创建一个“User1”数据库用户，步骤如下。

（1）在企业管理其中扩展SQL服务器及数据库文件夹。用鼠标右键单击用户文件夹，弹出一个快捷菜单，如图3-5所示，在弹出的菜单中选择“新建数据库用户”项，会出现如图3-6所示的数据库用户属性对话框。 图3-5 数据库用户的弹出菜单 图3-6新建数据库用户属性对话框 （2）在对话框的登录名栏中选择一个SQL服务器登陆用户名，本例为“login1”,并在他下面的用户名栏中输入数据库用户参加的角色。 4. 服务器及角色的管理

登录用户可以通过两种方法加入到服务器角色中：一种方法是在创建登陆时，通过

服务器角色页面中的服务器角色选项，确定登录用户应属于的角色；另一种方法是对已有的

26

登录，通过参加或移出服务器角色的方法。

具体步骤为：

（1）在企业管理器中指定的SQL服务器、安全性文件夹、单击服务器角色后，就会出现8个预定义的服务器级角色。

（2）选中一个服务器级角色，例如Database Creators，单击鼠标右键弹出菜单。

（3）在弹出的菜单中选择“属性”项后，就会出现一个服务器角色属性对话框，其中常规选项卡用于将登录用户添加到服务器角色中或从服务器角色中移去登录用户；权限选项卡的主要功能是介绍所选择的服务器角色的权限情况。

（4）选择常规页面．并单击“添加”按钮，在出现的选择登录用户对话框中，选择登录名后。单击“确定?”按钮，之，新选的登录就会出现在常规对话框中。如果要从服务器角色中移去登录，则先选中登录用户，再单击“删除”按钮即可。

（5）选择权限页面，可以看到该服务器角色可以执行的全部管理命令，即新加的登录也可以使用这些操作命令。

5. 数据库角色的管理

（1）在数据库角色中增加或移去用户

在企业管理器中，向数据库角色添加或移去用户的方法是：

? 展开一个SQL服务器、数据库文件夹和指定的数据库文件夹，选中角色文件夹后，

在细节窗口中就会出现该数据库已有的角色。

? 选中要加入的角色，用鼠标右击它，在弹出的数据库角色属性对话框中，单击“添

加”按钮，则出现选择该数据库用户的对话框，选择要加入角色的用户。

? 如果在数据库角色中要移走一个用户，在用户栏中选中它后，单击“删除”按钮。 （2）创建新的数据库角色

? 在企业管理器中打开SQL服务器组、服务器、数据库文件夹和特定的数据库文件

夹。

? 选中角色子文件夹后，用鼠标右击任意角色，并在弹出的菜单中选择“新建数据库

角色”项 ，在对话框的名称栏中输入新角色名，再确定数据库角色的类型。

数据库角色的类型有两种选择：标准角色（Standard Role）和应用程序角色（ApplicationRole).标准角色用于正常的用户管理，它可以包括成员；而应用程序角色是一种特殊角色，需要指定口令，是一种安全机制。

6. 对象权限的管理

对象极限的管理可以通过两种方法实现：一种是通过对象管理它的用户及操作权；另一种是通过用户管理对应的数据库对象及操作权。

（1）通过对象授予、撤消和废除用户权限

在SQL Server 2000的企业管理器中，实现对象权限管理的操作步骤如下：

? 展开SQL服务器、数据库文件夹和数据库，选中一个数据库对象。单击鼠标右键，

使之出现弹出菜单。

? 在弹出的菜单中，选择“所有任务”中的“管理权限”项，随后就会出现一个对象权限

对话框。

? 在对象权限对话框的上部有两个单选框：选择“列出全部用户/户定义的数据库角色

/public”项。则在下面的权限表中列出所有的数据库用户和角色；选择“仅列出对此对象具有权限的用户/用户定义的数据库角色/public”项，在权限表中列出仅具有本对象操作权的数据库用户和角色。

? 在对象权限对话框的下面是有关数据库用户和角色所对应的权限表，这些权限均以

复选框的形式表示。复选框有三种状态。“√”为授权：“╳”为废除权；空为撤权。

27

在表中可以对各用户或角色的各种对象操作权（SELECT、INSERT、UPDATE、DELETE、EXEC和DRI）进行授予或撤消。

（2）通过用户或角色授予、撤消和废除对象权限

如果要为一个用户或角色同时授予、撤消或者废除多个数据库对象的使用权限，则可以通过用户或角色的方法进行。

在企业管理器中，通过用户或角色授权（或收权）的操作步骤如下：

? 展开一个SQL服务器和数据库文件夹，单击用户或角色文件夹。在细节窗口中找

到要选择的用户或角色，鼠标右键单击该角色，弹出数据库角色属性对话框。 ? 在对话框中的权限列表中，对每个对象进行授权、撤消权和废除极限操作。 7. 语句权限的管理

在SQL Server 2000的企业管理器中，还提供了管理语句权限的方法，其操作的具体步骤如下：

（1）展开一个SQL服务器、数据库文件夹，右键单击指定的数据库文件夹，会出现数据库属性对话框。

（2）在数据库属性对话框中，选择“权限”选项卡，出现管理数据库语句权限的对话框。在对话框的列表栏中，单击表中的各复选小方块可分别对各用户或角色授予、撤消和废除数据库的语句操作权限。

8. 创建和修改存储过程 在企业管理器中，如果要创建新的存储过程或要修改一个已存在的存储过程。首先要展开服务器、数据库文件夹以及存储过程所属的数据库，然后用鼠标右击存储过程文件夹，出现存储过程功能菜单、在弹出的菜单上选择“新建存储过程”项，就会出现存储过程属性对话框。

在存储过程属性对话框的文本框中，输入创建存储过程的Transact-SQL 语句, 然后，单击“检查语法”按钮进行语法检查。检查无误后，单击“确定?按钮。这样就创建了一个存储过程。

如果要修改一个已存在的存储过程，用鼠标双击该存储过程，就会出现存储过程属性对话框。在它的文本框中，已经有原存储过程的内容，可以对其Transact-SQL语句进行检查、修改，直到满意后关闭对话框为止。

【思考题】

1. 为什么要建立视图？视图和基本表有什么不同？ 2. 视图和图表有什么不同？

3. 两种 SQL Server的安全认证模式及特点是什么？

28

实验四 简单的数据库设计与应用(8学时)

【实验目的】

1．熟悉E_R模型的基本概念和图形的表示方法。 2．掌握将现实世界的事物转化成E_R图的基本方法。 3．掌握数据库和应用程序的连接技术。 4. 掌握数据库应用系统的开发过程。

【实验要求】

1. 学生管理工作主要是学生基本情况的管理包括学生学籍变动的管理（转入、转出、休学、复学、退学。

2. 学生成绩的管理和教师基本信息管理和教课的管理，主要包括数据输入、修改、删除、追加、查询、统计和打印报表等功能。

3. 学生管理系统要能高效、准确地完成这些功能，并要达到界面美观友好、使用方便。 【实验内容】

1．确定学生管理系统的实体、属性和联系。 2．将实体、属性和联系转化为E_R图。 3．将E_R图转化为表，写出关系模式。

4．数据完整性设计（即实体完整性：设置每张表的主码；参照完整性：设置必要的外码；区域完整性：设置各字段的类型、宽度、取值范围、默认值与约束条件等）。

5．使用SQL SERVER 2000建立数据库，利用JDBC、ODBC或ADO技术实现后台数据库和前台应用程序的连接，选择自己熟悉的可视化开发工具，编程实现应用系统，并输人数据、调试运行系统。

6．运行演示开发的数据库应用系统，提交所有文档。

7．集体讨论、互相学习，指出各自的特点和不足，交流开发过程中的收获和体会。 【实验步骤】

实现学生管理工作的自动化成为学生管理部门的迫切需要，该系统分析如下。 1．系统总体功能结构

在登录窗口中是对使用系统人的身份的识别，包括以下内容：

? 系统管理员：拥有修改、插入、追加、删除、保存数据的权利，并可对能使用本系

统人员进行管理。

? 数据管理员：拥有修改、插入、追加、删除、保存数据的权利，没有对使用本系统

人员的管理项。

? 普通用户：只能进行查询、打印、页面设置、打印预览等简单功能。 在各个学生管理和学籍变动管理窗口中都有对数据的备份功能。

29

图4-1系统总体功能结构

2．系统总体物理结构

本系统可以采用C/S或B/S模式。 3．E-R图设计

（1）确定该系统中的实体及其属性。

（2）确定各实体之间的联系及其联系类型。 （3）确定联系本身的属性。

（4）画出该数据库中关系的E_R图。

（5）将E_R图转化为表，写出表的关系模式。

（6）标明各表的主码或外码，说明各表字段的类型、宽度、取值范围、默认值和其它约束。

E-R图如下。

30

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