子查询
定义：一个查询语句中嵌套一个或者多个查询语句，内层嵌套的查询语句称为子查询。
意义：普通多表查询先构建多张表的笛卡尔积在笛卡尔积结果上进行过滤，使用子查询能够在创建多表笛卡尔积时对子表先进行一遍过滤，这样能够有效降低笛卡尔结果集的数量，降低内存消耗，提高查询效率。
单行子查询：
子查询的结果是多行单列，子查询的结果作为主查询的where判断输入
原始表信息：
mysql> select * from teacher;
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+-----+--------+------+---------------------+-----------+-----------------+
mysql> select * from course;
+-------+-----------------+-----+
| cno | cname | tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+-------+-----------------+-----+
子查询结果是单行单列主查询的限定可以使用“=”
mysql> SELECT t.* FROM teacher AS t WHERE t.tno=(SELECT c.tno FROM course AS c WHERE c.cname="计算机导论");
+-----+--------+------+---------------------+--------+--------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+--------+------+---------------------+--------+--------------+
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
+-----+--------+------+---------------------+--------+--------------+
子查询结果是多行单列主查询的限定可以使用关键字in
mysql> SELECT t.* FROM teacher AS t WHERE t.tno IN (SELECT c.tno FROM course AS c WHERE c.cname IN ("计算机导论", "操作系统"));
+-----+--------+------+---------------------+-----------+--------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+--------+------+---------------------+-----------+--------------+
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
+-----+--------+------+---------------------+-----------+--------------+
多行子查询
多行子查询中子查询的结果返回多行多列，作为临时表来构建最终多表的查询笛卡尔积的子表。
学生表和成绩表的基表
mysql> select * from student;
+-----+--------+------+---------------------+-------+----------+
| sno | sname | ssex | sbirthday | class | romemate |
+-----+--------+------+---------------------+-------+----------+
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | 103 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 108 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | 109 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 |
| 111 | jIMe | Man | NULL | NULL | 101 |
+-----+--------+------+---------------------+-------+----------+
mysql> select * from score;
+----+-----+-------+--------+
| id | sno | cno | degree |
+----+-----+-------+--------+
| 1 | 103 | 3-245 | 86 |
| 2 | 105 | 3-245 | 75 |
| 3 | 109 | 3-245 | 68 |
| 4 | 103 | 3-105 | 92 |
| 5 | 105 | 3-105 | 88 |
| 6 | 109 | 3-105 | 76 |
| 7 | 103 | 3-105 | 64 |
| 8 | 105 | 3-105 | 91 |
| 9 | 109 | 3-105 | 78 |
| 10 | 103 | 6-166 | 85 |
| 11 | 105 | 6-166 | 79 |
| 12 | 109 | 6-166 | 81 |
+----+-----+-------+--------+
/*查询成绩大于该课程平均成绩的学生信息*/
/*步骤一：查询每门课程的平均成绩*/
mysql> SELECT sc.cno,AVG(sc.degree) AS avg_degree FROM score AS sc GROUP BY sc.cno;
+-------+------------+
| cno | avg_degree |
+-------+------------+
| 3-105 | 81.5000 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+------------+
/*步骤二：过滤成绩大于平均成绩成绩表*/
mysql> SELECT sc_last.* FROM score AS sc_last INNER JOIN (SELECT sc.cno,AVG(sc.degree) AS avg_degree FROM score AS sc GROUP BY sc.cno) AS sc_avg ON sc_last.cno=sc_avg.cno AND sc_last.degree >= sc_avg.avg_degree;
+----+-----+-------+--------+
| id | sno | cno | degree |
+----+-----+-------+--------+
| 1 | 103 | 3-245 | 86 |
| 4 | 103 | 3-105 | 92 |
| 5 | 105 | 3-105 | 88 |
| 8 | 105 | 3-105 | 91 |
| 10 | 103 | 6-166 | 85 |
+----+-----+-------+--------+
/*步骤三：将学生表和步骤二中的临时表进行多表查询*/
mysql> SELECT st.*,sc_tmp.cno,sc_tmp.degree FROM student AS st INNER JOIN (SELECT sc_last.* FROM score AS sc_last INNER JOIN (SELECT sc.cno,AVG(sc.degree) AS avg_degree FROM score AS sc GROUP BY sc.cno) AS sc_avg ON sc_last.cno=sc_avg.cno AND sc_last.degree >= sc_avg.avg_degree) AS sc_tmp ON st.sno=sc_tmp.sno;
+-----+--------+------+---------------------+-------+----------+-------+--------+
| sno | sname | ssex | sbirthday | class | romemate | cno | degree |
+-----+--------+------+---------------------+-------+----------+-------+--------+
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 3-245 | 86 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 3-105 | 92 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-105 | 88 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-105 | 91 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 | 101 | 6-166 | 85 |
+-----+--------+------+---------------------+-------+----------+-------+--------+