实验二数据的查询更新.docx
《实验二数据的查询更新.docx》由会员分享，可在线阅读，更多相关《实验二数据的查询更新.docx（33页珍藏版）》请在冰豆网上搜索。
实验二数据的查询更新
实验二数据的查询、更新
一、实验目的
1、掌握用户自定义数据类型的方法
2、掌握用T-SQL语句进行数据的插入、修改、删除的方法
3、熟练掌握SELECT语句，能够运用该语句完成各种查询
二、实验要求
1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；
2、能认真独立完成实训内容；
3、实验后做好实验总结，根据实验情况完成总结报告。
三、实验内容
1、用T-SQL语句，创建一用户自定义数据类型：
名称为“char20”，数据类型为varchar，长度为20，允许为空。
sp_addtypechar20,'varchar（20）',null
提示：
sp_addtype[@typename=]用户自定义类型的名字
[,@phystype=]系统类型名
[,[@nulltype=]'notnull|null'][,[@owner=]'拥有该类型的用户名']
例：
自定义一个名为address的类型，其所属系统类型为varchar，长度为80，不能为空。
sp_addtypeaddress,'varchar（80）','notnull'
2、用T-SQL语句，建立一个“学生课程数据库”，在此基础上建立该数据库包含的学生表，课程表，学生选修表，并向各表插入如下相应的数据。
createdatabase学生课程数据库
学生表:
Student（Sno,Sname,Ssex,Sage,Sdept）其中Sno为主键、Ssex取值为男或女、Sage在15到30之间:
Sno
Sname
Ssex
Sage
Sdept
95001
李敏勇
男
20
CS
95002
刘晨
女
19
IS
95003
王敏
女
18
MA
95004
张立
男
18
IS
createtablestudent（snointprimarykey,
snamechar（10）,
ssexchar
（2）constraintssex_chcheck（ssexin（'男','女'））,
sageintconstraintsage_chcheck（sage>=15andsage<=30）,
sdeptchar（10）
）
insertintostudent
values（95001,'李敏勇','男',20,'CS'）
insertintostudent
values（95002,'刘晨','女',19,'IS'）
insertintostudent
values（95003,'王敏','女',18,'MA'）
insertintostudent
values（95004,'张立','男',18,'IS'）
课程表:
Course（Cno,Cname,Cpno,Credeit,remarks）其中Cno为主键、Teacher的类型为char20即为用户定义的数据类型;
Cno
Cname
Cpno
Credit
Teacher
1
数据库
5
4
王芳
2
数学
NULL
2
刘新
3
信息系统
1
4
刘新
4
操作系统
6
3
高升
5
数据结构
7
4
宋明
6
数据处理
NULL
2
张彬
7
Pascal语言
6
4
李磊
createtablecourse（cnointprimarykey,
cnamechar（15）,
cpnointnull,
creditint,
teacherchar20
）
insertintocourse
values（1,'数据库',5,4,'王芳'）
insertintocourse
values（2,'数学',NULL,2,'刘新'）
insertintocourse
values（3,'信息系统',1,4,'刘新'）
insertintocourse
values（4,'操作系统',6,3,'高升'）
insertintocourse
values（5,'数据结构',7,4,'宋明'）
insertintocourse
values（6,'数据处理',NULL,2,'张彬'）
insertintocourse
values（7,'PASCAL语言',6,4,'李磊'）
学生选修表:
SC（Sno,Cno,Grade）其中Sno,Cno为主键同时又为外键、Grade值在0到100;
Sno
Cno
Grade
95001
1
92
95001
2
85
95001
3
88
95002
2
90
95003
2
55
95004
2
70
createtablesc（snoint,
cnoint,
gradeintconstraintgrade_chcheck（gradebetween0and100）,
primarykey（sno,cno）,
constraintfk_snoforeignkey（sno）referencesstudent（sno）,
constraintfk_cnoforeignkey（cno）referencescourse（cno）,
）
insertintosc
values（95001,1,92）
insertintosc
values（95001,2,85）
insertintosc
values（95001,3,88）
insertintosc
values（95002,2,90）
insertintosc
values（95003,2,55）
insertintosc
values（95004,2,70）
3、用T-SQL语句，修改上面所建学生课程数据库中数据:
1）向学生表:
Student中加入一条记录：
（95030,谢非,男,22,CS）并保存
insertintostudent
values（95030,'谢非','男',22,'CS'）
2）将李敏勇的数据库的成绩改为98分
updatesc
setgrade=98
whereo=（selecto
fromcourse
whereame='数据库'
andsno=（selectsno
fromstudent
wheresname='李敏勇'
）
）
3）删除学生表Student中谢非的记录并保存
delete
fromstudent
wheresname='谢非'
4）能不能从Student表中删除李敏勇学生的记录,为什么?
能不能删除王敏,张立两个学生的记录?
不能，sc表中列sno是外码，参照student表的sno列。
4、用T-SQL语句，完成下面简单的查询
1）查询全体学生的学号、姓名及年龄.
use学生课程数据库
select*
fromstudent
2）查询全体学生的姓名,年龄及所在系（要用小写字母表示系名，并用“系名”来表示
列名）。
selectsname,sage,lower（sdept）'系名'
fromstudent
3）查询选修了课程的学生学号
selectdistinctsno
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
wheresagenotbetween19and23
8）查询不是信息系（IS）和计算机系（CS）学生的姓名和性别
selectsname,ssex
fromstudent
wheresdeptnotin（'IS','CS'）
9）查询所有姓刘的学生的姓名,学号和性别
selectsname,sno,ssex
fromstudent
wheresnamelike'张%'
10）查询姓“张”且名为一个汉字的学生的姓名
selectsname,sno,ssex
fromstudent
wheresnamelike'张_'
11）查询名字中第2个字为”敏”字的学生姓名和学号
selectsname,sno
fromstudent
wheresnamelike'_敏%'
12）查询所有不姓刘的学生姓名
selectsname
fromstudent
wheresnamenotlike'刘%'
13）查询全体学生情况,结果按所在系升序排列,同一系中的学生按年龄降序
select*
fromstudent
orderbysdeptasc,sagedesc
14）查询学生表中所有学生信息，要求只显示前10％行数据
selecttop10percent*
fromstudent
15）按成绩降序查询输出SC表中成绩大于等于70分的所有学生的学号，要求只显示前2
行数据,若第3行后的数据也等于70分也要显示。
selecttop2withtiessno
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（sno）'人数'
fromstudent
2）查询选修了课程的学生人数
selectCOUNT（distinctsno）'选课人数'
fromsc
3）计算选2号课程的学生平均成绩
selectAVG（grade）'平均分'
fromsc
wherecno=2
4）查询选修2号课程的学生最高分数
selectMAX（grade）'最高分'
fromsc
wherecno=2
5）求各个课程号及相应的选课人数
selectcno,COUNT（sno）'人数'
fromsc
groupbycno
6）查询选修了2门以上的课程的学生学号
selectsno
fromsc
groupbysnohaving（COUNT（cno）>2）
7）查询每个学生及其选修课程的情况
selectstudent.sno,o,cname
fromstudent,sc,course
wherestudent.sno=sc.snoando=o
8）查询每一门课的间接先修课（即先修课的先修课）
selecto,c2.cpno
fromcoursec1,coursec2
whereo=o
9）查询选修2号课程且成绩在90分以上（包括90分）的所有学生。
selectstudent.sno,sname
fromstudent
wherestudent.snoin（selectsc.sno
fromsc
wherecno=2andgrade>=90）
6.用T-SQL语句完成下面的查询
1）查询与“刘晨”在同一个系学习的学生
selectsno,sname
fromstudent
wheresname<>'刘晨'andsdept=（selectsdept
fromstudent
wheresname='刘晨'）
2）查询选修了课程名为“数学”的学生学号和姓名
selectsno,sname
fromstudent
wheresnoin（selectsno
fromsc
wherecno=（selectcno
fromcourse
wherecname='数学'））
3）查询其它系中比信息系中某一学生年龄小的学生姓名和年龄
selectsname,sage
fromstudent
wheresdept<>'IS'andsagefromstudent
wheresdept='IS'
）
4）查询其它系中比计算机系所有学生年龄都小的学生姓名及年龄
selectsname,sage
fromstudent
wheresdept<>'IS'andsagefromstudent
wheresdept='IS'
）
5）查询所有选修了2号课程的学生姓名
selectsname
fromstudent,sc
wherestudent.sno=sc.snoandcno=2
6）查询没有选修3号课程的学生姓名
selectdistinctsname
fromstudent,sc
wherestudent.sno=sc.snoandcno!
=3
7、用T-SQL语句完成下面的复杂查询
1）至少选修刘老师所授课程中一门课程的女学生姓名
selectsname
fromstudent
whereexists（selectsno
fromsc
whereexists（selectcno
fromcourse
whereteacherlike'刘%'andssex='女'））
2）检索王同学不学的课程的课程号
selectcno
fromcourse
wherecnonotin（selecto
fromsc,student
wheresnamelike'王%'
andstudent.sno=sc.sno
ando=o
）
3）检索全部学生都选修的课程的课程号与课程名。
selectcno,cname
fromcourse
wherenotexists（select*
fromstudent
wherenotexists（select*
fromsc
wherestudent.sno=sc.sno
ando=o））
4）检索选修课程包含刘老师所授课的学生学号。
selectdistinctsno
fromsc
whereexists（select*
fromcourse
whereo=o
andteacherlike'刘%'）
5）求选修课程号为2的学生的平均年龄。
selectAVG（sage）'平均年龄'
fromstudent,sc
wherestudent.sno=sc.snoando=2
6）求刘老师所授课程的每门课程的学生平均成绩。
selectteacher,cname,AVG（grade）'平均分'
fromsc,course,student
whereo=o
andstudent.sno=sc.sno
andcourse.teacherlike'刘%'
groupbyteacher,o,cname
7）检索学号比刘同学大，而年龄比他小的学生姓名。
selectsname
fromstudent
wheresno>（selectsno
fromstudent
wheresnamelike'刘%'）
andsage<（selectsage
fromstudent
wheresnamelike'刘%'）
8）求年龄大于女同学平均年龄的男同学姓名和年龄。
selectsname,sage
fromstudent
wheresage>（selectavg（sage）
fromstudent
wheressex='女'）
andssex='男'
9）求年龄大于所有女同学年龄的男学生姓名和年龄。
selectsname,sage
fromstudent
wheressex='男'
andsage>all（selectsage
fromstudent
wheressex='女'）
10）检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表S（SNO,SNAME,SEX）。
selectsno,sname,ssex
intos
fromstudent
wheresnoin（selectsno
fromsc
wheregrade>=80）
11）把选课数学课不及格的成绩全改为空值。
updatesc
setgrade=''
wheresnoin（selectsno
fromsc
wheregrade<60）
andcno=（selectcno
fromcourse
wherecname='数学'）
12）把王同学的选课信息全部删去。
delete
fromsc
wheresno=（selectsno
fromstudent
wheresnamelike'王%'）
13）把低于总平均成绩的男同学成绩提高5％。
updatesc
setgrade=grade*1.05
wheregrade<（selectAVG（grade）
fromsc）
andcnoin（selecto
fromstudent,sc
wheressex='男'
andstudent.sno=sc.sno）
14）检索没有选修‘1’课程的学生学号和姓名
selectsno,sname
fromstudent
wheresnonotin（selectsc.sno
fromsc,student
wherecno=1andstudent.sno=sc.sno）
15）检索至少有一门课程超过学生‘95001’一门成绩的学生学号
selectdistinctsc.sno
fromsc,student
wheresc.sno<>95001
andsc.sno=student.sno
andgrade>any（selectgrade
fromsc
wheresno=95001）
16）向学生选修课程表中插入元组“学生95003选修课程1”。
insertintosc（sno,cno）
values（95003,1）
17）求出女同学的每一年龄组（超过10人）有多少人？
要求查询结果按人数升序排列，人数相同的按年龄降序排列。
selectsage,COUNT（sno）'人数'
fromstudent
wheressex='女'
groupbysagehavingcount（*）>10
orderbycount（sno）asc,sagedesc
18）定义视图S_AVG，其中包括学生学号以及每个学生选修课程的门数（要求成绩非空）和平均成绩。
createviews_avg（学号,选修门数,平均成绩）
as
selectsno,COUNT（cno）,AVG（grade）
fromsc
wheregradeisnotnull
groupbysno
四、思考题
1、在创建基本表时是否可以缺省主码？
可以
2、简述GROUPBY、COMPUTE、COMPUTEBY的区别？
GROUP BY 生成单个结果集。
每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。
选择列表只能包含分组依据列和聚合函数。
COMPUTE 生成多个结果集。
一类结果集包含每个组的明细行，其中包含选择列表中的表达式。
另一类结果集包含组的子聚合，或 SELECT 语句的总聚合。
选择列表可包含除分组依据列或聚合函数之外的其它表达式。
聚合函数在 COMPUTE 子句中指定，而不是在选择列表中。
GROUP BY子句返回的结果集中只有合计数据，而没有原始的详细记录。
COMPTE生成合计作为附加的汇总列出现在结果集的最后。
当与BY一起使用时，COMPUTE 子句在结果集内生成控制中断和分类汇总。
升级会员 