sql_2

查询_2

1. 聚合函数

计算列中的值，返回一个单一的值

SUM() 求总和

MIN() 最小值

MAX() 最大值

AVG() 平均值

count() 返回行数

first()

last()

2. join (inner join,left join,right join)

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;
//把两张表拼起来查询

3. group by 语句

结合聚合函数使用

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;
//按某一字段，聚合其他字段数据后显示新表

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

4. having 子句

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;
//having的作用在于对group by后的数据进行筛选，类似where，而where不能用聚合函数 

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;