唐山官方网站建设,wordpress博客注册,wordpress 手机登录,做网站的分页查询最近在DataCamp上学习SQL#xff08;基于PostgreSQL#xff09;的课程#xff0c;本文主要记录自己易记混的点#xff0c;以便日后参考学习#xff0c;不做原理讲解。GROUP BY#xff08;分组#xff09;一般和聚合函数一起使用#xff0c;包括COUNT()#xff0c;AVG(…最近在DataCamp上学习SQL基于PostgreSQL的课程本文主要记录自己易记混的点以便日后参考学习不做原理讲解。GROUP BY分组一般和聚合函数一起使用包括COUNT()AVG()MAX()MIN()SUM()一般跟在FROM后面SELECT语句中未出现在聚合函数里的列都要出现在GROUP BY。2. WHERE/ GROUP BY/ HAVING/ ORDER BY 执行顺序SELECT 首先WHERE将最原始记录中不满足条件的记录删除(所以应该在where语句中尽量将不符合条件的记录筛选掉这样可以减少分组的次数)WHERE语句不能用聚合函数然后通过GROUP BY关键字对数据进行分组 接着根据HAVING关键字后面指定的筛选条件将分组后不满足条件的记录筛选掉HAVING可以用聚合函数如 HAVING AVG(col) 10最后按照ORDER BY语句进行排序。WHER子句在聚合前先筛选记录也就是说作用在GROUP BY和 HAVING子句前而HAVING子句在聚合后对组记录进行筛选。3. JOININNER JOIN / JOIN : only includes records in which the key is is both tables.LEFT JOINkeeps all of the records in the left table while bringing in missing values for those key field values that dont appear in the right table.RIGHT JOINkeeps all of the records in the right table while bringing in missing values for those key field values that dont appear in the left table.FULL JOINcombines a LEFT JOIN and a RIGHT JOIN, it will bring in all records from both the left and the right table and keep all of the missing values accordingly. 当用于联结两个表的字段相同时USING等价于JOIN操作中的ON如以下2个实例等价SELECT a.name, b.age FROM test AS a
JOIN test2 AS b
ON a.id b.id;等价于SELECT a.name, b.age
FROM test AS a
JOIN test2 AS b
USING(id);注细微区别在与USING(id) 在结果集中只会有一个id列。4. UNIONUNIONincludes every record in both tables but DOES NOT double count those that are in both tables.包含两个表中的每个记录但重复的行最终只会出现一次UNION ALLincludes every record in both tables and DOES replicate those are in bot tables.包括两个表中的每个记录并且保留重复行INTERSECTresults in only those records found in both of the tow tables.交集两个集中共同的部分EXCEPTresults in only those records in one table BUT NOT the other.差异两个集中不重复的部分你的点赞是我持续更新的动力~ 谢谢 Thanks♪(ω)其他SQL学习笔记 友情链接JessieYSQL学习笔记 - 窗口函数OVERzhuanlan.zhihu.comJessieYSQL学习笔记 - CTE通用表表达式和WITH用法zhuanlan.zhihu.comJessieYSQL学习笔记 - CASE WHEN THENzhuanlan.zhihu.com
