数据库实验及其答案(共15页).doc

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

1、精选优质文档-倾情为你奉上数据库系统概论实验报告书专业班级学 号姓 名指导教师安徽工业大学计算机学院实验一：数据定义/数据操纵语言 实验日期 2011 年 4 月 10 日 实验目的 熟悉SQL SERVER上机环境；熟练掌握和使用DDL语言，建立、修改和删除数据库表；熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。 实验内容 1. 先建立数据库：STUDENT用两种方式建立:在查询分析器中以DDL语言方式建立.步骤为:先在指定的地方建立放置数据库文件的文件夹(如学生数据库),然后将建立的数据库文件放到指定的文件夹中.2. SQL数据定义语句： 例1-1： (建立数据库表) 建立教学

2、数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空。create table Student(SNO char(5) primary key,SNAME char(8) NULL,SDEPT char(2),SCLASS char(2),SAGE smallint)create table Course(CNO char(3) primary key,CNAME char(16),CTIME smallint)create table Teach(TNAME CHAR(8),TSEX CHAR(2),CNO CHAR(3)

3、,TDATE smalldatetime,TDEPT CHAR(2)create table Score (sno char(5),cno char(3),Score float); 例1-2： (修改数据库表) 在Student表中增加SSEX(C,2) 字段。alter table student add SSEX char(2) 例1-3： (修改数据库表) 将Student表中把Sname 字段修改为Sname(C,10)且为非空。alter table student alter column SNAME char(10) not null 例1-4： (建立数据库表) 建立数据库表

4、S1(SNO,SNAME,SD,SA)，其字段类型定义与Student表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。create table S1(SNO char(5)primary key,SNAME char(10) not NULL,SD char(2),SA smallint)3. SQL数据操纵语句： 例2-1： (插入数据) 按前面各表中的数据分别插入到教学数据库的四个数据库表中。insert into Student values(96001,马小燕,CS,01,女,21);insert into Student values(96002,黎明,

5、CS,01,男,18);insert into Student values(96003,刘东明,MA,01,男,18);insert into Student values(96004,赵志勇,IS,02,男,20);insert into Student values(97001,马蓉,MA,02,女,19);insert into Student values(97002,李成功,CS,01,男,20);insert into Student values(97003,黎明,IS,03,女,19);insert into Student values(97004,李丽,CS,02,女,1

6、9);insert into Student values(96005,司马志明,CS,02,男,18);insert into COURSE values(001,数学分析,144);insert into COURSE values(002,普通物理,144);insert into COURSE values(003,微机原理,80);insert into COURSE values(004,数据结构,72);insert into COURSE values(005,操作系统,80);insert into COURSE values(006,数据库原理,80);insert int

7、o COURSE values(007,编译原理,60);insert into COURSE values(008,程序设计,40);insert into TEACH values(王成刚,男,004,1999.9.5,CS);insert into TEACH values(李正科,男,003,1999.9.5,CS);insert into TEACH values(严敏,女,001,1999.9.5,MA);insert into TEACH values(赵高,男,004,1999.9.5,MA);insert into TEACH values(刘玉兰,女,006,2000.2.

8、23,CS);insert into TEACH values(王成刚,男,004,2000.2.23,IS);insert into TEACH values(马悦,女,008,2000.9.6,CS);insert into Score values(96001,001,77.5);insert into Score values(96001,003,89);insert into Score values(96001,004,86);insert into Score values(96001,005,82);insert into Score values(96002,001,88);

9、insert into Score values(96002,003,92.5);insert into Score values(96002,006,90);insert into Score values(96005,004,92);insert into Score values(96005,005,90);insert into Score values(96005,006,89); 例2-2：(多行插入) 将表Student表中计算机系(CS)的学生数据插入到表S1中。insert into s1 select sno,sname,sdept,sage from student wh

10、ere sdept=cs 例2-3：(利用查询来实现表的定义与数据插入) 求每一个学生的平均成绩，把结果存入数据库表Student_Gr中。create table Student_Gr(Score float)insert into student_gr select avg (score) from score GROUP BY sno 例2-4： (修改数据) 将S1表中所有学生的年龄加2。update s1 set sa=sa+2 例2-5： (修改数据) 将Course表中程序设计课时数修改成与数据结构的课时数相同。update Courseset ctime =(select ct

11、ime from course where cname=数据结构)where cname=程序设计 例2-6： (插入数据) 向Score表中插入数据（98001, 001, 95），根据返回信息解释其原因。insert into Score values(98001,001,95) 添加一条信息成功 例2-7： (插入数据) 向Score表中插入数据（97001, 010, 80），根据返回信息解释其原因。insert into Score values(97001,010,80) 插入一条信息成功例2-8： (删除数据) 删除Score表中学号为96001的成绩信息，根据返回信息解释其原因

12、。delete from score where sno=96001将四行学号是96001的学生信息删去例2-9： (删除数据) 删除Score表中课程号为003 的成绩信息，根据返回信息解释其原因。delete from score where cno=003 将一条课程号是003的信息删去 例2-10：(删除数据) 删除学生表S1中学号以96打头的学生信息。delete from s1 where sno like 96% 例2-11：(删除数据) 删除数据库表S1中所有学生的数据。delete from s1 例2-12：(删除表) 删除数据库表S1和Student_Gr。drop ta

13、ble s1,student_gr 实验要求 熟悉SQL Server上机环境； 建立数据库表，修改数据库表结构； 对数据库表进行插入、修改和删除数据的操作。 实验方法 执行SQL语句； 将实验需求用SQL语句表示； 查看执行结果，如果结果不正确，进行修改，直到正确为止。 实验总结 SQL语句以及执行结果；Sql语句已经验证成功 对重点实验结果进行分析； 实验中的问题和提高；遇到不会的问题，主动查找资料，知道了怎样解决问题。 收获与体会。熟悉了数据库基本的表的建立，删去，修改等操作 实验二：数据查询语言 实验日期 2011 年 4 月 14 日 实验目的 体会SQL语言数据查询功能的丰富和复杂

14、。 实验内容 3 SQL数据查询语句： 例3-1： (选择表中的若干列) 求全体学生的学号、姓名、性别和年龄。select sno,sname,ssex,sage from student 例3-2： (不选择重复行) 求选修了课程的学生学号。select distinct sno from score 例3-3： (选择表中的所有列) 求全体学生的详细信息。Select * from student 例3-4： (使用表达式) 求全体学生的学号、姓名和出生年份。select sno,sname,year( getdate ()-sage from student 例3-5： (使用列的别名)

15、 求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。select sno as 学号,sname,year( getdate ()-sage as 出生年月 from student 例3-6： (比较大小条件) 求年龄大于19岁的学生的姓名和年龄。select sname,sage from student where sage19 例3-7： (比较大小条件) 求计算机系或信息系年龄大于18岁的学生的姓名、系和年龄。select sname,sdept,sage from student where sage18 and sdept in (cs,is) 例3-8： (确定范围

16、条件) 求年龄在19岁与22岁(含20岁和22岁)之间的学生的学号和年龄。select sno,sage from student where sage between 19 and 22 例3-9： (确定范围条件) 求年龄不在19岁与22岁之间的学生的学号和年龄。select sno,sage from student where sage not between 19 and 22 例3-10：(确定集合条件) 求在下列各系的学生信息：数学系、计算机系。select * from student where sdept in (cs,ma) 例3-11：(确定集合条件) 求不是数学系、计

17、算机系的学生信息。select * from student where sdept not in (cs,ma) 例3-12：(匹配查询) 求姓名是以“李”打头的学生。select * from student where sname like 李% 例3-13：(匹配查询) 求姓名中含有“志”的学生。select * from student where sname like %志% 例3-14：(匹配查询) 求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生。select * from student where sname like %_马_% 例3-15：(匹配查询) 求选修

18、课程001或003，成绩在80至90之间，学号为96xxx的学生的学号、课程号和成绩。select distinct cno,student.sno,score from student,scorewhere student.sno=score.sno and cno in (001,003) and score between 80 and 90and student.sno like 96% 例3-16：(涉及空值查询) 求缺少学习成绩的学生的学号和课程号。select sno,cno from score where score is null 例3-17：(控制行的显示顺序) 求选修0

19、03课程或004课程的学生的学号、课程号和分数。select sno,cno,score from score where cno in (003,004) 例3-18：(组函数) 求学生总人数。select count(distinct sno) from student 例3-19：(组函数) 求选修了课程的学生人数。select count( distinct sno) from score 例3-20：(组函数) 求计算机系学生的平均年龄。select avg( distinct sage) from student where sdept=cs 例3-21：(组函数) 求选修了课程0

20、01的最高、最低与平均成绩。select max(score),min(score),avg(score) from score where o=001 例3-22：(分组查询) 求各门课程的平均成绩与总成绩。select cno,avg(score),count(score) from score group by cno 例3-23：(分组查询) 求各系、各班级的人数和平均年龄。select count(sno),avg(sage),sclassfrom student group by sclass unionselect count(sno),avg(sage),sdeptfrom s

21、tudent group by sdeptselect count(sno),avg(sage),sclass,sdeptfrom student group by sclass,sdept 例3-24：(分组查询) 输入以下查询语句并执行，观察出现的其结果并分析其原因。 SELECT SNAME，SDEPT，COUNT（*）FROM STUDENT WHERE SDEPT=CS GROUP BY SDEPT;因为是按sdept分组的，其他列不在分组后的表中 例3-25：(分组查询) 分析以下语句为什么会出现错误。并给出正确的查询语句。 SELECT SAGE FROM STUDENT GRO

22、UP BY SNO;因为是按sno分组的，sage不在其中SELECT SAGE FROM STUDENT GROUP BY SNO,sage; 例3-26：(分组查询) 求学生人数不足3人的系及其相应的学生数。 select sdept,count(sdept) from studentgroup by sdept having count(sdept)s1.sage 例3-33：(外部连接查询) 求选修了课程002或003的学生的学号、课程号、课程名和成绩。select sno,o,cname,score from score right join course on o=owhere o

23、=002 or o=003 例3-34：(子查询) 求与 李丽 年龄相同的学生的姓名和系。select sname,sdept from studentwhere sage=(select sage from student where sname=李丽) 例3-35：(子查询) 求选修了课程名为 数据结构 的学生的学号和姓名。select sno,sname from studentwhere sno=(select sno from score where cno=(select cno from course where cname=数据结构) 例3-36：(子查询ANY) 求比数学系中

24、某一学生年龄大的学生的姓名和系。 select sname,sdept from student where sage any(select sage from student where sdept=ma) 例3-37：(子查询ALL) 求比数学系中全体学生年龄大的学生的姓名和系。 select sname,sdept from student where sage all(select sage from student where sdept=ma) 例3-38：(子查询EXISTS) 求选修了课程004的学生的姓名和系。 select sname,sdept from student

25、where exists(select * from score where sno=student.sno and cno=004) 例3-39：(返回多列的子查询) 求与 李丽 同系且同龄的学生的姓名和系。 select sname,sdept from student where sdept=(select sdept from student where sname=李丽)and sage=(select sage from student where sname=李丽) 例3-40： (相关子查询) 求未选修课程004的学生的姓名。 select sname from student

26、 where not exists(select * from score where sno=student.sno and cno=004) 实验要求 对数据库表进行各种查询操作。 实验方法 将实验需求用SQL语句表示； 执行SQL语句； 查看执行结果，如果结果不正确，进行修改，直到正确为止。 实验总结 SQL语句以及执行结果；语句验证成功 对重点实验结果进行分析；知道了sql语句的要求，知道了有些语句不能成功运行的原因，并作了修改，完成要求。 实验中的问题和提高；总会遇到一些不会的问题，但是经过对这些问题做了分析和解决，更清晰了语法的要求。 收获与体会。通过不同的语句将相同的问题解决，知

27、道了不同方法有不同的用法，有些用起来更方便，大致知道了sql的基本语句和用法。实验三：视图、授权控制与事务处理 实验日期 2011 年 4 月 20 日 实验目的 通过实验进一步理解视图的建立和更新、数据库的权限管理和事务处理功能。 实验内容 4 SQL视图的定义与操纵： 例4-1： (建立视图) 建立计算机系的学生的视图STUDENT_CS。create view student_csas select * from student where sdept=cs 例4-2： (建立视图) 建立由学号和平均成绩两个字段的视图STUDENT_GR。create view student_gr (

28、 sno,avg_score)as select sno,avg(score) from score group by sno 例4-3： (视图查询) 利用视图STUDENT_CS，求年龄大于19岁的学生的全部信息。select * from student_cswhere sage19 例4-4： (视图查询) 利用视图STUDENT_GR，求平均成绩为88分以上的学生的学号和平均成绩。select sno,avg_score from student_grwhere avg_score=88 例4-5： (视图更新) 利用视图STUDENT_CS，增加学生( 96006，张然，CS，02

29、，男，19 )。insert into student_csvalues(96006,张然,cs,02,男,19) 例4-6： (视图更新) 利用视图STUDENT_CS，将学生年龄增加1岁。观察其运行结果并分析原因。 update student_csset sage=sage+1返回6行，视图中的年龄都加了一岁 例4-7： (视图更新) 利用视图STUDENT_GR，将平均成绩增加2分。观察其运行结果并分析原因。update student_grset avg_score=avg_score+2因为视图student_gr 中有聚合，不能更新，如果平均成绩改变，score表中不能确定是哪些

30、课程的成绩改变能改变平均成绩 例4-8： (视图更新) 删除视图STUDENT_CS中学号为 96006 的学生的全部数据。delete from student_cs where sno=96006 例4-9： (视图更新) 删除视图STUDENT_GR的全部数据。delete from student_gr 例4-10：(删除视图) 删除视图STUDENT_CS和STUDENT_GR。drop view student_cs ,student_gr5 SQL数据控制语句： 例5-1： (授权) 给左右邻近同学（用户）授予在表Student上的SELECT权限，并使这两个用户具有给其他用户授

31、予相同权限的权限。grant select on student to u1,u2with grant option 例5-2： (授权) 给邻近同学（用户）授予Teach表上的所有权限。grant all privileges on teach to u1,u2 例5-3： (授权) 给所有用户授予Score表上的SELECT权限。grant select on score to public 例5-4： (授权验证) 观察左右邻近同学查询你所授权的表中的内容。 例5-5： (收回授权) 收回上面例子中的所有授予的权限。revoke select on student from guest

32、cascaderevoke all privileges on teach from guestrevoke select on score from public6 SQL事务处理： 例6-1： (事务回退) 将课程名称表中的 程序设计 课程学时数修改为80、 编译原理 课程学时数修改为70学时，查询全部课程的总学时数后,取消所有修改(ROLLBACK)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。begin transaction update course set ctime=80 where cname=程序设计update courseset ctime=70 where

33、 cname=编译原理rollback workselect sum(ctime) from course查询结果为750，加上rollback后，结果恢复原值 例6-2： (事务提交) 将课程名称表中的 程序设计 课程学时数修改为80、 编译原理 课程学时数修改为70学时，查询全部课程的总学时数后,确认所有修改(COMMIT)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。begin transaction update course set ctime=80 where cname=程序设计update courseset ctime=70 where cname=编译原理com

34、mitselect sum(ctime) from course 实验要求 建立视图，视图查询，视图更新； 给某一或全部用户授权和收回授权； 事务回退，事务提交。 实验方法 将实验需求用SQL语句表示； 执行SQL语句； 查看执行结果，如果结果不正确，进行修改，直到正确为止。 实验总结 SQL语句以及执行结果； 对重点实验结果进行分析； 实验中的问题和提高；遇到问题解决了，知道了一些基本用法。 收获与体会。知道了视图的基本语法和使用价值，通过对视图的应用和对基本表的对比，知道了有时候，掌握视图的应用更能完成相应的操作。实验四：存储过程与触发器 实验日期 2011 年 4 月 24 日 实验目的

35、 通过实验进一步理解和掌握数据库的存储过程和触发器。 实验内容 7索引与数据库完整性 例7-1： (建立索引) 为Score表按课程号升序、分数降序建立索引，索引名为SC_GRADE。create unique index sc_grade on score(cno asc,score desc) 例7-2： (删除索引) 删除索引SC_GRADE。drop index score.sc_grade例7-3： (修改数据库表) 添加成绩表Score的参照完整性约束关系。ALTER TABLE SCORE ADD CONSTRAINT FK_SC_SNO FOREIGN KEY (SNO) RE

36、FERENCES STUDENT (SNO);ALTER TABLE SCORE ADD CONSTRAINT FK_SC_CNO FOREIGN KEY (CNO) REFERENCES COURSE (CNO); 例7-4： (修改数据库表) 删除成绩表Score的参照完整性约束关系。ALTER TABLE SCORE drop CONSTRAINT FK_SC_SNOALTER TABLE SCORE drop CONSTRAINT FK_SC_CNO8 存储过程与触发器：例8-1： (存储过程) 创建一个显示学生总人数的存储过程。create procedure countstuden

37、tas select count(sno) from student例8-2： (存储过程) 创建显示学生信息的存储过程STUDENT_LIST，并引用STU_COUNT存储过程。create procedure student_lsitas beginselect * from studentexecute countstudentendexecute student_lsit例8-3： (存储过程) 创建一个显示学生平均成绩的存储过程。create procedure avgallscoreas beginselect avg(score)from score endexecute avg

38、score例8-4： (存储过程) 创建显示所有学生平均成绩的存储过程。create procedure avgscoreas beginselect avg(score)from score group by snoendexecute avgscore例8-5：(存储过程) 创建一个对学生姓名进行模糊查找的带参存储过程。create procedure lookname sscore floatas beginselect * from student where sname=%李%and ssex=sscoreendexecute lookname 90例8-7： (触发器) 创建包含插

39、入、删除、修改多种触发事件的触发器Trig_xs_xskc，对xs、kc和xs_kc表进行参照完整性关系的维护。create trigger trig_xs_xskc on xs,kc,xs_kcfor insert,update,delete asconstraint(1) 当在xs_kc表中插入一条记录时，要检查其学号在xs表中和课程号在kc表中是否存在，若不存在则不允许插入。 create trigger xs_kc_tg1 on xs_kc for insert asif exists(select * from inserted where inserted.学号 not in(se

40、lect 学号 from xs)orinserted.课程号 not in(select 课程号 from kc)beginraiserror(违背数据的一致性,16,1)rollback tranend(2) 对xs表进行删除操作，则删除XS_KC表中对应学号的所有记录；对xs表的学号进行更新操作，则更新XS_KC表中对应学号的所有记录； create trigger trg_xs_del on xsfor deleteasdelete from xs_kcwhere 学号 in(select 学号 from deleted)create trigger trg_xs_upd on xsfor updateasupdate from xs_kcwhere 学号 in(select 学号 from updated)(3) 对kc表进行删除操作，则删除XS_KC表中对应课程号的所有记录；对kc表的课程号进行更新操作，则更新XS_KC表中对应课程号的所