SQL 进阶学习

--1、创建临时表  设置默认排序规则
 create table #temp
(
ID int identity(1,1),
empName Nvarchar(50) collate Database_Default not null
)


select * from #temp




 --2、判断表是否存在，删除临时表
 if OBJECT_ID('tempdb..#temp') is not null
begin 
truncate table #temp;
drop table #temp;
print '已经删除临时表#temp';
end;
else
begin
print '不存在临时表 #temp';
end;




 -- 3、不能判断临时表
 if OBJECT_ID('tmom','U')is not null
begin
print '表存在'
end;
else
begin
print '表不存在'
end;




 -- 4、创建索引
 create index idx_tmp on  #temp (ID ASC ,EmpName desc )  


 drop index idx_tmp on #temp




 -- 5、Sys 对象：
 select * from sys.triggers where name='spTrg_tSysUser' --触发器
 Select * from sysobjects where xtype='TR' --查询所有触发器：‘TR’ 触发器
 select * from sys.indexes where name ='PK_tAllowance' --索引
 select * from sys.procedures   where name='spRD_ViewDeadLock' --存储过程
 Select * From sys.tables where name ='tMoM' --表
 select * from sys.objects  --对象包含所有数据




 --  6、 字段操作
-- 增加 、删除字段
 alter table  #temp add EmpID int default(0); 
 alter table  #temp drop Column empID
 




 -- 7、Row_Number() Over 按顺序生成行序号
 select  row_number() over(order by empName) as RowNo  ,* from vwWorkOriGar
 Select  RowNo=ROW_NUMBER() Over (Order By EmpID Desc) ,* From tWork_Today (NoLock) 
 
 
 -- 8、Char(39)单引号 （根据ASCLL值转换）
 select  '11112222' --结果：11112222
 select Char(39) + '11112222' + Char(39) --结果：'11112222'
 
 --=========================================
 --== 9、 查询Ascll值，最大为128 
 --=========================================
 create table #mm
 (
id int identity(1,1) not null,
Name varchar(50) null
 )
 declare @i int 
set @i=1;
 while @i<=128
   begin
insert into #mm(Name)
select Char(@i)
set @i =@i+1;
   end;
select * from #mm
drop table #mm
 --=======================
 
 
 -- 10、返回日期 带 — 的
  select GETDATE()   --结果：2017-01-05 15:40:56.040
  select CONVERT(VARCHAR(10),GETDATE(),120) --结果：2017-01-05


 -- 11、获取随机记录,随机排序（可用于抽奖等）
  Select * From tEmployee Order By NewID()
  
  
  
  -- 12、检测子字符串第一个字符在 父字符串中的位置 
  -- charIndex('子字符串','父字符串')
     Select CharIndex('2','111123444456') --返回5



  -- 13、SubString 获取子串函数,从1位置开始
  Select SubString ('abcdefg',1,1)     -- 返回'a'
  Select SubString ('abcdefg',0,1)     -- 返回空串
  Select SubString ('abcdefg',1,100)   -- 返回'abcdefg', 注意第3个参数的长度为100.
  Select SubString( N'Åkergatan 24',1,1) --Å
  Select SubString( N'项目完成',3,2)      --完成
  
 -- 14、删除左边/右边空串
  select Ltrim('  1334  00  ')  --返回'1334  00  '
  select Rtrim('  1334  00  ')  --返回'  1334  00'
  
  -- 15、Left/Right返回左右两边指定几个字符
  Select Left('abcdefg',2) --返回ab
  Select Right('abcdefg',2) --返回fg
  
  -- 16、返回字符串的小写/大写.
   select Lower('AAbbcc')  --aabbcc
   select Upper('AAbbcc')  --AABBCC
   
  -- 17、将第一个参数父串的原值全部换成新值.  第2个参数: 原值  第3个参数: 新值.
   Select Replace('123456789','456','xxx');  --返回123xxx789


  -- 18、返回字符表达式的逆向表达式(反转)。 
   Select Reverse('abc')  --返回的是 cba
   
 --  19、将表中某一列的所有值转换成一个逗号分隔的字符串
    Declare @vResult VarChar(8000) ;
Set @vResult = '';
Select Top 10 @vResult = @vResult + ',' +  MONo FROM tMOM
Select SubString(@vResult,2,Len(@vResult)-1) ;
   
   
--  20、case when 的使用

select EmpName ,
case sex  when  'M' then '男'
 when  'F' then '女'
else  '怪兽'     end as 'sex'
from tEmployee






-- 21、游标示例：
 Declare @vEmpID VarChar(100);
 Declare @vEmpName VarChar(100);
 Set @vEmpID = '';
 Set @vEmpName = '';
  --声明游标 cursor3
  Declare cursor3 cursor FAST_FORWARD For Select Top 1000 EmpID,EmpName 
From tEmployee Order By EmpID ;   --For Read Only为只读游标
  Open cursor3 ; --打开游标
  Fetch Next From cursor3 into @vEmpID,@vEmpName;  --读取第一条记录，将2个字段的值存放在变量中
  While @@Fetch_Status = 0  --循环体
  begin 
print '工号=' + @vEmpID + '  姓名=' + @vEmpName;
Fetch Next From cursor3 into @vEmpID,@vEmpName;
  end ;
  Close cursor3 ;   --关闭游标
  Deallocate cursor3;


-----------------------------------
--- 22、   触发器  =================
-----------------------------------
create trigger tr1 
on tCOmpany
 for update
 as
if update(Remark)   --如果更新了某列,则回滚用户的修改
rollback tran


create trigger tr1 
on tCOmpany
for update
as
    if update(Remark)   --如果更新了某列,则回滚用户的修改
        rollback tran
        
         update tCompany Set Remark = '55' 
         
         Drop trigger tr1  
         
         select remark from tCompany
         
         maxturn181
         
IF Exists (Select name From sysobjects Where name = 'tr1' AND type = 'TR')
 Drop Trigger tr1
         
         
     create table temp
     (
Nid int identity(1,1),
MoNo NVarchar(50) collate Database_default null,
[Guid] Uniqueidentifier
     )    
         
         
         select * from temp
         DROP TABLE temp
         DROP TRIGGER InsteadTrigger
CREATE TRIGGER InsteadTrigger 
on tmom 
INSTEAD OF INSERT
AS
BEGIN 
INSERT INTO temp 
SELECT MONo, Guid
FROM inserted
END;
GO




--禁用某一个触发器  
Alter Table tCutBundCard Disable Trigger sptrg_tCutBundCard

--"禁用某个表下面的所有触发器"  
Alter Table tCutBundCard Disable Trigger All

--启用某一个触发器  
Alter Table tCutBundCard Enable Trigger sptrg_tCutBundCard

--"启用某个表下面的所有触发器"  
Alter Table tCutBundCard Enable Trigger All

--"显示某一个sql表下面的所有触发器信息"  
exec sp_helptrigger tCutBundCard

--查看触发器内容
exec sp_helptext 'trgTCutBundCard_tUpdate'




--=========================
--索引


create index idx_Name_ID on #aa(EmpName,EmpID)


Drop Index  idx_Name_ID on  #aa 








---------------------------------------------------------
---== 23、双向条件查询
---------------------------------------------------------
--"双边查询条件(字符串型)
     and (( (@AMONoMin='' And @AMONoMax='')
      or (@AMONoMin = '' And @AMONoMax <> '' And AT.MONo = @AMONoMax )
      or (@AMONoMin <> '' And @AMONoMax = '' And AT.MONo = @AMONoMin )
      or (@AMONoMin <> '' And @AMONoMax <> '' And AT.MONo >= @AMONoMin And AT.MONo <= @AMONoMax))  
      
--"双边查询条件(数值型)" "
And ((@ASeqNoMin = 0 And @ASeqNoMax = 0 )
      or (@ASeqNoMin = 0 And @ASeqNoMax <> 0 And AT.SeqNo = @ASeqNoMax )
      or (@ASeqNoMin <> 0 And @ASeqNoMax = 0 And AT.SeqNo = @ASeqNoMin )
      or (@ASeqNoMin <> 0 And @ASeqNoMax <> 0 And AT.SeqNo >= @ASeqNoMin And AT.SeqNo <= @ASeqNoMax))
      
------------------------------------------------------------
---====  24、单向条件查询
------------------------------------------------------------
--单边查询条件(字符型)  
And ( (@AEmpID ='') or (@AEmpID <>'' And W.EmpID =@AEmpID))
--单边查询条件(数值型)  
And((@ATermNo=0 or T.TermNo=@ATermNo))




--- 25、Pivot 进行行转列
create table #tmpTest(姓名 varchar(10) , 课程 varchar(10) ,分数 int);
insert into #tmpTest values('张三' , '语文' , 74);insert into #tmpTest values('张三' , '数学' , 83);
insert into #tmpTest values('张三' , '物理' , 93);insert into #tmpTest values('李四' , '语文' , 74);
insert into #tmpTest values('李四' , '数学' , 84);insert into #tmpTest values('李四' , '物理' , 94);
go


select * from #tmpTest;


select m.* , n.平均分 , n.总分 from
( 
  select * from (select * from #tmpTest) a pivot (max(分数) for 课程 in (select Distinct 课程 from #tmpTest)) b
) m,
(
  select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from #tmpTest group by 姓名
) n 
where m.姓名 = n.姓名;


Drop Table #tmpTest;


select quoteName('123','{}')

SELECT  COALESCE(NULL, '111', '222')  

select SUBSTRING('1234567890',2,4)

SELECT REPLACE('123456789','456','xxxxx')GO

Select Replicate('abc',10)

SELECT REVERSE('abcd');

--列转行
create table #tb(id int, value varchar(10))  
insert into tb values(1, 'aa')  
insert into tb values(1, 'bb')  
insert into tb values(2, 'aaa')  
insert into tb values(2, 'bbb')  
insert into tb values(2, 'ccc')  
go  
 
select id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')  
from tb  
group by id  

--=======================================