SQLServer之常用函数总结详解

SQLServer中的常用函数

字符串函数

len() 计算字符串的长度

想知道一个字段里字符串有多长？LEN()函数就是干这个的。直接用它就能返回字符数。

select LEN(name) from test1  --计算name的长度

大小写转换 lower() upper()

数据规格化处理时，大小写统一是个常见需求。LOWER()帮你转小写，UPPER()帮你转大写，非常直观。

select lower('STUDENT !')
select upper('student !')

去空 ltrim() 字符串左侧的空格去掉 ，rtrim()字符串右侧的空格去掉

用户输入或数据导入时常会带上前缀或后缀空格，影响比对和查询。用LTRIM()清左边，RTRIM()清右边，数据立马就干净了。

declare @str varchar(100) = '   a a a   '
select ltrim(@str)
select rtrim(@str)

字符串截取 substring() left() right()

截取字符串有三种“武器”：SUBSTRING()最灵活，可以指定任意起点和长度；LEFT()和RIGHT()则分别从最左或最右开始截取指定数量的字符，适合处理有固定格式的字符串。

select substring('HelloWorld!',6,6)  --可截取任意长度
select left('HelloWorld!' ,5)  --从左开始截取
select right('HelloWorld!' ,6) --从右开始截取

字符串替换 replace()

想把文本里的特定字符或单词全部换掉？REPLACE()函数就是你的“查找并替换”工具，它会替换掉所有匹配项。

select replace('HelloWorld!','o','e')    --string,要被替换的字符串，替换的字符串

字符串 掉个顺序 reverse()

这个函数挺有意思，REVERSE()能把字符串整个倒序排列，在某些特定算法或数据转换中会派上用场。

select reverse('abc')    --cba

返回 字符串1在字符串2中间出现的未位置 charindex(）

需要定位一个子串在母串中的位置吗？CHARINDEX()就是干这个的。不过要注意，它只返回第一次匹配到的位置，如果没找到，则返回0。

charindex(srt1 ,srt2）--srt1 在srt2中的开始位置
select charindex('H','elloHWorld') 结果为：5 --只能查第一次出现的位置，匹配不到返回0

指定的次数重复字符串值 replicate()

REPLICATE()函数能让你轻松生成重复的字符串模式，比如快速构造测试数据或某些分隔线。

select replicate('abc',4) 结果为：abcabcabcabc

聚合函数

说到数据分析，聚合函数绝对是核心角色。它们能对一组值进行计算，然后返回一个单一的汇总值。除了COUNT()在统计项数时会包含NULL值，其他如A VG()、SUM()等在计算时都会自动忽略NULL，这点在使用时需要留意。

平均值 a vg() 算一组数的总和，然后除以为null的个数，得到平均值。

select a vg(id) from test1     a vg(列名)

最小值min(） 最大值max（）

快速找出数据范围的边界？MIN()和MAX()这对搭档再合适不过。

select min(id) from test1
select max(id) from test1

求和 sum()

select sum(id) from test1

计算总数 count()

统计记录数，无论是全部行数还是某列非空值的数量，COUNT()都是最常用的工具。

select count(id) from test1

分组

聚合函数真正发挥威力，往往是在和GROUP BY子句搭配的时候。比如，要统计每个学生的总成绩并排序，可以这么写：

统计学生的总成绩并排序
select stu_id as 学生编号 ,name as 学生姓名 , SUM(语文+英语+数学+代数) as 总分
from tb_stuAchievement 
GROUP BY stu_id ,name 
ORDER BY 总分 DESC

（这里列举的是一些高频使用的聚合函数，更完整的清单建议查阅官方手册。）

日期和时间函数

获取当前日期GetDate

select getdate() 

GetUTCDate 获取UTC时间值

如果你的系统服务于全球用户，那么获取协调世界时（UTC）就很有必要了。

select GETUTCDATE()

单独获取年月日

从日期时间值里单独提取年、月、日成分，YEAR()、MONTH()、DAY()这三个函数用起来非常顺手。

select year(getdate())
select month(getdate())
select day(getdate())

日期减法 DATEDIFF

计算两个日期之间的间隔是常见需求。DATEDIFF()可以按你指定的单位（年、天、分钟等）返回差值。

select datediff(YYYY,'2011-11-11','2012-12-12')    --输出1 年份相减之后的确是1
select datediff(day,'2011-11-11','2012-12-12')     --输出 397 两个日期相差的天数

SQLServer 2008中新增的日期时间型函数

从SQL Server 2008开始，引入了一些精度更高、时区感知更强的日期时间函数，比如：

1、获取系统时间 SysDateTime()
2、获取当前日期和时间 SysDateTimeOffset
3、获取系统UTC时间 SysUTCDateTime
4、Current_TimeStamp当前数据库系统时间戳
5、判断是否为日期数据isDate
select isdate('2012-12-12')    -- 输出1　　　　 
select isdate('xxxx-12-12')　　-- 输出0

（同前，这里是部分常用日期函数，更多功能请参考官方文档。）

MID() 从文本字段中提取字符。

SELECT MID(City,1,3) as SmallCity FROM Persons

ROUND() 函数 数值字段舍入为指定的小数位数。

SELECT ROUND(column_name,decimals) FROM table_name

NOW() 函数 返回当前的日期和时间。

SELECT NOW() FROM table_name

FORMAT () 用于对字段的显示进行格式化。

想让日期、时间或数字以一种更友好、统一的格式呈现？FORMAT()函数提供了强大的格式化能力。

select FORMAT(stu_intime,'yyyy-MM-dd hh:mm:ss') as intime from stu

SELECT INTO 从一个表中选取数据，然后把数据插入另一个表中。

这个语句非常实用，可以快速基于查询结果创建并填充一个新表，常用于数据备份、中间表制作等场景。

select stu_name,stu_id into stu1 from stu   --将stu表中的stu_id和stu_name插入新表stu1(会创建一个新表)

sql server 中没有limit 但是有top

用过MySQL的朋友常找LIMIT，在SQL Server里，实现类似分页或限定行数功能的是TOP子句。配合子查询，可以灵活实现从第N条开始取M条数据的需求。

/*查询 从n开始的m条数据 */  
declare @n int=2;  
declare @m int = 5;  
select top (@m) * from stu  
where id not in (select top (@n) id from stu)  
/*查询n到m之间的数据*/  
declare @n int=2;  
declare @m int = 5;  
select top (@m-@n+1) * from stu  
where id not in (select top (@n-1) id from stu)

BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

这是一个非常直观的范围查询操作符，闭区间，包含两端的值。

/* 查询id 1 到3 的数据 */  
select * from stu where id between '1' and '3' 

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

表结构不是一成不变的。ALTER TABLE让你可以在不删除重建表的情况下，动态调整它的结构。

 alter table stu add stu_sj varchar(200)   --添加一列名为stu_sj 
 alter table stu drop column stu_sj  --删除列

DISTINCT 用于返回唯一不同的值。

当查询结果中有大量重复值，而你只想看有哪些不同的选项时，DISTINCT关键字就派上用场了。

/* 返回名字，重复的不返回 */  
select distinct stu_name from stu