数据库综合查询与视图操作

数据库名：student

数据表：

学生基本信息表(表名：S)

sno	sname	ssex	sbirth	Sdept
95001	李勇	男	1986/1/1	CS
95002	刘晨	女	1985/2/1	IS
95003	王敏	女	1886/10/4	MA
95004	张立	男	1985/6/8	IS

课程基本信息表(表名：C)

cno	cname	cpno	Ccredit
1	数据库	2	4
2	数学		3
3	信息系统	1	4
4	操作系统	5	3
5	数据结构	6	3
6	C语言		2

学生选课信息表(表名：SC)

sno	cno	grade	term
95001	1	92	1
95001	2	85	2
95001	3	87	3
95002	2	78	2
95002	3	84	3
95003	1	30	1
95004	2	58	2
95004	4	70	3
95002	5	60	4
95003	4	70	3

一、综合查询：

使用sql

语句完成下述任务，下面为操作过程中的

sql

语句和操作结果截图。

1、

求不选修

C

语言课程的学生学号。

使用嵌套查询中的

“IN”子查询语句进行查询，对于不选修C

语言这一条件，

使用

“！=C

语言

”表示。

2、

查询平均成绩在

60

分以上的学生姓名。

使用连接查询进行操作，其中使用了之前未使用过的

“HAVING”字句。

having

字句可以让我们筛选成组后的各种数据，

where

字句在聚合前先筛选记录，也就是说作用在

group by

和

having

字句前。而

having

子句在聚合后对组记录进行筛选。我的理解就是

having

中用的数据

真实表中并没有，而是通过一些函数生存。详细可以参考【1】

3、

求这样的学生姓名：该学生选修了全部课程并且其中一门课在

90

分以上。

使用嵌套查询，利用了

“not exists”字句，

这一字句我的理解是一个判断语句，

exists

（

sql

返回结果集为真）

not exists (sql

不返回结果集为真）。

4、查询选修课程数在两门以上的学生姓名。（用连接查询和嵌套查询两种方法实现）

使用两种方式实现，其实，在嵌套查询中使用了having

和

in

语句，

in

实则和

exists

有点类似，但是

in

是把外表和内表作

hash(

字典集合

)

连接，而

exists

是对外表作循环，每次循环再对内表进行查询。一直以来认为

exists

比

in

效率高的说法是不准确的，如果查询的两个表大小相当，那么用

in

和

exists

差别不大；如果两个表中一个较小一个较大，则子查询表大的用

exists

，子查询表小的用

in

。

5、查询所有成绩都及格的学生人数。

使用

“distinct”对成绩及格的人次进行去重。

6、查询成绩及格的学生人次。

和上一题类似，主要是不用去重，直接统计成绩大于等于60

的人次

7、查询所有学生都没有选修的课程名称。

使用

“not exists”对选修课程进行判断，判断是否被选，将没有返回结果（即没有被选）的课程输出。

8、查询每个学生的平均成绩，并按平均成绩的降序排序。

主要是对于order by

的使用，

order by

是用来写在

where

之后，给多个字段来排序的一个

DQL

查询语句。其和

where

的搭配有多种使用方法，参考【

4

】。

9、查询每个同学的最高分，要求显示学号、姓名和分数等信息。

10、查询选修同一门课程的学生学号。

这里主要是对选修每一门课程的学生学号进行查询，其实操作的数据不多，是需要对

sc

表进行操作就行，难点在于如果将多个学生的学号输出到一行。这里使用

group_comcat

字句实现这一功能。详细用法参考【

5

】

11、查询不及格学生的学号、课程名、开课学期的信息。

12、

按学号分组汇总总分高于

100

的学生记录，并按总分的降序排列。

13、求恰好有两门课程不及格的学生信息。

难点在于in

、

where

以及

having

的同时使用的嵌套查询。

14、查询每门课程的最高分的学生记录。

每门课程最高分，使用MAX

函数求，并且使用嵌套查询查出对应的学生记录。

15、查询每个学生的最低分的课程记录。

最低分查询使用min

函数

二、视图管理：

1、

为学生表创建一个视图

V_XSQK

，以显示学生的基本信息，如学号、姓名、性别和出生年月。

难度不大，直接使用create view

语句和查询语句结合

2、

创建视图

V_SC

，要求显示学生的姓名、选修的课程名和成绩。

需要进行连接查询，从s

，

c

，

sc

三个表中分别得到学生的姓名、课程名和成绩。

3、

创建视图

V_ST

，显示在

1981-1-1

以后出生的学生信息，包括学号，姓名，性别和出生年月。

4、

创建视图

v_HG

，显示成绩合格的学生信息，包括姓名、课程名和成绩。

5、

创建分组视图

V_GP

，要求查询每个学生的平均成绩，包括姓名，平均成绩。

6、向视图V_XSQK

中插入一条记录：‘

2020206

’，‘李红’，‘

1983-12-3

’。

使用insert into

语句，将需要插入的数据放入

values

中，然后进行插入，这里需要赋予

ssex

一个空值，否则会出现计数不对的情况而导致插入失败。

7、修改视图V_XSQK

，将李红同学是出生年月改为

1982-12-3

。

使用update

语句，使用

where

查询到姓名为“李红”的记录，然后将生日这一项改成

1982-12-3

。

8、

删除视图

V_XSQK

中李红同学的信息。

在视图中删除信息，使用delete

语句。

9、

删除视图

V_XSQK

。

删除视图，和删除数据库数据表所用的语句一样，都是drop

10、

创建视图

V_PJ

，查询平均成绩在

60

分以上的学生信息，包括姓名和平均成绩。

难度不大，实际上就是使用视图来表示上述综合操作中对学生平均成绩的查询，并加上60

分以上这个条件。

三、

实验总结

这次实验，主要还是更加熟悉和掌握了一些基本的

sql

查询语句，特别是对于一些连接查询和嵌套查询的理解和相关的

sql

字句的认识。通过这次实验，也对连接查询和嵌套查询有了一个更加深入的理解。

用自己的话来说，若一个查询同时涉及

2

个或以上的表，就可以称为连接查询。其中包括等值和非等值连接查询，自然连接查询，外连接查询，复合条件连接查询等。而嵌套查询，实际上可以通过其字面意思来理解，也就是嵌套，在

sql

语言中，一个

select-from-where

语句称为一个查询块。将一个查询块嵌套在另外一个查询块的

where

子句或

having

短语的条件中的查询叫做嵌套查询。

初次之外，这次实验对于数据库视图的操作也是一个比较新的东西，查询资料，然后亲身操作体会后我发现，其实视图操作可以很广泛的说成是把用查询语句查询出来的结果使用一个视图表示并将这个视图保存下来。主要的形式就是

“

create view view_name as

”

+

“查询语句”。当然，这样说也许是比较宽泛的说法，还是不太严谨。严谨的说法应该是这样描述（查阅资料后所得）：视图只是一个或多个表依照某个条件组合而成的结果集，一般来说你可以用

update

，

insert

，

delete

等

sql

语句修改表中的数据，而对视图只能进行

select

操作。但是也存在可更新的视图，对于这类视图的

update

，

insert

和

delete

等操作最终会作用于与其相关的表中数据。因此，表是数据库中数据存储的基础，而视图只是为了满足某种查询要求而建立的一个对象。