SQL 拥有很多可用于计数和计算的内建函数

SQL Aggregate 函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

AVG() 函数返回数值列的平均值：SELECT AVG(column_name) as name FROM table_name

COUNT() 函数返回匹配指定条件的行数:SELECT COUNT(*) FROM table_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 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;

在 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;

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;

SQL Scalar 函数

UCASE() 函数把字段的值转换为大写:SELECT UCASE(column_name) FROM table_name;#SQL Server是UPPER

LCASE() 函数把字段的值转换为小写:SELECT LCASE(column_name) FROM table_name;#SQL Server是LOWER

MID() 函数用于从文本字段中提取字符：SELECT MID(column_name,start,length) FROM table_name;

LEN() 函数返回文本字段中值的长度：SELECT LEN(column_name) FROM table_name;#MySQL为LENGTH

ROUND() 函数用于把数值字段舍入为指定的小数位数:SELECT ROUND(column_name,decimals) FROM table_name;

NOW() 函数返回当前系统的日期和时间

FORMAT() 函数用于对字段的显示进行格式化:SELECT FORMAT(column_name,format) FROM table_name;

从 “Websites” 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期

SELECT name, url, DATE_FORMAT(Now(),’%Y-%m-%d’) AS date FROM Websites;