数据库应用实验教案.docx
《数据库应用实验教案.docx》由会员分享，可在线阅读，更多相关《数据库应用实验教案.docx（19页珍藏版）》请在冰点文库上搜索。
数据库应用实验教案
课程
实验教案
课程编号：
总学时：
适用年级专业（学科类）：
开课时间
使用教材：
授课教师姓名：
实验二：
数据库定义实验
一、实验目的
掌握在SQLServerQueryAnalyzer中利用CREATE、DROP、ALTER等SQL语句创建和删除数据库，创建、删除、更新基本表。
二、实验内容
1、利用查询分析器创建学生课程数据库（student），保存在学生学号命名的文件夹下。
注意观察数据库文件的后缀名。
2、利用查询分析器在student数据库中，创建基本表学生（Student）、课程（Course）、选课（SC）,各表属性具体描述如下：
学生（学号，姓名，年龄，性别，所在系）；/*学号是主码，姓名取值惟一*/
课程（课程号，课程名，先行课，学分）；/*课程号是主码*/
选课（学号，课程号，成绩）。
/*学号和课程号为主码*/
Student（Snochar（10）,Snamechar（10）,Ssexchar
（2）,Sageint,SdeptVarchar（10））
Course（Cnochar（4）,Cnamechar（20）,Cpnochar（4）,Ccreditint）
SC（Snochar（10）,Cnochar（4）,Gradeint）
要求：
为属性选择合适的数据类型。
定义必要的列级约束。
建库、建表、建立表间联系（表间完整性约束）。
注意：
（1）将建立表SQL语句保存为以表名命名的sql脚本文件。
（2）AllowNull和DefaultValue在定义表中的约束作用是什么？
3、在查询分析器窗体下键入如下命令DROPTABLEStudent及DROPTABLESC命令,运行后观察数据库student中的表有什么变化？
再重新运行student.sql及sc.sql，再观察数据库student中的表有什么变化？
分析DROPTABLEStudent及DROPTABLESC命令的功能。
4、在查询分析器窗体下完成下列操作命令：
（1）在Student表中增加一个新的属性“专业”—Smajorchar（20）;
（2）将Student表中的Sdept字段的长度更改为20；
（3）删除Student表中刚增加的属性“专业”；
（4）删除Student表、SC表和Course表。
三、实验学时
2学时
四、实验设备与环境
Windows2003平台+SQLServer2005系统
实验三：
数据更新实验
一、实验目的
了解企业管理器中数据表数据的输入、修改和删除操作，熟练掌握使用SQL语句向数据库输入数据、修改数据和删除数据的方法。
二、实验内容
学生课程数据库的三个基本表为学生（学号，姓名，年龄，性别，所在系）；课程（课程号，课程名，先行课，学分）；选课（学号，课程号，成绩）。
1、利用企业管理器更新数据
在学生课程数据库的学生、课程和选课３个表中各输入下列表中的记录。
Student表
学号
姓名
性别
年龄
所在系
200215121
李勇
男
20
CS
200215122
刘晨
女
19
CS
200215123
王敏
女
18
MA
200215125
张立
男
19
IS
Course表
课程号
课程名
先行课
学分
1
数据库
5
4
2
数学
2
3
信息系统
1
4
4
操作系统
6
3
5
数据结构
7
4
6
数据处理
2
7
Pascal语言
6
4
SC表
学号
课程号
成绩
200215121
1
92
200215121
2
85
200215121
3
88
200215122
2
90
200215122
3
80
2、使用查询分析器更新数据，写出相应的SQL语句，并查看、分析执行的结果。
（1）使用SQL语句删除Student表、SC表和Course表中的所有数据；
（2）使用SQL语句将上面三个表中的数据添加到Student表、SC表和Course表中；
（3）将Student表中每个学生的年龄都增加1岁；
（4）将SC表中每个学生的每门课程成绩都乘以0.8。
（5）思考：
删除表和删除表中的数据使用的SQL语言有什么不同？
三、实验学时
2学时
四、实验设备与环境
Windows2003平台+SQLServer2005系统
实验四：
数据查询实验
一、实验目的
了解在企业管理器或查询分析器中执行数据查询的方法；掌握SQLServerQueryAnalyzer中简单查询、连接查询、嵌套查询操作方法。
二、实验内容
1、使用企业管理器进行查询（验证性，了解部分）
（1）查询数据表的全部数据
在企业管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，这时将显示该表的全部数据。
（2）使用SELECT语句查询数据表的数据
在企业管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，在打开返回表内容的子窗口中，单击工具栏按钮“显示/隐藏SQL窗格”，把子窗口分为上下两部分，上面部分能输入不同的SQL命令。
执行时单击工具栏按钮“运行”即可。
（3）学习和使用QBE查询
在企业管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，在打开返回表内容的子窗口中，单击工具栏按钮“显示/隐藏条件窗格”，把子窗口分为上下两部分，上面部分能选择QBE查询条件，执行时单击工具栏按钮“运行”即可。
若单击工具栏按钮“显示/隐藏SQL窗格”，则在SQL窗格中会显示自动生成的对应SQL语句。
2、使用查询分析器进行查询（验证性）
（1）查询Student表中所有学生的学号、姓名和性别。
SELECTSno,Sname,Ssex
FROMStudent
（2）可以用“*”来选取数据表的全部列
例如：
查询Student表中所有学生的基本情况。
SELECT*
FROMStudent
（3）在查询结果中增加计算列，还可修改数据列的显示名称。
例如：
查询Student表中所有学生的学号、姓名、性别和出生年份。
SELECTSno学号,Sname姓名,Ssex性别,Year（GetDate（））-Sage出生年份
FROMStudent
（4）使用WHERE子句，可以选择满足条件的部分记录
例如：
查询成绩在85～90分之间的学生情况。
SELECTSno,Cno,Grade
FROMSC
WHEREGrade>=85ANDGrade<=90
（5）使用DISTINCT关键字，可以消除重复记录
例如：
查询有成绩的学生的学号。
SELECTDISTINCTSno
FROMSC
（6）使用IN关键字，选择不连续条件的记录
例如：
查询学生成绩为80或85的学生的学号。
SELECT*
FROMSC
WHEREGradeIN（80,85）
（7）使用谓词LIKE和通配符“%”或“_”，实现模糊查询
例如：
查询姓“张”的学生的基本情况。
SELECT*
FROMStudent
WHERESnameLIKE'张%'
注意：
“%”代表0个或多个字符，“_”代表一个字符。
有的书上说，一个汉字占两个字符，但这里一个汉字只占一个字符位置，这与系统的设置有关。
（8）使用ORDERBY子句，对查询结果进行排序
例如：
查询所有学生的2号课的成绩，并按成绩由高向低排序。
SELECT*
FROMSC
WHERECno=’2’
ORDERBYGradeDESC
（9）使用TOP关键字，选择查询结果的前几条记录
例如：
查询2号课成绩最高的学生记录。
SELECTTOP1*
FROMSC
WHERECno='2'
ORDERBYGradeDESC
注意：
如果2号课成绩存在并列最高，则使用下面的SQL语句。
SELECTTOP1WITHTIES*
FROMSC
WHERECno='2'
ORDERBYGradeDESC
（10）用WHERE子句指定连接条件
例如：
查询所有有2号课程成绩的学生的学号、姓名和成绩。
SELECTStudent.Sno,Sname,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.SnoANDCno='2'
（11）将查询结果存储到表中
例如：
将总分在200分以上的学生的情况（学号、姓名、性别、总成绩）存储到数据表GradeList中，假设数据表GradeList不存在。
SELECTA.Sno,Sname,Ssex,SUM（Grade）ASsumGrade
INTOGradeList
FROMStudentA,SC
WHEREA.Sno=SC.Sno
GROUPBYA.Sno,Sname,Ssex
HAVING（SUM（SC.Grade）>=200）
该语句执行成功后，在企业管理器中可以看到已经创建的数据表GradeList，并将查询结果是存储在该表中了。
（12）将查询结果保存到变量中
例如：
查询学号为200215121的学生1号课的成绩，将其保存到变量Grade中。
DECLARE@GradeINT--定义变量
SELECT@Grade=Grade--给变量赋值
FROMSC
WHERESno='200215121'
PRINT@Grade--显示变量的值
注意：
要将查询结果保存到变量中，只能将查询结果集中第一条记录的值赋给变量。
3、分析设计部分
在学生选课库中实现下列数据查询操作，并写出相应的SQL脚本。
（1）求计算机系学生的学号和姓名
（2）求选修3号课程的学生学号和成绩,结果按成绩降序排列,如成绩同按学号升序排列
（3）求选修课程2成绩在80-90之间的学生学号和成绩,并将成绩乘以0.8输出
（4）求数学或计算机系姓’张’的学生的信息
（5）求缺少了成绩的学生的学号和课程号
（6）查询各门课程及相应的选课人数。
（7）查询总成绩在200分以上的学生的学号、总成绩和平均成绩
（8）在FROM子句中用INNERJOIN连接符指定连接条件查询所有有2号课程成绩的学生的学号、姓名和成绩。
（9）查询学生中年龄相同的学生情况（使用自连接查询）。
（10）查询所有学生的总成绩（包括没有成绩的学生）、学号和姓名（外部连接查询）。
（11）查询某课程成绩在90分以上的学生的学号和姓名（使用谓词IN连接子查询）。
（12）查询有课程成绩的学生的学号和姓名（使用谓词EXISTS连接子查询）。
三、实验学时
2学时
四、实验设备与环境
Windows2003平台+SQLServer2005系统
实验五：
用户权限管理实验
一、实验目的
掌握SQLServer中有关用户、角色及操作权限的设置方法。
二、实验内容
1.用超级用户登录学生数据库student。
2.建立两个新用户:
用户名stu1,密码stu1（登录名login1）；用户名stu2,密码stu2（登录名login2）和用户名stu3,密码stu3（登录名login3）。
3.授予stu1对SC具有select和insert权限,授予stu1对student具有select,update,insert和delete权限,授予stu1对Course具有select,update,insert和delete权限。
4．授予stu2对SC具有select权限,授予stu2对student具有select权限,授予stu2对Course具有select权限；授予stu2对Course具有更新属性Ccredit的权限。
5．测试授权
◆以用户Stu1登录学生数据库：
（1）查询SC、student和Course表中的所有数据；
（2）分别向SC、student和Course表中插入一组合法数据；
（3）分别对SC、student和Course表更改一个元组；
（4）分别对SC、student和Course表删除一个元组。
观察运行结果并分析原因。
◆以用户Stu2登录学生数据库：
（1）分别向SC、student和Course表中插入一组合法数据；
（2）更新Course表中课程号为’1’的课程的名称为’人工智能’；
（3）更新Course表中课程号为’1’的课程的学分为3。
观察运行结果并分析原因。
◆以用户Stu1登录学生数据库：
（1）收回stu1对SC表的select权限，对student表的update和insert权限，对Course表的delete权限；
（2）查询SC、student和Course表中的数据，观察运行结果；
（3）分别向SC、student和Course表中插入一组合法数据，观察运行结果；
（4）分别对SC、student和Course表更改一个元组，观察运行结果；
（5）分别对SC、student和Course表删除一个元组，观察运行结果。
思考Course是否具有delete权限执行效果有何不同？
6．创建数据库角色Myrole，设置访问student表的select和insert权限，并添加用户成员stu3。
以stu3登录查看验证。
7．使用SQL命令完成：
删除登录名login1、login2和login3；删除用户名stu1、stu2和stu3；删除角色Myrole。
三、实验学时
2学时
四、实验设备与环境
Windows2003平台+SQLServer2005系统
实验六：
数据完整性实验
一、实验目的：
掌握使用SQL中的PRIMARYKEY、CHECK、FOREIGNKEY……REFERENCES、NOTNULL、UNIQUE等关键字是现实体完整性、参照完整性及用户定义完整性约束定义。
二、实验步骤：
（一）验证分析部分
1、利用SQL查询分析器用PRIMARYKEY子句保证实体完整性。
在查询分析器窗体下键入如下命令：
CREATETABLEStudent（
snoCHAR（5）NOTNULLUNIQUE,
snameCHAR（8）,
ssexCHAR
（1）,
sageINT,
sdeptCHAR（20），
CONSTRAINTPK_StudentPRIMARYKEY（sno））;
UPDATEStudentSETsno=’‘WHEREsdept=’CS’;
UPDATEStudentSETsno=’200215121‘WHEREsname=’王敏’;
运行并观察结果。
2、利用SQL查询分析器用FOREIGNKEY……REFERENCES子句保证实体完整性。
在查询分析器窗体下键入如下命令：
（1）先删除原来SC表中关于Sno的外键，再将SC的Sno外键修改为：
alterTABLESCaddCONSTRAINTFK_SnoFOREIGNKEY（sno）
REFERENCESStudent（sno）
ONupdateCASCADE;
然后执行以下语句
UpdateStudentSETsno=’200215128‘WHEREsno=’200215121’;
（2）先删除原来SC表中关于Sno的外键，再将SC的Sno外键修改为：
alterTABLESCaddCONSTRAINTFK_SnoFOREIGNKEY（sno）
REFERENCESStudent（sno）
ONdeleteCASCADE;
然后执行以下语句
deletefromstudentWHEREsno=’200215122’;
运行并观察结果。
3、利用SQL查询分析器用短语NOTNULL、UNIQUE、CHECK保证用户定义完整性。
CREATETABLEStudent（
snoCHAR（5）,
snameCHAR（8）CONSTRAINTU1UNIQUE,
ssexCHAR
（1）,
sageINTCONSTRAINTU2CHECKFORsage<=28,
sdeptCHAR（20），
CONSTRAINTPK_StudentPRIMARYKEY（sno））
执行以下SQL语句
Insertintovalues（‘2002101’,’li’,’女’,89,’DS’）
运行并观察结果。
思考：
如果要求sdept输入内容只能为字母该如何约束？
（二）设计分析部分
在Student数据库中，完成以下完整性约束设置。
1、设置Student表中Ssex的取值只能是‘男’或‘女’；
2、设置Student表中Sdept的取值默认为‘CS’；
3、删除操作测试
（1）设置Course表和SC表之间的关系，实现cascadedeleterelatedrecords。
（2）删除Course表中记录，查看SC表中数据的变化（SC表中Cno有与Course表中Cno对应的记录删除和无对应记录的删除都要测试），有什么结果，为什么？
4.修改操作测试
（1）在SC表中，将课程号为3的改为33，都有什么结果？
为什么？
（2）将Course表和sc表的课程号为3的都改为33，请用完整性约束实现？
5.插入数据测试
（1）在STUDENT表中插入一条新纪录：
95006李三男21IS
（2）在SC表中插入一条新纪录：
95008380
插入执行后都有什么结果？
为什么？
如何完成上述操作？
三、实验学时
2学时
四、实验设备与环境
Windows2003平台+SQLServer2005系统
实验七：
触发器设计实验
一、实验目的：
掌握触发器编写方法，能运用设计的触发器实现对数据表中数据完整性约束的控制。
二、实验内容：
要求为Course表编写一个名为CourseTrigger插入触发器，当插入记录到Course表时，该触发器检查新插入记录的userid字段，如果插入这个字段的值不存在于Teacher表中，则从Course表中删除这个新插入记录。
Teacher（useridchar（20）,namechar（20）,departmentchar（20）,age）
Course（courseidint,useridchar（20）,namechar（20）,gradeint）
说明：
1．对表Teacher和Course之间不允许建立外键约束；
2．本实验属于设计型实验，要求学生设计一个符合实验内容要求的触发器。
三、实验学时
2学时
四、实验设备与环境
Windows2003平台+SQLServer2005系统
课外补充：
在Student、Course和SC表中创建以下触发器
（1）创建一个after触发器，在Student表中删除某学生的记录时，删除其相应的选课记录（不允许使用外键级联删除约束）。
（2）创建一个触发器，不允许删除Course表中的任何数据。
实验八、概念数据模型设计实验
一、实验目的：
熟悉PowerDesigner环境，掌握创建概念数据模型的方法。
二、实验内容：
1．设计出版社出版图书管理系统的概念数据模型，其设计CDM图结构如下图所示。
完成下图所示的CDM图，并对图中的实体和标识符进行完善和补充。
2．将实验2中创建的CDM模型转换成SQLSERVER对应的逻辑模型。
3．实验报告中写出完善的E-R图和对应的逻辑模型图。
三、实验学时
2学时
四、实验设备与环境
Windows2003平台+PowerDesigner+SQLServer2005系统
实验九存储过程设计
一、实验目的
掌握使用SQLServer2005平台设计存储过程的方法。
二、实验内容
设计要求：
1、使用STUDENT数据库中的学生表、课程表、选课表，创建一个带参数的存储过程—userproc。
该存储过程的作用是：
当任意输入一个学生的姓名时，将从三个表中返回该学生的学号、选修的课程名称和课程成绩。
2、将姓名为XXX的学生所有课程的成绩减少10分（其中学生姓名为存储过程的参数）。
三、实验学时
2学时
四、实验设备与环境
Windows2003平台+SQLServer2005系统
存储过程设计辅助实验
一实验目的
通过实验掌握SQLSERVER存储过程的基本设计方法。
二、内容和方法
1.创建存储过程步骤
1）写SQL语句
例如：
查询所有学生的记录
select*fromstudent
2）测试SQL语句
执行这些SQL语句。
确认符合要求。
3）若得到所需结果，则创建过程
如果发现符合要求，则按照存储过程的语法，定义该存储过程。
createproceduredem1
as
select*fromstudent
4）执行过程
执行存储过程，验证正确性。
EXECdem1
检查是否已经存在存储过程
例如：
检查是否已经存在spp，如果存在则删除它。
ifexistsSELECTnameFROMsysobjectsWHEREname='spp'ANDtype='P'
dropprocedurespp
2.无参数传递存储过程
执行时，不需要向存储过程传递参数。
先阅读下列程序，并且运行之，如果结果正确，将其写入存储过程SPP1中。
Select*fromsc
WHILE（SELECTAVG（grade）FROMsc）<90
BEGIN
UPDATEsc
SETgrade=grade*1.1;
SELECTMAX（grade）FROMsc;
IF（SELECTMAX（grade）FROMsc）>98
BREAK
ELSE
CONTINUE
END
请注意观察结果。
3.带参数传递存储过程
阅读并试运行下列程序：
createproceduredem1（@sno1char（20））
as
selectavg（grade）,@sno1fromscwheresno=@sno1groupbysno
执行存储过程：
execdem195001---记录结果，查询95002的平均成绩。
实验十数据库管理
一、实验目的
使学生了解SQLServer的数据备份和恢复机制，掌握使用SQLServer的数据库备份和恢复的方法。
二、实验内容
1
升级会员 