《数据库原理及课程设计》实验指导书

1、辽东学院自编教材数据库原理实验指导书鲁琴李荣霞编（信息管理与信息系统专业用）信息技术学院2011年1月实验一 SQL SERVER环境、数据定义与数据更新 1实验二数据查询6实验三数据安全性10课程设计 12实验一 SQL Server环境、数据定义与数据更新实验类型：验证实验课时：2指导教师：时 间：20 年月日课 次：第 节教学周次：第周实验分室： 实验台号：实验员:实验目的1 .熟悉SQL Server 200际发环境，了解各常用工具的使用。2 .掌握数据库服务器的本机注册，熟悉网络中其他数据库服务器的注册。3 .掌握数据库的创建、删除。4 .熟练掌握命令方式下各种难度表的创建。5 .掌

2、握命令方式下不同类型索引的创建与删除。6 .熟练掌握命令方式下对表中数据的增、删、改。实验内容及要求要将所有实验内容写在“实验结果”，弁在本实验完成时完成实验报告弁上父。1 .创建数据库要求：(1) 创建的数据库名为你的姓名。(2)在默认路径下建库，能指出这个默认的路径会在某一盘符下以你的班级姓名学号命名的文件夹中创建数据库2 .定义基本表要求：(1)在建好的数据库中用命令方式创建三个关系,分别为学生关系student、课程关系course(3) 课程关系的关系模式为分。cid设为主键。(4) 选课关系的关系模式为学cid选课关系SC。(2) 学生关系的关系模式为： student(sid,s

3、name,ssex,sage, sdept/段分另 U代表学号、姓名、性别、年龄、所在系。sid设为主键。course(cid,cname,credit)各字段分别代表课程号、课程名、sc(sid,cid,score备字段分别代表学号、课程号、成绩。和sid为复和主键, sid和cid为外键。(5) 各字段数据类型要设置恰当，弁对ssex score设置合理的约束。(6) 将建表命令存成脚本文件，可存为一个文件也可存为独立的多个脚本文件。3.修改基本表要求：(1) 向student表增加“入学时间”歹U,其数据类型为日期型。(2) 将course表中的cname列加宽到60个字符。(3) *在

4、student表中增加完整性约束定义，使sagea值在15- 40之间(4) *增加课程名称必须取唯一值的约束条件。(5) 删除student表中“入学时间”歹U。4 .删除基本表要求:*删除sc表。5 .索引的建立、删除要求：(1) 为表sc按学号升序和课程号降序建唯一索引scio(2) *在实例数据库pubs中，为表authors在au_id上建立聚集索引(3) 删除表sc的索引scio6 .插入数据要求：在建好的3个表student,course,sc中各插入2条记录，要求内容如下。用命令方式写出相关命令(注意顺序)Coursecidcn amecreditC801高等数学3C802C+

5、语舌4Scsidcidscore0009001C801540009002C80270stude ntsidsn amessexsagesdept0009001葛文卿女22:国际贸易0009002郑秀丽男21<NULL>7?修改数据要求：(1)将“郑秀丽”的系改为“国际贸易”系。(2)将所有学生的成绩提高到原来的1.1倍(3)将所有学生的年龄加1岁。(4)将“国际贸易”系所有学生的成绩置零。8 .删除数据要求：(1)删除学号为“ 0009002'的学生记录。(2)删除所有学生选课记录。(3)删除计算机科学系所有学生的选课记录。三、实验结果-创建基本表一建立学生表create

6、table Student (sname char ( 5), ssex char ( 1), int ,sagesdept char ( 20 );create tableCourse (cid char ( 4) not cname char ( 20 ), credit float ,);一成绩表create tableSc(sid char( 7),cid char( 4),score int>null primaryprimary foreign foreign-修改 Coursealter table-删除 coursekey ( sid,cid ),key ( sid)re

7、ferenceskey ( cid)references的cid长度，失败Course alter column表sid char ( 7) not null primary key-3.修改基本表-向student表增加“入学时间”歹其数据类型为日期型alter table Student addk学时间date ;-将course表中的cname列加宽到个字符alter table * 在-建堂I股表student alterCourse alter column cname varchar ( 6霞中增加完整性约束定义，使-*增加课程名称必须取唯一值的约束条件sageW值在一之间 Stu

8、denttable-删除student表中“入学时间”列.keyalter table Student drop column 学时间;-5.索引的建立，删除-为表sc按学号升序和课程号降序建唯一索引sci create unique index sci on Sc( sid asc , cid desc )在实例数据库 pubs 中，为表 authors在 au_id 上建立聚集索弓 I -删除表 sc 的索弓 I sci drop index Sc. sci ;-6.插入数据中各插入条记录Student ( sid ), Course ( cid );/* 在建好的个表 student,c

9、ourse,scCoursecid cname creditC801高等数学3cid char ( 4);drop tableCourse ;insertintoCourseinsertintoCourseinsertintoStudent',22,国际贸易');insertintoStudent',21);(sid , sname , ssex , sage , sdept ) values ( '0009001',(sid , sname , ssex , sage , sdept ) values ( '0009002葛文卿，女，'

10、郑秀丽'，'男C802 C+ 语言 4Scsid cid score0009001C801540009002C80270studentsid snamessexsagesdept0009001葛文卿女22国际贸易0009002郑秀丽男21<NULL>*/(cid , cname , credit ) values ( 'C80停等数学',3);values ( 'c802', 'c+if言',4);-7.修改数据()将郑秀丽”的系改为 国际贸易”系esupdateStudent set sdept =国际贸易'

11、wheresname =郑秀丽'()update将所有学生的成绩提高到原来的Sc set score =score * 1.1 ;.1倍()将所有学生的年龄加岁updateStudent set sage =sage +1;-()update将“国际贸易”系所有学生的成绩置零Sc set score =0 where sid in ( selectsid from Studentwhere sdept =国际贸易');- -8.删除数据一删除学号为“0009002”的学生记录DELECT FROM Student WHERE sid='0009002;- -(2)删除所

12、有学生选课记录Delect from Sc;- -(3)删除计算机科学系所有学生的选课记录Delect from Sc where (select cid from Course where cname针算机科学 ');四、实验中遇到的问题及解决方法本次实验成绩项目及比例实验操作(40%)报告书写(40%)出勤和课堂纪律(10%)课堂提问(10%)得分成绩合计教师签字:批改日期:实验二数据查询实验类型：设计实验课时：2指导教师：时间：20年月日课次：第节教学周次：第周实验分室：实验台号：实验员：实验目的1. 掌握查询的基本语法格式。2. 熟练掌握单表查询。3. 掌握连接查询。4. 熟悉

13、嵌套查询。实验内容及要求要求将所有实验内容写在“实验结果”，弁在本实验完成时完成实验报告弁上父。对实验一所创建的数据库中的三个表 ：student、course和sc,用SQL完成下面的操作。1. 简单查询(1) 查询全体学生的学号与姓名。(2) *查询全体学生的姓名及其出生年份。(3) *查询选修了课程的学生学号。(4) 查询平均成绩大于60分的课程号和平均成绩。(5) 查询考试成绩有不及格的学生的学号。(6) 查询年龄不在20- 25岁之间的学生学号、姓名和年龄。(7) *查询计算机系、国际贸易系、数学系学生的姓名和性别。(8) *查询有多少学生专业不确定。(9) *查询所有姓刘的学生的姓

14、名、学号和性别。(10) 查询名字中第2个字为“阳”字的学生的学号、姓名。(11) 查询所有不姓张的学生姓名。(12) *查询选修课程号为“ C801”或“ C802'且分数不低于90分的学生的学号、课程号和成绩(13) *查询没有考试成绩的学生的学号和课程号。(14) *查询选修课程号为“ C801”的学生的学号及成绩，按成绩降序排列。(15) 查询选修了课程的学生人数。(16) 查询各个课程号及相应的选课人数。(17) 查询选修了 3门以上课程的学生学号。2. 复杂检索(1) *查询单科成绩不及格的学生的学号、姓名、课程名、系别及成绩。(2) *查询选修了 2门以上课程的学生学号。

15、(3) *查询既不是外语系、数学系，也不是财务会计系的学生的姓名和系别。(4)查询每个学生及其选修课程的情况，要求结果当中包含学号、姓名、性别、所在系、课 名、成绩。(5) *查询选修了 2号课程且成绩在90分以上的所有学生的学号、姓名。(6) *查询与“张三”在同一个系学习的学生的学号、姓名、系别。(7) 查询选修了课程名为“信息系统”的学生学号和姓名。(8) *查询每个学生超过他选修课程平均成绩的课程号。(9) *查询其它系中比计算机科学系所有学生年龄都小的学生姓名及年龄。(10) *查询所有选修了 1号课程的学生姓名。三、实验结果-实验二数据查询-1.简单查询- -()查询全体学生的学号

16、与姓名。select sid , sname from Student ;- -()*查询全体学生的姓名及其出生年份。select sname , DATENAME ( YEAR, GETDATE () ) - sage from Student ;select YEAR ( GETDATE () from student-获取当前年份- -()*查询选修了课程的学生学号。select sid from Sc where cid is not null;- -()查询平均成绩大于分的课程号和平均成绩。select AVG ( score ) , cid from Sc where AVG (

17、score ) > 60;- -()查询考试成绩有不及格的学生的学号。select sid from Sc where score <60 ;- -()查询年龄不在一岁之间的学生学号、姓名和年龄。select sid , sname , sage from Student where sage between 20 and 25 ;- -()*查询计算机系、国际贸易系、数学系学生的姓名和性别。select sname , ssex from Student where sdept)in#算机'，'国际贸易'，'数学);- -()*查询有多少学生专业不

18、确定。select COUNT (*) from Student where sdept is null;- -()*查询所有姓刘的学生的姓名、学号和性别。seleCt sname , sid , ssex from Student wheresname like '刘 '-()查询名字中第个字为“阳”字的学生的学号、姓名。seleCt sid , sname fromStudent where sname like '_ 阳 '-()查询所有不姓张的学生姓名。seleCt sname from Student where sname notlike 弓长 &#

19、39;- -()*查询选修课程号为“C801”或“C802且分数不低于分的学生的学号、课程号和成绩。select student . sid , cid , score from Student , Sc where Student . sid =sc . sid and Cid ='C801,C802' and sCore >=90;seleCt sid , Cid , sCore from SC where Cid ='C801,C802' and sCore >=90;- -()*查询没有考试成绩的学生的学号和课程号。select sid ,

20、cid from Sc where scoreis null;- -()*查询选修课程号为“C801”的学生的学号及成绩，按成绩降序排列。select sid , score from Sc where cid ='c801'order by score desc- -()查询选修了课程的学生人数。seleCt COUNT(*) from SC where Cid is not null;- -()查询各个课程号及相应的选课人数。seleCt Cid , COUNT( sid ) from SC groupby Cid ;- -()查询选修了门以上课程的学生学号。seleCt

21、sid from SC group by sid having COUNT( Cid )> 3;-2.复杂检索- -()*查询单科成绩不及格的学生的学号、姓名、课程名、系别及成绩。seleCt sC . sid , sname , Cname , sdept , sCore from Student , SC, Course where Student . sid =sC . sid andCourse . Cid =sC . Cid and sCore <60 ;- -()*查询选修了门以上课程的学生学号。seleCt sid from SC group by sid havin

22、g COUNT(*)>= 2;外语',数学'，'财务会计-()*查询既不是外语系、数学系，也不是财务会计系的学生的姓名和系别seleCt sname , sdept from Student where sdept not in (成绩-()O查询每个学生及其选修课程的情况，要求结果当中包含学号、姓名、性别、所在系、课名from Student,SCCourseseleCt student . sid , sname , ssex , sdept , Cname , sCore where Student . sid =sC . sid and sC . Cid

23、= Course . Cid ;.sid =sC . sid andsdept from Student- -()*查询选修了 C802号课程且成绩在分以上的所有学生的学号、姓名。seleCt student . sid , sname from SC, Studentwhere studentsCore >=90- -()*查询与“张三”在同一个系学习的学生的学号、姓名、系别。seleCt sid , sname , sdept from Studentwhere sdept in ( seleCtwhere sname 承三');- -() 查询选修了课程名为“信息系统”的学

24、生学号和姓名。seleCt student . sid , sname from Student , Course , SC where student. sid =sC . sidandsC . Cid = Course . Cid and Cname =言息系统'- -()*查询每个学生超过他选修课程平均成绩的课程号。seleCt Cid from SC group by Cid , sCorehaving sCore >AVG( sCore )- -()*查询其它系中比计算机科学系所有学生年龄都小的学生姓名及年龄。seleCt sname , sage from Stude

25、nt group by sname , sage , sdept having sdepOL ''- -()*查询所有选修了(号)课程名为的学生姓名。seleCt sname from Course , Student , SC where student . sid =sC . sid andsC . Cid =Course . Cid and Cname ='1本次实验成绩项目及比例实验操作(40%)报告书写(40%)出勤和课堂纪律(10%)课堂提问(10%)得分成绩合计四、实验中遇到的问题及解决方法教师签字：批改日期:实验三数据安全性实验类型：验述 实验课时：套时

26、间：20年 月 日课次：第节实验分室：实验台号：一、实验目的1. 掌握视图的定义以及与基表的区别。2. 掌握视图的建立、删除、查询、更新。3. 熟悉用户的创建。4. 掌握用户权限的授予。5. 掌握用户权限的收回。6. 熟悉数据库角色。指导教师：教学周次：第 周实验员:、实验内容及要求要求将所有实验内容写在“实验结果”，弁在本实验完成时完成实验报告弁上交。1. 建立信息系学生情况视图In fo_stude nt ,通过该视图可查询学生的学号、姓名、年龄。2. 建立信息系选修了1号课程的学生视图In fo_stude nt1 ,通过该视图可查询学生的学号、姓名、成绩。3. 在Info_studen

27、t1的基础上，建立信息系选修了1号课程且成绩在90分以上的学生视图4. 建立视图，将其定义为学生的学号及他的平均成绩。5. 建一视图myview,通过该视图可查询某个学生某门课的成绩，要求内容有学号、姓名、课 程名、成绩。6. 删除视图 Info_student1。7. 查询信息系学生视图Info_student8. 将信息系学生视图In fo_stude nt9. 向信息系学生视图Info_student10. 删除信息系学生视图Info_student中年龄小于20岁的学生。中学号为1的学生姓名改为“马丽”。中插入一个新的学生记录"20081000,刘一，19”中学号为"

28、;20081000'的记录。11. 利用可视化方式在企业管理器下完成如下操作题：(1) 新建登录账号 aa禾口 bbo 将aa转换成 pubs库下的用户，弁授予对 delete 权限。新建角色 my_role,为其授予对 pubs库下delete权限，通过将用户添加到角色的方法,12. 用命令方式完成权限的授予与收回：authors 表的 select insert、 updateauthors 表的 select insert、 update使bb用户自动具有my_role拥有的权限。(1)授予用户aa查询student表的权限。(2)把查询student表和修改学生学号的权限授给用

29、户bb、一, ，一 1 一O(3)收回用户aa和bb查询student表的权限。(4)收回用户bb修改学生学号的权限。-1.实验结果建立信息系学生情况视图 Infostudent,通过该视图可查询学生的学号、姓名、年龄。( sid , sname , sage )create view Info_student- 实验三 数据安全性as select sid , sname , sage from-2. 建立信息系选修了号课程的学生视图StudentInfostudent1通过该视图可查询学生的学号、姓名、create view Info_student1成绩。( sid , sname ,

30、scoureas selectstudent . sid , sname , score fromStudent , Sc-3.where student . sid =sc . sidand student . sdept='信息'and cid = 'c801'在 Infostudent1的基础上，建立信息系选修了号课程且成绩在分以上的学生视图。create viewInfo_student2 as select * from Info_student1 where scoure >90 ;-4.建立视图，将其定义为学生的学号及他的平均成绩。creat

31、e viewInfostudent3( sid , avgscore ) as select sid , AVG( score ) from Sc-5.建一视图myview ，通过该视图可查询某个学生某门课的成绩，要求内容有学号、 姓名、 课程名、group by sid ;成绩。create view myview ( sid , sname , cname , score ) as select student . sid , sname , cname , score from Student , Sc, Course-6.删除视图 Info_student1where student

32、. sid =sc . sid and sc . cid = Course . cid ;-7.查询信息系学生视图 Infostudent中年龄小于岁的学生。-8.-9.-10.select将信息系学生视图* from Infostudentupdate Info_student向信息系学生视图insert删除信息系学生视图InfostudentInfostudentwhere sage <20;中学号为的学生姓名改为set sname =' 马丽 'where sid ='1Infostudent中插入一个新的学生记录values ( '20081000

33、' , ' 刘一 ' , 19);Infostudent中学号为“”的记录马丽 ”。，刘一，delete from Info_student drop view Info_student1where sid = '20081000'- -/*11.利用可视化方式在企业管理器下完成如下操作题：(、新建登录账号aa和bb 。- - ) -()权 将aa转换成pubs库下的用户，并授予对 authors表的select、insert、update、delete 限。- -()权 新建角色 my_role,为其授予对 pubs 库下 authors 表的 select、insert、up