SQL语句(4)：查询函数

以下只针对SQL Server，其它数据库类型可能会不支持某些函数或函数名不同。

聚合函数

聚合函数又称合计函数、组函数、多行函数，它通常是用来获得字段的最大值max()、最小值min()、平均值avg()、值总和sum()、记录条数count()等结果的：

select max([price]) as 'max price' from titles;--最大值
select min([price]) as 'min price' from titles;--最小值
select sum([price]) as 'total price' from titles;--求和
select avg([price]) as 'avg price' from titles;--平均值
select count([title_id]) from titles;--行数
select [price] from titles where [price] > (select avg([price]) from titles);

大多数聚合函数会忽略空值，但count()会统计空值。

count与distinct的配合使用：有时候希望count()统计出来的个数不含重复的值，如

select count(distinct [name]) as 'authors_count' from authors; --计算作者的个数，相同作者名肯定只算一个作者

Group by：根据一列或多个列对相同的值进行分组

group by一般与聚合函数一起使用，它用来指定聚合函数的作用范围。通常聚合函数是将要查询的所有记录归为一组来计算其结果，利用group by可以将要计算的记录分组进行计算。如select count(*) from person group by name;是根据name分组，会将name值相同的划分为一组。

select [au_name], count([an_name]) as 'books_nums' from title_authors group by [au_name];
--列出每位作者所写书的数量(根据[au_name]进行分组: 将相同的au_name分为一组,统计其行数)
select [type], sum([sales]) as 'sales_total' from titles group by [type];
--列出每类图书的总销量
select [type], sum([sales]) as 'sum_sales' from titles where price >= 13 group by [type] order by 'sum_sales';
--列出每种图书的总销量,按照升序排列,去掉了价格低于13元的图书
select [pub_id], [type], count(*) as 'book_nums' from titles group by [pub_id], [type];
--列出每个出版社的每种图书的数量(根据[pub_id]和[type]对结果集进行分组：将相同的pub_id+type分为一组,统计其行数)

group by会对空值进行分组。

having：相当于where，解决了聚合函数不能作为where的条件的限制，只有满足having后表达式的分组才会被选出来，即having会对组进行过滤。

select * from student_table group by teacher having count(*) > 2;
-- 输出学生信息，如果该学生的老师拥有2个以上学生的话
select [au_name], count(*) as 'books_num' from title_authors group by [au_name] having count(*) > 3;
-- 列出写过3本以上书的作者
select [type], avg([price]*[sales]) as 'avg_reven' from titles group by [type] having avg([price]*[sales]) > 1000;
-- 列出平均收入大于1000的图书类型

现有以下表shopping，查出购买两种或两种以上商品类型（goodsname）的顾客

customer  goodsname num

A                  G1                 1

B                 G1                 2

C                 G1                 2

B                 G2                 2

C                 G1                 2

A                 G3                  1

select [customer] from shopping group by [customer] 
       having count(distinct [goodsname]) >= 2;

如果上面的查询语句不使用distinct去除重复的话则顾客C也会被列出，但顾客C只是买了两次同样的商品。

getdate()：获取当前时间

select getdate(); --输出为：2014-10-23 16:01:14.740
select [title_name], [sales], getdate() as 'time' from titles;

convert()：格式化时间

select convert(varchar(30), getdate(), 120);-- yy-mm-dd hh:mi:ss
select convert(varchar(15), getdate(), 111);-- yy-mm-dd
select convert(varchar(30), getdate(), 108);-- hh:mi:ss

可以使用的 style 值：

Style ID	Style 格式
100 或者 0	mon dd yyyy hh:miAM （或者 PM）
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
109 或者 9	mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
113 或者 13	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120 或者 20	yyyy-mm-dd hh:mi:ss(24h)
121 或者 21	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM

datepart()：只获取时间的一部分

select [title_name], datepart(yy, [pubdate]) from titles;--只保留年
select [title_name], datepart(mm, [pubdate]) from titles;--只保留月
select [title_name], datepart(dd, [pubdate]) from titles;--只保留日

datepart 参数可以是下列的值：

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

dateadd()：将时间字段加上或减去指定的时间段

select [title_name], dateadd(dd, 2, [pubdate]) from titles;--出版日期加上两天
select [title_name], dateadd(yy, -1, [pubdate]) from titles;--出版日期减去一年

datediff()：获取两个时间的时间间隔

select datediff(mm, '2000-1-1', '2000-5-10');--月为单位
select datediff(ww, '2000-1-1', '2000-1-10');--周为单位
select datediff(dd, '2000-1-1', '2000-1-10');--天为单位
select datediff(hh, '2000-1-1', '2000-1-10');--小时为单位
select datediff(mi, '2000-1-1', '2000-1-10');--分钟为单位
select datediff(ss, '2000-1-1', '2000-1-10');--秒为单位

len()：获取字符字段的长度

select [title_name], len([title_name]) as 'name_length' from titles;

left()/right()：从字符字段的左边/右边开始，提取指定个数的字符

select left([type], 3) from titles;

substring()：从字符字段中提取指定个数的字符

select substring([type], 1, 3) from titles;--从type字段中第一个字符开始，一共提取3个字符

upper()/lower()：将字符字段转化为大写/小写

select [title_name], upper([type]) as 'TYPE' from titles;
select [title_name], lower([type]) as 'type' from titles;

round()：将数值字段的小数四舍五入或直接舍去

select round(123.456, 2);--四舍五入至小数点第二位,结果为123.46
select round(123.456, 2, 0);--相当于上面的select round(123.456, 2)
select round(123.456, 2, 1);--直接舍去至小数点第二位，结果为123.45

isnull():处理空值，如果字段为空则用指定的值替换

select [price]*[sales] from titles;--对空值做加减乘除等操作得到的仍是空值,故若price或sales为空，则结果也为空
select isnull([price], 0)*isnull([sales], 0) from titles;--若price为空则将其替换为0，若sales为空则将其替换为0

MySQL中的一些函数与SQL Server不太相同，如：

select char_length(name) from person; -- name字段长度
select md5('test'); -- MD5值
select if(expr1, expr2, expr3); -- 如果expr1为true且不为null或0，则返回expr2，否则返回expr

select now(); -- 当前日期和时间
select curdate(); -- 当前日期
select curtime(); -- 当前时间
select adddate('1988-01-01', interval 12 YEAR);  -- 时间加12年

select isnull(null); -- 判断是否为null
select ifnull(name, '无名氏') from person; -- 为null的话用指定内容替换
select nullif(name1, name2); -- 相等返回null，否则返回name1

以上日期格式部分转自http://www.w3school.com.cn/sql/sql_datatypes.asp