数据库上机实验指导.docx
《数据库上机实验指导.docx》由会员分享，可在线阅读，更多相关《数据库上机实验指导.docx（18页珍藏版）》请在冰豆网上搜索。
数据库上机实验指导
数据库原理实验指导书
实验1数据库模式设计及建立
一、实验目的：
（1）掌握数据库模式设计，依据实际要求设计表结构，建立表的关系;比较SQL命令方式和可视化环境管理器方式设计的异同点。
（2）掌握SQLServer查询分析器的使用方法，加深对SQL和SQL语言的查询语句的理解。
熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。
二、实验内容：
1、分别用sql查询分析器和企业管理器创建表，并设定相应的约束。
要求：
在学生管理数据库XSGL中创建如下三个表。
创建名为student（学生信息）的表，表中的各列要求如下：
字段名称
字段类型
大小
说明
Sno
Char
10
主键
Sname
Char
8
Sex
Char
2
默认值为男，只能输入男和女
Sage
Int
Sdept
Char
20
创建名为course（课程信息）的表，表中的各列要求如下：
字段名称
字段类型
大小
说明
Cno
Char
10
主键
Cname
Char
30
唯一性
Ccredit
Real
创建名为score（学生成绩）的表，表中的各列要求如下：
字段名称
字段类型
大小
取值范围
说明
Sno
Char
10
数据来自学生信息表
主键
Cno
Char
10
数据来自课程信息表
主键
Grade
Real
0--100
2、创建score–>student表，以及score–>course表的外部键。
3、增加、修改和删除字段，要求：
1）给student表增加一个memo（备注）字段，类型为varchar（200）。
2）将memo字段的类型修改为varchar（300）。
3）删除memo字段。
4、简单查询操作。
该实验包括投影、选择条件表达，数据排序，使用临时表等。
具体完成以下题目，将它们转换为SQL语句表示，在学生选课数据库中实现其数据查询操作。
1）求数学系学生的学号和姓名
2）求选修了课程的学生学号
3）求选修课程号为‘C1’的学生号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。
usestudent;
selectStudent.Sno,Grade
fromSC,Student
whereCno='3'andSC.Sno=Student.Sno
orderbyGradeasc,student.Snodesc
4）求选修课程号为‘3’且成绩在80~90之间的学生学号和成绩，并成绩乘以0.8输出。
usestudent;
selectdistinctStudent.Sno,Grade*0.8
fromSC,Student
whereCno='3'andstudent.Sno=SC.Snoand
grade>80andgrade<90
5）求数学系或计算机系姓张的学生的信息。
usestudent;
selectSno,Sname,Sage,Sdept
fromStudent
whereSnamelike'张%'and（Sdept='MA'orSdept='CS'）
6）求缺少了成绩的学生的学号和课程号
usestudent;
selectSno
fromSC
whereGradeisNULL
5、连接查询操作。
该实验包括等值连接、自然连接、求笛卡儿积、一般连接、外连接、内连接、左连接、右连接和自连接等。
1）查询每个学生的情况以及他所选修的课程
usestudent;
selectStudent.Sno,Ssex,Sname,Sage,Grade
fromStudentleftJOINSCON（Student.Sno=SC.Sno）
2）求学生的学号、姓名、选修的课程及成绩
usestudent;
selectStudent.Snoas'学号',Snameas'姓名',
Cnameas'选修的课程',Gradeas'得分'
fromSC,Student,Course
whereStudent.Sno=SC.SnoandSC.Cno=Course.Cno
3）求选修课程号为‘C1’且成绩在90以上的学生学号、姓名和成绩
usestudent;
selectStudent.Snoas'学号',Snameas'姓名',
Cnameas'选修的课程',Gradeas'得分'
fromSC,Student,Course
whereStudent.Sno=SC.SnoandSC.Cno=Course.Cno
andSC.Cno=3andGrade>80
4）查询每一门课程的间接先行课（即先行课的先行课）
usestudent;
selecta.Cnoas'课程号',a.Cnameas'课程',b.Cnameas'先行课'
fromCoursea,Courseb
wherea.Cpno=b.Cno
注意：
如果要是修改为NULL可按Ctrl+0
三、实验方法
将查询需求用SQL语言表示；在SQLServer查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。
实验2数据库的复杂查询实验
一．实验目的
本实验的目的是使学生进一步掌握SQLServer查询分析器的使用方法，加深SQL语言的嵌套查询语句的理解，熟练掌握数据查询中的分组、统计、计算和集合的操作方法。
二．实验内容
1、在SQLServer查询分析器中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。
具体完成以下例题。
将它们用SQL语句表示，在学生选课中实现其数据嵌套查询操作。
1）求选修了数据库的学号和姓名
usestudent;
selectSname,Cname
fromSC,Student,Course
whereSC.Cno=Course.CnoandStudent.Sno=SC.Sno
andCname='信息系统'
2）求3课程的成绩高于刘晨的学生学号和成绩
usestudent;
selectSname,Student.Sno,Grade
fromSC,Student
whereStudent.Sno=SC.Sno
andCno='3'andGrade>（
selectGrade
fromSC,Student
whereStudent.Sno=SC.sno
andSnamelike'刘晨'andCno='3'）
3）求其他系中比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）
usestudent;
selectdistinctSname,Sno,Sdept,Sage
fromStudent
whereSdept<>'CS'andSage<（
selectmax（Sage）
fromStudent
whereSdept='CS'
）
4）求其他系中比计算机系学生年龄都小的学生
usestudent;
selectSname,Sage
fromStudent
whereSage>any（
selectSage
fromStudent
whereSdept='CS'
）andSdept<>'CS'
5）求选修了C2课程的学生姓名
usestudent;
selectdistinctStudent.Sno
fromStudent,SC
where
exists（
select*
fromSC
whereStudent.Sno=SC.SnoandCno='3'）
6）求没有选修3课程的学生姓名
usestudent;
selectdistinctStudent.Sno
fromStudent,SC
wherenot
exists（
select*
fromSC
whereStudent.Sno=SC.SnoandCno='3'）
7）查询选修了全部课程的学生姓名
usestudent;
selectSno
fromSC
groupbySnohavingcount（*）>=3
8）求至少选修了学号为“95002”的学生所选修全部课程的学生学号和姓名
usestudent;
selectSname,Sno
fromStudent
wherenotexists（
select*
fromSCSCY
whereSCY.Sno='95002'and
notexists（
select*
fromSCSCZ
whereSCZ.Sno=Student.SnoandSCZ.Cno=SCY.Cno
）
）
2、进行分组查询，包括分组条件表达、选择组条件表达的方法；进行函数查询，包括统计函数和分组统计函数的使用方法；进行集合查询，包括并操作UNION、交操作INTERSECT和差操作MINUS的使用方法。
具体完成以下例题，将它们用SQL语句表示，在学生选课中实现其数据查询操作。
1）求学生的总人数
usestudent;
selectcount（distinctSno）
fromStudent
2）求选修了课程的学生人数
usestudent;
selectcount（distinctSno）
fromSC
3）求课程和选修了该课程的学生人数
usestudent;
selectCno,count（*）
fromSC
groupbyCno
4）求选修超过2门课的学生学号
usestudent;
selectCno,count（*）
fromSC
groupbyCno
havingcount（*）>2
5）查询计算机科学系的学生及年龄不大于20岁的学生
usestudent;
selectSno,Sname
fromStudent
whereSdept='CS'andSage<20
6）查询计算机科学系的学生与年龄不大于20岁的学生的交集
usestudent;
selectSno,Sname
fromStudent
whereSdept='CS'andSage<20
7）查询计算机科学系的学生与年龄不大于20岁的学生的差集
usestudent;
selectSno,Sname
fromStudent
whereSdept='CS'andSage>20
8）查询选修课程‘C1’的学生集合与选修课程‘C2’的学生集合的交集
usestudent;
selectSno
fromSC
whereCno='1'andSnoin（selectSno
fromSC
whereCno='2'）
9）查询选修课程‘C1’的学生集合与选修课程‘C2’的学生集合的差集
usestudent;
selectstudent.Sno,Sname
fromSC,Student
wherestudent.sno=sc.snoandCno='1'andsc.Snonotin（selectSno
fromSC
whereCno='2'）
三．实验步骤
将查询需求用SQL语言表示：
在SQLServer查询分析器的输入区中输入SQL查询语句：
设置查询分析器的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。
实验3数据库的更新和视图定义及维护实验
一、实验目的
本实验的目的是要求学生熟练掌握使用SQL和通过SQLServer企业管理器向数据库输入数据、修改数据和删除数据的操作，同时使学生掌握视图的定义与维护操作，加深对视图在关系数据库中的作用的理解。
二、实验内容
1、具体完成以下例题。
将它们转换为SQL语句表示，在学生选课库中实现其数据更新操作。
1）将一新学生记录（学号：
95050；姓名：
陈冬；性别：
男；年龄：
18；所在系：
计算机科学系）插入学生表中。
insert
intostudent（sno,sname,ssex,sdept,sage）
values（'95007','陈东','男','is',18）
2）插入一条选课记录（‘95020’,’C1’）。
insert
intoSC
values（'95007','2',NULL）
3）对每一个系，求学生的平均年龄，并把结果存入数据库。
对于这道题，首先要在数据库中建立一个有两个属性列的新表，其中一列存放系名，另一列存放相应系的学生平均年龄。
CREATETABLEDeptage（所在系CHAR（20），平均年龄SMALLINT）;
然后对数据库的学生表按所在系分组求平均年龄，再把所在系和平均年龄存入新表中。
usestudent;
createtableav_age（sdeptchar（15）,
ageint）;
insertintoav_age（sdept,age）
selectSdept,avg（Sage）
fromstudent
groupbySdept
3）将学生95001的年龄改为22岁。
usestudent;
updatestudent
setSage=22
whereSno=95001
4）将所有学生的年龄增加一岁。
usestudent;
updatestudent
setSage=Sage+1
5）将计算机科学系全体学生的成绩置零。
usestudent;
updateSC
setGrade=0
where'CS'=（selectSdept
fromstudent
wherestudent.Sno=SC.Sno）
6）删除学号为95006的学生记录
usestudent;
deletefromstudent
whereSno='95006'
7）删除所有的学生选课记录
usestudent;
deletefromSC
8）删除计算机科学系所有学生的选课记录
usestudent
deletefromSC
where'CS'=（selectSdept
fromstudent
wherestudent.Sno=SC.Sno）
2、具体完成以下例题。
1）建立信息系学生的视图
createviewCS_stu
as
selectSname,Sdept
fromstudent
whereSdept='CS'
2）建立信息系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生。
createviewIS_stu
as
selectSname,Sdept
fromstudent
whereSdept='IS'
withcheckoption
3）建立信息系选修了’C1’号课程的学生视图
createviewIS_student
as
selectStudent.Sno,Sname,Sdept
fromstudent,SC
whereSdept='IS'andstudent.Sno=SC.Sno
withcheckoption
4）建立信息系选修了’C1’号课程且成绩在90分以上的学生视图
createviewIS_student
as
selectStudent.Sno,Sname,Sdept,Grade
fromstudent,SC
whereSdept='IS'andstudent.Sno=SC.SnoandGrade>90
withcheckoption
5）定义一个反映学生出生年份的视图
createviewBIR_stu（Sno,Sname,Sbir）
as
selectSno,Sname,2011-Sage
fromstudent
三、实验步骤
将查询需求用SQL语言表示；在SQL查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。
实验四存储过程和触发器的使用
一、实验目的：
本实验要求学生应了解存储过程和事务的作用，基本掌握存储过程和事务的创建和使用方法，要求学生了解触发器的知识与作用，基本掌握触发器的创建方法。
要求学生在已建好的学生管理数据库中创建3个分别用于插入、删除和更新的触发器。
二、实验内容：
1、存储过程例题
（1）创建一个名为“proc_1”的存储过程，用于查看学生表的所有信息。
然后调用该存储过程。
createprocedureproc_1
as
begin
select*fromstudent
end
execproc_1
（2）创建一个名为“proc_2”的存储过程，用于向学生表的所有字段添加一条记录，记录内容由调用时决定。
然后调用该存储过程。
createprocedureproc_2（@numint,@namevarchar（20）,@sexvarchar（20）,@ageint,@deptvarchar（20））
as
begin
insertintostudent
values（@num,@name,@sex,@age,@dept）
end
execproc_295008,李冰,男,19,CS
（3）创建一个名为“proc_3”的存储过程，用于删除学生表中指定学号的记录，具体学号由调用时决定。
然后调用该存储过程。
createprocedureproc_3（@numvarchar）
as
begin
delete
fromstudent
whereSno=@num
end
execproc_395008
（4）修改存储过程“proc_4”，用于查询不小于指定成绩的学生的基本信息，具体成绩由调用时决定。
\
createprocedureproc_5（@gradevarchar）
as
begin
selectsname
fromstudent,SC
whereSC.Sno=student.SnoandGrade>@grade
end
execproc_650
2、触发器例题
（1）创建一个名为“trig_1”的触发器，当向学生表添加记录时，该触发器自动显示学生表的所有信息。
createtriggertrig_1onstudent
forinsert
as
begin
select*fromstudent
end
insertintostudent
values（95008,'李冰','男','18','CS'）
（2）创建一个名为“trig_2”的触发器，当试图向学生表添加、修改或删除记录时，该触发器自动显示如下信息：
“对不起，你无权进行更新操作！
”。
createtriggertrig_2onstudent
forinsert,update,delete
as
begin
print'对不起，你无权进行更新操作！
'
rollback
end
（3）创建一个名为“trig_3”的触发器，当向学生表删除记录时，该触发器自动删除成绩表中与之相关的所有记录。
createtriggertrig_3onstudent
fordelete
as
begin
delete
fromSC
whereSnoin（selectSno
fromdeleted）
end
（4）创建一个名为“trig_4”的触发器，当向成绩表添加记录时，该触发器自动显示与该记录相关的学生的学号、姓名和班级。
实验五安全管理
一、实验目的
本实验要求学生掌握SQLServer2000三级安全体系及验证模式的设置，掌握SQLServer2000服务器登录帐号及数据库用户帐号的管理，掌握创建用户自定义数据库角色并为其授权，掌握为数据库用户授权的方法
二、实验内容
1、创建SQLServer服务器登录账号
操作步骤：
展开SQLServer服务器，右击安全性节点下的“登录”，选择“新建登录“，在弹出的“登录属性”对话框中，选择一个windows用户，单击“确定”按钮，就可以创建一个登录账号。
2、创建数据库用户：
为“学生选课”数据库添加用户:
zhang。
操作步骤：
选择“学生选课”数据库，右击“用户”节点，选择“新建数据库用户”，在弹出的“数据库用户属性—新建用户”对话框中，在“登录名”右边的下拉列表框中选择一个登录名，可以在“用户名”后的文本框输入数据库用户名。
数据库用户名可以和选中的登录名相同，也可以不同。
3、创建数据库角色：
为“学生选课”数据库创建一用户自定义数据库角色s_c_reader,该角色的权限是可以查询学生表及选课成绩表的信息。
操作步骤：
展开“学生选课”数据库，右击“角色”节点，选择“新建数据库角色”，弹出的“数据库用户角色—新建角色”对话框，在“名称”下面的文本框输入数据库角色的名称，单击“确定”按钮即可。
可以将数据库用户添加到数据库角色中。
为数据库角色授权：
选中一个具体的数据库角色，单击鼠标右键，选择“属性”，在弹出的“数据库角色属性”对话框中，单击“权限”按钮，在弹出的对话框中设置数据库角色的权限。
4、数据库对象操作权限的授予和回收：
为数据库用户zhang授予学生表的增、删、改、查权限。
操作步骤：
选择“学生选课”数据库中的“表”节点，选择“学生”表，单击鼠标右键，选择“所有任务”中的“管理权限”命令，在弹出的对话框中设置各个数据库用户的操作权限。
升级会员 