数据库系统概论实验报告

1、数据库系统概论 实验报告书专业班级学 号姓 名指导教师安徽工业大学计算机学院实验一：数据定义/数据操纵语言实验目的熟悉Oracle上机环境及 Oracle客户端的配置；熟练掌握和使用 DDL语言，建 立、修改和删除数据库表、主键、外键约束关系和索弓I;熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。实验内容1. SQL数据定义语句：例1-1 :（建立数据库表） 建立教学数据库的四个数据库表，其中Student表中不包含SSEX（C,2）字段，Sname字段为Sname（C,8）且可为空。例1-2 :（修改数据库表）在Student表中增加SEX（C,2）字段。例1-3 :（修改列名）

2、 将Student表中列名 SEX修改为SSEX例1-4 :（修改数据库表）将Student表中把Sname字段修改为Sname（C,10）且为非空。例1-5 :（建立索引）为Score表按课程号升序、分数降序建立索引，索引名为SC_GRADE例1-6 :（删除索引）删除索引SC_GRAD>E例1-7 :（建立数据库表） 建立数据库表S1（SNO,SNAME,SD,SA）其字段类型定义与Student表中的相应字段（SNO,SNAME,SDEPT,SAG的数据类型定义相同。例1-8 :（修改数据库表）删除成绩表Score的参照完整性约束关系。例1-9 :（修改数据库表）添加成绩表Scor

3、e的参照完整性约束关系。例1-10 :（修改数据库表名）将数据库表S1改名为Student_Temp。2. SQL数据操纵语句：例2-1 :（插入数据）按前面各表中的数据分别插入到教学数据库的四个数据库表中。例2-2 :（多行插入）将表Student中在计算机系（CS）的学生数据插入到表 Student_Temp 中。例2-3 :（利用查询来实现表的定义与数据插入）求每一个学生的平均成绩，把结果存入数据库表Student_Gr中。例2-4 :（修改数据）将Student_Temp表中所有学生的年龄加2。例2-5 :（修改数据）将Course表中程序设计课时数修改成与 数据结构的课时数相同。例2

4、-6 :（插入数据）向Score表中插入数据（98001', 001',95 ）,根据返回信息解释其 原因。例2-7 :（插入数据）向Score表中插入数据（97001 ', 010',80 ）,根据返回信息解释 其原因。例2-8 :（删除数据）删除Student表中学号为96001'的学生信息，根据返回信息解释其原因。例2-9 :（删除数据）删除Course表中课程号为003'的课程信息，根据返回信息解释其原因。例2-10 :（删除数据）删除学生表Student_Temp中学号以96打头的学生信息。例2-11 :（删除数据）删除数据库表Stud

5、ent_Temp中所有学生的数据。例2-12 :（删除表）删除数据库表 Student_Temp 和 Student_Gr 。实验程序create table stude nt( sno char(5) primary key, sn ame char(8), sdept char(2) not nu II, sclass char(2) not n ull, sage nu meric(2)create table course(eno char(3) primary key, cn ame char(16) uniq ue, ctime nu meric(3)create table te

6、ach(tn ame char(8),tsex char(2),eno char(3), tdate datetime,tdept char(2),primary key(t name,cn o,tdept),foreig n key(c no) referen ces course(c no)create table score(sno char(5),score nu meric(5,2)primary key(s no,cno),foreig n key(s no) referen ces stude nt(s no),foreig n key(c no) referen ces cou

7、rse(c no)alter table stude nt add sex char(2)sp_re name 'stude nt.sex','ssex','colu mn'alter table stude nt alter colu mn sn ame char(10) not n ullcreate unique in dex sc_grade on score(s no ,score desc)drop in dex score.sc_gradecreate table s1(sno char(5) primary key,sn ame

8、char(8),sd char(2),sa nu meric(2)alter table score drop con stra int fk_score_stude ntalter table score add con stra int fk_score_stude ntforeig n key (sno)references stude nt(s no) on update cascade on delete cascadesp_re name 's1','stude nt_temp'in sert into stude nt values('96

9、001','马小燕','CS','01',21,'女');in sert into stude nt values('96002','黎明','CS','01',18,'男');in sert into stude nt values('96003','刘东明','MA','01',18,'男');in sert into stude nt values('

10、96004','赵志勇','IS','02',20,'男');in sert into stude nt values('97001','马蓉','MA','02',19,'女');in sert into stude nt values('97002','李成功','CS','01',20,'男');in sert into stude nt values(

11、9;97003','黎明','IS','03',19,'女');女')；in sert into stude nt values('96005','司马志明','CS','02',18,'in sert into Course values('001','数学分析',144);in sert into Course values('002','普通物理',144);in sert

12、into Course values('003','微机原理',80);in sert into course values('004','数据结构',72);in sert into course values('005','操作系统',80);in sert into course values('006','数据库原理',80);in sert into course values('007','编译原理',60);in sert

13、 into course values('008','程序设计',40);in sert into teach values('王成冈 U','男','004','1999.9.5','CS');in sert into teach values('李正科','男','003','1999.9.5','CS');insert into teach values('严敏','女

14、9;,'001','1999.9.5','MA');insert into teach values('赵高','男','004','1999.9.5','MA');in sert into teach values('刘玉兰','女','006','2000.2.23','CS')in sert into teach values('王成冈 U','男',

15、'004','2000.2.23','IS');insert into teach values('马悦','女','008','2000.9.6','CS');insert into student values('97004','李丽','CS','02',19,'男');in sert into score values('96001','001',7

16、7);in sert into score values('96001','003',89);in sert into score values('96001','004',86);in sert into score values('96001','005',82);in sert into score values('96002','001',88);in sert into score values('96002','003',9

17、2);in sert into score values('96002','006',90);in sert into score values('96005','004',92);in sert into score values('96005','005',90);in sert into score values('96005','006',89);in sert into score values('96005','007',7

18、6);in sert into score values('96003','001',69);in sert into score values('97001','001',96);in sert into score values('97001','008',95);in sert into score values('96004','001',87);in sert into score values('96003','003',9

19、1);in sert into score values('97002','003',91);in sert into score values('97002','004',NULL);in sert into score values('97002','006',92);in sert into score values('97004','005',90);in sert into score values('97004','006'

20、,85);in sert into stude nt_temp values('96001','马小燕','CS',21)in sert into stude nt_temp values('96002','黎明','CS',18)in sert into stude nt_temp values('97002','李成功','CS',20)in sert into stude nt_temp values('97004','李

21、丽','CS',19)in sert into stude nt_temp values('96005','司马志明','CS',18)select avg(score) avg1 into stude nt_gr from scoregroup by sno update stude nt_tempset sa=sa+2;select * from stude nt_grupdate courseset ctime=(select ctime from course clwhere ame='数据结构')

22、where cname='程序设计'插入的数据(98001' , 001' , 95 )中，student中没有98001这个学号插入的数据(97001 ,010' , 80 )中，course中没有010这个课程号删除的数据中，96001这个学号在score中有记录 删除的数据中，003这个课程号在score中有记录 delete from stude nt_temp where sno like '96%' delete from stude nt_temp drop table stude nt_temp,stude nt_gr实验

23、总结本次试验让我初步认识了Oracle上机环境及 Oracle客户端的配置；熟习，建立、修改和删除数据库表、主键、外键约束关系和索引；熟练掌握怎么对数据进行 增加、修改和删除操作。实验二：数据查询语言实验目的：体会SQL语言数据查询功能的丰富和复杂。实验内容：SQL数据查询语句。例 3-1 :（选择表中的若干列）求全体学生的学号、姓名、性别和年龄。例 3-2 :（不选择重复行）求选修了课程的学生学号。例 3-3 :（选择表中的所有列）求全体学生的详细信息。例 3-4 :（使用表达式）求全体学生的学号、姓名和出生年份。例 3-5 :年份”。（使用列的别名）求学生的学号和出生年份，显示时使用别名“

24、学号”和“出生例 3-6 :（比较大小条件）求年龄大于19岁的学生的姓名和年龄。例 3-7 :（比较大小条件）求计算机系或信息系年龄大于18岁的学生的姓名、系和年龄。例 3-8 :（确定范围条件）求年龄在19岁与22岁（含20岁和22岁）之间的学生的学号和年龄。例 3-9 :（确定范围条件）求年龄不在19岁与22岁之间的学生的学号和年龄。例 3-10 :（确定集合条件）求在下列各系的学生信息：数学系、计算机系。例 3-11 :（确定集合条件）求不是数学系、计算机系的学生信息。例3-12 :（匹配查询） 求姓名是以“李”打头的学生。例3-13 :（匹配查询） 求姓名中含有“志”的学生。例3-14

25、 :（匹配查询）求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生。例3-15 :（匹配查询） 求选修课程001或003，成绩在80至90之间，学号为96xxx的学 生的学号、课程号和成绩。例3-16 :（匹配查询） 求课程名中包含 ''字符的课程号、课程名和学时数。例3-17 :（涉及空值查询）求缺少学习成绩的学生的学号和课程号。例3-18 :（控制行的显示顺序） 求选修003课程或004课程的学生的学号、课程号和分，要求按课程号升序、分数降序的顺序显示结果。例 3-19 :（组函数）求学生总人数。例 3-20 :（组函数）求选修了课程的学生人数。例 3-21 :（组

26、函数）求计算机系学生的平均年龄。例 3-22 :（组函数）求选修了课程001的最高、最低与平均成绩。例3-23 :（分组查询） 求各门课程的平均成绩与总成绩。例3-24 :（分组查询） 求各系、各班级的人数和平均年龄。例3-25 :（分组查询） 输入以下查询语句并执行，观察出现的其结果并分析其原因。SELECT SNAME, SDEPT COUN（ *）FROM STUDENT WHERE SDElPT= GROUP BY SDEPT;例3-26 :（分组查询） 分析以下语句为什么会出现错误。并给出正确的查询语句。SELECT SAGE FROM STUDENT GROUP BY SNO;例3

27、-27 :（分组查询） 求学生人数不足 3人的系及其相应的学生数。例3-28 :（分组查询） 求各系中除01班之外的各班的学生人数。例3-29（涉及空值的查询）分别观察各组函数、行的显示顺序以及分组查询与空值的关系。例3-30 :（连接查询）求选修了课程001且成绩在70分以下或成绩在 90分以上的学生 的姓名、课程名称和成绩。例3-31 :（连接查询与表的别名） 求选修了课程的学生的学生姓名、课程号和成绩。例3-32 :（自身连接查询）求年龄大于李丽的所有学生的姓名、系和年龄。例3-33 :（外部连接查询） 求选修了课程 002或003的学生的学号、课程号、课程名和 成绩，要求必须将 002

28、和003课程的相关信息显示出来。例3-34:（子查询） 求与 黎明年龄相同的学生的姓名和系。例3-35:（子查询） 求选修了课程名为数据结构的学生的学号和姓名。例3-36:（子查询ANY）求比数学系中某一学生年龄大的学生的姓名和系。例3-37:（子查询ALL）求比数学系中全体学生年龄大的学生的姓名和系。例3-38 :（子查询EXISTS）求选修了课程 004的学生的姓名和系。例3-39 :（返回多列的子查询） 求与 李丽同系且同龄的学生的姓名和系。例3-40 :（多个子查询）求与 黎明同系，且年龄大于李丽的学生的信息。例3-41 :（子查询中使用表连接）求数学系中年龄相同的学生的姓名和年龄。例

29、3-42 :（连接或嵌套查询）检索至少选修王成刚老师所授课程中一门课程的女学生姓例3-43 :（嵌套与分组查询）检索选修某课程的学生人数多于3人的教师姓名。例3-44 :（集合查询）列出所有教师和同学的姓名和性别。例3-45 :（相关子查询） 求未选修课程004的学生的姓名。例3-46 :（相关子查询）求选修了全部课程的学生的姓名。例3-47 :（相关子查询）求至少选修了学生96002'所选修的全部课程的学生的学号。例3-48 :（相关子查询）求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。例3-49 :（相关子查询）查询被一个以上的学生选修的课程号。例3-50 :（相关子查

30、询）查询所有未选课程的学生姓名和所在系。实验程序select sno,sn ame,ssex,sagefrom sselect snofrom scgroup by snoselect s.s no,sn ame,sdept,sclass,ssex,sage,sc.score,c.c no ,c.c name,c.ctimefrom s,sc,cwhere s.s no=sc.s noand c.c no=sc.c no;select sno,sn ame,2009-sage birthdayfrom s;select sno 学号,2009-sage出生年份from s;select sn

31、ame,sagefrom swhere sage>19;select sn ame,sdept,sagefrom swhere sdept in ('CS','IS')and sage>18;select sno ,sagefrom swhere sage betwee n 19 and 22;select sno ,sagefrom swhere sage not betwee n 19 and 22;select *from swhere sdept in( 'CS','MA');select *from swhe

32、re sdept not in( 'CS','MA');select sno,sn amefrom swhere sn ame like ' 李 %'select sno,sn amefrom swhere sn ame like % 志 %'select * from swhere sn ame like %马 _'select sno,cno ,scorefrom scwhere cno i n('001','003')and score betwee n 80 and 90and sno li

33、ke '96%'select *from cwhere cno like '%_&'escape '' select sno,cnofrom scwhere score is nu II;select *from scwhere eno i n('003','004')order by eno ,score descselect coun t(s no)from sselect coun t(disti net sno)from scselect avg(sage)from swhere sdept=

34、9;cs'select max(score),m in( score),avg(score) from scwhere cno='001'select eno ,avg(score),sum(score)from scgroup by enoselect sdept,sclass,co un t(sclass),avg(sage)from sgroup by sdept,sclassorder by sdeptselect * from sselect sn ame,sdept,co un t(*)from s在结果的查询中，只有一行，而sname有好几个，故不能显示出

35、select sagefrom sgroup by sno ,sageselect sdept,co un t(*)from sgroup by sdepthavi ng coun t(*)<3select sdept,sclass,co un t(*)from swhere sclass!='O1'group by sdept,sclassorder by sdeptselect sn ame,c name,scorefrom sc,s,cwhere sc.s no=s.s noand c.c no=sc.c noand o='001'and score

36、 not betwee n 70 and 90select sn ame,c no ,scorefrom sc sc1,s s1where s1.s no=sc1.s noand sc1.c no is not nullselect s1.s name,s1.sdept,s1.sagefrom s s1,s s2where s1.sage>s2.sageand s2.sname=' 李丽'select sc.s no ,sc.c no ,c.c name,scorefrom sc,cwhere o i n('002','003')a nds

37、c.c no=c.c noselect sn ame,sdeptfrom swhere sage in(select sagefrom swhere sname='黎明')select sno,sn amefrom swhere sno in(select snofrom scwhere cno=(select enofrom cwhere cname='数据结构') select sn ame,sdept from swhere sage>a ny(select sagefrom swhere sdept='ma')select sn a

38、me,sdeptfrom swhere sage>all(select sagefrom sselect sn ame,sdept from swhere exists(select *from scwhere sc.s no=s.s noand o='004')select sn ame,sdept from s si where exists( select * from s s2where s2.sname=' 李丽'and s2.sdept=s1.sdeptand s2.sage=s1.sageand s2.s name!=s1.s name)se

39、lect *from s siwhere sdept in(select sdeptfrom swhere sname='黎明')and sage>(select sagefrom swhere sname='李丽')select dist inct s3.s name,s3.sage from s s3,s s2and s3.sage=s2.sageand s3.sdept=s2.sdept and s3.s no!=s2.s no order by s3.sage select disti net sn ame from s,se where ssex

40、='女' and s.s no=sc.s no and eno in( select eno from t where tname='王成冈U ')select * from s where sdept='cs' select tn ame from t where exists( select * from sc where sc.c no=t.c no havi ng coun t(*)>3) select sn ame,ssex from s unionselect tn ame,tsex from tselect sn amefro

41、m sselect *from scwhere sc.s no=s.s noand cn o='004')select sn amefrom swhere not exists(select *from cwhere not exists(select *from scwhere sc.s no=s.s noand sc.c no=c.c no)select dist inct snofrom sc sc1where not exists( select *from sc sc2where sc2.s no='96002'and not exists(selec

42、t *from sc sc3where sc3.s no=sc1.s noand sc3.c no=sc2.c no)and sc1.s no!='96002'select *from sc sc1where score>(select avg(score)from sc sc2where sc2.s no=sc1.s no)select enofrom scgroup by enohavi ng coun t(*)>1select sn ame,sdept from s siwhere not exists(select *from scwhere sc.s no

43、=s1.s no)实验总结本次试验使我认识到了体会 SQL语言数据查询功能的丰富和复杂性。也更加加深了对了解和运用SQL语言的兴趣，感觉语言并不是想象中那样的有难度， 反而极大方便了编程的 一些功能实验三：视图、授权控制与事务处理实验目的：通过实验进一步理解视图的建立和更新、数据库的权限管理和事务处理功能。实验内容：1.视图的定义与操纵；例4-1 :（建立视图） 建立计算机系的学生的视图STUDENT_CS例4-2 :（建立视图） 建立由学号和平均成绩两个字段的视图STUDENT_GR例4-3 :（视图查询） 利用视图STUDENT_GS求年龄大于19岁的学生的全部信息。例4-4 :（视图查询

44、）利用视图STUDENT_G求平均成绩为88分以上的学生学号和平均成绩。例4-5 ：（视图更新）利视图STUDENT_G增加学生（96006',张然，CS, 02',男，19 ）。例4-6 :视图更新）利用视图STUDENT_GSW学生年龄增加1岁。观察其结果并分析原因。例 4-7 :（视图更新）利用视图STUDENT_GR将平均成绩增加 2分。观察其运行结果并分析原因。例 4-8 :（视图更新）删除视图STUDENT_C中学号为 96006'的学生的全部数据。例 4-9 :（视图更新）删除视图STUDENT_GR全部数据。例 4-10 :（删除视图）删除视图STUDE

45、NT_C和 STUDENT_GR2. SQL数据控制语句;例5-1 :（授权）给左右邻近同学（用户）授予在表Student上的SELECT权限，并使这两个用户具有给其他用户授予相同权限的权限。例5-2 :（授权）给邻近同学（用户）授予 Teach表上的所有权限。例5-3 :（授权）给所有用户授予 Score表上的SELECT权限。例5-4 :（授权验证） 观察左右邻近同学查询你所授权的表中的内容。例5-5 :（收回授权）收回上面例子中的所有授予的权限。3. SQL事务处理。例6-1 :（事务回退） 将课程名称表中的 程序设计课程学时数修改为 80、 微机原理 课程学时数修改为 70学时，查询全

46、部课程的总学时数后 ，取消所有修改（ROLLBACK）再次查 询全部课程的总学时数。注意比较分析两次查询的结果。例6-2 :（事务提交）将课程名称表中的 程序设计课程学时数修改为 80、微机原理' 课程学时数修改为 70学时，查询全部课程的总学时数后，确认所有修改（COMMIT。再次查询 全部课程的总学时数。注意比较分析两次查询的结果。实验程序1create view stude nt_cs asselect *from swhere sdept='cs'create view stude nt_gr(s no ,avg_score) asselect sno ,avg

47、(score)from scgroup by snoselect *from stude nt_cswhere sage>19from stude nt_grwhere avg_score>88insert into stude nt_csvalues('96006',' 张然','CS','O2',' 男',19)update stude nt_csset sage=sage+1;update stude nt_grset avg_score=avg_score+2;原因是student_gr 表的a

48、vg_score由avg函数生成，不能更新不能删除数据drop view stude nt_cs,stude nt_gr2-exec sp_addlog in logi name='zha ng',passwd='123'exec sp_gra ntdbaccess 'zha ng'exec sp_addlog in logi name='wa ng',passwd='123'exec sp_gra ntdbaccess 'wa ng'grant selecton sto wan g,zha ngw

49、ith gra nt optio ngrant all privilegeson tto wan g,zha nggrant selecton scto public3on scfrom publicrevoke selecton sfrom wan g,zha ngcascaderevoke all privilegeson tfrom wan g,zha ng实验总结本次试验使我进一步理解视图的建立和更新、数据库的权限管理和事务处理功能实验四：存储过程与触发器实验目的：通过实验进一步理解视图的建立和更新、数据库的权限管理和事务处理功能。实验内容：1.存储过程；例6-1 :（事务回退） 将课

50、程名称表中的 程序设计课程学时数修改为 80、 微机原理 课程学时数修改为 70学时，查询全部课程的总学时数后 ，取消所有修改（ROLLBACK）再次查 询全部课程的总学时数。注意比较分析两次查询的结果。例6-2 :（事务提交）将课程名称表中的程序设计课程学时数修改为 80、微机原理课程学时数修改为 70学时，查询全部课程的总学时数后，确认所有修改（COMMIT。再次查询 全部课程的总学时数。注意比较分析两次查询的结果。 2.触发器。例7-1 :（存储过程）创建一个显示学生总人数的存储过程。例7-2 :（存储过程）创建显示学生信息的存储过程STUDENT_LIST并引用STU_COUN存储过程

51、。例7-3 :（存储过程）创建一个显示学生平均成绩的存储过程。例7-4 :（存储过程）创建显示所有学生平均成绩的存储过程。例7-5 :（修改数据库表）在Student表中增加SAVG（N,6,2）字段。例7-6 :（存储过程）创建存储过程，计算每个学生的平均成绩保存到学生表SAVG字段中。例7-7 :（触发器）创建包含插入、删除、修改多种触发事件的触发器DML_LO,对SCORE表的操作进行记录。用INSERTING DELETING UPDATING胃词来区别不同的 DML操作。实验程序create procedure stu_co untasselect coun t(*) from sc

52、reate procedure stude nt_listasselect s.s no ,s.s name,s.sdept,s.sclass,s.ssex,s.sage,sc.c no ,sc.scorefrom s,scwhere s.s no=sc.s no;2stu_co untcreate procedure stu_every_avgasselect sno ,avg(score)from scgroup by snocreate procedure stu_avg_scoreasselect avg(score) from scalter table s add ssex nu

53、meric(6,2)create procedure add_avgasupdate sset s.savg=(select avg(score)from scwhere sc.s no=s.s no)CREATE TABLE logs(LOGD NUMBER(10) PRIMARY KEY,LOG_TABLE VARCHAR2(10) NOT NULL,LOG_DML VARCHAR2(10),LOG_KEY_ID NUMBER(10),LOG_DATE DATE,LOG_USER VARCHAR2(15);CREATE SEQUENCE logs_id_squ INCREMENT BY 1START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;CREATE OR REPLACE TRIGGER DML_LOGBEFOREDELETE OR INSERT OR UPDATEON scFOR EACH ROWBEGINIF INSERTING THENINSERT INTO l