查看锁表
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
222
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
