2023年SQL语言实验报告.doc

实验五 SQL语言一、目的与规定1. 掌握SQL语言的查询功能；2. 掌握SQL语言的数据操作功能；3. 掌握对象资源管理器建立查询、索引和视图的方法；二、实验准备1. 了解SQL语言的查改增删四大操作的语法； 2. 了解查询、索引和视图的概念；3. 了解各类常用函数的含义三、实验内容 (一)SQL查询功能使用提供的studentdb数据库文献，先附加到目录树中，再完毕下列题目，SQL命令请保存到脚本文献中1．基本查询(1) 查询所有姓王的学生的姓名、学号和性别Select St_Name,St_Sex,St_IDFrom st_infoWhere St_Name like'王%'图5-1(2) 查询全体学生的情况，查询结构按班级降序排列，同一班级再按学号升序，并将结果存入新表new中select *into newfrom st_infoorder by Cl_Name desc,st_ID asc图5-2(3) 对S_C_info表中选修了“体育”课的学生的平均成绩生成汇总行和明细行提醒：用compute汇总计算)因2023版本已不支持compute关键字，所以选择用其他方式Select c_no,scoreFrom s_c_infoWhere c_no=29000011group by c_no,score图5-32．嵌套查询(1) 查询其他班级中比“材料科学0601班”的学生年龄都大的学生姓名和年龄select st_name,born_datefrom st_infowhere cl_name!='材料科学0601班'and born_date<(select min(born_date)from st_info where cl_name='材料科学0601班')图5-4(2) 用exists查询选修了“9710041”课程的学生姓名select st_namefrom st_infowhere exists(select*from s_c_info where c_no=9710041 and st_id=st_info.st_id)图5-5(3) 用in查询找出没有选修“9710041”课程的学生的姓名和所在班级。

select st_name,cl_namefrom st_infowhere st_id not in(select st_id from s_c_info where c_no='9710041')图5-6(4) 查询选修了学号为“”的学生所选所有课程的学生姓名select st_name from st_info where st_id in(select distinct st_id from s_c_info where not exists(select* from s_c_info where st_id=''and not exists(select* from s_c_info where st_info.st_id=s_c_info.st_id andc_no=any(select c_no from s_c_info where st_id=''))))图5-73．连接综合查询及其他(1) 查询每个学生所选课程的最高成绩，规定列出学号，姓名，课程编号和分数select st_info.st_id,st_name,c_info.c_no,scorefrom st_info inner join s_c_info on st_info.st_id=s_c_info.st_id inner joinc_info on s_c_info.c_no=c_info.c_nowhere score=(select max(s_c_info.score)from s_c_infowhere st_info.st_id=s_c_info.st_id)图5-8(2) 查询所有学生的总成绩，规定列出学号、姓名、总成绩，没有选修课程的学生总成绩为空。

select st_info.st_id,st_name,总成绩from st_infoleft outer join (select st_id,sum(score)as 总成绩 from s_c_info group by st_id)s_c_info on st_info.st_id=s_c_info.st_id图5-9(3) 查询“大学计算机基础”课程考试成绩前三名的学生姓名和成绩select st_info.st_id,st_name,scorefrom st_infoinner join s_c_info on st_info.st_id=s_c_info.st_idinner join c_info on s_c_info.c_no=c_info.c_noand c_name='大学计算机基础'图5-10(4) 将s_c_info中的score列的值转为等级制输出，即60分以下显示为“不及格”，60~69分显示“及格”，70~79分显示“中档”，80~81显示“良好”，90~100显示“优秀”规定输出学号、姓名、课程名、成绩等级提醒：在select字句中使用case…when…end语句)select st_info.st_id,st_name,c_name,成绩等级=casewhen score>=90 then'优秀'when score>=80 then'良好'when score>=70 then'中档'when score>=60 then'及格'when score<60 then'不及格'endfrom s_c_info,st_info,c_infowhere st_info.st_id=s_c_info.st_id and c_info.c_no=s_c_info.c_no图5-11(二)SQL的增删改功能在实验四建立的studb数据库中，写SQL语句实现增删改功能。

1．在S表中增长如下记录：图5-12insert Svalues('s3','张明华','男','1995/08/21 00:00:00.000','MA_数学','530.0','浙江杭州',NULL) 图5-13图5-142. 在C表中将课程名为“数据库”的学分更改为3update C   set ccredit='3' where cname='数据库'图5-15图5-163．删除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-221． 使用SQL语言定义TC表的(tno，cno)列上的复合索引ix_tc，tno列设为升序，cno列设为降序先增长cno列，再删除聚集索引ix_tnamecreate  clustered  index  ix_tc  on T(tno,cno)图5-23图5-24(四)视图在studb数据库中操作。

1． 在对象资源管理中建立视图v_s_c，列出所有学生所选课程的成绩：学号，姓名，班级名，课程号，课程名，成绩图5-25图5-26图5-272．使用SQL语言建立视图v_cjtj，列出每位同学的学号，最高成绩，最低成绩，平均成绩和总成绩，按总成绩降序排列create view v_cjtj(xh,zgf,zdf,pjf,zf)as(select top 100 sno,max(score),min(score),avg(score),sum(score)from SC group by snoorder by sum(score) desc)图5-28图5-29四、思考与练习1．视图和表有何区别？（1）视图是已经编译好的sql语句而表不是       （2）视图没有实际的物理记录    （3）表是内容，视图是窗口 （4）表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改     （5）表是内模式，视图是外模式     （6）视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。

 （7）表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表      （8）视图的建立和删除只影响视图自身，不影响相应的基本表2．视图中的列都能更新吗？ 不一定3．查询年龄最大的教师号和年龄，SQL命令如下：请问为什么报错？如何修改？Select tno,max(year(getdate( ))-year(tbirday))From T选择列表中的列‘T.tno’无效，由于该列没有包含在聚合函数或GROUP BY 子句中 在from后面加group by tno。