SQL进阶查询语句实战（附例题）

前言

上期我们介绍了一些实用的查询操作，包含了简单条件查询以及带子查询的查询。本期我们继续升级，看看更多可能的查询操作变化。

前导知识

本期涉及的数据库也是有关学校学生选课和教师授课关系的，我们暂时把这个数据库命名为“jiaoxue”。

数据库中有4张表，分别是S表（学生信息表）、C表（课程信息表）、SC表（选课信息表）、T表（教师信息表）：

和之前一样，本篇教学重点在于查找的逻辑和技巧，创建表和录入数据的过程省略，以下表示例也仅仅给出前几行做演示，非全表

表头出现的中文仅仅为示例，字段名不包含出现的中文

S：

C：

SC：

T：

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