以下只针对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