数据库概论实验报告书

1、-PAGE . z.数据库系统概论实验报告书班级：网141：郭杰*：149074269指导教师：周兵工业大学计算机科学与技术学院-. z.实验一：数据定义语言 实验日期 2016年 12 月 9 日 实验目的 熟悉Oracle上机环境及Oracle客户端的配置；熟练掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引。 实验容 Oracle上机环境以及Oracle客户端的配置参见附录。1 SQL数据定义语句： 例1-1： (建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSE*(C,2) 字段，Sname字段为Sname(C,8)且可为空。1、C

2、REATE TABLE S(SNO CHAR(5),SNAME VARCHAR2(8) ,SDEPT CHAR(2) NOT NULL ,SCLASS CHAR(2) NOT NULL ,SAGE NUMBER(2) CHECK(SAGE BETWEEN 12 AND 60),CONSTRAINT SNO_pk PRIMARY KEY(SNO);2、CREATE TABLE C(O CHAR(3),AME VARCHAR2(16) NOT NULL,CTIME NUMBER(3),CONSTRAINT O_pk PRIMARY KEY(O);3、CREATE TABLE T(TNAME VAR

3、CHAR(8) NOT NULL,TSE* CHAR(2) CHECK(TSe* IN (男,女),O CHAR(3),TDATE DATE,TDEPT CHAR(2),CONSTRAINT T_pk PRIMARY KEY(TNAME,O,TDEPT),CONSTRAINT O_fk FOREIGN KEY(O) REFERENCES C(O);4、CREATE TABLE SC(SNO CHAR(5),O CHAR(3),SCORE NUMBER(5,2),CONSTRAINT SC_pk PRIMARY KEY(SNO,O),CONSTRAINT SNO_fk FOREIGN KEY(S

4、NO) REFERENCES S(SNO),CONSTRAINT O2_fk FOREIGN KEY(O) REFERENCES C(O),CONSTRAINT Ck1 CHECK(SCORE=0 and SCORE19; 例3-7： (比拟大小条件) 求计算机系或信息系年龄大于18岁的学生的、系和年龄。SELECT SNAME,SDEPT,SAGE FROM S WHERE(SDEPT=CS OR SDEPT=IS) AND SAGE18; 例3-8： (确定围条件) 求年龄在19岁与22岁(含20岁和22岁)之间的学生的*和年龄。SELECT SNO,SAGE FROM S WHERE S

5、AGE19 AND SAGE=22; 例3-9： (确定围条件) 求年龄不在19岁与22岁之间的学生的*和年龄。SELECT SNO,SAGE FROM S WHERE SAGE22;SELECT SNO,SAGE FROM S WHERE SAGE NOT BETWEEN 19 AND 22; 例3-10：(确定集合条件) 求在以下各系的学生信息：数学系、计算机系。SELECT * FROM S WHERE SDEPT=MA OR SDEPT=CS;select*fromStudentwheresdeptin(MA,CS);select * from Student where SDEPT=

6、MA union select * from Student where SEPT=CS; 例3-11：(确定集合条件) 求不是数学系、计算机系的学生信息。select*fromSwhereSDEPTnotin(MA,CS); 例3-12：(匹配查询) 求是以打头的学生。select*fromSwhereSNAMElike%; 例3-13：(匹配查询) 求中含有志的学生。select * from S where SNAME like %志%; 例3-14：(匹配查询) 求长度至少是三个汉字且倒数第三个汉字必须是马的学生。select * from S where SNAME like %马_

7、; 例3-15：(匹配查询) 求选修课程001或003，成绩在80至90之间，*为96*的学生的*、课程号和成绩。select SNO,O,SCORE from SC where O in(001,003)and SCORE between 80 and 90 and SNO like 96%; 例3-16：(匹配查询) 求课程名中包含 _ 字符的课程号、课程名和学时数。selectO,AME,CTIMEfromCwhereAMElike%_%escape; 例3-17：(涉及空值查询) 求缺少学习成绩的学生的*和课程号。selectSNO,OfromSCwhereSCOREisnull; 例

8、3-18：(控制行的显示顺序) 求选修003课程或004课程的学生的*、课程号和分数，要求按课程号升序、分数降序的顺序显示结果。selectSNO,O,SCOREfromSCwhereOin(003,004)orderbyOasc,SCOREdesc; 例3-19：(组函数) 求学生总人数。selectcount(SNO)fromS; 例3-20：(组函数) 求选修了课程的学生人数。select count(SNO) from S where SNO in (select distinct SNO from SC where SCORE is not null); 例3-21：(组函数) 求计

9、算机系学生的平均年龄。selectavg(SAGE)fromSwhereSDEPT=CS; 例3-22：(组函数) 求选修了课程001的最高、最低与平均成绩。select ma*(SCORE),min(SCORE),avg(SCORE) from SC where O=001; 例3-23：(分组查询) 求各门课程的平均成绩与总成绩。select avg(SCORE),sum(SCORE) from SC group by O; 例3-24：(分组查询) 求各系、各班级的人数和平均年龄。selectSDEPT,SCLASS,count(*),avg(SAGE)fromSgroup by SCL

10、ASS,SDEPT; 例3-25：(分组查询) 输入以下查询语句并执行，观察出现的其结果并分析其原因。 SELECT SNAME，SDEPT，COUNT*FROM S WHERE SDEPT=CS GROUP BY SDEPT; 例3-26：(分组查询) 分析以下语句为什么会出现错误。并给出正确的查询语句。 SELECT SAGE FROM S GROUP BY SNO;对分组后的语句查找满足条件的，用到HAVING 例3-27：(分组查询) 求学生人数缺乏3人的系及其相应的学生数。select SDEPT,count(SNO) from Sgroup by SDEPT having coun

11、t(SNO)3;SELECT后所选择的列，要在后面分组中出现，除非SELECT后面用到集函数 例3-28：(分组查询) 求各系中除01班之外的各班的学生人数。 selectcount(SNO)fromSwhereSCLASS not in(01)groupbySCLASS,SDEPT; 例3-29：(涉及空值的查询) 分别观察各组函数、行的显示顺序以及分组查询与空值的关系。SELECT AVG(SCORE) FROM SC; SELECT AVG(SCORE) FROM SC WHERE SCORE IS NOT NULL;结果都是84.3478261，说明AVG函数将空值没有包括在SELEC

12、T MA*(SCORE) FROM SC; SELECT MA*(SCORE) FROM SC WHERE SCORE IS NOT NULL;结果都是96，说明MA*函数不受空值影响SELECT MIN(SCORE) FROM SC;SELECT MIN(SCORE) FROM SC WHERE SCORE IS NOT NULL;结果都是58，说明空值不是MIN函数的最小值SELECTCOUNT(SCORE)FROMSC;SELECT COUNT(SCORE) FROM SC WHERE SCORE IS NOT NULL;结果是23，而总共有24个数据，因而COUNT函数对值为空的选项不

13、予计算SELECT*FROMSCORDERBYSCORE;结果中分数为NULL的选项排在最后一个位置，而默认的排列顺序是升序的，所以在分组中NULL是在后面的 例3-30：(连接查询) 求选修了课程001且成绩在70分以下或成绩在90分以上的学生的、课程名称和成绩。 selectSNAME,AME,SCOREfromS,C,SCwhereS.SNO=SC.SNOandC.O=SC.OandSC.O=001and(SCORE90); 例3-31：(连接查询与表的别名) 求选修了课程的学生的学生、课程号和成绩。select SNAME,O,SCORE from S S1,SC where S1.S

14、NO=SC.SNO; 24行 例3-32：(自身连接查询) 求年龄大于 丽 的所有学生的、系和年龄。select S1.SNAME,S1.SDEPT,S1.SAGE from S S1,SS2 where S2.SNAME= 丽andS1.SAGES2.SAGE; 例3-33：(外部连接查询) 求选修了课程002或003的学生的*、课程号、课程名和成绩，要求必须将002和003课程的相关信息显示出来。 select SNO,C.O,AME,Score from C left outer join SC on (SC.O=C.O) where C.O in(002,003); 例3-34：(子查

15、询) 求与 黎明 年龄一样的学生的和系。AND后面的select SNAME,SDEPT from S where SAGE in(select SAGE from S where SNAME=黎明) and SNAME黎明; 例3-35：(子查询) 求选修了课程名为 数据构造 的学生的*和。select SNO,SNAME from S where SNO in(select SNO from SC where O in(select O from C where AME=数据构造); 例3-36：(子查询ANY) 求比数学系中*一学生年龄大的学生的和系。select SNAME,SDEPT

16、 from S where SAGEany (select SAGE from S where SDEPT=MA); 例3-37：(子查询ALL) 求比数学系中全体学生年龄大的学生的和系。select SNAME,SDEPT from S where SAGEall (select SAGE from S where SDEPT=MA); 例3-38：(子查询E*ISTS) 求选修了课程004的学生的和系。selectSNAME,SDEPTfromSwheree*ists(select*fromSCwhereSC.SNO=S.SNOandO=004); 例3-39：(返回多列的子查询) 求与

17、黎明 同系且同龄的学生的和系。select S1.SNAME,S1.SDEPT from S S1 where e*ists (select * from S S2 where S2.SNAME=黎明 and S1.SDEPT=S2.SDEPT and S1.SAGE=S2.SAGE) and SNAME黎明; 例3-40：(多个子查询) 求与 黎明 同系，且年龄大于 丽 的学生的信息。 select * from S where SDEPT in(select SDEPT from S where SNAME= 黎明) and SAGE(select SAGE from S where SN

18、AME=丽); 例3-41：(子查询中使用表连接) 求数学系中年龄一样的学生的和年龄。select SNAME,SAGE from S where SNO in(select S1.SNO from S S1, S S2 where S1.SDEPT=MA and S1.SAGE=S2.SAGE);或selectdistinctS1.SNAME,S1.SAGEfromSS1,SS2whereS1.SDEPT=MAandS1.SAGE=S2.SAGE; 例3-42：(连接或嵌套查询) 检索至少选修王成刚教师所授课程中一门课程的女学生。select SNAME from S where SSE*=

19、女 and SNO in (select SNO from SC where O in(select O from T where TNAME=王成刚); 例3-43：(嵌套与分组查询) 检索选修*课程的学生人数多于3人的教师。 select distinct TNAME from T where O in(select O from SC group by O having count(SNO)3); 例3-44：(集合查询) 列出所有教师和同学的和性别。 select SNAME,SSE* from S union select TNAME,TSE* from T;例3-45：(相关子查询

20、) 求未选修课程004的学生的。 selectSNAMEfromSwhereSNOnotin(selectSNOfromSCwhereO=004); 例3-46：(相关子查询) 求选修了全部课程的学生的。 selectSNAMEfromSwherenote*ists(select*fromCwherenote*ists(select*fromSCwhereSNO=S.SNOandO=C.O);未有学生选了全部课程。 例3-47：(相关子查询) 求至少选修了学生 96002 所选修的全部课程的学生的*。 select SNO from S where not e*ists(select * fr

21、om SC Sc1 where Sc1.SNO=96002 and not e*ists(select * from SC Sc2 where Sc2.SNO=S.SNO and Sc1.O=Sc2.O); 例3-48：(相关子查询) 求成绩比所选修课程平均成绩高的学生的*、课程号、和成绩。select Sc1.SNO,Sc1.O,Sc1.SCORE from SC Sc1 where SCORE(select avg(SCORE) from SC Sc2 where Sc1.SNO=Sc2.SNO); 例3-49：(相关子查询) 查询被一个以上的学生选修的课程号。select O from

22、C where O in(select O from SC group by O having count(SNO)1);例3-50：(相关子查询) 查询所有未选课程的学生和所在系。select SNAME,SDEPT from S where SNO not in(select distinct SNO from SC ); 实验要求 对数据库表进展各种查询操作。 实验方法 将实验需求用SQL语句表示； 执行SQL语句； 查看执行结果，如果结果不正确，进展修改，直到正确为止。 实验总结 SQL语句以及执行结果； 对重点实验结果进展分析； 实验中的问题和提高； 收获与体会实验四：视图、授权控制

23、与事务处理 实验日期 2017 年 12 月 7 日 实验目的 通过实验进一步理解视图的建立和更新、数据库的权限管理和事务处理功能。 实验容 4 SQL视图的定义与操纵： 例4-1： (建立视图) 建立计算机系的学生的视图STUDENT_CS。create view STUDENT_CS as select * from S where SDEPT=CS; 例4-2： (建立视图) 建立由*和平均成绩两个字段的视图STUDENT_GR。createviewSTUDENT_GR(SNO,AVERAGE)asselectSNO,avg(SCORE)fromSCgroupbySNO; 例4-3： (

24、视图查询) 利用视图STUDENT_CS，求年龄大于19岁的学生的全部信息。select * from STUDENT_CS where SAGE19; 例4-4： (视图查询) 利用视图STUDENT_GR，求平均成绩为88分以上的学生的*和平均成绩。select*fromSTUDENT_GRwhereAVERAGE88; 例4-5： (视图更新) 利用视图STUDENT_CS，增加学生(96006，然，CS，02，男，19 )。insertintoSTUDENT_CSvalues(96006,然,CS,02,19,男); 例4-6： (视图更新) 利用视图STUDENT_CS，将学生年龄增

25、加1岁。观察其运行结果并分析原因。update STUDENT_CS set SAGE=SAGE+1; 例4-7： (视图更新) 利用视图STUDENT_GR，将平均成绩增加2分。观察其运行结果并分析原因。 updateSTUDENT_GRsetAVERAGE=AVERAGE+2; 例4-8： (视图更新) 删除视图STUDENT_CS中*为 96006 的学生的全部数据。delete from STUDENT_CS where SNO=96006; 例4-9： (视图更新) 删除视图STUDENT_GR的全部数据。deletefromSTUDENT_GR; 例4-10：(删除视图) 删除视图

26、STUDENT_CS和STUDENT_GR。drop view STUDENT_CS;drop view STUDENT_GR;5 SQL数据控制语句： 例5-1： (授权) 给左右邻近同学用户授予在表Student上的SELECT权限，并使这两个用户具有给其他用户授予一样权限的权限。grant select on S to U149074316 with grant option; 例5-2： (授权) 给邻近同学用户授予Teach表上的所有权限。grant all privileges on T to U149074316; 例5-3： (授权) 给所有用户授予Score表上的SELECT

27、权限。grant select on SC to public; 例5-4： (授权验证) 观察左右邻近同学查询你所授权的表中的容。 例5-5： (收回授权) 收回上面例子中的所有授予的权限。revoke select on Student from U149074266; revoke all privileges on Teach from U149074266; revoke select on Score from public;6 SQL事务处理： 例6-1： (事务回退) 将课程名称表中的 程序设计 课程学时数修改为80、微机原理 课程学时数修改为70学时，查询全部课程的总学时数后

28、,取消所有修改(ROLLBACK)。再次查询全部课程的总学时数。注意比拟分析两次查询的结果。update C set CTIME=80 where AME=程序设计;update C set CTIME=70 where AME=微机原理;select * from C;rollback;select * from C; 例6-2： (事务提交) 将课程名称表中的 程序设计 课程学时数修改为80、微机原理 课程学时数修改为70学时，查询全部课程的总学时数后,确认所有修改(MIT)。再次查询全部课程的总学时数。注意比拟分析两次查询的结果。update C set CTIME=80 where A

29、ME=程序设计;update C set CTIME=70 where AME=微机原理;select*fromC; 实验要求 建立视图，视图查询，视图更新； 给*一或全部用户授权和收回授权； 事务回退，事务提交。 实验方法 将实验需求用SQL语句表示； 执行SQL语句； 查看执行结果，如果结果不正确，进展修改，直到正确为止。 实验总结 SQL语句以及执行结果； 对重点实验结果进展分析； 实验中的问题和提高； 收获与体会。实验五：Oracle存储过程与触发器 实验目的 通过实验进一步理解和掌握Oracle数据库的存储过程和触发器。 实验容 4 存储过程与触发器：例7-1： (存储过程) 创立一

30、个显示学生总人数的存储过程。方法一：常用 CREATE OR REPLACE PROCEDURE STU_COUNT AS A NUMBER; BEGIN SELECT COUNT(*) INTO A FROM STUDENT; DBMS_OUTPUT.PUT_LINE(学生总人数为 |A); END;方法二：例7-2： (存储过程) 创立显示学生信息的存储过程STUDENT_LIST，并引用STU_COUNT存储过程。CREATE OR REPLACE PROCEDURE STUDENT_LISTASA NUMBER;CURSOR STUDENT_CURSOR ISSELECT * FROM

31、 STUDENT;BEGINFOR STUDENT_RECORD IN STUDENT_CURSOR LOOPDBMS_OUTPUT.PUT_LINE(STUDENT_RECORD.SNO| |STUDENT_RECORD.SNAME| |STUDENT_RECORD.SDEPT| |STUDENT_RECORD.SCLASS| |STUDENT_RECORD.SSE*| |STUDENT_RECORD.SAGE);END LOOP;STU_COUNT( );END;/例7-3： (存储过程) 创立一个显示学生平均成绩的存储过程。 CREATE OR REPLACE PROCEDURE STU

32、_AVG(P IN SCORE.SNO%TYPE)ASA NUMBER(5,2);BEGINSELECT AVG(SCORE) INTO A FROM SCORE WHERE SNO=P GROUP BY SNO;DBMS_OUTPUT.PUT_LINE(*为 |P| 的学生的平均成绩为： |A);END;/例7-4： (存储过程) 创立显示所有学生平均成绩的存储过程。 CREATE OR REPLACE PROCEDURE STUDENT_SAVG AS SAVG NUMBER(5,2); SSNO CHAR(5); CURSOR SCORE_CURSOR IS SELECT SNO,AVG

33、(SCORE) FROM SCORE GROUP BY SNO; BEGIN OPEN SCORE_CURSOR; LOOP FETCH SCORE_CURSOR INTO SSNO,SAVG; E*IT WHEN SCORE_CURSOR%NOTFOUND; DBMS_OUTPUT.PUT_LINE(SSNO| |SAVG); END LOOP; CLOSE SCORE_CURSOR; END; /例7-5： (修改数据库表) 在Student表中增加SAVG(N,6,2) 字段。ALTER TABLE STUDENT ADD SAVG NUMBER (6，2);例7-6： (存储过程) 创

34、立存储过程，计算每个学生的平均成绩保存到学生表SAVG字段中。CREATE OR REPLACE PROCEDURE STUDENT_INSERTSAVG AS SSAVG NUMBER(5,2); SSNO CHAR(5); CURSOR SCORE_CURSOR IS SELECT SNO,AVG(SCORE) FROM SCORE GROUP BY SNO; BEGIN OPEN SCORE_CURSOR; LOOP FETCH SCORE_CURSOR INTO SSNO,SSAVG; UPDATE STUDENT SET SAVG=SSAVG WHERE SNO=SSNO; E*IT

35、 WHEN SCORE_CURSOR%NOTFOUND; END LOOP; CLOSE SCORE_CURSOR; END;例7-7： (触发器) 当更新学生成绩表SCORE中的学生成绩时，自动计算该学生的平均成绩保存到学生表SAVG字段中。CREATEORREPLACETRIGGERSTU_AVGAFTERUPDATEONSCOREFOREACHROWDECLAREPRAGMAAUTONOMOUS_TRANSACTION;BEGINIFUPDATINGTHENUPDATESTUDENTSETSAVG=(SELECTAVG(SCORE)FROMSCOREWHERESNO=:NEW.SNOGR

36、OUPBYSNO)WHERESNO=:NEW.SNO;MIT;ENDIF;END;例7-8： (触发器) 创立包含插入、删除、修改多种触发事件的触发器DML_LOG，对SCORE表的操作进展记录。用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。先创立事件记录表LOGS，该表用来对操作进展记录。该表的字段含义解释如下： LOG_ID：操作记录的编号，数值型，它是该表的主键，自动增1，可由序列自动生成。 LOG_TABLE：进展操作的表名，字符型，非空，该表设计成可以由多个触发器共享使用。比方我们可以为Student表创立类似的触发器，同样将操作记录到该表。 LO