数据库原理实验指导书计科.docx

1、数据库原理实验指导书计科辽东学院自编教材数据库原理实验指导书高素春 编（计算机科学与技术专业用）信息技术学院2014年9月实验一 数据定义与数据更新实验类型： 验证 实验课时： 2 指导教师： 高素春 时 间：20 14年12 月16 日 课 次： 第 1-2节 教学周次：第 15 周 实验分室： 3#101 实验台号： C05 实 验 员： 一、实验目的1.熟悉SQL Server开发环境，了解各常用工具的使用。2.掌握数据库服务器的本机注册，熟悉网络中其他数据库服务器的注册。3.掌握数据库的创建、删除。4.熟练掌握命令方式下表的创建、修改与删除。5.熟练掌握命令方式下表中数据的插入、修改与

2、删除。6.熟练掌握命令方式下索引的创建与删除。二、实验内容与要求1.创建数据库要求：（1）在默认路径下，创建的数据库名为你的姓名（拼音缩写），写出默认路径。C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA（2）在E:，以你的班级姓名学号命名的文件夹中创建数据库。2.定义基本表要求：（1）在建好的数据库中用命令方式创建四个关系，分别为学生关系s、教师关系t、课程关系c、选课关系sc。认真分析各个关系，设置主键、外键，要求属性的数据类型、长度设置要合理。（2）学生关系的关系模式为：s(sid, sname, ssex,

3、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）将定义基本

4、表的命令写在空白处。create table s(sid char(10) primary key,sname char(10),ssex char(2),sage int,sdept char(30)select * from screate table t(tid char(10) primary key,tname char(10),title char(30)select * from tcreate table c(cid char(10) primary key,cname char(10),credit int,tid char(10) ,foreign key (tid) re

5、ferences t(tid)select * from ccreate table sc(sid char(10)foreign key references s(sid),cid char(10)foreign key references c(cid),score int,primary key(sid,cid)select * from sc3.修改基本表要求：（1）向s表增加“入学时间”列，其数据类型为日期型。alter table sadd s_entrance date（2）增加课程名称必须取唯一值的约束条件。alter table cadd unique (cname)（3）增

6、加学生年龄取值范围在15到40岁之间的约束条件。alter table sadd constraint sage_chk check (sage between 15 and 40)（4）将c表中cname属性列长度加宽到50个字符。alter table calter column cname varchar(50)（5）删除s表中“入学时间”列。alter table sdrop column s_entrance（6）删除学生年龄取值范围在15到40岁之间的约束。alter table sdrop constraint sage_chk4.删除基本表要求： 删除sc表。 说明：为方便下面

7、的步骤，删除后再创建sc。drop table sc5.插入数据要求：在建好的4个表s,t, c, sc中各插入几条记录，内容如下。用命令方式写出插入数据的相关命令。 ssidsnamessexsagesdept0009001王华女18计算机科学0009002马强男20计算机科学0009003关心女22数学0009004王珍一女19国际贸易0009005关勇男21ttidtnametitlet001刘力副教授t002张勇讲师t003张力英副教授t004方佳林讲师ccid cnamecredittidc001高等数学2t001c002Java4t003c003C3t003c004数据库4t004

8、c005经济学4t002scsidcidscore0009001c001910009001c002500009001c003500009001c0040009002c001920009002c002950009002c003550009002c0040009003c001950009004c001920009004c00591insert into s values(0009001,王华,女,18,计算机科学)insert into s values(0009002,马强,男,20,计算机科学)insert into s values(0009003,关心,女,22,数学)insert int

9、o s values(0009004,王珍一,女,19,国际贸易)insert into s values(0009005,关勇,男,21,null)insert into t values(t001,刘力,副教授)insert into t values(t002,张勇,讲师)insert into t values(t003,张力英,副教授)insert into t values(t004,方家林,讲师)insert into c values(c001,高等数学,2,t001)insert into c values(c002,Java,4,t003)insert into c val

10、ues(c003,C,3,t003)insert into c values(c004,数据库,4,t004)insert into c values(c005,经济学,4,t002)insert into sc values(0009001,c001,91)insert into sc values(0009001,c002,50)insert into sc values(0009001,c003,50)insert into sc values(0009001,c004,null)insert into sc values(0009002,c001,92)insert into sc v

11、alues(0009002,c002,95)insert into sc values(0009002,c003,55)insert into sc values(0009002,c004,null)insert into sc values(0009003,c001,95)insert into sc values(0009004,c001,92)insert into sc values(0009004,c005,91)6.修改数据要求：（1）将“马强”的系改为“数学”。update sset sdept=数学where sname=马强（2）将所有学生的成绩提高到原来的1.1倍。upda

12、te scset score=1.1*score（3）将所有学生的年龄加1岁。update sset sage=1+sage（4）将选修了“c001”课程的学生的成绩提高5%。update scset score=0.05 * scorewhere cid=c0017.删除数据要求： （1）删除学号为“0009002”的学生记录。delete from scdelete from swhere sid=0009002（2）删除所有学生选课记录。delete from sc 说明：为了方便以后的操作，数据删除后再重新插入数据。 8. 创建索引 要求： （1）为s表建立索引，按学号升序建唯一索引。

13、 create unique index unq_ind_sid on s(sid) （2）为sc表建立索引，按学号升序和课程号降序建唯一索引。 create unique index unq_ind_sidcid on sc(sid asc,cid desc)9. 删除索引要求： 删除为s表按学号升序创建的唯一索引。drop index s.unq_ind_sid 成 绩教师签字：批改日期：实验二 数据查询实验类型： 设计 实验课时： 4 指导教师： 高素春 时 间：20 14 年12 月23日 课 次：第 1-4 节 教学周次：第 16 周 实验分室： 3#101 实验台号： C04 实

14、验 员： 一、实验目的1.掌握查询的基本语法格式。2.熟练掌握单表查询。3.掌握连接查询。4.熟悉嵌套查询。二、实验内容与要求要求：对实验一所创建的数据库中的四个表：s、t、c、和sc，用SQL完成下面的查询并将SQL语句写在空白处。1.简单查询（主要是单表查询）（1）查询职称是副教授的教师号与姓名。select tname,tidfrom twhere title=副教授（2）查询全体学生的姓名及其出生年份，出生年份用别名“birth_year”标识。select sname, birth_year:,2014-sagefrom s（3）查询选修了课程的学生学号。select sidfrom

15、 sc（4）查询考试成绩有不及格的学生的学号。select sidfrom scwhere score60（14）查询选修了3门以上课程的学生学号。Select sidFrom scgroup by sidhaving count(*)3（15）查询各个课程号及相应的选课人数。Select cid,COUNT(sid)From scgroup by cid（16）查询选修课程号为“c001”或“c002”且分数不低于90分的学生的学号、课程号和成绩，按成绩降序排列。select cid,sid,scorefrom scwhere score=90 and cid=c001or cid=c002

16、order by score desc2.复杂查询（主要是多表查询，可以用连接查询、嵌套查询等多种方式进行查询。）（1）查询所有选修了“c001”课程的学生学号、姓名。select s.sid,snamefrom s,scwhere sc.sid=s.sid and cid=c001（2）查询学号是0009001学生选修课程的总学分。select SUM(credit)from c,scwhere sc.cid=c.cid and sid=0009001（3）查询单科成绩不及格的学生的学号、姓名、课程名、成绩。select s.sid,sname,cname,scorefrom sc,s,cw

17、here s.sid=sc.sid and sc.cid=c.cid and score90（7）查询不学“c003”课程的学生学号、姓名。select sname,sidfrom swhere not exists(select * from sc where sid=s.sid and cid=c003)（8）查询与“张力英”老师职称相同的教师号、姓名、职称。select tid,tname,titlefrom twhere title IN(select title from t where tname=张力英)（9）查询选修了课程名为“Java”的学生学号和姓名。select snam

18、e,s.sidfrom s,sc,cwhere s.sid=sc.sid and sc.cid=c.cid and cname=Java（10）查询每个学生超过他选修课程平均成绩的课程号。select sid,cidfrom sc xwhere score=(select AVG(score) from sc y where y.sid=x.sid)成 绩教师签字：批改日期：实验三 数据安全实验类型： 验证 实验课时： 2 指导教师： 高素春 时 间：20 14年12 月16 日 课 次：第 3-4 节 教学周次：第 15 周 实验分室： 3#101 实验台号： C03 实 验 员： 一、实验

19、目的1.掌握视图的定义以及与基表的区别。2.掌握视图的建立、删除、查询、更新。3.熟悉用户的创建。4.掌握用户权限的授予。5.掌握用户权限的收回。6.熟悉数据库角色。二、实验内容与要求（一）视图的建立、删除、查询、更新1.建立信息系学生情况视图Info_student，通过该视图可查询学生的学号、姓名、年龄。 create view Info_student as select sid,sname,sage from s where sdept=信息系2 建立信息系选修了1号课程的学生视图Info_student1，通过该视图可查询学生的学号、姓名、成绩。create view Info_st

20、udent1as select s.sid,sname,scorefrom sc,swhere sc.sid=s.sid and cid=c001 and sdept=信息系3在Info_student1的基础上，建立信息系选修了1号课程且成绩在90分以上的学生视图。create view Info_student2as select s.sid,sname,scorefrom sc,swhere sc.sid=s.sid and cid=c001 and sdept=信息系 and score904建立视图，将其定义为学生的学号及他的平均成绩。create view sid_avgscore

21、(sid,avgscore) as select sid,AVG(score) from sc group by sid5建一视图myview，通过该视图可查询某个学生某门课的成绩，要求内容有学号、姓名、课程名、成绩。create view myviewas select s.sid,sname,cname,scorefrom sc,s,c6删除视图Info_student1。drop view Info_student17查询信息系学生视图Info_student中年龄小于20岁的学生。select sid,sagefrom Info_studentwhere sage208查询选修了1号课

22、程的信息系学生。select Info_student.sid,snamefrom Info_student,scwhere Info_student.sid=sc.sid and sc.cid=c0019 向信息系学生视图Info_student中插入一个新的学生记录“0009010，王楠，19”。 insert intoInfo_studentvalues(0009010,王楠,19);10 将信息系学生视图Info_student中学号为0009001的学生姓名改为“李丽”。 update Info_studentset sname=李丽where sid=000900111. 删除信息

23、系学生视图Info_student中学号为“0009002”的记录。 delete from Info_studentwhere sid=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表的权限，并允许将此权限再授予其他用户。 grant select on table s to aa with grant option;（2）把查询student表和修改学生学号的权限授给用户bb。 grant update(sid),select on table s to bb;（3）收回用户aa查询student表的权限。 revoke select on table s from aa;（4）收回用户bb修改学生学号的权限。 revoke update(sid) on table s from bb;成 绩教师签字：批改日期：