sqlserver常用语法大全

查看锁表

SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName 
FROM sys.dm_tran_locks 
WHERE resource_type='OBJECT '

不锁表查询

select * from TBW_PTProductactivity WITH(NOLOCK)

单独解锁

exec('kill 64')  --64 spid

查看存储过程，触发器，视图的内容

exec sp_helptext 'sp_Api_BackCharging'

查看相关依赖关系

exec sp_depends 'Accounts_Users'

查看sysobjects 表中的内容

select * from sysobjects where xtype='TR'--触发器
select * from sysobjects where xtype='v'--视图
select * from sysobjects where xtype='p'--存储过程
select * from sysobjects where xtype='s'--系统表
select * from sysobjects where xtype='fn' --标量函数
select * from sysobjects where xtype='tf'--表函数

获取字符切割

declare @str nvarchar(30)  
set @str='12|箱' 
print substring (@str,1, charindex ('|',@str)-1)  --输出数量12  
print substring (@str, charindex ('|',@str)+1,len(@str)-charindex ('|',@str)) --输出箱  

declare @ProductUnits nvarchar (20) 
set @ProductUnits='1|盒,10|箱'
print substring (@ProductUnits,charindex(',',@ProductUnits)+1,len(@ProductUnits)-charindex(',',@ProductUnits))
输出 10|箱

使用遍历方法操作数据：

DECLARE 
@A1 VARCHAR(10),
@A2 VARCHAR(10),
@A3 INT
DECLARE YOUCURNAME CURSOR FOR SELECT ProductId, Id,Qty  FROM TBBrandLevelUpGiving 
OPEN YOUCURNAME 
fetch next from youcurname into @a1,@a2,@a3 
while @@fetch_status<>-1
 begin 
--您要执行的操作写在这里 
print @a1
fetch next from youcurname into @a1,@a2,@a3 
end 
close youcurname 
deallocate youcurname

使用临时表

declare @tableTop table(
			Id int,
			Amount decimal(12,2)
		)
		insert into @tableTop select * from TBTopUpSet where Amount>1
		select * from  @tableTop

动态分页加参数查询
1

ALTER PROCEDURE [dbo].[Test]
@flag int,
@inorout int
AS
BEGIN
SELECT * FROM Demo 
where 1 = 1
and ((@flag IS NULL) or (flag=@flag) or @flag = '')
and ((@id IS NULL) or (id=@id) or @id= '')
END
@flag是传入的参数

so…如果要启用id作为查询条件，就传入一个非null的值，否则就是不启用id作为查询条件，这就是传说中的动态查询

2

select
*
from(select *,rn= ROW_NUMBER() over(order by ApplyTime) from 
TBCustomer)as T2 
where T2.rn between (@Page-1)*@Rows+1 and @Rows*@Page order by ID desc

随机获取表中一条数据

SET STATISTICS TIME ON 
SET STATISTICS IO ON
SELECT TOP 1 vspCusid  FROM    TB_Api_VspCusid where State=2
GROUP BY vspCusid
ORDER BY NEWID() 
SET STATISTICS TIME OFF
SET STATISTICS IO OFF

根据逗号拆分，返回临时表

create FUNCTION [dbo].[func_split](@str nvarchar(4000),@separtor varchar(10))   
  returns @temp table([row] [int] IDENTITY(1,1) NOT NULL,valuess nvarchar(4000))   
  as    
begin  
   declare @i int  
   set  @str=rtrim(ltrim(@str))   
   set  @i=charindex(@separtor,@str)   
   while   @i>=1   
    begin  
     insert   @temp   values(left(@str,@i-1))   
     set  @str=substring(@str,@i+1,len(@str)-@i)   
     set  @i=charindex(@separtor,@str)   
    end  
    if @str<>''    
    insert @temp values(@str)   
    return    
end

使用方法：
select * from  dbo.func_split('1,2,3,4,5,7', ',')

动态条件查询为空或者是空字符串处理

and (@State is null or A.State=@State)
and isnull(AuthCode,'') like (case when isnull(@AuthCode,'') = '' then isnull(AuthCode,'') else '%'+@AuthCode+'%' end)
		and isnull(Name,'') = (case when isnull(@Name,'') = '' then isnull(Name,'') else @Name end)
		and isnull(ApplyTime,'') >= (case when isnull(@BeginTime,'') = '' then isnull(ApplyTime,'') else convert(datetime,@BeginTime) end)
	    and isnull(ApplyTime,'') <= (case when isnull(@EndTime,'') = '' then isnull(ApplyTime,'') else convert(datetime,@EndTime) end)

使用事务

BEGIN TRAN
pritn '在这里执行需要回滚的操作'
**ROLLBACK TRAN**
--------------------------------
BEGIN
	set @Message=''
    SET NOCOUNT ON;

    BEGIN TRY---------------------开始捕捉异常
       BEGIN TRAN------------------开始事务
        UPDATE A SET A.names = B.names FROM T1 AS A INNER JOIN T2 AS B ON A.id = B.id

        UPDATE A SET A.names = B.names FROM T1 AS A INNER JOIN T2 AS B ON A.TEST = B.TEST

    COMMIT TRAN -------提交事务
    END TRY-----------结束捕捉异常
    BEGIN CATCH------------有异常被捕获
        IF @@TRANCOUNT > 0---------------判断有没有事务
        BEGIN
            ROLLBACK TRAN----------回滚事务
        END 
        set @Message='绑定出现问题'-----------记录存储过程执行时的错误信息，自定义
    END CATCH--------结束异常处理
END

获取随机数

DECLARE @NumBegin Int=1    --随机数的最小值 
DECLARE @NumEnd Int=100     --随机数的最大值 
DECLARE @Decimal Int=0      --保留小数点几位 
SELECT @NumBegin+round((@NumEnd-@NumBegin)*rand(),@Decimal)

整个库检索某个字段（返回表、对应的列）

declare @Str nvarchar(max), @tableName varchar(50), @colName varchar(50), @rowCount int

select a.name tableName, b.name Colname, 0 as IsFound into #t1
from sysobjects a join syscolumns b on a.id=b.id join systypes c on b.xtype=c.xtype
where a.[type]='U' and c.name in ('varchar', 'nvarchar', 'char', 'nchar') --这里是设置字段的类型，以缩小范围

declare _c1 cursor for select Colname, tableName from #t1
open _c1
fetch next from _c1 into @colName, @tableName
while @@FETCH_STATUS=0 begin
--print @Str
	select @Str='select @rowCount=count(1) from ['+@tableName+'] where ['+@colName+'] like ''%售后反馈模板%''' --这里是要查找的内容
	exec sp_executesql @Str, N'@rowCount int output', @rowCount output
	if @rowCount>0 update #t1 set IsFound=1 where ColName=@colName and tableName=@tableName
fetch next from _c1 into @colName, @tableName
end
close _c1
deallocate _c1
select * from #t1 where IsFound=1
drop table #t1

导出所有表和字段说明：
1\、执行下面的SQL,
2\、然后选择所有、
3\、另存为XXX

SELECT
     表名       = Case When A.colorder=1 Then D.name Else '' End,
     表说明     = Case When A.colorder=1 Then isnull(F.value,'') Else '' End,
     字段序号   = A.colorder,
     字段名     = A.name,
     字段说明   = isnull(G.[value],''),
     标识       = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,
     主键       = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (
                      SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,
     类型       = B.name,
     占用字节数 = A.Length,
     长度       = COLUMNPROPERTY(A.id,A.name,'PRECISION'),
     小数位数   = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),
     允许空     = Case When A.isnullable=1 Then '√'Else '' End,
     默认值     = isnull(E.Text,'')
 FROM
     syscolumns A
 Left Join
     systypes B
 On
     A.xusertype=B.xusertype
 Inner Join
     sysobjects D
 On
     A.id=D.id  and D.xtype='U' and  D.name<>'dtproperties'
 Left Join
     syscomments E
 on
     A.cdefault=E.id
 Left Join
 sys.extended_properties  G
 on
     A.id=G.major_id and A.colid=G.minor_id
 Left Join

 sys.extended_properties F
 On
     D.id=F.major_id and F.minor_id=0
     --where d.name='OrderInfo'    --如果只查询指定表,加上此条件
 Order By
     A.id,A.colorder

根据日期统计订单数

select 
	   SUBSTRING(CONVERT(varchar(100),WorkDate,120),1,4) 年,
       SUBSTRING(CONVERT(varchar(100), WorkDate, 120),7,4) 日期,
       count(ID) 订单量
from TBCustomerOrder
where 
WorkDate>='2020-06-02 00:00:00'
and  WorkDate<='2020-07-02 00:00:00'
group by SUBSTRING(CONVERT(varchar(100),WorkDate,120),1,4),
		SUBSTRING(CONVERT(varchar(100), WorkDate, 120),7,4)
order by 年

结果显示

**cast和convert的用法和区别**
用法：
select CAST('123' as int)   -- 123
select CONVERT(int, '123')  -- 123
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250 
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47 
区别
CAST和CONVERT比较
(1)cast一般更容易使用,convert的优点是可以格式化日期和数值;
(2)convert一般用于日期和时间值，小数之间转换,cast一般用于小数转数值和字符型;
(3)converk显示转换,cast是强制转换;    

递归

RETURN 
(
 WITH NODES     
 AS (   
 SELECT * FROM DBO.TBCustomerBrand par  WITH (NOLOCK) WHERE par.Customer_ID=@Customer_ID  and Brand_ID=@Brand_ID
 UNION ALL     
 SELECT child.* FROM TBCustomerBrand AS child  WITH (NOLOCK) INNER JOIN   
  NODES  AS RC ON child.Brand_ParentId = RC.Customer_ID where child.Brand_ID=@Brand_ID)    
  --SELECT ID FROM TBCustomerBrand WHERE Customer_ID IN (SELECT Customer_ID  FROM NODES N )  and Brand_ID=@Brand_ID 
  --SELECT ID  FROM NODES N
  SELECT ID FROM TBCustomerBrand  WITH (NOLOCK) WHERE ID IN (SELECT ID  FROM NODES N  WITH (NOLOCK) )  
)

获取生成的ID

@@identity ：返回当前会话最后一个标识值，不限于特定的作用域 ；
ident_current('tablename'):返回任何会话，任何作用域中的指定表中生成的
最后一个标识值 ；
scope_identity ：返回当前会话当前作用域任何表生成的最后一个标识值 。

创建聚合函数，拼接字符串

CREATE AGGREGATE [dbo].[hy_joinstr]
(@input [nvarchar](200))
RETURNS[nvarchar](max)
EXTERNAL NAME [MyAgg].[hy_joinstr]
GO

查看表里面某个字段是否包含另一张表某个字符串

s表 字符串
t表 字符串集
查看t表是否包含s表某个字符串

declare @u_id   varchar(100)    --所包含的字符串
	SELECT   
                   (SELECT   COUNT(*)
                     FROM      dbo.s
                     WHERE   (CHARINDEX(CONVERT(varchar(100), t.id), t.idlist) > 0)) 
       FROM      dbo.t   AS t   where t.id=@u_id