广东海洋大学计算机科学与技术数据库实验2.docx

广东海洋大学计算机科学与技术数据库实验2.docx

《广东海洋大学计算机科学与技术数据库实验2.docx》由会员分享，可在线阅读，更多相关《广东海洋大学计算机科学与技术数据库实验2.docx（36页珍藏版）》请在冰豆网上搜索。

广东海洋大学计算机科学与技术数据库实验2

GDOU-B-11-112

广东海洋大学学生实验报告书（学生用表）

实验名称

实验2数据的查询、更新

课程名称

数据库系统原理

课程号

1620072

学院（系）

信息学院

专业

计算机科学与技术

班级

1113

学生姓名

学号

实验地点

科技楼

实验日期

04-12

实验二数据的查询、更新

一、实验目的

1、掌握用户自定义数据类型的方法

2、掌握用T-SQL语句进行数据的插入、修改、删除的方法

3、熟练掌握SELECT语句，能够运用该语句完成各种查询

二、实验要求

1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；

2、能认真独立完成实训内容；

3、实验后做好实验总结，根据实验情况完成总结报告。

三、实验内容

1、用T-SQL语句，创建一用户自定义数据类型：

名称为“char20”，数据类型为varchar，长度为20，允许为空。

sp_addtypechar20,'varchar（20）',null

2、用T-SQL语句，建立一个“学生课程数据库”，在此基础上建立该数据库包含的学生表，课程表，学生选修表，并向各表插入如下相应的数据。

（1）建立“学生课程数据库”数剧库：

CREATEDATABASE学生课程数据库

ONPRIMARY

（

NAME=Student_dat,

FILENAME='D:

\学生课程数据库.mdf',

SIZE=10MB,

MAXSIZE=50MB,

FILEGROWTH=10%

）

LOGON

（

NAME=Student_log,

FILENAME='D:

\学生课程数据库.ldf',

SIZE=5MB,

FILEGROWTH=10%

）

GO

（2）创建学生表

CREATETABLEStudent（

SnoINTPRIMARYKEY,

SnameCHAR（10）,

SsexCHAR

（2）CHECK（Ssex='男'ORSsex='女'）,

SageSMALLINTCHECK（SageBETWEEN15AND30）,

SdeptCHAR（20）

）;

插入数据：

INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept）

VALUES（'95001','李敏勇','男',20,'CS'）

INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept）

VALUES（'95002','刘晨','女',19,'IS'）

INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept）

VALUES（'95003','王敏','女',18,'MA'）

INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept）

VALUES（'95004','张立','男',18,'IS'）;

（2）创建课程表

CREATETABLECourse（

CnoCHAR（4）PRIMARYKEY,

CnameCHAR（10）NOTNULL,

CpnoCHAR（4）,

CreditINTCHECK（Credit>=0ANDCredit<=100）,

Teacherchar20

）;

插入数据：

INSERTINTOCourse（Cno,Cname,Cpno,Credit,Teacher）

VALUES（1,'数据库',5,4,'王芳'）

INSERTINTOCourse（Cno,Cname,Cpno,Credit,Teacher）

VALUES（2,'数学',NULL,2,'刘新'）

INSERTINTOCourse（Cno,Cname,Cpno,Credit,Teacher）

VALUES（3,'信息系统',1,4,'刘新'）

INSERTINTOCourse（Cno,Cname,Cpno,Credit,Teacher）

VALUES（4,'操作系统',6,3,'高升'）

INSERTINTOCourse（Cno,Cname,Cpno,Credit,Teacher）

VALUES（5,'数据结构',7,4,'宋明'）

INSERTINTOCourse（Cno,Cname,Cpno,Credit,Teacher）

VALUES（6,'数据处理',NULL,2,'张彬'）

INSERTINTOCourse（Cno,Cname,Cpno,Credit,Teacher）

VALUES（7,'Pascal语言',6,4,'李磊'）;

（2）创建学生选修表

CREATETABLESC（

SnoINTFOREIGNKEYREFERENCESStudent（Sno）,

CnoCHAR（4）FOREIGNKEYREFERENCESCourse（Cno）,

GradeINTCHECK（Grade>=0ANDGrade<=100）,

PRIMARYKEY（Sno,Cno）

）;

插入数据：

INSERTINTOSC（Sno,Cno,Grade）

VALUES（'95001','1',92）

INSERTINTOSC（Sno,Cno,Grade）

VALUES（'95001','2',85）

INSERTINTOSC（Sno,Cno,Grade）

VALUES（'95001','3',88）

INSERTINTOSC（Sno,Cno,Grade）

VALUES（'95002','2',90）

INSERTINTOSC（Sno,Cno,Grade）

VALUES（'95003','2',55）

INSERTINTOSC（Sno,Cno,Grade）

VALUES（'95004','2',70）

3、用T-SQL语句，修改上面所建学生课程数据库中数据:

1）向学生表:

Student中加入一条记录：

（95030,谢非,男,22,CS）并保存

INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept）

VALUES（'95030','谢非','男',22,'CS'）

2）将李敏勇的数据库的成绩改为98分

UPDATESC

SETGrade=98

WHERESC.SnoIN

（SELECTSC.SnoFROMStudent,SC

WHEREStudent.Sno=SC.SnoANDStudent.Sname='李敏勇'

）AND

SC.CnoIN（SELECTSC.CnoFROMSC,Course

WHERECourse.Cno=SC.CnoANDCourse.Cname='数据库'）

3）删除学生表Student中谢非的记录并保存

DELETEStudent

WHEREStudent.Sname='谢非'

4）能不能从Student表中删除李敏勇学生的记录,为什么?

能不能删除王敏,张立两个学生的记录?

DELETEStudent

WHEREStudent.Sname='李敏勇'

不能删除李敏勇学生的记录：

“DELETE语句与REFERENCE约束"FK__SC__Sno__07020F21"冲突。

该冲突发生于数据库"学生课程数据库"，表"dbo.SC",column'Sno'。

”；

也不能删除王敏,张立两个学生的记录。

4、用T-SQL语句，完成下面简单的查询

1）查询全体学生的学号、姓名及年龄.

SELECTSno,Sname,Sage

FROMStudent

2）查询全体学生的姓名,年龄及所在系（要用小写字母表示系名，并用“系名”来表示

列名）。

SELECTSname,Sage,LOWER（Sdept）'系名'

FROMStudent

3）查询选修了课程的学生学号

SELECTSno

FROMSC

4）查询信息系全体学生的姓名

SELECTSname

FROMStudent

WHERESdept='IS'

5）查询所有年龄在20岁以下的学生姓名及其年龄

SELECTSname,Sage

FROMStudent

WHERESage<20

6）查询年龄在20到18间的学生的姓名,系别及年龄

SELECTSname,Sdept,Sage

FROMStudent

WHERESageBETWEEN18AND20

7）查询年龄不在23到19间的学生的姓名,系别及年龄

SELECTSname,Sdept,Sage

FROMStudent

WHERESage<19ORSage>23

8）查询不是信息系（IS）和计算机系（CS）学生的姓名和性别

SELECTSname,Ssex,Sdept

FROMStudent

WHERENOTSdept='IS'ANDNOTSdept='CS'

9）查询所有姓刘的学生的姓名,学号和性别

SELECTSname,Sno,Ssex

FROMStudent

WHERESnameLIKE'刘%'

10）查询姓“张”且名为一个汉字的学生的姓名

SELECTSname

FROMStudent

WHERESnameLIKE'张_'

11）查询名字中第2个字为”敏”字的学生姓名和学号

SELECTSname,Sno

FROMStudent

WHERESnameLIKE'_敏%'

12）查询所有不姓刘的学生姓名

SELECTSname

FROMStudent

WHERESnameNOTLIKE'刘%'

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

SELECTSno,Sname,Ssex,Sage,Sdept

FROMStudent

ORDERBYSdept,SageDESC

14）查询学生表中所有学生信息，要求只显示前10％行数据

SELECTTOP10PERCENT*

FROMStudent

15）按成绩降序查询输出SC表中成绩大于等于70分的所有学生的学号，要求只显示前2

行数据,若第3行后的数据也等于70分也要显示。

SELECTTOP2WITHTIESSno,Grade

FROMSC

WHEREGrade>=70

ORDERBYGradeDESC

16）查询每个学生的学号、课程号及分数，同时统计每个学生的总分

SELECTSno,Cno,Grade

FROMSC

ORDERBYSno

COMPUTESUM（Grade）BYSno

17）查询每个学生的各科分数、最高分、最低分、总分、平均分

SELECTSno,Cno,Grade

FROMSC

ORDERBYSno

COMPUTEMAX（Grade）,MIN（Grade）,SUM（Grade）,AVG（Grade）BYSno

5、用T-SQL语句完成下面的查询

1）查询学生的总人数

SELECTCOUNT（*）'总人数'

FROMStudent

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

SELECTCOUNT（*）

FROMStudent,SC

WHEREStudent.Sno=SC.Sno

3）计算选2号课程的学生平均成绩

SELECTAVG（Grade）

FROMSC

WHERECno=2

4）查询选修2号课程的学生最高分数

SELECTMAX（Grade）

FROMSC

WHERECno=2

5）求各个课程号及相应的选课人数

SELECTCno,COUNT（Sno）'选课人数'

FROMSC

GROUPBYCno

6）查询选修了2门以上的课程的学生学号

SELECTSno

FROMSC

GROUPBYSno

HAVINGCOUNT（*）>2

7）查询每个学生及其选修课程的情况

SELECTStudent.*,Course.*

FROMStudent,SC,Course

WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno

8）查询每一门课的间接先修课（即先修课的先修课）

SELECTCourse.Cname,Course_2.Cname'间接先修课'

FROMCourseINNERJOIN

CourseASCourse_1ONCourse.Cno=Course_1.CpnoINNERJOIN

CourseASCourse_2ONCourse_1.Cno=Course_2.Cpno

9）查询选修2号课程且成绩在90分以上（包括90分）的所有学生。

SELECTSname

FROMStudent

WHERESnoIN（

SELECTSno

FROMSC

WHERECno=2ANDGrade>=90）

6.用T-SQL语句完成下面的查询

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

SELECTSno,Sname,Ssex,Sage,Sdept

FROMStudent

WHERESdeptIN（

SELECTSdept

FROMStudent

WHERESname='刘晨'）ANDSname!

='刘晨'

2）查询选修了课程名为“数学”的学生学号和姓名

SELECTStudent.Sno,Student.Sname

FROMStudent,SC,Course

WHERECourse.Cname='数学'ANDStudent.Sno=SC.SnoANDSC.Cno=Course.Cno

3）查询其它系中比信息系中某一学生年龄小的学生姓名和年龄

SELECTSname,Sage

FROMStudent

WHERESdept!

='IS'ANDSage

FROMStudent

WHERESdept='IS'）

4）查询其它系中比计算机系所有学生年龄都小的学生姓名及年龄

SELECTSname,Sage

FROMStudent

WHERESdept!

='IS'ANDSage

FROMStudent

WHERESdept='IS'）

5）查询所有选修了2号课程的学生姓名

SELECTSname

FROMStudent,SC

WHEREStudent.Sno=SC.SnoANDSC.Cno=2

6）查询没有选修3号课程的学生姓名

SELECTSname

FROMStudent

WHERENOTEXISTS（SELECT*FROMSCWHEREStudent.Sno=SC.SnoANDCno=1）

7、用T-SQL语句完成下面的复杂查询

1）至少选修刘老师所授课程中一门课程的女学生姓名

SELECTSname

FROMStudent,SC,Course

WHERESsex='女'ANDCourse.TeacherLIKE'刘%'ANDStudent.Sno=SC.SnoANDSC.Cno=Course.Cno

2）检索王同学不学的课程的课程号

SELECTCno

FROMCourse

EXCEPT

SELECTCourse.Cno

FROMStudent,SC,Course

WHEREStudent.SnameLIKE'王%'ANDStudent.Sno=SC.SnoANDSC.Cno=Course.Cno

3）检索全部学生都选修的课程的课程号与课程名。

SELECTCno,Cname

FROMCourse

WHERENOTEXISTS

（SELECT*

FROMStudent

WHERENOTEXISTS

（SELECT*

FROMSC

WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno））

4）检索选修课程包含刘老师所授课的学生学号。

SELECTDISTINCTStudent.Sno

FROMStudent,SC,Course

WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.TeacherLIKE'刘%'

5）求选修课程号为2的学生的平均年龄。

SELECTAVG（Sage）'平均年龄'

FROMStudent,SC

WHEREStudent.Sno=SC.SnoANDSC.Cno=2

6）求刘老师所授课程的每门课程的学生平均成绩。

SELECTCname,Teacher,AVG（Grade）'平均成绩'

FROMStudent,SC,Course

WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.TeacherLIKE'刘%'

GROUPBYCname,Teacher

7）检索学号比刘同学大，而年龄比他小的学生姓名。

SELECTSname

FROMStudent

WHERESno>（SELECTSnoFROMStudentWHERESname='刘%'）

ANDSage<（SELECTSageFROMStudentWHERESname='刘%'）

8）求年龄大于女同学平均年龄的男同学姓名和年龄。

SELECTSname,Sage

FROMStudent

WHERESage>（SELECTAVG（Sage）FROMStudentWHERESsex='女'）ANDSsex='男'

9）求年龄大于所有女同学年龄的男学生姓名和年龄。

SELECTSname,Sage

FROMStudent

WHERESage>（SELECTMAX（Sage）FROMStudentWHERESsex='女'）ANDSsex='男'

10）检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表S（SNO,SNAME,SEX）。

CREATETABLES（

SNOINTPRIMARYKEY,

SNAMECHAR（10）,

SEXCHAR

（2）CHECK（SEX='男'ORSEX='女'））

INSERTINTOS（SNO,SNAME,SEX）

SELECTDISTINCTStudent.Sno,Sname,Ssex

FROMStudent,SC

WHEREStudent.Sno=SC.SnoANDGrade>80

11）把选课数学课不及格的成绩全改为空值。

UPDATESC

SETGrade=NULL

WHERE'数学'IN（

SELECTCnameFROMCourse,SC

WHERESC.Cno=Course.CnoANDGrade<=60）

12）把王同学的选课信息全部删去。

DELETESC

WHERESnoIN（SELECTSC.SnoFROMStudent,SC

WHERESnameLIKE'王%'ANDStudent.Sno=SC.Sno）

13）把低于总平均成绩的男同学成绩提高5％。

UPDATESC

SETGrade=Grade*1.05

WHEREGrade<（SELECTAVG（Grade）FROMSC）AND

SnoIN（SELECTSnoFROMStudentWHERESsex='男'）

14）检索没有选修‘1’课程的学生学号和姓名

SELECTSno,Sname

FROMStudent

WHERENOTEXISTS

（SELECT*FROMSCWHERESno=Student.SnoANDCno=1）

15）检索至少有一门课程超过学生‘95001’一门成绩的学生学号

SELECTDISTINCTStudent.Sno

FROMStudent,SC

WHEREGrade>（SELECTMIN（Grade）FROMSCWHERESno='95001'）

ANDStudent.Sno=SC.Sno

16）向学生选修课程表中插入元组“学生95003选修课程1”。

INSERTINTOSC（Sno,Cno）

VALUES（'95003',1）

17）求出女同学的每一年龄组（超过10人）有多少人？

要求查询结果按人数升序排列，人数相同的按年龄降序排列。

SELECTCOUNT（Sage）'人数',Sage

FROMStudent

WHERESsex='女'

GROUPBYSage

ORDERBY'人数'ASC,SageDESC

18）定义视图S_AVG，其中包括学生学号以及每个学生选修课程的门数（要求成绩非空）和平均成绩。

CREATEVIEWS_AVG（学号,选课门数,平均成绩）

AS

SELECTSno,COUNT（Sno）,AVG（Grade）

FROMSC

GROUPBYSno

四、思考题

1、在创建基本表时是否可以缺省主码？

答:

可以

2、简述GROUPBY、COMPUTE、COMPUTEBY的区别？

答:

a）GROUPBY生成单个结果集。

每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。

选择列表只能包含分组依据列和聚合函数;

b）

COMPUTE生成多个结果集。

一类结果集包含每个组的明细行，其中包含选择列表中的表达式。

另一类结果