数据库原理实验答案

实 验 报 告

课程名称： 数据库原理

系部名称： 计算机 专业班级： 网络工程 学生姓名： 学 号： 指导教师：

实验（ 一 ）

实验名称： 数据库表的建立 实验时间： 2013-3-4

一、实验目的：

本实验的目的是使熟悉SQL SERVER的常用数据类型，加深对SQL和SQL语言的查询语句的理解。

1、熟练掌握简单表的创建。

2、掌握主键约束、外键约束及及check约束的用法； 3、掌握默认值约束的应用； 4、了解规则、触发器的使用。

二、实验内容：

1、 使用查询分析器，用ddl建立studentinfo数据库

2、 在studentinfo 数据库中定义3个表，students, courses,和sc,表的具体要求如下：用

ddl建立上述3个表并增加如下约束：

students,表 列名 Sno Sname Ssex Sage Sdept

Courses表 列名 Cno Cname PreCno Credits SC表 列名 Sno Cno Grade 具体约束为：

1、 请用至少2种方法定义stu数据库中student表的主键sno；

说明 学号 课程号 成绩 数据类型 字符串,长度为10 字符串，长度为6 整数 约束说明 主键 主键 允许为空值 说明 课程号 课程名 先修课程号 学分 数据类型 字符串，长度为6 字符串，长度为20 字符串，长度为6 整数 约束说明 主键 非空值 允许为空值 允许为空值 说明 学号 姓名 性别 年龄 所在系 数据类型 字符串,长度为10 字符串,长度为8 字符串,长度为2 整数 字符串,长度为15 约束说明 主键 非空值 非空值 允许空值 非空值 2、 为表course中的字段cname添加唯一值约束；

3、 对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表

course的主码cno对应，实现如下参照完整性：

1) 删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录； 2) 修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；

3) 修改course表cno字段值时，该字段在sc表中的对应值也应修改； 4) 删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；

5) 向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；

4、定义check约束，要求学生学号sno必须为10位字符，且不能以0开头，第二三位皆为0；check(sno like‘[^0]00__________’ )

5、定义stu数据库中student表中学生年龄值在16-25范围内；

6、定义stu数据库中student表中学生姓名长度在2-8之间；check(len(sname)<8) 7、定义stu数据库中student表中学生性别列中只能输入“男”或“女”； 8、定义stu数据库student表中学生年龄值默认值为20；

2. studentinfo数据库的3个表students,courses,sc 表中输入若干纪录，内容为： students sno 20010101 20010102 20010103 20030101 20030102

courses cno C1 C2 C3 Sc Sno 20010101 20010102 20010102 20010102 Cno C1 C1 C2 C2 Grade 90 88 94 62 cname English Math Database ProCno C1 C2 Credits 4 2 2 sname Hone Sue Smith Allen Deepa ssex M F M M F sage 19 20 19 18 21 sdept Computer Computer Math Automation Art 4、在上面的基础上分别向studentinfo 数据库中的students, courses, sc 表中添加少5条数据。须满足三大约束条件

三、源程序：

create database students

create table Students( Sno char(10) primary key,

Sname char(8) check(len(Sname)>=2 and len(Sname)<=8), Ssex char(2) check(Ssex in('男','女')),

Sage int check(Sage>=16 and Sage<=25) default 20, Sdept char(15) not null )

create table Courses( Cno char(6) primary key, Cname char(20) unique, PreCno char(6) null, Credits int null )

create table SC( Sno char(10), Cno char(6), Grade int null, primary key(Sno,Cno),

foreign key(Sno) references Students(Sno) on delete cascade,

foreign key(Cno) references Courses(Cno) on delete cascade on update cascade )

四、主要问题和解决方法：

我们都对数据库不陌生，但是使用sql语句来使用数据库的还是第一次，感觉挺新鲜。第一次使用，问题的难度不大，都可以自主完成。

五、测试数据及结果：

与预测结果一样。

六、心得体会与自我评价：

首次使用sql，感觉还好，但是我知道这只是一个开始，后面还有更加复杂的指令，这些都是从基础开始的，所以，即使这一次简单，还是要认真的对待。

实验（ 二 ）

实验名称： 数据库的完整性约束 实验时间： 2013-3-18

一、实验目的：

1. 掌握主键约束、外键约束及及check约束的用法； 2. 掌握默认值约束的应用； 3.了解规则、触发器的使用。

二、实验内容：

1、 使用查询分析器，用ddl建立studentinfo数据库

2、 在studentinfo 数据库中定义3个表，students, courses,和sc,表的具体要求如下：

用ddl建立上述3个表并增加如下约束：

students,表 列名 Sno Sname Ssex Sage Sdept Courses表 列名 Cno Cname PreCno Credits

SC表 列名 Sno Cno Grade 具体约束为：

1、 请用至少2种方法定义stu数据库中student表的主键sno； 2、 为表course中的字段cname添加唯一值约束；

3、 对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表

说明 学号 课程号 成绩 数据类型 字符串,长度为10 字符串，长度为6 整数 约束说明 主键 主键 允许为空值 说明 课程号 课程名 先修课程号 学分 数据类型 字符串，长度为6 字符串，长度为20 字符串，长度为6 整数 约束说明 主键 非空值 允许为空值 允许为空值 说明 学号 姓名 性别 年龄 所在系 数据类型 字符串,长度为10 字符串,长度为8 字符串,长度为2 整数 字符串,长度为15 约束说明 主键 非空值 非空值 允许空值 非空值 course的主码cno对应，实现如下参照完整性：

1) 删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录； 2) 修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；

3) 修改course表cno字段值时，该字段在sc表中的对应值也应修改； 4) 删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；

5) 向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；

4、定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0；(不用做)

5、定义stu数据库中student表中学生年龄值在16-25范围内； 6、定义stu数据库中student表中学生姓名长度在2-8之间；

7、定义stu数据库中student表中学生性别列中只能输入“男”或“女”； 8、定义stu数据库student表中学生年龄值默认值为20；

三、源程序：

create database studentinfo

create table students (Sno char(10)primary key, Sname char(8), Ssex char(2), Sage smallint, Sdept char(15))

create table courses

(Cno char(6)primary key, Cname char(20)not null, PreCno char(6), Ccredits smallint);

create table SC (Sno char(10), Cno char(6), Grade smallint, primary key(Sno,Cno),

foreign key(Sno)references students(Sno), foreign key(Cno)references courses(Cno)); INSERT

INTO Students(Sno,Sname,Ssex,Sage,Sdept) VALUES('20010101','Hone','M',19,'Computer'); INSERT

INTO Students(Sno,Sname,Ssex,Sage,Sdept) VALUES('20010102','Sue','F',20,'Computer'); INSERT

INTO Students(Sno,Sname,Ssex,Sage,Sdept) VALUES('20010103','Smith','M',19,'Math'); INSERT

INTO Students(Sno,Sname,Ssex,Sage,Sdept)

VALUES('20030101','Allen','M',18,'Automation'); INSERT

INTO Students(Sno,Sname,Ssex,Sage,Sdept) VALUES('20030102','Deepa','F',21,'Art');

四、主要问题和解决方法：

对于sql语句，在之前也有过了一些接触，但是没有深入的去了解，涉及到了一些复杂的sql语句时，还是感到陌生，不过相信多加练习就会好的。

五、测试数据及结果：

与预测结果一样。

六、心得体会与自我评价：

这次的实验是在上次的实验的基础上加上他的一些约束条件，相对来说也是比较简单的，操作也相对容易。

实验（ 三 ）

实验名称： 数据库单表查询 实验时间： 2013-4-1

一、实验目的：

1. 掌握SELECT语句的基本语法和查询条件表示方法； 2. 掌握查询条件表达式和使用方法； 3. 掌握GROUP BY 子句的作用和使用方法； 4. 掌握HAVING子句的作用和使用方法； 5. 掌握ORDER BY子句的作用和使用方法。

二、实验内容：

1、以数据库原理实验三数据库中数据为基础，请使用T-SQL 语句实现以下操作： 1) 列出所有不姓刘的所有学生；

2) 列出姓“沈”且全名为3个汉字的学生； 3) 显示在1985年以后出生的学生的基本信息；

4) 按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下

规定显示：性别为男显示为男 生，性别为女显示为女 生，其他显示为“条件不明”；

5) 查询出课程名含有“数据”字串的所有课程基本信息；

6) 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、

年龄及院系；

7) 列出选修了‘1’课程的学生，按成绩的降序排列；

8) 列出同时选修“1”号课程和“2”号课程的所有学生的学号； 9) 列出课程表中全部信息，按先修课的升序排列；

10) 列出年龄超过平均值的所有学生名单，按年龄的降序显示；

11) 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果

集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；

12) 显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院

系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；

13) 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩； 14) 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学

生总人数，平均年龄”；

15) 显示选修的课程数大于3的各个学生的选修课程数；

16) 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩； 选做题：

17) 显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩； 18) 显示选修每门课程的及格人数;

19) 显示被选修最多的课程数和最少的课程数；

20) 显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生

人数、女生人数”；

21) 列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

三、源程序：

1

select student.* from student

where sname not like'刘%' 2

select student.* from student

where sname like'沈 ' 3

select student.* from student where sage<28 4

select ssex=

case when ssex='男'then'男生' when ssex='女'then'女生' else'条件不明'

end,sno,sname,sdept from student 5

select cname from course

where cname like'%数据%'

6

select sno,sname,ssex,sage,sdept from student

where sno like ' [12349]%' or sno like ' [12349]%' 7

select student.sno,sname,grade from student,sc where Cno='1' order by Grade desc 8

select sno,Cno from sc

where cno=1 or cno=2 9

select * from course order by cpno asc 10

select sno,sname from student

where sage>(select AVG(sage)from student) order by sage desc 11

select sno 学号,sname 姓名,ssex 性别,出生年份=year(GETDATE())-sage,sdept 院系

from student

order by sage desc 12

select sdept, sdept= case

when COUNT(distinct sno)>=5then '规模很大'

when COUNT(distinct sno)>=4and COUNT(distinct sno)<5 then '规模一般' when COUNT(distinct sno)>=2and COUNT(distinct sno)<4 then '规模小' when COUNT(distinct sno)<2 then '规模很小' end

from student group by sdept 13

select sno,cno,grade from sc

where Grade>=70 and Grade<=80 order by Cno desc,Grade desc 14

select 学生总人数=COUNT(distinct sno),平均年龄=AVG(sage)

from student 15

select sno,选修的课程数=COUNT(Cno) from sc group by sno

having COUNT(Cno)>3 16

select cno,总人数=COUNT(distinct sno),最高成绩=MAX(grade),最低成绩=MIN(grade),平均成绩=AVG(grade)

from sc

group by cno order by cno desc 17

select sno,平均成绩=AVG(grade) from sc group by sno

having AVG(Grade)>(select AVG(Grade) from sc where sno='200515001') 18

select cno,count(sno) as 合格人数 from sc

where grade>60 group by cno 19

select sno,选课数= case

when MAX(Cno)=COUNT(Cno)then '课程数最多' when MIN(Cno)=COUNT(Cno)then '课程数最少' end from sc group by sno

having MAX(Cno)=COUNT(Cno)or MIN(Cno)=COUNT(Cno) 20

select sdept 院系名称,

男生人数=sum(case when ssex='男' then 1 else 0 end), 女生人数=sum(case when ssex='女' then 1 else 0 end) from student group by sdept 21

select sno,平均成绩=AVG(grade) from sc

where Grade<60 group by sno

having COUNT (*)>=2

四、主要问题和解决方法：

这次的实验相对前面的两次实验就显得困难了许多，约束、限制的条件也不相同，对于题目的问题也比较不适应，所以做起来有点困难，不过参考书上的例子，最后还是做出来了。

五、测试数据及结果： 1

2

3

4

5

6

7

8

9

10

六、心得体会与自我评价：

虽然这一次的实验有很大的困难，但是克服了种种困难后完成了本次的实验，感觉收获很多，不仅进一步掌握了SQL的使用，还学会了使用SQL中的SELECT关键字。

实验（ 四 ）

实验名称： 数据库连接查询 实验时间： 2013-4-29

一、实验目的：

1、掌握查询语句的一般格式；

2、熟练掌握单表查询、连接查询、集合查询、统计查询和嵌套查询。 3、熟悉连接查询嵌套查询等的应用。

二、实验内容：

设有一图书馆数据库,其中包括3个表,即图书表、读者表、和借阅表。三个表的结构如下 图书表 列名 书号 书名 作者 出版社 单价

读者表 列名 读者号 姓名 性别 办公电话 部门

借阅表 列名 读者号 书号 借出日期 归还日期 说明 读者唯一的编号 图书唯一的编号 借出图书的日期 归还图书的日期 数据类型 字符串，长度为10 字符串，长度为20 约束说明 主键 主键 说明 读者唯一的编号 读者姓名 读者的性别 读者性别 读者办公电话 数据类型 字符串，长度为8 字符串，长度为2 字符串，长度为8 约束说明 非空值 非空值 空值 字符串，长度为10 主键 说明 图书唯一的编号 图书的名称 图书的编著者名 图书的出版社 出版社确定的图书的单价 数据类型 约束说明 字符串，长度为20 主键 字符串，长度为50 空值 字符串，长度为30 空值 字符串，长度为30 空值 浮点型，float 空值 字符串，长度为30 空值 字符串，长度为8，为’yymmdd’ 非空值 字符串，长度为8，为’yymmdd’ 空值

（1）针对以上3个表，用SQL语言完成以下各项多表连接查询 1） 查询借了书的读者的基本信息及其借阅情况

2） 查询读者的读者号、姓名、借阅的图书名、借出日期、归还日期

3） 查询借阅了机械工业出版社出版，并且书名中包含‘数据库’三个字的图书的读者，并

显示读者号、姓名、书名、出版社，借出日期、归还日期

4） 查询办公室电话为‘88320701’的所有读者的借阅情况，要求包括借阅了图书的读者和

没有借阅的读者，显示他们的读者号、姓名、书名及其借阅日期 （2）用SQL语言完成以下各项子查询：

1）查询单价小于平均单价的图书的书号，书名及其出版社

2）查询单价比‘机械工业出版社’最高单价还高的图书书名及单价

3）查询‘人民邮电出版社’单价比‘机械工业出版社’最低单价还低的图书书名及单价 4）查询已归还的图书的信息 5）查询从未被借阅过的图书信息 6）查询正在借阅的图书信息

7）查询至少借阅了1本机械工业出版社出版的图书的读者的读者号、姓名 8）查询与‘王平’的办公电话相同的读者的姓名

三、源程序：

1

select 读者.*,借阅.* from 读者,借阅

where 读者.读者号=借阅.读者号; 2

select 读者.读者号,姓名,图书.书名,借阅.借出日期,归还日期 from 读者,图书,借阅

where 读者.读者号=借阅.读者号 and 图书.书号=借阅.书号; 3

select 读者.读者号,姓名,图书.书名,出版社,借阅.借出日期,归还日期 from 读者,图书,借阅

where 读者.读者号=借阅.读者号 and 图书.书号=借阅.书号 and 图书.出版社='机械工业出版社' and 书名 like'%数据库%'; 4

select 读者.读者号,姓名,图书.书名,借阅.借出日期 from 读者,图书,借阅

where 读者.读者号*=借阅.读者号 and 办公电话='88320701'; 5

select 书号,书名,出版社 from 图书

where 单价<(select avg(单价) from 图书); 6

select 书名,单价 from 图书

where 单价>(select max(单价)from 图书 where 出版社='机械工业出版社'); 7

select 书名,单价 from 图书

where 出版社='人民邮电出版社' and 单价<(select min(单价)from 图书 where 出版社='机械工业出版社'); 8

select 图书.* from 图书,借阅

where 借阅.归还日期 is not null and 借阅.书号=图书.书号; 9

select distinct 图书.* from 图书,借阅

where 借阅.书号<>图书.书号; 10

select 图书.* from 图书,借阅

where 借阅.归还日期 is null and 借阅.书号=图书.书号;

实验（ 六 ）

实验名称： 视图 实验时间： 2013-6-10

一、实验目的：

1. 了解视图作为虚拟表的作用；

2. 熟练掌握视图的创建、修改和删除的方法。

二、实验内容：

1. 为S表的sno和sname属性建立名为S_View的视图

2. 为三建工程项目建立一个名为SanJian_View的供应情况视图，该视图包括供应商

代码（sno），零件代码（pno），供应数量（qty）

3. 在SanJian_View中找出三建工程项目使用的各种零件代码及其数量 4. 建立颜色为红色且重量大于25的零件视图P_View

5. 将供应商号以及它为所有工程所供应零件的总数定义为一个视图S_Q_View 6. 在SPJ表上按属性SNO分组，求出每組在属性QTY属性上的最大值、最小值和平均

值，且将它们置于视图RVE_View中，然后查询视图RVE_View中供应商号为“S4”的记录。

7. 将S_View视图中供应商号为s5的供应商名修改为“为国”

8. 在S_View视图中插入一个新的记录，供应商号为“s6”，供应商名为“华誉” 9. 删除SanJian_View视图中供应商号为s1的相应记录 10. 为视图S_View建立供应商名sname的视图S_Sname_View 11. 删除视图S_View

三、源程序：

1

create view S_View as

select sno,sname from s 2

create view SanJian_View as

select sno,pno,qty from spj

where jno=(select jno from j

where jname='三建') 3

select pno,sum(qty) from SanJian_View group by pno 4

create view P_View as select * from p

where color='红' and weight>25 5

create view S_Q_View(sno,TotalQTY) as

select sno,sum(qty) from spj group by sno 6

create view RVE_View(sno,qtyMax,qtyMin,qtyMvg) as

select sno,max(qty),min(qty),avg(qty) from spj group by sno

select * from RVE_View where sno='s4' 7

update S_View set sname='为国' where sno='s5'

8 insert into S_View

Values('s6','华誉') 9 delete

from SanJian_View where sno='s1' 10

create view S_Sname_View as

select sname from S_View 11

Drop view SS_View

四、主要问题和解决方法：

这次的实验，主要的问题在于Group by 的用法不够了解，后来通过查询Group by语句的使用和方法，完成了本次的实验。

五、测试数据及结果：

与预测结果一样。

六、心得体会与自我评价：

本次试验过程中任课门数要用到group by，对于这个语句我开始的时候不是很会用，对于这个语句的用法和理解也是一知半解的，后来通过了自己的查询资料和同学的讲解，明白了group by 语句的使用，完成了本次的实验。做数据库实验到这一次，我终于学会了如何运用SQL建立视图，修改视图，查询视图。

11

select 读者.读者号,姓名,书名 from 图书,借阅,读者

where 借阅.书号=图书.书号 and 读者.读者号=借阅.读者号 and 出版社='机械工业出版社';

group by 读者.读者号,姓名 having count(书名)>=1; 12

select r2.姓名 from 读者 r1,读者 r2

where r1.办公电话=r2.办公电话 and r1.姓名='王平'；

四、主要问题和解决方法：

这一次的实验难度有加大了，看了书上的例子也有很多不明白的地方，通过和同学讨论，上网查找资料，最后完成了本次的实验。

五、测试数据及结果： 1

s2

3

4

六、心得体会与自我评价：

这一次的实验，内容比较多，运用了新的表，刚接触的时候感觉比较难，有挺多的地方不明白，通过上网查询资料和与同学讨论得出了一些结论，也有了一些心得，感觉还好。

实验（ 五 ）

实验名称： 数据更新 实验时间： 2013-5-27

一、实验目的：

1. 熟悉数据表结构及使用特点；

2. 熟悉使用Management Stuio界面方式管理数据表数据； 3. 熟悉使用T-SQL语句管理数据表数据。

二、实验内容：

以课本指定的数据库为例，并依据数据表的结构创建相对应的数据表（student、course、sc），请分别使用Management Stuio界面方式及T-SQL 语句实现进行以下操作：

1. 向student表中插入一条记录，值为：20010115,stefen,25岁,男,艺术系 2. 向sc表中新增一条记录，值为：学号为20010115,所选课程号为C2 3. 修改CS系姓名为“李勇”的学生姓名为“李咏”； 4. 修改课程“数据处理”的学分为3学分； 5. 将选修课程“1”的同学成绩加5分； 6. 将选修课程“大学英语”的同学成绩加5分；

7. 将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”； 8. 向student表中增加记录：（200515026，王婧婧、女、21，CS）； 9. 删除数据表student中无系别的学生记录；

10. 删除数据表student中计算机系年龄大于25的男同学的记录； 11. 删除数据表course中学分低于1学分的课程信息；

思考题：插入数据记录除了Insert 还有什么方法？如何导出数据表中的数据？

三、源程序：

1 INSERT

INTO Student(Sno,Sname,Sage,Ssex,Sdept) VALUES('20010115','stefeN','25','M','Art') 2 INSERT

INTO SC(Sno,Cno)

VALUES('20010115','C2')

3

UPDATE Student SET Sname='李永'

WHERE Sname='李勇'and Sdept='cs' 4

UPDATE course SET Ccredit=3

WHERE Cname='数据处理' 5

UPDATE SC

SET Grade = Grade + 5 WHERE Cno='1' 6

UPDATE sc

SET Grade=Grade=5

WHERE '大学英语' = (SELECT Cname FROM course

WHERE course.Cno=sc.Cno) 7

UPDATE Student

SET sname='王丹丹',Ssex='女',Sdept='MA' WHERE Sno='200515010' 8 Insert into student

values('200515026','王婧婧','女',21,'CS'); 9 delete from student where Sdept='' 10 delete from student

where Ssex='男' and Sage>25 and Sdept='CS'; 11 delete from Course where Ccredit<1;

四、主要问题和解决方法：

这次实验内容很多，其中有很多都是不明白的地方，很难下手。最后，通过和同学讨论，经过查询网上的资料和书本的例子对照，完成了本次的实验。

五、测试数据及结果：

与预测结果一样。

六、心得体会与自我评价：

本次的实验内容很多，也很复杂，从无从下手，到经过了讨论和查询书本资料等到完成了本次的实验，感觉学习到了很多的知识，有些以前不太熟悉的也掌握了，不明白的也弄明白了。

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