SQL Aggregate函数计算从列中取得的值，返回一个单一的值
- AVG()-返回平均值
- COUNT()-返回行数
- FIRST()-返回第一个记录的值
- LAST()-返回最后一个记录的值
- MAX()-返回最大值
- MIN()-返回最小值
- SUM()-返回总和
使用的两张数据库表
id|name|url|alexa|country|
---|---|---|---|---|--
1 | Google | https://www.google.cm/ | 1 | USA
2 | 淘宝 | https://www.taobao.com/ | 13 | CN
3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN
4 | 微博 | http://weibo.com/ | 20 | CN
5 | Facebook | https://www.facebook.com/ | 3 | USA
7 | stackoverflow | http://stackoverflow.com/ | 0 | IND
aid|site_id|count|date
---|---|---|---|---
1 | 1 | 45 | 2016-05-10
2 | 3 | 100 | 2016-05-13
3 | 1 | 230 | 2016-05-14
4 | 2 | 10 | 2016-05-14
5 | 5 | 205 | 2016-05-14
6 | 4 | 13 | 2016-05-15
7 | 3 | 220 | 2016-05-15
8 | 5 | 545 | 2016-05-16
9 | 3 | 201 | 2016-05-17
AVG()
select AVG(column_name) from table_name
求出列的平均值的函数
实例
select site_id,count from access_log
where count > (select AVG(count) from access_log;
COUNT()
count(column_name)返回指定列的值的数目
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;
MAX()
返回指定列的最大值
select MAX(column_name) from table_name;
MIN()
返回指定列的最小值
select MIN(column_name) from table_name;
SUM()
返回数值列的总数
select SUM(column_name) from table_name;
GROUP BY
由于结合聚合函数，根据一个或多个列结果集进行分组
select column_name,aggregate_function(column_name)
from table_name
where column_name operator value
group by column_name;
实例
select site_id, sum(access_log.count) as noms
from access_log group by site_id;
多表连接
实例
select website.name,COUNT(access_log.aid) AS nums from access_log
left join websites
on access_log.site_id=websites.id
GROUP BY websites.name;
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 noms
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,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;
UCASE() LCASE()
ucase()把字段转化为大写
lcase()把字段转化为小写
select ucase(column_name) from table_name
select lcase(column_name) from table_name
