MySQL实操基础全过程学习笔记（二）

四、子查询

（一）简单子查询

18.查询选修3-105课程的成绩高于‘109’号同学3-105成绩的所有同学的记录
步骤：

第一步：将109同学3-105的成绩查出来
select degree from score where sno='109'and cno='3-105';
+--------+
| degree |
+--------+
|     76 |
+--------+

第二步：利用嵌套将所有高于这个成绩的同学找出来
select *from score where degree>(select degree from score where sno='109'and cno='3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 103 | 3-105 |     92 |
| 105 | 3-105 |     91 |
| 103 | 6-166 |     85 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

第三步：但我们要的是3-105的，因此加一个and条件
select *from score where degree>(select degree from score where sno='109'and cno='3-105') and cno='3-105';
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     91 |
+-----+-------+--------+

遇到问题：
（1）之前数据插入重复了，导致一个人选修的课程有俩成绩，因此此处做下修订

delete from score where cno='3-105' and degree='78';
delete from score where cno='3-105' and degree='64';
delete from score where cno='3-105' and degree='88';

19.查询成绩高于学号109，课程号为‘3-105’的成绩的所有记录
这个题就很简单了，，，只要减去上一题的那个条件即可

select *from score where degree>(select degree from score where sno='109'and cno='3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 103 | 3-105 |     92 |
| 105 | 3-105 |     91 |
| 103 | 6-166 |     85 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

（二）year函数与带in的子查询

20.查询学号为108、101同学同年出生的所有学生的sno、sname、sbirthday

思路：

第一步：查询出101和108同学的信息
select * from student where sno='108' or sno='101';
或是
select * from student where sno in(101,108);
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 108 | 张全蛋 | 男   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

第二步：查询出他们的年份，用year
select year(sbirthday) from student where sno in(101,