SQL SERVER高级知识梳理一:语法，函数，系统表笔记

1. 基础dml，ddl不解释，有手就行

1）常用语法

select top(2) * from #Test1 --前两条
select distinct * from #Test1 --去重

Create table #Test1(id int,name Nvarchar(20),Age int)
INSERT INTO #Test1
VALUES(1,N'诸|葛|亮',70),(2,N'诸|葛|暗',70),
(3,N'刘|备',41),(4,N'张|飞',45)

select max(Age),MIN(Age),STDEV(Age),MIN(Age) from #Test1
select SUBSTRING(name,2,1)截取,charindex(N'刘',name)字符位置,REPLACE(name,N'诸葛','') 替换,CONVERT(varchar(30),name)类型转换
,CONVERT(datetime,'2023-01-01 01:01:01',120)时间转换 from #Test1

2）cross apply和outer apply

都是将左表的每一条数据带到右表中匹配（如果是函数就去执行)

corss apply：返回成功匹配的（inner join 类似）

outer apply:返回成功匹配的和未匹配到的,但右表为null（left join 类似）

两表相连

Create table #Test1(id int,name Nvarchar(20),Age int)
INSERT INTO #Test1
VALUES(1,N'诸|葛|亮',70),(2,N'诸|葛|暗',70),
(3,N'刘|备',41),(4,N'张|飞',45)

Create table #Test2(id int,name Nvarchar(20),Age int)
INSERT INTO #Test2
VALUES(1,N'诸|葛|亮',70),(2,N'诸|葛|暗',70),
(3,N'刘|备',41),(4,N'张小飞',45)

select * from #Test1
--以|分割数据，分割后数据都占一行
select * from #Test1 aa
cross apply(select * from #Test2 bb where aa.name =bb.name )f
select * from #Test1 aa
outer apply(select * from #Test2 bb where aa.name =bb.name )f

函数相连 

Create table #Test1(id int,name Nvarchar(20),Age int)
INSERT INTO #Test1
VALUES(1,N'诸|葛|亮',70),(2,N'诸|葛|暗',70),
(3,N'刘|备',41),(4,N'张|飞',45)

--以|分割数据，分割后数据都占一行
select * from #Test1
cross apply(select * from string_split(name,'|')f)s

2.临时表，用来写sp非常好用，一般使用方法为

if OBJECT_ID('tempdb.dbo.#test')is not null
drop table #test
select Id into #test  from T_Test  where Id<10

3.cte用来做递归非常好用，也可以当做临时表使用，但是切记不能套娃

创建测试表

Create table #TestCTE(id int,name varchar(20),Parentid int)
INSERT INTO #TestCTE
VALUES(1,'grandpa',0),(2,'grandma',0),
(3,'mom',1),(4,'dad',1),
(3,'mom',2),(4,'dad',2),
(5,'me',4),(5,'me',3),
(6,'younger sister',3),(6,'younger sister',4),
(7,'son',5)

查询出我的所有长辈

;with cte as(
select *from #TestCTE where id =5--找出最低层数据
union all
select  aa.*  from #TestCTE aa
inner join cte bb on aa.id =bb.Parentid --递归出来的表
)
select distinct *from cte

4.函数

1）窗口函数，包括rownumber，rank，dese_rank(以前只会用，不知道名字，面试一脸懵逼，野生开发者太悲惨)

select *from (
select ROW_NUMBER() over(partition by type order by type)rk,* from sys.all_objects
)s where s.rk <5

2）行转列（pivot/case when）和列转行(unpivot/union)，

Create table #Test(id int,name varchar(20),Age int)
INSERT INTO #Test
VALUES(1,'grandpa',70),(2,'grandma',70),
(3,'mom',41),(4,'dad',45)


select *from #Test   
select *   from (
select * from (select  name,Age from #Test )s1
pivot (max(Age) for [name] in (grandpa,grandma,mom,dad))s2)s3

select * into #pt  from (
select * from (select  name,Age from #Test )s1
pivot (max(Age) for [name] in (grandpa,grandma,mom,dad))s2)s3

select * from (
select *from #pt
unpivot (name for age in (grandpa,grandma,mom,dad))s1)s2

 3）STUFF 经常配合 for xml，将多列合并为一列，再用pivot进行动态行转列

Create table #Test(id int,name varchar(20),Age int)
INSERT INTO #Test
VALUES(1,'grandpa',70),(2,'grandma',70),
(3,'mom',41),(4,'dad',45)



declare @name varchar(200)='',@sql varchar(max) =''
select @name=STUFF( (select ','+name from #Test  for xml path('')),1,1,'')
--select STRING_AGG(name,',') from #Test 也可
select @name

 select @sql='
select *   from (
select * from (select  name,Age from #Test )s1
pivot (max(Age) for [name] in ('+@name+'))s2)s3
'
exec(@sql)

5.常用的系统表

select * from INFORMATION_SCHEMA.TABLES --本数据库所有表信息
select * from INFORMATION_SCHEMA.COLUMNS --本数据库所有列信息
select * from INFORMATION_SCHEMA.SCHEMATA --本数据看所有schema信息
select *from sys.all_columns  --本数据库所有列信息,包括了许多系统级的view的列
select *from sys.columns --本数据库所有列信息，主要是用户定义的table和view列
select *from sys.tables --本数据库所有表信息
select *from sys.all_objects -- 所有object，USER_TABLE(u)，SQL_STORED_PROCEDURE(p)，view(V)，PRIMARY_KEY_CONSTRAINT(PK),SQL_SCALAR_FUNCTION(fn)等

--columns和all_columns的区别
select *from sys.all_objects where object_id  in 
(select object_id from (select *from sys.all_columns
except
select *from sys.columns)s)
select *from sys.all_objects where object_id  in 
(
select object_id from sys.columns)

select *from msdb..sysjobs --所有job
select *from msdb..sysjobschedules -- job和schedule中间表
select *from msdb..sysjobsteps --job的step
select * from msdb..sysschedules --schedule
select * from msdb..sysjobhistory
select case run_status
				  WHEN 0 THEN N'失败'
				  WHEN 1 THEN N'成功'
				  WHEN 2 THEN N'重试'
				  WHEN 3 THEN N'取消'
				  WHEN 4 THEN N'正在运行'end RunState,convert(datetime,left(run_date,4)+'-'+SUBSTRING(convert(varchar(11),run_date),5,2)+'-'+RIGHT(run_date,2)+' ' + 
		left(run_time,2)+':'+SUBSTRING(convert(varchar(11),run_time),3,2)+':'+RIGHT(run_time,2)+':000')lastTime,RunTime,job_id, step_id, step_name from(
		select job_id, h.step_id, h.step_name, h.run_duration,run_status , run_date, right('000000' + convert(varchar(10), run_time),6) run_time
		,	 STUFF(STUFF( RIGHT ( '000000' + CAST ([run_duration] AS VARCHAR ( 6 )), 6 ),
                  3 , 0 , ':' ), 6 , 0 , ':' ) AS 'RunTime'from msdb..sysjobhistory h)s --所有job的历史记录(可用来监控job执行状态)

6. 查询表在那些SP中有dml操作(update/insert/delete)

原理就是在查询SP中的包含的字符中，将多余的字符替换为空，再嵌有一层条件含update/insert/delete字符的语句

--查询哪些SP中对表T_Test有Insert操作
if OBJECT_ID('tempdb.dbo.#ss') is not null
drop table #ss
select distinct sp into #ss from (
SELECT obj.Name  sp, REPLACE(REPLACE(REPLACE(REPLACE(sc.TEXT,' ',''),'[',''),char(10),''),'dbo.','') 存储过程内容 --如果有数据库名也需替换
FROM syscomments sc
INNER JOIN sysobjects obj ON sc.Id = obj.ID
WHERE sc.TEXT LIKE '%T_Test%'
 )s where s.存储过程内容 like '%insertintoT_Test%'