SQL语言实验报告.docx
《SQL语言实验报告.docx》由会员分享,可在线阅读,更多相关《SQL语言实验报告.docx(17页珍藏版)》请在冰豆网上搜索。
SQL语言实验报告
试验五SQL语言
一、目与要求
1.掌握SQL语言查询功效;
2.掌握SQL语言数据操作功效;
3.掌握对象资源管理器建立查询、索引和视图方法;
二、试验准备
1.了解SQL语言查改增删四大操作语法;
2.了解查询、索引和视图概念;
3.了解各类常见函数含义。
三、试验内容
(一)SQL查询功效
使用提供studentdb数据库文件,先附加到目录树中,再完成下列题目,SQL命令请保留到脚本文件中。
1.基础查询
(1)查询全部姓王学生姓名、学号和性别
SelectSt_Name,St_Sex,St_ID
Fromst_info
WhereSt_Namelike'王%'
图5-1
(2)查询全体学生情况,查询结构按班级降序排列,同一班级再按学号升序,并将结果存入新表new中
select*intonew
fromst_info
orderbyCl_Namedesc,st_IDasc
图5-2
(3)对S_C_info表中选修了“体育”课学生平均成绩生成汇总行和明细行。
(提醒:
用compute汇总计算)
因本已不支持compute关键字,所以选择用其她方法。
Selectc_no,score
Froms_c_info
Wherec_no=29000011
groupbyc_no,score
图5-3
2.嵌套查询
(1)查询其她班级中比“材料科学0601班”学生年纪都大学生姓名和年纪
selectst_name,born_date
fromst_info
wherecl_name!
='材料科学0601班'andborn_date<(selectmin(born_date)
fromst_infowherecl_name='材料科学0601班')
图5-4
(2)用exists查询选修了“9710041”课程学生姓名
selectst_name
fromst_info
whereexists(select*froms_c_infowherec_no=9710041andst_id=st_info.st_id)
图5-5
(3)用in查询找出没有选修“9710041”课程学生姓名和所在班级。
selectst_name,cl_name
fromst_info
wherest_idnotin(selectst_idfroms_c_infowherec_no='9710041')
图5-6
(4)查询选修了学号为“”学生所选全部课程学生姓名。
selectst_name
fromst_infowherest_idin
(selectdistinctst_idfroms_c_infowherenotexists
(select*froms_c_info
wherest_id=''andnotexists
(select*froms_c_infowherest_info.st_id=s_c_info.st_idand
c_no=any(selectc_nofroms_c_infowherest_id=''))))
图5-7
3.连接综合查询及其她
(1)查询每个学生所选课程最高成绩,要求列出学号,姓名,课程编号和分数。
selectst_info.st_id,st_name,c_info.c_no,score
fromst_infoinnerjoins_c_infoonst_info.st_id=s_c_info.st_idinnerjoin
c_infoons_c_info.c_no=c_info.c_no
wherescore=(selectmax(s_c_info.score)froms_c_info
wherest_info.st_id=s_c_info.st_id)
图5-8
(2)查询全部学生总成绩,要求列出学号、姓名、总成绩,没有选修课程学生总成绩为空。
selectst_info.st_id,st_name,总成绩
fromst_info
leftouterjoin(selectst_id,sum(score)as总成绩froms_c_infogroupby
st_id)s_c_infoonst_info.st_id=s_c_info.st_id
图5-9
(3)查询“大学计算机基础”课程考试成绩前三名学生姓名和成绩。
selectst_info.st_id,st_name,score
fromst_info
innerjoins_c_infoonst_info.st_id=s_c_info.st_id
innerjoinc_infoons_c_info.c_no=c_info.c_no
andc_name='大学计算机基础'
图5-10
(4)将s_c_info中score列值转为等级制输出,即60分以下显示为“不及格”,60~69分显示“及格”,70~79分显示“中等”,80~81显示“良好”,90~100显示“优异”。
要求输出学号、姓名、课程名、成绩等级。
(提醒:
在select字句中使用case…when…end语句)
selectst_info.st_id,st_name,c_name,成绩等级=
case
whenscore>=90then'优异'
whenscore>=80then'良好'
whenscore>=70then'中等'
whenscore>=60then'及格'
whenscore<60then'不及格'
end
froms_c_info,st_info,c_info
wherest_info.st_id=s_c_info.st_idandc_info.c_no=s_c_info.c_no
图5-11
(二)SQL增删改功效
在试验四建立studb数据库中,写SQL语句实现增删改功效。
1.在S表中增加以下统计:
图5-12
insert S
values('s3','张明华','男','1995/08/2100:
00:
00.000','MA_数学','530.0','浙江杭州',NULL)
图5-13
图5-14
2.在C表中将课程名为“数据库”学分更改为3。
update C set ccredit='3'
where cname='数据库'
图5-15
图5-16
3.删除S表中S2学生统计,请问是否能删除,为何,要怎样操作。
能删除
delete from S
where sno='S2'
图5-17
图5-18
图5-19
图5-20
(三)索引
1.在studb数据库中,分别用对象资源管理器和SQL语言定义索引
在对象资源管理器中,在T表tname列上中建立聚集索引ix_tname,降序。
查看聚集效果。
图5-21
图5-22
1.使用SQL语言定义TC表(tno,cno)列上复合索引ix_tc,tno列设为升序,cno列设为降序
先增加cno列,再删除聚集索引ix_tname。
create clustered index ix_tc
on T(tno,cno)
图5-23
图5-24
(四)视图
在studb数据库中操作。
1.在对象资源管理中建立视图v_s_c,列出全部学生所选课程成绩:
学号,姓名,班级名,课程号,课程名,成绩。
图5-25
图5-26
图5-27
2.使用SQL语言建立视图v_cjtj,列出每位同学学号,最高成绩,最低成绩,平均成绩和总成绩,按总成绩降序排列。
createviewv_cjtj(xh,zgf,zdf,pjf,zf)
as
(selecttop100sno,max(score),min(score),avg(score),sum(score)
fromSCgroupbysno
orderbysum(score)desc
)
图5-28
图5-29
四、思索与练习
1.视图和表有何区分?
(1)视图是已经编译好sql语句。
而表不是
(2)视图没有实际物理统计。
而表有。
(3)表是内容,视图是窗口
(4)表只用物理空间而视图不占用物理空间,视图只是逻辑概念存在,表能够立刻对它进行修改,但视图只能有创建语句来修改
(5)表是内模式,视图是外模式
(6)视图是查看数据表一个方法,能够查询数据表中一些字段组成数据,只是部分SQL语句集合。
从安全角度说,视图能够不给用户接触数据表,从而不知道表结构。
(7)表属于全局模式中表,是实表;视图属于局部模式表,是虚表。
(8)视图建立和删除只影响视图本身,不影响对应基础表。
2.视图中列都能更新吗?
不一定
3.查询年纪最大老师号和年纪,SQL命令以下:
请问为何报错?
怎样修改?
Selecttno,max(year(getdate())-year(tbirday))
FromT
选择列表中列‘T.tno’无效,因为该列没有包含在聚合函数或GROUP BY 子句中。
在from后面加group by tno