mysql查询学习笔记【四】【子查询】

一. 子查询

首先什么是子查询？
把一个查询的结果在另一个查询中使用就叫做子查询
那么直接看题比较好理解：
查询‘95031’班学生每门课的平均成绩
那这个题我们分为以下几步：
1.找出‘95031’班的学生
2.查找学生所上的课程
3.计算课程平均分
那接下来就一个个的做：
第一步：
找出‘95031’班的学生：select * from students where class=‘95031’;
然后我们得到：

mysql> select * from students where class='95031';
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 102 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳   | 女   | 1974-06-03 00:00:00 | 95031 |
| 106 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男   | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

第二步：
查找学生所上的课程：利用学号在成绩表中筛出他们选修的课程的成绩：

select * from score where sno in(select sno from students where class=‘95031’);

mysql> select * from score where sno in(select sno from students where class='95031');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

第三步：
计算课程的平均分：利用对课程进行分组，然后avg（）计算平均分

select cno,avg(degree) from score where sno in(select sno from students where class=‘95031’) group by cno;

然后我们就得到了我们想要的答案：

mysql> select cno,avg(degree) from score where sno in(select sno from students where class='95031') group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+

综上我们可以把这个查询分为：

select cno,avg(degree) from score                   //2.在score中查询
where sno in                                       //3.利用学号找出学生对应的成绩
(select sno from students where class='95031')     //1.查询班级是‘95031’的学生的学号
group by cno;                                     //4.对课程分组然后求平均分

那接下来再看一个题：
查询选修了‘3-105’课程的同学中成绩比109号同学高的同学的所有记录
我们把这个题也分为以下几步去做：
1.查询109号同学的3-105课程的成绩
2.查询‘3-105’课程的所有同学的记录
3.筛选出第二步中的成绩比109号高的同学的记录

分完之后我们一步步做就好了：
第一步：
查询109号同学的3-105课程的成绩：这个很简单了

mysql> select degree from score where sno='109' and cno='3-105';
+--------+
| degree |
+--------+
|     76 |
+--------+

第二步：
查询‘3-105’课程的所有同学的记录：这个也好做：

mysql> select * from score where cno='3-105';
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+

第三步：
筛选出第二步中的成绩比109号高的同学的记录

select * from score where cno=‘3-105’ and degree> (select degree from score where sno=‘109’ and cno=‘3-105’);

也就是把前面两步做的给连接到一起去，看一下结果：

mysql> select * from score where cno='3-105' and degree> (select degree from score where sno='109' and cno='3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
+-----+-------+--------+

我们再把这个代码拆分一下：

select * from score                                            //2.在成绩表中查询
where cno='3-105'                                              //3.课程为‘3-105’的
and degree>                                                    //4.并且成绩要大于第一步的结果的 
(select degree from score where sno='109' and cno='3-105');   //1.查询109号学生3-105课程的成绩