SQL高级查询案例讲解与分析

由于在大学生活中接触到了数据库开发与管理这门课程，于是就相对的对实验指导书内的内容进行了实验
本实验的系统是windows10系统，使用的软件是sql serve 2008（其他sql软件也该也可以，最新出的sql serve 2019 有6个月的试用期，对于短期接触来说的人，这是一个比较好的福利）！
下载网址：
https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads
选择免费试用版，如如下载出现问题，请大家可以咨询度娘哦！因为本人就是这么做的，大佬们的经验还是很管用的！
在学习高级查询之前，首先要掌握一些基本查询的相关知识
大家可以看一下博客：（沿用大佬们的）
https://blog.csdn.net/weixin_45566037/article/details/101306805?utm_source=app
希望对你们学习基本查询以及高级查询有所帮助！
下面我们从实验指导书入手！
查询语句的基本格式为：

SELECT〈目标列组〉
FROM〈数据源〉
[WHERE〈元组选择条件〉]
[GROUP BY〈分列组〉[HAVING 〈组选择条件〉]]
[ORDER BY〈排序列1〉〈排序要求1〉 [，…n]](1) SELECT子句

[ ]内的可以省略，也就是说在语句中select以及from是必不可少的成分！
讲解基本格式中的代码：
(1)SELECT子句
查询结果用作查询条件集的目标列。子查询也称为内部查询或嵌套查询。它是一个SELECT查询嵌套在SELECT、INSERT、UPDATE、DELETE或其他子查询中。任何使用表达式的地方都可以使用子查询。
(2) FROM子句
FROM子句用于指明查询的数据源，数据源为多张表，可以通过两表连接JOIN…ON <连接条件>，并实现多表连接；或直接用“，”分隔列出数据表，连接条件要在WHERE子句中体现。
(3) WHERE子句
WHERE子句通过条件表达式描述关系中元组的选择条件。
(4) GROUP BY子句
GROUP BY子句的作用是按分组列的值对结果集分组。
(5) ORDER BY子句ORDER BY子句的作用是对结果集进行排序。
在数据库的表链接中，我们可以利用join实现连接！
连接方式：

下面我们开始讲解实验🧪：
实验任务要求：
(1)启动SQL Server Management Studio， 完成连接数据库服务器登录，进入SQL Server Management Studio界面，查看有没有学生数据库（含三张数据表全部记录数据），若没有则需要创建学生数据库和输入三张数据表的全部记录。【运行上次实验保存的代码】或【附加学生数据库】或【打开.sql文件执行相关代码】。
要求在实验报告中体现（1）复制题目（2）复制该题查询语句（代码）（3）该题显示结果截图。
任务如下：
1.查询学号为2020102的学生所选的全部课程的课程名和成绩。
2.查询没有成绩的课程号，课程名。
3.查询选修了101课程并且成绩在80分以上的所有学生（学号、姓名）。
4.查询与“王玲玲”在同一专业学习的学生姓名。
5.查询选修了课程名为“计算机文化基础”的学生学号和姓名。
6.查询每门课程的最高分（课程号、课程名、最高成绩）。
7.查询所有有选课记录的学生的情况（姓名，课程名，成绩）。
8.统计每个学生每个学期的最低分、最高分及平均分（姓名、开课学期、最低分、最高分、平均分）。
9.查询平均成绩在60分以上的学生姓名。
10.查询每个学生的姓名、所选修的课程名及成绩。
11.查询选修课程数在两门以上的学生姓名。（用连接查询和嵌套查询两种方法实现）
12.查询所有学生都没有选修的课程名称。
13.查询每个学生的平均成绩（显示学号、姓名和平均成绩），并按平均成绩的降序排序。
14.查询每个同学的最高分，要求显示学号、姓名和分数等信息。
15.查询所有课程成绩都及格的学生姓名。
16.查询成绩在所有同学成绩平均分之上的学生姓名。
17.查询没有选修“操作系统”课程的学生姓名。
18.查询同时选修了“101”和“208”课程的学生姓名。
19.查询每个同学成绩及格的课程门数。（姓名、及格课程数）
20.查询选修了课程名为“计算机文化基础”或“数据结构”的学生学号和姓名。（用联合查询实现）
注意⚠️：下面是我的代码以及结果的演示！！！
(1)启动SQL Server Management Studio， 完成连接数据库服务器登录，进入SQL Server Management Studio界面，查看有没有学生数据库（含三张数据表全部记录数据），若没有则需要创建学生数据库和输入三张数据表的全部记录。【运行上次实验保存的代码】或【附加学生数据库】或【打开.sql文件执行相关代码】。
(1)由于在上一个实验中我们建立学生student2数据库，这次也需要用到，于是执行：USE student2同来选中student2数据库，如图：

下面我们就开始我们的任务！
要求在实验报告中体现（1）复制题目（2）复制该题查询语句（代码）（3）该题显示结果截图。
1.查询学号为2020102的学生所选的全部课程的课程名和成绩。
代码如下：

SELECT 学号,课程号,成绩 INTO XSQK1 
FROM xscj 
WHERE 学号=2020102 
SELECT * FROM XSQK1 
SELECT 课程号,课程名 FROM xskc where exists(
SELECT 课程号 FROM XSQK1 WHERE xskc.课程号=XSQK1.课程号  
)

结果为：

我们不难发现，这样并不能实现它们显示在一张表内，而是完成了分表显示，这样较为繁琐！因此我后续运用了join 将两张表链接在一起，实现查询！
代码如下：

sqlselect xscj.学号,xscj.课程号,xskc.课程名,xscj.成绩
from xscj join xskc
on xscj.课程号=xskc.课程号
where xscj.学号=2020102

结果为：

2.查询没有成绩的课程号，课程名。
在这里我们运用not exists（不存在）来查询没有成绩的课程号！
代码如下：

SELECT 课程号,课程名 FROM xskc where not exists(
SELECT 课程号 FROM xscj WHERE xskc.课程号=xscj.课程号  
)

结果为：

这里最重要的一点就是not exists的运用！
3.查询选修了101课程并且成绩在80分以上的所有学生（学号、姓名）。
在这里我们运用了exists，存在的意思！用于查询一个表里的内容在第二个表中符合条件的值。
代码如下：

SELECT 学号,姓名 FROM xsqk where exists(
SELECT 课程号,成绩,学号 FROM xscj where 课程号=101 and 成绩>80 and xsqk.学号=xscj.学号
)

实验结果如下：

4.查询与“王玲玲”在同一专业学习的学生姓名。
在这道题目中我才用了2个方法，在这里建议大家使用第三种，方便且快捷，运用连接！同时在这里还涉及到表别名用于同一张表的调用数据！
代码如下：

--方法一：创建新表链接
SELECT 专业,姓名 into xsqk2  
FROM xsqk 
where 姓名='王玲玲'
SELECT 姓名 FROM xsqk WHERE exists(
SELECT 专业,姓名 FROM xsqk2 where xsqk2.专业=xsqk.专业
)
--方法二：表连接
select b.姓名 
from xsqk As a,xsqk As b
where a.姓名='王玲玲' and b.专业=a.专业

结果为：
方法一：

方法二：

这里较为重要的是表别名的运用，对于自己表调用表有较大的用处！
5.查询选修了课程名为“计算机文化基础”的学生学号和姓名。
代码如下：

--在表中可以明显得到”计算机文化基础“的课程编码为.
--SELECT 课程号,课程名FROM xskc WHERE 课程名='计算机文化基础'
SELECT xscj.学号,xscj.课程号,xsqk.姓名 
from xscj inner join xsqk 
on xscj.学号=xsqk.学号
WHERE xscj.课程号=101

结果如下：

6.查询每门课程的最高分（课程号、课程名、最高成绩）。
在实际情况中，本人用了3种方法，但前两种较为繁琐，因此，直介绍我的第三种方法！
代码如下：

select xscj.课程号,xskc.课程名,MAX(xscj.成绩) As 最高成绩
from xscj join xskc
on xscj.课程号=xskc.课程号 
GROUP BY xscj.课程号,xskc.课程名

结果：

这里运用join！
7.查询所有有选课记录的学生的情况（姓名，课程名，成绩）。
代码如下：

SELECT xscj.学号,xscj.课程号,xscj.成绩,xskc.课程名,xsqk.姓名
from xscj join xskc on  xscj.课程号=xskc.课程号
join xsqk on  xscj.学号=xsqk.学号

结果为：

8.统计每个学生每个学期的最低分、最高分及平均分（姓名、开课学期、最低分、最高分、平均分）。
代码如下：

SELECT xsqk.姓名,xskc.开课学期,MAX(xscj.成绩) As 最高分,MIN(xscj.成绩) As 最低分,AVG(xscj.成绩) As 平均分
FROM xscj join xskc on xscj.课程号=xskc.课程号
join xsqk on xscj.学号=xsqk.学号
GROUP BY xsqk.姓名,xskc.开课学期

结果为：

9.查询平均成绩在60分以上的学生姓名。
代码如下：

SELECT xsqk.姓名,AVG(xscj.成绩) As 平均分
FROM xsqk join xscj
on xsqk.学号=xscj.学号
group by xsqk.姓名 HAVING AVG(xscj.成绩)>60

结果如下：

10.查询每个学生的姓名、所选修的课程名及成绩。
代码如下：

select xsqk.姓名,xskc.课程名,xscj.成绩
from xscj join xskc on xskc.课程号=xscj.课程号
join xsqk on xscj.学号=xsqk.学号
--group by xsqk.姓名

结果如下：

11.查询选修课程数在两门以上的学生姓名。（用连接查询和嵌套查询两种方法实现）
代码如下：

--连接查询
SELECT xsqk.姓名,COUNT(xscj.课程号) AS 选修课程数
from xsqk join xscj
on xsqk.学号=xscj.学号
group by xsqk.姓名 having COUNT(xscj.课程号)>=2
--嵌套查询
select 姓名 from xsqk where exists(
select COUNT(xscj.课程号) AS 选修课程数 from xscj where xsqk.学号=xscj.学号 group by xscj.学号 having COUNT(xscj.课程号)>=2
)

结果如下：
链接查询结果为：

嵌套查询结果为：

连接查询运用了join，嵌套查询运用了exists！
12.查询所有学生都没有选修的课程名称。
代码如下：
这里主要运用not exists！

select 课程名 from xskc where not exists(
select 课程号 from xscj where xskc.课程号=xscj.课程号 
)

结果为：

13.查询每个学生的平均成绩（显示学号、姓名和平均成绩），并按平均成绩的降序排序。
代码如下：

select xscj.学号,xsqk.姓名,AVG(xscj.成绩) As 平均成绩
from xscj join xsqk
on xscj.学号=xsqk.学号
group by xscj.学号,xsqk.姓名
--ORDER BY〈排序列〉〈排序要求〉
ORDER BY 平均成绩 DESC

结果如下：

降序用 order by 语句 desc表示降序！
14.查询每个同学的最高分，要求显示学号、姓名和分数等信息。
代码如下：

SELECT xscj.学号,xsqk.姓名,xsqk.系别,xsqk.专业,max(xscj.成绩) As 最高分
from xscj join xsqk
on xscj.学号=xsqk.学号
group by xscj.学号,xscj.学号,xsqk.姓名,xsqk.系别,xsqk.专业

结果为：

15.查询所有课程成绩都及格的学生姓名。
代码为：

select xsqk.姓名,MIN(xscj.成绩) As 最低成绩
from xsqk join xscj
on xscj.学号=xsqk.学号
where xscj.成绩>60
group by xsqk.姓名

结果为：

16.查询成绩在所有同学成绩平均分之上的学生姓名。
代码为：
这道题在运用和理解上有较大难度，今天会与老师交流，后续会进行改进！
在这里采用先生成平均分表，得到平均数，再对其进行调用！

select xsqk.系别,AVG(xscj.成绩) AS 平均
from xsqk join xscj
on xsqk.学号=xscj.学号
group by xsqk.系别
这段用来提取所有成绩的平均分为75
select xsqk.姓名,xscj.成绩 
from xscj join xsqk
on xscj.学号=xsqk.学号
where xscj.成绩>75
group by xsqk.姓名,xscj.成绩

运行结果为：

17.查询没有选修“操作系统”课程的学生姓名。
代码如下：

select xsqk.姓名 from xsqk where not exists( 
select xscj.学号,xsqk.姓名,xscj.课程号
from xsqk join xscj
on xsqk.学号=xscj.学号
where xscj.课程号='210'
)

结果为：

18.查询同时选修了“101”和“208”课程的学生姓名。
代码如下：

select xsqk.姓名
from xscj join xsqk
on xscj.学号=xsqk.学号
where 课程号=101 or 课程号=208
group by xsqk.姓名 having COUNT(*)=2

由于无法同时用and使得结果输出，所以调用了count函数来计算101与208出现的次数，和为2，就输出答案。
结果为：

19.查询每个同学成绩及格的课程门数。（姓名、及格课程数）
代码为：

select xsqk.姓名,COUNT(xscj.成绩) as 及格课程数
from xsqk join xscj
on xsqk.学号=xscj.学号
where xscj.成绩>=60
group by xsqk.姓名

结果为：

20.查询选修了课程名为“计算机文化基础”或“数据结构”的学生学号和姓名。（用联合查询实现）
代码为：

select xscj.学号,xsqk.姓名,xskc.课程名
 from xscj join xskc
 on xscj.课程号=xskc.课程号
 join xsqk 
 on xscj.学号=xsqk.学号
 where xskc.课程名='计算机文化基础' or xskc.课程名='数据结构'

结果为：

这样我们实验例题的讲解就到此结束了！
感谢大家！支持和鼓励！
每天都要进步一点！