SQL Aggregate函數(shù)計算從列中取得的值进倍，返回一個單一的值
- AVG()-返回平均值
- COUNT()-返回行數(shù)
- FIRST()-返回第一個記錄的值
- LAST()-返回最后一個記錄的值
- MAX()-返回最大值
- MIN()-返回最小值
- SUM()-返回總和
使用的兩張數(shù)據(jù)庫表
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
求出列的平均值的函數(shù)
實例
select site_id,count from access_log
where count > (select AVG(count) from access_log;
COUNT()
count(column_name)返回指定列的值的數(shù)目
select COUNT(column_name) from table_name;
count(*)返回表中的記錄數(shù)
select COUNT(*) from table_name
count(distinct column_name)返回指定列的不同的數(shù)目
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()
返回數(shù)值列的總數(shù)
select SUM(column_name) from table_name;
GROUP BY
由于結(jié)合聚合函數(shù)赤炒，根據(jù)一個或多個列結(jié)果集進行分組
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無法與聚合函數(shù)一起使用失球。
having子句可以讓我們篩選分組后的各族數(shù)據(jù)。
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的網(wǎng)站
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()把字段轉(zhuǎn)化為大寫
lcase()把字段轉(zhuǎn)化為小寫
select ucase(column_name) from table_name
select lcase(column_name) from table_name
