安徽工业大学数据库实验报告.doc

数据库系统概论实验报告书专业班级学 号姓 名指导教师安徽工业大学计算机科学与技术学院目录实验一：数据定义语言 1实验二：数据操纵语言 10实验三：数据查询语言17实验四：视图、授权控制与事务处理43实验五：Oracle存储过程与触发器54数据库学习总结56实验一：数据定义语言 实验日期 年 月 日 实验目的 熟悉Oracle上机环境及Oracle客户端的配置；熟练掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引。 实验内容 Oracle上机环境以及Oracle客户端的配置参见附录。1 SQL数据定义语句： 例1-1： (建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空。创建表Student：CREATE TABLE STUDENT( SNOCHAR(5), SNAMEVARCHAR2(8), SDEPTCHAR(2)NOT NULL, SCLASSCHAR(2) NOT NULL, SAGENUMBER(2),CONSTRAINT SNOS_PK PRIMARY KEY(SNO);创建表Course：CREATE TABLE Course( CNOCHAR(3), CNAMEVARCHAR2(16), CTIMENUMBER(3),CONSTRAINT CNOC_PK PRIMARY KEY(CNO);创建表Teach：CREATE TABLE Teach( TNAMEVARCHAR2(8), TSEXCHAR(2), CNOCHAR(3), TDATEDATE, TDEPTCHAR(2),CONSTRAINT TCT_PK PRIMARY KEY(TNAME,CNO,TDEPT),CONSTRAINT CNOT_FK FOREIGN KEY(CNO) REFERENCES Course(CNO);创建表Score：CREATE TABLE SCORE( SNOCHAR(5), CNOCHAR(3), SCORENUMBER(5，2),CONSTRAINT SCS_PK PRIMARY KEY(SNO,CNO),CONSTRAINT SNOS_FK FOREIGN KEY(SNO) REFERENCES Student(SNO),CONSTRAINT CNOS_FK FOREIGN KEY(CNO) REFERENCES Course(CNO) );插入数据：1、StudentINSERT 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)INSERT INTO Student VALUES(20001,赵薇 ,IS,02,19)2、CourseINSERT INTO Course VALUES(001,数学分析,144)?INSERT INTO Course VALUES(002,普通物理,144)?INSERT INTO Course VALUES(003,微机原理,72)?INSERT INTO Course VALUES(004,数据结构,72)?INSERT INTO Course VALUES(005,操作系统,64)INSERT INTO Course VALUES(006,数据库原理,64)?INSERT INTO Course VALUES(007,DB_Design,48)?INSERT INTO Course VALUES(008,程序设计,56)3、TeachINSERT INTO Teach VALUES(王成钢,男,004,TO_DATE( 1999-09-05, YYYY-MM-DD),CS)?INSERT INTO Teach VALUES(李正科,男,003,TO_DATE( 1999-09-05, YYYY-MM-DD),CS)?INSERT INTO Teach VALUES(严敏 ,女,001,TO_DATE( 1999-09-05, YYYY-MM-DD),MA)?INSERT INTO Teach VALUES(赵高 ,男,004,TO_DATE( 1999-09-05, YYYY-MM-DD),IS)?INSERT INTO Teach VALUES(李正科,男,003,TO_DATE( 2000-02-23, YYYY-MM-DD),MA)?INSERT INTO Teach VALUES(刘玉兰,女,006,TO_DATE( 2000-02-23, YYYY-MM-DD),CS)?INSERT INTO Teach VALUES(王成钢,男,004,TO_DATE( 2000-02-23, YYYY-MM-DD),IS)?INSERT INTO Teach VALUES(马悦 ,女,008,TO_DATE( 2000-09-06, YYYY-MM-DD),CS)?INSERT INTO Teach VALUES(王成钢,男,007,TO_DATE( 1999-09-05, YYYY-MM-DD),CS)?4、ScoreINSERT 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,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, )?INSERT INTO Score VALUES(97002,006,92 )?INSERT INTO Score VALUES(97004,005,90 )?INSERT INTO Score VALUES(97004,006,85 )?INSERT INTO Score VALUES(97004,008,75 )?INSERT INTO Score VALUES(97003,001,59 )?INSERT INTO Score VALUES(97003,003,58 ) 例1-2： (修改数据库表) 在Student表中增加SEX(C,2) 字段。ALTER TABLE Student ADD SEX CHAR(2); 例1-3： (修改列名) 将Student表中列名SEX修改为SSEX。ALTER TABLE Student RENAME COLUMN SEX TO SSEX; 例1-4： (修改数据库表) 将Student表中把Sname 字段修改为Sname(C,10)且为非空。ALTER TABLE Student MODIFY SNAME VARCHAR2(10) NOT NULL; 例1-5： (建立索引) 为Score表按课程号升序、分数降序建立索引，索引名为SC_GRADE。CREATE INDEX SC_GRADE ON Score(CNO ASC,SCORE DESC); 例1-6： (删除索引) 删除索引SC_GRADE。DROP INDEX SC_GRADE; 例1-7： (建立数据库表) 建立数据库表S1(SNO,SNAME,SD,SA)，其字段类型定义与Student表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。CREATE TABLE S1( SNOCHAR(5), SNAMEVARCHAR2(10) NOT NULL, SDCHAR(2) NOT NULL, SANUMBER(2); 例1-8： (修改数据库表) 删除成绩表Score的参照完整性约束关系。TER TABLE Score DROP CONSTRAINT SNOS_FK;ALTER TABLE Score DROP CONSTRAINT CNOS_FK; 例1-9： (修改数据库表) 添加成绩表Score的参照完整性约束关系。ALTER TABLE Score ADD CONSTRAINT SNO_FK FREIGN KEY(SNO) REFERENCES STUDENT(SNO);ALTER TABLE Score ADD CONSTRAINT CNOS_FK FOREIGN?KEY(CNO) REFERENCES Course(CNO); 例1-10： (修改数据库表名) 将数据库表S1改名为Student_Temp。RENAME S1 TO Student_Temp; 实验要求 熟悉Oracle上机环境，掌握Oracle客户端的配置； 建立数据库表，修改数据库表结构，建立、删除索引； 实验方法 按照附录中的操作步骤进行客户端的配置； 将实验需求用SQL语句表示； 执行SQL语句； 查看执行结果，如果结果不正确，进行修改，直到正确为止。 实验总结 SQL语句以及执行结果； 对重点实验结果进行分析； 实验中的问题和提高； 收获与体会。从实验一中我学会了表的建立、表的内部一些格式的修改，我在实验一就把数据全插入了，因为student表的性别列在开始的时候没有建立，所以在开始的他并没有显示，到后来为了和实验报告上的表一样，还得重新添加性别列。我知道了因为各表之间有了关联，所以有些时候简单的修改回行不通，我就遇到了“未找到父项关键字”等等问题，还好后来都改正过来了，总的来说，也算开始了解SQL语言了。实验二：数据操纵语言 实验日期 年 月 日 实验目的 在实验一的基础上，熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。 实验内容 2 SQL数据操纵语句： 例2-1：(插入数据) 按前面各表中的数据分别插入到教学数据库的四个数据库表中。 例2-2：(多行插入) 将表Student中在计算机系(CS)的学生数据插入到表Student_Temp中。INSERTINTOSTUDENT_TEMP(SNO,SNAME,SD,SA)SELECTSNO,SNAME,SDEPT,SAGE FROMSTUDENTWHERESDEPT=CS（以上操作中，注意用COMMIT提交数据，将数据保存到数据库服务器） 例2-3：(利用查询来实现表的定义与数据插入) 求每一个学生的平均成绩，把结果存入数据库表Student_Gr中。CREATE TABLE STUDENT_GR(SNO CHAR(5),SNAME VARCHAR2(10),GAVG NUMBER(5,2); INSERT INTO STUDENT_GR(SNO,GAVG) SELECT SNO,AVG(SCORE) FROM SCORE GROUP BY SNO; 例2-4： (修改数据) 将Student_Temp表中所有学生的年龄加2。UPDATE STUDENT_TEMP SET SA=SA+2; 例2-5： (修改数据) 将Course表中程序设计课时数修改成与数据结构的课时数相同。UPDATE COURSE SET CTIME=(SELECT CTIME FROM COURSE WHERE CNAME=数据结构)WHERE CNAME=程序设计; 例2-6： (插入数据) 向Score表中插入数据（98001, 001, 95），根据返回信息解释其原因。INSERT INTO SCORE VALUES(98001,001,95); 因为Score中的Sno是参照Student表中的Sno,而Student表中没有98001的Sno，所以插入失败。 例2-7： (插入数据) 向Score表中插入数据（97001, 010, 80），根据返回信息解释其原因。INSERT INTO SCORE VALUES(97001,010,80);因为Score中的Cno是参照Course表中的Cno，而Course表中没有010的Cno，所以插入失败。例2-8： (删除数据) 删除Student表中学号为96001的学生信息，根据返回信息解释其原因。DELETE FROM STUDENT WHERE SNO=96001; 因为Score表中的Sno是参照Student表中的Sno，Sno_FK 默认关系是当父表的被删除时，如果该行在子表中引用，则它不能被删除。相当于ON DELETE RESTRICTED.例2-9： (删除数据) 删除Course表中课程号为003 的课程信息，根据返回信息解释其原因。DELETE FROM COURSE WHERE CNO=003; 因为Score表中的Cno是参照Course表中的Cno，Cno_FK 默认关系是当父表的被删除时，如果该行在子表中引用，则它不能被删除。相当于ON DELETE RESTRICTED. 例2-10： (删除数据) 删除学生表Student_Temp中学号以96打头的学生信息。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 PURGE; DROP TABLE STUDENT_GR PURGE; 实验要求 对数据库表进行插入、修改和删除数据的操作。 实验方法 按照附录中的操作步骤进行客户端的配置； 将实验需求用SQL语句表示； 执行SQL语句； 查看执行结果，如果结果不正确，进行修改，直到正确为止。 实验总结 SQL语句以及执行结果； 对重点实验结果进行分析； 实验中的问题和提高； 收获与体会。实验心得：相对实验一来讲，实验二大多是对表的内部数据的修改，以及删除一些不必要的表。在（2-7、2-8、2-9）中删除一些数据时，我就发现因为关联等原因，一些表的数据在删除时会违反完整约束条件所以是删不掉的。主要还是学会了发现问题和解决问题吧。实验三：数据查询语言 实验日期 年 月 日 实验目的 体会SQL语言数据查询功能的丰富和复杂。 实验内容 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,2015-Sage AS 出生年份 from Student; 例3-5： (使用列的别名) 求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。Select Sno AS 学号,2012-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(IS,CS); 例3-8： (确定范围条件) 求年龄在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(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 Sno LIKE 96_ AND SCore between 80 AND 90 AND Cno IN(001,003); 例3-16：(匹配查询) 求课程名中包含 _ 字符的课程号、课程名和学时数。Select Cno,Cname,Ctime from Course WHERE Cname LIKE %_% ESCAPE; 例3-17：(涉及空值查询) 求缺少学习成绩的学生的学号和课程号。Select Sno,Cno from Score WHERE SCore IS NULL; 例3-18：(控制行的显示顺序) 求选修003课程或004课程的学生的学号、课程号和分数，要求按课程号升序、分数降序的顺序显示结果。Select Sno,Cno,SCore from Score WHERE Cno IN(003,004) ORDER BY Cno ASC,SCore DESC; 例3-19：(组函数) 求学生总人数。Select COUNT(Sno) from Student; 例3-20：(组函数) 求选修了课程的学生人数。Select COUNT(DISTINCT Sno) from Score; 例3-21：(组函数) 求计算机系学生的平均年龄。Select AVG(Sage) from Student WHERE Sdept=CS; 例3-22：(组函数) 求选修了课程001的最高、最低与平均成绩。Select MAX(SCore),MIN(SCore),AVG(SCore) from Score WHERE Cno=001; 例3-23：(分组查询) 求各门课程的平均成绩与总成绩。Select AVG(SCore),SUM(SCORE) from Score GROUP BY Cno; 例3-24：(分组查询) 求各系、各班级的人数和平均年龄。Select COUNT(Sno),AVG(Sage) from Student GROUP BY Sdept,Sclass; 例3-25：(分组查询) 输入以下查询语句并执行，观察出现的其结果并分析其原因。 SELECT SNAME，SDEPT，COUNT（*）FROM STUDENT WHERE SDEPT=CS GROUP BY SDEPT;如果在分组后的查询结果中在选择出一组符合条件的分组需用HAVING字句，不能用WHERE字句。否则会出错。 例3-26：(分组查询) 分析以下语句为什么会出现错误。并给出正确的查询语句。 SELECT SAGE FROM STUDENT GROUP BY SNO;因为用的是GROUP BY语句所以查询出来的是一组一组的数据，而Select后面的Sage要与GROUP BY后面的 保持一致。 例3-27：(分组查询) 求学生人数不足3人的系及其相应的学生数。 Select Sdept,COUNT(DISTINCT Sno) from Student GROUP BY Sdept HAVING COUNT(DISTINCT Sno)3; 例3-28：(分组查询) 求各系中除01班之外的各班的学生人数。 Select Sclass,COUNT(Sno) from Student GROUP BY Sdept,Sclass HAVING Sclass 01; 例3-29：(涉及空值的查询) 分别观察各组函数、行的显示顺序以及分组查询与空值的关系。SELECT COUNT(SCore) AS Count FROM Score; SELECT SUM(SCore) AS Sum FROM Score;SELECT AVG(SCore) AS Avg FROM Score; SELECT MAX(SCore) AS Max FROM Score; SELECT MIN(SCore) AS Min FROM Score; SELECT SCore FROM Score ORDER BY SCore DESC; SELECT SCore FROM Score ORDER BY SCore ASC; 例3-30：(连接查询) 求选修了课程001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。SELECT SNAME,CNAME,SCORE FROM STUDENT S,COURSE C,SCORE SC WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND SC.CNO= 001 AND (SCORE NOT BETWEEN 70 AND 90); 例3-31：(连接查询与表的别名) 求选修了课程的学生的学生姓名、课程号和成绩。 Select Sname,Cno,SCore from Student S,Score SC WHERE S.Sno=SC.Sno; 例3-32：(自身连接查询) 求年龄大于 李丽 的所有学生的姓名、系和年龄。 SELECT S1.SNAME,S1.SDEPT,S1.SAGE FROM STUDENT S1,STUDENT S2 WHERE S2.SNAME=李丽 AND S1.SAGES2.SAGE; 例3-33：(外部连接查询) 求选修了课程002或003的学生的学号、课程号、课程名和成绩，要求必须将002和003课程的相关信息显示出来。 Select Sno,Course.Cno,Cname,SCore from Score RIGHT OUTER JOIN Course ON Score.Cno=Course.Cno WHERE SCORE.Cno IN(002,003); 例3-34：(子查询) 求与 黎明 年龄相同的学生的姓名和系。SELECT SNAME,SDEPT FROM Student WHERE SAGE IN ( SELECT SAGE FROM Student WHERE 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 Student WHERE Sdept=MA); 例3-37：(子查询ALL) 求比数学系中全体学生年龄大的学生的姓名和系。 Select Sname,Sdept from Student WHERE SageALL(Select Sage from Student WHERE Sdept=MA); 例3-38：(子查询EXISTS) 求选修了课程004的学生的姓名和系。 SELECT SNO,Sname,Sdept from Student WHERE SNO IN(Select SNO from Score WHERE Cno=004); 例3-39：(返回多列的子查询) 求与 黎明 同系且同龄的学生的姓名和系。 Select Sname,Sdept from Student WHERE (Sdept,Sage)IN(Select Sdept,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：(子查询中使用表连接) 求数学系中年龄相同的学生的姓名和年龄。 SELECT X.SNAME,X.SAGE FROM STUDENT X,STUDENT Y WHERE X.SAGE=Y.SAGE AND X.SDEPT=MA AND Y.SDEPT=MA AND X.SNOY.SNO 例3-42：(连接或嵌套查询) 检索至少选修王成刚老师所授课程中一门课程的女学生姓名。 Select Sname from Student WHERE SSex=女 AND Sno IN(Select Sno from Score WHERE Cno IN(Select DISTINCT Cno from Teach WHERE Tname=王成钢); 例3-43：(嵌套与分组查询) 检索选修某课程的学生人数多于3人的教师姓名。Select DISTINCT Tname, Cno from Teach WHERE Cno IN(SELECT Cno FROM Score GROUP BY CNO HAVING COUNT(Cno)3); 例3-44：(集合查询) 列出所有教师和同学的姓名和性别。Select Sname,SSEX from Student UNION Select Tname,TSEX from Teach; 例3-45：(相关子查询) 求未选修课程004的学生的姓名。 Select SNO,Sname from Student WHERE SNO NOT IN (Select SNO from Score WHERE 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=Course.Cno); 例3-47：(相关子查询) 求至少选修了学生 96002 所选修的全部课程的学生的学号。 SELECT DISTINCT SNO FROM SCORE SC1 WHERE NOT EXISTS(SELECT * FROM SCORE SC2 WHERE SC2.SNO=96002 AND NOT EXISTS(SELECT * FROM SCORE SC3 WHERE SNO=SC1.SNO AND CNO=SC2.CNO) 例3-48：(相关子查询) 求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。Select Sno,Cno,SCore from Score SC WHERE SCore(Select AVG(SCore) from Score SC1 WHERE SC1.Cno=SC.Cno ); 例3-49：(相关子查询) 查询被一个以上的学生选修的课程号。Select DISTINCT Cno from Score SC WHERE