sql server 常用函数

常用函数

sql server 为T-SQL语言提供了大量的系统函数，使用户对数据库查询和修改时更加方便，同时还允许用户使用自定义函数。本章介绍常用的聚合，数学，字符串，日期和时间函数以及如何自定义函数。

为了便于测试函数先创建数据库

use masterif exists(select * from sysdatabases where name ='test2014129') drop database test2014129create database test2014129on primary(name='test2014129',filename='G:\sql08\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2014129.mdf',size=5,filegrowth=1)log on (name='test2014129log',filename='G:\sql08\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2014129log.ldf',size=5,filegrowth=1)

聚合函数

聚合函数经常与select语句的group by子句一起使用，所有的聚合函数均为确定性函数，也就是说只要使用一组特定的输入值调用聚合函数，该函数总是返回特定的值。它可以对一组值执行计算，并返回单个值。

除了count函数以外，其他聚合函数会忽略空置。

以下用代码测试这些函数，创建学生表

/*id-姓名-年龄-分数-年级-班级*/

use test2014129if exists(select * from sysobjects where name ='stuinfo') drop table stuinfocreate table stuinfo(id int identity(1,1) primary key,name varchar(12) not null,age int constraint CK_AGE CHECK(age>=18 and age<=22) default(18) not null,score real constraint CK_SCORE check(score>=0 and score<=100) not null,grade int not null,class int not null)

插入数据

insert into stuinfo(name,age,score,grade,class)select 'czk',18,80,1,1 unionselect 'czk1',18,50,2,2 unionselect 'czk2',19,60,1,3 unionselect 'czk3',20,80,2,1 unionselect 'czk4',21,20,1,3 unionselect 'czk5',22,40,2,1 unionselect 'czk6',19,50,3,2 unionselect 'czk7',19,60,3,1 

使用函数查询

/*AVG函数查询每个年级的平均成绩*/select grade as '年级',AVG(score)as '平均成绩' from stuinfo group by grade/*COUNT函数查询记录总数*/select count(*) as 记录总数 from stuinfo /*MAX查询班级最高分*/select MAX(score) as 最高分 from stuinfo/*MIN查询班级最低分*/select MIN(score) as 最低分 from stuinfo/*SUM查询总分*/select SUM(score) as 总分 from stuinfo

数学函数

SQL Server提供了20多个用于处理整数与浮点值的数学函数。这些数学函数包括可在任何编程语言中发现的所有常用函数

select CEILING(43.2)--返回44select FLOOR(43.9)--返回43select SQRT(100)--10

字符串函数

/*查看a的ascii代码*/select ASCII('a')--返回97/*查看ascii代码对应的字符*/select CHAR(97)--返回a/*返回从左边开始2个字符长度的字符串*/select left('中国people',2)--返回中国/*返回字符数*/select LEN('中国people ') as '字符数'--返回8/*LTRIM删除前导功空格字符串*/select LTRIM(' 中国People')/*REPLACE*/select REPLACE('计算机大展','展','赛')--计算机大赛/*RTRIM删除字符串右边尾部空格*/select RTRIM(' 中国People ')/*SPACE打印空格*/select 'a'+SPACE(2)+'b'--a  b/*STR int转varchar*/select STR(120.123,3,0)--120/*SUBSTRING*/select SUBSTRING('abcd',1,2)--ab/*STUFF 从第三个位置开始删除2个字符后插入新的字符串*/select STUFF('计算机天地',3,2,'图书')--计算机图书/*查找字符p在字符串的第几个位置*/select CHARINDEX('p','中国people')/*查找从第四个字符开始后的p在字符串的第几个位置*/select CHARINDEX('p','中国people',4)

日期和时间函数

/*查询当前系统日期*/select GETDATE()/*DATEADD将指定的数值添加到指定的日期部分后的日期*/select DATEADD(YY,1,'00-1-1')--增加一年select DATEADD(MM,4,'00-1-1')--增加4个月select DATEADD(DD,-1,'00-1-1')--减少一天（查询昨天）/*DATEDIFF两个日期指定部分的区别*/select DATEDIFF(MM,'14-2-2','14-10-5')--相差8个月、/*DATENAME查询时间来的某个字段*/select DATENAME(YY,GETDATE())--2014select DATENAME(DW,GETDATE())--星期二select '今天是公元'+DATENAME(YY,GETDATE())+'年'+DATENAME(MM,getdate())+'月'+DATENAME(DD,GETDATE())+'日'+DATENAME(HH,GETDATE())+'点'+DATENAME(mi,GETDATE())+'分'+DATENAME(SS,GETDATE())+'秒'+datename(DW,GETDATE())--今天是公元2014年12月9日14点8分14秒星期二

系统函数

select CONVERT(varchar(6),12345)+'678'--将int转varcharselect DATALENGTH('我叫tom')--中文算2个字节