SQL语句（下）-CSDN博客

本文链接：https://blog.csdn.net/qq_40934293/article/details/120841149

本文详细介绍了SQL中的聚合函数，如AVG(), COUNT()以及GROUP BY和HAVING子句的用法。通过实例展示了如何计算平均值、计数、分组统计以及筛选分组数据。还探讨了EXISTS运算符在查询中的应用，用于判断子查询是否存在记录。

摘要由CSDN通过智能技术生成

摘自菜鸟教程

1.AVG()函数

AVG() 函数返回数值列的平均值。

SELECT AVG(COLUMN_NAME) FROM TABLE_NAME;

从 "access_log" 表的 "count" 列获取平均值：

SELECT AVG(count) AS CountAverage FROM access_log;

选择访问量高于平均访问量的 "site_id" 和 "count"：

SELECT site_id, count FROM access_log WHERE count > (SELECT AVG(count) FROM access_log);

2.SQL COUNT() 函数

COUNT() 函数返回匹配指定条件的行数。

COUNT(COLUMN_NAME)函数返回指定列的值的数目，NULL不计入；

SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME;

COUNT(*)函数返回表中的记录数；

SELECT COUNT(*) FROM TABLE_NAME;

COUNT(DISTINCT COLUMN_NAME)函数返回指定列的不同值的数目：

SELECT COUNT(DISTINCT COLUMN_NAME) FROM TABLE_NAME;

计算 "access_log" 表中 "site_id"=3 的总访问量
 
SELECT COUNT(count) AS nums FROM access_log WHERE site_id = 3;

 "access_log" 表中总记录数：

SELECT COUNT(*) AS nums FROM access_log;

计算 "access_log" 表中不同 site_id 的记录数：

SELECT COUNT(DISTINCT site_id) AS nums FROM access_log;

3.SQL GROUP BY语句

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组

SELECT COLUMN_NAME, aggregate——function(column_name) FROM TABLE_NAME WHERE column_name operator value GROUP BY column_name;

统计 access_log 各个 site_id 的访问量：

SELECT site_id, SUM(access_log.count) AS nums FROM access_log GROUP BY site_id;

统计有记录的网站的记录数量：

SELECT Websites.name, COUNT(access_log.count) AS nums FROM access_log LEFT JOIN Websites ON access_log.site_id = Websites.id GROUP BY Websites.name;

4.SQL HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

SELECT COLUMN_NAME, aggregate——function(column_name) FROM TABLE_NAME WHERE COLUMN_NAME operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value;

查找总访问量大于 200 的网站

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log INNER JOIN Websites ON access_log.site_id=Websites.id) GROUP BY Websites.name HAVING SUM(access_log.count)>200;

查找总访问量大于 200 的网站，并且 alexa 排名小于 200

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM Websites INNER JOIN access_log ON  Websites.id=access_log.site_id WHERE Websites.alexa<200 GROUP BY Websites.name HAVING SUM(access_log.count)>200;

5.SQL EXISTS

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

SELECT COLUMN_NAME(S) FROM TABLE_NAME WHERE EXISTS (SELECT COLUMN_NAME FROM TABLE_NAME WHERE condition);

查找总访问量(count 字段)大于 200 的网站是否存在。

SELECT Websites.name, Websites.url FROM Websites WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count>200);