数据库实验

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

（本科）

福建工程学院计算机与信息科学系计算机软件与理论教研室

2011年9月

目录

实验一数据定义语言 (1)

实验二SQL Sever中的单表查询 (3)

实验三SQL Serve中的连接查询 (4)

实验四SQL Serve的数据更新、视图 (5)

实验五数据控制（完整性与安全性） (6)

实验六语法元素与流程控制 (8)

实验七存储过程与用户自定义函数 (10)

实验八触发器 (11)

实验一数据定义语言

一、实验目的

1.熟悉SQL Server2000/2005查询分析器。

2.掌握SQL语言的DDL语言，在SQL Server2000/2005环境下采用Transact-SQL实现表

的定义、删除与修改，掌握索引的建立与删除方法。

3.掌握SQL Server2000/2005实现完整性的六种约束。

二、实验内容

1.启动SQL Server2000/2005查询分析器，并连接服务器。

2.创建数据库: （请先在D盘下创建DB文件夹）

1）在SQL Server2000中建立一个StuDB数据库：

有一个数据文件：逻辑名为StuData，文件名为“d:\db\StuDat.mdf”，文件初始大小为5MB，文件的最大大小不受限制，文件的增长率为2MB；

有一个日志文件，逻辑名为StuLog，文件名为“d:\db\StuLog.ldf”，文件初始大小为5MB，文件的最大大小为10MB，文件的增长率为10%

2）刷新管理器查看是否创建成功，右击StuDB查看它的属性。

3.设置StuDB为当前数据库。

4.在StuDB数据库中作如下操作：

设有如下关系表S：S(CLASS,SNO, NAME, SEX, AGE)，

其中：CLASS为班号，char(5) ；SNO为座号，char(2)；NAME为姓名，char(10)，设姓名的取值唯一；SEX为性别，char(2) ；AGE为年龄，int，表中主码为班号+座号。

写出实现下列功能的SQL语句。

(1)创建表S；

(2)刷新管理器查看表是否创建成功；

(3)右击表S插入3个记录：95031班25号李明，男性，21岁；

95101班10号王丽，女性，20岁；

95031班座号为30，名为郑和的学生记录；

(4)将年龄的数据类型改为smallint；

(5)向S表添加“入学时间（comedate）”列，其数据类型为日期型（datetime）；

(6)对表S，按年龄降序建索引（索引名为inxage）；

(7)删除S表的inxage索引；

(8)删除S表；

5.在StuDB数据库中，

（1）按照《数据库系统概论》(第四版)P82页的学生－课程数据库创建STUDENT、COURSE 和SC三张表，每一张表都必须有主码约束，合理使用列级完整性约束和表级完整性。

并输入相关数据。

（2）将StuDB数据库分离，在D盘下创建DB文件夹下找到StuDB数据库的两个文件，进行备份，后面的实验要用到这个数据库。

6.（课外）按照《数据库系统概论》(第四版)P74页习题5的SPJ数据库。创建SPJ数据

库，并在其中创建S、P、J和SPJ四张表。每一张表都必须有主码约束，合理使用列级完整性约束和表级完整性。要作好备份以便后面的实验使用该数据库数据。

三、实验要求：

1.学会使用SQL Server2000/2005的查询分析器，企业管理器和联机丛书。

2.根据实验内容认真写好实验报告，记录每个步骤的脚本和结果的截图。

3.报告内容：实验内容1~5

四、实验总结

实验二SQL Sever中的单表查询

一、实验目的：

1.掌握SQL语言的DML子语言，在SQL Server2000/2005环境下采用Transact-SQL的

SELECT语句熟练掌握各种单表查询方法。

二、实验内容：

(一) 附加上次实验备份的StuDB数据库和SPJ数据库。

(二) 按照《数据库系统概论》P82页中的学生课程数据库及数据实现以下查询：

1.查询所有课程的课号，课程名。

2.查询计算机系年龄不到21岁的所有男生的姓名和出生年份。

3.查询已被学生所选课程的课程号。

4.查询所有有成绩的学生学号和课程号，并为各列分别取相应的中文别名。

(三) 按照《数据库系统概论》P75页中的SPJ数据库及数据实现以下查询：

1.查询地点在‘北京’、‘上海’的供应商代码和供应商名字。

2.查询供应商名中包含‘方’这个汉字的供应商信息。

3.查询零件名以‘刀’字结尾，包含三个汉字的零件信息。

4.查询使用了供应商S1所供应零件的工程项目数。

5.查询各个供应商所供应的零件的总数，要求对查询结果降序显示。

6.查询供应了3个以上项目的供应商代码和供应的项目总数。

三、实验要求：

1.根据实验内容认真写好实验报告，记录每个步骤正确的Transact-SQL命令。

2.无须记录正确的查询结果。但要求记录实验过程中发生的有学习意义的错误及错误信

息。

四、实验小结

实验三SQL Serve中的连接查询

一、实验目的：

1.掌握采用Transact-SQL实现连接查询。

2.掌握等值连接（自然连接）、自身连接、外连接和复合条件连接的查询方法。

二、实验内容：

按照《数据库系统概论》P82页中的学生课程数据库和P75页中的SPJ数据库完成以下查询

(1)查询使用红色零件的工程名称。

(2)查询每个工程的信息及相应的供应信息（包括列出尚未被供应零件的那些工程）。

(3)查询供应工程J1红色零件的供应商号SNO，请用两种方法实现。

(4)求供应工程J1零件的供应商的完整信息。

(5)查询使用北京供应商供应零件的工程信息。

(6)查询选修3号课程且成绩在85分以上的所有学生。

(7)查询先行课的学分为4的课程信息。

(8)查询课程与其间接先行课的名称。

(9)查询其他系中比计算机科学系所有学生年龄都小的学生完整信息，用两种方法实现。

(10)查询其他系中比计算机科学系某一学生年龄大的学生姓名与年龄，用两种方法实现。

(11)（可选）查询至少用了供应商S1所供应的所有零件的工程号JNO，用带EXISTS谓

词的子查询实现。

(12)（可选）查询使用了全部零件的工程号JNO，用带EXISTS谓词的子查询实现。

(13)（可选）查询所有使用了P3零件的工程号JNO，用带EXISTS谓词的子查询实现。

三、实验要求：

1.写出正确的Transact-SQL命令。

2.无须记录正确的查询结果。要求记录实验过程中发生的有学习意义的错误及错误信息。

四、实验小结

实验四SQL Serve的数据更新、视图

一、实验目的：

1.掌握采用Transact-SQL实现嵌套查询。

2.掌握采用Transact-SQL实现数据更新。

3.掌握采用Transact-SQL实现视图的定义、删除、查询与更新。

二、实验内容

按照《数据库系统概论》P82页中的学生课程数据库和P75页中的SPJ数据库完成以下操作：

（一）数据更新

1)插入一个学生：张红，女，200215135，信息系，20岁

2)插入一个选课录：200215135，1，成绩未定

3)将计算机系所有学生的年龄加1岁

4)删除所有200215130学生的选课记录

5)删除所有姓张的同学的信息

6)请自行设计案例对学生－课程数据库的数据更新，并观察是否有违反数据的完整性约

束。

（二）视图

1）创建一个学生成绩统计视图，包括学号，姓名，选课门数，平均分，最高分，最低分2）利用上题所建视图实现：

①查询成绩高于自己平均成绩的选课记录

②查询每个同学获得最高分的选课记录

3）请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。针对该视图完成下列查询：

①找出三建工程项目使用的各种零件代码及其数量；

②找出供应商S1的供应情况；

三、实验要求

1.写出正确的Transact-SQL命令。

2.无须记录正确的查询结果。但要求记录实验过程中发生的有学习意义的错误及错误信

息。

四、实验小结

实验五数据控制（完整性与安全性）

一、实验目的：

1.通过实验加深对数据库完整性与数据库安全性的理解；

2.掌握SQLServer对数据进行安全性控制的方法

二、实验内容和要求

(一)数据库完整性

某公司使用数据库进行内部管理：表Employees存储雇员的代号（整型，标识位，种子1000001，增量为1，主键）、身份证号码（18个字符）、名字（最长20个字符）和工资等信息；表Departments存储部门的部门号（2个字符，唯一，主键）、部门名称（30个字符）等信息；表Work每一行表示某雇员在某部门工作过及其开始工作时间和备注。

请写出创建这三个表的SQL语句，要保证：工资的值大于0，身份证号码唯一，开始工作时间非空，默认值为当前时间。

(二) 以sa登录查询分析器，输入下列代码并执行

第1行exec sp_addlogin 's1','123'

第2行use StuDB

第3行exec sp_grantdbaccess 's1','u1'

第4行grant select,insert,update on student to public

第5行grant all on student to u1

第6行revoke select on student to u1

问：

1）第1行创建了一个名为s1登录帐户,请问'123'的含义是什么？

2）第3行将登录帐户s1映射到数据库用户u1上，它将作为哪个数据库的用户？

3）请解释4到6行的含义

4）在查询分析器中以s1帐户连接服务器，能否对学生－课程数据库的student表进行select 操作，为什么？

（提示：废除权限是删除已授予的权限，并不妨碍用户、组或角色从更高级别继承已授予的权限。因此，如果废除用户查看表的权限，不一定能防止用户查看该表，因为已将查看该表的权限授予了用户所属的角色。）

5) 切换回sa用户的查询窗口，输入下面代码并执行

deny select on student to u1

请问该代码含义？

再切换至s1的查询窗口，能不能对student 表进行查询操作？为什么？

(三)请完成以下操作,并记录完整语句。在实验过程注意验证语句的执行效果。

(1)需要为学生－课程数据库，创建一用户，该用户以“SQL Server身份验证”方式登录SQL Server 服务器的账号为学生自己的姓名，密码为“123”。

(2)给该登录用户映射到数据库用户user2上，并赋予该用户对student表的sno、sname两列的查询权限，对sc表的所有操作权限及对course的查询权限。

（请先执行revoke select on student from public

Revoke all on sc from public

注：新建用户自动被加入到public角色中）

(3)请分析、验证数据库用户user2的权限。

(4)用户user2授予对course表的插入操作权限，并允许将此权限授予其它用户

(5)收回授予user2对sc表的所有权限

(6)创建一个角色R1

(7) 然后使用GRANT语句，使角色R1拥有SC表的SELECT、UPDATE、INSERT

(8)将这个角色授予user2。使它具有角色R1所包含的全部权限。

三、实验要求：

1)写出正确的Transact-SQL命令。

2)无须记录正确的查询结果。要求记录实验过程中发生的有学习意义的错误及错误信息。

四、实验小结

实验六语法元素与流程控制

一、实验目的：

1)掌握Transact-SQL局部变量的声明、赋值和使用方法以及全局变量的运用方法，掌握Transact-SQL的局部变量的作用域概念。

2)掌握Transact-SQL的控制流语言的使用方法。

3)掌握与控制流语言语句一起使用的其它Transact-SQL 语句：

CASE、/*...*/（注释）、--（注释）、PRINT。

二、实验内容和要求

1、使用局部变量、全局变量

(1)定义一个int的整型变量，为其赋值45，并显示变量的值。

(2)定义一个长度为20的可变长度型字符变量，为其赋值“Welcome to FJUT”，并显示变量的值。

(3)在批处理中声明两个局部变量＠ssex和@sdept并对它们赋值，对Student表进行查询，要求两局部变量在SELECT 语句的WHERE 子句中被使用，查询的要求是性别为‘女’或所在系为‘IS’系的那些学生。

(4)查询当前数据库服务器名。

(5)查询当前数据库管理系统版本。

(6)在student表中执行所有学生年龄加1岁语句，并用@@ROWCOUNT 来检测是否有发生更改的行。

2．函数的使用

(1) 数学函数的使用

分别用函数求出-3的绝对值，16的平方根，5的三次方。

(2) 字符串函数的使用

1）分别将字符串'china'、'MACHINE'转换成大写、小写字母；

2）去掉字符串' machine '左边的空格，右边与'press'连接起来；

(3) 日期、时间函数的使用

1）返回系统当前日期并以整数形式返回当前日期的年份、月份、日；

2）返回给定日期“2006-2-21”与当前日期相差的天数。

(4) 系统函数与元数据函数的使用

显示正在使用的用户名

3．编写较复杂的Transact-SQL程序

1)在SELECT 语句中，CASE 搜索函数允许根据比较值在结果集内对值进行替换。要求仿照《数据库概论》的sc表，根据学生的成绩范围将成绩（grade 列）显示为文本注释。（要求：＜60分，文本注释为“不及格”，60－70，“及格”，70－80，“中”，80－90，“良”，90－100，“优”）。

2)用Transact－SQL语言编写程序计算1～100之间所有能被7整除的数的总和。

3)自选实例，实践并掌握Transact-SQL以下控制流语言的使用方法：

BEGIN...END、WHILE、BREAK、CONTINUE、IF...ELSE、WAITFOR

4)自选实例，实践并掌握以下Transact-SQL语句：/*...*/（注释）、--（注释）、PRINT

三、实验要求：

1)写出正确的Transact-SQL命令。

2)无须记录正确的查询结果。要求记录实验过程中发生的有学习意义的错误及错误信息。报告书写内容：内容1的脚本及1（4）的结果；内容2的脚本及结果；内容3（1）（2）的脚本。

四、实验小结

实验七存储过程与用户自定义函数

一、实验目的：

1)掌握SQLServer中存储过程的使用方法。

2)掌握SQLServer中用户自定义函数的使用方法。

二、实验内容和要求

(一)存储过程

1、对学生课程数据库，编写2个存储过程，分别完成下面功能：

1）统计某一门课的成绩分布情况，即按照各分数段统计人数，要求使用游标。

2）将学生选课成绩从百分制改为等级制（即A、B、C、D、E五级）。

2、对SPJ数据库，

1）创建一个存储过程ins_s_count，功能为根据提供的供应商号，供应商名，供应商所在地等信息，往S表中插入数据，并返回插入该记录之后，S表中的记录数。

2）调用该存储过程实现往S表中插入一条记录(…S6?,?天盛?,‘40’‘福州?)，并显示插入该记录之后，S表中的记录数。

(二)用户自定义函数

1.创建一个返回标量值的用户定义函数RectangleArea：输入矩形的长和宽就能计算矩形的面积。调用该函数。

2.创建一个用户自定义函数，功能为产生一张有关学生成绩统计的报表。该报表显示每一门课程的课程号、课程名、选修人数、本门最高分、最低分和平均分。调用这个函数，生成相应的报表并给用户浏览。

三、实验报告书写要求

实验内容的脚本。

四、实验小结

实验八触发器

一、实验目的

掌握SQLServer中触发器的使用方法。

1)实验内容

按照《数据库系统概论》的SPJ关系模式实现以下操作：

1、创建一个作用在P表上的的触发器P_Ins，确保用户在插入或更新数据时所提供的WEIGHT值是大于0的，否则给出错误提示并回滚此操作。请测试该触发器。测试方法自定。

2、创建一个作用在J表上的触发器J_Update，禁止同时修改项目的名称和所在城市，并进行相应的错误提示。请测试该触发器。测试方法自定。

3、首先在数据库中使用SELECT INTO 语句从SPJ、J、P表中创建一个新表J_P_QTY。表中的属性列包括：工程号、工程名、零件号、零件名及总数量，其中总数量的值是各工程使用各种零件的数量总和（不考虑零件是由哪个供应商提供的）。然后创建一个作用在SPJ表上的Insert型触发器，该触发器能完成两项任务：

1）当用户插入的JNO使用的PNO的值在J_P_QTY中不曾存在时禁止插入；

2）每当在SPJ表中插入一条合理记录时，使J_P_QTY表的QTY列的值保持同步更新。请测试该触发器。测试方法自定。

三、实验要求：

1)记录完整语句。在实验过程注意验证语句的执行效果。

2)记录实验过程中发生的有学习意义的错误及错误信息。

四、实验小结