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 或者 0mon dd yyyy hh:miAM （或者 PM）
101mm/dd/yy
103dd/mm/yy
<
105dd-mm-yy
106dd mon yy
107Mon dd, yy
108hh:mm:ss
109 或者 9mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110mm-dd-yy
111yy/mm/dd
112yymmdd
113 或者 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:(24h)
126yyyy-mm-ddThh:（没有空格）
130dd mon yyyy hh:mi:ss:mmmAM
131dd/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()：将字符字段转化为⼤写/⼩写
distinct查询
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
以上⽇期格式部分转⾃www.w3school/sql/sql_datatypes.asp

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。