实验二 数据的查询、更新

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

2、char(20)',null提示：sp_addtype typename=用户自定义类型的名字 , phystype=系统类型名 , nulltype= ' not null | null ' , owner= '拥有该类型的用户名 ' 例：自定义一个名为address的类型，其所属系统类型为varchar，长度为80，不能为空。sp_addtype address,' varchar(80)', ' not null '2、用T-SQL语句，建立一个“学生课程数据库”，在此基础上建立该数据库包含的学生表，课程表，学生选修

3、表，并向各表插入如下相应的数据。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('男',&#

4、39;女'), 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,

5、'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 k

6、ey, 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,'高升')i

7、nsert 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;SnoCnoGrade950011929500128595001388950022909500325595004270cr

8、eate 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)

9、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 s

10、c 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

11、）查询全体学生的学号、姓名及年龄.use 学生课程数据库select *from student2）查询全体学生的姓名, 年龄及所在系（要用小写字母表示系名，并用“系名”来表示列名）。select sname,sage, lower(sdept) '系名'from student3）查询选修了课程的学生学号select distinct sno from sc4）查询信息系全体学生的姓名select snamefrom studentwhere sdept='IS'5）查询所有年龄在20岁以下的学生姓名及其年龄select sname,sagefrom stud

12、entwhere sage<206）查询年龄在20到18间的学生的姓名,系别及年龄select sname,sdept,sagefrom studentwhere sage between 18 and 207）查询年龄不在23到19间的学生的姓名,系别及年龄 select sname,sdept,sagefrom studentwhere sage not between 19 and 238）查询不是信息系(IS)和计算机系(CS)学生的姓名和性别select sname,ssexfrom studentwhere sdept not in('IS','CS&

13、#39;)9）查询所有姓刘的学生的姓名,学号和性别select sname,sno,ssexfrom studentwhere sname like '张%'10）查询姓“张”且名为一个汉字的学生的姓名select sname,sno,ssexfrom studentwhere sname like '张_'11）查询名字中第2个字为”敏”字的学生姓名和学号select sname,snofrom studentwhere sname like '_敏%'12）查询所有不姓刘的学生姓名select snamefrom studentwhere s

14、name not like '刘%'13）查询全体学生情况,结果按所在系升序排列,同一系中的学生按年龄降序select *from studentorder by sdept asc,sage desc14）查询学生表中所有学生信息，要求只显示前10行数据select top 10 percent *from student15）按成绩降序查询输出SC表中成绩大于等于70分的所有学生的学号，要求只显示前2行数据,若第3行后的数据也等于70分也要显示。select top 2 with ties snofrom scwhere grade>=70order by grade

15、 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)查询选

16、修了课程的学生人数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

17、 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.sn

18、o 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

19、 cno from course where cname='数学' )3)查询其它系中比信息系中某一学生年龄小的学生姓名和年龄select sname,sagefrom studentwhere sdept<>'IS' and sage< any(select sagefrom student where sdept='IS')4)查询其它系中比计算机系所有学生年龄都小的学生姓名及年龄select sname,sagefrom studentwhere sdept<>'IS' and sage<

20、 all(select sagefrom student where sdept='IS')5)查询所有选修了2号课程的学生姓名select snamefrom student,scwhere student.sno=sc.sno and cno=26)查询没有选修3号课程的学生姓名select distinct snamefrom student,scwhere student.sno=sc.sno and cno!=37、用T-SQL语句完成下面的复杂查询1)至少选修刘老师所授课程中一门课程的女学生姓名select snamefrom studentwhere exists

21、 (select sno from sc where exists(select cno from course where teacher like '刘%' and ssex='女')2)检索王同学不学的课程的课程号select cnofrom coursewhere cno not in(select o from sc,student where sname like '王%' and student.sno=sc.sno and o=o )3)检索全部学生都选修的课程的课程号与课程名。select cno,cnamefrom cours

22、ewhere not exists(select * from student where not exists(select * from sc where student.sno=sc.sno and o=o )4)检索选修课程包含刘老师所授课的学生学号。select distinct snofrom scwhere exists(select * from course where o=o and teacher like '刘%')5)求选修课程号为2的学生的平均年龄。select AVG(sage) '平均年龄'from student,scwhere

23、 student.sno=sc.sno and o=26)求刘老师所授课程的每门课程的学生平均成绩。select teacher,cname,AVG(grade)'平均分'from sc,course,studentwhere o=o and student.sno=sc.snoand course.teacher like'刘%'group by teacher,o,cname7)检索学号比刘同学大，而年龄比他小的学生姓名。select snamefrom studentwhere sno >(select sno from student where

24、sname like'刘%')and sage<(select sage from student where sname like'刘%')8)求年龄大于女同学平均年龄的男同学姓名和年龄。select sname,sagefrom studentwhere sage>(select avg(sage)from studentwhere ssex='女')and ssex='男'9)求年龄大于所有女同学年龄的男学生姓名和年龄。select sname,sagefrom studentwhere ssex='男&

25、#39; and sage>all(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

26、 from sc where grade<60) 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(selec

27、t 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.sno<>95001and sc.sn