跟他学Sql（第一天）

前言

感觉自己sql语句部分实在薄弱，平时要用sql语句的时候都是先网上百度相似的，然后自己修修改改大半天才能写出来。
就我这么个水平被面试问到不是凉凉，所以决定还是跟着别人的脚步来学习写sql。正好看到csdn上有个老哥总结了面试题目类型和答案，就决定跟着他从零开始学习sql。
从零开始复习SQL（ 连续带大家每天做几道题，进行足够的详解）

第一问

题目：查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）
分析：
首先来看需要哪些字段：

• 课程编号c_id
• 分数字段s_score
• 学生学号s_id

当我们把需要的字段列出来之后发现只需要一张score表就能满足我们的需求。
对于同表某字段比较，我们经常会把一张表自联数次。
以这道题为例，我们首先筛选出课程编号c_id为01的行，然后再筛选出c_id为02的行，然后把这两者视图通过s_id相连接，我们就得到了一张每行都包含有同一个学生c_id = 01和c_id = 02课程分数的视图，然后我们再使用where来对同行的字段进行比较，筛选出c1.s_score > c2.s_score的学生，最后返回s_id字段。

分析之后我们就不难得出如下代码：

select s1.s_id from
(select s_id,c_id,s_score from score where c_id = 01) s1
join
(select s_id,c_id,s_score from score where c_id = 02) s2
on s1.s_id = s2.s_id
where s1.s_score > s2.s_score

最后得出如下视图：

---

第二问

题目：查询平均成绩大于60分的学生的学号和平均成绩
分析：
看到题目之后还是按照上面的步骤来进行分析，首先需要的字段：

• 学号s_id
• 成绩s_score

发现还是使用一张score表就能满足我们的要求，对于求平均数我们可以使用AVG函数。又因为score表中有多行重复的s_id所以我们需要使用group by把这些相同的s_id聚合起来，最后得到如下代码：

select s_id,AVG(s_score) 
from score
where AVG(s_score) > 60
group by s_id

你以为这就结束了？你中了我的陷阱啦JOJO。
其实就是我自己写错了。
group by不能使用where来进行筛选，需要使用having。所以正确的代码是下面这样的：

select s_id,AVG(s_score) 
from score
group by s_id
having AVG(s_score) > 60

最后的结果如下：

---

第三问

题目：查询所有学生的学号、姓名、选课数、总成绩
分析：

• 学号s_id
• 姓名s_name
• 选课数SUM(c_id)
• 总成绩SUM(s_score)

这次看来一张表是不够了，需要Student表和score表，因为我们需要计算总的选课数和总成绩数，所以采用score表连接Student表，然后计算SUM(distinct s_id)和SUM(s_score)，最后使用group by字段将学生聚合起来。
这边其实对同一个学生来说c_id在score表中是不会重复的，所以其实不加distinct字段查询结果也是一样的。

select s1.s_id,s2.s_name,SUM(distinct s1.c_id),SUM(s1.s_score)
from score s1
join Student s2
on s1.s_id = s2.s_id
group by s1.s_id

最后得出结果：

---

第四问

题目：查询姓“张”的老师的个数
分析：

• 老师姓名t_name

这道题其实只用到了一个字段，所以只需要查询表teacher就行。个数一般使用COUNT(*)考虑到教师表中一个教师不会重复出现，所以使用*计数就可以。姓张其实就是使用模糊查询like '张%'（这里注意要使用单引号把张%括起来）。所以我们得到如下代码：

select count(*)
from teacher
where t_name like '张%'

---

第五问

题目：查询没学过“张三”老师课的学生的学号、姓名（仔细看一下）
分析：
前面的题目比较简单是不是感觉自己又行了？那看到这道题一定能让你醒一醒。
题目比较复杂我们慢慢分析。
首先，张三老师的课，这里有两个字段

• 老师姓名t_name
• 课程号c_id

然后学生姓名、学号以及学过的课。

• 学生姓名s_name
• 学生学号s_id
• 课程号c_id

我们先把问题简化，查询每个学生上过哪些课，以及这些课是谁上的。

每个学生上过哪些课？
这个问题我们使用表score就能知道。

那么如何获得这些课是谁上的呢？
我们的目标是获得score表中c_id对应的t_name。
显然我们无法直接通过c_id查询t_name。

那么通过c_id我们可以查询出什么呢？
在表course中，我们可以得到有关教师的字段t_id。
所以我们需要把score表和course表使用c_id连接起来，得到视图v1。
我们的目的是获得t_name，所以我们再使用字段t_id把表teacher连接到视图v1上，得到视图把v2。。
那么现在v2中有s_id、c_id、t_name，v2代表着每个学生上过哪些老师的课。

最后我们需要得到哪些学生没有学过，那么我们首先筛选出v2中学过张三老师课的学生，得到视图v3。然后判断Student表中的哪些s_id不在v3中，得到视图v4，最后返回v4中每一行的s_name，代码如下：

select s1.s_id,s1.s_name from Student s1
where s1.s_id not in
(select s2.s_id from score s2
join course s3
on s2.c_id = s3.c_id
join teacher s4
on s3.t_id = s4.t_id
where t_name = '张三')

结果如下：

---

第六问

题目：查询学过“张三”老师所教的所有课的同学的学号、姓名

第五题是不是很难，第六题更难，没想到吧JOJO。
这道题原博客主还写错了。
我们还是把问题拆分一下，把这个问题分为两个子问题：
张三老师教过哪些课？
哪些同学修过这些课？

我们把表course和表teacher通过t_id连接起来，并且使用where查询哪些课是张三老师上的，最后得到了视图v1。
v1中包含了张三老师上的所有课程的c_id。

那么问题的关键在于，修过所有这些课的同学。

如果只是修过这些课的同学，那么我们简单的在表score中筛选一下c_id在v1中的行，得到视图v2，最后在判断一下Student表中s_id在v2中的行就行了。

所有，怎么办呢？
最暴力的方法就是，先查询修过第一门课的学生，再查询出修改第二门课的学生，然后取交集，然后再查询修改第三门课的学生，再取交集，以此类推。

那么问题来了，循环怎么写？取并集怎么写？怎么定义一个视图然后循环取并集？

。。。
有没有别的方法？其实我们转换一下思路，首先查询出张三老师一共上过多少课，也就是select count(*) from v1，然后再查询出每个学生上过多少张三老师的课，然后判断一下两个count是否想等，就可以得到上过所有张三老师课的学生。

好了，现在问题转化为了每个学生上过多少张三老师的课。
这个简单，我们判断一下表score中c_id在v1中的行然后group by s_id最后取count(*)得到了视图v2。
最后筛选出count(*) = (select count(*) from v1)的行，最后查询一下s_name，代码如下：

select s1.s_id,s1.s_name from Student s1
where s_id in
(
	select v2.s_id
	from(
		select count(*) count1 from course c1
		join teacher t1
		on c1.t_id = t1.t_id
		where t1.t_name = '张三'
	) v1,
	(
		select s_id, count(*) count2 from score s2
		join course c2
		on s2.c_id = c2.c_id
		join teacher t2
		on c2.t_id = t2.t_id
		where t2.t_name = '张三'
		group by s2.s_id
	) v2
	where v1.count1 = v2.count2
)

最后结果如下：

---

后记

今天就先做到这里，一共大概有40多题，每天做6题差不多一个礼拜刚好做完。
今天的第五问和第六问比较难，做的时间比较久，但是理清了思路之后面试的时候应该能做出来吧。
应该吧。
大概吧。