这里记录一下我学习 SQL 必知必会这门课程的体会，还是很有意思的。

学习方法

与教材那样的森罗万象的完整体系叙事方式不同，极客时间上的这些课程更像是一个提纲。虽然没有教材那样详尽严谨，但好处是能够快速地建立一个认识。毕竟很多时候我们只是为了搞工程，不是为了搞什么基础研究。

另一方面，极客时间也提供了一定的材料帮助读者进行更深入的思考，是的，我是说评论。每次我都会仔细阅读评论，评论里的话虽然基本都很短，但是什么角度都有，能够启发我去更加全面的理解和掌握知识点，非常有趣。

子查询

这是为啥？

mysql> SELECT * FROM test_tb;
+----+---------+---------+------------+---------------------+--------+---------+
| id | name    | address | birthday   | created_at          | height | team_id |
+----+---------+---------+------------+---------------------+--------+---------+
|  1 | li      | panshui | 1992-04-01 | 1992-06-01 00:00:00 |    170 |       1 |
|  2 | shen    | NULL    | 1993-09-01 | 2021-10-01 00:00:00 |    165 |       1 |
|  3 | shuange | NULL    | NULL       | NULL                |    190 |       2 |
|  4 | yaoming | NULL    | NULL       | NULL                |    200 |       2 |
|  5 | shabi   | NULL    | NULL       | NULL                |    160 |       2 |
+----+---------+---------+------------+---------------------+--------+---------+
5 rows in set (0.00 sec)

-- will error if height not in SELECT
mysql> SELECT name FROM test_tb GROUP BY team_id HAVING height > AVG(height);
ERROR 1054 (42S22): Unknown column 'height' in 'having clause'

-- executable, but the result is incorrect.
mysql> SELECT name,height FROM test_tb GROUP BY team_id HAVING height > AVG(height);
+---------+--------+
| name    | height |
+---------+--------+
| li      |    170 |
| shuange |    190 |
+---------+--------+
2 rows in set (0.00 sec)

-- avg result is correct.
mysql> SELECT name, height, AVG(height), team_id FROM test_tb GROUP BY team_id HAVING height > AVG(height);
+---------+--------+-------------+---------+
| name    | height | AVG(height) | team_id |
+---------+--------+-------------+---------+
| li      |    170 |    167.5000 |       1 |
| shuange |    190 |    183.3333 |       2 |
+---------+--------+-------------+---------+
2 rows in set (0.03 sec)

-- correct results with child query.
mysql> SELECT name, height, team_id FROM test_tb as a WHERE height > (SELECT AVG(height) FROM test_tb AS b WHERE b.team_id = a.team_id)
    -> ;
+---------+--------+---------+
| name    | height | team_id |
+---------+--------+---------+
| li      |    170 |       1 |
| shuange |    190 |       2 |
| yaoming |    200 |       2 |
+---------+--------+---------+
3 rows in set (0.01 sec)

-- wrong result when using aggregating function in SELECT.
mysql> SELECT name, AVG(height), team_id FROM test_tb as a WHERE height > (SELECT AVG(height) FROM test_tb AS b WHERE b.team_id = a.team_id)
    -> ;
+------+-------------+---------+
| name | AVG(height) | team_id |
+------+-------------+---------+
| li   |    186.6667 |       1 |
+------+-------------+---------+
1 row in set (0.00 sec)