《数据库系统原理》实验指导书参考答案(计本10、电商10)

浙江树人大学信息科技学院

《数据库系统原理》

实 验 指 导 书

适合专业：计算机科学与技术本科专业

编写部门：电子商务教研室

编写日期：2012.02

实验一：SQL Server 2005服务器管理 ............................. 1 实验二：创建和管理数据库 ................................................ 4 实验三：数据定义 ................................................................ 4 实验四：数据更新 .............................................................. 11 实验五：数据查询1——简单查询 ................................... 15 实验六：数据查询（2）—连接查询 ................................ 17 实验七：数据查询（3）—综合 ........................................ 20 实验八：视图 ...................................................................... 21 实验九：约束、默认、规则 .............................................. 25 实验十：存储过程 .............................................................. 29 实验十一：触发器（1） .................................................... 34 实验十二：触发器（2） .................................................... 39 实验十三：安全性管理 ...................................................... 46 实验十四：数据库的备份与还原 ...................................... 47

实验一：SQL Server 2005服务器管理

一、 实验目的

通过实验使学生掌握SQL Server 2005数据库服务器启动、暂停、停止的方法；掌握SQL Server 2005数据库服务器的注册方法。

二、 原理解析

1、 SQL Server 2005服务器注册

注册服务器就是在SQL Server Management Studio中登记服务器，然后把它加入到一个指定的服务器组中，并在SQL Server Management Studio中显示SQL Server服务器的运行状态和在SQL Server Management Studio连接时自动启动SQL Server服务器。

2、 SQL Server 2005的各种实用工具

? SQL Server Management Studio

SQL Server提供了多种实用工具来帮助用户管理和使用数据库，这些工具大大方便了用户的工作。

SQL Server Management Studio是微软管理控制台中的一个内建控制台，用来管理所有的SQL Server数据库。

? SQL Server Management Studio查询窗口

SQL Server Management Studio查询窗口是一个图形界面的查询工具，用它可以提交Transact-SQL语言，然后发送到服务器，并返回执行结果，该工具支持基于任何服务器的任何数据库连接。

? 性能监视器

SQL Server提供了监视服务器性能和活动的功能，其功能是在选择的计数器上设置阀值，当达到阀值时，产生警告。利用性能监视器可以创建、观察和保存功能图表，用户可以选择对象和计数器并把它们添加到图表中，不同颜色的图表代表不同的计数器，如果要使图表较小并且可读，可以创建不同的图表来监视不同的统计类型。

? 活动监视器

SQL 活动监视器是图形工具，使系统管理员得以监视SQL Server 2005实例中的事件，可以捕获有关每个事件的数据并将其保存到文件或SQL Server表中供以后分析。

? SQL Server 2005联机丛书

提供如何使用SQL Server 2005的各种帮助。

1

3、 SQL Server 2005的常用属性配置

? ?

SQL Server 2005的登录方式设置 SQL Server 2005的连接设置

4、 SQL Server 2005服务器的运行管理 SQL Server 2005服务器的启动、暂停和停止。 三、 实验内容

1、 启动、暂停和停止SQL Server实例 2、 熟悉SQL Server 2005的各种实用工具 3、 SQL Server 2005服务器注册

四、 实验步骤

1、 启动、暂停和停止SQL Server实例 ? 利用SQL Server Management Studio

在已注册的服务器或对象资源管理器中，右键单击要启动的服务器实例，然后单击“启动”。 如果服务器名称旁边的图标上出现一个绿色箭头，则说明服务器已成功启动。

用同样的方法可以暂停和停止SQL Server实例。 ? 利用SQL Server配置管理器

（1）在“开始”菜单中，依次指向“所有程序”、“Microsoft SQL Server 2005”和“配置工具”，然后单击“SQL Server 配置管理器”。

（2）在 SQL Server 配置管理器中，展开“服务”，再单击 SQL Server。

（3）在详细信息窗格中，右键单击 SQL Server (MSSQLServer)，再单击“启动”。

如果工具栏上和服务器名称旁的图标上出现绿色箭头，则指示服务器已成功启动。 用同样的方法可以暂停和停止SQL Server实例。 2、 熟悉SQL Server 2005的各种实用工具 ? SQL Server Management Studio

? SQL Server Management Studio查询窗口 ? 练习使用SQL Server 2005联机丛书 3、 SQL Server 2005服务器注册

? 在对象资源管理器中，右键单击已经连接的服务器，然后单击“注册”。

? 在“注册服务器”对话框的“服务器名称”文本框中，键入希望显示在“已注册的服务

器”中的该服务器的名称。此项并不要求一定是服务器名称。

2

? 在“服务器说明”文本框中，可以根据需要键入一些其他信息，以帮助标识服务器。 ? 在“选择服务器组”框中，单击服务器组，再单击“保存”。

3

实验二：创建和管理数据库

一、实验目的

通过实验使学生掌握SQL Server 2005数据库的文件结构，数据库的创建和修改方法。

二、原理解析

5、 SQL Server 2005数据库文件和文件组

SQL Server 2005用文件来存放数据库，即将数据库映射到操作系统文件上。数据库文件有主数据文件、次数据文件、事务日志文件。主数据文件的后缀.mdf，次数据文件的后缀.nfd，事务日志文件的后缀.ldf。一个数据库有且仅有一个主文件，事务日志文件至少一个，次数据文件可有可无。

SQL Server 2005中提供了两种类型的文件组：主文件组和用户自定义文件组。

6、 SQL Server 2005数据库类型

SQL Server 2005数据库分为系统数据库和用户数据库。系统数据库包括master数据库、tempdb数据库、model数据库和msdb数据库。

7、 SQL Server 2005数据库的创建方法

SQL Server 2005数据库可以使用SQL Server Management Studio创建，也可以使用T-SQL语句来创建。用SQL Server Management Studio创建数据库比较简单，只要设置好数据库名和主文件、次文件以及日志文件的位置和逻辑名、物理名就可以了。下面介绍用T-SQL的CREATE DATABASE语句创建数据库。

CREATE DATABASE database_name [ON

[[,?n]] [,[,?n]]] [LOG ON {[,?n]}] [COLLATE ] [FOR LOAD|FOR ATTACH] ::= [PRIMARY] (NAME='逻辑文件名',

FILENAME='存放数据库的物理路径和文件名' [, SIZE=数据文件的初始大小]

4

[, MAXSIZE=指定文件的最大大小] [, FILEGROWTH=指出文件每次的增量]）

8、 SQL Server 2005数据库的修改

SQL Server 2005数据库的修改主要包括括修改数据库选项、扩充分配给数据库的数据或事务日志空间、压缩分配给数据库的数据或事务日志空间、添加数据和事务日志文件、创建文件组等。本次实验要求掌握使用企业管理器工具和T-SQL语句及系统存储过程对数据库进行管理。

SQL Server 2005数据库的修改可以在SQL Server Management Studio中完成，也可以使用T-SQL的ALTER DATABASE语句进行。

ALTER DATABASE语句的语法结构如下： ALTER DATABASE database_name

{ADD FILE [,?n][TO FILEGROUP filegroup_name]

/*在文件组中增加数据文件*/

|ADD LOG FILE [,…n] /*增加事务日志文件*/ |REMOVE FILE logical_file_name /*删除数据文件*/ |ADD FILEGROUP filegroup_name /*增加文件组*/ |REMOVE FILEGROUP filegroup_name /*删除文件组*/

|MODIFY FILE /*修改文件属性*/ |MODIFY NAME=new_dbname /*更新数据库名称*/ }

三、实验内容

4、 用SQL Server Management Studio创建数据库 5、 用CREATE DATABASE语句创建数据库 3、 用SQL Server Management Studio修改数据库 4、 用ALTER DATABASE语句修改数据库

四、实验步骤

1、利用SQL Server Studio创建学生数据库，数据文件的存放路径和个数可以自己设定。 2、用CREATE DATABASE语句来创建一个名为BOOK的数据库，它由2MB的主数据文

5

件、5MB的次数据文件和5MB的日志文件组成。并且主数据文件以1MB的增长速度其最大数据文件的大小为10MB，次数据文件以2MB的增长速度其最大次数据文件的大小为15MB，事务日志文件以1MB的增长速度其最大日志文件的大小为10MB。

create database book on

(name=book1,

filename='C:\\SQLServerBook2\\book1.mdf', size=2,

maxsize=10, filegrowth=1), (name=book2,

filename='C:\\SQLServerBook2\\book2.ndf', size=5,

maxsize=15, filegrowth=2) LOG ON

(name=book_log,

filename='C:\\SQLServerBook2\\book_log.ld', size=5,

maxsize=10， filegrowth=1)

3、在SQL Server Studio中将学生数据库的主文件大小改为4MB。

4、用ALTER DATABASE语句修改BOOK数据库，为其增加一个次要数据文件（Book_data2.ndf），该文件的大小是5MB，最大可增长到20MB，以20%的速度增长。

alter database book add file

(name=book_data2,

filename='c:\\sqlServerbook\\book_data2.ndf', size=5MB, maxsize=20MB, filegrowth=20%)

9、 利用SQL Server Studio为学生数据库增加一个文件组MyFilegroup。 提示：通过数据库属性→文件组进行修改。 10、

用ALTER DATABASE语句为BOOK数据库增加一个文件组MyFilegroup。

alter database Student add filegroup MyFilegroup

6

7、用ALTER DATABASE语句为BOOK数据库增加两个次数据文件，并将他们放到MyFilegroup文件组。 ALTER DATABASE BOOK ADD FILE (

NAME = test1dat3,

FILENAME = '''+ @data_path + 't1dat3.ndf'', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ), (

NAME = test1dat4,

FILENAME = '''+ @data_path + 't1dat4.ndf'', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB )

TO FILEGROUP Test1FG1'

7

实验三：数据定义

一、实验目的

通过实验使学生掌握用不用的方法设计数据库表结构、修改数据库表结构以及删除表结构。

二、原理解析

1、用SQL Server 2005的SQL Server Management Studio设计数据库表结构

用SQL Server 2005的SQL Server Management Studio设计数据库表结构比较简单，只要在表设计器中对表中的各个字段进行设计，主要包括字段名、字段的数据类型等。

2、用T-SQL的CREATE TABLE语句创建表结构

CREATE TABLE语句的语法结构如下：

CREATE TABLE table_name

（{column_name datatype NOT NULL|NULL}）

3、用SQL Server 2005的SQL Server Management Studio设计数据库表结构

用SQL Server 2005的SQL Server Management Studio设计数据库表结构比较简单，与创建表结构类似，只要打开表设计器，在表设计器中修改并加以保存就可以了。

4、用T-SQL的CREATE TABLE语句创建表结构

CREATE TABLE语句的语法结构如下： ? 向表中添加列 ALTER TABLE 表名 ADD 列名 列的描述 ? 删除列

ALTER TABLE 表名 DROP COLUMN 列名 ? 修改列

ALTER TABLE 表名

ALTER COLUMN 列名 列的描述

5、删除表结构

? 使用SQL Server Management Studio删除表 ? 用T-SQL的DROP TABLE语句删除表

8

DROP TABLE 表名

三、实验内容

1、用SQL Server Management Studio创建数据库表 2、用CREATE TABLE语句创建数据库表

3、 用SQL Server Management Studio修改数据库表结构 4、 用ALTER TABLE语句修改数据库表结构 5、 用SQL Server Management Studio删除表 6、 用DROP TABLE语句删除表

四、实验步骤

学生数据库由三张包括三张表xsqk、xskc、xscj，各个表的结构如下：

xsqk结构

列名 系别 班级 专业 学号 姓名 性别 出生年月 总学分 备注 数据类型 Char Char Varchar Char Char Char Smalldatetime Tinyint Text 长度 10 12 30 8 8 2 4 1 16 允许空值 √ √ √ × × √ √ √ √ 说明 主键 默认值：男 xskc结构

列名 课程号 课程名 开课学期 学时 学分 数据类型 Char Char Tinyint Tinyint Tinyint 长度 3 16 1 1 1 允许空值 × × × × √ 说明 主键 规则：1~8 xscj结构

列名 学号 课程号 成绩 学分 数据类型 Char Char Tinyint Tinyint 长度 8 3 1 1 允许空值 × × √ √ 说明 主键 主键 1、利用SQL Server Studio创建xsqk表。

9

2、用CREATE TABLE语句创建xskc表和xscj表。

3、利用SQL Server Studio修改xskc表，向表中添加一个字段：字段名为授课教师、数据

类型为char、长度为10、并且允许为NULL。

4、用ALTER TABLE语句修改xskc，将刚刚添加的授课教师字段删除。 5、用ALTER TABLE语句修改xskc，将课程名长度改为18。 6、用SQL Server Studio删除数据表xscj。 7、用DROP TABLE语句删除数据表xskc。

10

实验四：数据更新

一、实验目的

通过实验使学生掌握用不用的方法实现数据库表数据的插入、更新和删除。

二、原理解析 11、

用SQL Server 2005的SQL Server Management Studio实现表数据的更新

用SQL Server 2005的SQL Server Management Studio实现表数据的更新比较简单，只要打开表，直接在里边作修改，然后退出，系统会自动保存。

12、 用T-SQL的INSERT语句向表中插入数据

INSERT语句的语法结构如下： INSERT [INTO] 表名 [（列名）] VALUES（表达式）

13、 用INSERT…SELECT语句向表中批量插入数据

INSERT?SELECT语句的语法结构如下： INSERT [INTO] 表名 SELECT语句

14、 用T-SQL的UPDATE语句修改表数据

UPDATE语句的语法结构如下： UPDATE 表名 SET 列名= 表达式 [ WHERE 条件 ]

15、 删除表数据

? 用T-SQL的DELETE语句删除表数据 DELETE语句的语法结构： DELETE 表名 [WHERE 条件]

? 用T-SQL的TRUNCATE TABLE语句删除表 TRUNCATE TABLE语句的语法结构： TRUNCATE TABLE 表名

三、实验内容

11

6、 用SQL Server Management Studio更新表数据 7、 用INSERT语句向表中插入数据

3、 用INSERT…SELECT语句向表中插入数据 4、 用UPDATE语句修改表数据 5、 用DELETE语句删除表数据

6、 用TRUNCATE TABLE语句删除表数据

四、实验步骤

1、利用SQL Server Studio向xsqk表、xskc表、xscj表中插入数据。

表1 学生情况表（xsqk）数据样本

总学姓名 性别 出生年月 分 备注 9 9 8 8 系别 班级 专业 学号 计算机应用与维02020101 王玲玲 女 1981-8-26 计算机 计算机0203 护 计算机应用与维02020102 张燕红 女 1981-10-20 计算机 计算机0203 护 计算机应用与维02020103 杨勇 计算机 计算机0203 护 男 1982-3-15 计算机应用与维02020104 王红庆 男 1983-5-17 计算机 计算机0203 护 计算机应用与维02020105 陈园 计算机 计算机0203 护 计算机 计算机 计算机 计算机 计算机 信息管理0201 信息管理0201 信息管理0201 信息管理0201 信息管理0201 信息管理 信息管理 信息管理 信息管理 信息管理 女 1982-4-12 02020201 黄薇娜 女 1983-8-19 02020202 沈昊 男 1982-3-18 02020203 傅亮达 男 1983-1-22 02020204 任建刚 男 1981-12-21 02020205 叶小红 女 1983-7-16 表1 学生课程表（xskc）数据样本

课程号 101 102 205 206

课程名 Qbasic 离散数学 VC 开课学期 1 1 3 2 12

学时 86 68 64 68 学分 4 4 4 4 计算机文化基础 208 210 212 216 301 数据结构 操作系统 计算机组成 数据库原理 计算机网络 2 3 4 2 5 68 64 86 68 56 4 4 5 4 3 表2 学生成绩表（xscj）数据样本

学号 02020101 02020101 02020102 02020102 02020201 02020201 02020202 02020202 课程号 101 102 101 102 101 208 208 216 成绩 85 70 90 80 86 80 50 60 学分 4 5 4 5 4 4 4 4 2、用向xskc表中插入如下表所示的两条记录。假设学生成绩表（xskc）中有如下数据：

表4 要插入的记录值

课程号 102 205 课程名 Qbasic 离散数学 开课学期 学时 1 3 68 64 学分 4 4 insert into xskc(课程号,课程名,开课学期,学时,学分) values ('102','QBasic',1,68,4) insert into xskc(课程号,课程名,开课学期,学时,学分) values ('205','离散数学',3,94,4)

3、利用SQL Server Studio Management修改xsqk表、xskc表、xscj表中的数据。

4、用UPDATE语句修改xskc，将102课程的学分改为6。 Update xskc set 学分=6 where 课程号=? 102?

5、用UPDATE语句修改xsqk，将信息管理专业学生的总学分改为12。 Update xsqk set 总学分=12 where 专业=?信息管理? 6、用DELETE语句删除“离散数学”的课程信息。 DELTE FROM xskc where 课程名=?离散数学? 7、用DELETE语句删除1983年出生的学生信息。

DELTE FROM xsqk where 出生年月 between ‘1983-1-1’ and ‘1983-12-31’ 8、用DELETE语句删除不及格的选课信息。

13

DELETE FROM xscj where 成绩<60

9、用TRUNCATE TABLE语句删除xsqk表中的所有信息。 TRUNCATE TABLE xsqk

14

实验五：数据查询1——简单查询

一、 实验目的

通过对SELECT的使用，掌握SELECT语句的结构及其应用，掌握基于单个表的查询。 二、 原理解析

语法格式为：

SELECT〈目标列组〉 FROM〈数据源〉

[WHERE〈元组选择条件〉]

[GROUP BY〈分列组〉[HAVING 〈组选择条件〉]]

[ORDER BY〈排序列1〉〈排序要求1〉 [，…n]](1) SELECT子句 SELECT子句用于指明查询结果集的目标列。 (2) FROM子句

FROM子句用于指明查询的数据源。 (3) WHERE子句

WHERE子句通过条件表达式描述关系中元组的选择条件。 (4) GROUP BY子句

GROUP BY子句的作用是按分组列的值对结果集分组。

(5) ORDER BY子句ORDER BY子句的作用是对结果集进行排序。 三、实验内容

（1）取出生年月小于’1982-1-1’的学生名单。 select * from xsqk

where 出生年月 <'1982-1-1'

（2）检索xsqk表中的所有记录数据。 select * from xsqk

（3）查询班级为“计算机0203”的学生情况。 select * from xsqk where 班级 ='计算机0203'

（4）查询出生年月在“1981-01-01”至“1982-05-01”之间的学生情况。

15

select * from xsqk where 出生年月 between '1981-1-1' and '1982-5-1' （5）查询姓名中含有“红”字的学生情况。 select * from xsqk where 姓名 like '%红%'

（6）查询出生年月在“1981-01-01”至“1982-05-01”之间的学生情况，并以出生年月按降序排列。 select * from xsqk

where 出生年月 between '1981-1-1' and '1982-5-1' order by 出生年月 desc

（7）统计学生情况(xsqk)表中“计算机0203”的班级有多少人。 select count(学号) from xsqk where 班级='计算机0203'

（8）统计学生成绩(xscj)表中每门课程的最低分、最高分和平均分。

select 课程号,max(成绩) 最高分,min(成绩) 最低分,avg(成绩) 平均分 from xscj group by 课程号

（9）在学生成绩库中查询出生年月在“1981-01-01”至“1982-05-01”之间的男生情况，并以出生年月按降序排列。 select * from xsqk

where 出生年月 between '1981-1-1' and '1982-5-1' and 性别='男' order by 出生年月 desc

（10）统计“信息管理”专业的人数。 select count(学号) from xsqk where 专业='信息管理'

（11）统计每个学生每个学期的最低分、最高分及平均分。

select 学号,max(成绩) 最高分,min(成绩) 最低分,avg(成绩) 平均分 from xscj

group by 学号

16

实验六：数据查询（2）—连接查询

一、实验目的

通过对SELECT的使用，掌握SELECT语句的结构及其应用，掌握连接查询。

二、 原理解析

语法格式为：

SELECT〈目标列组〉 FROM〈数据源〉

[WHERE〈元组选择条件〉]

[GROUP BY〈分列组〉[HAVING 〈组选择条件〉]]

[ORDER BY〈排序列1〉〈排序要求1〉 [，…n]](1) SELECT子句 SELECT子句用于指明查询结果集的目标列。 (2) FROM子句

FROM子句用于指明查询的数据源。 (3) WHERE子句

WHERE子句通过条件表达式描述关系中元组的选择条件。 (4) GROUP BY子句

GROUP BY子句的作用是按分组列的值对结果集分组。

(5) ORDER BY子句ORDER BY子句的作用是对结果集进行排序。 三、实验内容

1.查询学号为的学生所选的全部课程的课程名和成绩

select 课程名,成绩from xscj,xskc where xscj.课程号=xskc.课程号and 学号='2020102'

2.查询没有学生选的课程号

select 课程号from xskc where 课程号not in( select distinct 课程号from xscj) --或

select 课程号from xskc where not exists(

select 课程号from xscj where xskc.课程号=xscj.课程号)

17

3.查询没有成绩的课程号，课程名

select 姓名,课程号,课程名from xscj,xskc where 成绩is null and xscj.课程号=xskc.课程号 and xscj.学号=xsqk.学号 4.查询没有选修号课程的学生姓名 select 姓名from xsqk where not exists(

select * from xscj where xscj.学号=xsqk.学号and 课程号='102') --或

select 姓名from xsqk where 学号not in( select 学号from xscj where 课程号='102' )

5.查询选修了课程并且成绩在分以上的所有学生（学号、姓名）。 select xsqk.学号,姓名from xscj,xsqk

where xscj.学号=xsqk.学号and 成绩>=80 and 课程号='101'

6.查询与“王玲玲”在同一专业学习的学生姓名。 select姓名

from xsqk where 专业=(select 专业from xsqk where 姓名='王玲玲') 7.查询选修了课程名为“计算机文化基础”的学生学号和姓名。 select xsqk.学号, 姓名 from xsqk, xskc,xscj

where xsqk.学号=xscj.学号and xskc.课程号=xscj.课程号 and 课程名='计算机文化基础'

9.查询选修了课程名为“计算机文化基础”或“数据结构”的学生学号和姓名。 select xsqk.学号,姓名,课程名from xsqk,xskc,xscj

where xsqk.学号=xscj.学号and xskc.课程号=xscj.课程号and (课程名='计算机文化基础' or 课程名='数据结构')

10.统计每个学生每个学期的最低分、最高分及平均分。

select 学号,开课学期,min(成绩) as 最低分,max(成绩)as 最高分,avg(成绩)as

平均分 from xscj,xskc

18

where xscj.课程号=xskc.课程号group by 学号,开课学期 11.查询每门课程的最高分（课程号、课程名、最高成绩）。 select xscj.课程号,课程名, max(成绩)as 最高分

from xscj,xskc where xscj.课程号=xskc.课程号group by xscj.课程号,课程名 12.查询所有有选课记录的学生的情况（姓名，课程名，成绩。） select xsqk.*,课程名,成绩from xscj,xskc,xsqk

where xscj.学号=xsqk.学号and xscj.课程号=xskc.课程号 --或

select 姓名,课程名,成绩from xscj,xskc,xsqk

where xscj.学号=xsqk.学号and xscj.课程号=xskc.课程号 13.查询选修了学号为“”的学生所选的某门课程的学生编号。 select distinct 学号from xscj

where 课程号in (select 课程号 from xscj where 学号='2020101')

19

实验七：数据查询（3）—综合

一、实验目的

通过对SELECT的使用，掌握SELECT语句的结构及其应用，掌握连接查询。

二、原理解析

语法格式为：

SELECT〈目标列组〉 FROM〈数据源〉

[WHERE〈元组选择条件〉]

[GROUP BY〈分列组〉[HAVING 〈组选择条件〉]]

[ORDER BY〈排序列1〉〈排序要求1〉 [，…n]](1) SELECT子句 SELECT子句用于指明查询结果集的目标列。 (2) FROM子句

FROM子句用于指明查询的数据源。 (3) WHERE子句

WHERE子句通过条件表达式描述关系中元组的选择条件。 (4) GROUP BY子句

GROUP BY子句的作用是按分组列的值对结果集分组。

(5) ORDER BY子句ORDER BY子句的作用是对结果集进行排序。 三、实验内容

1、查询所有学生的姓名和课程名称。 2、查询平均成绩在60分以上的学生姓名。 3、查询每个学生的姓名、所选修的课程名及成绩。 4、查询选修了课程的学生姓名。

5、查询选修课程数在两门以上的学生姓名。（用连接查询和嵌套查询两种方法实现）

6、查询成绩及格的学生人数。 7、查询成绩及格的学生人次。

8、查询所有学生都没有选修的课程名称。

9、查询每个学生的平均成绩，并按平均成绩的降序排序。 10、查询每个同学的最高分，要求显示学号、姓名和分数等信息。 11、查询选修同一门课程的学生学号。

20

实验八：视图

一、实验目的

通过实验使学生掌握视图的创建、视图的查询以及通过视图更新（插入、修改、删除）基本表中的数据。

二、原理解析

1、用SQL Server 2005的SQL Server Management Studio创建视图

用SQL Server 2005的SQL Server Management Studio创建视图比较简单，只要打开视图设计器对视图进行编辑就可以完成视图的创建。

2、用T-SQL的CREATE VIEW语句创建视图

CREATE VIEW语句的语法结构如下：

CREATE VIEW 视图名[(列名1, 列名2[, ?n])] [WITH 属性]] AS 查询语句 [WITH CHECK OPTION]

3、 视图查询

视图创建后就可以像使用基本表一样来使用，可以通过视图进行查询。基于视图的查询能够简化查询语句。

4、 视图更新

通过视图更新可以实现基本表数据的更新，视图更新包括视图的插入、修改和删除操作。

5、 视图删除

DROP VIEW 视图名

三、实验内容

1、用SQL Server Management Studio创建视图 2、用CREATE VIEW语句创建视图 3、 视图查询 4、 视图更新 5、 视图删除

四、实验步骤

21

一、实验目的

通过实验使学生掌握视图的创建、视图的查询以及通过视图更新（插入、修改、删除）基本表中的数据。

二、原理解析 16、

用SQL Server 2005的SQL Server Management Studio创建视图

用SQL Server 2005的SQL Server Management Studio创建视图比较简单，只要打开视图设计器对视图进行编辑就可以完成视图的创建。

17、 用T-SQL的CREATE VIEW语句创建视图

CREATE VIEW语句的语法结构如下：

CREATE VIEW 视图名[(列名1, 列名2[, ?n])] [WITH 属性]] AS 查询语句 [WITH CHECK OPTION]

18、 视图查询

视图创建后就可以像使用基本表一样来使用，可以通过视图进行查询。基于视图的查询能够简化查询语句。

19、 视图更新

通过视图更新可以实现基本表数据的更新，视图更新包括视图的插入、修改和删除操作。

20、 视图删除

DROP VIEW 视图名 三、实验内容

8、 用SQL Server Management Studio创建视图 9、 用CREATE VIEW语句创建视图 3、 视图查询 4、 视图更新 5、 视图删除

四、实验步骤

1、用SQL Server Management Studio创建视图。为学生情况（XSQK）表创建一个视图V_XSQK，以显示学生的基本信息，如学号、姓名、性别和出生年月。

22

2、用T-SQL的CREATE VIEW语句创建上述视图。 CREATE VIEW V_XSQK AS

SELECT 学号 , 姓名 , 性别 , 出生年月 FROM XSQK

3、用CREATE VIEW语句创建视图V_Student，要求显示学生的姓名、选修的课程名和成绩。

CREATE VIEW V_Student AS

SELECT 姓名，课程名，成绩 FROM xsqk，xscj，xskc

WHERE xsqk.学号=xscj.学号 and xscj.课程号=xskc.课程号

4、查询在1981-1-1以后出生的学生信息，包括学号，姓名，性别和出生年月。 SELECT * FROM V_XSQK WHERE 出生年月>?1981-1-1?

5、查询成绩合格的学生信息，包括姓名、课程名和成绩。 SELECT 姓名，课程名，成绩 FROM V_STUDENT WHERE 成绩>60

6、创建分组视图V_STUDENT1，要求查询每个学生的平均成绩，包括姓名，平均成绩。 CREATE VIEW V_STUDENT1（姓名,平均成绩) AS

SELECT姓名， AVG（成绩） FROM xsqk，xscj，xskc

WHERE xsqk.学号=xscj.学号 and xscj.课程号=xskc.课程号 GROUP BY 姓名

7、向视图V_XSQK中插入一条记录：‘2020206’，‘李红’，‘1983-12-3’，并观察XSQK表中的数据是否发生变化。 INSERT INTO V_XSQK

VALUES（‘2020206’，‘李红’，‘1983-12-3’）

23

8、修改视图V_XSQK，将李红同学是出生年月改为1982-12-3，并观察XSQK表中数据的变化。

UPDATE V_XSQK

SET 出生年月=‘1982-12-3’ WHERE 姓名=‘李红’

9、删除视图V_XSQK中李红同学的信息，并观察XSQK表中数据的变化。 DELETE FROM V_XSQK WHERE 姓名=‘李红’

10、查询平均成绩在60分以上的学生信息，包括姓名和平均成绩。 SELECT * FROM V_STUDENT1 WHERE 平均成绩>60

24

实验九：约束、默认、规则

一、实验目的

通过数据库中约束、默认和规则的使用，掌握约束、默认、规则的概念及约束、默认、规则的操作和使用方法。 二、原理解析

数据完整性维护数据库输入数据的正确性和一致性，通过定义数据完整性规则，SQL Server可以通过自身提供的完整性规则有效地管理数据的输入，而不必要使用额外的应用程序来协助管理，这样一方面可以节省系统开销，另一方面使数据库中的数据独立于应用程序，使创建开放式数据库系统成为可能。

数据完整性包括实体的完整性、域完整性、参照完整性和用户自定义完整性。 1、实体完整性

实体完整性将记录行定义为特定表的唯一实体。实体完整性强制表中的所有记录都有一个惟一的标识符列（通过UNIQUE约束、PRIMARY KEY约束或IDENTITY属性）。例如：在学生情况表中，学生的学号是惟一的，它与每个学生一一对应。 2、域完整性

域完整性是指给定列的输入有效性。强制域有效性的方法有：通过限制数据类型(包括自定义数据类型)、格式（CHECK约束和规则）或可能的取值范围（FOREIGN KEY约束、CHECK约束、DEFAULT定义、NOT NULL定义和规则）来实现。 3、参照完整性

参照完整性能确保数据库中数据的一致性。在SQL Server中，参照完整性基于外键与主键之间或外键与唯一键之间的关系（通过FOREIGN KEY和 CHECK约束）。参照完整性确保键值在所有表中一致，这样的一致性要求不能引用不存在的值（即指向不存在的行）。如果在被参考的表中，某一记录被外部键参考，则该记录就不能删除，若需要更改键值，那么在整个数据库中，对该键值的所有引用都要进行一致的更改。

参照完整性在输入或删除记录时保持表之间已定义的关系。当设置了参照完整性时，SQL Server将从以下几个方面限制用户对数据库的操作：

25

当主表中没有关联的记录时，向一个相关表添加记录。

更改了主表中的键值但没有更改相关表中的数据，从而使相关表中的记录无法在主表中找到对应记录。

从主表中删除记录，但在相关表中仍存在与该记录相匹配的记录。 例如：在学生成绩库中，只要学生在成绩表中有成绩记录，则该学生记录就不能直接在学生情况表中删除。 4、用户定义完整性

可以在SQL Server中定义不属于上述类别的特定规则的用户完整性定义。所有的完整性类型都支持用户定义完整性（CREATE TABLE中的所有列级和表级约束、存储过程和触发器）。

在SQL Server中，有两种方式可以实现数据完整性，声明数据完整性和过程数据完整性。声明数据完整性是通过在对象定义中定义的标准来实现数据完整性，是由系统本身的自动强制功能来实现的，它包含使用各种约束、默认和规则。而过程数据完整性是通过在脚本语言(使用触发器或存储过程)中定义的完整性标准来实现的，当这些脚本被执行时，就可以强制完整性的实现。 三、实验内容

（1）对学生情况表xsqk_sample使用NOT NULL约束限制系别、学号和姓名列不能为空，使用PRIMARY KEY约束将学号设置为主键，使用CHECK约束对性别列中的值进行限制，使其值只能接受“男”或“女”，以强制执行域的完整性，性别的默认值为“男”。

CREATE TABLE xsqk_sample (

系别 char(10) not null， 班级 char(12), 专业 Varchar(30),

学号 char(8) not null primary key, 姓名 Varchar(10) not null, 性别 char(2) default ‘男’, 出生年月 smalldatetime, 总学分tinyint, 备注 text,

CONSTRAINT chk_Sex CHECK ( 性别 in （‘男’，‘女’）)

)

26

（2）在学生课程表中创建DEFAULT约束，约束名为Period_Def，要求实现使学时列的默认值设置为72。

alter table xskc

add constraint Period_Def default 72 for 学时

（3）在学生成绩表中创建FOREIGN KEY约束，约束名为Con_CourseNo，要求实现把学生成绩表中的“课程号”列和学生课程表中的“课程号”关联起来。

alter table xscj

add constraint Con_CourseNo foreign key(课程号) references xskc(课程号)

（4）为学生课程表中的学分列定义一个DEFAULT约束，默认值为4。

alter table xskc add constraint x default 4 for 学分

（5）在学生成绩表中定义FOREIGN KEY约束，把学生成绩表中的“学号”列和学生情况表中的“学号”关联起来。

alter table xscj

add constraint Con_StudentNo foreign key(学号) references xsqk(学号)

（6）创建一个默认值为'1982/1/1'的默认值。

create default y as '1982-1-1'

（7）将创建的Default_Birthday默认值绑定到学生情况表的出生年月列上。

27

sp_bindefault 'y','xsqk.出生年月'

（8）创建一个规则rule_Term_Range，用以限制输入该规则所绑定的列中的数据范围为1～8。

create rule rule_Term_Range as @range>=1 and @range<=8

（9）将上题创建的rule_Term_Range规则绑定到学生课程表的开课学期列上。

sp_bindrule 'rule_Term_Range','xskc.开课学期'

（10）定义一个规则rule_Speciality，这个规则限制学生情况表中的专业列只能取以下的值：计算机应用与维护、信息管理、电子商务、电子技术。绑定后给学生情况表插入数据行，观察规则的设置情况，使用完毕后，解除并删除该规则。实现过程要求使用SQL语句完成。

create rule rule_Speciality

as @zy in('计算机应用与维护','信息管理','电子商务','电子技术')

sp_bindrule 'rule_Speciality','xsqk.专业' sp_unbindrule 'xsqk.专业' drop rule rule_Speciality

（11）将上述题目中的要求在SQL Server Management Studio中加以实现。

28

实验十：存储过程

一、实验目的

通过实验使学生加深对数据完整性的理解，学会创建和使用存储过程。 二、原理解析 1、存储过程概述

存储过程是指，在一个执行规划中预先定义并编译好的一组Transact-SQL语句。这些语句在一个名称下存储并作为一个单元进行处理。存储过程是编写数据库代码中的重要成份。它们可以是构成任何一个由数据库支撑的应用程序的代码，并且能被这些应用程序中的任何一个调用。

存储过程能在查询分析器（Query Analyzer）窗口运行。它们可以被批处理文件调用，也可以由Access、Visual Basic或者其他编程语言的用户接口程序来调用。可以像其他子例程调用一样，把参数传给存储过程。存储过程会返回数据值、状态代码以及错误信息，这样用户就可以做出适当的响应。存储过程也能调用其他存储过程。存储过程能够在本地计算机或远程服务器上运行。在执行系统内部任务时，用户可以指示SQL Server自动运行某种存储过程。

存储过程类型包括系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。通过存储过程的创建、执行、修改和删除操作，初步了解存储过程的类型，掌握利用存储过程进行程序设计的方法技巧。 2、存储过程的类型

系统存储过程、用户自定义存储过程、扩展存储过程。 3、在SQL中创建存储过程的语法 创建存储过程的语法为：

CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ]

29

AS sql_statement [ ...n ]

4、存储过程的执行 执行存储过程： [ [ EXEC [ UTE ] ] {

[ @return_status = ]

{ procedure_name [ ;number ] | @procedure_name_var }

[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] } [ ,...n ]

[ WITH RECOMPILE ] 5、存储过程中的参数

输入参数和输出参数（OUTPUT）。 三、实验内容

（1） 创建不带参数的存储过程。 （2） 创建带输入参数的存储过程。 （3） 创建带输出参数的存储过程。 四、实验步骤

1、在学生成绩库中创建存储过程，存储过程名为proc1，要求实现如下功能：根据学生学

号，查询该学生的选课情况，其中包括该学生学号、姓名、性别、课程号、课程名、成绩和学分等。

CREATE PROCEDURE proc1

@sno char(8) --@sno是参数，字符型，表示某个学生的学号 AS

SELECT xscj.学号, xsqk.姓名, xsqk.性别, xscj.课程号, xskc.课程名, xscj.成绩, xscj.学分 FROM xscj, xsqk, xskc WHERE xscj.学号 = xsqk.学号 AND xscj.课程号 = xskc.课程号 AND xsqk.学号 = @sno GO

30

2、现有一学生学号为“02020101”，要求通过调用上例中所建的名为proc1的存储过程，实现显示该学生的选课情况列表。 EXECUTE proc1 ?02020101? 或

EXECUTE proc1 @sno = ?02020101? 或

EXEC proc1 @sno =?02020101?

3、在学生成绩库中创建一个名为proc2的存储过程，产生一个学生选课情况列表，其中包括学号、姓名、性别、课程号、课程名、成绩、学分等，并调用该存储过程查看学生的选课情况。

CREATE PROCEDURE proc2 AS

SELECT xscj.学号, xsqk.姓名, xsqk.性别, xscj.课程号, xskc.课程名, xscj.成绩, xscj.学分 FROM xscj, xsqk, xskc WHERE xscj.学号 = xsqk.学号 AND xscj.课程号 = xskc.课程号 GO EXEC proc2

4、在学生成绩库中创建一个名为proc3的存储过程，产生某门课程的选课学生情况列表，其中包括课程号、课程名、学号、姓名、系别、班级、专业、性别等。要求输入某门课程的课程号，得到上述信息。 CREATE PROCEDURE proc3

@cno char (3) --@cno是参数，字符型，表示某门课的课程号 AS

SELECT xscj.课程号, xskc.课程名,xscj.学号, xsqk.姓名, xsqk.系别,xsqk.班级, xsqk.专业,xsqk.性别 FROM xscj, xsqk, xskc WHERE xscj.学号 = xsqk.学号 AND xscj.课程号 = xskc.课程号 AND xscj.课程号 = @cno

31

GO

5、利用上述存储过程查询课程号为“101”的课程的选课学生情况列表。 Exec Proc3 ?101?

6、定义存储过程proc4，要求实现输入学生学号，根据该学生所选课程的平均成绩显示提示信息，即如果平均成绩在60分以上，显示“此学生综合成绩合格！”，否则显示“此学生综合成绩不合格！”。

CREATE PROCEDURE proc4

@sno char(8) --@sno是参数，字符型，表示学生的学号 AS

DECLARE @savg tinyint --声明变量@savg，用于存放学生平均成绩 SELECT @savg = avg(成绩) FROM xscj

WHERE xscj.学号 = @sno --变量@savg存放学号为@sno的学生平均成绩 IF @savg>=60 --进行平均成绩的判断 PRINT ?此学生综合成绩合格！? ELSE

PRINT ?此学生综合成绩不合格！? GO

7、利用已创建的存储过程查询学号为“02020101”同学的平均成绩情况。 Exec proc4 ?02020101?

8、定义存储过程proc5，要求实现输入学号，输出该学生所选课程的平均成绩，要求用输入参数和输出参数实现。 CREATE PROCEDURE proc5

@sno char(10),@avgscore int OUTPUT AS

IF @sno=NULL

BEGIN

PRINT ?学号不能为空！? RETURN END

SELECT @avgscore=AVG(成绩)

32

from xscj

WHERE 学号=@sno RETURN

9、利用创建的存储过程查询学号为“02020101”的同学的平均成绩。 DECLARE @avgscore int

EXECUTE proc5 ?02020101?,@avgscore OUTPUT SELECT ?His average score is :?,@avgscore

10、定义存储过程proc6，要求实现如下功能：根据某学生的学号查询该生的已修学分，如果已修学分>9，则将已修学分加2分；如果已修学生小于9，则将已修学分加4分。 create proc proc6

@sno char(10),@xxf tinyint output as

declare @yxf tinyint

select @yxf=总学分from dbo.xsqk where 学号=@sno if @yxf>9 update dbo.xsqk set 总学分=总学分+2 where 学号=@sno else

update dbo.xsqk set 总学分=总学分+4 where 学号=@sno

select @xxf=总学分from dbo.xsqk where 学号=@sno

11、利用已经创建的存储过程查询学号为“02020101”的同学修改后的已修学分。 declare @xxf tinyint

exec proc6 '02020101',@xxf output select @Xxf

33

实验十一：触发器（1）

一、 实验目的

通过实验使学生加深对数据完整性的理解，学会创建触发器，会用触发器实现复杂的数据库完整性。

二、 原理解析 1、触发器概述

触发器是一种特殊类型的存储过程，用于保证数据完整性。事实上，它是一组SQL语句。当表中的一行被插入、修改或删除时，触发器被执行。

触发器与一般存储过程有一些区别，例如，存储过程可以直接调用，但是触发器不能被直接调用执行，只能由系统自动激活。触发器主要的用途是维护行级数据的完整性，而不是返回结果。 2、触发器的类型

插入INSERT、更新UPDATE、删除DELETE。 3、在SQL中创建触发器的语法 创建触发器的语法为：

CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] {

{ { FOR | AFTER | INSTEAD OF } { [ 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 ] } ]

34

sql_statement [ ...n ] } }

三、实验内容

（1）创建INSERT类型触发器。 （2）创建DELETE类型触发器。 （3）创建UPDATE类型触发器。 四、实验步骤

（1）在学生成绩库中创建触发器trigger1，实现如下功能：当在学生成绩表（xscj）中插入一条学生选课信息后，自动实现更新该学生在学生情况表（xsqk）中的总学分信息。

分析：根据题意，也即要求在学生成绩表中插入一条记录时，自动更新学生情况表中的相应记录信息。可以通过在学生成绩表中定义INSERT类型的触发器，触发器中语句要完成的功能是更新学生情况表中的相应学生的总学分信息。其实，只要在该生原总学分基础上加上新选课程的学分就可以了。 USE StuScore --切换到学生成绩库 GO

CREATE TRIGGER trigger1 ON xscj --创建触发器trigger_9_1 FOR INSERT AS

--定义变量@xh，表示学号，定义变量@xf，表示学分 DECLARE @xh char(8),@xf tinyint

--从inserted表取出新插入的学生选课记录的学号与学分，分别赋给变量@xh和@xf

SELECT @xh=学号,@xf=学分 FROM inserted

--更新学生情况表中指定学号的学生的总学分，其中ISNULL为函数，用0替换原总学分中的空值 UPDATE xsqk

SET 总学分=ISNULL(总学分 , 0 )+@xf

35

WHERE 学号=@xh GO

（2）创建触发器trigger2，实现当修改学生课程表（xskc）中的数据时，显示提示信息“学生课程表被修改了”。 USE StuScore GO

CREATE TRIGGER trigger2 ON xskc FOR UPDATE AS

PRINT '学生课程表被修改了' GO

（3）创建触发器trigger3，实现当删除学生课程表中某门课程的记录时，对应学生成绩表中所有有关此课程的记录均删除。 USE StuScore GO

CREATE TRIGGER trigger3 ON xskc FOR DELETE AS

DELETE xscj FROM xscj,deleted WHERE xscj.课程号=deleted.课程号 GO

（4）创建触发器trigger4，实现当修改学生课程表（xskc）中的某门课的课程号时，对应学生成绩表（xscj）中的课程号也作相应修改。 USE StuScore GO

CREATE TRIGGER trigger4 ON xskc

36

FOR UPDATE AS

IF update(课程号) BEGIN UPDATE xscj

SET 课程号=(SELECT 课程号 FROM inserted) FROM xscj,deleted

WHERE xscj.课程号=deleted.课程号 END GO

（5）创建触发器trigger5，实现当向学生成绩表（xscj）中插入一条选课记录时，查看该学生的信息是否存在在学生信息表中，如果不存在，则把该学生的基本信息加入到学生信息表中。 use stuscore go

create trigger trigger5 on xscj for insert as

declare @xh char(8)

select @xh=学号from inserted

if not exists(select * from dbo.xsqk where 学号=@xh) insert into dbo.xsqk(学号,姓名) values(@xh,'zhangsan') 测试：

insert into xscj values('30','301',89,2)

（6）创建触发器trigger5，实现当向学生成绩表（xscj）中插入一条选课记录时，调用实验九中创建的存储过程proc5，实现统计该生的平均成绩功能。 存储过程的创建：

CREATE PROCEDURE proc5

37

@sno char(10),@avgscore int OUTPUT AS

IF @sno=NULL BEGIN

PRINT '学号不能为空！' RETURN END

SELECT @avgscore=AVG(成绩) from xscj

WHERE 学号=@sno RETURN

在触发器中调用存储过程： create trigger trigger6 on xscj for insert as begin

declare @xh char(8),@avgscore int select @xh=学号from inserted exec proc5 @xh,@avgscore output select @avgscore end

38

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