实验5实验报告

学号：20164477 姓名：陈家凤

实验五

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'王%'

图5-1

(2) 查询全体学生的情况，查询结构按班级降序排列，同一班级再按学号升序，并将结果存入新表new中

select *into new from st_info

order by Cl_Name desc,st_ID asc

1 / 13

图5-2

(3) 对S_C_info表中选修了“体育”课的学生的平均成绩生成汇总行和明细行。(提示：用compute汇总计算)

因2014版本已不支持compute关键字，所以选择用其他方式。 Select c_no,score From s_c_info

Where c_no=29000011 group by c_no,score

图5-3

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 / 13

图5-4

(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)

图5-5

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

3 / 13

图5-6

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

图5-7

4 / 13

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)

图5-8

(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

5 / 13

图5-9

(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='大学计算机基础'

图5-10

(4) 将s_c_info中的score列的值转为等级制输出，即60分以下显示为“不及格”，60~69分显示“及格”，70~79分显示“中等”，80~81显示“良好”，90~100显示“优秀”。要求输出学号、姓名、课程名、成绩等级。(提示：

6 / 13

在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

图5-11

(二)SQL的增删改功能

在实验四建立的studb数据库中，写SQL语句实现增删改功能。 1．在S表中增加如下记录：

图5-12

7 / 13

insert S

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

图5-13

图5-14

2. 在C表中将课程名为“数据库”的学分更改为3。 update C set ccredit='3' where cname='数据库'

图5-15

图5-16

3．删除S表中S2的学生记录，请问是否能删除，为什么，要如何操作。 能删除

delete from S where sno='S2'

8 / 13

图5-17

图5-18

图5-19

图5-20

(三)索引

1.在studb数据库中，分别用对象资源管理器和SQL语言定义索引

在对象资源管理器中，在T表的tname列上中建立聚集索引ix_tname，降序。查看

9 / 13

聚集的效果。

图5-21

图5-22

1．使用SQL语言定义TC表的(tno，cno)列上的复合索引ix_tc，tno列设为升序，cno列设为降序

先增加cno列，再删除聚集索引ix_tname。 create clustered index ix_tc

10 / 13

本文来源：https://www.bwwdw.com/article/ah16.html