太原理工数据库实验报告Word格式.docx

资源描述

太原理工数据库实验报告Word格式.docx

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

太原理工数据库实验报告Word格式.docx

1．创建视图

2．视图查询

三、主要仪器设备

操作系统：

Windows7Windows2003Server数据库管理系统：

SQLServer。

四、操作方法与实验步骤

1.创建，修改及删除基本表

（1）创建Student表

CREATETABLEStudent

（SnoCHAR（8）PRIMARYKEY,

SnameCHAR（8）,

SsexCHAR

（2）NOTNULL,

SageINT,

SdeptCHAR（20）

）;

（2）创建Course表

CREATETABLECourse

（CnoCHAR（4）PRIMARYKEY,

CnameCHAR（40）NOTNULL,

CpnoCHAR（4）,

CcreditSMALLINT,

（3）创建SC表

CreatetableSC

（Snochar（8）,

Cnochar（4）,

Gradesmallint,

Primarykey（Sno,Cno）,

Foreignkey（Sno）referencesStudent（Sno）,

Foreignkey（Cno）referencesCourse（Cno）

（4）创建员工表Employee

CREATETABLEEmployee

（

编号CHAR（8）PRIMARYKEY,

姓名VARCHAR（8）notnull

部门CHR（40）,

工资numeric（8,2）,

生日datetime，

职称char（20）,

指出该语句中的错误并改正后执行。

（5）检查表是否创建成

SELECT*FROMStudent

SELECT*FROMCourse

SELECT*FROMSC

SELECT*FROMEmployee

（6）修改表结构及约束

增加班级列

ALTERTABLEStudentADDSclasschar（4）

修改年龄列

ALTERTABLEStudentALTERCOLUMNSagesmallint

增加约束

ALTERTABLECourseADDUNIQUE（Cname）

（7）删除表

DROPTABLEEmployee

2．创建索引

（1）为Course表按课程名称创建索引

CREATEINDEXiCnameOnCourse（Cname）

（2）为Student表按学生姓名创建唯一索引

CREATEUNIQUEINDEXiSnameONStudent（Sname）

（3）为SC表按学号和课程号创建聚集索引

CREATECLUSTEREDINDEXiSnoCnoOnSC（Sno,Cnodesc）

（4）为Course表按课程号创建唯一索引

CREATEUNIQUEINDEXiCnoONCourse（Cno）

3．创建视图

建立信息系学生的视图：

CREATEVIEWIS_Student

SELECTSno,Sname,SageFROMStudent

WHERESdept='

IS'

数据操作

1.插入数据

（1）插入到Student表

INSERTINTOStudentVALUES（'

李勇'

男'

20,'

CS'

1001'

）

INSERTINTOStudentVALUES（'

刘晨'

女'

19,'

INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,Sclass）VALUES（'

王敏'

18,'

MA'

1002'

张立'

1003'

INSERTINTOStudent（Sno,Sname,Ssex,Sclass）VALUES（'

刘洋'

INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,sclass）VALUES（'

’赵斌’,'

19'

1005'

张明明'

（2）插入到Course表

insertintoCourse（Cno,Cname,Cpno,Ccredit）values（'

数据库系统原理'

4）

高等数学'

null,2）

管理信息系统'

操作系统原理'

数据结构'

数据处理'

null,'

C语言'

（3）插入到Sc表

insertintoScvalues（'

92）

85）

insertintoSc（Sno,Cno）values（'

insertintoSc（Sno,Cno,Grade）values（'

null）

88）

90）

80）

（4）多行插入到表中

createtablecs_Student

学号char（8）,

姓名char（8）,

年龄smallint

insertintocs_Student

selectSno,Sname,Sage

fromStudentwhereSdept='

;

（5）检查插入到表中的数据

SELECT*FROMStudent

SELECT*FROMCourse

SELECT*FROMSC

2.修改数据

（1）将学生的年龄改为22岁。

UPDATEstudentSETSage=22WHERESno='

（2）将所有学生的年龄增一岁。

UPDATEStudentSETSage=Sage+1

（3）填写赵斌同学的管理信息系统课程的成绩

UPDATESCSETGrade=85

WHERESno='

ANDCno='

（4）将计算机科学系全体学生的成绩加5分

UPDATEscSETGrade=Grade+5

WHERE'

=（selectSdeptfromstudentwhere=;

（5）请自己完成如下操作

将刘晨同学的2号课程成绩修改为80

updateSCsetGrade=80

whereSno='

ANDCno='

将“”同学的学号修改为“”

updateStudentsetSno=whereSno='

（6）检查数据是否修改

（1）删除学号为2的学生记录

DELETEFROMStudentWHERESno='

（2）删除学号学生的1号课程选课记录

将选课信息复制到一个临时表tmpSC中：

SELECT*INTOtmpSCFROMSC

在tmpSC中执行删除操作：

DELETEFROMtmpSCWHERESno='

andCno='

（3）删除临时表中学生的全部选课记录

deletefromtmpSCwhereSno='

（4）删除计算机科学系所有学生的选课记录

DELETEFROMtmpSCWHERE'

=（selectSdeptfromstudentwhere=）;

（5）删除全部选课记录

DELETEFROMtmpSC

（6）检查数据是否删除

完成如下查询操作：

1．单表查询

（1）按指定目标列查询

查询学生的详细记录：

SELECT*FROMStudent;

查询学生的学号、姓名和年龄

SELECTSno,Sname,SageFROMStudent;

（2）目标列包含表达式的查询

查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。

SELECTSname,'

YearofBirth:

2004-Sage,LOWER（Sdept）FROMStudent;

（3）查询结果集中修改列名称

SELECTSname,'

asBIRTH,2000-SageBIRTHDAY,DEPARTMENT=LOWER（Sdept）

FROMStudent;

（4）取消重复行

查询选修了课程的学生学号：

比较ALL和DISTINCT的区别

SELECTSnoFROMSC;

SELECTDISTINCTSnoFROMSC;

（5）简单条件查询

查询计算机科学系全体学生的名单

SELECTSnameFROMStudentWHERESdept='

（6）按范围查询

查询年龄在20~23岁之间的学生的姓名、系

SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23

（7）查询属性值属于指定集合的行

查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名

SELECTSname,SsexFROMStudentWHERESdeptIN（'

（8）模糊查询

查询所有姓刘学生的姓名、学号

SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE'

刘%'

（9）查询空值

查询缺少成绩的学生的学号和相应的课程号

SELECTSno,CnoFROMscWHEREGradeisnull;

（10）多重条件查询

查询计算机科学系年龄在岁以下的学生姓名

SELECTSnameFROMstudentWHERESdept='

andSage<

20;

（11）结果集排序

查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列

SELECT*FROMStudentORDERBYSdept,SageDESC;

（12）完成下列查询

查询学生基本信息，结果集属性名使用汉字

selectSname'

姓名'

Ssex'

性别'

Sage'

年龄'

Sdept'

所在系'

Sclass'

班级'

fromStudent;

查询信息系且年龄大于23岁同学的学号

selectSno,SnamefromStudentwhereSdept='

andSage>

23;

查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系

selectSno,Sname,Sage,SdeptfromStudentwhereSagein（'

17'

18'

20'

23'

查询年龄不在21~24岁之间的学生的姓名、系别和年龄

selectSname,Sdept,SagefromStudentwhereSagenotbetween21and24;

2．分组统计

（1）聚集函数的使用

查询学生总人数

SELECTCOUNT（*）FROMStudent;

查询选修了课程的学生人数

SELECTCOUNT（DISTINCTSno）FROMSC

查询最高分

SELECTMAX（Grade）FROMSC

（2）聚集函数作用于部分行

统计2号课程的总分、均分

SELECTSUM（grade）总分,AVG（grade）均分,MAX（grade）最高分

FROMscWHERECno='

（3）分组统计

统计各门课程的选课人数、均分和最高分

selectcno课程号,count（*）人数,AVG（grade）均分,MAX（grade）最高分

fromscgroupbyCno

统计均分大于90的课程

havingAVG（grade）>

（4）完成下面的查询

统计每个同学的学号、选课数、平均成绩和最高成绩

selectSno'

学号'

COUNT（Cno）'

选课数'

AVG（Grade）'

平均成绩'

MAX（Grade）'

最高成绩'

fromSCgroupbySno

统计每个班的每门课的选课人数、平均成绩和最高成绩

select班级,课程号,COUNT（*）选课人数,AVG'

MAX'

fromSC,Student

where=

groupby,

3.连接查询

（1）在WHERE中指定连接条件

查询每个参加选课的学生信息及其选修课程的情况

SELECT,Sname,Ssex,Sage,Sdept,Cno,Grade

FROMStudent,SC

WHERE=

查询每一门课的间接先修课

SELECT*FROMcoursefirst,coursesecond

WHERE=;

SELECT,FROMcoursefirst,coursesecond

（2）在FROM中指定连接条件

SELECT,Sname,Ssex,Sage,Sdept,Cno,Grade

FromStudentJOINSCON=

（3）使用外连接查询

查询每个学生信息及其选修课程的情况

FROMStudentLEFTOUTERJOINSCON=

（4）复合条件连接查询

查询选修号课程且成绩在分以上的所有学生

SELECT,Sname

FROMStudentjoinSCON=/*连接条件*/

WHERE='

AND>

90;

/*过滤条件*/

（5）多表查询

查询每个学生的学号、姓名、选修的课程名及成绩

SELECT,Sname,Cname,Grade

FROMStudent,SC,Course

WHERE=AND=;

（6）完成下列查询

查询选修了2号课程的同学的学号和姓名

select,Sname

fromStudent,SC

where=and='

查询各门课程的课程号、课程名称以及选课学生的学号

select课程号,课程名称,学号

fromSC,Course

查询选修了数据库系统原理课程的同学的学号和姓名和成绩

select,Sname,Grade

fromStudent,Course,SC

where=and

=andCname='

4.嵌套查询

（1）由In引出的子查询

查询与“刘晨”在同一个系学习的学生

SELECTSno,Sname,SdeptFROMStudent

WHERESdeptIN（SELECTSdeptFROMStudentWHERESname='

（2）由比较运算符引出的子查询

找出每个学生超过他选修课程平均成绩的课程号。

SELECTSno,CnoFROMSCx

WHEREGrade>

=（SELECTAVG（Grade）FROMSCy

WHERE=;

（3）带修饰符的比较运算符引出的子查询

查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

SELECTSname,SageFROMStudent

WHERESage<

ALL（SELECTSageFROMStudentWHERESdept='

ANDSdept<

;

（4）由EXISTS引出的子查询

查询所有选修了1号课程的学生姓名

SELECTSnameFROMStudent

WHEREEXISTS（SELECT*FROMSCWHERESno=ANDCno='

5.集合查询

（1）集合并

查询计算机科学系的学生及年龄不大于19岁的学生

SELECT*FROMStudentWHERESdept='

UNION

SELECT*FROMStudentWHERESage<

=19

（2）集合交

查询计算机科学系且年龄不大于19岁的学生

SELECT*FROMStudentWHERESdept='

INTERSECT

（3）集合差

查询计算机科学系且年龄大于19岁的学生

EXCEPT

SELECT*FROMStudentWHERESage<

=19;

视图操作

建立视图并基于视图进行查询：

1.创建视图

（1）建立学生基本信息视图

CREATEVIEWStudent_VIEW（学号,姓名,性别,年龄,系,班级）

SELECTSno,Sname,Ssex,Sage,Sdept,Sclass

FROMStudent;

（2）建立学生均分视图

CREATEVIEWS_G（Sno,Gavg）

SELECTSno,avg（Grade）

FROMSCGROUPBYSno;

（3）建立选课信息视图

CREATEVIEWXK_VIEW

SELECTStudent.*,Course.*,Grade

2.视图查询

（1）查询学生基本信息

SELECT*FROMStudent_VIEW

（2）找出每个学生超过他选修课程平均成绩的课程号

SELECT,Cno,grade

FROMSC,S_G

WHERE=andGrade>

（3）查询每个学生的学号、姓名、选修的课程名及成绩

SELECTSno,Sname,Cname,Grade

FROMXK_VIEW

（4）比较使用视图查询和直接从基表查询的优点

五、实验数据记录和处理

检查数据是否被修改：

检查数据是否删除：

单表查询：

查询学生基本信息，结果集属性名使用查询信息系且年龄大于23岁同学的学号

汉字

无结果

查询年龄是17、18、20、23岁同学的查询年龄不在21~24岁之间的学生的姓名、

学号、姓名、年龄和所在系系别和年龄

分组统计：

统计每个同学的学号、选课数、平均成绩统计每个班的每门课的选课人数、平均成绩

连接查询：

查询选修了2号课程的同学的学号和姓名查询各门课程的课程号、课程名称以及选课学

生的学号

查询选修了数据库系统原理课程的同学的学号

六、实验结果与分析

Student

CourseSc

比较使用视图查询和直接从基表查询的优点

1.视图可以简化用户的操作

视图机制使用户可以将注意力集中在所关心的数据上，而从基本表直接得来则显得复杂，定义了视图则可以简化查询操作。

2.视图使用户能以多种角度看待同一数据

视图可以让用户从不同的方式看待同一数据，适当利用视图可以比基本表更清晰地表达。

3.视图对重构数据库提供了一定程度的逻辑独立性

逻辑结构改变，不影响程序使用。

4.视图能够对机密数据提供安全保护

视图可以让特定的用户查询特定的内容，把用户限制在数据不同的子集，保证安全性。

七、讨论、心得

这个实验是对数据库基本操作的熟悉，花费了大量的时间去进行测试，有时候是出错不是因为代码错误，而是因为插入数据是不能一一对应，或是在连接时出现问题，与表的定义也有一定的关系。

总之编写的时候遇到不少问题，通过解决问题来熟悉了操作，更是要耐心的解决问题，有时候出错的问题非常简单，只要认真的检查一下代码就能发现，希望自己可以在编写代码时更加认真。

实验三：

数据完整性

1、实验目的

（1）了解SQLSerer数据库系统中数据完整性控制的基本方法

（2）熟练掌握常用CREATE或ALTER在创

展开阅读全文