SqlServer的各种工具SQL和语法实例

1.嵌套游标

declare my_cursor cursor for 
select Id,AreaName from areaInfo where ParentId=0
open my_cursor 
declare @AreaName varchar(50) = '123'
declare @Pid varchar(50) = '123'
declare @Index int = 1
fetch next from my_cursor into @Pid,@AreaName
while @@FETCH_STATUS=0
begin
set @AreaName=convert(varchar(1000),@Index) +'.'+ @AreaName
select @AreaName

declare my_cursor2 cursor for 
select Id,AreaName from areaInfo where ParentId=@Pid
open my_cursor2 
declare @AreaName2 varchar(50) = '123'
declare @Pid2 varchar(50) = '123'
declare @Index2 int = 1
fetch next from my_cursor2 into @Pid2,@AreaName2
while @@FETCH_STATUS=0
begin
set @AreaName2=convert(varchar(1000),@Index2) +'.'+ @AreaName2
select @AreaName2
set @Index2=@Index2+1
fetch next from my_cursor2 into @Pid,@NodeName
end
close my_cursor2
deallocate my_cursor2

set @Index=@Index+1
fetch next from my_cursor into @Pid,@NodeName
end
close my_cursor
deallocate my_cursor

2.递归查找

WITH TEMP   --递归
     AS (SELECT [Id]
				  ,[NodeName]
				  ,[ParentId]
				  ,[ParentIdList]
         FROM   [dbo].[cfgAreaInfo]
         WHERE  Id = 1   --查询当前部门
         UNION ALL
         SELECT B.[Id]
				  ,B.[NodeName]
				  ,B.[ParentId]
				  ,B.[ParentIdList]
         FROM   TEMP A
                INNER JOIN [dbo].[cfgAreaInfo] B
                        ON B.ParentId = A.Id)
SELECT [Id]
	,[NodeName]
	,[ParentId]
	,[ParentIdList]
FROM   TEMP   --获取递归后的集合

3.查找某列是最大或最小值的整行数据not exists

select * from teacher a where not exists(select 1 from teacher b where a.createDate < b.createDate)

4.stuff配合FOR XML PATH串起结果

STUFF((
	SELECT ',' + NAME
	FROM table
	WHERE table.Id>0
	FOR XML PATH('')
), 1, 1, '')

5.从execl导入数据到表

(1).安装支持

https://www.microsoft.com/zh-cn/download/details.aspx?id=13255

(2).开启服务

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

(3).语句

insert [表名] ([列名],t2,t3,t4)
select F2,F3,F4,F5 --execl中的列名 统一都是f1 f2
from OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 8.0;HDR=YES;DATABASE=D:\work\7788\文档\2021年公寓在住老人花名册.xls'-- 文件
,老人花名册$)--老人花名册$ 为表格中的具体表+$

(4).关闭服务

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

(5)清楚表数据并清楚自增列值

TRUNCATE TABLE kcAllKey;

6.查看执行过的语句

 

1:
SELECT TOP 1000

　　　　--创建时间
　　　　QS.creation_time,
　　　　--查询语句
　　　　SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
　　　　((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
　　　　ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1
　　　　) AS statement_text,
　　　　--执行文本
　　　　ST.text,
　　　　--执行计划
　　　　QS.total_worker_time,
　　　　QS.last_worker_time,
　　　　QS.max_worker_time,
　　　　QS.min_worker_time
　　　　FROM
　　　　sys.dm_exec_query_stats QS
　　　　--关键字
　　　　CROSS APPLY
　　　　sys.dm_exec_sql_text(QS.sql_handle) ST
　　　　ORDER BY
　　　　QS.creation_time DESC

 

 

2:
SELECT TOP 1000 
       ST.text AS '执行的SQL语句',
       QS.execution_count AS '执行次数',
       QS.total_elapsed_time AS '耗时',
       QS.total_logical_reads AS '逻辑读取次数',
       QS.total_logical_writes AS '逻辑写入次数',
       QS.total_physical_reads AS '物理读取次数',       
       QS.creation_time AS '执行时间' ,  
       QS.*
FROM   sys.dm_exec_query_stats QS
       CROSS APPLY 
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE  QS.creation_time BETWEEN '2015-08-01 00:00:00' AND '2015-09-02 11:00:00' 
ORDER BY
     QS.total_elapsed_time DESC

 

 7.查询最耗时SQL

SELECT
(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
,creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like 'tch%'
ORDER BY
total_elapsed_time / execution_count DESC;

8.查被锁进程和杀进程

--查 select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT' and OBJECT_NAME(resource_associated_entity_id) is not null order by request_session_id --杀 declare @spid int Set @spid = 53 --锁表进程 declare @sql varchar(1000) set @sql='kill '+cast(@spid as varchar) exec(@sql)