软121班-沈克-129074160

《数据库系统概论》

实验报告书

安徽工业大学计算机学院

专业班级 学 号 姓 名 指导教师

软121 129074160 沈克 戴小平

《数据库系统概论》实验报告书 安徽工业大学计算机学院

实验一：数据定义语言

[ 实验日期 ] 2014 年 10 月 23 日 [ 实验目的 ]

熟悉Oracle上机环境及Oracle客户端的配置；熟练掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引。 [ 实验内容 ]

Oracle上机环境以及Oracle客户端的配置参见附录。 1． SQL数据定义语句：

例1-1： (建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空。 1,创建表STUDENT CREATE TABLE STUDENT ( SNO CHAR(5) NOT NULL, SNAME CHAR(10), SDEPT CHAR(2) , SCLASS CHAR(2) , SAGE NUMBER(2),

CONSTRAINT SNO_PK PRIMARY KEY(SNO));

2，创建表COURSE CREATE TABLE COURSE( CNO CHAR(3) NOT NULL, CNAME VARCHAR2(16) NOT NULL, CTIME NUMBER(3),

CONSTRAINT CNO_PK PRIMARY KEY(CNO));

3,创建表TEACH CREATE TABLE TEACH( TNAME VARCHAR(8) NOT NULL, TSEX CHAR(2), CNO CHAR(3), TDATE VARCHAR2(9), TDEPT CHAR(2),

1/18

《数据库系统概论》实验报告书 安徽工业大学计算机学院

CONSTRAINT TNAME_PK PRIMARY KEY(TNAME));

4,创建表SCORE CREATE TABLE SCORE( SNO CHAR(5) NOT NULL, CNO CHAR(3) NOT NULL, SCORE NUMBER(5,2),

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，STUDENT

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,'男'); 2，COURSE

INSERT 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，TEACH

2/18

《数据库系统概论》实验报告书 安徽工业大学计算机学院

INSERT INTO TEACH VALUES('王成钢','男','004','1999-09-05','CS'); INSERT INTO TEACH VALUES('李正科','男','003', '1999-09-05','CS'); INSERT INTO TEACH VALUES('严敏','女','001','1999-09-05','MA'); INSERT INTO TEACH VALUES('赵高','男','004','1999-09-05','IS'); INSERT INTO TEACH VALUES('李正科 ','男','003','2000-02-23','MA'); INSERT INTO TEACH VALUES('李玉兰','女','006','2000-02-23','CS'); INSERT INTO TEACH VALUES('王成钢 ','男','004','2000-02-23','IS'); INSERT INTO TEACH VALUES('马悦','女','008','2000-09-06','CS'); INSERT INTO TEACH VALUES('王成钢 ','男','007','1999-09-05','CS'); 4,SCORE

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',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); INSERT INTO SCORE VALUES('97004','008',75); INSERT INTO SCORE VALUES('97003','001',59);

3/18

《数据库系统概论》实验报告书 安徽工业大学计算机学院

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 VARCHAR(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

(SNO CHAR(5) NOT NULL, SNAME VARCHAR2(10), SD VARCHAR2(2),

SA NUMBER(2));

例1-8： (修改数据库表) 删除成绩表Score的参照完整性约束关系。 ALTER TABLE SCORE DROP CONSTRAINT SNO_FK; ALTER TABLE SCORE DROP CONSTRAINT CNO_FK;

例1-9： (修改数据库表) 添加成绩表Score的参照完整性约束关系。

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

RENAME S1 TO Student_Temp;

[ 实验要求 ]

4/18

《数据库系统概论》实验报告书 安徽工业大学计算机学院

① 熟悉Oracle上机环境，掌握Oracle客户端的配置； ② 建立数据库表，修改数据库表结构，建立、删除索引； [ 实验方法 ]

① 按照附录中的操作步骤进行客户端的配置； ② 将实验需求用SQL语句表示； ③ 执行SQL语句；

④ 查看执行结果，如果结果不正确，进行修改，直到正确为止。 [ 实验总结 ]

实验通过使用sql*plus，学习对oracle的客户端创建，在服务器上建立用户，基本表是数据库的核心，所以创建正确的基本表是数据库的使用的前提，本次实验学习正确建立基本表并根据基本表中的属性建立索引和参照完整性约束关系，学习插入，修改，删除基本表。

5/18

《数据库系统概论》实验报告书 安徽工业大学计算机学院

实验二：数据操纵语言

[ 实验日期 ] 2014 年 10 月 28 日 [ 实验目的 ]

在实验一的基础上，熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。 [ 实验内容 ]

2． SQL数据操纵语句：

例2-2：(多行插入 ) 将表Student中在计算机系(‘CS’)的学生数据插入到表Student_Temp中。

INSERT INTO Student_Temp(SNO,SNAME,SD,SA)

SELECT SNO,SNAME,SDEPT,SAGE FROM STUDENT WHERE SDEPT=’CS’;

例2-3：(利用查询来实现表的定义与数据插入) 求每一个学生的平均成绩， 把结果存入数据库表Student_Gr中。 CREATE TABLE Student_Gr( SNO CHAR(8) NOT NULL, SAVG NUMBER(5,2));

INSERT INTO Student_Gr(SNO,SAVG) 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建立的

6/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

例2-7： (插入数据) 向Score表中插入数据（‘97001’, ‘010’, 80），根据返回信息解释其原因。

INSERT INTO SCORE VALUES(‘97001’,’010’,80);

例2-8： (删除数据) 删除Student表中学号为‘96001’的学生信息，根据返回信息解释其原因。 DELETE FROM STUDENT WHERE SNO=’96001’;

因为STUDENT表中的学号已经被别的表作为外键，不可以被删除，违反完整约束条件

例2-9： (删除数据) 删除Course表中课程号为‘003’ 的课程信息，根据返回信息解释其原因。 DELETE FROM COURSE WHERE CNO=’003’;

因为Course表中课程号已经被别的表作为外键，不可以被删除，违反完整约束条件

例2-10： (删除数据) 删除学生表Student_Temp中学号以96打头的学生信息。 （此操作后，注意用ROLLBACK回退可能更新的数据） DELETE FROM Student_Temp WHERE SNO LIKE '96%';

例2-11： (删除数据) 删除数据库表Student_Temp中所有学生的数据。 DELETE FROM Student_Temp;

例2-12：(删除表) 删除数据库表Student_Temp和Student_Gr。

DROP TABLE Student_Temp DROP TABLE Student_Gr; [ 实验要求 ]

对数据库表进行插入、修改和删除数据的操作。 [ 实验方法 ]

① 按照附录中的操作步骤进行客户端的配置；

② 将实验需求用SQL语句表示； ③ 执行SQL语句；

④ 查看执行结果，如果结果不正确，进行修改，直到正确为止。

7/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

[ 实验总结 ]

创建基本表时，首先要确定具体要创建几张基本表，再根据具体某张基本表确定该表的具体属性，为每个属性确定数据类型，再跟数据间的关系确定表的主键和外键。基本表建立完成后，根据基本表的数据类型插入具体的数据

8/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

实验三：数据查询语言

[ 实验日期 ] 2014 年 11 月 2 日 [ 实验目的 ]

体会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,2014-SAGE AS \

9/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

例3-5： (使用列的别名) 求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。

SELECT SNO AS\学号\出生年份\ 例3-6： (比较大小条件) 求年龄大于19岁的学生的姓名和年龄。

SELECT SNAME,SAGE FROM STUDENT WHERE SAGE>19;

例3-7： (比较大小条件) 求计算机系或信息系年龄大于18岁的学生的姓名、系和年龄。

SELECT SNAME,SDEPT,SAGE FROM STUDENT WHERE SDEPT IN('CS','IS') AND SAGE>18;

例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');

10/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

例3-11：(确定集合条件) 求不是数学系、计算机系的学生信息。

SELECT * FROM STUDENT WHERE SDEPT NOT IN('MA','CS');

例3-12：(匹配查询) 求姓名是以“李”打头的学生。

SELECT SNAME FROM STUDENT WHERE SNAME LIKE '李%'; 例3-13：(匹配查询) 求姓名中含有“志”的学生。

SELECT SNAME FROM STUDENT WHERE SNAME LIKE '%志%';

例3-14：(匹配查询) 求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生。

SELECT SNAME 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 CNO,CNAME,CTIME FROM COURSE WHERE CNAME LIKE '%A_%' ESCAPE'A';

11/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

例3-17：(涉及空值查询) 求缺少学习成绩的学生的学号和课程号。

SELECT SNO,CNO FROM SCORE WHERE SCORE IS NULL;

例3-18：(控制行的显示顺序) 求选修003课程或004课程的学生的学号、课程号和分数，要求按课程号升序、

分数降序的顺序显示结果。

SELECT * FROM SCORE WHERE CNO IN('003','004')

ORDER BY CNO ,SCORE DESC;

例3-19：(组函数) 求学生总人数。

SELECT COUNT(*) FROM STUDENT;

12/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

例3-20：(组函数) 求选修了课程的学生人数。

SELECT COUNT(DISTINCT SNO) FROM SCORE WHERE CNO IS NOT NULL; 例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-27：(分组查询) 求学生人数不足3人的系及其相应的学生数。 SELECT SDEPT,COUNT(SNO) FROM STUDENT GROUP BY SDEPT HAVING COUNT(SNO)<3;

13/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

例3-28：(分组查询) 求各系中除01班之外的各班的学生人数。 SELECT SDEPT,SCLASS,COUNT(SNO) FROM STUDENT WHERE SCLASS!='01' GROUP BY SDEPT,SCLASS;

例3-30：(连接查询) 求选修了课程001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。

SELECT SNAME,CNAME,SCORE FROM STUDENT,COURSE,SCORE WHERE SCORE.CNO='001' AND (SCORE>90 OR SCORE<70) AND COURSE.CNO=SCORE.CNO AND STUDENT.SNO=SCORE.SNO;

例3-31：(连接查询与表的别名) 求选修了课程的学生的学生姓名、课程号和成绩。 SELECT SNAME,CNO,SCORE FROM STUDENT,SCORE WHERE STUDENT.SNO=SCORE.SNO AND SCORE.CNO IS NOT NULL;

例3-32：(自身连接查询) 求年龄大于 ’李丽’ 的所有学生的姓名、系和年龄。

SELECT X.SNAME,X.SDEPT,X.SAGE FROM STUDENT X,STUDENT Y WHERE X.SAGE>Y.SAGE

14/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

AND Y.SNAME='李丽';

例3-33：(外部连接查询) 求选修了课程002或003的学生的学号、课程号、课程名和成绩， 要求必须将002和003课程的相关信息显示出来。

SELECT SCORE.SNO,SCORE.CNO,COURSE.CNAME,SCORE.SCORE,COURSE.CTIME FROM COURSE,SCORE WHERE COURSE.CNO IN ('002','003') AND SCORE.CNO=COURSE.CNO;

例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=(

15/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

SELECT CNO FROM COURSE WHERE CNAME='数据结构') );

例3-36：(子查询ANY) 求比数学系中某一学生年龄大的学生的姓名和系。

SELECT SNAME,SDEPT FROM STUDENT

WHERE SAGE>ANY(SELECT SAGE FROM STUDENT WHERE SDEPT='MA') AND SDEPT<>'MA';

例3-37：(子查询ALL) 求比数学系中全体学生年龄大的学生的姓名和系。

SELECT SNAME,SDEPT FROM STUDENT

WHERE SAGE>ALL(SELECT SAGE FROM STUDENT WHERE SDEPT='MA') AND SDEPT<>'MA';

例3-38：(子查询EXISTS) 求选修了课程004的学生的姓名和系。

SELECT SNAME,SDEPT FROM STUDENT WHERE EXISTS ( SELECT * FROM SCORE WHERE CNO='004'

AND SCORE.SNO=STUDENT.SNO);

16/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

例3-39：(返回多列的子查询) 求与 ‘李丽’ 同系且同龄的学生的姓名和系。

SELECT SNAME,SDEPT FROM STUDENT WHERE SDEPT=(

SELECT SDEPT FROM STUDENT WHERE SNAME='李丽' ) AND SAGE=(

SELECT SAGE FROM STUDENT WHERE 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.SNO<>Y.SNO;

17/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

例3-42：(连接或嵌套查询) 检索至少选修王成刚老师所授课程中一门课程的女学生姓名。

SELECT SNAME FROM STUDENT WHERE SNO IN(

SELECT SNO FROM SCORE WHERE CNO IN (

SELECT CNO FROM TEACH WHERE TNAME='王成刚') AND SSEX='女');

例3-43：(嵌套与分组查询) 检索选修某课程的学生人数多于3人的教师姓名。

SELECT TNAME FROM TEACH WHERE CNO IN ( SELECT CNO FROM SCORE GROUP BY CNO

HAVING 3

18/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

例3-44：(集合查询) 列出所有教师和同学的姓名和性别。

SELECT SNAME AS \UNION

SELECT TNAME,TSEX FROM TEACH;

例3-45：(相关子查询) 求未选修课程004的学生的姓名。 SELECT SNAME FROM STUDENT WHERE NOT EXISTS ( SELECT * FROM SCORE WHERE CNO='004'

AND SCORE.SNO=STUDENT.SNO);

例3-46：(相关子查询) 求选修了全部课程的学生的姓名。

SELECT SNAME FROM STUDENT WHERE NOT EXISTS ( SELECT * FROM COURSE WHERE NOT EXISTS

( SELECT * FROM SCORE

19/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

WHERE SCORE.SNO=STUDENT.SNO AND SCORE.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 SC1 WHERE EXISTS(

SELECT * FROM SCORE SC2

20/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

GROUP BY CNO

HAVING SC1.SCORE>AVG(SCORE) AND CNO=SC1.CNO);

例3-49：(相关子查询) 查询被一个以上的学生选修的课程号。

SELECT CNO FROM COURSE WHERE EXISTS (SELECT * FROM SCORE GROUP BY CNO HAVING COUNT(SNO)>1 AND CNO=COURSE.CNO);

例3-50：(相关子查询) 查询所有未选课程的学生姓名和所在系 SELECT SNAME,SDEPT FROM STUDENT WHERE NOT EXISTS(

21/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

SELECT * FROM SCORE

WHERE SCORE.SNO=STUDENT.SNO);

22/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

[ 实验要求 ]

对数据库表进行各种查询操作。 [ 实验方法 ]

① 将实验需求用SQL语句表示； ② 执行SQL语句；

③ 查看执行结果，如果结果不正确，进行修改，直到正确为止。

[ 实验总结 ]

此次实验练习使用了SQL语句按所给要求在基本表里面进行查找，实验涉及到选择表中的若干列、不选择重复行、 选择表中的所有列、使用表达式、使用列的别名、比较大小条件、确定范围条件、确定集合条件、匹配查询、空值查询、控制行的显示顺序、组函数、分组查询、连接查询、自身连接查询、外部连接查询、子查询、连接或嵌套查询、嵌套与分组查询、集合查询、相关子查询。使用SQL语句进行查询，首先要对查询的要求进行分析，这需要好的逻辑，能够正确分析问题，对于复杂的问题运用相关的查询语句逐层进行剖析，一步步整理出层次。在语句书写时应注意书写格式，这样能很清晰的看出语句的层次，这样也有利于修改.

23/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

实验四：视图、授权控制与事务处理

[ 实验日期 ] 2014 年 11 月 5 日 [ 实验目的 ]

通过实验进一步理解视图的建立和更新、数据库的权限管理和事务处理功能。 [ 实验内容 ]

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,GAVG) AS SELECT SNO,AVG(SCORE) FROM SCORE

GROUP BY SNO;

例4-3： (视图查询) 利用视图STUDENT_CS，求年龄大于19岁的学生的全部信息。

SELECT * FROM STUDENT_CS WHERE SAGE>19;

24/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

例4-4： (视图查询) 利用视图STUDENT_GR，求平均成绩为88分以上的学生的学号和平均成绩。

SELECT SNO,GAVG FROM STUDENT_GR WHERE GAVG>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 GAVG=GAVG+2;

因为平均成绩是按照基本表计算得来的，不可以直接更改

例4-8： (视图更新) 删除视图STUDENT_CS中学号为 ‘96006’ 的学生的全部数据。

DELETE FROM STUDENT_CS WHERE SNO='96006';

25/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

例4-9： (视图更新) 删除视图STUDENT_GR的全部数据。

NDELETE FROM STUDENT_GR;

例4-10：(删除视图) 删除视图STUDENT_CS和STUDENT_GR。

DROP VIEW STUDENT_CS; DROP VIEW STUDENT_GR;

5． SQL数据控制语句：

例5-1： (授权) 给左右邻近同学（用户）授予在表Student上的SELECT权限，并使这两个用户具有给其他用户授予相同权限的权限。 GRANT SELECT ON STUDENT TO U099074214 WITH GRANT OPTION

例5-2： (授权) 给邻近同学（用户）授予Teach表上的所有权限。 GRANT ALL ON TEACH TO U099074214;

例5-3： (授权) 给所有用户授予Score表上的SELECT权限。 GRANT SELECT ON SCORE TO PUBLIC;

例5-5： (收回授权) 收回上面例子中的所有授予的权限。

REVOKE SELECT ON STUDENT FROM U099074214; REVOKE ALL ON TEACH FROM PUBLIC; REVOKE SELECT ON SCORE FROM PUBLIC;

26/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

6． SQL事务处理：

例6-1： (事务回退) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、‘微机原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,取消所有修改(ROLLBACK)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。

例6-2： (事务提交) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、‘微机原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,确认所有修改(COMMIT)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。

27/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

[ 实验要求 ]

① 建立视图，视图查询，视图更新； ② 给某一或全部用户授权和收回授权； ③ 事务回退，事务提交。

[ 实验方法 ]

① 将实验需求用SQL语句表示； ② 执行SQL语句；

③ 查看执行结果，如果结果不正确，进行修改，直到正确为止。 [ 实验总结 ]

本次实验学习对SQL的数据控制和事务处理，包括使用GTANT授予权限，REVOKE撤销权限，COMMIT事务提交，ROLLBACK事务提交。数据控制包括事务管理和并发控制，这是数据库的安全性和数据的完整性控制。授权的目的是使不同的用户对不同的数据对象有不同的存取权限。在事务处理中，在数据库增删改时，如果对数据库进行了误操作，在为提交数居前，

28/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

可利用事务回退，返回操作，这样保护了数据的一致和完整。

实验五：Oracle存储过程与触发器

[ 实验日期 ] 2014 年 11 月 11 日 [ 实验目的 ]

通过实验进一步理解和掌握Oracle数据库的存储过程和触发器。 [ 实验内容 ]

4． 存储过程与触发器：

例7-1： (存储过程) 创建一个显示学生总人数的存储过程。 CREATE PROCEDURE STU_COUNT(N OUT NUMBER) AS

29/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

BEGIN

SELECT COUNT(*) INTO N FROM STUDENT; END; /

SET SERVEROUTPUT ON; DECLARE SCOUNT NUMBER; BEGIN

STU_COUNT(SCOUNT);

DBMS_OUTPUT.PUT_LINE('SCOUNT='||SCOUNT); END; /

EXECUTE STU_COUNT;

例7-2： (存储过程) 创建显示学生信息的存储过程STUDENT_LIST，并引用STU_COUNT存储过程。

CTEATE OR REPLACE PROCEDURE STUDENT_LIST AS

P1 STUDENT.SNO%TYPE; P2 STUDENT.SNAME%TYPE; P3 STUDENT.SDEPT%TYPE; P4 STUDENT.SCLASS%TYPE; P5 STUDENT.SAGE%TYPE; P6 STUDENT.SSEX%TYPE; N NUMBER(2);

30/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

CURSOR C_S IS SELECT * FROM STUDENT; BEGIN OPEN C_S; STU_COUNT(N); FOR I IN 1..N LOOP

FETCH C_S INTO P1,P2,P3,P4,P5,P6;

DBMS_OUTPUT.PUT_LINE('SNO:'||p1||’ SNAME:’||p2||’ SDEPT:’||p3|| 'SCLASS:'||p4||’ SSEX:’||p5||’ SAGE:’||p6); END LOOP; END; /

EXECUTE STUDENT_LIST;

例7-3： (存储过程) 创建一个显示学生平均成绩的存储过程。 CREATE OR REPLACE PROCEDURE STU_SAVG(V_SNO IN STUDENT.SNO%TYPE) AS

V_SNAME STUDENT.SNAME%TYPE; V_AVG NUMBER(5,2); C BEGIN

SELECT AVG(SCORE) INTO V_AVG FROM SCORE WHERE SNO=V_SNO; SELECT SNAME INTO V_SNAME FROM STUDENT WHERE SNO=V_SNO;

DBMS_OUTPUT.PUT_LINE('SNAME:'||V_SNAME||' '||'AVG_SCORE:'||V_AVG); END; /

EXECUTE STU_SAVG('97001');

例7-4： (存储过程) 创建显示所有学生平均成绩的存储过程。 CREATE OR REPLACE PROCEDURE SCORE_AVG AS

SAVG NUMBER(7,3);

31/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

BEGIN

SELECT AVG(SCORE) INTO SAVG FROM SCORE; DBMS_OUTPUT.PUT_LINE('SAVG ='|| SAVG); END; /

SET SERVEROUTPUT ON; EXECUTE SCORE_AVG;

例7-5： (修改数据库表) 在Student表中增加SAVG(N,6,2) 字段。 ALTER TABLE STUDENT ADD SAVG NUMBER(6,2);

例7-6： (存储过程) 创建存储过程，计算每个学生的平均成绩保存到学生表SAVG字段中。

create or replace procedure EVERYSTU_AVG as

v_no STUDENT.SNO%TYPE; v_name Student.SNAME%type; v_dept Student.SDEPT%type; v_class Student.SCLASS%type; v_age Student.SAGE%type; v_sex Student.SSEX%type; v_avg Student.SAVG%type;

32/19

《数据库系统概论》实验报告书 安徽工业大学计算机学院

m number(2);

cursor C_ADD is select * from Student; begin

STU_COUNT(m); OPEN C_ADD; FOR i IN 1..m LOOP

FETCH C_ADD INTO v_no,v_name,v_dept,v_class,v_age,v_sex,v_avg;

dbms_output.put_line('SNO:'|| v_no ||' SNAME:'||v_name||' SDEPT:'||v_dept|| ' SCLASS:'||v_class||' SAGE:'||v_age||' SSEX:'||v_sex||' SAVG:'||v_avg); END LOOP; END;

/

[ 实验要求 ]

① 创建、调试和修改、调用和执行Oracle存储过程； ② 创建、调试和修改、测试Oracle触发器。 [ 实验方法 ]

① 将实验需求用SQL语句表示；

② 执行SQL语句；

③ 查看执行结果，如果结果不正确，进行修改，直到正确为止。 ②

[ 实验总结 ]

本次实验学习使用存储过程和触发器。在学习此内容之前,我学习了PL/SQL语言，掌握了基本的语言用法后才能进行存储过程和触发器的编写。在一开始用存储过程输出结果时，却发现没有内容显示，后来才发现在使用dbms_output之前需要使用set serverout on打开serveroutput开关。存储过程的执行可以用直接执行的方法，例如：execute 过程名；或者利用其他过程调用。有关于数据库实验的心得体会，总的来说，受益匪浅。在这些天中，我们学到了很多东西,包括建表，导入数据，查询，插入。最重要的是我们有机会用电脑自己进行实践，没接触的时候总是觉得它比较深奥或是不可接近的新型语言，尽管自己对C语言非常感兴趣，但还是有些心理上的陌生感.通过学习还是学到了好多东西.

33/19

本文来源：https://www.bwwdw.com/article/yaw3.html