数据库原理实验报告.docx

资源描述

数据库原理实验报告.docx

《数据库原理实验报告.docx》由会员分享，可在线阅读，更多相关《数据库原理实验报告.docx（21页珍藏版）》请在冰豆网上搜索。

数据库原理实验报告.docx

数据库原理实验报告

《数据库原理》

实

验

报

告

学号:

08093556

姓名:

张雪涛

班级:

信科09-2班

指导教师：

谢红侠

中国矿业大学计算机科学与技术学院

2011年4月

数据库原理教师成绩评定表

评定成绩的依据：

①基础理论及基本技能的掌握

②独立解决实际问题的能力；

③研究内容的理论依据和技术方法；

④取得的主要成果

⑤工作态度及工作量；

成绩采用优良中差四个等级

评定成绩表

实验序号

实验成绩

指导教师

日期

实验一

实验二

实验一：

SQL数据定义功能

一、实验内容及要求

1.使用SQL语句建立学生管理系统相关的表，同时完善各表的相关完整性约束。

其中带有下划线的为主码

学院（学院代码，学院名称）

学生（学号，姓名，性别，学院代码），学院代码为外码

教师（教师号，教师姓名，学院代码），学院代码为外码

课程（课程号，课程名，学时）

学习（学号，课程号，成绩），学号为外码，课程号为外码

开课（教师号，课程号），教师号为外码，课程号为外码

2.对各表进行增加、删除、修改属性操作

添加操作：

对学生表添加出生日期字段，和家庭地址字段，教师表增加性别字段，出生日期字段，对课程表增加先修课程字段，并且先修课程为本关系外码。

删除操作：

删除学生表家庭地址字段，删除教师表出生日期字段

修改操作：

修改先课程表中先修课程字段名称为先修课程号，修改学生姓名字段，该字段值不允许取空值

3.建立索引

为学生表在学生姓名上建立名为sname-index的索引，在学院代码字段上建立名为dept-index，降序。

4.删除表操作

删除上述表，重新执行查询再次建立各表

5.利用ACCESS2003/2007完善各表的数据内容

二、实验目的

熟练掌握SQL的各种数据定义功能，包括

1.定义表的功能，包括主码和外码的定义

2.修改表的定义功能，包括增加属性，删除属性，修改属性类型

4.建立和删除索引操作，理解索引的作用

5.删除表功能

三、实验步骤及运行结果

（一）

1、建立学院表：

CREATETABLE学院

（学院代码CHAR（8）PRIMARYKEY,

学院名称CHAR（15））;

2、建立学生表：

CREATETABLE学生

（学号CHAR（8）PRIMARYKEY,

姓名CHAR（15）,

性别CHAR

（2）,

学院代码CHAR（8）,

FOREIGNKEY（学院代码）REFERENCES学院（学院代码））;

3、建立教师表：

CREATETABLE教师

（教师号CHAR（8）PRIMARYKEY,

教师姓名CHAR（20）,

学院代码CHAR（8）,

FOREIGNKEY（学院代码）REFERENCES学院（学院代码））;

4、建立课程表：

CREATETABLE课程

（课程号CHAR（8）PRIMARYKEY,

课程名CHAR（15）,

学时SMALLINT）;

5、建立学习表：

CREATETABLE学习

（学号CHAR（8）,

课程号CHAR（8）,

成绩SMALLINT,

PRIMARYKEY（学号,课程号）,

FOREIGNKEY（学号）REFERENCES学生（学号）,

FOREIGNKEY（课程号）REFERENCES课程（课程号））;

6、建立开课表：

CREATETABLE开课

（教师号CHAR（8）,

课程号CHAR（8）,

PRIMARYKEY（教师号,课程号）,

FOREIGNKEY（教师号）REFERENCES教师（教师号）,

FOREIGNKEY（课程号）REFERENCES课程（课程号））;

（二）

1、开课表增加属性：

ALTERTABLE开课ADD开课时间DATETIME;

2、学习表增加属性：

ALTERTABLE学习ADD任课教师号CHAR（8）

3、学生表增加属性：

ALTERTABLE学生ADD年龄SMALLINT;

4、学生表删除属性：

ALTERTABLE学生DROPCOLUMN年龄;

5、修改课程表属性：

ALTERTABLE课程ALTERCOLUMN课程名CHAR（20）;

（三）

1、课程表建立索引：

CREATEUNIQUEINDEXCOU_IND_CNOON课程（课程号）;

2、学生表建立索引：

CREATEUNIQUEINDEXSTU_IND_SNOON学生（学号）;

3、学习表建立索引：

CREATEUNIQUEINDEXSC_IND_SNO_CNOON学习（学号,课程号）;

（四）

1、建立临时表：

CREATETABLE临时表

（时间DATETIME,

姓名CHAR（15）,

地点CHAR（15）,

PRIMARYKEY（时间））;

2、删除临时表：

DROPTABLE临时表CASCADE;

（五）

完善表内容之后，

运行结果：

学院表：

学院

学院代码

学院名称

1122

计算机学院

1123

化工学院

1124

文法学院

1125

外文学院

1126

体育学院

1127

信电学院

1128

环侧学院

学生表：

学生

学号

姓名

性别

学院代码

2100

孙策

男

1122

2101

赵子玉

男

1122

2102

高夏丽

女

1122

2103

孙晓峰

男

1123

2104

豆顺

男

1123

3100

张静静

女

1123

3101

武梦醒

女

1124

3102

禹贵

女

1124

3103

李明凯

男

1125

4100

杨柳

男

1125

4101

李阳

男

1126

4102

张恒

男

1126

4103

周浩

男

1127

4104

姚梦玲

女

1127

5100

王选

男

1128

5101

赵忠祥

男

1128

5102

陈天宇

男

1122

5103

吴浩天

男

1123

5104

陈圆圆

女

1124

教师表：

教师

教师号

教师姓名

学院代码

0001

马辉

1122

0002

邱理想

1123

0003

杜明新

1124

0004

罗慧芬

1125

0005

刘芳

1126

0006

蒋克福

1127

0007

鲍福萍

1128

0008

王敏

1128

0009

赵柯

1128

课程表：

课程

课程号

课程名

学时

5000

化学

5001

英语

5002

数据库

5003

数学

5004

物理

5005

生物

5006

体育

5007

美术

5008

音乐

学习表：

学习

学号

课程号

成绩

任课教师号

2100

5000

0001

2100

5001

0002

2100

5002

0002

2100

5003

0004

2100

5004

0005

2100

5005

0006

2100

5006

0007

2100

5007

2100

5008

2101

5003

0003

2102

5000

0001

2102

5003

0004

2102

5004

0004

2103

5000

0007

2103

5001

0001

2104

5005

0005

3100

5000

0001

3100

5001

0002

3101

5002

0003

3101

5004

0004

3101

5005

0006

3102

5002

0003

3103

5002

0002

3103

5006

0007

4100

5001

0002

4100

5002

0003

4100

5003

0003

4101

5000

0007

4101

5003

0003

4102

5002

0002

4102

5003

0004

4103

5004

0005

4103

5005

0005

4104

5004

0004

4104

5006

0007

5100

5002

0003

5100

5003

0003

5101

5003

0004

5101

5005

0005

5101

5006

0006

5102

5000

0007

5102

5001

0001

5103

5000

0001

5103

5001

0001

5103

5002

0002

5103

5003

0004

5103

5004

0005

5103

5005

0006

5103

5006

0006

5103

5007

5103

5008

开课表：

开课

教师号

课程号

0001

5000

0001

5001

0002

5001

0002

5002

0003

5002

0003

5003

0004

5004

0005

5004

0005

5005

0006

5005

0006

5006

0007

5000

0007

5007

0008

5007

0009

5008

四、实验体会

在建立查询的时候，要明确各表之间的联系，要正确指明每个表的主码和外码，这样之后，关系表中才会正确显示各表之间的联系。

另外，从这次实验中，我学到数据更新中“插入子查询结果”的好处，在修改查询编码后，我们可以用这个操作，来重新建表和保留原表中的有效记录（原表先重命名，在执行改后的查询，在编写一个插入子查询的编码，就可以向新表中插入原有数据了）。

实验二：

练习SQL数据操纵功能

一、实验内容及要求

1．求选修了某个老师上课的学生，成绩在90分以上的姓名、课程名称和成绩；

2．求选修了某个老师所授全部课程的学生姓名和学院名称；

3．求没有选修某门课程的学生学号和姓名；

4．求至少选修了两门课程的学生学号；

5．求某门课程不及格学生姓名和考试成绩

6．求至少选修了与某个同学选修的课程相同的学生学号

7．求至少选修了某两门课程的学生姓名和学院名称。

8．查询某个同学没有选修的课程号和课程名

9．查询没有被任何学生选修的课程的课程号

10．求选修了全部课程的学生姓名

11．查询各学院某门课程的平均分，并按照成绩从高到低的顺序排列。

12．查询选修数据库原理课程的学生姓名和所在院系，结果按各院系排列，同时成绩从高到低排列。

13．求学时在30-45之间（含30和45）的课程的课程号和课程名称及授课教师。

14．检索选修数据库原理课程的最高分学生的姓名

15．查询选课人数超过50（根据情况自己定人数）人的课程的课程号及课程名。

16．自行提出查询要求，内容包括并运算、差运算、交运算、笛卡儿积运算、连接运算、自然连接运算以及复杂统计表。

二、实验目的

使用SQL语句进行各种查询

1、熟练掌握单表查询，包括

（1）选择表中的若干列（投影）

（2）选择表中的若干元组（选择）（3）对查询结果排序（4）使用集函数（5）对查询结果分组

2、熟练掌握复杂查询，包括1）等值与非等值连接查询2）自身连接:

一个表与其自己进行连接。

3）外连接查询4）复合条件连接：

WHERE子句中可以有多个连接条件。

3、熟练掌握嵌套查询，包括1）带有IN谓词的子查询2）带有比较运算符的子查询（子查询一定要跟在比较符之后）3）带有ANY或ALL谓词的子查询（使用ANY和ALL时必须同时使用比较运算符）4）带有EXISTS谓词的子查询（查询结果不返回任何数据，只产生逻辑“真”或“假”）

4、使用SQL和ACCESS结合完成一些复杂的统计

三、实验步骤及运行结果

1、查询编码：

SELECT姓名,课程.课程号,成绩

FROM学生,课程,学习

WHERE成绩>90

AND学习.任课教师号=

（select教师号

from教师

where教师姓名='马辉'）

AND学生.学号=学习.学号

and课程.课程号=学习.课程号;

运行结果：

查询1

姓名

课程号

成绩

孙策

5000

孙晓峰

5001

吴浩天

5000

2、查询编码：

SELECT姓名,学院名称

FROM学生,学院

WHERE学生.学院代码=学院.学院代码

andnotexists

（select*

from开课

where教师号='0001'

andnotexists

（select*

from学习

where学号=学生.学号

and课程号=开课.课程号））;

运行结果：

查询2

姓名

学院名称

孙策

计算机学院

孙晓峰

化工学院

张静静

化工学院

陈天宇

计算机学院

吴浩天

化工学院

3、查询编码：

SELECT学号,姓名

FROM学生

WHEREnotexists

（select*

from学习

where课程号='5000'

and学生.学号=学习.学号）;

运行结果：

查询3

学号

姓名

2101

赵子玉

2104

豆顺

3101

武梦醒

3102

禹贵

3103

李明凯

4100

杨柳

4102

张恒

4103

周浩

4104

姚梦玲

5100

王选

5101

赵忠祥

5104

陈圆圆

4、查询编码：

SELECT学号

FROM学习

GROUPBY学号

HAVINGcount（课程号）>=2;

运行结果：

查询4

学号

2100

2102

2103

3100

3101

3103

4100

4101

4102

4103

4104

5100

5101

5102

5103

5、查询编码：

SELECT学生.姓名,学习.成绩

FROM学生,学习

WHERE（（（学生.学号）=学习.学号）And（（学习.课程号）='5001'）And（（学习.成绩）<60））;

运行结果：

查询5

姓名

成绩

陈天宇

6、查询编码：

SELECTDISTINCTB.学号

FROM学习ASA,学习ASB

WHEREA.课程号=B.课程号AndA.学号='2103';

运行结果：

查询6

学号

2100

2102

2103

3100

4100

4101

5102

5103

7、查询编码：

SELECT姓名,学院名称

FROM学生,学院

WHERE学生.学院代码=学院.学院代码

and学号in

（selectA.学号

from学习asA,学习asB

whereA.学号=B.学号

AndA.课程号='5000'

AndB.课程号='5001'

GROUPBYA.学号）;

运行结果：

查询7

姓名

学院名称

孙策

计算机学院

孙晓峰

化工学院

张静静

化工学院

陈天宇

计算机学院

吴浩天

化工学院

8、查询编码：

SELECT课程号,课程名

FROM课程

WHERE课程号notin

（select课程号

from学习,学生

where学生.学号=学习.学号

and姓名='张静静'）;

运行结果：

查询8

课程号

课程名

5002

数据库

5003

数学

5004

物理

5005

生物

5006

体育

5007

美术

5008

音乐

9、查询编码：

SELECT课程号

FROM课程

WHERE课程号notin

（select课程号

from学习）;

运行结果：

查询9

课程号

10、查询编码：

SELECT姓名

FROM学生

WHEREnotexists（select*from课程wherenotexists（select*from学习where学号=学生.学号and课程号=课程.课程号））;

运行结果：

查询10

姓名

孙策

吴浩天

11、查询编码：

SELECT课程号,avg（成绩）AS平均分

FROM学习

GROUPBY课程号

ORDERBYavg（成绩）DESC;

运行结果：

查询11

课程号

平均分

5006

90.4

5003

86.6666666666667

5004

86.166********67

5000

84.5714285714286

5001

82.5

5005

82.3333333333333

5002

5008

5007

53.5

12、查询编码：

SELECT姓名,成绩AS数据库成绩,学院名称

FROM学习,课程,学生,学院

WHERE学习.课程号=课程.课程号And学生.学号=学习.学号And学生.学院代码=学院.学院代码And课程.课程名='数据库'

ORDERBY学院名称,成绩DESC;

运行结果：

查询12

姓名

数据库成绩

学院名称

吴浩天

化工学院

王选

环侧学院

孙策

计算机学院

张恒

体育学院

杨柳

外文学院

李明凯

外文学院

武梦醒

文法学院

禹贵

文法学院

13、查询编码：

SELECT课程.课程号,课程名,教师姓名

FROM课程,开课,教师

WHERE课程.课程号=开课.课程号And教师.教师号=开课.教师号And学时Between30And45;

运行结果：

查询13

课程号

课程名

教师姓名

5003

数学

杜明新

5005

生物

刘芳

5005

生物

蒋克福

14、查询编码：

SELECT姓名

FROM学生,学习,课程

WHERE学生.学号=学习.学号

and学习.课程号=课程.课程号

and课程.课程名='数据库'

and学习.成绩>=all

（select成绩

from学习,课程

where课程.课程号=学习.课程号

and课程.课程名='数据库'）;

运行结果：

查询14

姓名

王选

15、查询编码：

SELECT课程号,课程名

FROM课程

WHERE课程号in

（SELECT课程.课程号

FROM学习,课程

WHERE学习.课程号=课程.课程号

GROUPBY课程.课程号

HAVINGcount（*）>5）;

运行结果：

查询15

课程号

课程名

5000

化学

5001

英语

5002

数据库

5003

数学

5004

物理

5005

生物

四、实验体会

从这次实验中，我认识到查询编码是需要下功夫掌握的，自己在编码时，在很多问题上要花很长时间思考。

要熟练的掌握和运用各类查询语句，并灵活应用是要继续努力。

展开阅读全文