开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第13天，点击查看活动详情
✔个人主页：Mr.Darcy8的掘金主页
🎉欢迎关注👀点赞👍收藏⭐留言📝以及交流人生哲理🎈学习心得🎁
前言
上期我们介绍了一些实用的查询操作，包含了简单条件查询以及带子查询的查询。本期我们继续升级，看看更多可能的查询操作变化。
前导知识
本期涉及的数据库也是有关学校学生选课和教师授课关系的，我们暂时把这个数据库命名为“jiaoxue”。
数据库中有4张表，分别是S表（学生信息表）、C表（课程信息表）、SC表（选课信息表）、T表（教师信息表）：
和之前一样，本篇教学重点在于查找的逻辑和技巧，创建表和录入数据的过程省略，以下表示例也仅仅给出前几行做演示，非全表
表头出现的中文仅仅为示例，字段名不包含出现的中文
S：
| S# 学号，下同 | SN 学生姓名 | AGE 年龄 | DEPT 系别 |
|---|---|---|---|
| S1 | 丁一 | 20 | 计算机 |
| …… |
C：
| C# 课程号 | CN 课程名 |
|---|---|
| C1 | 数据库 |
| …… |
SC：
| S# | C# | GR 成绩 |
|---|---|---|
| S1 | C1 | 80 |
| …… |
T：
| T# 教室号 | TN 教师姓名 | SAL 工资 | COMM 津贴 | C# |
|---|---|---|---|---|
| T1 | 王力 | 800 | null | C1 |
| …… |
1.简单条件查询（字符匹配）
1.1.检索姓王的教师所讲授课程的课程号以及课程名
如果按照我们之前介绍的“逻辑最自然的解题法”，把所有涉及的数据表连接起来成一个大表，然后直接找出需要的字段就行了。这样处理的话这题的逻辑其实很简单，只要把C表和T表连接起来就可以了，注意连接依据是他们的关系字段C#（他们都有这个课程号列）。
想必大家也发现了，这里有意思的是“王姓教师”，我们利用like语句即可，用替代符%表示任意长度的字符，王%就是我们需要匹配的字符。
完整代码如下：
select C.C#,CN from C,T where(
C.C#=T.C# and
TN like '王%'
)
1.2.检索张三同学所学课程的成绩、列出 SN，C#，GR
那么请自己试试这道题吧~写完看答案：
select SN,SC.C#,GR from S,SC where(
S.S#=SC.S# and
SN='张三'
)
2.简单条件查询（数值运算）
2.1.检索选修总收入超过 1000 元的教师所讲授课程的学生姓名、课程号和成绩
不难发现，我们这里需要计算和比较数据了。看起来是新东西，其实不然，sql是支持运算符的，逻辑比较符也可以直接使用。我们需要计算的“总收入”其实就可以直接把SAL工资字段和COMM津贴字段加起来，得到的结果和1000比较即可（不会吧不会有人以为工资就是总收入吧？😜）
完整代码如下：
select SN,T.C#,GR from S,SC,T where(
T.C#=SC.C# and
SC.S#=S.S# and
(SAL+COMM)>1000
)
3.进阶条件查询
3.1.检索没有选修 C1 课程且选修课程数为两门的学生的姓名和平均成绩，并按平均成绩降序排列
（涉及SubQuery子查询、反选、数值运算和判断、排序） 属于是buff拉满了，我尽量讲解清楚，其他的就可以融会贯通了
先整理条件：
- 我们要求什么：符合条件的学生姓名和平均成绩
- 怎么输出：按成绩降序排序
- 什么条件？
- 没有选C1
- 选了2门课
以此来看看我们需要的数据分别在哪里：
- 学生成绩和姓名：成绩GR在SC表，姓名SN在S表（这两个表可以通过S#学号建立联系）
- 没选C1：即选课情况C#在SC表
- 选了两门课：同上SC表
我们发现涉及的表不多，但是条件多，所以我们那种直接连成大表的方法并不能起到理想的简化作用
明确需求之后我们可以先捋一下大致逻辑。很自然的想法应该是，把符合条件的数据找到，按要求输出：在SC表中找到满足条件的同学的S#学号，并与S表建立关系得到每个S#对应的SN姓名
我们先来看看符合条件的数据怎么找到。
- 没有选C1。即“学生选的课里没有C1”，换句话说就是“C1不在学生的选课中”。这个就比较好实现了。not exists语句就可以实现了。
- 选了两门课。其实这个也是很好解决的，group by之后count计数与2进行比较即可。
不过分析倒不是这个题目的最大难点。如何把比较多量的信息有条理、有规划地用sql语句写出来，才是考验操作者水平的时候，而且很多时候sql语句不像其他的编程类语言一样，可以有许多功能性强的语句，也可以很方便地把中间变量暂存——而sql暂存为视图会比较麻烦，事后还要主动删除。所以sql往往写的是类似综合式的命令（一个大式子嵌套着所有子句）。
这道题笔者从网上找到了一个比较简洁的解法，逻辑和我的讲解基本一样，难点在于这个not exists后面跟着的s.s#=sc.s#如何理解。交给大家思考了~
select sn,avg(gr) as 'AVG' from sc inner join s on s.s#=sc.s# where (
sc.s# in(
select s# from sc where
not exists (select c# from sc where s.s#=sc.s# and C#='C1')
group by s# having count(c#)=2
)
)
group by s.sn
order by round(avg(gr),2) desc
新人上路，欢迎互相帮扶~Mr_darcy8的掘金主页
可以的话给咱点个赞呗💖
