当前位置：首页 > article >正文

【SQL Server】华中农业大学空间数据库实验报告实验七数据查询

article 2024/11/23 23:37:56

1.实验目的

数据查询为我们SQL Server实验与未来工作实践中非常实用与经常用到的一个功能，其主要包含单表查询、连接查询、嵌套查询、集合查询四种查询方法，多次利用各种语句、子句、谓词查询；
实验要求深刻理解并熟练掌握SELECT语句的基本语法和查询条件表示方法，通过反复练习达到可以在不利用外界帮助的条件下，根据实际需要自行编写SQL语句实现需求，并且可以使用多种查询方法，理解各种查询方法间的异同优缺与如何相互转换；
根据往期实验课程的内容，可以在建立的数据库【jiaoxuedb】中输入相关的虚拟数据，并在特定的题目要求下自行编写SQL语句，实现在单表查询的基础上结合嵌套方法、各种谓词、连接方式等等方法查询并分析查询结果。

2.实验内容

单表查询：指定列或全部列查询、按条件列查询及模糊查询、对查询结果排序、使用聚集函数的查询、分组统计查询；
连接查询：连接查询、自身连接、外连接；
嵌套查询（重点）：返回一个值的子查询、返回一组值的子查询；
集合查询：查询年龄不大于19岁或者属于地信专业的学生。

3.实验步骤

3.1单表查询

1．指定列或全部列查询

（1）查询STUDENT表中所有的内容：

新建查询，输入代码如下所示，其代码含义为使用数据库jiaoxuedb，选中显示数据库表【STUDENT】中的所有内容，得到结果如下所示：

（2）查询所有学生的姓名及出生年份：

新建查询，输入代码如下所示，数据库表中没有出生年份的字段属性，但利用SQL语句中的日期函数GETDATE我们可以获得轻松获得当前系统的日期和时间，将此时间与年龄相减，即可得到学生的出生年份：

结果如下所示：

2．按条件列查询及模糊查询

（1）查询有考试成绩不及格的学生的学号

新建查询，输入代码如下所示：

得到结果如下，符合数据库表中的数据：

（2）查询年龄在20—21岁之间的学生的姓名、专业、年龄

新建查询，输入代码如下所示，利用AND选择的是数据库表中年龄小于22大于19的值，也可以同实验指导书中的代码一样利用BETWEEN：

得到结果如下，符合数据库表中的数据：

（3）查询姓付的学生的姓名、学号、性别

新建查询，输入代码如下所示:

得到结果如下所示，符合数据库表中的数据：

（4）查询第二个字为2的男生的姓名和专业

新建查询，输入代码如下所示：

得到结果如下所示，符合数据库表中的数据与题目要求:

3．对查询结果排序

（1）查询计算机系，地信专业的学生姓名与专业，结果按专业升序，姓名降序

新建查询，输入代码如下所示：

得到结果如下所示，符合数据库表数据与题目要求：

（2）查询所有有课程号为C2的课程的学生的学号、课程号、成绩

新建查询，输入代码如下所示：

得到结果如下，符合数据库数据与题目要求：

4．使用聚集函数的查询

（1）查询地信专业的总人数

新建查询，输入代码如下所示：

得到结果如下所示，符合数据库表数据与题目要求：

（2）查询选修了数据库的学生人数、平均成绩、最高成绩

新建查询，输入代码如下所示：

得到结果如下所示，符合数据库表数据与题目要求：

5．分组统计查询

（1）查询各个课程号与相应的选课人数

新建查询，输入代码如下所示,其中主要利用的是GROUP BY语句，通过利用分组语句将数据库表中的数据根据课程号进行分组，然后即可进行累加计算计算选修人数：

得到结果如下所示，符合数据库表数据与题目要求：

（2）查询选修了两门以上课程的学生的姓名与平均成绩

新建查询，输入代码如下所示,此代码中需要注意的是HAVING语句的运用，在已经对数据库表进行了分组后，如果我们还需要去除某些条件的数据，则需要在GROUP BY语句后利用HAVING语句进行如下操作，此题目中的计数条件是非常常见的一种类型：

得到结果如下所示，符合数据库数据与题目要求：

3.2连接查询

1.连接查询

（1）查询所有选课学生的学号、姓名、选课名称、成绩

新建查询，输入代码如下所示，此代码通过两个连接操作将三个数据库表连接在一起：

得到结果如下所示，符合数据库表数据与题目要求：

（2）查询每门课程的课程号、任课老师姓名及其选课人数

新建查询，输入代码如下所示：

得到结果如下所示，符合数据库数据与题目要求：

2.自身连接

（1）查询所有比齐3工资高的教师姓名、工资和齐3的工资

新建查询，输入代码如下所示，此代码中主要运用了一个自身链接，将数据库表【TEACHER】中的数据分别复制给了X与Y：

得到结果如下所示，符合数据库表中的数据与题目要求：

(2)查询同时选修了“程序设计”和“数据库”的学生姓名与专业

新建查询，输入代码如下所示，此代码初步看较为复杂，其主要思路为：我们需要查询同时选择了两门课程的学生的相关信息，因此将学生表与成绩表与课程表通过学号再通过课号相连接就是必要的，但是连接后我们没有语句可以实现在单列属性中选中A又选中B，因此我们将成绩表与课程表复制为两个，分别与学生表连接，同时满足两个表的条件的学生，就是我们最后要找到的学生：

得到结果如下所示，符合数据库表中的数据与题目要求：

3.外连接

查询所有学生的学号、姓名、选课名称及成绩（没有选课的学生的选课信息为空）

新建查询，输入代码如下所示，此代码主要利用了外连接的这一个语句，其最大作用在于连接后，将次表没有数据的部分自动赋值为空，此处我们将学生表【STUDENT】作为主表，分别左外连接SC表与COURESE表，注意此处左外连接的代码不可互换：

得到结果如下所示，符合数据库表中的数据与题目要求：

3.3嵌套查询

1.返回一个值的子查询

查询与齐3教师职称相同的教师号、姓名和职称

新建查询，输入代码如下所示，此题目与我们之前做的自身连接的第一题有一定的相似之处，也同样可以利用自身连接的方法来解决，但此处选择了在WHERE语句中再次嵌套一个SELECT语句查询PROF与“齐3”相同的行的信息，更为简单快捷：

得到结果如下所示，符合数据库表中的数据与题目要求：

2.返回一组值的子查询

（1）使用ANY谓词查询课程号为C1的教师姓名

新建查询，输入代码如下所示,此代码并没有使用表与表相连接的方式，而是利用了一个嵌套查询，获得教授课程C1的老师的编号TNO，但是有多个老师同时教授这门课程，此时的ANY代表其中的某一个老师，此处我们也可以用IN来替代：

得到结果如下所示，符合数据库表数据与题目要求：

（2）使用IN谓词查询课程号为C1的教师姓名

新建查询，输入代码如下所示,与上一实验步骤中使用的方法相同，此代码并没有使用表与表相连接的方式，只是将ANY转化为了IN来替代。

得到结果与上一题一致，符合数据库表数据与题目要求。

（3）使用ALL谓词查询其它系中比地信所有教师工资都高的教师的姓名、工资和专业

新建查询，输入代码如下所示，代码并没有使用表与表相连接的方式，而是在WHERE处利用了一个嵌套查询与ALL谓词共同寻找地信专业中工资最高的教师，其中的ALL谓词表示范围内的每一个，因此WHERE条件处表示选择工资大于所有地信专业老师工资，且专业不为地信的老师：

得到结果如下图所示，符合数据库表数据与题目要求

（4）使用EXISTS谓词查询没有讲授课程号为C1的课程的教师的姓名与专业

新建查询，输入代码如下所示,其代码主要利用了NOT EXISTS谓词，获得了TEACHER表中，没有某一特征的数据：

得到结果与上一题一致，符合数据库表数据与题目要求：

（5）使用NOT EXISTS谓词查询至少选修了学生S4选修的所有课程的学生的学号

新建查询，输入代码如下所示，此代码中主要利用了一个NOT EXISTS谓词查询，该谓词最重要的为判断：NOT EXISTS括号中的SQL语句是否有结果，如果为假，即无结果，则继续执行WHERE条件；但如果为真，即有结果，则视为WHERE条件不成立，不执行WHERE语句。

此代码的基本思路为：将SC表复制为三个表分别代表学生学号、学生S4课程与原SC表，利用NOT EXISTS谓词，一个一个学号进行查询，如果某学号的课程号能完全筛选掉S4同学的课程号，则将该同学保留下来，得到最终的结果。

仅仅只说代码思路可能有点难以理解，我们以第一行数据，第一位同学S1为例。首先明确如下这种多次嵌套运用多个NOT EXISTS谓词查询的代码我们需要用特定的理解顺序，中间层的WHERE：我们获得所有学号为S4的同学上的课程的课程号；最内层的WHERE：我们获得S1同学与S4同学相同的课程号，如果存在，即为真，NOT EXISTS有结果，则视为WHERE条件不成立，因此筛选掉该课程号C2，但仍保留课程号C3；最外层的WHERE:因为第二个SELECT得到了结果C3，NOT EXISTS有结果，WHERE条件不成立，不执行WHERE语句，因此筛选掉了第一个同学S1。

得到结果如下所示，符合数据库表数据与题目要求：

3.4集合查询

1.查询年龄不大于18岁或者属于地信专业的学生

新建查询，输入代码如下所示，此代码代表选择数据库表中满足条件A或条件B的数据，虽然仍然使用了两条SELECT语句，但此时我们就可以利用UNION语句将这两条语句放到同一个新建查询中：

得到结果如下所示，符合数据库表数据与题目要求：

4.课后习题

1.查询至少有4个同学选修的课程名：

输入代码如下所示：

得到结果如下所示，符合数据库表数据与题目要求，C2代表课程数据库，只有数据库一门课程有四位及以上的同学选择：

2.查询其他系中比“地信专业”所有学生年龄都大的学生名单及年龄,并按年龄降序输出：

输入代码如下所示：

得到结果如下所示，符合数据库表数据与题目要求，地信年龄最大为20岁，而数据库表STUDENT中只有付1同学的年龄大于20为21岁：

3.查询成绩比该课程平均成绩高的学生的成绩表

输入代码如下所示：

得到结果如下所示，符合数据库表数据与题目要求，S1同学有一名课程高于平均分，S2同学有两门课程高于平均分，S3同学有1门课程高于平均分，S4同学有两门课程高于平均分，S5同学有一门课程高于平均分：

4.查询选修了课号为C2的课程且成绩高于课程号为C1的课程的学生的姓名、此两门课程的课程名和成绩：

输入代码如下所示，其核心思路为将SC表分为SC1与SC2两个相同的子表，判断哪些同学同时选修了两门课程，并分别获得每位同学的两门课程的成绩来进行比较，仿照这个思路，对COURESE表做同样的操作用以获得课程名：

得到结果如下所示，符合数据库表数据与题目要求，有三位同学同时选修了C1与C2课程，但只有付3与付4两位同学C2的课程分数高于C1：

5.查询所有未修C1号课程的学生名单：

输入代码如下所示，此题目主要应用了NOT EXISTS谓词的用法，在实验内容中有一道题目非常类似，需要注意的是两个表相连接的代码不能写在NOT EXISTS的括号外面，目前不太清楚其原理：

得到结果如下所示，符合数据库表数据与题目要求,只有S2,S3,S4三名同学选择了C1课程：

6.查询每个学生各门课程的平均成绩和最高成绩,按降序排列输出姓名、平均成绩和最高成绩：

输入代码如下所示，选择按平均成绩降序输出，主要利用了分组语句，以学生姓名为分组标准，因此仅适用于没有重名学生的情况，如果有重名学生，可以通过学号分组，首先获得学号，通过学号获得学生姓名：

查询选修了S4号学生选修了的课程的学生的学号和姓名：

输入代码如下所示：

得到结果如下所示，符合数据库表数据与题目要求，S4同学选修了C2与C1课程，学生S1选修了课程C2，学生S2选修了课程C1与C2，学生S3选修了课程C1与C2：

5.实验总结

1.相关概念的学习与区分

SQL中 % ，_,【】，【^】通配符的使用，此通配符多使用于模糊查询中：'%':代表0个或多个字符；'_':代表一个字符；'[]':表示在某一范围的字符，例如我们可以用该符号查询姓名为李或付的学生；'[^]':用法与上一通配符相反，表示不在某一范围的字符；
EXISTS与NOT EXISTS的使用：这两个谓词的子查询不返回数据，只产生逻辑真或假，EXISTS是遇真为真，遇假为假，NOT EXISTS是遇假为真，遇真为假。因此，他们是不能与IN 和NOT IN 完全等同的。EXISTS的用法本质上是过滤，在WHERE语句中，利用此谓词通过过滤，实现实际要求的更复杂的特定条件，明白这一点之后就能更好理解NOT EXISTS的用法。

2.代码执行与编写思路总结

ELECT * FROM Table WHERE ...语句是目前我们学习的SQL语句中重点要求掌握理解的语句，也是我们编写SQL语句实现相关功能必须理解的一部分：

(1)SQL语句执行思路：选择FROM后面的TABLE,指针指向从第一条数据开始，一个一个牵引至WHERE语句中，进行筛选，如果满足WHERE语句的条件，则被SELECT筛选出来，指针在TABLE中指向下一条数据，再牵着第二条数据进入WHERE中进行筛选，一直这样操作，直到TABLE表中的所有数据都被筛选完毕。

ELECT：最简单最直接的部分，直接在此处输入我们需要获得什么信息；

FROM：输入的TABLE表示从哪一个数据库表中获取我们需要的信息；

WHERE：筛选该信息是否符合我们需要的条件，是最重要也往往是最难编写的一部分；

GROUP BY：输入列属性，表示以TABLE中哪一列的数据为基础进行分组；

ORDER BY：输入列名+DESC，表示某列以降序排列。

(2)SQL语句编写思路：首先明确我们需要获得什么信息，从哪个TABLE中获得，如此可以非常轻松与快速的确定ELECT与FROM，接下来，开始逆向思考如果我们想要获得结果，需要从哪列数据或者哪个数据库表下手，并且，对于较复杂的数据库与需求来说，为获得结果C，通常我们无法直接建立A—>C的关系，但我们可以通过多个中间值，将A—>C拆分为A—>B—>C，逐个逐个的利用SQL语句编码进行解决，但这样的步骤就非常需要我们建立自己的“主键思维”，对数据库表中的“主键”非常清晰，这里的“主键”指的是数据库表中与其它表相连接的最重要的充当中间值的列，例如学号、课程号等，并不一定必须是主键，因为大部分问题我们都可以转化为获得“主键”。

3.常见问题总结

选择列表中的列 '……' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

原因：如果SELECT语句后包含聚合函数，则GROUP BY将计算每个组的汇总值，

指定GROUP BY时，选择列表中任何非聚合表达式内的每个属性名都应包含在GROUP BY列表中，因此GROUP BY表达式必须与选择列表表达式完全匹配，即必须包含所有我们选择的SELECT中的表达式,如下图代码所示：

后续：在课后练习题中的倒数第二题，同样出现了如上的问题，题目要求我们使用分组语句，但如果以学生姓名为分组标准，仅适用于没有重名学生的情况，如果有重名学生，我们的首先想法应该都是先获得学号，再通过学号获得学生姓名，原理非常简单，但我在实际尝试编写代码的过程中却一时间无法下手，因此我在更改数据库表数据后尝试了如下代码，将学生学号与学生姓名同时作为GROUP BY语句后的字段：

可以看到此时虽然学生名字有重复的，但是我们得到的结果依然是正确的，如下左图所示，查阅资料后发现：GROUP BY语句后如果有多个字段，我们需要将多个字段看成一个整体，以这个整体来进行分组，如下右图所示，因为每个字段都会被语句考虑到，因此GROUP BY 后一定不能是“*”。

会出现如上的问题，根本原因在于我没有真正理解GROUP BY这个语句的执行原理，在第一次遇到这个实验问题时，我只是简单的将他理解为GROUP BY表达式必须包含所有我们选择的SELECT中的表达式，但后续的学习也告诉了我这种想法并不准确。GROUP BY语句的这个特性使得之前我认为的一些比较困难的问题迎刃而解了，我也通过深入的学习更加理解了这个语句的使用方法。

4.实验心得

(1)个人在真正开始做实验课内容前，往往习惯于先整体的回顾复习实验所用到的原理内容与知识点，再根据实验指导书上的内容进行相关具体的操作，操作过程中如果碰到了没理解的知识点或者未讲解到的新概念，也倾向于马上在网络上查询，因此平时实验课的速度都要较慢于其它同学，需要课下花更多时间来完成实验内容与实验报告。但本次实验课的内容，在充分理解其基本原理与SQL语句的编写方法后，实验内容更像是需要我去解决的题目，先自我思考，再参考或核对实验指导书上的答案，不像以往实验重复性的操作，沉浸式的做题使自我感觉本次完成实验内容的速度快了很多。除此之外，实验中同一个题目往往有着多样性的方法，就算是遇到无法下手的题目在参考答案后也能收获对知识点充分理解的成就感；

(2)此次实验操作的内容，其实无论是原理还是具体的例子，老师大部分都已在第四章SQL语言中详细讲解过，但只有在自己真正思考编写代码，不断犯错后，才能更深刻的感受到老师说的切忌纸上谈兵，以“使用NOT EXISTS谓词查询”的题目为例，在课堂上老师讲过一道类似的题目是“查询选了所有课程的学生”，当时我听讲过后认为自己是已经掌握这种谓词查询的用法了，但在实验过程中实际要用到时却编写不出来，甚至看答案都无法完全准确理解，最后花了半个小时左右反复在CSDN上查找例子学习，才终于明白并能用文字表达出来。但这半个小时的时间，我不仅解决了这道题，而且完全理解了EXISTS的作用与使用方法、掌握了一类题型的解决方式、深刻体会了SQL语句中多层嵌套的用法与作用并见识了一种之前未曾想到的解题思路，因此我也认为这个时间花的值得；

(3)在完成所有实验内容与习题练习内容后，我认为我们最后需要达到的目的与水平，就是可以在不看任何参考资料，只给定数据库与题目的条件的情况下，依然可以编写出正确的代码。如果是真正深入带着思考去完成实验内容的话，可以发现这样的要求并不简单，而能够做到这一点，需要我们对于SQL语句有着深刻的自我理解与清晰的分析思路，以及能够做到对各个函数编写的正确，但最重要的还是需要我们反复用心的练习，这种练习不求练习的多少，但求用心与深入的挖掘，做到真正的举一反三。