数据库实验报告

《数据库系统概论》实验报告书

专业班级软件

学号16677777

姓名哈哈

指导教师陈业斌

安徽工业大学计算机学院

实验一：数据定义/数据操纵语言

[ 实验日期 ] 2016 年 9 月 24 日

[ 实验目的 ]

熟悉Oracle上机环境；熟练掌握和使用DDL语言，建立、修改和删除数据库表；熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。

[ 实验内容 ]

1.SQL数据定义语句：

例1-1： (建立如附录一所示的四张数据表) 按要求建立教学数据库表结构及其完整性约束关系，并将数据分别插入到教学数据库的四个数据库表中。

例1-2： (修改表结构) 在Student表中将SSEX字段修改为char(2),观察返回结果，分析原因。显示：alter table STUDENT modify (SSEX char(2))

错误报告 -

SQL 错误: ORA-01441: 无法减小列长度, 因为一些值过大

01441. 00000 - "cannot decrease column length because some value is too big"

例1-3： (修改表结构) 在Student表中增加BirthDay(date) 字段。

alter table STUDENT add BirthDay DATE;

例1-4： (修改表结构) 在Student表中删除BirthDay字段。

alter table STUDENT drop column BirthDay;

例1-5： (修改表结构) 在Student表中增加一个默认值约束，约束’性别’的默认值为’男’。alter table STUDENT modify (SSEX default '男');

2. SQL数据操纵语句：

例2-1： (插入数据) 执行如下语句向student表中插入一条数据。

insert into student(sno,sname,sdept) values('96001','张三','SC');

(1)根据返回信息解释其原因;

显示：

在行: 5 上开始执行命令时出错 -

insert into student(sno,sname,sdept) values('96001','张三','SC')

错误报告 -

SQL 错误: ORA-01400: 无法将 NULL 插入 ("STU"."STUDENT"."SCLASS")

01400. 00000 - "cannot insert NULL into (%s)"

原因：在定义表结构时已将SCLASS约束为NOT NULL。并且学号是主键，表里已经有学号为96001的学生，违反了主键的唯一性约束条件。

(2)修改上面插入语句，让其能正确地插入一条记录。

正确的为：insert into student(sno,sname,sdept,sclass) values('97006','张三','SC','02');

例2-2：(插入数据) 执行如下语句向Score表中插入一条数据。

insert into Score values('96006','001',85);

（1）根据返回信息解释其原因;

显示：在行: 6 上开始执行命令时出错 -

insert into Score values('96006','001',85)

错误报告 -

SQL 错误: ORA-02291: 违反完整约束条件 (STU.FK_STUDENT_SCORE) - 未找到父项关键字

02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"

原因：在表SCORE中，sno作为其外键，在表STUDENT中学号为96006的学生，所以自然插不进去。

（2）修改上面插入语句，让其能正确地插入一条记录。

修改为：insert into Score values('96002','002',85);

例2-3： (修改数据) 将Student表中所有学生的年龄加2。

update STUDENT set SAGE=SAGE+2;

例2-4： (修改数据) 将Course表中‘程序设计’课时数修改成50。

update COURSE set CTIME=50 where CNAME='程序设计';

例2-5： (修改数据) 将Student表中的学号’96001’改为‘96010’，根据返回信息解释其原因。

update STUDENT set SNO='96010' where SNO='96001';

显示：在行: 10 上开始执行命令时出错 -

update STUDENT set SNO='96010' where SNO='96001'

错误报告 -

SQL 错误: ORA-02292: 违反完整约束条件 (STU.FK_STUDENT_SCORE) - 已找到子记录

02292. 00000 - "integrity constraint (%s.%s) violated - child record found"

原因：学号为96001的学生已经出现在成绩表SCORE中，不可更改

例2-6： (删除数据) 删除Student表中学号为‘96001’的成绩信息，根据返回信息解释其原因。delete from STUDENT where sno='96001';

显示：在行: 11 上开始执行命令时出错 -

delete from STUDENT where sno='96001'

错误报告 -

SQL 错误: ORA-02292: 违反完整约束条件 (STU.FK_STUDENT_SCORE) - 已找到子记录02292. 00000 - "integrity constraint (%s.%s) violated - child record found"

原因：在表SCORE中已经有学号为96001的成绩记录。

例2-7： (创建表) 利用Student表的查询结果创建Sc表，表中记录与Student表相同。create table Sc

as

select * from STUDENT;

例2-8： (删除数据) 删除Sc表中性别为’男’的所有学生。

delete from Sc where SSEX='男';

例2-9： (删除数据) 删除数据库表Sc中所有学生的数据。

truncate table Sc;

例2-10： (删除表) 删除数据库表Sc。

drop table Sc;

[ 实验要求 ]

①熟悉SQL Server上机环境；

②建立数据库表，修改数据库表结构；

③对数据库表进行插入、修改和删除数据的操作。

[ 实验方法 ]

①执行SQL语句；

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

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

[实验总结 ]

① SQL语句以及执行结果；

②对重点实验结果进行分析；

③实验中的问题和提高；

④收获与体会。

附录一. 教学管理数据库设计

各表的数据结构和数据如下：

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)

IS ：信息系

CS ：计算机系 MA ：数学系

附录二：《数据库概论》实验操作指导

创建一个新的方案：SHIYAN

在方案中建立基本数据表的SQL

1.建立学生表。

create table Student

( SNO char(5) primary key,

SNAME varchar2(20) not null,

SDEPT char(2) not null,

SCLASS char(2) not null,

SSEX char(3),

SAGE number(2,0)

);

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); commit;

2．建立课程表

create table Course

(CNO char(3) primary key,

CNAME varchar2(50),

CTIME number(3,0)

);

insert into COURSE values('001','数学分析',144);

insert into COURSE values('002','普通物理',144);

insert into COURSE values('003','微机原理',80);

insert into COURSE values('004','数据结构',72);

insert into COURSE values('005','操作系统',80);

insert into COURSE values('006','数据库原理',80);

insert into COURSE values('007','编译原理',60);

insert into COURSE values('008','程序设计',40);

commit;

3．建立教师表

create table Teach

(TNAME VARCHAR2(20),

TSEX CHAR(3),

CNO CHAR(3),

TDATE date,

TDEPT CHAR(2),

CONSTRAINT PK_STUDENT PRIMARY KEY(TNAME,CNO,TDEPT),

CONSTRAINT FK_C_T FOREIGN KEY (CNO ) REFERENCES Course(CNO)

);

insert into TEACH values('王成刚','男','004','9-5月-99','CS');

insert into TEACH values('李正科','男','003','09-5月-99','CS');

insert into TEACH values('严敏','女','001','09-5月-99','MA');

insert into TEACH values('赵高','男','004','09-5月-99','MA');

insert into TEACH values('刘玉兰','女','006','23/2月/00','CS');

insert into TEACH values('王成刚','男','004','23/2月/00','IS');

insert into TEACH values('马悦','女','008','6/9月/00','CS');

COMMIT;

4．建立成绩表

create table Score

(sno char(5),

cno char(3),

Score number(4,1),

CONSTRAINT PK_Score primary key(sno,cno),

CONSTRAINT FK_Student_Score FOREIGN KEY (sno ) REFERENCES student(sno),

CONSTRAINT FK_Sourse_Score FOREIGN KEY (cno) REFERENCES Course(cno) );

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',78);

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

COMMIT;

实验二：数据查询语言

[ 实验日期 ] 年月日

[ 实验目的 ]

体会SQL语言数据查询功能的丰富和复杂。

[ 实验内容 ]

3． SQL数据查询语句：

例3-1： (选择表中的若干列) 求全体学生的学号、姓名、性别和年龄。

select SSEX,SAGE,SNO,SNAME from student;

例3-2： (不选择重复行) 求选修了课程的学生学号。

select distinct sno from score where sno=score.sno;

例3-3： (选择表中的所有列) 求全体学生的详细信息。

select * from student;

例3-4： (使用表达式) 求全体学生的学号、姓名和出生年份。

select sno,sname,sage from student;

例3-5： (使用列的别名) 求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。 select sno as 学号,sage as 出生年份 from student;

例3-6： (比较大小条件) 求年龄大于19岁的学生的姓名和年龄。

select sname,sage from student where sage>19;

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

select sname,sage,sdept from student where sdept='CS' or sdept='IS'and sage>18;

例3-8： (确定范围条件) 求年龄在19岁与22岁(含19岁和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 cno in('001','003')and score between 80 and 90 and sno like '96__';

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

select sno,cno from score where score is null;

例3-17：(控制行的显示顺序) 求选修003课程学生的学号、课程号和分数，并按分数的降序进行排列。

select sno,cno,score from score where cno='003'order by score desc;--升序是asc

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

select count(sno) as 总人数 from student;

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

select count(sno) as 总人数 from score;

例3-20：(组函数) 求计算机系学生的平均年龄。

select avg(sage) as 平均年龄 from student where sdept='CS';

例3-21：(组函数) 求选修了课程001的最高、最低与平均成绩。

select max(score) as 最高成绩,min(score) as 最低成绩,avg(score) as 平均成绩 from score where cno='001';

例3-22：(分组查询) 求各门课程的平均成绩与总成绩。

select cno,avg(score) as 平均成绩,sum(score) as 总成绩 from score group by cno;

例3-23：(分组查询) 求各系、各班级的人数和平均年龄。

select count(sno) as 总人数,avg(sage) as 平均年龄from student group by sdept,sclass;

例3-24：(分组查询) 输入以下查询语句并执行，观察出现的其结果并分析其原因。

SELECT SNAME，SDEPT，COUNT（*）FROM STUDENT GROUP BY SDEPT;

Sname字段具有唯一性，不可叠加

例3-25：(分组查询) 分析以下语句为什么会出现错误。并给出正确的查询语句。

SELECT SAGE FROM STUDENT GROUP BY SNO;

例3-26：(分组查询) 求学生人数不足3人的系及其相应的学生数。

例3-27：(分组查询) 求各系中除01班之外的各班的学生人数。

select count(sno)as 总人数,sdept as 系,sclass as 班from(select * from student where sclass!='01') group by sdept,sclass;

例3-28：(自然连接查询) 求学生学号、姓名以及其选修课程的课程号和成绩，但查询结果中只能

有一个SNO字段。

select sno,cno,score from score where sno in(select sno from score group by score,sno having

count(*)=1);

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

程名称和成绩。

select sname,cname,score from student,course,score where student.sno=score.sno and

https://www.360docs.net/doc/1314636015.html,o=https://www.360docs.net/doc/1314636015.html,o and https://www.360docs.net/doc/1314636015.html,o='001' and not score between 70 and 90;

例3-30：(连接查询与表的别名) 求选修了课程的学生姓名、课程号和成绩。

select sname,cno,score from student st,score sc where st.sno=sc.sno;

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

select sname,sdept,sage from student where sage>(select sage from student where sname='李

丽');

例3-32：(外部连接查询) 求未选修了任何课程的学生的学号、姓名。

select sname,sno from student where sno not in (select distinct student.sno from student

right outer join(select distinct sno s from score,course where https://www.360docs.net/doc/1314636015.html,o=https://www.360docs.net/doc/1314636015.html,o) on student.sno=s)

例3-33：(子查询) 求与‘李丽’ 年龄相同的学生的姓名和系。

select sname,sdept from student where sage=(select sage from student where sname='李丽');

例3-34：(子查询) 求选修了课程名为’数据结构’ 的学生的学号和姓名。

select sno,sname from student where sno in(select sno from score where https://www.360docs.net/doc/1314636015.html,o=(select cno

from course where cname='数据结构'));

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

select sname,sdept from student where sage>any (select sage from student where sdept='MA');

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

select sname,sdept from student where sage>all(select sage from student where sdept='MA');

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

select sname,sdept from student where exists ( select * from score where cno='004');

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

select sname,sdept from student where (sdept , sage )= (select sdept,sage from student where

sname='李丽');

例3-39： (相关子查询) 求未选修课程004的学生的姓名。

select sname from student where not exists (select * from score where cno='004');

例3-40 （from子查询）求总成绩排名前五的学生学号和总成绩。

select * from (select sno,sum(score) as 总成绩 from score group by sno order by sum(score)

desc) where rownum<6;

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

[ 实验日期 ] 年月日

[ 实验目的 ]

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

[ 实验内容 ]

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 as select sno as 学号,avg(score) as 平均成绩 from score group by sno;

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

select * from student_cs where sage>19;

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

select 学号,平均成绩 from student_gr where 平均成绩>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 平均成绩=平均成绩+2;

不能对含有聚合函数的视图进行更新操作，因为更新操作的结果是会反映到基本表上的，而更新聚合函数无法得知更新基本表的那个元件

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

delete from student_cs where sno='96006';

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

delete from student_gr;

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

drop view student_cs;

drop view student_gr;

5． SQL数据控制语句：

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

create user user1 identified by user1;

grant select on student to user1 with grant option;

例5-2： (授权) 给邻近同学（用户）授予Teach表上的所有权限。

grant all on teach to user1;

例5-3： (授权) 给所有用户授予Score表上的SELECT权限。

grant select on score to public;

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

revoke all on teach from user1;

revoke select on score from public;

revoke select on student from user1;

6． SQL事务处理：

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

update course set ctime=80 where cname='程序设计';

update course set ctime=70 where cname='编译原理';

select cname as 课程,ctime as 课时 from course ;

rollback;

select cname as 课程,ctime as 课时 from course ;

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

update course set ctime=80 where cname='程序设计';

update course set ctime=70 where cname='编译原理';

select cname as 课程,ctime as 课时 from course ;

commit;

select cname as 课程,ctime as 课时 from course ;

实验四：存储过程与触发器

[ 实验日期 ] 年月日

[ 实验目的 ]

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

[ 实验内容 ]

7．索引与数据库完整性

例7-1： (建立索引) 为Score表按课程号升序、分数降序建立索引，索引名为SC_GRADE。create unique index sc_grade on score(sno asc,score desc);

例7-2： (删除索引) 删除索引SC_GRADE。

drop index sc_grade;

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

alter table score add constraint fk_sno foreign key(sno) references

student(sno) on delete cascade;

例7-4： (修改数据库表) 删除成绩表Score的参照完整性约束关系。

alter table score drop constraint fk_sno;

8．存储过程与触发器：

例8-1：(存储过程) 创建显示学生信息的存储过程Student_List。

create or replace procedure Student_List

as

stu Student%rowtype;

cursor CUR_VAR is select * from Student;

begin

open CUR_VAR;

loop

fetch CUR_VAR INTO stu;

exit when CUR_VAR%notfound;

DBMS_output.put_line(stu.SNO||stu.Sname||stu.SDEPT||stu.Sclass||stu.Ssex||stu.Sage); end loop;

end;

END;例8-2：(存储过程) 创建一个显示学生平均成绩的存储过程Student_Avg。

create or replace procedure Student_Avg

as

AvgGrade number(5,2);

cursor CUR_AVG is select avg(score) from score group by sno;

begin

open CUR_AVG;

loop

fetch CUR_AVG INTO AvgGrade;

exit when CUR_AVG%notfound;

DBMS_output.put_line(AvgGrade);

end loop;

close CUR_AVG;

END;例8-3：(存储过程) 创建一个对学生姓名进行模糊查找的带参存储过程Student_Name。

create or replace procedure Student_List

as

stu Student%rowtype;

cursor CUR_VAR is select * from Student;

begin

open CUR_VAR;

loop

fetch CUR_VAR INTO stu;

exit when CUR_VAR%notfound;

DBMS_output.put_line(stu.SNO||stu.Sname||stu.SDEPT||stu.Sclass||stu.Ssex||stu.Sage);

end loop;

end;

例8-4： (触发器) 创建满足下列要求的触发器，对Student、Score和Course表进行模仿参照完整性关系的维护。

(1)在Student上创建一个删除TR_Student_Del，对Student表进行删除操作，则删除Score表中

对应学号的所有记录；

create or replace trigger TR_Student_Del after delete on student

referencing new as new old as old for each row

begin

if deleting then

delete from score where sno=:old.sno;

end if;

end;

(2)在Score上创建一个插入触发器TR_Score_insert，当在Score表中插入一条记录时，要检查其

学号在Student表和课程号在Course表中是否存在，若不存在则不允许插入；

create or replace trigger TR_Score_insert before insert ON score

referencing old as new old as old

for each row

declare

psno student.sno%type;

pcno https://www.360docs.net/doc/1314636015.html,o%type;

begin

select sno into psno from student where sno=:new.sno;

select cno into pcno from course where cno=:https://www.360docs.net/doc/1314636015.html,o;

if(psno is null and pcno is null)

then

rollback;

end if;

end;

(3)在Course上创建一个更新触发器TR_Course_Upd，对Course表的课程号进行更新操作，则更新

Score表中对应的课程号。

create or replace trigger TR_Course_Upd after update on COURSE

referencing new as new old as old for each row

begin

if updating then

update SCORE set cno=:https://www.360docs.net/doc/1314636015.html,o where cno=:https://www.360docs.net/doc/1314636015.html,o;

end if;

end;