数据库实验(共24页).doc

《数据库实验(共24页).doc》由会员分享，可在线阅读，更多相关《数据库实验(共24页).doc（24页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上附 录实验一 数据描述、定义实验一、实验学时4学时。二、实验类型演示、设计类型。三、实验目的熟悉和掌握数据库的创建和连接方法；熟悉和掌握数据表的建立、修改和删除；加深对表的实体完整性、参照完整性和用户自定义完整性的理解。四、需用仪器、设备486以上微机；Windows95/97/98操作系统；安装了SQL Server2000/2005。五、实验准备1、熟悉SQL SERVER 工作环境；2、复习有关表的建立、修改和删除的SQL语言命令。六、实验方法及步骤1、介绍有关SQL SERVER的图形界面工作环境。2、创建数据库或连接已建立的数据库。3、在当前数据库上建立新表

2、。4、定义表的结构：（1）用SQL命令形式；（2）用SQL SERVER提供的企业管理器以交互方式进行。5、进一步定义表的主码，外码及在表上建立索引。6、将以上表的定义以.SQL文件的形式保存在磁盘上。7、修改表的结构。8、删除表的定义。七、实验内容背景材料：在以下实验中，使用学生-课程数据库，它描述了学生的基本信息、课程的基本信息及学生选修课程的信息。1、创建学生-课程数据库；2、创建学生关系表Student；学号姓名性别年龄所在系SnoSnameSsexSageSdept3、创建课程关系Course；课程号课程名先行课学分CnoCnameCpnoCcredit4、创建课程关系表SC；学号课

3、程号成绩SnoCnoGrade5、将以上创建表Student、Course、SC的SQL命令以 .SQL文件的形式保存在磁盘上。6、在表Student上增加“出生日期”属性列。7、删除表Student的“年龄” 属性列。8、在表Student上，按“Sno” 属性列的唯一值方式建立索引。9、在表SC上，按“Grade” 属性列的多值方式建立索引。10、删除表SC，利用磁盘上保存的.SQL文件重新创建表SC。11、选做：创建教材P74-75习题5、SPJ数据库中的四个关系S、P、J、SPJ。八、实验思考1、定义表结构是对数据模型的哪个要素进行描述，对主属性有什么要求？2、修改表结构时，能否修改已

4、定义属性的参数？新增加的属性列能否定义为非空？为什么？3、分别在表Student、SC上按“sno”列的唯一值方式建立索引，可能出现问题吗？情况有什么区别？4、能否在非主属性列上建立索引？其恰当的方式是什么？九、问题1、 在表的定义中，如何确定属性是NOT NULL/NULL ( 主码，关键字，主属性/非主属性)？2、能否将Student、Course、SC表单独以一个数据文件的形式保存在磁盘上？3、在表Student的“SNO”，“SNAME”属性上分别按唯一值方式建立索引和在Student的“SNO”，“SNAME”上唯一值方式建立联合索引有无区别？4、要修改主属性的定义，正确的方式是什么

5、？实验二 数据查询实验一、实验学时6学时。二、实验类型综合类型。三、实验目的熟悉和掌握对数据表中数据的查询操作和SQL命令的使用，学会灵活熟练的使用SQL 语句的各种形式；加深理解关系运算的各种操作（尤其是关系的选择，投影，连接和除运算）。四、需用仪器、设备486以上微机；Windows95/97/98操作系统；安装了SQL Server2000/2005。五、实验准备1、熟悉SQL SERVER 工作环境；2、连接到学生-课程数据库；3、复习对表中数据查询的SQL语言命令。六、实验方法及步骤1、在表Student、Course、SC上进行简单查询；2、在表Student、Course、SC上

6、进行连接查询；3、在表Student、Course、SC上进行嵌套查询；4、使用聚合函数的查询；5、对数据的分组查询；6、对数据的排序查询。七、实验内容在表Student、Course、SC上完成以下查询：1、查询学生的基本信息； select * from Student2、查询“CS”系学生的基本信息； select * from Student where sdept=cs3、查询“CS”系学生年龄不在19到21之间的学生的学号、姓名； select sno,sname from Student where sage not between 19 and 214、找出最大年龄； sele

7、ct max(sage) from Student5、找出“CS”系年龄最大的学生，显示其学号、姓名； select sno,sname from Student where sdept=cs and sage=( select max(sage) from Student where sdept=cs)6、找出各系年龄最大的学生，显示其学号、姓名；（1）方法一可用查询和连接来做此题。 select Sno 学号,Sname 姓名from Studentjoin (select max(Sage) Sage,Sdept from Student group by Sdept) as a on

8、(a.Sage=Student.Sage and a.Sdept=Student.Sdept)（2）方法二可用视图来做此题。 create view ms(sdept,sage)asselect sdept,max(sage) sage from Student group by sdeptselect sno,snamefrom Student,mswhere Student.sdept=ms.sdept and Student.sage=ms.sage7、统计“CS”系学生的人数； select count(sno) from Student where sdept=cs8、统计各系学生的

9、人数，结果按升序排列；（1）方法一 select sdept,count(sno) snum from Student group by sdept order by snum asc（2）方法二select sdept,count(*) from Student group by sdept order by count(*) asc9、按系统计各系学生的平均年龄，结果按降序排列； select sdept,avg(sage) avgage from Student group by sdept order by avgage desc10、查询每门课程的课程名； select cname

10、from Course11、查询无先修课的课程的课程名和学分数； select cname,ccredit from Course where cpno is null12、 统计无先修课的课程的学分总数； select sum(ccredit) from Course where cpno is null13、统计每位学生选修课程的门数、学分及其平均成绩； select count(SC.cno) snum,sum(Course.ccredit) scredit,avg(sc.grade) avggrade from SC,Course where SC.cno=Co group by SC

11、.sno14、统计选修每门课程的学生人数及各门课程的平均成绩； select count(sno) snum,avg(grade) avggrade from SC group by cno15、找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列；（1）方法一可用视图与查询来做此题。 create view avgs1asselect sno,avg(grade) avggrade from SC group by snoselect Student.sno,sname,sdept,avggradefrom avgs1,studentwhere avggrade=85 and

12、 student.sno=avgs.snoorder by sdept,avggrade（2）方法二可用查询来做此题。select student.sno 学号,sdept 系,avg(grade) 平均成绩from sc,studentwhere sc.sno=student.snogroup by student.sdept,student.snohaving avg(grade)=85order by avg(grade) asc16、查询选修了“1”或“2”号课程的学生学号和姓名；（1）方法一可用查询语句和OR操作来实现。 select distinct Student.sno,sna

13、me from SC,Student where SC.sno=Student.sno and (cno=1or cno=2)（2）方法二可用Union（并）来实现。select Student.sno,sname from SC,Student where SC.sno=Student.sno and cno=1 unionselect Student.sno,sname from SC,Student where SC.sno=Student.sno and cno=217、查询选修了“1”和“2”号课程的学生学号和姓名；（1）方法一select student.sno,sname fro

14、m Student where sno in (select sno from SC where cno=1and sno in ( select sno from SC where cno=2) （2）方法二select sc.sno 学号,sname 姓名from sc,studentwhere cno=1 and sc.sno in(select sc.snofrom scwhere cno=2 and sc.sno=student.sno)18、查询选修了课程名为“数据库”且成绩在60分以下的学生的学号、姓名和成绩； select Student.sno,sname,gradefrom

15、 SC,Student,Coursewhere cname=数据库and grade322、查询选修课程成绩至少有一门在80分以上的学生学号；select distinct snofrom SCwhere grade8023、查询选修课程成绩均在80分以上的学生学号；（1）方法一select distinct snofrom SC where sno not in ( select sno from SC where grade8024、查询选修课程平均成绩在80分以上的学生学号；select snofrom SC group by snohaving avg(grade)8025、选做：针对

16、SPJ数据库中的四个表S，P，J，SPJ，完成教材P74-75-习题5中的查询及教材P127-习题5中的查询。实验三 数据更新实验一、实验学时2学时。二、实验类型综合、设计类型。三、实验目的熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用；加深理解表的定义对数据更新的作用。四、需用仪器、设备486以上微机；Windows95/97/98操作系统；安装了SQL Server2000/2005。五、实验准备1、熟悉SQL SERVER 工作环境；2、连接到学生-课程数据库；3、复习对表中数据的插入、修改和删除的SQL语言命令。六、实验方法及步骤1、将数据插入当前数据库的表Student、C

17、ourse、SC中；（1）用SQL命令形式；（2）用SQL SERVER提供的企业管理器以交互方式进行。2、将以上插入的数据分别以.SQL文件和.txt文件的形式保存在磁盘上；3、修改表Student、Course、SC中的数据；（1）用SQL命令形式（2）用SQL SERVER提供的企业管理器以交互方式进行4、删除表Student、Course、SC中的数据。（1）用SQL命令形式；（2）用SQL SERVER提供的企业管理器以交互方式进行。七、实验内容1、将数据分别插入表Student、Course、SC；方法：使用insert语句进行插入。2、将表Student、Course、SC中的数

18、据分别以.SQL文件或.txt文件的形式保存在磁盘上。方法：（1）将表Student的数据分别以.SQL文件形式保存在磁盘上EXECmaster.xp_cmdshell bcp 学生课程数据库.dbo.Student out c:student.sql -c（2）将表Student的数据分别以.txt文件形式保存在磁盘上EXECmaster.xp_cmdshell bcp 学生课程数据库.dbo.Student out c:student.txt -c说明：（1）对于Course、SC中的数据可以采用上述相同的方法来执行。（2）xp_cmdshell存储过程用来运行平常从命令提示符下执行的程序

19、。在需要SQL SEVER 2005创建一个用来自动存档Bulk Copy Program（BCP）文件或此类文件的目录时，可以使用该存储过程。-c表示指定BCP使用char字符类型来执行大量复制数据。3、在表Student、Course、SC上练习数据的插入、修改、删除操作。（比较在表上定义/未定义主码（Primary Key）或外码（Foreign Key）时的情况）方法：插入操作用insert语句，修改操作用update语句，删除操作用delete语句。4、将表Student、Course、SC中的数据全部删除，再利用磁盘上备份的数据来恢复数据。方法：删除操作用delete语句。5、 如

20、果要在表SC中插入某个学生的选课信息（如：学号为“”，课程号为“5”，成绩待定），应如何进行？insert into sc values(,5,null)6、求各系学生的平均成绩，并把结果存入数据库；create table Dept_grade( Sdept char(15), Avg_grade smallint)insert into Dept_grade(Sdept,Avg_grade)select Sdept,Avg(grade) from sc,studentwhere sc.sno=student.snogroup by sdept7、将“CS”系全体学生的成绩置零；update

21、 scset grade=0where cs=(select sdept from studentwhere student.sno=sc.sno)8、删除“CS”系全体学生的选课记录；delete from scwhere cs=(select sdept from student where student.sno=sc.sno)9、删除学号为“S1”的相关信息；delete from student where sno=S110、将学号为“S1”的学生的学号修改为“S001”；update studentset sno=s001where sno=s111、把平均成绩大于80分的男同学的

22、学号和平均成绩存入另一个表S_GRADE（SNO，AVG_GRADE）；create table S_GRADE( sno char(10), AVG_GRADE smallint)insertinto S_GRADE(sno,AVG_GRADE)select sc.sno,avg(Grade)from student,sc,coursewhere student.sno=sc.sno and o=ogroup by sc.sno,ssexhaving avg(Grade)80 and ssex=男12、把选修了课程名为“数据结构”的学生的各门课成绩提高10%；update scset gra

23、de=grade*1.1where sc.sno in( select sc.sno from sc,course where o=o and cname=数据结构 )13、把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生的成绩提高5%；update scset grade=grade*1.05where grade( select avg(grade) from sc where cno=2 )14、把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉；delete from scwhere cno=2 and grade908、查询各课成绩均大于平均成绩的学生学号、姓

24、名、课程和成绩；（1）方法一create view pjuncj(cno,avg_grade) as select cno,avg(grade) from sc group by cnocreate view xues(sno,sname,cno,cname,grade)asselect sc.sno,sname,o,cname,gradefrom sc,student,coursewhere student.sno=sc.sno and o=oselect student.sno,sname,o,gradefrom student,sc xwhere not exists (select *

25、 from pjcj where not exists (select * from sc y where gradeavg_grade and student.sno=y.sno and o=o ) and student.sno = x.sno（2）方法二create view pjuncj(cno,avg_grade)asselect cno,avg(grade)from scgroup by cnoselect student.sno,sname,o,gradefrom student,sc x,pjuncjwhere gradeavg_grade and student.sno=x.

26、sno and o=o9、按系统计各系平均成绩在80分以上的人数，结果按降序排列；select sdept 系,count(distinct student.sno) 人数from sc,studentwhere sc.sno=student.snogroup by student.sdepthaving avg(grade)80order by 人数 asc10、通过视图V_IS，分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,”S4_MMM” 并查询结果；update V_IS SET Sname=S1_MM where sno=s1select * from V_IS11

27、、通过视图V_IS，新增加一个学生记录 (S12,YAN XI, 男,19,IS)，并查询结果；insert into V_IS values(S12,YAN XI,男,19,IS)select * from V_IS12、通过视图V_IS，新增加一个学生记录 (S13,XAN XI,19,MA)，并查询结果；insert into V_IS values(S13,XIAN ZI, 男,19,MA)select * from student13、通过视图V_IS，删除学号为“S12”和“S13”的学生信息，并查询结果；delete from V_IS where sno=s12说明：用dele

28、te from V_IS where sno=s13不能删除“S13”学生的信息，因为学号为“S13”的学生不在视图V_IS中。所以用下面的语句来删除。delete from student where sno=s1314、要通过视图V_S_C_G，将学号为“S12”的姓名改为“S12_MMM”，是否可以实现？update V_S_C_Gset sname=S12_MMM where sno=S1215、要通过视图V_AVG_S_G，将学号为“S12”的平均成绩改为90分，是否可以实现？不可以用update V_AVG_S_G set Avggrade=90 where sno=S12。视图V

29、_AVG_S_G的平均成绩不可更新，因为它包含聚集函数。八、实验思考1、在定义视图的子查询中，是否可以使用SQL查询语句的任意子句及短语？需要注意什么？2、视图一经定义后，就可以象基本表一样用于查询，但DBMS执行对视图的查询过程与执行基本表的查询过程有什么区别？3、通过视图是否能更新数据？通常那些视图可以进行更新？4、使用视图有什么好处？5、SQL 语言提供了那些方式可以实现对数据的安全保护功能？实验五 SQL语言的DCL（安全性部分）一、实验学时2学时二、实验类型 演示、设计类型。三、实验目的SQL的数据控制通过DCL(数据控制语言) 实现。DCL通过对数据库用户的授权和收权命令来实现有关

30、数据的存取控制，以保证数据库的安全性。本次实验了解DCL语言的GRANT和REVOKE语句对数据库存取权限的控制，学会SQL Server 2000的查询分析器中用DCL语言对数据库存取权限进行设定。四、需用仪器、设备486以上微机；Windows95/97/98操作系统；安装了SQL Server2000/2005。五、实验准备1、熟悉SQL Server 2000工作环境；2、连接到学生-课程数据库；3、复习有关DCL语言的GRANT和REVOKE语句命令；4、建立用户U1、U2、U3、U4、U5、U6、U7，选择全部为Connect角色。六、实验内容（一）授予对象权限启动数据库服务软件S

31、QL Server 2000的查询分析器，用GRANT语句对数据库存取权限进行授权操作，语法格式如下：GRANT To |PUBLIC 注：PUBLIC指数据库的所有用户。（二）回收对象权限用REVOKE语句将DBA(数据库管理员)、DBO(建库用户)授与其它用户对数据库的操作权收回，语法格式如下：REVOKE |ALL PRIVILEGES ON FROM |PUBLIC注：ON用于被回收特权的对象，ALL PRIVILEGES指回收所有特权，PUBLIC指数据库的所有用户。七、实验过程（一）授予权限在SQL Server中建立多个用户，给他们赋予不同的权限，然后查看是否真正拥有被授予的权限

32、了。（在SYSTEM（即DBA）与七个Connect用户之间进行授权。）首先，使用系统存储过程sp_addlogin创建新的 SQL Server 登录。例如，为用户 U1创建SQL Server登录，密码为123。 EXEC sp_addlogin U1, 123;然后，使用系统存储过程sp_adduser向当前数据库中添加新的用户。例如，使用现有的SQL Server登录名U1向当前数据库添加用户U1。 EXEC sp_adduser U1;注：上面的EXEC可省略。1、把查询Student表的权限授给用户U1。（因为当前用户即为SYSTEM，所以直接写授权语句即可。）GRANT SELECT ON Student TO U1;2、把对Student表和Course表的全部操作权限授予用户U2和U3。GRANT ALL PRIVILEGES ON