写点什么

SQL 笔记

用户头像
越锋利
关注
发布于: 刚刚

这里记录一下我学习 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 SELECTmysql> 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)
复制代码


发布于: 刚刚阅读数: 2
用户头像

越锋利

关注

这个人懒很。 2021.10.18 加入

Just another Java developer at Shanghai.

评论

发布
暂无评论
SQL 笔记