【8】查询练习：子查询

1.查询和学号11328、11427同学同年出生的所有学生的stu_num、stu_name、stu_birth；

查询11328与11427：

mysql> select * from student where stu_num in (11328,11427);
+---------+----------+---------+---------------------+-------+
| stu_num | stu_name | stu_sex | stu_birth           | class |
+---------+----------+---------+---------------------+-------+
| 11328   | DingQi   | F       | 1994-08-15 00:00:00 | 113   |
| 11427   | NanNan   | F       | 1995-10-20 00:00:00 | 114   |
+---------+----------+---------+---------------------+-------+

查询年份：使用year函数：

mysql> select year(stu_birth) from student where stu_num in (11328,11427);
+-----------------+
| year(stu_birth) |
+-----------------+
|            1994 |
|            1995 |
+-----------------+

查询同年出生所有同学：

mysql> select stu_name,stu_birth,stu_num
    -> from student
    -> where year(stu_birth)
    -> in (select year(stu_birth) from student where stu_num in (11328,11427));
+----------+---------------------+---------+
| stu_name | stu_birth           | stu_num |
+----------+---------------------+---------+
| DingQi   | 1994-08-15 00:00:00 | 11328   |
| LinJie   | 1994-06-12 00:00:00 | 11424   |
| XieZhou  | 1995-03-11 00:00:00 | 11425   |
| NanNan   | 1995-10-20 00:00:00 | 11427   |
+----------+---------------------+---------+

2.查询教师姓名为ShaoGuoYing任课课程的学生成绩：

查询教师编号：

mysql> select tea_num from teacher where tea_name='ShaoGuoYing';
+---------+
| tea_num |
+---------+
| 0438    |
+---------+

教师所对应的课程号：

mysql> select cour_num from course
    -> where tea_num = ( select tea_num from teacher where tea_name='ShaoGuoYing');
+----------+
| cour_num |
+----------+
| 1-245    |
+----------+

查询课程成绩：

mysql> select * from score
    -> where cour_num=(select cour_num from course where tea_num=(select tea_num from teacher where tea_name='ShaoGuoYing'));
+---------+----------+--------+
| stu_num | cour_num | degree |
+---------+----------+--------+
| 11423   | 1-245    |     84 |
| 11426   | 1-245    |     61 |
| 11427   | 1-245    |     78 |
+---------+----------+--------+

3.查询选修某课程的同学人数多于2人的教师姓名:

mysql> select cour_num,count(*) from score group by cour_num;
+----------+----------+
| cour_num | count(*) |
+----------+----------+
| 1-245    |        3 |
| 2-271    |        3 |
| 3-105    |        1 |
| 4-321    |        1 |
+----------+----------+

人数大于2的课程：

mysql> select cour_num from score group by cour_num having count(*) > 2;
+----------+
| cour_num |
+----------+
| 1-245    |
| 2-271    |
+----------+

查找课程教师编号：

mysql> select tea_num from course where cour_num in (select cour_num from score group by cour_num having count(*) > 2);
+---------+
| tea_num |
+---------+
| 0435    |
| 0438    |
+---------+

查询教师姓名：

mysql> select tea_name from teacher
    -> where tea_num in (select tea_num from course where cour_num in (select cour_num from score group by cour_num having count(*) > 2));
+-------------+
| tea_name    |
+-------------+
| LiMei       |
| ShaoGuoYing |
+-------------+

 一步一步查询再嵌套。