sql部分常用

----查看表信息
declare @table_name as varchar(max)
set @table_name ='表名' 
select sys.columns.name, sys.types.name,sys.columns.max_length, sys.columns.is_nullable,
(select count(*) from sys.identity_columns where sys.identity_columns.object_id
= sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity ,
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id
and sys.extended_properties.minor_id = sys.columns.column_id) as description
from sys.columns, sys.tables, sys.types where sys.columns.object_id
= sys.tables.object_id
and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name=@table_name
order
by sys.columns.column_id
----查看表信息

 

游标查询使用
declare @变量1            
declare @变量2
SET @suminsuredamount=0      
        
Declare curStudentFee1 Cursor for select 数据1,数据2 from 表 where 条件            
     Open curStudentFee1              
 Fetch Next From curStudentFee1 Into @变量1,@变量2
  While ( @@Fetch_Status=0 )                 
      begin            
       使用这些变量                  
       Fetch Next From curStudentFee1 Into  @变量1,@变量2            
      end            
   Close curStudentFee1              
     Deallocate curStudentFee1   

 

循环插入多条数据
 INSERT INTO [表名]                                
     ( 字段1,字段2)                               
     ( select  字段1，字段2 from 表名 where ...) 

 

--Description:　 <拆分字符串函数>  
--select sp from [dbo].[Split]('1,2,3',',')
CREATE FUNCTION[dbo].[Split]  
(  
@SplitString nvarchar(max), 
-- 如果要传入NText类型，下面需要相应的修改，注释行为NText下同  
@Separator nvarchar(2) 
)  
RETURNS @SplitStringsTable TABLE  
(sp nvarchar(max)
)  
AS  
BEGIN  
 DECLARE @CurrentIndex int  
 DECLARE @NextIndex int  
 DECLARE @ReturnText nvarchar(max)  
 SELECT @CurrentIndex=1  
 WHILE (@CurrentIndex<=datalength(@SplitString)/2)
 BEGIN  
  SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex)  
  IF(@NextIndex=0 OR @NextIndex IS NULL)  
   SELECT @NextIndex=datalength(@SplitString)/2+1
  SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex)  
  INSERT INTO @SplitStringsTable(sp) VALUES(@ReturnText)  
  SELECT @CurrentIndex=@NextIndex+1  
 END  
 RETURN  
END  

 

循环拆分
declare @str varchar(50)
declare @spliter varchar(10)
declare @skhc varchar(50)
DECLARE @Num int,@Pos int, @NextPos int   
   set @str='1,2,3,4,5,6'
   set @spliter=','
   SET @Num = 0    
   SET @Pos = 1     
   WHILE(@Pos <= LEN(@str))   
   
   BEGIN   
   
   SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos)   
   
   IF (@NextPos = 0 OR @NextPos IS NULL)   
   
   SELECT @NextPos = LEN(@str) + 1   
   
   SET @skhC =(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos))))   
     
   SET  @Pos = @NextPos+1   
	--print @skhc
   END

 

更新字段
update 表1 set 表1字段=表2字段
 from 表2 where 
表1字段=表2字段

 

Case具有两种格式。简单Case函数和Case搜索函数。
 --简单Case函数
 CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END
 --Case搜索函数
 CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END