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

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

si课后补充阅读与参考

【1】mysql—having字句

https://www.yiibai.com/mysql/having.html

【2】嵌套查询与连接查询

https://blog.csdn.net/ccsuxwz/article/details/70157911

【3】Mysql数据库中的EXISTS和NOT EXISTS

https://blog.csdn.net/qsyzb/article/details/12523051

【4】SQL order by 用法

https://www.cnblogs.com/claricre/p/6187672.html

【5】Group_comcat

https://blog.csdn.net/mary19920410/article/details/76545053

 

MySQL查询语句大全

http://www.jb51.net/article/85889.htm

MySQL查询语句45道练习题

https://www.cnblogs.com/aqxss/p/6563625.html

菜鸟教程——MySQL查询语句

http://www.runoob.com/mysql/mysql-select-query.html

MySQL操作手册

https://dev.mysql.com/doc/