《数据库概论》
实验报告书
安徽工业大学计算机学院
实验一：数据定义语言
[ 实验日期 ] 20## 年 12 月 11 日
[ 实验目的 ]
熟悉Oracle上机环境及Oracle客户端的配置；熟练掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引。
[ 实验内容 ]
Oracle上机环境以及Oracle客户端的配置参见附录。
1． SQL数据定义语句：
例1-1： (建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空。
例1-2： (修改数据库表) 在Student表中增加SEX(C,2) 字段。
例1-3： (修改列名) 将Student表中列名SEX修改为SSEX。
例1-4： (修改数据库表) 将Student表中把Sname 字段修改为Sname(C,10)且为非空。
例1-5： (建立索引) 为Score表按课程号升序、分数降序建立索引，索引名为SC_GRADE。
例1-6： (删除索引) 删除索引SC_GRADE。
例1-7： (建立数据库表) 建立数据库表S1(SNO,SNAME,SD,SA)，其字段类型定义与Student表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。
例1-8： (修改数据库表) 删除成绩表Score的参照完整性约束关系。
例1-9： (修改数据库表) 添加成绩表Score的参照完整性约束关系。
例1-10： (修改数据库表名) 将数据库表S1改名为Student_Temp。
[ 实验要求 ]
① 熟悉Oracle上机环境，掌握Oracle客户端的配置；
② 建立数据库表，修改数据库表结构，建立、删除索引；
[ 实验方法 ]
① 按照附录中的操作步骤进行客户端的配置；
② 将实验需求用SQL语句表示；
③ 执行SQL语句；
④ 查看执行结果，如果结果不正确，进行修改，直到正确为止。
[实验总结 ]
① SQL语句以及执行结果；
② 对重点实验结果进行分析；
③ 实验中的问题和提高；
④ 收获与体会。
1-1
SQL> CREATE TABLE Student
2 (
3 SNO CHAR(5),
4 SNAME VARCHAR2(10),
5 SDEPT CHAR(2) NOT NULL,
6 SCLASS CHAR(2) NOT NULL,
7 SAGE NUMBER(2));
8 CONSTRAINT Sno_pk PRIMARY KEY(SNO));
表已创建。
SQL> CREATE TABLE Course
2 (CNO CHAR(3) PRIMARY KEY,
3 CNAME VARCHAR2(16) NOT NULL UNIQUE,
4 CTIME NUMBER(3) CHECK(CTIME>=0));
表已创建。
SQL> CREATE TABLE Teach
2 (TNAME CHAR(8),
3 TSEX CHAR(2) CHECK(TSEX IN('男','女')),
4 CNO CHAR(3),
5 TDATE DATE,
6 TDEPT CHAR(2),
7 CONSTRAINT Teach_pk PRIMARY KEY(TNAME,CNO,TDEPT),
8 CONSTRAINT Teach_CNO_fk FOREIGN KEY(CNO) REFERENCES Course(CNO));
表已创建。
SQL> CREATE TABLE Score
2 (
3 SNO CHAR(5),
4 CNO CHAR(3),
5 SCORE NUMBER(5,2),
6 CONSTRAINT Score_pk PRIMARY KEY(SNO,CNO),
7 CONSTRAINT Teach_SNO_fk FOREIGN KEY(SNO) REFERENCES Student(SNO),
8 CONSTRAINT Score_CNO_fk FOREIGN KEY(CNO) REFERENCES Course(CNO));
表已创建。
1-2
SQL> ALTER TABLE Student ADD SEX CHAR(2);
1-3
SQL> ALTER TABLE Student RENAME COLUMN SEX TO SSEX;
表已更改。
1-4
SQL> ALTER TABLE Student MODIFY(Sname CHAR(10) NOT NULL);
表已更改。
1-5
SQL>CREATE INDEX SC_GRADE ON Score(SNO ASC,SCORE DESC);
索引已创建。
1-6
SQL>DROP INDEX SC_GRADE;
索引已丢弃。
1-7
SQL> CREATE TABLE S1
2 (SNO CHAR(5),
3 SNAME VARCHAR2(10),
4 SD CHAR(2),
5 SA NUMBER(2));
表已创建。
1-8
SQL> ALTER TABLE Score DROP CONSTRAINT Teach_SNO_fk;
表已更改。
SQL> ALTER TABLE Score DROP CONSTRAINT Score_CNO_fk;
表已更改。
1-9
SQL> ALTER TABLE Score ADD CONSTRAINT Teach_SNO_fk FOREIGN KEY(SNO) REFERENCES Student(SNO);
表已更改。
SQL> ALTER TABLE Score ADD CONSTRAINT Score_CNO_fk FOREIGN KEY(CNO) REFERENCES Course(CNO);
表已更改。
1-10
SQL> RENAME S1 TO Student_Temp;
表已重命名。
收获与体会：
这一次实验主要是书上第四章的内容，CREATE、DROP、ALTER和RENAME等语句都比较容易掌握。
实验二：数据操纵语言
[ 实验日期 ] 20## 年 12 月 11 日
[ 实验目的 ]
在实验一的基础上，熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。
[ 实验内容 ]
2． SQL数据操纵语句：
例2-1： (插入数据) 按前面各表中的数据分别插入到教学数据库的四个数据库表中。
例2-2：(多行插入) 将表Student表中计算机系(‘CS’)的学生数据插入到表S1中。
（以上操作中，注意用COMMIT提交数据，将数据保存到数据库服务器）
例2-1： (插入数据) 按前面各表中的数据分别插入到教学数据库的四个数据库表中。
例2-2：(多行插入) 将表Student中在计算机系(‘CS’)的学生数据插入到表Student_Temp中。
（以上操作中，注意用COMMIT提交数据，将数据保存到数据库服务器）
例2-3：(利用查询来实现表的定义与数据插入) 求每一个学生的平均成绩，把结果存入数据库表Student_Gr中。
例2-4： (修改数据) 将Student_Temp表中所有学生的年龄加2。
例2-5： (修改数据) 将Course表中‘程序设计’课时数修改成与‘数据结构’的课时数相同。
例2-6： (插入数据) 向Score表中插入数据（‘98001’, ‘001’, 95），根据返回信息解释其原因。
例2-7： (插入数据) 向Score表中插入数据（‘97001’, ‘010’, 80），根据返回信息解释其原因。
例2-8： (删除数据) 删除Student表中学号为‘96001’的学生信息，根据返回信息解释其原因。
例2-9： (删除数据) 删除Course表中课程号为‘003’ 的课程信息，根据返回信息解释其原因。
例2-10： (删除数据) 删除学生表Student_Temp中学号以96打头的学生信息。
（此操作后，注意用ROLLBACK回退可能更新的数据）
例2-11： (删除数据) 删除数据库表Student_Temp中所有学生的数据。
例2-12：(删除表) 删除数据库表Student_Temp和Student_Gr。
[ 实验要求 ]
对数据库表进行插入、修改和删除数据的操作。
[ 实验方法 ]
对数据库表进行插入、修改和删除数据的操作。
[ 实验方法 ]
① 按照附录中的操作步骤进行客户端的配置；
② 将实验需求用SQL语句表示；
③ 执行SQL语句；
④ 查看执行结果，如果结果不正确，进行修改，直到正确为止。
[实验总结 ]
① SQL语句以及执行结果；
② 对重点实验结果进行分析；
③ 实验中的问题和提高；
④ 收获与体会。
2-1
INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) VALUES('96001','马小燕','CS','01','女',21);
INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) VALUES('96002','黎明','CS','01','男',18);
INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) VALUES('96003','刘东明','MA','01','男',18);
INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) VALUES('96004','赵志勇','IS','02','男',20);
INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) VALUES('97001','马蓉','MA','02','女',19);
INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) VALUES('97002','李成功','CS','01','男',20);
INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) VALUES('97003','黎明','IS','03','女',19);
INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) VALUES('97004','李丽','CS','02','女',19);
INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) VALUES('96005','司马志明','CS','02','男',18);
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);
INSERT INTO Teach VALUES('王成刚','男','004','05-9月-1999','CS');
INSERT INTO Teach VALUES('李正科','男','003','05-9月-1999','CS');
INSERT INTO Teach VALUES('严敏','女','001','05-9月-1999','MA');
INSERT INTO Teach VALUES('赵高','男','004','05-9月-1999','IS');
INSERT INTO Teach VALUES('李正科','男','003','23-2月-00','MA');
INSERT INTO Teach VALUES('刘玉兰','女','006','23-2月-00','CS');
INSERT INTO Teach VALUES('王成刚','男','004','23-2月-00','IS');
INSERT INTO Teach VALUES('马悦','女','008','06-9月-00','CS');
INSERT INTO Teach VALUES('王成刚','男','007','05-9月-1999','CS');
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('96001','001',96);
INSERT INTO Score VALUES('96001','008',95);
INSERT INTO Score VALUES('96004','001',87);
INSERT INTO Score VALUES('96003','003',91);
INSERT INTO Score VALUES('96002','003',91);
INSERT INTO Score(SNO,CNO) VALUES('96002','004');
INSERT INTO Score VALUES('96002','006',92);
INSERT INTO Score VALUES('96004','005',90);
INSERT INTO Score VALUES('96004','006',85);
INSERT INTO Score VALUES('96004','008',75);
INSERT INTO Score VALUES('96003','001',59);
SQL>INSERT INTO Score VALUES('96003','003',58);
2-2
SQL> INSERT INTO Student_Temp SElECT SNO,SNAME,SDEPT,SAGE FROM Student WHERE Student.SDEPT='CS';
2-3:
SQL>CREATE TABLE Student_Gr
(
SNO CHAR(5) PRIMARY KEY,
AVGSCORE NUMBER(5,2));
SQL>INSERT INTO Student_Gr(SNO,AVGSCORE) SELECT SNO,AVG(Score) FROM SCORE GROUP BY SNO;
2-4:
SQL>UPDATE Student_Temp SET SA=SA+2;
2-5:
SQL>UPDATE Course SET CTIME=(SELECT CTIME FROM Course WHERE CNAME='数据结构') WHERE CNAME='程序设计';
2-6:
SQL>INSERT INTO Score VALUES('98001','001',95);
因为违反了完整性约束CONSTRAINT SSno_fk FOREIGN KEY(SNO) REFERENCES Student(SNO) Student表中没有建立98001的学生
2-7:
SQL>INSERT INTO Score VALUES('97001','010',80);
因为违反了完整性约束CONSTRAINT Cno_fk FOREIGN KEY(CNO) REFERENCES Course(CNO) Course表中没有010的课程号
2-8:
SQL>DELETE FROM Student WHERE SNO='96001';
因为违反了完整性约束CONSTRAINT SSno_fk FOREIGN KEY(SNO) REFERENCES Student(SNO)
2-9:
SQL>DELETE FROM Course WHERE CNO='003';
因为违反了完整性约束CONSTRAINT Cno_fk FOREIGN KEY(CNO) REFERENCES Course(CNO)
2-10:
SQL>DELETE FROM Student_Temp WHERE SNO LIKE '96%';
2-11:
SQL>DELETE FROM Student_Temp;
2-12:
SQL>DROP TABLE Student_Temp CASCADE CONSTRAINT;
SQL>DROP TABLE Student_Gr CASCADE CONSTRAINT;
收获与体会：
通过这一节的实验，我已经基本上会用了数据库的操纵语句：INSERT、UPDATE、DELETE等，这些语句和上一节实验的用法基本上都差不多，比较容易上手。
实验三：数据查询语言
[ 实验日期 ] 20## 年 12 月 13 日
[ 实验目的 ]
体会SQL语言数据查询功能的丰富和复杂。
[ 实验内容 ]
3． SQL数据查询语句：
例3-1： (选择表中的若干列) 求全体学生的学号、姓名、性别和年龄。
例3-2： (不选择重复行) 求选修了课程的学生学号。
例3-3： (选择表中的所有列) 求全体学生的详细信息。
例3-4： (使用表达式) 求全体学生的学号、姓名和出生年份。
例3-5： (使用列的别名) 求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。
例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：(匹配查询) 求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生。
例3-15：(匹配查询) 求选修课程001或003，成绩在80至90之间，学号为96xxx的学生的学号、课程号和成绩。
例3-16：(匹配查询) 求课程名中包含 ’_’ 字符的课程号、课程名和学时数。
例3-17：(涉及空值查询) 求缺少学习成绩的学生的学号和课程号。
例3-18：(控制行的显示顺序) 求选修003课程或004课程的学生的学号、课程号和分数，要求按课程号升序、分数降序的顺序显示结果。
例3-19：(组函数) 求学生总人数。
例3-20：(组函数) 求选修了课程的学生人数。
例3-21：(组函数) 求计算机系学生的平均年龄。
例3-22：(组函数) 求选修了课程001的最高、最低与平均成绩。
例3-23：(分组查询) 求各门课程的平均成绩与总成绩。
例3-24：(分组查询) 求各系、各班级的人数和平均年龄。
例3-25：(分组查询) 输入以下查询语句并执行，观察出现的其结果并分析其原因。
SELECT SNAME，SDEPT，COUNT（*）FROM STUDENT
WHERE SDEPT=’CS’ GROUP BY SDEPT;
例3-26：(分组查询) 分析以下语句为什么会出现错误。并给出正确的查询语句。
SELECT SAGE FROM STUDENT GROUP BY SNO;
例3-27：(分组查询) 求学生人数不足3人的系及其相应的学生数。
例3-28：(分组查询) 求各系中除01班之外的各班的学生人数。
例3-29：(涉及空值的查询) 分别观察各组函数、行的显示顺序以及分组查询与空值的关系。
例3-30：(连接查询) 求选修了课程001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。
例3-31：(连接查询与表的别名) 求选修了课程的学生的学生姓名、课程号和成绩。
例3-32：(自身连接查询) 求年龄大于 ’李丽’ 的所有学生的姓名、系和年龄。
例3-33：(外部连接查询) 求选修了课程002或003的学生的学号、课程号、课程名和成绩，要求必须将002和003课程的相关信息显示出来。
例3-34：(子查询) 求与 ‘黎明’ 年龄相同的学生的姓名和系。
例3-35：(子查询) 求选修了课程名为 ’数据结构’ 的学生的学号和姓名。
例3-36：(子查询ANY) 求比数学系中某一学生年龄大的学生的姓名和系。
例3-37：(子查询ALL) 求比数学系中全体学生年龄大的学生的姓名和系。
例3-38：(子查询EXISTS) 求选修了课程004的学生的姓名和系。
例3-39：(返回多列的子查询) 求与 ‘李丽’ 同系且同龄的学生的姓名和系。
例3-40：(多个子查询) 求与 ‘‘黎明’ 同系，且年龄大于 ‘李丽’ 的学生的信息。
例3-41：(子查询中使用表连接) 求数学系中年龄相同的学生的姓名和年龄。
例3-42：(连接或嵌套查询) 检索至少选修王成刚老师所授课程中一门课程的女学生姓名。
例3-43：(嵌套与分组查询) 检索选修某课程的学生人数多于3人的教师姓名。
例3-44：(集合查询) 列出所有教师和同学的姓名和性别。
例3-45：(相关子查询) 求未选修课程004的学生的姓名。
例3-46：(相关子查询) 求选修了全部课程的学生的姓名。
例3-47：(相关子查询) 求至少选修了学生 ‘96002’ 所选修的全部课程的学生的学号。
例3-48：(相关子查询) 求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。
例3-49：(相关子查询) 查询被一个以上的学生选修的课程号。
例3-50：(相关子查询) 查询所有未选课程的学生姓名和所在系。
[ 实验要求 ]
对数据库表进行各种查询操作。
[ 实验方法 ]
① 将实验需求用SQL语句表示；
② 执行SQL语句；
③ 查看执行结果，如果结果不正确，进行修改，直到正确为止。
[实验总结 ]
① SQL语句以及执行结果；
② 对重点实验结果进行分析；
③ 实验中的问题和提高；
④ 收获与体会。
3-1
SQL> SELECT SNO,SNAME,SSEX,SAGE FROM Student;
3-2
SQL> SELECT DISTINCT SNO FROM Score;
3-3
SQL> SELECT * FROM Student;
3-4
SQL> SELECT SNO,SNAME,20##-SAGE AS "Year of Birth" FROM Student;
3-5
SQL> SELECT SNO AS "学号",20##-SAGE AS "出生年份" FROM Student;
3-6
SQL> SELECT SNAME,SAGE FROM Student WHERE SAGE>19;
3-7
SQL> SELECT SNAME,SDEPT,SAGE FROM Student WHERE SAGE>18 AND SDEPT IN('IS','CS');
3-8
SQL> SELECT SNO,SAGE FROM Student WHERE SAGE BETWEEN 20 AND 22;
3-9
SQL> SELECT SNO,SAGE FROM Student WHERE SAGE NOT BETWEEN 19 AND 22;
3-10
SQL> SELECT * FROM Student WHERE SDEPT IN ('MA','CS');
3-11
SQL> SELECT * FROM Student WHERE SDEPT NOT IN ('MA','CS');
3-12
SQL> SELECT * FROM Student WHERE SNAME LIKE '李%';
3-13
SQL> SELECT * FROM Student WHERE SNAME LIKE '%志%';
3-14
SQL> SELECT * FROM Student WHERE SNAME LIKE '%马__';
3-15
SQL> SELECT SNO,CNO,SCORE FROM Score
2 WHERE SNO LIKE '96%'
3 AND CNO IN ('001','003')
4 AND SCORE BETWEEN 80 AND 90;
3-16
SQL> SELECT CNO,CNAME,CTIME FROM Course WHERE CNAME LIKE '%\_%' ESCAPE '\';
3-17
SQL> SELECT SNO,CNO FROM Score WHERE SCORE IS NULL;
3-18
SQL> SELECT SNO,CNO,SCORE FROM Score
2 WHERE CNO IN ('003','004')
3 ORDER BY CNO,SCORE DESC;
3-19
SQL> SELECT COUNT(*) FROM Student;
3-20
SQL> SELECT COUNT (DISTINCT SNO) FROM Score;
3-21
SQL> SELECT AVG(SAGE) FROM Student WHERE SDEPT='CS';
3-22
SQL> SELECT MAX(Score),MIN(Score),AVG(Score) FROM SCORE WHERE CNO='001';
3-23
SQL> SELECT SUM(Score),AVG(Score) FROM SCORE GROUP BY CNO;
3-24
SQL> SELECT COUNT(SNO),AVG(SAGE) FROM Student GROUP BY SDEPT,SCLASS ORDER BY SDEPT,SCLASS;
3-25
ORA-00979: 不是 GROUP BY 表达式
GROUP BY表达式只能是集函数或者GROUP BY 中用于分组的列名。
SQL> SELECT SNAME,SDEPT,COUNT(*) FROM STUDENT WHERE SDEPT='CS' GROUP BY SDEPT,SNAME;
3-26
GROUP BY表达式只能是集函数或者GROUP BY 中用于分组的列名。
SQL> SELECT SAGE FROM Student GROUP BY SNO;
3-27
SQL> SELECT SDEPT,COUNT(SNO) FROM Student
2 GROUP BY SDEPT
3 HAVING COUNT(SNO)<3;
3-28
SQL> SELECT SDEPT,SCLASS,COUNT(SNO) FROM Student
2 WHERE SCLASS <> '01'
3 GROUP BY SDEPT,SCLASS;
3-30
SQL> SELECT SNAME,CNAME,SCORE FROM Student,Course,Score
2 WHERE Student.SNO=Score.SNO AND Course.CNO=Score.CNO
3 AND Score.CNO='001' AND SCORE NOT BETWEEN 70 AND 90;
3-31
SQL> SELECT SNAME,CNO,SCORE FROM Student,Score
2 WHERE Student.SNO=Score.SNO
3 /
3-32
SQL> SELECT X.SNAME,X.SDEPT,X.SAGE
2 FROM Student X,Student Y
3 WHERE Y.SNAME='李丽' AND X.SAGE>Y.SAGE;
3-33
SQL> SELECT SNO,Course.CNO,CNAME,SCORE
2 FROM Course,Score
3 WHERE Course.CNO=Score.CNO(+)
4 AND Course.CNO IN ('002','003');
3-34
SQL> SELECT SNAME,SDEPT FROM Student
2 WHERE SAGE=(
3 SELECT SAGE FROM Student WHERE SNAME='黎明'
4 AND SDEPT='CS');
3-35
SQL> SELECT SNO,SNAME FROM Student
2 WHERE NOT EXISTS
3 (SELECT * FROM Course
4 WHERE CNAME='数据结构' AND NOT EXISTS
5 (SELECT * FROM Score
6 WHERE SNO=Student.SNO AND CNO=Course.CNO
7 ));
3-36
SQL> SELECT SNAME,SDEPT
2 FROM Student
3 WHERE SAGE>ANY(SELECT SAGE FROM Student
4 WHERE SDEPT='MA');
3-37
SQL> SELECT SNAME,SDEPT
2 FROM Student
3 WHERE SAGE>ALL(SELECT SAGE FROM Student
4 WHERE SDEPT='MA');
3-38
SQL> SELECT SNAME,SDEPT
2 FROM Student
3 WHERE EXISTS
4 (SELECT * FROM Score
5 WHERE SNO=Student.SNO
6 AND CNO='004');
3-39
SQL> SELECT SNAME,SDEPT FROM Student
2 WHERE SDEPT=(SELECT SDEPT FROM Student
3 WHERE SNAME='李丽') AND SAGE=(SELECT SAGE
4 FROM Student
5 WHERE SNAME='李丽');
3-40
SQL> SELECT * FROM Student
2 WHERE SDEPT='CS' AND SAGE>(SELECT SAGE
3 FROM Student
4 WHERE SNAME='李丽');
3-41
SQL> SELECT SNAME,SAGE
2 FROM Student S1
3 WHERE SDEPT='MA' AND EXISTS
4 (SELECT * FROM Student S2
5 WHERE SNO <> S1.SNO
6 AND SDEPT='MA' AND SAGE=S1.SAGE);
3-42
SQL> SELECT SNAME FROM Student
2 WHERE SSEX='女' AND SNO IN
3 (SELECT SNO FROM Score
4 WHERE CNO IN
5 (SELECT CNO FROM Teach
6 WHERE TNAME='王成刚'));
3-43
SQL> SELECT DISTINCT TNAME FROM Teach
2 WHERE CNO IN
3 (SELECT CNO FROM Score
4 GROUP BY CNO
5 HAVING COUNT(SNO)>3);
3-44
SQL> SELECT SNAME,SSEX FROM Student
2 UNION
3 SELECT TNAME,TSEX FROM Teach;
3-45
SQL> SELECT SNAME FROM Student
2 WHERE NOT EXISTS
3 (SELECT * FROM Score
4 WHERE SNO=Student.SNO
5 AND CNO='004');
3-46
SQL> SELECT SNAME FROM Student
2 WHERE NOT EXISTS
3 (SELECT * FROM Course
4 WHERE NOT EXISTS
5 (SELECT * FROM Score
6 WHERE SNO=Student.SNO
7 AND CNO=Course.CNO));
3-47
SQL> SELECT DISTINCT SNO FROM Score S1
2 WHERE NOT EXISTS
3 (SELECT * FROM Score S2
4 WHERE S2.SNO='96002'
5 AND NOT EXISTS
6 (SELECT * FROM Score S3
7 WHERE SNO=S1.SNO
8 AND CNO=S2.CNO));
3-48
SQL> SELECT SNO,CNO,SCORE FROM Score S1
2 WHERE EXISTS
3 (SELECT * FROM Score S2
4 GROUP BY CNO
5 HAVING S1.SCORE > AVG(SCORE));
3-49
SQL> SELECT CNO FROM Course
2 WHERE EXISTS
3 (SELECT * FROM Student
4 WHERE EXISTS
5 (SELECT * FROM Score
6 WHERE SNO=Student.SNO
7 AND CNO=Course.CNO));
3-50
SQL> SELECT SNAME,SDEPT FROM Student
2 WHERE NOT EXISTS
3 (SELECT * FROM Course
4 WHERE EXISTS
5 (SELECT * FROM Score
6 WHERE SNO=Student.SNO
7 AND CNO=Course.CNO));
注意：如果不是固定字符串长度固定的字段最好设为varchar2类型，如本章14题。
‘车’是一个字符，可以代替为’_’
Char（10）不足10个字节，则用占用1个字节的字符填充。
收获与体会：
这次实验的目的主要是掌握SELECT语句的用法，它能搭配很多查询条件，但经过这50个小题，已经差不多能熟练地运用了。
实验四：视图、授权控制与事务处理
[ 实验日期 ] 20## 年 12 月 13 日
[ 实验目的 ]
通过实验进一步理解视图的建立和更新、数据库的权限管理和事务处理功能。
[ 实验内容 ]
4． SQL视图的定义与操纵：
例4-1： (建立视图) 建立计算机系的学生的视图STUDENT_CS。
例4-2： (建立视图) 建立由学号和平均成绩两个字段的视图STUDENT_GR。
例4-3： (视图查询) 利用视图STUDENT_CS，求年龄大于19岁的学生的全部信息。
例4-4： (视图查询) 利用视图STUDENT_GR，求平均成绩为88分以上的学生的学号和平均成绩。
例4-5： (视图更新) 利用视图STUDENT_CS，增加学生( ‘96006’，‘张然’，‘CS’，‘02’，‘男’，19 )。
例4-6： (视图更新) 利用视图STUDENT_CS，将学生年龄增加1岁。观察其运行结果并分析原因。
例4-7： (视图更新) 利用视图STUDENT_GR，将平均成绩增加2分。观察其运行结果并分析原因。
例4-8： (视图更新) 删除视图STUDENT_CS中学号为 ‘96006’ 的学生的全部数据。
例4-9： (视图更新) 删除视图STUDENT_GR的全部数据。
例4-10：(删除视图) 删除视图STUDENT_CS和STUDENT_GR。
5． SQL数据控制语句：
例5-1： (授权) 给左右邻近同学（用户）授予在表Student上的SELECT权限，并使这两个用户具有给其他用户授予相同权限的权限。
例5-2： (授权) 给邻近同学（用户）授予Teach表上的所有权限。
例5-3： (授权) 给所有用户授予Score表上的SELECT权限。
例5-4： (授权验证) 观察左右邻近同学查询你所授权的表中的内容。
例5-5： (收回授权) 收回上面例子中的所有授予的权限。
6． SQL事务处理：
例6-1： (事务回退) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、‘微机原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,取消所有修改(ROLLBACK)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。
例6-2： (事务提交) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、‘微机原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,确认所有修改(COMMIT)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。
[ 实验要求 ]
① 建立视图，视图查询，视图更新；
② 给某一或全部用户授权和收回授权；
③ 事务回退，事务提交。
[ 实验方法 ]
① 将实验需求用SQL语句表示；
② 执行SQL语句；
③ 查看执行结果，如果结果不正确，进行修改，直到正确为止。
[实验总结 ]
① SQL语句以及执行结果；
② 对重点实验结果进行分析；
③ 实验中的问题和提高；
④ 收获与体会。
4-1
SQL> CREATE VIEW STUDENT_CS
2 AS
3 SELECT * FROM Student
4 WHERE SDEPT='CS'
5 WITH CHECK OPTION;
4-2
SQL> CREATE VIEW STUDENT_GR(SNO,AVERAGE)
2 AS
3 SELECT SNO,AVG(SCORE) FROM Score
4 GROUP BY SNO
5 WITH CHECK OPTION;
4-3
SQL> SELECT * FROM STUDENT_CS WHERE SAGE>19;
4-4
SQL> SELECT SNO,AVERAGE FROM STUDENT_GR WHERE AVERAGE>88;
4-5
SQL> INSERT INTO STUDENT_CS VALUES('96006','张然','CS','02',19,'男');
4-6
SQL> UPDATE STUDENT_CS SET SAGE=SAGE+1;
已更新6行。
视图在更新时只对属于视图范围内的基本表数据进行操作。
4-7
SQL> UPDATE STUDENT_GR SET AVERAGE=AVERAGE+2;
UPDATE STUDENT_GR SET AVERAGE=AVERAGE+2
*
ERROR 位于第 1 行:
ORA-01732: 此视图的数据操纵操作非法
此次视图更新操作不能有意义地转化成相应基本表的更新，即该视图是不可更新视图。
4-8
SQL> DELETE FROM STUDENT_CS WHERE SNO='96006';
4-9
SQL> DROP VIEW STUDENT_CS;
4-10
SQL> DROP VIEW STUDENT_GR;
5-1
SQL> GRANT SELECT ON Student TO U109074191 WITH GRANT OPTION;
授权成功。
SQL> GRANT SELECT ON Student TO U109074198 WITH GRANT OPTION;
授权成功。
5-2
SQL> GRANT ALL ON Teach TO U109074191;
授权成功。
5-3
SQL> GRANT SELECT ON Score TO PUBLIC;
授权成功。
5-5
SQL> REVOKE SELECT ON Student FROM u109074191;
撤销成功。
SQL> REVOKE SELECT ON Student FROM U109074198;
撤销成功。
SQL> REVOKE ALL ON Teach FROM U109074191;
撤销成功。
SQL> REVOKE SELECT ON Score FROM PUBLIC;
撤销成功。
6-1
SQL> UPDATE Course SET CTIME=80
已更新 1 行。
SQL> UPDATE Course SET CTIME=70
已更新 1 行。
SQL> SELECT * FROM Course;
CNO CNAME CTIME
--- ---------------- ----------
001 数学分析 144
002 普通物理 144
003 微机原理 70
004 数据结构 72
005 操作系统 64
006 数据库原理 64
007 DB_Design 48
008 程序设计 80
已选择8行。
SQL> ROLLBACK;
回退已完成。
SQL> SELECT * FROM Course;
CNO CNAME CTIME
--- ---------------- ----------
001 数学分析 144
002 普通物理 144
003 微机原理 72
004 数据结构 72
005 操作系统 64
006 数据库原理 64
007 DB_Design 48
008 程序设计 56
已选择8行。
6-2
SQL> UPDATE Course SET CTIME=80
已更新 1 行。
SQL> UPDATE Course SET CTIME=70
已更新 1 行。
SQL> SELECT * FROM Course;
CNO CNAME CTIME
--- ---------------- ----------
001 数学分析 144
002 普通物理 144
003 微机原理 70
004 数据结构 72
005 操作系统 64
006 数据库原理 64
007 DB_Design 48
008 程序设计 80
已选择8行。
SQL> COMMIT;
提交完成。
SQL> SELECT * FROM Course;
CNO CNAME CTIME
--- ---------------- ----------
001 数学分析 144
002 普通物理 144
003 微机原理 70
004 数据结构 72
005 操作系统 64
006 数据库原理 64
007 DB_Design 48
008 程序设计 80
已选择8行。
收获与体会：
视图的操作比较简单，跟上次的实验操作差不了多少，主要就是事务处理，它能把以前的语句全部结合起来，这样才能达到以前知识的熟练运用吧。
实验五：Oracle存储过程与触发器
[ 实验日期 ] 20## 年 12 月 15 日
[ 实验目的 ]
通过实验进一步理解和掌握Oracle数据库的存储过程和触发器。
[ 实验内容 ]
4． 存储过程与触发器：
例7-1： (存储过程) 创建一个显示学生总人数的存储过程。
例7-2： (存储过程) 创建显示学生信息的存储过程STUDENT_LIST，并引用STU_COUNT存储过程。
例7-3： (存储过程) 创建一个显示学生平均成绩的存储过程。
例7-4： (存储过程) 创建显示所有学生平均成绩的存储过程。
例7-5： (修改数据库表) 在Student表中增加SAVG(N,6,2) 字段。
例7-6： (存储过程) 创建存储过程，计算每个学生的平均成绩保存到学生表SAVG字段中。
例7-7： (触发器) 当更新学生成绩表SCORE中的学生成绩时，自动计算该学生的平均成绩保存到学生表SAVG字段中。
例7-8： (触发器) 创建包含插入、删除、修改多种触发事件的触发器DML_LOG，对SCORE表的操作进行记录。用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。
先创建事件记录表LOGS，该表用来对操作进行记录。该表的字段含义解释如下：
LOG_ID：操作记录的编号，数值型，它是该表的主键，自动增1，可由序列自动生成。
LOG_TABLE：进行操作的表名，字符型，非空，该表设计成可以由多个触发器共享使用。比如我们可以为Student表创建类似的触发器，同样将操作记录到该表。
LOG_DML：操作的动作，即INSERT、DELETE或UPDATE三种之一。
LOG_KEY_ID：操作时表的主键值，数值型。之所以记录表的主键，是因为主键是表的记录的惟一标识，可以识别是对哪一条记录进行了操作。对于Score表，主键是由SNO_CNO构成。
LOG_DATE：操作的日期，日期型，取当前的系统时间。
LOG_USER：操作者，字符型，取当时的操作者账户名。比如登录SCOTT账户进行操作，在该字段中，记录账户名为SCOTT。
[ 实验要求 ]
① 创建、调试和修改、调用和执行Oracle存储过程；
② 创建、调试和修改、测试Oracle触发器。
[ 实验方法 ]
① 将实验需求用SQL语句表示；
② 执行SQL语句；
③ 查看执行结果，如果结果不正确，进行修改，直到正确为止。
[实验总结 ]
① SQL语句以及执行结果；
② 对重点实验结果进行分析；
③ 实验中的问题和提高；
④ 收获与体会。
7-1
CREATE OR REPLACE PROCEDURE STU_COUNT (SUM OUT NUMBER)
AS
BEGIN
SELECT COUNT(*) INTO SUM FROM STUDENT;
END;
7-2
CREATE OR REPLACE PROCEDURE STUDENT_LIST IS
CURSOR C IS
SELECT SNO,SNAME,SDEPT,SCLASS,SAGE,SSEX FROM STUDENT;
P1 STUDENT.SNO%TYPE;
P2 STUDENT.SNAME%TYPE;
P3 STUDENT.SDEPT%TYPE;
P4 STUDENT.SCLASS%TYPE;
P5 STUDENT.SAGE%TYPE;
P6 STUDENT.SSEX%TYPE;
NUM1 NUMBER;
I NUMBER;
BEGIN
STU_COUNT(NUM1);
OPEN C;
FOR I IN 1 .. NUM1 LOOP
FETCH C INTO P1,P2,P3,P4,P5,P6;
DBMS_OUTPUT.PUT_LINE('学号:'||P1||' 姓名:'||P2||' 专业:'||
P3||' 班级:'||P4||' 性别:'||P5||' 年龄:'||P6);
END LOOP;
CLOSE C;
END STUDENT_LIST ;
EXECUTE STUDENT_LIST;
7-3
CREATE OR REPLACE PROCEDURE SAVG(S_SNO IN STUDENT.SNO%TYPE)
AS
S_NAME STUDENT.SNAME%TYPE;
S_AVG NUMBER(5,2);
BEGIN
SELECT SNAME INTO S_NAME FROM STUDENT WHERE SNO = S_SNO;
SELECT AVG(SCORE) INTO S_AVG FROM SCORE WHERE SNO = S_SNO;
DBMS_OUTPUT.PUT_LINE('SNAME:'||S_NAME||' AVG_SCORE:'||S_AVG);
END;
EXECUTE SAVG('96001');
7-4
CREATE OR REPLACE PROCEDURE SC_AVG
AS
S_AVG NUMBER(5,2);
BEGIN
SELECT AVG(SCORE) INTO S_AVG FROM SCORE;
DBMS_OUTPUT.PUT_LINE('S_AVG:'||S_AVG);
END;
SET SERVEROUTPUT ON;
EXECUTE SC_AVG;
7-5
ALTER TABLE STUDENT ADD SAVG NUMBER(6,2);
7-6
CREATE OR REPLACE PROCEDURE SAVE_AVG
AS
S_SNO STUDENT.SNO%TYPE;
NUM NUMBER(2);
I NUMBER;
CURSOR C_ADD IS SELECT SNO FROM STUDENT;
BEGIN
STU_COUNT(NUM);
OPEN C_ADD;
FOR I IN 1..NUM LOOP
FETCH C_ADD INTO S_SNO;
UPDATE STUDENT SET SAVG =
( SELECT AVG(SCORE) FROM SCORE
GROUP BY SNO
HAVING SNO = S_SNO )
WHERE SNO = S_SNO ;
END LOOP;
CLOSE C_ADD;
END SAVE_AVG;
EXECUTE SAVE_AVG;
7-7
CREATE OR REPLACE TRIGGER SC_UP AFTER UPDATE ON SCORE
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
IF UPDATING THEN
SAVE_AVG;
END IF;
END;
7-8
CREATE TABLE LOGS(
LOG_ID 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 1
START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;
CREATE OR REPLACE TRIGGER DML_LOG
BEFORE DELETE OR INSERT OR UPDATE
ON SCORE
FOR EACH ROW -- 行级触发器
BEGIN
IF INSERTING THEN
INSERT INTO LOGS VALUES(LOGS_ID_SQU.NEXTVAL,'SCORE','INSERT',:new.SNO,SYSDATE,USER);
ELSIF DELETING THEN
INSERT INTO LOGS VALUES(LOGS_ID_SQU.NEXTVAL, 'SCORE','DELETE',:old.SNO,SYSDATE,USER);
ELSE
INSERT INTO LOGS VALUES(LOGS_ID_SQU.NEXTVAL, 'SCORE','UPDATE',:new.SNO,SYSDATE,USER);
END IF;
END;
收获与体会：
我认为存储过程与触发器还是比较难的，我参考了一些网上的资料，看来要掌握这些知识点还是要多看书了。
实验所用表格(各表的数据结构和数据如下)：
1． 学生情况表 Student 主关键字：SNO 非空字段：SNAME，SDEPT，SCLASS
2． 课程名称表 Course 3． 教师授课表 Teach
主关键字：CNO 候选关键字：CNAME 主关键字：TNAME，CNO，TDEPT 参照关系：Course(CNO)
4． 成绩表 Score 主关键字：SNO，CNO 参照关系：Student(SNO)，Course(CNO)
