数据库原理实验指导书计科.docx
《数据库原理实验指导书计科.docx》由会员分享，可在线阅读，更多相关《数据库原理实验指导书计科.docx（16页珍藏版）》请在冰豆网上搜索。
数据库原理实验指导书计科
辽东学院自编教材
《数据库原理》实验指导书
高素春编
（计算机科学与技术专业用）
信息技术学院
2014年9月
实验一数据定义与数据更新
实验类型：
验证实验课时：
2指导教师：
高素春
时间：
2014年12月16日课次：
第1-2节教学周次：
第15周
实验分室：
3#101实验台号：
C05实验员：
一、实验目的
1.熟悉SQLServer开发环境，了解各常用工具的使用。
2.掌握数据库服务器的本机注册，熟悉网络中其他数据库服务器的注册。
3.掌握数据库的创建、删除。
4.熟练掌握命令方式下表的创建、修改与删除。
5.熟练掌握命令方式下表中数据的插入、修改与删除。
6.熟练掌握命令方式下索引的创建与删除。
二、实验内容与要求
1.创建数据库
要求：
（1）在默认路径下，创建的数据库名为你的姓名（拼音缩写），写出默认路径。
C:
\ProgramFiles\MicrosoftSQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\
（2）在E:
\，以你的班级姓名学号命名的文件夹中创建数据库。
2.定义基本表
要求：
（1）在建好的数据库中用命令方式创建四个关系，分别为学生关系s、教师关系t、课程关系c、选课关系sc。
认真分析各个关系，设置主键、外键，要求属性的数据类型、长度设置要合理。
（2）学生关系的关系模式为：
s（sid,sname,ssex,sage,sdept），各字段分别代表学号、姓名、性别、年龄、所在系。
sid为主键。
（3）教师关系的关系模式为：
t（tid,tname,title），各字段分别代表教师号，教师名，职称。
tid为主键。
（4）课程关系的关系模式为：
c（cid,cname,credit,tid），各字段分别代表课程号、课程名、学分，教师号。
cid为主键，tid为外键。
（5）选课关系的关系模式为：
sc（sid,cid,score），各字段分别代表学号、课程号、成绩。
sid和cid为复合主键，sid和cid为外键。
（6）将建表命令存成脚本文件，可存为一个文件也可存为独立的多个脚本文件。
（7）将定义基本表的命令写在空白处。
createtables（
sidchar（10）primarykey,
snamechar（10）,
ssexchar
（2）,
sageint,
sdeptchar（30）
）
select*froms
createtablet（
tidchar（10）primarykey,
tnamechar（10）,
titlechar（30）
）
select*fromt
createtablec（
cidchar（10）primarykey,
cnamechar（10）,
creditint,
tidchar（10）,
foreignkey（tid）referencest（tid）
）
select*fromc
createtablesc（
sidchar（10）foreignkeyreferencess（sid）,
cidchar（10）foreignkeyreferencesc（cid）,
scoreint,
primarykey（sid,cid）
）
select*fromsc
3.修改基本表
要求：
（1）向s表增加“入学时间”列，其数据类型为日期型。
altertables
adds_entrancedate
（2）增加课程名称必须取唯一值的约束条件。
altertablec
addunique（cname）
（3）增加学生年龄取值范围在15到40岁之间的约束条件。
altertables
addconstraintsage_chkcheck（sagebetween15and40）
（4）将c表中cname属性列长度加宽到50个字符。
altertablec
altercolumncnamevarchar（50）
（5）删除s表中“入学时间”列。
altertables
dropcolumns_entrance
（6）删除学生年龄取值范围在15到40岁之间的约束。
altertables
dropconstraintsage_chk
4.删除基本表
要求：
删除sc表。
说明：
为方便下面的步骤，删除后再创建sc。
droptablesc
5.插入数据
要求：
在建好的4个表s,t,c,sc中各插入几条记录，内容如下。
用命令方式写出插入数据的相关命令。
s
sid
sname
ssex
sage
sdept
0009001
王华
女
18
计算机科学
0009002
马强
男
20
计算机科学
0009003
关心
女
22
数学
0009004
王珍一
女
19
国际贸易
0009005
关勇
男
21
t
tid
tname
title
t001
刘力
副教授
t002
张勇
讲师
t003
张力英
副教授
t004
方佳林
讲师
c
cid
cname
credit
tid
c001
高等数学
2
t001
c002
Java
4
t003
c003
C
3
t003
c004
数据库
4
t004
c005
经济学
4
t002
sc
sid
cid
score
0009001
c001
91
0009001
c002
50
0009001
c003
50
0009001
c004
0009002
c001
92
0009002
c002
95
0009002
c003
55
0009002
c004
0009003
c001
95
0009004
c001
92
0009004
c005
91
insertintosvalues（'0009001','王华','女',18,'计算机科学'）
insertintosvalues（'0009002','马强','男',20,'计算机科学'）
insertintosvalues（'0009003','关心','女',22,'数学'）
insertintosvalues（'0009004','王珍一','女',19,'国际贸易'）
insertintosvalues（'0009005','关勇','男',21,null）
insertintotvalues（'t001','刘力','副教授'）
insertintotvalues（'t002','张勇','讲师'）
insertintotvalues（'t003','张力英','副教授'）
insertintotvalues（'t004','方家林','讲师'）
insertintocvalues（'c001','高等数学',2,'t001'）
insertintocvalues（'c002','Java',4,'t003'）
insertintocvalues（'c003','C',3,'t003'）
insertintocvalues（'c004','数据库',4,'t004'）
insertintocvalues（'c005','经济学',4,'t002'）
insertintoscvalues（'0009001','c001',91）
insertintoscvalues（'0009001','c002',50）
insertintoscvalues（'0009001','c003',50）
insertintoscvalues（'0009001','c004',null）
insertintoscvalues（'0009002','c001',92）
insertintoscvalues（'0009002','c002',95）
insertintoscvalues（'0009002','c003',55）
insertintoscvalues（'0009002','c004',null）
insertintoscvalues（'0009003','c001',95）
insertintoscvalues（'0009004','c001',92）
insertintoscvalues（'0009004','c005',91）
6.修改数据
要求：
（1）将“马强”的系改为“数学”。
updates
setsdept='数学'
wheresname='马强'
（2）将所有学生的成绩提高到原来的1.1倍。
updatesc
setscore=1.1*score
（3）将所有学生的年龄加1岁。
updates
setsage=1+sage
（4）将选修了“c001”课程的学生的成绩提高5%。
updatesc
setscore=0.05*score
wherecid='c001'
7.删除数据
要求：
（1）删除学号为“0009002”的学生记录。
deletefromsc
deletefroms
wheresid='0009002'
（2）删除所有学生选课记录。
deletefromsc
说明：
为了方便以后的操作，数据删除后再重新插入数据。
8.创建索引
要求：
（1）为s表建立索引，按学号升序建唯一索引。
createuniqueindexunq_ind_sidons（sid）
（2）为sc表建立索引，按学号升序和课程号降序建唯一索引。
createuniqueindexunq_ind_sidcidonsc（sidasc,ciddesc）
9.删除索引
要求：
删除为s表按学号升序创建的唯一索引。
dropindexs.unq_ind_sid
成绩
教师签字：
批改日期：
实验二数据查询
实验类型：
设计实验课时：
4指导教师：
高素春
时间：
2014年12月23日课次：
第1-4节教学周次：
第16周
实验分室：
3#101实验台号：
C04实验员：
一、实验目的
1.掌握查询的基本语法格式。
2.熟练掌握单表查询。
3.掌握连接查询。
4.熟悉嵌套查询。
二、实验内容与要求
要求：
对实验一所创建的数据库中的四个表：
s、t、c、和sc，用SQL完成下面的查询并将SQL语句写在空白处。
1.简单查询
（主要是单表查询）
（1）查询职称是副教授的教师号与姓名。
selecttname,tid
fromt
wheretitle='副教授'
（2）查询全体学生的姓名及其出生年份，出生年份用别名“birth_year”标识。
selectsname,'birth_year:
',2014-sage
froms
（3）查询选修了课程的学生学号。
selectsid
fromsc
（4）查询考试成绩有不及格的学生的学号。
selectsid
fromsc
wherescore<60
（5）查询年龄不在20－25岁之间的学生学号、姓名和年龄。
Selectsid,sname,sage
Froms
Wheresagenotbetween20and50
（6）查询计算机科学系、国际贸易系、数学系学生的学号、姓名和性别。
Selectsid,sname,ssex
Froms
Wheresdeptin（'计算机科学','国际贸易','数学'）
（7）查询所有姓王的学生的姓名、学号和性别。
Selectsname,sid,ssex
Froms
Wheresnamelike'王%'
（8）查询名字中第2个字为“力”字的教师号、教师姓名。
Selecttid,tname
Fromt
Wheretnamelike'_力%'
（9）查询所有不姓张的教师信息。
Select*
Fromt
Wheretnamenotlike'张%'
（10）查询有多少学生专业不确定。
Selectcount（*）
Froms
Wheresdeptisnull
（11）查询没有考试成绩的学生的学号和课程号。
Selectsid,cid
Fromsc
Wherescoreisnull
（12）查询选修了课程的学生人数。
SelectCOUNT（distinctsid）
Fromsc
（13）查询平均成绩大于60分的课程号和平均成绩。
selectcid,AVG（score）
fromsc
groupbycid
havingAVG（score）>60
（14）查询选修了3门以上课程的学生学号。
Selectsid
Fromsc
groupbysid
havingcount（*）>3
（15）查询各个课程号及相应的选课人数。
Selectcid,COUNT（sid）
Fromsc
groupbycid
（16）查询选修课程号为“c001”或“c002”且分数不低于90分的学生的学号、课程号和成绩，按成绩降序排列。
selectcid,sid,score
fromsc
wherescore<=90andcid='c001'orcid='c002'
orderbyscoredesc
2.复杂查询
（主要是多表查询，可以用连接查询、嵌套查询等多种方式进行查询。
）
（1）查询所有选修了“c001”课程的学生学号、姓名。
selects.sid,sname
froms,sc
wheresc.sid=s.sidandcid='c001'
（2）查询学号是0009001学生选修课程的总学分。
selectSUM（credit）
fromc,sc
wheresc.cid=c.cidandsid='0009001'
（3）查询单科成绩不及格的学生的学号、姓名、课程名、成绩。
selects.sid,sname,cname,score
fromsc,s,c
wheres.sid=sc.sidandsc.cid=c.cidandscore<60
（4）查询刘力老师所教授课程的相关信息，包括课程号、课程名、学分。
selectcid,cname,credit
fromc,t
wheret.tid=c.tidandtname='刘力'
（5）查询每个学生及其选修课程的情况，要求结果当中包含学号、姓名、课程名、授课教师名及成绩。
selects.sid,sname,cname,tname,score
froms,sc,c,t
wheres.sid=sc.sidandsc.cid=c.cidandc.tid=t.tid
（6）查询选修了“c001”课程且成绩在90分以上的所有学生的学号、姓名、课程名、授课教师名及成绩。
selects.sid,sname,cname,tname,score
froms,sc,c,t
wheres.sid=sc.sidandsc.cid=c.cidandc.tid=t.tid
andsc.cid='c001'andscore>90
（7）查询不学“c003”课程的学生学号、姓名。
selectsname,sid
froms
wherenotexists（select*fromscwheresid=s.sidandcid='c003'）
（8）查询与“张力英”老师职称相同的教师号、姓名、职称。
selecttid,tname,title
fromt
wheretitleIN（selecttitlefromtwheretname='张力英'）
（9）查询选修了课程名为“Java”的学生学号和姓名。
selectsname,s.sid
froms,sc,c
wheres.sid=sc.sidandsc.cid=c.cidandcname='Java'
（10）查询每个学生超过他选修课程平均成绩的课程号。
selectsid,cid
fromscx
wherescore>=（selectAVG（score）fromscywherey.sid=x.sid）
成绩
教师签字：
批改日期：
实验三数据安全
实验类型：
验证实验课时：
2指导教师：
高素春
时间：
2014年12月16日课次：
第3-4节教学周次：
第15周
实验分室：
3#101实验台号：
C03实验员：
一、实验目的
1.掌握视图的定义以及与基表的区别。
2.掌握视图的建立、删除、查询、更新。
3.熟悉用户的创建。
4.掌握用户权限的授予。
5.掌握用户权限的收回。
6.熟悉数据库角色。
二、实验内容与要求
（一）视图的建立、删除、查询、更新
1.建立信息系学生情况视图Info_student，通过该视图可查询学生的学号、姓名、年龄。
createviewInfo_student
as
selectsid,sname,sage
froms
wheresdept='信息系'
2．建立信息系选修了1号课程的学生视图Info_student1，通过该视图可查询学生的学号、姓名、成绩。
createviewInfo_student1
as
selects.sid,sname,score
fromsc,s
wheresc.sid=s.sidandcid='c001'andsdept='信息系'
3．在Info_student1的基础上，建立信息系选修了1号课程且成绩在90分以上的学生视图。
createviewInfo_student2
as
selects.sid,sname,score
fromsc,s
wheresc.sid=s.sidandcid='c001'andsdept='信息系'andscore>90
4．建立视图，将其定义为学生的学号及他的平均成绩。
createviewsid_avgscore（sid,avgscore）
as
selectsid,AVG（score）
fromsc
groupbysid
5．建一视图myview，通过该视图可查询某个学生某门课的成绩，要求内容有学号、姓名、课程名、成绩。
createviewmyview
as
selects.sid,sname,cname,score
fromsc,s,c
6．删除视图Info_student1。
dropviewInfo_student1
7．查询信息系学生视图Info_student中年龄小于20岁的学生。
selectsid,sage
fromInfo_student
wheresage<20
8．查询选修了1号课程的信息系学生。
selectInfo_student.sid,sname
fromInfo_student,sc
whereInfo_student.sid=sc.sidandsc.cid='c001'
9．向信息系学生视图Info_student中插入一个新的学生记录“0009010，王楠，19”。
insertinto
Info_student
values（'0009010','王楠',19）;
10．将信息系学生视图Info_student中学号为0009001的学生姓名改为“李丽”。
updateInfo_student
setsname='李丽'
wheresid='0009001'
11.删除信息系学生视图Info_student中学号为“0009002”的记录。
delete
fromInfo_student
wheresid='0009002'
（二）利用可视化方式在对象资源管理器下完成如下操作
（1）新建登录账号aa和bb。
（2）将aa转换成pubs库下的用户，并授予对authors表的select、insert、update、delete权限。
（3）新建角色my_role，为其授予对pubs库下authors表的select、insert、update、delete权限，通过将用户添加到角色的方法，使bb用户自动具有my_role拥有的权限。
（三）用命令方式完成权限的授予与收回
（1）授予用户aa查询student表的权限，并允许将此权限再授予其他用户。
grantselect
ontables
toaa
withgrantoption;
（2）把查询student表和修改学生学号的权限授给用户bb。
grantupdate（sid）,select
ontables
tobb;
（3）收回用户aa查询student表的权限。
revokeselect
ontables
fromaa;
（4）收回用户bb修改学生学号的权限。
revokeupdate（sid）
ontables
frombb;
成绩
教师签字：
批改日期：
升级会员 