SQL Server一些有用的语句

1.给查询出的结果集排出序号,[PARTITION BY COLUMN]可以分组进行排序
select ROW_NUMBER() OVER([PARTITION BY COLUMN] ORDER BY COLUMN1) as rn from table

2.如果COLUMN1值是Null,就用0取代它
select  isnull(COLUMN1,0) from table

3.如果COLUMN1值是中国，就显示我国，其它就显示外国
select (case COLUMN1 when '中国' then '我国' else then '外国' end) as Country from table

4.如果table1有值就显示1，否则显示2,也适用于if not exists,if(1=1)
if exists(select count(1) from table1)
   begin select 1 end
else
   begin select 2 end

5.table groupTB 如下，想把数据按小组，列出组内所有头2个人名
id groupName name
1    group1      小明
2    group1      小红
3    group2      小云
4    group2      小铃
5    group2      小聪
select groupName,max(case num when 1 then name else '' end)+max(case num when 2 then','+name else '' end) as names from(
select groupName,name,row_number() over(partition by groupName order by name) as num from groupTB) t group by groupName

groupName  names
     group1    小红,小明
     group2    小聪,小铃

6.上面的groupTB,如果需要列出全部小组成员名字，则可以利用stuff... for xml path
select groupName,                                                                                        
stuff((select ','+name from groupTB t0 where t0.groupName=t1.groupName for xml path('')),1,1,'') as names
from groupTB t1 group by groupName 

groupName  names
     group1    小明,小红
     group2    小云,小铃,小聪


7.把字符串变成datetime格式,这样就可以直接跟datatime类型的字段比较了
select CONVERT(datetime, '1/12/2002', 103)
select CONVERT(datetime, '12/1/2002', 103)
select CONVERT(datetime, '20130101', 103)

8.比较天数
select datediff(d,'2013-07-23',getdate()) as days


9.直接用1/2,查询结果会是0,无法返回小数，将除数*1.0就可以
select 1*1.0/2

10.如果表tb存在就删除
if object_id('tb') is not null drop table tb

11.@@rowcount与rowcount

@@rowcount返回上一条执行的SQL影响的行数
可以利用rowcount查询可变条数的记录,如下,会返回前10条记录
set rowcount 10
select * from test
http://www.cnblogs.com/daohuen/archive/2010/01/11/1644473.html
不过这个用法一定要记得取消,以免影响后面的查询

set rowcount 0

12.@@Fetch_Status

在fetch了后,用@@Fetch_Status = 0来判断有取到record

13.@@IDENTITY与SCOPE_IDENTITY的区别
@@IDENTTITY返回在当前会话中的任何作用域内插入的最后一个标识列的值。
SCOPE_IDENTITY()只返回同一作用域内插入到当前作用域中的值
http://mslagee.blog.51cto.com/1065828/317055

14.@@ERROR

返回执行语句的错误号,0表示无错误


15.SUSER_SNAME()

16.View里不能用order
select top 10 PERSENT * from table order by id 

17.批量更新
update table2 set pwd=table1.ip from table1 inner join table2 on table1.username=table2.username

18.换行
select 'aaa'+char(13)+'bbb'

 
19.1条结果变几条
select * from (select 9 as id,3 as id2) as t1,
(select 9 as id,1 as id1
union 
select 9 as id,2 as id1) as t where t1.id=t.id 

20.With as 语句
with sr as (
select * from test

)
select * from test,sr where test.id=sr.id

21.前面补0
REPLICATE ( string_expression ,integer_expression ) 将字串值重复指定的次数
DECLARE @MM VARCHAR(2) = REPLICATE(0, 2 - LEN(@MM)) + @MM

22.检查ascii码,当含有ascii码<26的数据用来填充xml时会引发错误

--错误
select * from Data where Name like '%→%'

--正确
select * from data where name like '%' + chr(26) + '%'

23.使用正则表达式

set @Name = replace(@Name, ' ', '')
 if @Name like '%[^a-zA-Z0-9.,\-]%'
        set @ErrorMessage = 'Contains non alpha character(s)'
  ...

24.transaction in storeprocedure

begin transaction 
 begin try
   ...
   commit tran
   return 1
 end try
 begin catch
   if @@trancount > 0      
         rollback tran
   return 0
   set Exception = '(' + cast(error_number() as nvarchar) + ') ' + error_message()
 end catch

25.導入表

INSERT INTO Tableselect * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Temp\Excel.xlsx;HDR=YES', 'SELECT * FROM [Sheet]')

BULK INSERT ReqOutDated
 FROM 'C:\Temp\Temp.CSV'         --  –> change the file path
 WITH
 (
 FIRSTROW = 2,                       -- –> An indicator where the data starts. Usually its 2 because row 1 is the column names.
 FIELDTERMINATOR = '\n',    --–> the field terminator is a comma (,), you may change it for your own needs
 ROWTERMINATOR = '\n'
 )

26.SQL Server text field里面有换行符的时候copy到excel数据会散乱

select '"'+convert(varchar(8000),testField)+'"'astestField from testTable
这样虽然结果集里面有引号将数据括住，但copy到excel的时候会消失，数据也不会散乱

27.加用戶和Role的命令

CREATE USER [userName] FOR LOGIN [userName] WITH DEFAULT_SCHEMA=[dbo]
ALTER ROLE [db_owner] ADD MEMBER [userName]

CREATE LOGIN [IIS APPPOOL\DefaultAppPool] FROM WINDOWS
 GO
 --USE Table Name
 GO
 CREATE USER [IIS APPPOOL\DefaultAppPool] FOR LOGIN [IIS APPPOOL\DefaultAppPool]
 GO
 EXEC sp_addrolemember 'db_datareader', 'IIS APPPOOL\DefaultAppPool'
 GO
 EXEC sp_addrolemember 'db_datawriter', 'IIS APPPOOL\DefaultAppPool'
 GO

28.Alter View

Alter View ViewName As select * from testTb

29.改列類型

ALTER TABLE testTb ALTER COLUMN Name varchar(1)

30.查询正在更新的表

select * from [testTb] with (nolock) where id=1

31.store procedule default parameter

ALTER PROCEDURE [dbo].[testSP] 
	@actionDate = null
AS
BEGIN

	if @actionDate = null begin 
	set @actionDate = getdate()
	end 
	
END

32.trigger

CREATE TRIGGER [dbo].[testTG] ON [dbo].[testTB] 
FOR INSERT, UPDATE
AS
IF UPDATE(Remarked)
UPDATE testTB SET Status = 3 WHERE id = (select id from Inserted) and Remarked = 1

33.查看table信息

SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = 'User'
   
SELECT CONSTRAINT_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'User'

EXEC sp_statistics User

查看表中各列，不過不可以改順序
select * from dbo.syscolumns where id=OBJECT_ID(N'TB') and name='TB'


34.修改登录用户密码命令

ALTER LOGIN [user] WITH PASSWORD = '123456' UNLOCK

35.判断该表,存储过程存不存在

IF OBJECT_ID(N'table', N'U') IS NOT NULL
select 1;
GO

IF object_id('YourSp') IS NULL
    EXEC ('create procedure dbo.YourSp as select 1')
GO
ALTER PROCEDURE dbo.YourSp
AS
...

36.兩個引號表示一個引號
select '''call'''

37.判斷是否日期
 select isdate('01-19-2014')

38.StoreProcedure中的動態SQL

exec sp_executesql N'select @para1,@para2',N'@para1 int,@para2 nvarchar(2)',1,'6a'
1 6a

39.convert 和 cast
convert一般用于日期和时间值，小数之间转换；
 cast一般用于小数转数值和字符型. 
 不同之处：convert只是显示转换
 　　　　　cast是强制转换
 cast是SQL标准
 convert是MS的

40.設置主鍵時找出duplicate record

select name,count(*) from [dbo].[PeoperInfo]
group by name
having count(*)>1

41.裝換成CSV字符串

select stuff( (select CHAR(10)+content from 
			(
			select t.id +','+ convert(nvarchar(10),t.Date,103) as content from (
			select '12345' as id, getdate() as Date 
			union
			select '54321' as id, getdate() as Date ) as t
			) as t2
			 for xml path('')),1,1,'') as contents   

42.OA date
select cast(41300 as datetime)

43.取字符串某一部分

selectPARSENAME(REPLACE('aaa;bbb;ccc;ddd',';','.'),4)

aaa

但当数量超过4个的时候就会出错了
select PARSENAME(REPLACE('a,b,c,d,e',',','.'),5)
NULL
If we give more than 4 parts, it will always return null.

44.将数据库设置为只读,脱机

USE master
EXEC sp_dboption 'pubs', 'read only', 'TRUE'
EXEC sp_dboption 'pubs', 'read only', 'FALSE'
EXEC sp_dboption 'sales', 'offline', 'TRUE'

45.使用游标查看全部表占用空间

create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100)) 
 
declare @name varchar(100) 
declare cur cursor  for 
    select name from sysobjects where xtype='u' order by name 
open cur 
fetch next from cur into @name 
while @@fetch_status=0 
begin 
    insert into #data 
    exec sp_spaceused   @name 
    print @name 
 
    fetch next from cur into @name 
end 
close cur 
deallocate cur 

select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data, 
convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #data 
http://www.cnblogs.com/nikyxxx/archive/2012/10/08/2715423.html

46.前面補0
select  right('000000'+'123',6)
select  right('000000'+'testeste',6)

47.对设置了identity列的表删除全部记录
truncate table testTb

48.使用SQL Server，使用like '%_%'这样的语句想要匹配下划线，结果查询不到结果。
是因为SQL Server的like中下划线类似于通配符%，就不能用以上的语句来匹配，要想匹配下划线可用如下两种方法：

1.使用escape转义字符，escape是将后面的字符指定为转义字符，可用任意字符来做转义字符
    like '%\_%' escape '\'
    like '%|_%' escape '|'

  2.使用instr函数判断是否存在下划线
    instr(xxxx,'_') != 0

49.SET ANSI_NULLS ON

SET ANSI_NULLS ON
select * from T where F=null按照ANSI SQL标准会返回F字段为NULL的记录
SET ANSI_NULLS OFF
select * from T where F=null
而按照非ANSI SQL标准，则会返回F字段为NULL的记录而用F IS NULL总是能返回F为空的记录

50.Union 和 Union all 的区别
union  优先取上面
union all  全部取

51.varbinary
 select 1 a where cast('tm' as varbinary)=cast('TM' as varbinary)

52.批量更新

  update TaskUser set targetusertitle=t2.targetusertitle from TaskUser@ t2 where TaskUser.id=t2.id

53.随机数
select rand()

随机字母
selectchar(cast(rand()*1000asint)%26+97)
随机数字
selectcast(rand()*100asint)%6

SELECT REPLACE(NEWID(),'-','')

54.字母出现的位置

selectcharindex(' ','adbc fgf')

55.print @@version

56.发邮件

select * from msdb.dbo.sysmail_profile

exec msdb..sysmail_add_account_sp
 @account_name = 'abc@abc.com.cn' -- 邮件帐户名称（SQL Server 使用）
,@email_address = 'abc@abc.com.cn' -- 发件人邮件地址
,@display_name = null -- 发件人姓名
,@replyto_address = null
 ,@description = null
 ,@mailserver_name = 'smtpserver.abc.com.cn' -- 邮件服务器地址
,@mailserver_type = 'SMTP' -- 邮件协议（SQL 2005 只支持 SMTP）
,@port = 25 -- 邮件服务器端口
,@username = 'abc'-- 用户名
,@password = '***' -- 密码
,@use_default_credentials = 0
 ,@enable_ssl = 0
 ,@account_id = null
 
 exec msdb..sysmail_add_profile_sp @profile_name = 'abc@abc.com.cn'      -- profile 名称    
                              ,@description  = 'test by xxx' -- profile 描述           
                              ,@profile_id   = null
 
 exec msdb..sysmail_add_profileaccount_sp  @profile_name    = 'abc@abc.com.cn' -- profile 名称      
                                    ,@account_name    = 'abc@abc.com.cn'     -- account 名称                 
                                    ,@sequence_number = 1             -- account 在 profile 中顺序

EXEC msdb.dbo.sp_send_dbmail 
   @profile_name = 'abc@abc.com.cn',
   --@from_address = @FROM,
   @blind_copy_recipients = '',
   --@recipients ='abc@abc.com.cn',   
   @recipients = 'abc@abc.com.cn',
   @body = 'test',
   @subject = 'subject',
   @body_format = 'html';
   
   use msdb
   select top 500 * from sysmail_allitems order by sent_date desc
   select top 500 * from sysmail_mailitems order by sent_date desc
   select top 500 * from sysmail_event_log order by log_date desc

57.in 和exist
   in 外面大里面小
   exist  外面小里面大

58.设置行号
select groupName as names from(
select groupName,name,row_number() over(partition by groupName order by name) as num from groupTB) t group by groupName

id groupName name
1    group1      小明
2    group1      小红
3    group2      小云
4    group2      小铃
5    group2      小聪

select groupName,                                                                                        
stuff((select ','+name from groupTB t0 where t0.groupName=t1.groupName for xml path('')),1,1,'') as names
from groupTB t1 group by groupName 
groupName  names
group1    小红,小明
group2    小聪,小铃

59.xml raw

select * from a,b where a.id=b.id for xml raw
select * from a, b where a.id=b.id for xml auto
select * from a for xml raw('field')
select id as [aaa!1!id] from a for xml explicit

60.xml path

select * from a,b where a.id=b.id for xml path

61.查看log
select * from fn_dblog(null,null) where AllocUnitName like '%testTB%'
select distinct Operation from fn_dblog(null,null)
SELECT [Begin Time] ,* FROM ::fn_dblog(null,null) WHERE [Begin Time] >= '2012/10/22'

62.去掉decimal字段後面無用的0
select cast(48.0000000000 as float)
48

63.SQL中使用LEFT函数会导致index不起作用，查询结果慢
SELECT * FROM testTB WHERE Left(table_Type, 4) = 'Test'
 
64.存储过程中给变量赋值
declare @id int
select @id = user_id from users where name = '张三' -- 从users表读取张三的user_id，并赋值给变量@id
execute upYourProc @id --将变量传递给存储过程。

在存储过程中同时给几个变量一起赋值
declare @id int ,@name varchar(2)
 select @id=1,@name='aa' 
 select @id,@name

65.变量表
declare  @Temp  table(

 id int,
          name nvarchar(50))

66.游标

   declare 游标名字 cursor for select 列名 from 表名 where 条件--先申明游标指向查询出的结果，一列，或者多列都可以，条件自定
   declare 变量名  varchar(400)--存储取到的值
   open 游标名 --开启游标
   while @@FETCH_STATUS=0--取值
     begin
     fetch next FROM 游标名 into 变量名--这样就将游标指向下一行，得到的第一行值就传给变量了
     -------------------------------------------
     --需要执行的操作，例如修改某表中的字段
     update 表名
     set 列名=值
     where （修改表中的列）=变量名
     -------------------------------------------
 end
  close 游标名--关闭游标

  deallocate  游标名--释放游标

67.对于包含基于字符的数据类型的列（该列与单个词和短语，以及与另一个词一定范围之内的近似词精确或模糊（不太精确的）匹配或者加权匹配），返回具有零行、一行或多行的表。

返回的表中有一列名为 KEY，其中包含全文键值。每个全文索引表都有这样一列，它的值保证是唯一的，而且 KEY 列中的返回值都是与包含搜索条件中指定的选择标准相匹配的行的全文键值。从 OBJECTPROPERTY 函数获得的 TableFulltextKeyColumn 属性为这个唯一的 key 列提供标识。若要从原始表中获得所需要的行，请在 CONTAINSTABLE 行指定一个联接。使用 CONTAINSTABLE 的 SELECT 语句的 FROM 子句的典型形式为：SELECT select_listFROM table AS FT_TBL INNER JOIN CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL ON FT_TBL.unique_key_column = KEY_TBL.[KEY]

68.Kill dead lock jobs

DECLARE @v_spid INT
 DECLARE c_Users CURSOR
    FAST_FORWARD FOR
    SELECT SPID
      FROM master..sysprocesses (NOLOCK)
    WHERE  status='sleeping' 
   AND LTRIM(RTRIM(hostname))='PROJECTX-AGENT'
   and LTRIM(RTRIM(loginame))='ProjectixAgentUser'
   AND DATEDIFF(mi,last_batch,GETDATE())>=5
    AND spid<>@@spid
    and open_tran<>0
 
OPEN c_Users
 FETCH NEXT FROM c_Users INTO @v_spid
 WHILE (@@FETCH_STATUS=0)
 BEGIN
   PRINT 'KILLing '+CONVERT(VARCHAR,@v_spid)+'...'
   EXEC('KILL '+@v_spid)
   FETCH NEXT FROM c_Users INTO @v_spid
 END
 
CLOSE c_Users
 DEALLOCATE c_Users

69.查看Role对于object的权限

SELECT 

perms.state_desc AS State, 

permission_name AS [Permission], 

obj.name AS [on Object], 

dPrinc.name AS [to User Name], 

sPrinc.name AS [who is Login Name]

FROM sys.database_permissions AS perms

JOIN sys.database_principals AS dPrinc

ON perms.grantee_principal_id = dPrinc.principal_id

JOIN sys.objects AS obj

ON perms.major_id = obj.object_id

LEFT OUTER JOIN sys.server_principals AS sPrinc

ON dPrinc.sid = sPrinc.sid

order by perms.state_desc,permission_name,obj.name,dPrinc.name

70.逻辑与运算

declare @a int,@b int
set @a=38320
set @b=32768
select @a&@b,@a|@b,@a^@b
set @b=16384
select @a&@b,@a|@b,@a^@b


32768 38320 5552
0 54704 54704


71.位移运算


left  <<
right >>

declare @i int = 1 -- integer
 declare @n int = 14 -- shift
 -- Function
 declare @m int,@s int
 select  @n%=32,@m=power(2,31-@n),@s=@i&@m,@i&=@m-1,@i*=power(2.,@n)
 if(@s>0)set @i|=0x80000000
 select @i -- -1382285312

  int bit = 1 << bitpostion;

 16384

 ALTER FUNCTION [dbo].[BitMove]
(
  @i int,  -- integer
  @n int,  -- shift
  @left bit
)
RETURNS int
AS
BEGIN

 -- Function
 declare @m int,@s int
 if @left = 0
 select  @n%=32,@m=power(2,31-@n),@s=@i&@m,@i&=@m-1,@i*=power(2.,@n)
 else
 select  @n%=32,@m=power(2,31-@n),@s=@i&@m,@i&=@m-1,@i/=power(2.,@n)
 if(@s>0)set @i|=0x80000000
 return @i -- -1382285312

END

  select dbo.BitMove(1,14,0)
  16384
  select dbo.BitMove(32768,1,1)
  16384
  select dbo.BitMove(16384,1,1)
  8192


72.十六进制与十进制的转换
SELECT CONVERT(VARBINARY(50), 38320)
0x000095B0
SELECT CONVERT(VARBINARY(50), 65535)
0x0000FFFF


select Convert(int,0x095B0) , Convert(int,0x0000FFFF)
38320 65535

73.十六进制和字符串的转换

十六进制转换成字符串
 CREATE FUNCTION [dbo].[Binary2HexStr](@bin VARBINARY(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @re VARCHAR(8000),@i INT
    SELECT @re='',@i=datalength(@bin)
    WHILE @i>0
        SELECT @re=substring('0123456789ABCDEF',substring(@bin,@i,1)/16+1,1)
                +substring('0123456789ABCDEF',substring(@bin,@i,1)%16+1,1)
                +@re
            ,@i=@i-1
    RETURN('0x'+@re)
END

SELECT dbo.Binary2HexStr(0x095B0)
0x0095B0
select Convert(int,0x095B0)
38320
SELECT dbo.Binary2HexStr(38320)
0x000095B0

字符串转换成十六进制
SELECT CONVERT(VARBINARY(3), '0x004BD8', 1);
注意这样的字符串是无效的
SELECT CONVERT(VARBINARY(3), '0x04BD8', 1);



74.存储过程的变量命名要求

临时表以#开头，变量以@开头

begin...end类似于c#的#region...#endregion，可以收起代码

75.出错
begin try
  DECLARE @msg NVARCHAR(MAX)=ERROR_MESSAGE()

  RAISERROR ('非法输入', 16, 1)

...

  

end try

begin catch
      SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;

end catch