SQL拥有很多可用于计数和计算的内建函数。
SQL Aggregat函数
计算从列中取得的值,返回一个单一的值。有用的Aggregate函数如下:
AVG() - 返回平均值
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和
SQL Scalar函数
基于输入值,返回一个单一的值。有用的Scalar函数如下:
UCASE() - 将某个字段转换为大写
LCASE() - 将某个字段转换为小写
MID() - 从某个文本字段提取字符,MySql 中使用
SubString(字段,1,end) - 从某个文本字段提取字符
LEN() - 返回某个文本字段的长度
ROUND() - 对某个数值字段进行指定小数位数的四舍五入
NOW() - 返回当前的系统日期和时间
FORMAT() - 格式化某个字段的显示方式
1.1、SQL AVG() 函数
AVG()函数返回数值列的平均值。
SELECT AVG(column_name) FROM table_name
实例1
SELECT AVG(count) AS CountAverage FROM access_log;
//获取平均值。
实例2
SELECT site_id, count FROM access_log
WHERE count > (SELECT AVG(count) FROM access_log);
//选择访问量高于平均水平的“site_id”和“count”。
1.2、SQL COUNT() 函数
COUNT()函数返回匹配指定条件的行数。
① COUNT(column_name)函数返回指定列的值的数目(NULL不计入);
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;
注释:COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server,但是无法用于 Microsoft Access。
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 |
select count(count) as nums from access_log
where site_id=3; //计算表中site_id=3的总访问量。
select count(*) as nums from access_log; //计算表中的总的记录数,共9条。
selcet count(distinct site_ias nums from access_log; //计算表中不同的site_id的记录数,为5条。(去重,共有多少种不同的ID)
1.3、SQL FIRST() 函数
FIRST()函数返回指定的列中第一个记录的值。
SELECT FIRST(column_name) FROM table_name;
注释:只有MS Access支持FIRST()函数。
select spot_id from test
order by ogc_fid desc
limit 1;
//通过ogc_fid降序排列,选择spot_id中第一行。
1.4、SQL LAST() 函数
LAST()函数返回指定的列中最后一个记录的值。
SQL LAST()语法
SELECT LAST(column_name) FROM table_name;
注释:只有MS Access支持FIRST()函数。
通过降序排列,选择第一个记录,即升序排列的最后一个记录。
1.5、SQL MAX() 函数
MAX()函数返回指定列的最大值。
SELECT MAX(column_name) FROM table_name;
select max(area) from test; //从test表中选出面积area最大的值。
1.6、SQL MIN() 函数
MIN()函数返回指定列的最小值。
SELECT MIN(column_name) FROM table_name;
select min(area) from test; //从test表中选出面积area最小的值。
1.7、SQL SUM() 函数
SUM()函数返回数值列的总数。
SELECT SUM(column_name) FROM table_name;
select SUM(area) from test; //从test表中计算面积area列的总和。
1.8、SQL GROUP BY 语句
GROUP BY语句可结合一些聚合函数来使用。用于结合聚合函数,根据一个或多个列对结果集进行分组。
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
//统计access_log表中各个site_id 的访问总量。
SELECT site_id, SUM(access_log.count) as nums FROM access_log
GROUP BY site_id; //将表中相同site_id的记录分组,并将count值汇总。
1.9、SQL 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;
1.10、SQL UPPER() 函数
UCASE()函数把字段的值转换为大写。
SELECT UPPER(column_name) FROM table_name; //把表中某一列的值转化为大写。
SELECT LOWER(column_name) FROM table_name; //把表中某一列的值转化为小写。
1.11、SQL MID() 函数
MID()函数用于从文本字段中提取字符。
SELECT MID(column_name,start[,length]) FROM table_name;
参数 | 描述 |
Column_name | 必需。要提取字符的字段。 |
Start | 必需。规定开始的位置(起始值是1)。 |
length | 可选。要返回的字符数。如省略,则MID()函数返回剩余文本。 |
select substr(position,1,5) from test; //从test表中的“position”列中提取前五个字符。
1.12、SQL LEN() 函数
LEN()函数返回文本字段中值的长度。
SELECT LEN(column_name) FROM table_name;
select length(position) from test; //MySQL中函数为LENGTH(),从表test中返回position的长度。
1.13、SQL ROUND() 函数
ROUND()函数用于把数值字段舍入为指定的小数位数。
SELECT ROUND(column_name,decimals)
FROM table_name;
参数:column_name必需,为指定的字段;decimals必需,规定要返回的小数位数。
(1)ROUND(X):返回参数X的四舍五入的一个整数。
select ROUND(-1.23); //返回-1。
select ROUND(3.65); //返回4。
(2)ROUND(X,D):返回参数X的四舍五入的有D位小数的一个数字。如果D为0,结果将没有小数点或小数部分。
select ROUND(3.657,2); //返回3.66。
select ROUND(4.567,0); //返回5。
select ROUND(3.35,0); //返回3。
注意:ROUND 返回值被变换为一个BIGINT!
1.14、SQL NOW() 函数
NOW()函数返回当前系统的日期和时间。
SELECT NOW() FROM table_name;
select ogc_fid,map_id,now() from test; //选择test表中两列,及当天日期返回。
1.15、SQL FORMAT() 函数
FORMAT()函数用于对字段的显示进行格式化。
SELECT FORMAT(column_name,format) FROM table_name;