2015版《数据库原理实验》指导书

《数据库原理实验》指导书

计算机与通信工程学院

2010年7月

目 录

前言 ................................................................................................................................ 1 实验一 数据库查询 ...................................................................................................... 2 实验二 数据更新 .......................................................................................................... 5 实验三 视图和索引 ...................................................................................................... 6 实验四 数据库保护 ...................................................................................................... 8 实验五 T-SQL编程 .................................................................................................... 10 实验六 存储过程编程 ................................................................................................ 11 实验七 触发器编程 .................................................................................................... 12 实验八 JDBC编程* ................................................................................................. 13 实验九 事务与死锁* .................................................................................................. 14

1 2 3 4 5 6 7 8 9 实验名称 实验1 数据库查询 实验2 数据更新实验 实验3 索引和视图 实验4数据库保护 实验5 T-SQL编程 实验6存储过程 实验7 触发器 实验8 JDBC编程 实验9事务处理 学时 4 2 2 4 4 4 4 4 4 上课时间 6 7 8 10 11 9 12 13 1

前言

（一）本课程的教学目的和要求：

SQL语言是关系数据库的标准语言，是本课程的一个重点。通过上机实验，可以使学生加深对课堂讲授内容的理解，循序渐进地掌握SQL语言的使用；同时，使学生了解和熟悉SQL SERVER 2000开发的环境，逐步掌握编辑、调试、运行程序的方法，初步积累编程经验。 （二）实验步骤：

1．准备好上机所需的程序。手编程序应书写整齐，并经人工检查无误后才能上机，以提高上机效率。对程序中自己有疑问的地方，应作出记号，以便在上机时给予注意。

2．上机输入和调试自己所编的程序。一人一组，独立上机，上机过程中出现的问题，除了是系统的问题以外，一般应自己独立处理。

3．上机结束后，整理出实验报告，实验报告应包括以下内容：

1) 题目；

2) 程序清单（加注释）； 3) 运行结果；

4) 对运行情况所作的分析 5) 本次调试程序取得的经验。 （三）教学重点与难点：

1. 数据库及表的建立与使用 2. 简单查询 3. 连接查询 4. 嵌套查询 5. 数据库更新 6. 视图的定义和查询 7. 安全性控制 8. 事务的建立和使用 9. 存储过程的使用 10.触发器的使用 11.数据转换

1

实验一 数据库查询

课程名称： 实验名称

数据库原理实验 数据库查询

实验类型： 学时

验证型 4学时

实验目的：

使学生掌握SQL Server Query Analyzer的使用方法，加深对SQL和T-SQL语言的查询语句的理解。熟练掌握表的基本查询，连接查询和嵌套查询，以及掌握数据排序和数据分组的操作方法。 实验原理：

SELECT [ALL|DISTINCT] <目标列表达式>[，<目标列表达式>]? FROM <表名或视图名>[，<表名或视图名>]? [WHERE <条件表达式>]

[GROUP BY <列名1> [HAVING <条件表达式>]] [order by <列名2> [ASC|DESC]]；

实验方法：

将查询需求用T-SQL语言表示；在SQL Server Query Analyzer的输入区中输入T-SQL查询语句；设置 Query Analyzer的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。 实验内容：

1. 分别用带DISTINCT和不带DISTINCT关键字的SELELCT在student中进行查询.

2. 查询teacher表中各教师的姓名、教工号及工资按95％发放的信息，并将工资按95％发

放后的列名改为‘预发工资’

3. 查询选修了2学分以上的课程，并且笔试成绩不及格的学生的信息，只需给出学号、课

程号和笔试成绩。

4. 查询学分在4～8之间的课程信息.（用between..and和复合条件分别实现） 5. 从sc表中查询出学生“g9940202”，“g9940204”选择的课程号、学生号以及笔试成绩，

并按笔试成绩降序排列（用in实现）

6. 从teacher表中分别检索出姓王的教师的资料，或者姓名的第2个字是远或辉的教师的

资料

7. 查询每个学生及其选修课情况

8. 以student表为主体列出每个学生的基本情况及其选课情况，如果学生没有选课，只输

出其基本情况（提示：使用外连接）

9. 查询选修dep04_s001号课程且笔试成绩在80分以上的学生信息。（分别用连接，in和

exists实现）

10. 查询所有上“计算机基础”课程的学生的学号、选修课程号以及分数（分别用连接，in

和exists实现）

11. 查询所有“计算机科学与技术”专业学生的学号、选修课程号以及分数（分别用连接，

in和exists实现） 12．查询每个dep_04系学生的总成绩、平均成绩， 仅显示平均成绩及格的学生的记录。（提

2

示使用ISNULL函数，用法见附录2） 13．查询“数据库开发技术”的平均成绩

14．按职称查询教师的平均工资，并按总工资降序排列 15．将笔试成绩转换成A、B、C、D和E五分制显示。（提示使用CASE函数）

附录1：

教务管理数据库jwgl结构

student表结构 列名称 Student_id Student_name Sex Age Class_id Department_id Address Course表 字段名称 Course_id Course_name Book_id credit Total_period Week_period

SC表 字段名称 Course_id Student_id WrittenScore LabScore Teacher表 字段名称 Teacher_id Teacher_name Department_id Profession Sex phone adress

数据类型 Char Varchar Bit Int Char Char varchar 长度 8 8 1 4 6 6 50 允许空值 否 否 否 否 否 否 否 说明 学生学号 学生姓名 性别 年龄 班级号 系编号 家庭住址 数据类型 Char Varchar Char Tinyint Tinyint Tinyint 长度 10 20 15 允许空 否 否 否 否 否 否 说明 课程号 课程名称 书标识 学分 总学时 周学时 数据类型 Char Varchar Tinyint Tinyint 长度 10 8 允许空 否 否 否 否 说明 课程号 学生号 笔试成绩 实验成绩 数据类型 Char nvarchar Char Nvarchar Bit Nvarchar Nchar 长度 9 8 6 16 15 40 3

允许空 否 否 否 否 是 是 说明 教师编号 教师姓名 所在系号 职称 性别 电话 住址

Salary Birth Postalcode Book表 字段名称 Book_id Book_name Author Price Class表 字段名称 Class_id Monitor Class_course表 字段名称 Class_id Course_id Department表 字段名称 Department_id Numeric Smalldatetime Numeric 数据类型 Char Varchar nvarchar Numeric 数据类型 Char Varchar 7.2 6,0 长度 13 30 50 8 5.2 长度 6 8 是 否 是 允许空 否 否 否 是 是 允许空 否 是 工资 出生日期 邮编 说明 书号 书名 出版社 作者 价格 说明 班级号 班主任姓名 Publish_company Varchar 数据类型 Char char 长度 6 10 允许空 否 否 说明 班级号 课程号 数据类型 Char 长度 6 20

允许空 否 否 说明 系编号 系名称 Department_name nvarchar 附录2：ISNULL函数

? 作用：使用指定的替换值替换 NULL

语法：ISNULL ( check_expression , replacement_value ) ? 参数：

1. check_expression 将被检查是否为 NULL 的表达式。 check_expression 可以为任何

类型。

2. replacement_value 当 check_expression 为 NULL 时要返回的表达式。

replacement_value 必须是可以隐式转换为 check_expresssion 类型的类型。 ? 返回类型：

返回与 check_expression 相同的类型。 如果文字 NULL 作为 check_expression 提供，则返回 replacement_value 的数据类型。 如果文字 NULL 作为 check_expression 提供并且未提供 replacement_value，则返回 int。

4

实验二 数据更新

课程名称： 实验名称

数据库原理 数据更新

实验类型： 学时

验证型 2学时

一．实验目的和要求：

掌握用SQL语句实现数据的插入、修改和删除。

二．实验内容：

1．将一个新学生记录（student_id：g9940305、student_name：张红、sex：女、birth：1985-02-08、class_id：g99403,department_id:Dep_01,home_addr:沈阳皇浦区34号）插入到student表中 2．对每一个班，求学生的平均年龄，并把结果存入数据库。（提示：先建立新表“age_avg”，其中一列存放班级，另一列存放相应的学生平均年龄，然后将所需数据存入age_avg） 3．将学号为g9940201的学生的姓名改为张宏，年龄改为18 4．student表中的所有系别(department_id)均改为“dep_03” 5．将班级号(class_id)为g99402的全体学生的笔试成绩加10分 6．删除所有选修了课程号(course_id)为dep04_b001的学生

三．实验报告：

1. 实验各项内容实现的过程。 2. 实验结果分析。

5

实验三 视图和索引

课程名称： 实验名称

数据库原理 视图和索引

实验类型： 学时

验证型 2学时

实验目的：

(1) 掌握视图的定义、查询和更新； (2) 掌握索引的定义；

(3) 掌握利用索引优化查询效率； 实验原理：

1．视图

? 定义视图

CREATE VIEW <视图名> [(<列名> [，<列名>]?)] AS <子查询>

[WITH CHECK OPTION]； ? 删除视图

DROP VIEW <视图名>

2．索引（注意：索引不能修改） ? 定义索引

CREATE [UNIQUE][CLUSTER] INDEX <索引名>

ON <表名> （<列名>[<次序>][，<列名>[<次序>]]?）； ? 删除索引

DROP INDEX <索引名> 实验方法：

将查询需求用T-SQL语言表示；在SQL Server Query Analyzer的输入区中输入T-SQL查询语句；设置 Query Analyzer的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。 实验内容：

在实验1中用到的教务管理数据库jwgl中完成如下问题：

(1) 创建一个名为stud_query_viewl的视图，从数据库JWGL的student表中查询出性别为“男”的所有学生的资料;

(2) 创建一个名为stud_query_view2的视图，从数据库JWGL的student表中查询出性别为“男”的所有学生的学号，姓名，性别，班级，家庭住址，年龄;

(3) 创建一个名为“stud_query__view3”的视图，它是由表course、book及class_course创建的一个显示“g99402”班所开课程的课程名、所用教材的教材名、出版社及作者的视图;

(4) 创建一个从视图stud_query_viewl中查询出班级名为g99402的所有学生的信息； (5) 从视图stud_query_view2中查询出班级为“g99402”、姓名为“张华”的信息;

(6) 向视图stud_query_View2中插入一行数据。学号：g9940209，姓名：赵青，班级：g99402，性别：男，家庭住址：南京中山北路10号，年龄17岁;

(7) 修改视图stud_query_view2中的数据。将stud_query_view2中姓名为“林红”的家庭地址改为“扬州市南京路8号”；

(8) 从视图中stud_query_view2将姓名为“林红”的同学删除；

6

(9) 为表teacher创建一个索引名为teacher_index的惟一性非聚簇索引，索引关键字为teacher_id，升序；

(10) 为表class_course上创建一个名为class_course_index的非聚簇复合索引，索引关键字为class_id,course_id，升序

(11) 假设第（6）小题在插入赵青信息时，希望将赵青所在系“dep_03”也同时插入到数据表中，该如何处理。本题目留作思考题，可自行查找资料完成。

实验报告：

1. 实验各项内容实现的过程。 2. 实验结果分析。

7

实验四 数据库保护

课程名称： 实验名称

数据库原理 数据库保护

实验类型： 学时

设计型 4学时

实验目的：

（1） 使学生掌握E_R图的设计方法，并能将E_R图转换成关系模式； （2） 熟悉SQL DDL和DML语言，能够完成基本的表结构的定义、修改； （3） 掌握数据库完整性约束定义 （4） 掌握数据库安全授权 实验原理：

1. 将E_R图转换为关系的方法：

? 一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的码

就是关系的码

? 一个1:1联系可以转换为一个独立的关系模式，也可以与任意对应的关

系模式合并

? 一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关

系模式合并

? 一个m:n联系转换为一个关系模式

? 三个或三个以上实体间的一个多元联系可以转换为一个关系模式 2. 定义库：

Create DATABASE <数据库名> 3. 表

? 定义表

CREATE TABLE <表名>（<列名><数据类型>[列级完整性约束] [，<列名><数据类型>[列级完整性约束]][，<表级完整性约束>]）； ? 添加列

ALTER TABLE <表名> ADD <列名> <类型>[列级完整性约束] ? 修改列

ALTER TABLE <表名> ALTER COLUMN <列名> <类型>[列级完整性约束] ? 删除列

ALTER TABLE <表名> DROP COLUMN <列名> 4．安全授权 ? 创建登录名

CREATE LOGIN <登录名> WITH PASSWORD=<密码> ? 为当前数据库创建用户

CREATE USER <用户名> FOR LOGIN <登录名> ? 授权

GRANT< 权限>[,<权限>]? [ ON <对象名>]

TO <用户 >[,<用户>]?

8

[WITH GRANT OPTION]; ? 回收权限

REVOKE< 权限>[,<权限>]? [ ON <对象名>]

FROM <用户 >[,<用户>]? [CASCADE];

实验方法：

将查询需求用T-SQL语言表示；在SQL Server Query Analyzer的输入区中输入T-SQL查询语句；设置 Query Analyzer的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。 实验内容：

假设某公司的业务规则如下：

（1）公司下设几个部门，如技术部、财务部、市场部等。

（2）每个部门承担多个工程项目，每个工程项目属于一个部门。 （3）每个部门有多名职工，每一名职工只能属于一个部门。

（4）一个部门可能参与多个工程项目，且每个工程项目有多名职工参与施工，一个职工可以参与多个项目。根据职工在工程项目中完成的情况发放酬金。 （5）工程项目有工程号、工程名两个属性；部门有部门号、部门名称、办公电话和办公地点等属性；职工有职工号、姓名、性别属性，出生日期； 问题：

1．根据上述规则设计E-R模型;

2．将E-R模型转换成关系数据模型； 3．利用SQL语句定义上述关系，要求： （1）为每个关系定义主码和外码；

（2）职工姓名、部门名称不能取空值，并且部门名称是唯一的； （3）职工的性别只能取“男”和“女”，默认值为“男”；

（4）部门号具有类似“B01”的形式，即每个部门号的首字符为“B”，其余两个字符为数字。

(5)酬金不低于2000元。

4．修改部门号的约束，改为“B001”的形式，每个部门号的首字符为“B”，其余三个字符为数字。

5．在原有关系的基础上，增加一个新列，用于记录每名职工参加项目的起始时间。

6．数据库安全性实验

(1)在SQL Server Query Analyzer中建立登录用户u1,u2,u3,u4，并使其成为jwgl数据库的用户。

(2)把对student表的查询权限授予用户u1，并允许其将权限转授给其它用户。验证u1是否获得相应权限。

(3)用户u1将查询student表的权限授予用户u2。验证u2是否获得相应权限。

(4)将对course表的查询权限授予所有用户。

(5)建立用户u5，并验证u4和u5是否获得查询course表的权限。 (6)回收u4查询course表的权限，并验证。

9

实验五 T-SQL编程

课程名称： 实验名称

数据库原理实验 T_SQL编程

实验类型： 学时

综合型 4

实验目的和要求：

1. 掌握用户自定义类型的使用 2. 掌握变量的分类及其使用 3. 掌握各种运算符的使用 4. 掌握各种控制语句的使用

5. 掌握系统函数及用户自定义函数的使用。

实验内容：

1．定义一个函数check_id()，实现如下功能。对于1个给定的department_id之值，查询该值在department表中是否存在，若存在返回0，否则返回-1。

2. 写一段T—SQL脚本程序调用上述函数。当向student表插入1条记录时，首先调用函数check_id()检索该记录的department_id之值在表departments的

department_id字段中是否存在对应值，若存在，则将该记录插入student表中，否则打印错误提示信息。

3. 创建一个名为age()的用户自定义函数，用于根据给定的学生编号计算学生的年龄，然后在查询语句中调用该函数。

4．创建一个名为score()的函数，返回一每个学生的学号、姓名、笔试成绩、实验成绩和总成绩，然后在查询语句中调用该函数查询中成绩不及格的学生。 5．打印乘法口诀表，显示格式如下：

10

实验六 存储过程编程

课程名称： 实验名称

数据库原理实验 存储过程编程

实验类型： 学时

综合型 4

实验目的

掌握存储过程的创建及调用。

实验内容

1、请创建存储过程，查看“计算机基础”课程本次考试平均分以及未通过考试的学员名单。（笔试成绩满分80分，上机成绩满分20分，双科成绩均达到满分的80%以上为优秀，单科不足满分的60%为不合格，否则为合格）。(提示：用带有游标的存储过程)

2、修改上题：由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是满分的60%），这导致考试的评判结果也相应变化。（提示：使用带输入参数的存储过程） 3、修改上题：返回未通过考试的学员人数。（提示：使用带输出参数的存储过程） 4、编写存储过程，显示男生或女生的名单（性别要求通过参数输入）。格式为： 序号 学号 姓名 (提示：用带有游标的存储过程) 11

实验七 触发器编程

课程名称： 实验名称

数据库原理实验 触发器编程

实验类型： 学时

综合型 4

实验目的

理解触发器的工作原理，掌握如何使用inserted表和deleted表及如何创建：INSERT、UPDATE、DELETE触发器。 实验内容

建立银行业务数据库bankdb，其中，帐户信息表(bank)存放帐户的信息，交易信息表(transInfo)存放每次的交易信息。

1、当向交易信息表(transInfo)中插入一条交易信息时，自动更新对应帐户的余额。

2、当删除交易信息表时，要求自动备份被删除的数据到表backupTable中。

3、跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。 实验报告

1、实验各项内容实现的过程。 2、实验结果分析。

12

实验八 JDBC编程*

课程名称： 学时

数据库原理实验 4

实验类型：

综合型

实验目的：

熟悉使用JDBC来进行数据库应用程序的设计，熟悉通过JDBC接口编程访问数据库并对数据库进行操作。 实验要求：

配置环境变量。使用Java语言编程实现对数据库的访问，所有的SQL操作均在自己建立的新库里进行，数据库建议选用教务管理数据库，读者可以选择进行创建、插入、查询、删除和更新等操作。

要求：

（1） 以对学生表的操作为例，程序循环输出如下格式菜单，根据用户的选

择，完成对学生表的各类操作：

1=List ALL; 2=List(By Student_ID);3=Insert;4=Insert; 0=Exit （2）提交源程序并标识必要的注释。保证程序能正确编译和运行，认真填写实验报告。

13

实验九 事务与死锁*

课程名称： 学时

数据库原理实验 4

实验类型：

验证型

实验目的

1．了解事务的ACID属性，掌握事务和批处理的工作原理。 2．了解死锁的类型和其兼容性。 3．了解死锁发生的情况。 4．了解可锁定的资源。

5．了解死锁的一般处理过程。 实验内容：

1.银行业务数据库bankdb，其中，帐户信息表(bank)存放帐户的信息，交易信息表(transInfo)存放每次的交易信息。 (1)试用事务解决银行转帐问题。

参考代码如下： BEGIN TRANSACTION

/*--定义变量，用于累计事务执行过程中的错误--*/ DECLARE @errorSum INT

SET @errorSum=0 --初始化为0，即无错误

/*--转帐：张三的帐户少1000元，李四的帐户多1000元*/ UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='张三' SET @errorSum=@errorSum+@@error

UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName='李四'

SET @errorSum=@errorSum+@@error --累计是否有错误 IF @errorSum<>0 --如果有错误 BEGIN

print '交易失败，回滚事务' ROLLBACK TRANSACTION END ELSE BEGIN

print '交易成功，提交事务，写入硬盘，永久的保存' COMMIT TRANSACTION

14

END GO

print '查看转帐事务后的余额' SELECT * FROM bank GO

(2)试演示：分别转帐1000和800时的转帐情况。注意：转账时不得出现透支。

2．实验内容

1）锁与并行查询执行相关的资源。 2）如何查看进程信息。 实验步骤

打开两个查询窗口，并发执行下面两个事务 2）

--transaction 1 begin tran update student set age=20

where student_name='曹雨'

--阻塞秒钟

waitfor delay '00:00:10'

update sc

set writtenScore=76

where student_id='g9940201' and course_id='dep04_b001' commit;

2）

--transaction 2 begin tran update sc

set writtenScore=76

where student_id='g9940201' and course_id='dep04_b001'

--阻塞秒钟

waitfor delay '00:00:10'

update student set age=20

where student_name='曹雨'

15

commit;

3）处理死锁的一般过程或步骤。

Sp_who:提供关于当前Microsoft SQL Server用户和进程信息。如：列出

全部当前进程。

在SQL查询分析器的命令窗口中输入 sp_who

sp_lock:报告有关锁的信息

在SQL查询分析器的命令窗口中输入

sp_lock @spid1=从sp_who中查出的进程标识号 实验报告

1、实验各项内容实现的过程。 2、实验结果分析。

16

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