实验五 SQL语言

实验五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_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,score

From s_c_info

Where c_no=29000011

compute avg(score)

2．嵌套查询

(1)查询其他班级中比“材料科学0601班”的学生年龄都大的学生姓名

和年龄

Select St_Name ,Born_Date

from st_info

where Cl_Name!='材料科学0601班' and Born_Date<(select Min(Born_Date) from st_info where Cl_Name='材料科学0601班')

(2)用exists查询选修了“9710041”课程的学生姓名

select St_Name

from st_info

where exists (select * from s_c_info where c_no = 9710041 and st_id=st_info.St_ID )

(3)用in查询找出没有选修“9710041”课程的学生的姓名和所在班级。select St_Name,Cl_Name

from st_info

where st_ID not in (select st_id from s_c_info where c_no ='9710041')

(4)查询选修了学号为“2001050105”的学生所选全部课程的学生姓名。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='2001050105' and not exists

(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='2001050105'))))

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_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 st_info.St_ID=s_c_info.st_id

(3)查询“大学计算机基础”课程考试成绩前三名的学生姓名和成绩。

select st_info.St_ID,St_Name,score

from st_info

inner join s_c_info on st_info.St_ID=s_c_info.st_id

inner join C_info on s_c_info.c_no=C_info.c_no

and c_Name='大学计算机基础'

(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,成绩等级=

case

when score>=90 then '优秀'

when score>=80 then '良好'

when score>=70 then '中等'

when score>=60 then '及格'

when score<60 then '不及格'

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 S

values('s3','张明华','男','1995-08-21 00:00:00.000','MA_数学','530.0','浙江杭州',NULL)

2. 在C表中将课程名为“数据库”的学分更改为3

update 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语言定义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(score),sum(score)

from SC

group by son

order by sum(score) desc

)四、思考与练习

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。