实验5实验报告

1/13学号：20164477姓名：陈家凤实验五SQL语言一、目的与要求1.掌握SQL语言的查询功能；2.掌握SQL语言的数据操作功能；3.掌握对象资源管理器建立查询、索引和视图的方法；二、实验准备1.了解SQL语言的查改增删四大操作的语法；2.了解查询、索引和视图的概念；3.了解各类常用函数的含义。三、实验内容(一)SQL查询功能使用提供的studentdb数据库文件，先附加到目录树中，再完成下列题目，SQL命令请保存到脚本文件中。1．基本查询(1)查询所有姓王的学生的姓名、学号和性别SelectSt_Name,St_Sex,St_IDFromst_infoWhereSt_Namelike'王%'图5-1(2)查询全体学生的情况，查询结构按班级降序排列，同一班级再按学号升序，并将结果存入新表new中select*intonewfromst_infoorderbyCl_Namedesc,st_IDasc2/13图5-2(3)对S_C_info表中选修了“体育”课的学生的平均成绩生成汇总行和明细行。(提示：用compute汇总计算)因2014版本已不支持compute关键字，所以选择用其他方式。Selectc_no,scoreFroms_c_infoWherec_no=29000011groupbyc_no,score图5-32．嵌套查询(1)查询其他班级中比“材料科学0601班”的学生年龄都大的学生姓名和年龄selectst_name,born_datefromst_infowherecl_name!='材料科学0601班'andborn_date(selectmin(born_date)fromst_infowherecl_name='材料科学0601班')3/13图5-4(2)用exists查询选修了“9710041”课程的学生姓名selectst_namefromst_infowhereexists(select*froms_c_infowherec_no=9710041andst_id=st_info.st_id)图5-5(3)用in查询找出没有选修“9710041”课程的学生的姓名和所在班级。selectst_name,cl_namefromst_infowherest_idnotin(selectst_idfroms_c_infowherec_no='9710041')4/13图5-6(4)查询选修了学号为“2001050105”的学生所选全部课程的学生姓名。selectst_namefromst_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'))))图5-75/133．连接综合查询及其他(1)查询每个学生所选课程的最高成绩，要求列出学号，姓名，课程编号和分数。selectst_info.st_id,st_name,c_info.c_no,scorefromst_infoinnerjoins_c_infoonst_info.st_id=s_c_info.st_idinnerjoinc_infoons_c_info.c_no=c_info.c_nowherescore=(selectmax(s_c_info.score)froms_c_infowherest_info.st_id=s_c_info.st_id)图5-8(2)查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生总成绩为空。selectst_info.st_id,st_name,总成绩fromst_infoleftouterjoin(selectst_id,sum(score)as总成绩froms_c_infogroupbyst_id)s_c_infoonst_info.st_id=s_c_info.st_id6/13图5-9(3)查询“大学计算机基础”课程考试成绩前三名的学生姓名和成绩。selectst_info.st_id,st_name,scorefromst_infoinnerjoins_c_infoonst_info.st_id=s_c_info.st_idinnerjoinc_infoons_c_info.c_no=c_info.c_noandc_name='大学计算机基础'图5-10(4)将s_c_info中的score列的值转为等级制输出，即60分以下显示为“不及格”，60~69分显示“及格”，70~79分显示“中等”，80~81显示“良好”，90~100显示“优秀”。要求输出学号、姓名、课程名、成绩等级。(提示：7/13在select字句中使用case…when…end语句)selectst_info.st_id,st_name,c_name,成绩等级=casewhenscore=90then'优秀'whenscore=80then'良好'whenscore=70then'中等'whenscore=60then'及格'whenscore60then'不及格'endfroms_c_info,st_info,c_infowherest_info.st_id=s_c_info.st_idandc_info.c_no=s_c_info.c_no图5-11(二)SQL的增删改功能在实验四建立的studb数据库中，写SQL语句实现增删改功能。1．在S表中增加如下记录：图5-128/13insertSvalues('s3','张明华','男','1995/08/2100:00:00.000','MA_数学','530.0','浙江杭州',NULL)图5-13图5-142.在C表中将课程名为“数据库”的学分更改为3。updateCsetccredit='3'wherecname='数据库'图5-15图5-163．删除S表中S2的学生记录，请问是否能删除，为什么，要如何操作。能删除deletefromSwheresno='S2'9/13图5-17图5-18图5-19图5-20(三)索引1.在studb数据库中，分别用对象资源管理器和SQL语言定义索引在对象资源管理器中，在T表的tname列上中建立聚集索引ix_tname，降序。查看10/13聚集的效果。图5-21图5-221．使用SQL语言定义TC表的(tno，cno)列上的复合索引ix_tc，tno列设为升序，cno列设为降序先增加cno列，再删除聚集索引ix_tname。createclusteredindexix_tc11/13onT(tno,cno)图5-23图5-24(四)视图在studb数据库中操作。1．在对象资源管理中建立视图v_s_c，列出所有学生所选课程的成绩：学号，姓名，班级名，课程号，课程名，成绩。图5-2512/13图5-26图5-272．使用SQL语言建立视图v_cjtj，列出每位同学的学号，最高成绩，最低成绩，平均成绩和总成绩，按总成绩降序排列。createviewv_cjtj(xh,zgf,zdf,pjf,zf)13/13as(selecttop100sno,max(score),min(score),avg(score),sum(score)fromSCgroupbysnoorderbysum(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’无效，因为该列没有包含在聚合函数或GROUPBY子句中。在from后面加groupbytno