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

1、广东海洋大学计算机科学与技术数据库实验2GDOU-B-11-112广东海洋大学学生实验报告书（学生用表）实验名称实验2数据的查询、更新课程名称数据库系统原理课程号1620072学院(系)信息学院专业计算机科学与技术班级 1113学生姓名 学号实验地点科技楼实验日期04-12实验二 数据的查询、更新一、实验目的1、掌握用户自定义数据类型的方法2、掌握用T-SQL语句进行数据的插入、修改、删除的方法3、熟练掌握SELECT语句，能够运用该语句完成各种查询二、实验要求1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；2、能认真独立完成实训内容；3、

2、实验后做好实验总结，根据实验情况完成总结报告。三、实验内容1、用T-SQL语句，创建一用户自定义数据类型：名称为“char20”，数据类型为varchar，长度为20，允许为空。sp_addtype char20,varchar(20),null2、用T-SQL语句，建立一个“学生课程数据库”，在此基础上建立该数据库包含的学生表，课程表，学生选修表，并向各表插入如下相应的数据。(1) 建立“学生课程数据库”数剧库：CREATE DATABASE 学生课程数据库ON PRIMARY ( NAME=Student_dat, FILENAME=D:学生课程数据库.mdf, SIZE=10MB, MA

3、XSIZE=50MB, FILEGROWTH=10%)LOG ON( NAME=Student_log, FILENAME=D:学生课程数据库.ldf, SIZE=5MB, FILEGROWTH=10%)GO(2)创建学生表CREATE TABLE Student(Sno INT PRIMARY KEY ,Sname CHAR(10) ,Ssex CHAR(2) CHECK(Ssex=男 OR Ssex=女),Sage SMALLINT CHECK(Sage BETWEEN 15 AND 30),Sdept CHAR(20) ) ;插入数据：INSERT INTO Student( Sno ,

4、Sname,Ssex,Sage,Sdept )VALUES (95001,李敏勇, 男, 20, CS)INSERT INTO Student( Sno ,Sname,Ssex,Sage,Sdept )VALUES (95002, 刘晨, 女, 19, IS)INSERT INTO Student( Sno ,Sname,Ssex,Sage,Sdept )VALUES (95003, 王敏, 女, 18, MA)INSERT INTO Student( Sno ,Sname,Ssex,Sage,Sdept )VALUES (95004, 张立, 男, 18 , IS );(2)创建课程表CRE

5、ATE TABLE Course(Cno CHAR(4) PRIMARY KEY ,Cname CHAR(10) NOT NULL,Cpno CHAR(4),Credit INT CHECK(Credit=0 AND Credit=0 AND Grade=100 ),PRIMARY KEY(Sno,Cno);插入数据：INSERT INTO SC ( Sno, Cno, Grade)VALUES (95001,1,92)INSERT INTO SC ( Sno, Cno, Grade)VALUES (95001,2,85)INSERT INTO SC ( Sno, Cno, Grade)VAL

6、UES (95001,3,88)INSERT INTO SC ( Sno, Cno, Grade)VALUES (95002,2,90) INSERT INTO SC ( Sno, Cno, Grade)VALUES (95003,2,55) INSERT INTO SC ( Sno, Cno, Grade)VALUES (95004,2,70)3、用T-SQL语句，修改上面所建学生课程数据库中数据:1) 向学生表:Student中加入一条记录：(95030,谢非,男,22,CS)并保存INSERT INTO Student( Sno ,Sname,Ssex,Sage,Sdept )VALUE

7、S (95030,谢非,男,22,CS)2) 将李敏勇的数据库的成绩改为98分UPDATE SC SET Grade=98WHERE SC.Sno IN (SELECT SC.Sno FROM Student,SCWHERE Student.Sno = SC.Sno AND Student.Sname =李敏勇) AND SC.Cno IN (SELECT SC.Cno FROM SC,CourseWHERE Course.Cno = SC.Cno AND Course.Cname=数据库)3) 删除学生表Student中谢非的记录并保存DELETE StudentWHERE Student.

8、Sname =谢非4) 能不能从Student表中删除李敏勇学生的记录,为什么?能不能删除王敏, 张立两个学生的记录?DELETE StudentWHERE Student.Sname =李敏勇不能删除李敏勇学生的记录：“DELETE 语句与REFERENCE 约束FK_SC_Sno_07020F21冲突。该冲突发生于数据库学生课程数据库，表dbo.SC, column Sno。”；也不能删除王敏, 张立两个学生的记录。4、用T-SQL语句，完成下面简单的查询1）查询全体学生的学号、姓名及年龄.SELECT Sno, Sname ,SageFROM Student2）查询全体学生的姓名, 年龄

9、及所在系（要用小写字母表示系名，并用“系名”来表示列名）。SELECT Sname, Sage,LOWER(Sdept) 系名FROM Student3）查询选修了课程的学生学号SELECT SnoFROM SC4）查询信息系全体学生的姓名SELECT Sname FROM StudentWHERE Sdept=IS5）查询所有年龄在20岁以下的学生姓名及其年龄SELECT Sname, SageFROM StudentWHERE Sage206）查询年龄在20到18间的学生的姓名,系别及年龄SELECT Sname, Sdept, SageFROM StudentWHERE Sage BET

10、WEEN 18 AND 207）查询年龄不在23到19间的学生的姓名,系别及年龄 SELECT Sname, Sdept, SageFROM StudentWHERE Sage238）查询不是信息系(IS)和计算机系(CS)学生的姓名和性别SELECT Sname, Ssex,SdeptFROM StudentWHERE NOT Sdept=IS AND NOT Sdept=CS9）查询所有姓刘的学生的姓名,学号和性别SELECT Sname,Sno, SsexFROM StudentWHERE Sname LIKE 刘%10）查询姓“张”且名为一个汉字的学生的姓名SELECT SnameFR

11、OM StudentWHERE Sname LIKE 张_11）查询名字中第2个字为”敏”字的学生姓名和学号SELECT Sname,SnoFROM StudentWHERE Sname LIKE _敏%12）查询所有不姓刘的学生姓名SELECT SnameFROM StudentWHERE Sname NOT LIKE 刘%13）查询全体学生情况,结果按所在系升序排列,同一系中的学生按年龄降序SELECT Sno, Sname, Ssex, Sage, SdeptFROM StudentORDER BY Sdept, Sage DESC14）查询学生表中所有学生信息，要求只显示前10行数据S

12、ELECT TOP 10 PERCENT *FROM Student 15）按成绩降序查询输出SC表中成绩大于等于70分的所有学生的学号，要求只显示前2行数据,若第3行后的数据也等于70分也要显示。SELECT TOP 2 WITH TIES Sno,Grade FROM SCWHERE Grade=70 ORDER BY Grade DESC16)查询每个学生的学号、课程号及分数，同时统计每个学生的总分SELECT Sno, Cno, GradeFROM SCORDER BY SnoCOMPUTE SUM (Grade) BY Sno17)查询每个学生的各科分数、最高分、最低分、总分、平均分

13、SELECT Sno, Cno, GradeFROM SCORDER BY SnoCOMPUTE MAX (Grade) ,MIN (Grade) ,SUM (Grade) ,AVG (Grade) BY Sno5、用T-SQL语句完成下面的查询1)查询学生的总人数SELECT COUNT(*) 总人数FROM Student2)查询选修了课程的学生人数SELECT COUNT(*)FROM Student ,SCWHERE Student.Sno = SC.Sno3)计算选2号课程的学生平均成绩SELECT AVG(Grade)FROM SCWHERE Cno=24)查询选修2号课程的学生最

14、高分数SELECT MAX(Grade)FROM SCWHERE Cno=25)求各个课程号及相应的选课人数SELECT Cno,COUNT(Sno) 选课人数FROM SCGROUP BY Cno6)查询选修了2门以上的课程的学生学号SELECT SnoFROM SCGROUP BY SnoHAVING COUNT( *) 27)查询每个学生及其选修课程的情况SELECT Student.*,Course.*FROM Student,SC,CourseWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno8)查询每一门课的间接先修课(即先修课的先

15、修课)SELECT Course.Cname,Course_2.Cname 间接先修课FROM Course INNER JOIN Course AS Course_1 ON Course.Cno = Course_1.Cpno INNER JOIN Course AS Course_2 ON Course_1.Cno = Course_2.Cpno9)查询选修2号课程且成绩在90分以上（包括90分）的所有学生。SELECT SnameFROM StudentWHERE Sno IN ( SELECT Sno FROM SC WHERE Cno = 2 AND Grade=90)6. 用T-S

16、QL语句完成下面的查询 1)查询与“刘晨”在同一个系学习的学生SELECT Sno, Sname, Ssex, Sage, SdeptFROM StudentWHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname=刘晨) AND Sname !=刘晨2)查询选修了课程名为“数学”的学生学号和姓名SELECT Student.Sno, Student.SnameFROM Student,SC,Course WHERE Course.Cname = 数学 AND Student.Sno = SC.Sno AND SC.Cno = Course.

17、Cno3)查询其它系中比信息系中某一学生年龄小的学生姓名和年龄SELECT Sname,SageFROM Student WHERE Sdept !=IS AND Sage ANY (SELECT Sage FROM Student WHERE Sdept =IS )4)查询其它系中比计算机系所有学生年龄都小的学生姓名及年龄SELECT Sname,SageFROM Student WHERE Sdept !=IS AND Sage (SELECT Sno FROM Student WHERE Sname=刘%) AND Sage(SELECT AVG(Sage) FROM Student W

18、HERE Ssex=女) AND Ssex=男9)求年龄大于所有女同学年龄的男学生姓名和年龄。SELECT Sname, Sage FROM StudentWHERE Sage(SELECT MAX(Sage) FROM Student WHERE Ssex=女) AND Ssex=男10)检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表S（SNO,SNAME,SEX）。CREATE TABLE S(SNO INT PRIMARY KEY ,SNAME CHAR(10) ,SEX CHAR(2) CHECK(SEX=男 OR SEX=女)INSE

19、RT INTO S(SNO,SNAME,SEX)SELECT DISTINCT Student.Sno, Sname, Ssex FROM Student,SC WHERE Student.Sno = SC.Sno AND Grade8011)把选课数学课不及格的成绩全改为空值。UPDATE SCSET Grade=NULLWHERE 数学 IN ( SELECT Cname FROM Course,SC WHERE SC.Cno = Course.Cno AND Grade =60)12)把王同学的选课信息全部删去。DELETE SC WHERE Sno IN (SELECT SC.Sno

20、FROM Student,SC WHERE Sname LIKE 王% AND Student.Sno = SC.Sno)13)把低于总平均成绩的男同学成绩提高5。UPDATE SC SET Grade=Grade*1.05WHERE Grade (SELECT MIN(Grade) FROM SC WHERE Sno=95001)AND Student.Sno = SC.Sno16)向学生选修课程表中插入元组“学生95003选修课程1”。INSERT INTO SC(Sno,Cno)VALUES( 95003,1)17)求出女同学的每一年龄组（超过10人）有多少人？要求查询结果按人数升序排列

21、，人数相同的按年龄降序排列。SELECT COUNT(Sage)人数 , SageFROM StudentWHERE Ssex = 女GROUP BY SageORDER BY 人数 ASC,Sage DESC 18)定义视图S_AVG，其中包括学生学号以及每个学生选修课程的门数（要求成绩非空）和平均成绩。CREATE VIEW S_AVG (学号,选课门数,平均成绩)AS SELECT Sno, COUNT(Sno), AVG(Grade)FROM SCGROUP BY Sno四、思考题1、在创建基本表时是否可以缺省主码？答:可以2、简述GROUP BY 、COMPUTE 、COMPUTE BY的区别？答:a) GROUP BY 生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。选择列表只能包含分组依据列和聚合函数;b) COMPUTE 生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表达式。另一类结果