SQL语句(4)：查询函数

来源：互联网发布：淘宝怎么取消代销合作编辑：程序博客网时间：2024/06/06 09:00

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

聚合函数（常用）

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：根据一列或多个列对数据进行分组

select [au_name], count(*) as 'books_nums' from title_authors group by [au_name];--列出每位作者所写书的数量(根据[au_id]进行分组:将相同的au_id分为一组,统计其行数)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不能与聚合函数一起使用的问题

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，查出购买两种以上(包含两种)商品的顾客

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.740select [title_name], [sales], getdate() as 'time' from titles;

convert()：格式化时间

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

可以使用的 style 值：

Style IDStyle 格式100 或者 0mon dd yyyy hh:miAM （或者 PM）101mm/dd/yy102yy.mm.dd103dd/mm/yy104dd.mm.yy105dd-mm-yy106dd mon yy107Mon dd, yy108hh:mm:ss109 或者 9mon dd yyyy hh:mi:ss:mmmAM（或者 PM）110mm-dd-yy111yy/mm/dd112yymmdd113 或者 13dd mon yyyy hh:mm:ss:mmm(24h)114hh:mi:ss:mmm(24h)120 或者 20yyyy-mm-dd hh:mi:ss(24h)121 或者 21yyyy-mm-dd hh:mi:ss.mmm(24h)126yyyy-mm-ddThh:mm:ss.mmm（没有空格）130dd mon yyyy hh:mi:ss:mmmAM131dd/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.46select 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

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

0 0