SQL(2)

SQL中一些常用的函数：
AVG函数：返回数值列的平均值。
例：select AVG（count）As countAverage from access_log；
Select site_id,count from access_log where count>(select AVG(count) from access_log);

COUNT():返回匹配指定条件的行数
COUNT(*)返回表中的记录数；
COUNT(DISTINCT column_name)from table_name；返回指定列不同值的记录数。
例:select count(count) as nums from access_log where site_id=3;
Select count(*) as nums from access_log;
Select count(distinct site_id) from access_log

选取表中name列的第一个记录的值：
Select name as firstSite from websites limit 1；
选取表中name列中最后一个记录的值：
Select name from websites
Order by id desc

MAX()返回指定列的最大值
Select MAX(alexa) AS max_alexa from websites;

MIN()返回指定列的最小值
Select MIN（alexa）AS min_alexa from websites;

SUM()返回数值列的总和
Select SUM(alexa) AS sum_alexa from websites;

GROUP BY语句：用于结合聚合函数，根据一个或多个列对结果集进行分组。
无法与where一起使用。
例：
Select site_id ,sum(access_log.count) AS sums
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 = wedsites.id
group by websites.name;

HAVING ：where关键字无法与聚合函数一起使用，having子句让我们筛选分组后的各组数据；
Select websites.name sum(access_log.count) from access_log inner join websites on access_log.site_id = websites.id group by websites.name having sum(access_log.count)>200;
`
Select websites.name sum(access_log.count)
from access_log inner join websites on access_log.site_id = websites.id
where websites.alexa<200
group by websites.name
having sum(access_log.count)>200

UCASE()把字段的值转换为大写
Select UCASE(name) AS site_title from websites;
LCASE()把字段的值转换为小写
Select LCASE(name) AS site_title from websites;

MID()用于从文本字段中提取字符
Select MID(name,1,4) AS shortTitle
From wenbsites;
参数：name：要提取的字符字段 示例中name
Start：开始位置，起始值为1 1
Length：要返回的字符数，如果省略，返回剩余文本。 4

LEN()返回文本字段中值的长度。
Select name，LEN（url） as LengthOfURL from wensites;

ROUND()函数把数值字段舍入为指定的小数位数。
示例：
Select ROUND(-1.23); 结果-1
Select ROUND(-1.58); 结果-2
Select ROUND(1.58); 结果2
Select ROUND(1.298,1); 结果1.3

NOW()函数返回当前系统的日期和时间
示例：
select name，url，now() AS date from websites;
结果：

FORMAT()用于对字段的显示进行格式化
Select FORMAT(column_name,format) from teble_name;
Select name,url,DATE_FORMATE(NOW(),’%Y-%M-%D’) AS date
From websites