常用函数

---------------------------------

1、将数据复制到一张新表中

select * into T1 from UserInfo

select * from T1

2、将表info_two的数据全部添加到info_one中

insert into info_one select * from info_two   【into】可以省略

--指定添加的列名

insert info_one(iName) select iName from info_two  【into】可以省略

同类型表向后添加

 3、删除重复信息
delete from info where id not in(select min(id) from info group by UserID,UserName)

4、修改语句

　　错误写法：

　　　　update a set a.validate='10' from my_kaohe_chuqin a
　　　　select * from my_kaohe_chuqin where system_id='1001001486' and individual_id='100100168827' and years='2016' and zhouqi='20'
 　  　　and c_text='14' and me_id='51701'  ------这是2条语句

     正确写法：

　　　　update a set a.validate='10' from my_kaohe_chuqin a
　　　　（select * from my_kaohe_chuqin） where a.system_id='1001001486' and a.individual_id='100100168827' and a.years='2016' and a.zhouqi='20'
 　　　 and a.c_text='14' and a.me_id='51701'  ------去掉括号里面的

----------------------------------

函数：

1、convert(类型,值)

　　例：

　　　　select convert(int,'1234')+1   //1235

　　　　select convert(varchar(20),getdate(),112)   //20160612 

　　　　select convert(varchar(5),getdate(),112)    //20160 [varchar(5)： 取值的前5位]

　　　　select convert(varchar,getdate(),108)   //17:06:30 [varchar ：不设置范围时取全值]

2、replace(值，'a','b')   //如果值中存在a 则将'a'取代成'b'，没有则不替换 

　　例：

　　　　select replace('112233','1','44')   //44442233

　　　　select replace('12:30:55',':','')    //123055

　　　　select replace('12:30:55','小马','')    //12:30:55

　　　　select replace(convert(varchar,getdate(),108),':','')  //170308  [结合convert() 函数使用]

　　　　select replace(convert(varchar(5),getdate(),108),':','')  //1703  [结合convert() 函数使用]

3、cast(值 as 类型) //类型转换

　　例：

　　　　select cast('123' as int)
　　　　select cast('123 1' as int) //在将 varchar 值 '123 1' 转换成数据类型 int 时失败
　　　　select cast('123a' as int) //在将 varchar 值 '123a' 转换成数据类型 int 时失败

4、substring(值,首位,个数)

　　例：

　　　　select substring('123456',1,3) //123
　　　　select substring('123666',4,2) //66
　　　　select substring('123666',4,100) //666
　　　　select substring('转换成数据类型',2,100)//换成数据类型       [一个中文为2字符]
　　　　select substring('在将 varchar 值 "123a" 转换成数据类型 int 时失败',6,4) //rcha
　　　　select substring('1 2 3 4',2,4) //2 3    [空格算一字符]

5、left/right(值,长度)

　　例：

　　　　select left('1234',3) //123 截取左边3位
　　　　select right('1234',3) //234 截取右边3位

6、isnull(值1，值2)  【值1为null，则取值2，不为null，则取值1】

　　例：

　　　　select isnull(1,100) //1
　　　　select isnull(null,100) //100
　　　　select isnull(null,null) //null
　　　　select isnull(1,null) //1
　　　　select isnull(null,'无') //无

7、case when sex=1 then '男'

　　　　when sex=2 then '女'

　　else '其他' end

　　例：

View Code

 8、datediff(day/year/month,时间段1，时间段2) 两个时间段相差的值

　　例：

　　　　select datediff(day,'2016-6-01 00:00:00','2016-6-02 00:00:00') --日 d/dd/day  --1 时分秒忽略不计
　　　　select datediff(d,'2016-6-01 23:59:00','2016-6-02 00:00:00')   --日 d/dd/day  --1 时分秒忽略不计
　　　　select datediff(year,'2016-6-01 00:00:00','2017-6-02 00:00:00') --年 yyyy/yy/year --1
　　　　select datediff(yy,'2016-6-01 00:00:00','2017-6-02 00:00:00') --年 yyyy/yy/year --1
　　　　select datediff(month,'2016-6-01 00:00:00','2017-7-02 00:00:00')  --月 m/mm/month --13
　　　　select datediff(WK,'2016-6-01 00:00:00','2016-7-02 00:00:00') --周 ww/wk/week --4
　　　　select datediff(WK,'2016-6-01 00:00:00','2016-8-01 00:00:00') --周 ww/wk/week --9
　　　　select datediff(WK,'2016-6-01 00:00:00','2016-9-01 00:00:00') --周 ww/wk/week --13
　　　　select datediff(WK,'2016-6-01 00:00:00','2016-10-01 00:00:00')--周 ww/wk/week --17
　　　　select datediff(w,'2016-6-01 00:00:00','2016-7-01 00:00:00') --星期 dw/w --30
　　　　select datediff(DW,'2016-6-01 00:00:00','2016-8-01 00:00:00') --星期 dw/w --61
　　　　select datediff(DW,'2016-6-01 00:00:00','2016-9-01 00:00:00')--星期 dw/w --92
　　　　select datediff(DW,'2016-6-01 00:00:00','2016-10-01 00:00:00')--星期 dw/w --122

所有缩写

 9、rtrim(str) 返回字符串str与尾部的空格字符去掉

　　例：

　　　　select rtrim('1     1     ')+'w'         //1     1w
　　　　select 'a'+rtrim('右边空   ')+'b'      //a右边空b
　　　　select 'a'+rtrim('   左边空')+'b'      //a   左边空b
　　　　select 'a'+rtrim('   两边空   ')+'b'   //a   两边空b

10、charindex('a',id)  id字段中含有"a"或者"b"的记录 【返回的值为0或1】

　　例：

　　　　　　select * from table_a where charindex('a',id)>0 or charindex('b',id)>0
　　　　　　table_a 表中 id字段中含有"a"或者"b"的记录.

　　　　　　select charindex('1',12) //1
　　　　　　select charindex('1',22) //0

　　　　　　select charindex('-',CAST(-100 as varchar(20))) //1
　　　　　　select case when 1=charindex('1','12') then '有值' end //有值

11、isnumeric(值) 判断值是否为‘数字’ 是为：‘1’     否为：‘0’

　　　　例：

　　　　　　select isnumeric('') //0
　　　　　　select isnumeric(null) //0
　　　　　　select isnumeric(23432423)  //1
　　　　　　select isnumeric(0)  //1
　　　　　　select isnumeric(-23432423) //1
　　　　　　select isnumeric('dsfdfds')  //0
　　　　　　select ISNUMERIC(GETDATE()) //0

 12、union or union all :合并两个或多个 SELECT 语句的结果集

　　　　请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

　　　　例：　

View Code

 13、dateadd(yy/mm/dd,-1,'年-月-日 00：00：00')  修改年月日

　　例：

　　　　select CONVERT(varchar(20),DATEADD(dd,-1,'2016-02-02 00:00:00'),20)   //2015-02-02 00:00:00

　　　　select CONVERT(varchar(20),DATEADD(MM,-1,'2016-02-02 00:00:00'),20)  //2016-01-02 00:00:00

　　　　select CONVERT(varchar(20),DATEADD(DD,-1,'2016-02-02 00:00:00'),20)  //2016-02-01 00:00:00

　　　　

 14、ident_current('表名') 查询出字段中带有约束‘indentity(1,1)’ 的最后一个‘值’

　　select ident_current('dbo.info_two') //null 【不含约束‘indentity’的表】

　　select ident_current('dbo.info_three') //3 

　　select ident_current('dbo.info_four') //98

　　

实例