实验二数据的查询更新.docx

1、实验二数据的查询更新实验二 数据的查询、更新 一、实验目的1、掌握用户自定义数据类型的方法2、掌握用T-SQL语句进行数据的插入、修改、删除的方法3、熟练掌握SELECT语句，能够运用该语句完成各种查询二、实验要求1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；2、能认真独立完成实训内容；3、实验后做好实验总结，根据实验情况完成总结报告。三、实验内容1、用T-SQL语句，创建一用户自定义数据类型：名称为“char20”，数据类型为varchar，长度为20，允许为空。sp_addtype char20, varchar(20),null提示

2、：sp_addtype typename=用户自定义类型的名字 , phystype=系统类型名 , nulltype= not null | null , owner= 拥有该类型的用户名 例：自定义一个名为address的类型，其所属系统类型为varchar，长度为80，不能为空。sp_addtype address, varchar(80), not null 2、用T-SQL语句，建立一个“学生课程数据库”，在此基础上建立该数据库包含的学生表，课程表，学生选修表，并向各表插入如下相应的数据。create database 学生课程数据库学生表:Student(Sno,Sname,Sse

3、x,Sage,Sdept) 其中Sno 为主键、Ssex取值为男或女、Sage在15到30之间:SnoSnameSsexSageSdept95001李敏勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男18IScreate table student(sno int primary key, sname char(10), ssex char(2) constraint ssex_ch check(ssex in(男,女), sage int constraint sage_ch check(sage=15 and sage=30), sdept char(10) )

4、 insert into studentvalues(95001,李敏勇,男,20,CS)insert into studentvalues(95002,刘晨,女,19,IS)insert into studentvalues(95003,王敏,女,18,MA)insert into studentvalues(95004,张立,男,18,IS)课程表:Course(Cno,Cname,Cpno,Credeit,remarks) 其中Cno为主键、Teacher的类型为char20即为用户定义的数据类型;CnoCnameCpnoCredit Teacher1数据库54王芳2数学NULL2刘新3

5、信息系统14刘新4操作系统63高升5数据结构74宋明6数据处理NULL2张彬7Pascal语言64李磊create table course(cno int primary key, cname char(15), cpno int null, credit int, teacher char20 ) insert into coursevalues(1,数据库,5,4,王芳)insert into coursevalues(2,数学,NULL,2,刘新)insert into coursevalues(3,信息系统,1,4,刘新)insert into coursevalues(4,操作系统

6、,6,3,高升)insert into coursevalues(5,数据结构,7,4,宋明)insert into coursevalues(6,数据处理,NULL,2,张彬)insert into coursevalues(7,PASCAL语言,6,4,李磊)学生选修表:SC(Sno,Cno,Grade) 其中Sno,Cno为主键同时又为外键、Grade值在0到100;SnoCnoGrade950011929500128595001388950022909500325595004270create table sc(sno int, cno int, grade int constrain

7、t grade_ch check(grade between 0 and 100), primary key(sno,cno), constraint fk_sno foreign key (sno) references student(sno), constraint fk_cno foreign key (cno) references course(cno), ) insert into scvalues(95001,1,92)insert into scvalues(95001,2,85)insert into scvalues(95001,3,88)insert into scva

8、lues(95002,2,90)insert into scvalues(95003,2,55)insert into scvalues(95004,2,70)3、用T-SQL语句，修改上面所建学生课程数据库中数据:1) 向学生表:Student中加入一条记录：(95030,谢非,男,22,CS)并保存insert into studentvalues(95030,谢非,男,22,CS)2) 将李敏勇的数据库的成绩改为98分update sc set grade=98where o=(select o from course where ame=数据库 and sno=(select sno

9、from student where sname=李敏勇 ) )3) 删除学生表Student中谢非的记录并保存delete from studentwhere sname=谢非4) 能不能从Student表中删除李敏勇学生的记录,为什么?能不能删除王敏, 张立两个学生的记录?不能， sc表中列sno是外码，参照student表的sno列。4、用T-SQL语句，完成下面简单的查询1）查询全体学生的学号、姓名及年龄.use 学生课程数据库select *from student2）查询全体学生的姓名, 年龄及所在系（要用小写字母表示系名，并用“系名”来表示列名）。select sname,sag

10、e, lower(sdept) 系名from student3）查询选修了课程的学生学号select distinct sno from sc4）查询信息系全体学生的姓名select snamefrom studentwhere sdept=IS5）查询所有年龄在20岁以下的学生姓名及其年龄select sname,sagefrom studentwhere sage=70order by grade desc16)查询每个学生的学号、课程号及分数，同时统计每个学生的总分select sno,cno,gradefrom scorder by snocompute sum(grade) by s

11、no17)查询每个学生的各科分数、最高分、最低分、总分、平均分select sno,cno,gradefrom scorder by snocompute max(grade),min(grade),sum(grade),avg(grade) by sno5、用T-SQL语句完成下面的查询1)查询学生的总人数select COUNT(sno) 人数from student2)查询选修了课程的学生人数select COUNT(distinct sno) 选课人数from sc3)计算选2号课程的学生平均成绩select AVG(grade)平均分from scwhere cno=24)查询选修2

12、号课程的学生最高分数select MAX(grade) 最高分from scwhere cno=25)求各个课程号及相应的选课人数select cno,COUNT(sno) 人数from scgroup by cno6)查询选修了2门以上的课程的学生学号select snofrom scgroup by sno having (COUNT(cno)2)7)查询每个学生及其选修课程的情况select student.sno,o,cnamefrom student,sc,coursewhere student.sno=sc.sno and o=o8)查询每一门课的间接先修课(即先修课的先修课)se

13、lect o,c2.cpnofrom course c1,course c2where o=o9)查询选修2号课程且成绩在90分以上（包括90分）的所有学生。select student.sno,snamefrom studentwhere student.sno in(select sc.sno from sc where cno=2 and grade=90)6. 用T-SQL语句完成下面的查询 1)查询与“刘晨”在同一个系学习的学生select sno,snamefrom studentwhere sname刘晨 and sdept=(select sdept from student

14、where sname=刘晨)2)查询选修了课程名为“数学”的学生学号和姓名select sno,snamefrom studentwhere sno in(select sno from sc where cno =(select cno from course where cname=数学 )3)查询其它系中比信息系中某一学生年龄小的学生姓名和年龄select sname,sagefrom studentwhere sdeptIS and sage any(select sage from student where sdept=IS )4)查询其它系中比计算机系所有学生年龄都小的学生姓名

15、及年龄select sname,sagefrom studentwhere sdeptIS and sage(select sno from student where sname like刘%)and sage(select avg(sage) from student where ssex=女)and ssex=男 9)求年龄大于所有女同学年龄的男学生姓名和年龄。select sname,sagefrom studentwhere ssex=男 and sageall(select sage from student where ssex=女)10)检索每一门课程成绩都大于等于80分的学生

16、学号、姓名和性别，并把检索到的值送往另一个已存在的基本表S（SNO,SNAME,SEX）。select sno,sname,ssexinto sfrom studentwhere sno in(select sno from sc where grade=80)11)把选课数学课不及格的成绩全改为空值。update scset grade=where sno in(select sno from sc where grade60) and cno=(select cno from course where cname=数学)12)把王同学的选课信息全部删去。delete from scwher

17、e sno = (select sno from student where sname like 王%)13)把低于总平均成绩的男同学成绩提高5。update scset grade=grade*1.05where grade (select AVG(grade) from sc) and cno in(select o from student,sc where ssex = 男 and student.sno = sc.sno)14)检索没有选修1课程的学生学号和姓名select sno,snamefrom studentwhere sno not in(select sc.sno fr

18、om sc,student where cno=1 and student.sno=sc.sno)15)检索至少有一门课程超过学生95001一门成绩的学生学号select distinct sc.snofrom sc,studentwhere sc.sno95001and sc.sno=student.snoand gradeany(select grade from sc where sno=95001)16)向学生选修课程表中插入元组“学生95003选修课程1”。insert into sc(sno,cno)values(95003,1)17)求出女同学的每一年龄组（超过10人）有多少人？

19、要求查询结果按人数升序排列，人数相同的按年龄降序排列。select sage,COUNT(sno) 人数from studentwhere ssex=女group by sage having count(*)10order by count(sno) asc,sage desc18)定义视图S_AVG，其中包括学生学号以及每个学生选修课程的门数（要求成绩非空）和平均成绩。create view s_avg(学号,选修门数,平均成绩)asselect sno,COUNT(cno),AVG(grade)from scwhere grade is not nullgroup by sno四、思考题

20、1、在创建基本表时是否可以缺省主码？ 可以2、简述GROUP BY 、COMPUTE 、COMPUTE BY的区别？GROUPBY生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。选择列表只能包含分组依据列和聚合函数。COMPUTE生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表达式。另一类结果集包含组的子聚合，或SELECT语句的总聚合。选择列表可包含除分组依据列或聚合函数之外的其它表达式。聚合函数在COMPUTE子句中指定，而不是在选择列表中。GROUPBY子句返回的结果集中只有合计数据，而没有原始的详细记录。COMPTE生成合计作为附加的汇总列出现在结果集的最后。当与BY一起使用时，COMPUTE子句在结果集内生成控制中断和分类汇总。