实验五-SQL语言

1、精选优质文档-倾情为你奉上 实验五 SQL语言一、目的与要求1. 掌握SQL语言的查询功能；2. 掌握SQL语言的数据操作功能；3. 掌握对象资源管理器建立查询、索引和视图的方法；二、实验准备1. 了解SQL语言的查改增删四大操作的语法； 2. 了解查询、索引和视图的概念；3. 了解各类常用函数的含义。三、实验内容 (一)SQL查询功能使用提供的studentdb数据库文件，先附加到目录树中，再完成下列题目，SQL命令请保存到脚本文件中。1基本查询(1) 查询所有姓王的学生的姓名、学号和性别Select St_Name ,St_Sex, St_ID From st_info Where St_

2、Name like '王%'(2) 查询全体学生的情况，查询结构按班级降序排列，同一班级再按学号升序，并将结果存入新表new中select * into new from st_info order by Cl_Name desc,St_ID asc (3) 对S_C_info表中选修了“体育”课的学生的平均成绩生成汇总行和明细行。(提示：用compute汇总计算)Select c_no,scoreFrom s_c_infoWhere c_no= compute avg(score)2嵌套查询(1) 查询其他班级中比“材料科学0601班”的学生年龄都大的学生姓名和年龄Selec

3、t St_Name ,Born_Datefrom st_infowhere Cl_Name!='材料科学0601班' and Born_Date<(select Min(Born_Date) from st_info where Cl_Name='材料科学0601班')(2) 用exists查询选修了“”课程的学生姓名select St_Name from st_info where exists (select * from s_c_info where c_no = and st_id=st_info.St_ID )(3) 用in查询找出没有选修“”课

4、程的学生的姓名和所在班级。select St_Name,Cl_Namefrom st_infowhere st_ID not in (select st_id from s_c_info where c_no ='')(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 ex

5、ists (select * from s_c_info where st_info.St_ID=s_c_info.st_id and c_no=any(select c_no from s_c_info where st_id='')3连接综合查询及其他(1) 查询每个学生所选课程的最高成绩，要求列出学号，姓名，课程编号和分数。select st_info.St_ID, St_Name,C_info.c_no,score from st_info inner join s_c_info on st_info.St_ID=s_c_info.st_id inner join C_

6、info on s_c_info.c_no=C_info.c_no where score=(select max(s_c_info.score)from s_c_info where st_info.St_ID=s_c_info.st_id)(2) 查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生总成绩为空。select st_info.St_ID,St_Name,总成绩from st_info left outer join (select st_id,sum(score)as 总成绩 from s_c_info group by st_id)s_c_info on s

7、t_info.St_ID=s_c_info.st_id(3) 查询“大学计算机基础”课程考试成绩前三名的学生姓名和成绩。select st_info.St_ID,St_Name,score from 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_no and c_Name='大学计算机基础'(4) 将s_c_info中的score列的值转为等级制输出，即60分以下显示为“不及格”，6069分显示“及格”，7079分显示“中

8、等”，8081显示“良好”，90100显示“优秀”。要求输出学号、姓名、课程名、成绩等级。(提示：在select字句中使用casewhenend语句)select St_info.st_id,St_name,C_Name,成绩等级= case when score>=90 then '优秀' when score>=80 then '良好' when score>=70 then '中等' when score>=60 then '及格' when score<60 then '不及格'

9、; end from s_c_info,St_info,C_Info where St_info.st_id=s_c_info.st_id and C_Info.C_No=s_c_info.c_no(二)SQL的增删改功能在实验四建立的studb数据库中，写SQL语句实现增删改功能。1在S表中增加如下记录：insert Svalues('s3','张明华','男','1995-08-21 00:00:00.000','MA_数学','530.0','浙江杭州',NULL)2. 在C表

10、中将课程名为“数据库”的学分更改为3update C set ccredit='3' where cname='数据库'3删除S表中S2的学生记录，请问是否能删除，为什么，要如何操作。能删除delete from S where sno='S2'(三)索引在studb数据库中，分别用对象资源管理器和SQL语言定义索引1 在对象资源管理器中，在T表的tname列上中建立聚集索引ix_tname，降序。查看聚集的效果。create clustered index ix_tname on T(tname)sp_helpindex T 2 使用SQL语言

11、定义T表的(tno，cno)列上的复合索引ix_tc，tno列设为升序，cno列设为降序create clustered index ix_tc on T(tno,cno)(四)视图在studb数据库中操作。1 在对象资源管理中建立视图v_s_c，列出所有学生所选课程的成绩：学号，姓名，班级名，课程号，课程名，成绩。2使用SQL语言建立视图v_cjtj，列出每位同学的学号，最高成绩，最低成绩，平均成绩和总成绩，按总成绩降序排列。create view v_cjtj(xh.zgf,zdf.pjf,zf)as(select top 100 son,max(score),min(score),avg

12、(score),sum(score)from SCgroup by sonorder by sum(score) desc)四、思考与练习1视图和表有何区别？1、视图是已经编译好的sql语句。而表不是 2、视图没有实际的物理记录。而表有。 3、表是内容，视图是窗口 4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改 5、表是内模式，视图是外模式 6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。 7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。 8、视图的建立和删除只影响视