数据库实验报告.doc

数据库概论实验数据库系统概论实验报告书专业班级软件学 号16677777姓 名哈哈指导教师陈业斌 安徽工业大学计算机学院实验一：数据定义/数据操纵语言 实验日期 2016 年 9 月 24 日 实验目的 熟悉Oracle上机环境；熟练掌握和使用DDL语言，建立、修改和删除数据库表；熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。 实验内容 1. SQL数据定义语句： 例1-1： (建立如附录一所示的四张数据表) 按要求建立教学数据库表结构及其完整性约束关系，并将数据分别插入到教学数据库的四个数据库表中。 例1-2： (修改表结构) 在Student表中将SSEX字段修改为char(2),观察返回结果，分析原因。显示：alter table STUDENT modify (SSEX char(2)错误报告 -SQL 错误: ORA-01441: 无法减小列长度, 因为一些值过大01441. 00000 - cannot decrease column length because some value is too big 例1-3： (修改表结构) 在Student表中增加BirthDay(date) 字段。alter table STUDENT add BirthDay DATE; 例1-4： (修改表结构) 在Student表中删除BirthDay字段。alter table STUDENT drop column BirthDay;例1-5： (修改表结构) 在Student表中增加一个默认值约束，约束性别的默认值为男。alter table STUDENT modify (SSEX default 男); 2. SQL数据操纵语句： 例2-1： (插入数据) 执行如下语句向student表中插入一条数据。insert into student(sno,sname,sdept) values(96001,张三,SC);(1) 根据返回信息解释其原因;显示：在行: 5 上开始执行命令时出错 -insert into student(sno,sname,sdept) values(96001,张三,SC)错误报告 -SQL 错误: ORA-01400: 无法将 NULL 插入 (STU.STUDENT.SCLASS)01400. 00000 - cannot insert NULL into (%s)原因：在定义表结构时已将SCLASS约束为NOT NULL。并且学号是主键，表里已经有学号为96001的学生，违反了主键的唯一性约束条件。(2) 修改上面插入语句，让其能正确地插入一条记录。正确的为：insert into student(sno,sname,sdept,sclass) values(97006,张三,SC,02); 例2-2：(插入数据) 执行如下语句向Score表中插入一条数据。insert into Score values(96006,001,85);（1） 根据返回信息解释其原因; 显示：在行: 6 上开始执行命令时出错 -insert into Score values(96006,001,85)错误报告 -SQL 错误: ORA-02291: 违反完整约束条件 (STU.FK_STUDENT_SCORE) - 未找到父项关键字02291. 00000 - integrity constraint (%s.%s) violated - parent key not found原因：在表SCORE中，sno作为其外键，在表STUDENT中学号为96006的学生，所以自然插不进去。（2） 修改上面插入语句，让其能正确地插入一条记录。修改为：insert into Score values(96002,002,85); 例2-3： (修改数据) 将Student表中所有学生的年龄加2。update STUDENT set SAGE=SAGE+2; 例2-4： (修改数据) 将Course表中程序设计课时数修改成50。 update COURSE set CTIME=50 where CNAME=程序设计; 例2-5： (修改数据) 将Student表中的学号96001 改为96010，根据返回信息解释其原因。 update STUDENT set SNO=96010 where SNO=96001; 显示：在行: 10 上开始执行命令时出错 -update STUDENT set SNO=96010 where SNO=96001错误报告 -SQL 错误: ORA-02292: 违反完整约束条件 (STU.FK_STUDENT_SCORE) - 已找到子记录02292. 00000 - integrity constraint (%s.%s) violated - child record found原因：学号为96001的学生已经出现在成绩表SCORE中，不可更改例2-6： (删除数据) 删除Student表中学号为96001的成绩信息，根据返回信息解释其原因。delete from STUDENT where sno=96001;显示：在行: 11 上开始执行命令时出错 -delete from STUDENT where sno=96001错误报告 -SQL 错误: ORA-02292: 违反完整约束条件 (STU.FK_STUDENT_SCORE) - 已找到子记录02292. 00000 - integrity constraint (%s.%s) violated - child record found原因：在表SCORE中已经有学号为96001的成绩记录。例2-7： (创建表) 利用Student表的查询结果创建Sc表，表中记录与Student表相同。create table Scasselect * from STUDENT; 例2-8： (删除数据) 删除Sc表中性别为男的所有学生。 delete from Sc where SSEX=男;例2-9： (删除数据) 删除数据库表Sc中所有学生的数据。truncate table Sc; 例2-10： (删除表) 删除数据库表Sc。 drop table Sc; 实验要求 熟悉SQL Server上机环境； 建立数据库表，修改数据库表结构； 对数据库表进行插入、修改和删除数据的操作。 实验方法 执行SQL语句； 将实验需求用SQL语句表示； 查看执行结果，如果结果不正确，进行修改，直到正确为止。 实验总结 SQL语句以及执行结果； 对重点实验结果进行分析； 实验中的问题和提高； 收获与体会。 附录一. 教学管理数据库设计各表的数据结构和数据如下： 1 学生情况表 Student SNO(C,5) SNAME(VC2,20) 姓名SDEPT(C,2) 系SCLASS(C,2) 班级SSEX(C,3) 性别SAGE(number(2,0)CS：计算机系 年龄 96001 马小燕 CS 01 女 21 96002 黎明 CS 01 男MA：数学系 18 96003 刘东明 MA 01 男 IS：信息系 18 96004 赵志勇 IS 02 男 20 97001 马蓉 MA 02 女 19 97002 李成功 CS 01 男 20 97003 黎明 IS 03 女 19 97004 李丽 CS 02 女 19 96005 司马志明 CS 02 男 18 主关键字：SNO 非空字段：SNAME，SDEPT，SCLASS 2 课程名称表 Course 3 教师授课表 Teach CNO(C,3) 课程号 CNAME(VC2,50) 课程名称CTIME(number(3) 学时数TNAME(VC2,20)教师姓名TSEX(C,3)性别CNO(C,3)课程号TDATE(Date)授课日期TDEPT(C,2)系 001 数学分析 144 王成刚男 004 1999.9.5 CS 002 普通物理 144 李正科男 003 1999.9.5 CS 003 微机原理 80 严敏女 001 1999.9.5 MA 004 数据结构 72 赵高男 004 1999.9.5 IS 005 操作系统 80 李正科男 003 2000.2.23 MA 006 数据库原理 80 刘玉兰女 006 2000.2.23 CS 007 编译原理 60 王成刚男 004 2000.2.23 IS 008 程序设计 40 马悦女 008 2000.9.6 CS主关键字：CNO 候选关键字：CNAME 主关键字：TNAME，CNO，TDEPT 参照关系：Course(CNO) 4 成绩表 Score SNO(C,5)学号CNO(C,3)课程号SCORE(number(4,1)分数SNO(C,5)学号CNO(C,3)课程号SCORE(number(4,1)分数SNO(C,5)学号CNO(C,3)课程号SCORE(number(4,1)分数 96001 001 77.5 96005 004 92 96004 001 87 96001 003 89 96005 005 90 96003 003 91 96001 004 86 96005 006 89 97002 003 91 96001 005 82 96005 007 76 97002 004 96002 001 88 96003 001 69 97002 006 92 96002 003 92.5 97001 001 96 97004 005 90 96002 006 90 97001 008 95 97004 006 85 主关键字：SNO，CNO 参照关系：Student(SNO)，Course(CNO)附录二： 数据库概论实验操作指导创建一个新的方案：SHIYAN 在方案中建立基本数据表的SQL1.建立学生表。create table Student( SNO char(5) primary key, SNAME varchar2(20) not null, SDEPT char(2) not null, SCLASS char(2) not null, SSEX char(3), SAGE number(2,0);insert into Student values(96001,马小燕,CS,01,女,21);insert into Student values(96002,黎明,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,女,19);insert into Student values(96005,司马志明,CS,02,男,18); commit;2建立课程表create table Course(CNO char(3) primary key,CNAME varchar2(50),CTIME number(3,0);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 into COURSE values(007,编译原理,60);insert into COURSE values(008,程序设计,40);commit;3建立教师表create table Teach(TNAME VARCHAR2(20),TSEX CHAR(3),CNO CHAR(3),TDATE date,TDEPT CHAR(2),CONSTRAINT PK_STUDENT PRIMARY KEY(TNAME,CNO,TDEPT),CONSTRAINT FK_C_T FOREIGN KEY (CNO ) REFERENCES Course(CNO);insert into TEACH values(王成刚,男,004,9-5月-99,CS);insert into TEACH values(李正科,男,003,09-5月-99,CS);insert into TEACH values(严敏,女,001,09-5月-99,MA);insert into TEACH values(赵高,男,004,09-5月-99,MA);insert into TEACH values(刘玉兰,女,006,23/2月/00,CS);insert into TEACH values(王成刚,男,004,23/2月/00,IS);insert into TEACH values(马悦,女,008,6/9月/00,CS);COMMIT;4建立成绩表create table Score (sno char(5),cno char(3),Score number(4,1),CONSTRAINT PK_Score primary key(sno,cno),CONSTRAINT FK_Student_Score FOREIGN KEY (sno ) REFERENCES student(sno), CONSTRAINT FK_Sourse_Score FOREIGN KEY (cno) REFERENCES Course(cno);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);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);insert into Score values(96005,007,78);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);COMMIT;实验二：数据查询语言 实验日期 年 月 日 实验目的 体会SQL语言数据查询功能的丰富和复杂。 实验内容 3 SQL数据查询语句： 例3-1： (选择表中的若干列) 求全体学生的学号、姓名、性别和年龄。select SSEX,SAGE,SNO,SNAME from student; 例3-2： (不选择重复行) 求选修了课程的学生学号。select distinct sno from score where sno=score.sno; 例3-3： (选择表中的所有列) 求全体学生的详细信息。 select * from student; 例3-4： (使用表达式) 求全体学生的学号、姓名和出生年份。 select sno,sname,sage from student; 例3-5： (使用列的别名) 求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。 select sno as 学号,sage as 出生年份 from student; 例3-6： (比较大小条件) 求年龄大于19岁的学生的姓名和年龄。 select sname,sage from student where sage19; 例3-7： (比较大小条件) 求计算机系或信息系年龄大于18岁的学生的姓名、系和年龄。 select sname,sage,sdept from student where sdept=CS or sdept=ISand sage18; 例3-8： (确定范围条件) 求年龄在19岁与22岁(含19岁和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(MA,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 %马_; 例3-15：(匹配查询) 求选修课程001或003，成绩在80至90之间，学号为96xxx的学生的学号、课程号和成绩。 select sno,cno,score from score where cno in(001,003)and score between 80 and 90 and sno like 96_; 例3-16：(涉及空值查询) 求缺少学习成绩的学生的学号和课程号。 select sno,cno from score where score is null; 例3-17：(控制行的显示顺序) 求选修003课程学生的学号、课程号和分数，并按分数的降序进行排列。 select sno,cno,score from score where cno=003order by score desc;-升序是asc 例3-18：(组函数) 求学生总人数。 select count(sno) as 总人数 from student; 例3-19：(组函数) 求选修了课程的学生人数。 select count(sno) as 总人数 from score; 例3-20：(组函数) 求计算机系学生的平均年龄。 select avg(sage) as 平均年龄 from student where sdept=CS; 例3-21：(组函数) 求选修了课程001的最高、最低与平均成绩。 select max(score) as 最高成绩,min(score) as 最低成绩,avg(score) as 平均成绩 from score where cno=001; 例3-22：(分组查询) 求各门课程的平均成绩与总成绩。 select cno,avg(score) as 平均成绩,sum(score) as 总成绩 from score group by cno; 例3-23：(分组查询) 求各系、各班级的人数和平均年龄。 select count(sno) as 总人数,avg(sage) as 平均年龄 from student group by sdept,sclass; 例3-24：(分组查询) 输入以下查询语句并执行，观察出现的其结果并分析其原因。 SELECT SNAME，SDEPT，COUNT（*）FROM STUDENT GROUP BY SDEPT; Sname字段具有唯一性，不可叠加 例3-25：(分组查询) 分析以下语句为什么会出现错误。并给出正确的查询语句。 SELECT SAGE FROM STUDENT GROUP BY SNO; 例3-26：(分组查询) 求学生人数不足3人的系及其相应的学生数。 例3-27：(分组查询) 求各系中除01班之外的各班的学生人数。 select count(sno)as 总人数,sdept as 系,sclass as 班 from(select * from student where sclass!=01) group by sdept,sclass; 例3-28：(自然连接查询) 求学生学号、姓名以及其选修课程的课程号和成绩，但查询结果中只能有一个SNO字段。 select sno,cno,score from score where sno in(select sno from score group by score,sno having count(*)=1); 例3-29：(连接查询) 求选修了课程001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。 select sname,cname,score from student,course,score where student.sno=score.sno and o=o and o=001 and not score between 70 and 90; 例3-30：(连接查询与表的别名) 求选修了课程的学生姓名、课程号和成绩。 select sname,cno,score from student st,score sc where st.sno=sc.sno; 例3-31：(自身连接查询) 求年龄大于 李丽 的所有学生的姓名、系和年龄。select sname,sdept,sage from student where sage(select sage from student where sname=李丽); 例3-32：(外部连接查询) 求未选修了任何课程的学生的学号、姓名。 select sname,sno from student where sno not in (select distinct student.sno from student right outer join(select distinct sno s from score,course where o=o) on student.sno=s) 例3-33：(子查询) 求与 李丽 年龄相同的学生的姓名和系。 select sname,sdept from student where sage=(select sage from student where sname=李丽); 例3-34：(子查询) 求选修了课程名为 数据结构 的学生的学号和姓名。select sno,sname from student where sno in(select sno from score where o=(select cno from course where cname=数据结构); 例3-35：(子查询ANY) 求比数学系中某一学生年龄大的学生的姓名和系。select sname,sdept from student where sageany (select sage from student where sdept=MA); 例3-36：(子查询ALL) 求比数学系中全体学生年龄大的学生的姓名和系。select sname,sdept from student where sageall(select sage from student where sdept=MA); 例3-37：(子查询EXISTS) 求选修了课程004的学生的姓名和系。select sname,sdept from student where exists ( select * from score where cno=004); 例3-38：(返回多列的子查询) 求与 李丽 同系且同龄的学生的姓名和系。 select sname,sdept from student where (sdept , sage )= (select sdept,sage from student where sname=李丽); 例3-39： (相关子查询) 求未选修课程004的学生的姓名。select sname from student where not exists (select * from score where cno=004); 例3-40 （from子查询） 求总成绩排名前五的学生学号和总成绩。 select * from (select sno,sum(score) as 总成绩 from score group by sno order by sum(score) desc) where rownum19; 例4-4： (视图查询) 利用视图STUDENT_GR，求平均成绩为88分以上的学生的学号和平均成绩。select 学号,平均成绩 from student_gr where 平均成绩88; 例4-5： (视图更新) 利用视图STUDENT_CS，增加学生( 96006，张然，CS，02，男，19 )。insert into student_cs values(96006,张然,CS,02,男,19); 例4-6： (视图更新) 利用视图STUDENT_CS，将学生年龄增加1岁。观察其运行结果并分析原因。 update student_cs set sage=sage+1;对视图的更新改变了基本表 例4-7： (视图更新) 利用视图STUDENT_GR，将平均成绩增加2分。观察其运行结果并分析原因。update student_gr set 平均成绩=平均成绩+2;不能对含有聚合函数的视图进行更新操作，因为更新操作的结果是会反映到基本表上的，而更新聚合函数无法得知更新基本表的那个元件 例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;drop view student_gr;5 SQL数据控制语句： 例5-1： (授权) 给左右邻近同学（用户）授予在表Student上的SELECT权限，并使这两个用户具有给其他用户授予相同权限的权限。create user user1 identified by user1;grant select on student to user1 with grant option; 例5-2： (授权) 给邻近同学（用户）授予Teach表上的所有权限。grant all on teach to user1; 例5-3： (授权) 给所有用户授予Score表上的SELECT权限。grant select on score to public; 例5-4： (收回授权) 收回上面例子中的所有授予的权限。revoke all on teach from user1;revoke select on score from public;revoke select on student from user1;6 SQL事务处理： 例6-1： (事务回退) 将Course表中的 程序设计 课程学时数修改为80、 编译原理 课程学时数修改为70学时，查询全部课程的总学时数后,取消所有修改(ROLLBACK)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。update course set ctime=80 where cname=程序设计;update course set ctime=70 where cname=编译原理;select cname as 课程,ctime as 课时 from course ;rollback;select cname as 课程,ctime as 课时 from course ; 例6-2： (事务提交) 将Course表中的 程序设计 课程学时数修改为80、 编译原理 课程学时数修改为70学时，查询全部课程的总学时数后,确认所有修改(COMMIT)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。update course set ctime=80 where cname=程序设计;update course set ctime=70 where cname=编译原理;select cname as 课程,ctime as 课时 from course ;commit;select cname as 课程,ctime as 课时 from course ;实验四：存储过程与触发器 实验日期 年 月 日 实验目的 通过实验进一步理解和掌握数据库的存储过程和触发器。 实验内容 7索引与数据库完整性 例7-1： (建立索引) 为Score表按课程号升序、分数降序建立索引，索引名为SC_GRADE。create unique index sc_grade on score(sno asc,score desc); 例7-2： (删除索引) 删除索引SC_GRADE。drop index sc_grade;例7-3： (修改数据库表) 添加成绩表Score的参照完整性约束关系。alter table score add constraint fk_sno foreign key(sno) referencesstudent(sno) on delete cascade; 例7-4： (修改数据库表) 删除成绩表Score的参照完整性约束关系。alter table score drop constraint fk_sno;8 存储过程与触发器：例8-1：(存储过程) 创建显示学生信息的存储过程Student_List。 create or replace procedure Student_Listasstu Student%rowtype;cursor CUR_VAR is select * from Student;begin open CUR_VAR;loopfetch CUR_VAR INTO stu;exit when CUR_VAR%notfound;DBMS_output.put_line(stu.SNO|stu.Sname|stu.SDEPT|stu.Sclass|stu.Ssex|stu.Sage);end loop;end;END;例8-2：(存储过程) 创建一个显示学生平均成绩的存储过程Student_Avg。create or replace procedure Student_AvgasAvgGrade