sql语句集锦

悠悠 2022-07-12 06:53 163阅读 0赞

*  一、主要是需要更新mssqlServer 的时候需要进行一些是否存在的判断，在mssql2005 和 2008 上测试没有问题

--//判断是表Table_UserInfo里是否有字段
    if not exists(select * from syscolumns where id=object_id('Table_UserInfo') and name='Tel') begin
    ALTER table Table_UserInfo  add Tel varchar(200)--增加字段
    --ALTER TABLE Table_UserInfo DROP COLUMN Tel--删除字段
    --ALTER TABLE table_name  ALTER COLUMN Tel varchar(200) --修改字段类型
    end
    --//判断要添加列的表Table_UserInfo中是否有主键
    if exists(select 1 from sysobjects where parent_obj=object_id('Table_UserInfo') and xtype='PK') begin 
    select top 1 * from Table_UserInfo
    end
    --//判断表Table_Stat是否有索引Index_Other
    IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[Table_Stat]') AND name = N'Index_Other')  begin 
    select top 1 * from Table_UserInfo
    end
    --// 判断是否有存储过程pr_DoStatCount
    IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[pr_DoStatCount]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)  begin 
    DROP PROCEDURE [dbo].[pr_DoStatCount]
    end
    --判断是否有此表Table_stat_Count
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].Table_stat_Count') and OBJECTPROPERTY(id, N'IsUserTable') = 1)   begin 
    DROP PROCEDURE [dbo].[Table_stat_Count]
    end
    --修改字段添加Gateway_bookstatus的主键示例
     if not exists(select 1 from sysobjects where parent_obj=object_id('Gateway_bookstatus') and xtype='PK')
     begin    
     ALTER TABLE Gateway_bookstatus  ALTER COLUMN MetaID nvarchar(128) not null--修改不为空
     ALTER TABLE [dbo].[gateway_bookstatus] ADD  CONSTRAINT [PK_gateway_bookstatus]
     PRIMARY KEY CLUSTERED ([MetaId] ASC) 
    end
    
    --删除字段
    1.查找出表中该字段的约束名称（或根据已有的提示及对象'DF__******'）
    
    declare @name varchar(50)
    select  @name =b.name from sysobjects b join syscolumns a on b.id = a.cdefault 
    where a.id = object_id('TableName') 
    and a.name ='ColumName'
    
    2. 删除存在的约束
    
    exec('alter table TableName drop constraint ' + @name)

--设置标识列允许插入
    set identity_insert aclist on
    
    --将数据从临时表转移过来
    insert into LtiUser
    select id,[date],version,[class],actitle,acdetail,author from #aclist
    
    --关闭标识列插入
    set identity_insert aclist off

--删除主键：  
    Declare @Pk varChar(100);  
    Select @Pk=Name from sysobjects where Parent_Obj=OBJECT_ID('Dlib_stat_Time') and xtype='PK';  
    select @Pk  
    if @Pk is not null  
    exec('Alter table Dlib_stat_Time Drop '+ @Pk)  
    --重建主键：  
    ALTER Table Dlib_stat_Time ADD CONSTRAINT Pk_Dlib_stat_Time_LogDate_LibID PRIMARY KEY (LogDate,LibID)

今天给新同事讲数据维护过程的时候,他提出了写一个存储过程来检查日志表出错之后调用相关的存储过程,我们的日志表当中写入的表名和相应存储过程名称的差别是多了一个etl\_,因此ETL\_||table就是存储过程的名称.但是在编译过程当中无法将变量的存储过程名称在嵌套时执行,经过多方查找,一度怀疑是否可以执行这种变量式的存储过程,但是终于找到了方法,存储过程如下:

create or replace procedure yx_etl_proc as
     cursor tb is select  'ETL_'||table_name
                 from etl_log
                 where end_time is null and (table_name,start_time) in  (select table_name,max(start_time)
                 from etl_log where start_time>trunc(sysdate)
                 group by table_name);
           c_proc_name etl_log.table_name%type;
    begin
     
    open tb;
    loop
    fetch tb into c_proc_name;
    exit when tb%notfound;
    execute  immediate 'begin '||c_proc_name||';end;';  
    commit;
    end loop;
    close tb;
    end yx_etl_proc;

粗体部分就是嵌套存储过程的执行方法。

*  二、 insert into select

SELECT INTO 和 INSERT INTO SELECT 两种表复制语句
          Insert是T-sql中常用语句，Insert INTO table(field1,field2,...) values(value1,value2,...)这种形式的在应用程序开发中必不可少。但我们在开发、测试过程中，经常会遇到需要表复制的情况，如将一个table1的数据的部分字段复制到table2中，或者将整个table1复制到table2中，这时候我们就要使用SELECT INTO 和 INSERT INTO SELECT 表复制语句了。
          1.INSERT INTO SELECT语句
          语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1
          要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。示例如下：
    INSERT INTO SELECT语句复制表数据
       --1.创建测试表
        create TABLE Table1
        (
            a varchar(10),
            b varchar(10),
            c varchar(10),
            CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
            (
                a ASC
            )
        ) ON [PRIMARY]
        create TABLE Table2
        (
            a varchar(10),
            c varchar(10),
            d int,
            CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
            (
                a ASC
            )
        ) ON [PRIMARY]
        GO
        --2.创建测试数据
        Insert into Table1 values('赵','asds','90')
        Insert into Table1 values('钱','asds','100')
        Insert into Table1 values('孙','asds','80')
        Insert into Table1 values('李','asds',null)
        GO
        select * from Table2
        --3.INSERT INTO SELECT语句复制表数据
        Insert into Table2(a, c, d) select a,c,5 from Table1
        GO
        --4.显示更新后的结果
        select * from Table2
        GO
        --5.删除测试表
        drop TABLE Table1
        drop TABLE Table2
          2.SELECT INTO FROM语句
          语句形式为：SELECT vale1, value2 into Table2 from Table1
          要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。示例如下：
    SELECT INTO FROM创建表并复制表数据
       --1.创建测试表
        create TABLE Table1
        (
            a varchar(10),
            b varchar(10),
            c varchar(10),
            CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
            (
                a ASC
            )
        ) ON [PRIMARY]
        GO
        --2.创建测试数据
        Insert into Table1 values('赵','asds','90')
        Insert into Table1 values('钱','asds','100')
        Insert into Table1 values('孙','asds','80')
        Insert into Table1 values('李','asds',null)
        GO
        --3.SELECT INTO FROM语句创建表Table2并复制数据
        select a,c INTO Table2 from Table1
        GO
        --4.显示更新后的结果
        select * from Table2
        GO
        --5.删除测试表
        drop TABLE Table1
        drop TABLE Table2

*  三、排重

ALTER TABLE DLib_Reference  add   rowid_1 int identity(1,1) 
    delete   from   DLib_Reference
       where   rowid_1   not   in  
       (  
           select   min(rowid_1)   from DLib_Reference group   by metaid,hostmetaid having   count(*)>0  
       )

*  四、如何合并多条记录成为一条记录

【参考http://zhidao.baidu.com/question/178504075.html?push=ql】

--例子sql语句，合并多条记录中的相同字段。
    --convert  stuff 函数的使用
    --可以使用游标,函数,自连接等方式进行处理
    /*select  商店id,时间id,用户id, 
      stuff((select ','+convert(varchar(10),购买产品id) from tb_test b  where 商店id=a.商店id and 时间id=a.时间id and 用户id=a.用户id
          for xml path('')),1,1,'') from tb_test a group by 商店id,时间id,用户id*/
    --创建表
    create table tb_test
    (
      [metaid] [nvarchar](128)NOT  NULL,
      PROID int  NOT  NULL default(1),
      Names [nvarchar](128)   NULL 
    )
    --插入数据
    insert into tb_test values('m.001',1,'zh中国' )
    insert into tb_test values('m.002',1,'zh海南' )
    insert into tb_test values('m.002',1,'zh北京' )
    insert into tb_test values('m.002',1,'zh海淀' )
    insert into tb_test values('m.003',1,'zh中关村' )
    select * from tb_test
    --获取数据最后结果
    select metaid,
        stuff((select ';' + names   from tb_test b  where metaid=a.metaid
          for xml path('')),1,1,'') as n
    from tb_test a 
    group by metaid

*  五 重命名字段

IF COL_LENGTH('USP_UserGroup','UserGroupSecurity') IS NOT NULL
    	EXEC sp_rename 'USP_UserGroup.UserGroupSecurity','UserGroupLevel','COLUMN'
    ELSE IF COL_LENGTH('USP_UserGroup','UserGroupLevel') IS NULL
    	ALTER TABLE dbo.USP_UserGroup ADD UserGroupLevel int NOT NULL DEFAULT 0

*  六、如果杀死正在执行的存储过程。

我后来用sp_who查看，然后通过kill命令直接杀死的。多亏兄弟告诉我用KILL。 
    sp_who --列出id列表，需要查询某个用户可以 sp_who "username"
    kill 67 --杀掉该id
    查询进程连接：

select * from sysprocesses where dbid in (select dbid from sysdatabases where name='MyDatabase')

*  七、判断自定义方法是否存在，如果不存在，则创建

if exists(select 1 from sysobjects where id=object_id('fn_GetCategoryCodebycode') 
    and objectproperty(id,'IsInlineFunction')=0)
    drop function [fn_GetCategoryCodebycode] 
    
    go
    create function [dbo].[fn_GetCategoryCodebycode](@CateCode nvarchar(64)) 
    returns int
    as
    begin
    declare @ret int
    SELECT TOP 1 @ret=CategoryID 
    FROM CMF_CategoryDetail a  where CategoryTypeID=1  
    and ( charindex(CategoryCode,@CateCode) = 1) ORDER BY Len(CategoryCode) DESC
    return @ret
    end
    go

*  八、SQL分页 2005及其以上

declare @PageIndex int 
    declare @PageSize int 
    set @PageIndex=2
    set @PageSize =10
    
    select * from 
    (select *,
        row_number() over (order by [StaffID]  ) as RowNum
        from [StaffInfo]   
     ) t 
      where RowNum > ( @PageIndex - 1 ) * @PageSize  AND RowNum <= @PageIndex * @PageSize

利用OVER(PARTITION BY)函数介绍 分组 分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行

select * from 
    (select *,
        row_number() over (partition by stafftype order by [StaffID]  ) as RowNum
        from [StaffInfo]   
     ) t 
      where RowNum=1 --获取各个分组的第一条记录

*  九、sql操作xml

参考：http://www.cnblogs.com/youring2/archive/2008/11/27/1342288.html

declare @xmlDoc xml;
    select @xmlDoc=[ProcessXml] from [dbo].[WF_Process]  
    set @xmlDoc.modify('replace value of (/Process/Activities/Activity[ActivityId=2]/ReceiverKey/text())[1] with "10551"')  
    select @xmlDoc.query('/Process/Activities/Activity[ActivityId=2]/ReceiverKey/text()')  
    update [WF_Process] set [ProcessXml]=@xmlDoc where ProcessId=1

十、性能检测

set statistics profile on 
    set statistics io on 
    set statistics time on 
    go 
    /*--你的SQL脚本开始*/
    
    SELECT * FROM  T_Name
    
    /*--你的SQL脚本结束*/
    
    go 
    set statistics profile off 
    set statistics io off 
    set statistics time off

declare @d datetime
    set @d=getdate()
    /*你的SQL脚本开始*/
    SELECT [TestCase] FROM [TestCaseSelect] 
    /*你的SQL脚本结束*/
    select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

可以通过执行优化计划可以分析出是否创建索引

十一、一个表中存在另一个表中不存在

--方法一：使用 not in ,容易理解,效率低  ~执行时间为：1.395秒~
    SELECT COUNT(1) FROM ecs_goods WHERE ecs_goods.goods_id NOT IN (SELECT ecs_member_price.goods_id FROM ecs_member_price);
    --方法二：使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID 字段为 null的记录  ~执行时间：0.739秒~
    SELECT COUNT(1) FROM ecs_goods LEFT JOIN ecs_member_price ON ecs_goods.goods_id=ecs_member_price.goods_id WHERE ecs_member_price.goods_id IS NULL;
    --方法三：逻辑相对复杂,但是速度最快  ~执行时间: 0.570秒~
    SELECT COUNT(1) FROM  ecs_goods c WHERE (SELECT COUNT(1) AS num FROM ecs_member_price WHERE ecs_member_price.goods_id=ecs_goods.goods_id) = 0;

十二、设计器修改db字段顺序

这几天用SQL2008的时候，想要修改表结构时，报错，提示:"阻止保存要求重新创建表的更改"。以前在SQL2000和SQL2005时代没遇到这个问题，看来是SQL2008新增的功能。经过试验，找到了解决办法，如下：

点击"工具"菜单中的"选项"，在弹出的对话框中展开"designer"项，去掉"阻止保存要求重新创建表的更改"前面的勾即可。

十三，sqlserver后台执行ing的语句查询

SELECT     [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid),
    
     [User] = nt_username, [Status] = er.status, 
     [Wait] = wait_type, 
     [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2, (CASE WHEN er.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) 
                          * 2 ELSE er.statement_end_offset END - er.statement_start_offset) / 2),
                           [Parent Query] = qt.text, 
                           Program = program_name, Hostname, 
                           nt_domain, start_time
    FROM    
         sys.dm_exec_requests er INNER JOIN  sys.sysprocesses sp ON er.session_id = sp.spid 
         CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
    WHERE     session_Id > 50 /* Ignore system spids.*/ AND session_Id NOT IN (@@SPID)

十四、优化整理统计信息

把sql放到数据库执行在1秒以内 到服务端就会超时。做了个时间测试，数据库执行确实用了30秒，然后就超时了。  
找了很多方案，最后在数据库执行一下命令就好了：exec sp\_updatestats;

十五、简单游标

declare m_cursor cursor scroll for
    select  Address,PeopleId from  PeopleDetail
     
    open m_cursor
    declare   @Address varchar(50), @PeopleId int
     
    fetch next from m_cursor into @Address,@PeopleId
    while @@FETCH_STATUS=0
    begin
        print @Address + convert(varchar(3), @PeopleId)
        fetch next from m_cursor into @Address,@PeopleId
    end
     
    close m_cursor
    deallocate m_cursor

十六、拼接字段拆解

例如存在 staffids ， id字段，其中staffids是多个值组合（逗号隔开）

Select Distinct b.StaffId,
                      1 As Flags
               From   (
                          Select Convert(
                                     Xml,
                                     '<root><v>' + Replace(StaffIds, ',', '</v> <v>')
                                     + ' </v> </root>'
                                 ) As StaffIdXml
                          From   DocumentInfo
                          Where  DocumentStatusId = 1
                      ) a
                      Cross Apply (
                   Select T.C.value('.', 'int') As StaffId
                   From   a.StaffIdXml.nodes('/root/v') T(C)
    			   )b