实验二 数据的查询、更新.doc

_实验二 数据的查询、更新 一、实验目的1、掌握用户自定义数据类型的方法2、掌握用T-SQL语句进行数据的插入、修改、删除的方法3、熟练掌握SELECT语句，能够运用该语句完成各种查询二、实验要求1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；2、能认真独立完成实训内容；3、实验后做好实验总结，根据实验情况完成总结报告。三、实验内容1、用T-SQL语句，创建一用户自定义数据类型：名称为“char20”，数据类型为varchar，长度为20，允许为空。sp_addtype char20, varchar(20),null提示：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,Ssex,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) ) 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信息系统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,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 constraint 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 scvalues(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 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,sage, 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 sno17)查询每个学生的各科分数、最高分、最低分、总分、平均分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号课程的学生最高分数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)查询每一门课的间接先修课(即先修课的先修课)select 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 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 sagefrom student where sdept=IS)4)查询其它系中比计算机系所有学生年龄都小的学生姓名及年龄select sname,sagefrom studentwhere sdeptIS and sage(select sno from student where sname like刘%)and sage(select avg(sage)from studentwhere ssex=女)and ssex=男9)求年龄大于所有女同学年龄的男学生姓名和年龄。select sname,sagefrom studentwhere ssex=男 and sageall(select sage from student where ssex=女)10)检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表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 scwhere 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 from 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人）有多少人？要求查询结果按人数升序排列，人数相同的按年龄降序排列。select sage,COUNT(sno) 人数from studentwhere sse