数据库实验指导

数据库系统概论实验报告书专业班级学 号姓 名指导教师安徽工业大学计算机学院实验一：数据定义/数据操纵语言 实验日期 年 月 日 实验目的 熟悉Oracle上机环境及Oracle客户端的配置；熟练掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引；熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。 实验内容 Oracle上机环境以及Oracle客户端的配置参见附录。1 SQL数据定义语句： 例1-1： (建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空。（1）学生表create table student(sno char(10) primary key,sname char(20),sage int,sdept char(30),sclass char(10),)（2）课程表create table course(cno char(10) primary key,cname char(20),period int,credit int,)（3）教师表create table teacher(tname char(8),tsex char(2),cno char(3),tdate char(12),tdept char(2);（4）成绩表create table score(sno char(10),cno char(10),grade float,credit int,/*定义sno cno 为主键*/constraint sc_pk primary key(sno,cno),/*定义外键*/constraint sno_fk foreign key(sno) references student(sno), constraint cno_fk foreign key(cno) references course(cno),) 例1-2： (修改数据库表) 在Student表中增加SEX(C,2) 字段。 alter table student add sex char(2); 例1-3： (修改列名) 将Student表中列名SEX修改为SSEX。 EXEC sp_rename student.sex,ssex,column; 例1-4： (修改数据库表) 将Student表中把Sname 字段修改为Sname(C,10)且为非空。alter table Student alter column sname char(10) NOT NULL; 例1-5： (建立索引) 为Score表按课程号升序、分数降序建立索引，索引名为SC_GRADE。 create unique index sc_grade on score(cno ASC,grade DESC); 例1-6： (删除索引) 删除索引SC_GRADE。 drop index score.sc_grade; 例1-7： (建立数据库表) 建立数据库表S1(SNO,SNAME,SD,SA)，其字段类型定义与Student表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。create table s1(sno char(10) primary key,sname char(20),sa int,sd char(30),) 例1-8： (修改数据库表) 删除成绩表Score的参照完整性约束关系。 alter table score drop constraint sc_pk;alter table score drop constraint sno_fk;alter table score drop constraint cno_fk; 例1-9： (修改数据库表) 添加成绩表Score的参照完整性约束关系。alter table score add constraint sc_pk primary key(sno,cno);alter table score add constraint sno_fk foreign key(sno) references student(sno);alter table score add constraint cno_fk foreign key(cno) references course(cno); 例1-10： (修改数据库表名) 将数据库表S1改名为Student_Temp。 sp_rename s1,student_temp;2 SQL数据操纵语句： 例2-1： (插入数据) 按前面各表中的数据分别插入到教学数据库的四个数据库表中。insert into student values(96001,马小燕,21,CS,01,女);insert into student values(96002,黎明,18,CS,01,男);insert into student values(96003,刘东明,18,MA,01,男);insert into student values(96004,赵志勇,20,IS,02,男);insert into student values(97001,马蓉,19,MA,02,女);insert into student values(97002,李成功,20,CS,01,男);insert into student values(97003,黎明,19,IS,03,女);insert into student values(97004,李丽,19,CS,02,女);insert into student values(96005,司马志明,18,CS,02,男);insert into course values(001,数学分析,144,5);insert into course values(002,普通物理,144,4);insert into course values(003,微机原理,80,3);insert into course values(004,数据结构,72,3);insert into course values(005,操作系统,80,3);insert into course values(006,数据库原理,80,3);insert into course values(007,编译原理,60,2);insert into course values(008,程序设计,40,2);insert into teacher values(王成刚,男,004,1999.9.5,CS);insert into teacher values(李正科,男,003,1999.9.5,CS);insert into teacher values(严敏,女,001,1999.9.5,MA);insert into teacher values(赵高,男,004,1999.9.5,MA);insert into teacher values(刘玉兰,女,006,2000.2.23,CS);insert into teacher values(王成刚,男,004,2000.2.23,IS);insert into teacher values(马悦,女,008,2000.9.6,CS);insert into score values(96001,001,77);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);insert into score values(96002,003,92);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);insert into score values(96005,007,76);insert into score values(96003,001,69);insert into score values(97001,001,96);insert into score values(97001,008,95);insert into score values(96004,001,87);insert into score values(96003,003,91);insert into score values(97002,003,91);insert into score values(97002,004,NULL);insert into score values(97002,006,92);insert into score values(97004,005,90);insert into score values(97004,006,85);例2-2：(多行插入) 将表Student表中计算机系(CS)的学生数据插入到表student_temp中。（以上操作中，注意用COMMIT提交数据，将数据保存到数据库服务器）begin transaction:insert into student_temp(sno,sname,sa,sd)(select sno,sname,sage,sdept from student where sdept=CS);commit; 例2-3：(利用查询来实现表的定义与数据插入) 求每一个学生的平均成绩，把结果存入数据库表Student_Gr中。create table student_gr(sno char(10),average float,)insert into student_gr(select sno,avg(grade) from score group by sno); 例2-4： (修改数据) 将Student_Temp表中所有学生的年龄加2。update student_temp set sa=sa+2; 例2-5： (修改数据) 将Course表中程序设计课时数修改成与数据结构的课时数相同。 update course set period=(select period from course where cname=数据结构)where cname=程序设计; 例2-6： (插入数据) 向Score表中插入数据（98001, 001, 95），根据返回信息解释其原因。insert into score values(98001, 001, 95);错误：INSERT 语句与 COLUMN FOREIGN KEY 约束 sno_fk 冲突。该冲突发生于数据库 学生，表 student, column sno。语句已终止。原因分析：在学生表student中没有学号为98001的学生，插入的这个数据中有sno foreign key references student.sno项，这就表明所有score的元组中的sno必需要在student中存在。而在student中不存在sno为98001的学生，因而插入的项与这个外键规则违背，不允许插入.例2-7： (插入数据) 向Score表中插入数据（97001, 010, 80），根据返回信息解释其原因。 insert into score values(97001, 010, 80);错误:INSERT 语句与 COLUMN FOREIGN KEY 约束 cno_fk 冲突。该冲突发生于数据库 学生，表 course, column cno。语句已终止。原因分析：因为在成绩表score中已经规定了(sno,cno)为该表的主键，这就表明不能有两个元组的主键是相同的，而在score中已经存在了学号为97001，课程号为001的元组，因此不允许插入.例2-8： (删除数据) 删除Student表中学号为96001的学生信息，根据返回信息解释其原因。delete from student where sno=96001;错误：DELETE 语句与 COLUMN REFERENCE 约束 sno_fk 冲突。该冲突发生于数据库 学生，表 score, column sno。语句已终止。原因分析：因为student的sno是score的外键，在删除student的数据时如果score有数据是以student中的数据为外键的话会导致score中的数据在student中找不到对应的数据，这样就违背了外键的规则。例2-9： (删除数据) 删除Course表中课程号为003 的课程信息，根据返回信息解释其原因。delete from course where cno=003;错误：DELETE 语句与 COLUMN REFERENCE 约束 cno_fk 冲突。该冲突发生于数据库 学生，表 score, column cno。语句已终止。原因分析：因为course的cno是score的外键，在删除course的数据时如果score有数据是以course中的数据为外键的话会导致score中的数据在course中找不到对应的数据，这样就违背了外键的规则. 例2-10： (删除数据) 删除学生表Student_Temp中学号以96打头的学生信息。（此操作后，注意用ROLLBACK回退可能更新的数据）begin transaction:delete from student_temp where sno like 96%;rollback; 例2-11： (删除数据) 删除数据库表Student_Temp中所有学生的数据。delete from student_temp; 例2-12：(删除表) 删除数据库表Student_Temp和Student_Gr。drop table student_temp;drop table student_gr; 实验要求 熟悉Oracle上机环境，掌握Oracle客户端的配置； 建立数据库表，修改数据库表结构，建立、删除索引； 对数据库表进行插入、修改和删除数据的操作。 实验方法 按照附录中的操作步骤进行客户端的配置； 将实验需求用SQL语句表示； 执行SQL语句； 查看执行结果，如果结果不正确，进行修改，直到正确为止。 实验总结 SQL语句以及执行结果； 对重点实验结果进行分析； 实验中的问题和提高； 收获与体会。实验二：数据查询语言 实验日期 年 月 日 实验目的 体会SQL语言数据查询功能的丰富和复杂。 实验内容 3 SQL数据查询语句： 例3-1： (选择表中的若干列) 求全体学生的学号、姓名、性别和年龄。select sno,sname,ssex,sage from student; 例3-2： (不选择重复行) 求选修了课程的学生学号。select distinct sno from score where cno is not null; 例3-3： (选择表中的所有列) 求全体学生的详细信息。select * from student; 例3-4： (使用表达式) 求全体学生的学号、姓名和出生年份。select sno,sname,2010-sage from student; 例3-5： (使用列的别名) 求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。select sno 学号,2010-sage 出生年份 from student; 例3-6： (比较大小条件) 求年龄大于19岁的学生的姓名和年龄。select sname,sage from student where sage19; 例3-7： (比较大小条件) 求计算机系或信息系年龄大于18岁的学生的姓名、系和年龄。select sname,sdept,sage from student where (sdept=CS or sdept=IS )and sage18; 例3-8： (确定范围条件) 求年龄在19岁与22岁(含20岁和22岁)之间的学生的学号和年龄。 select sno,sage from student where sage between 20 and 22; 例3-9： (确定范围条件) 求年龄不在19岁与22岁之间的学生的学号和年龄。select sno,sage from student where sage not between 20 and 22; 例3-10：(确定集合条件) 求在下列各系的学生信息：数学系、计算机系。select * from student where sdept=MA or sdept=CS; 例3-11：(确定集合条件) 求不是数学系、计算机系的学生信息。select * from student where sdept not in(MA,CS); 例3-12：(匹配查询) 求姓名是以“李”打头的学生。select * from student where sname like 李%; 例3-13：(匹配查询) 求姓名中含有“志”的学生。select * from student where sname like %志%; 例3-14：(匹配查询) 求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生。select * from student where sname like %马_ and len(sname)=3; 例3-15：(匹配查询) 求选修课程001或003，成绩在80至90之间，学号为96xxx的学生的学号、课程号和成绩。select * from score where cno in(001,003)and grade between 80 and 90 and sno like 96_; 例3-16：(匹配查询) 求课程名中包含 _ 字符的课程号、课程名和学时数。select cno,cname,period from course where cname like %/_% escape /; 例3-17：(涉及空值查询) 求缺少学习成绩的学生的学号和课程号。select sno,cno from score where grade is null; 例3-18：(控制行的显示顺序) 求选修003课程或004课程的学生的学号、课程号和分数，要求按课程号升序、分数降序的顺序显示结果。select * from score where cno in(003,004) order by cno,grade DESC; 例3-19：(组函数) 求学生总人数。select count(*) from student; 例3-20：(组函数) 求选修了课程的学生人数。select count(*) from student where sno in(select distinct sno from score where grade is not null); 例3-21：(组函数) 求计算机系学生的平均年龄。select avg(sage) from student where sdept=CS; 例3-22：(组函数) 求选修了课程001的最高、最低与平均成绩。select max(grade),min(grade),avg(grade) from score where cno=001; 例3-23：(分组查询) 求各门课程的平均成绩与总成绩。select avg(grade),sum(grade) from score group by cno; 例3-24：(分组查询) 求各系、各班级的人数和平均年龄。select sdept,sclass,count(*),avg(sage) from student group by sdept,sclass; 例3-25：(分组查询) 输入以下查询语句并执行，观察出现的其结果并分析其原因。 SELECT SNAME，SDEPT，COUNT（*）FROM STUDENT WHERE SDEPT=CS GROUP BY SDEPT;错误：列 STUDENT.sname 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。原因分析：选择列表中任一非聚合表达式内的所有列都应包含在 group by 列表中，或者 group by 表达式必须与选择列表表达式完全匹配,而上面的语句中sname不在group by子句中。例3-26：(分组查询) 分析以下语句为什么会出现错误。并给出正确的查询语句。 SELECT SAGE FROM STUDENT GROUP BY SNO;错误：列 STUDENT.sage 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。原因分析：选择列表中任一非聚合表达式内的所有列都应包含在 group by列表中，或者 group by表达式必须与选择列表表达式完全匹配,而上面的语句中sage不在group by子句中例3-27：(分组查询) 求学生人数不足3人的系及其相应的学生数。 select sdept,count(*) from student group by sdept having count(*)3; 例3-28：(分组查询) 求各系中除01班之外的各班的学生人数。 select sdept,sclass,count(*) from student where sclass 01 group by sdept,sclass; 例3-29：(涉及空值的查询) 分别观察各组函数、行的显示顺序以及分组查询与空值的关系。 select avg(grade) from score select avg(grade) from score where grade is not null结果一样，不包含空值select max(grade) from scoreselect max(grade) from score where grade is not null 结果一样，不受空值影响 select min(grade) from scoreselect min(grade) from score where grade is not null结构一样，不受空值影响select count(grade) from scoreselect count(grade) from score where grade is not null结果一样，都是20，但比数据库中少一个，说明空值没有被计入总数 例3-30：(连接查询) 求选修了课程001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。 select Sname,Cname,grade from student,course,score where student.Sno=score.Sno and course.Cno=score.Cno and course.Cno=001 and (grade90) 例3-31：(连接查询与表的别名) 求选修了课程的学生的学生姓名、课程号和成绩。select Sname,score.Cno,grade from student,score where student.Sno=score.Sno 例3-32：(自身连接查询) 求年龄大于 李丽 的所有学生的姓名、系和年龄。select S1.Sname,S1.Sdept,S1.Sage from student S1,student S2 where S1.SageS2.Sage and S2.Sname=李丽 ; 例3-33：(外部连接查询) 求选修了课程002或003的学生的学号、课程号、课程名和成绩，要求必须将002和003课程的相关信息显示出来。select Sno,course.Cno,Cname,grade from course left outer join score on(course.Cno=score.Cno)where course.Cno=002or course.Cno=003 例3-34：(子查询) 求与 黎明 年龄相同的学生的姓名和系。 select Sname,Sdept from student where Sage in(Select Sage from student where Sname=黎明) and Sname黎明 例3-35：(子查询) 求选修了课程名为 数据结构 的学生的学号和姓名。 select Sno,Sname from student where Sno in(select Sno from score where Cno=(Select Cno from course where Cname=数据结构) 例3-36：(子查询ANY) 求比数学系中某一学生年龄大的学生的姓名和系。 select Sname,Sdept from student where Sageany (Select Sage from studnet where Sdept=MA)and SdeptMA 例3-37：(子查询ALL) 求比数学系中全体学生年龄大的学生的姓名和系。 select Sname,Sdept from student where Sageall (Select Sage from student where Sdept=MA)and SdeptMA 例3-38：(子查询EXISTS) 求选修了课程004的学生的姓名和系。select Sname,Sdept from student where exists(Select * from score where student.Sno=score.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=李丽)and Sname李丽 例3-40：(多个子查询) 求与 黎明 同系，且年龄大于 李丽 的学生的信息。 select * from student where Sdept in(select Sdept from student where Sname=黎明) and Sage(select Sage from student where Sname=李丽) 例3-41：(子查询中使用表连接) 求数学系中年龄相同的学生的姓名和年龄。 例3-42：(连接或嵌套查询) 检索至少选修王成刚老师所授课程中一门课程的女学生姓名。 select Sname from student where Sno in(select Sno from score where Cno in(select Cno from teacher where Tname=王成刚) and Ssex=女 例3-43：(嵌套与分组查询) 检索选修某课程的学生人数多于3人的教师姓名。select tname from teacher where cno in(select cno from score group by cno having count(cno)3) 例3-44：(集合查询) 列出所有教师和同学的姓名和性别。select sname,ssex from studentunion select tname,tsex from teacher; 例3-45：(相关子查询) 求未选修课程004的学生的姓名。select sname from student where not exists(select * from score where score.sno=student.sno and cno=004) 例3-46：(相关子查询) 求选修了全部课程的学生的姓名。 select sname from student where not exists(select * from course where not exists(select * from score where sno=student.sno and cno=o) 例3-47：(相关子查询) 求至少选修了学生 96002 所选修的全部课程的学生的学号。 例3-48：(相关子查询) 求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。select s1.sno,o,s1.grade from score s1where grade(select avg(grade) from score s2 where s1.sno=s2.sno) 例3-49：(相关子查询) 查询被一个以上的学生选修的课程号。select cno from course where cno in(select cno from score group by cno having count(sno)1)例3-50：(相关子查询) 查询所有未选课程的学生姓名和所在系。select sname,sdept from student where not exists(select * from score where student.sno=score.sno) 实验要求 对数据库表进行各种查询操作。 实验方法 将实验需求用SQL语句表示； 执行SQL语句； 查看执行结果，如果结果不正确，进行修改，直到正确为止。 实验总结 SQL语句以及执行结果； 对重点实验结果进行分析； 实验中的问题和提高； 收获与体会。实验三：视图、授权控制与事务处理 实验日期 年 月 日 实验目的 通过实验进一步理解视图的建立和更新、数据库的权限管理和事务处理功能。 实验内容 4 SQL视图的定义与操纵： 例4-1： (建立视图) 建立计算机系的学生的视图STUDENT_CS。create view student_cs as select * from student where sdept=CS 例4-2： (建立视图) 建立由学号和平均成绩两个字段的视图STUDENT_GR。create view student_gr(sno,average) as select sno,AVG(grade) from score group by sno 例4-3： (视图查询) 利用视图STUDENT_CS，求年龄大于19岁的学生的全部信息。select * from student_cs where sage19 例4-4： (视图查询) 利用视图STUDENT_GR，求平均成绩为88分以上的学生的学号和平均成绩。select * from student_gr where average88 例4-5： (视图更新) 利用视图STUDENT_CS，增加学生( 96006，张然，CS，02，男，19 )。insert into student_cs values(96006,张然,19,CS,02,男) 例4-6： (视图更新) 利用视图STUDENT_CS，将学生年龄增加1岁。观察其运行结果并分析原因。update student_cs set sage=sage+1结果正确 例4-7： (视图更新) 利用视图STUDENT_GR，将平均成绩增加2分。观察其运行结果并分析原因。错误: 服务器: 消息 4403，级别 16，状态 1，行 1视图或函数 student_gr 不可更新，因为它包含聚合。原因分析: 如提示信息所示，不能对含有聚合函数的视图列进行更新操作，因为更新操的结果是会反映到基本表上的，而更新聚合函数无法得知更新基本表的哪个元组. 例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权限，并使这两个用户具有给其他用户授予相同权限的权限。grant select on s1 to s2 with grant option 例5-2： (授权) 给邻近同学（用户）授予Teach表上的所有权限。Grant all privileges on teacher to s1 例5-3： (授权) 给所有用户授予Score表上的SELECT权限。Grant select on score to public 例5-4： (授权验证) 观察左右邻近同学查询你所授权的表中的内容。以s1的用户名登陆：Select *from course服务器: 消息 229，级别 14，状态 5，行 1拒绝了对对象 course（数据库 master，所有者 dbo）的 SELECT 权限。Select * from teacher能够查出相应的内容Update teacher set tsex=tsex能够更新相应的内容 例5-5： (收回授权) 收回上面例子中的所有授予的权限。Revoke all privileges on teacher from public6 SQL事务处理： 例6-1： (事务回退) 将课程名称表中的 程序设计 课程学时数修改为80、微机原理 课程学时数修改为70学时，查询全部课程的总学时数后,取消所有修改(ROLLBACK)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。Begin transaction:Update course set ctime=80 where cname=程序设计;Update course set ctime=70 where cname=微机原理;Select ctime from course;Rollback;Select ctime from course; 例6-2： (事务提交) 将课程名称表中的 程序设计 课程学时数修改为80、微机原理 课程学时数修改为70学时，查询全部课程的总学时数后,确认所有修改(COMMIT)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。Begin transaction: Update course set ctime=80 where cname=程序设计;Update course set ctime=70 where cname=微机原理;Select ctime from course;Commit:Select ctime from course; 实验要求 建立视图，视图查询，视图更新； 给某一或全部用户授权和收回授权； 事务回退，事务提交。 实验方法 将实验需求用SQL语句表示； 执行SQL语句； 查看执行结果，如果结果不正确，进行修改，直到正确为止。 实验总结 SQL语句以及执行结果； 对重点实验结果进行分析； 实验中的问题和提高； 收获与体会。实验四：存储过程与触发器 实验日期 年 月 日 实验目的 通过实验进一步理解和掌握Oracle数据库的存储过程和触发器。 实验内容 4 存储过程与触发器：例7-1： (存储过程) 创建一个显示学生总人数的存储过程。create procedure student_count as select count(*) from student例7-2： (存储过程) 创建显示学生信息的存储过程STUDENT_LIST，并引用STU_COUNT存储过程。create procedure student_list as select * from studentstudent_count例7-3： (存储过程) 创建一个显示学生平均成绩的存储过程。Create pr