实验五 SQL语言.docx
- 文档编号:7049349
- 上传时间:2023-05-11
- 格式:DOCX
- 页数:11
- 大小:384.86KB
实验五 SQL语言.docx
《实验五 SQL语言.docx》由会员分享,可在线阅读,更多相关《实验五 SQL语言.docx(11页珍藏版)》请在冰点文库上搜索。
实验五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'王%'
(2)查询全体学生的情况,查询结构按班级降序排列,同一班级再按学号升序,并将结果存入新表new中
select*intonew
fromst_info
orderbyCl_Namedesc,St_IDasc
(3)对S_C_info表中选修了“体育”课的学生的平均成绩生成汇总行和明细行。
(提示:
用compute汇总计算)
Selectc_no,score
Froms_c_info
Wherec_no=29000011
computeavg(score)
2.嵌套查询
(1)查询其他班级中比“材料科学0601班”的学生年龄都大的学生姓名和年龄
SelectSt_Name,Born_Date
fromst_info
whereCl_Name!
='材料科学0601班'andBorn_Date<(selectMin(Born_Date)fromst_infowhereCl_Name='材料科学0601班')
(2)用exists查询选修了“9710041”课程的学生姓名
selectSt_Name
fromst_info
whereexists(select*froms_c_infowherec_no=9710041andst_id=st_info.St_ID)
(3)用in查询找出没有选修“9710041”课程的学生的姓名和所在班级。
selectSt_Name,Cl_Name
fromst_info
wherest_IDnotin(selectst_idfroms_c_infowherec_no='9710041')
(4)查询选修了学号为“2001050105”的学生所选全部课程的学生姓名。
selectSt_Name
fromst_infowhereSt_IDin
(selectdistinctSt_IDfroms_c_infowherenotexists
(select*froms_c_infowherest_id='2001050105'andnotexists
(select*froms_c_infowherest_info.St_ID=s_c_info.st_idandc_no=any(selectc_nofroms_c_infowherest_id='2001050105'))))
3.连接综合查询及其他
(1)查询每个学生所选课程的最高成绩,要求列出学号,姓名,课程编号和分数。
selectst_info.St_ID,St_Name,C_info.c_no,score
fromst_infoinnerjoins_c_infoonst_info.St_ID=s_c_info.st_idinnerjoinC_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)
(2)查询所有学生的总成绩,要求列出学号、姓名、总成绩,没有选修课程的学生总成绩为空。
selectst_info.St_ID,St_Name,总成绩
fromst_info
leftouterjoin(selectst_id,sum(score)as总成绩froms_c_infogroupbyst_id)s_c_infoonst_info.St_ID=s_c_info.st_id
(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='大学计算机基础'
(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
(二)SQL的增删改功能
在实验四建立的studb数据库中,写SQL语句实现增删改功能。
1.在S表中增加如下记录:
insertS
values('s3','张明华','男','1995-08-2100:
00:
00.000','MA_数学','530.0','浙江杭州',NULL)
2.在C表中将课程名为“数据库”的学分更改为3
updateC
setccredit='3'
wherecname='数据库'
3.删除S表中S2的学生记录,请问是否能删除,为什么,要如何操作。
能删除
deletefromSwheresno='S2'
(三)索引
在studb数据库中,分别用对象资源管理器和SQL语言定义索引
1.在对象资源管理器中,在T表的tname列上中建立聚集索引ix_tname,降序。
查看聚集的效果。
createclusteredindexix_tname
onT(tname)
sp_helpindexT
2.使用SQL语言定义T表的(tno,cno)列上的复合索引ix_tc,tno列设为升序,cno列设为降序
createclusteredindexix_tc
onT(tno,cno)
(四)视图
在studb数据库中操作。
1.在对象资源管理中建立视图v_s_c,列出所有学生所选课程的成绩:
学号,姓名,班级名,课程号,课程名,成绩。
2.使用SQL语言建立视图v_cjtj,列出每位同学的学号,最高成绩,最低成绩,平均成绩和总成绩,按总成绩降序排列。
createviewv_cjtj(xh.zgf,zdf.pjf,zf)
as
(selecttop100son,max(score),min(score),avg(score),sum(score)
fromSC
groupbyson
orderbysum(score)desc
)四、思考与练习
1.视图和表有何区别?
1、视图是已经编译好的sql语句。
而表不是
2、视图没有实际的物理记录。
而表有。
3、表是内容,视图是窗口
4、表只用物理空间而视图不占用物理空间,视图只是逻辑概念的存在,表可以及时对它进行修改,但视图只能有创建的语句来修改
5、表是内模式,视图是外模式
6、视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些SQL语句的集合。
从安全的角度说,视图可以不给用户接触数据表,从而不知道表结构。
7、表属于全局模式中的表,是实表;视图属于局部模式的表,是虚表。
8、视图的建立和删除只影响视图本身,不影响对应的基本表。
2.视图中的列都能更新吗?
不一定可以更新
3.查询年龄最大的教师号和年龄,SQL命令如下:
请问为什么报错?
如何修改?
Selecttno,max(year(getdate())-year(tbirday))
FromT
选择列表中的列‘T.tno’无效,因为该列没有包含在聚合函数或GROUPBY子句中。
在from后面加groupbytno。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验五 SQL语言 实验 SQL 语言