sql语句集锦

悠悠 2022-07-12 06:53 318阅读 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’

    1. 删除存在的约束

    exec(‘alter table TableName drop constraint ‘ + @name)

  1. --设置标识列允许插入
  2. set identity_insert aclist on
  4. --将数据从临时表转移过来
  5. insert into LtiUser
  6. select id,[date],version,[class],actitle,acdetail,author from #aclist
  8. --关闭标识列插入
  9. set identity_insert aclist off
  13. --删除主键：  
  14. Declare @Pk varChar(100);  
  15. Select @Pk=Name from sysobjects where Parent_Obj=OBJECT_ID('Dlib_stat_Time') and xtype='PK';  
  16. select @Pk  
  17. if @Pk is not null  
  18. exec('Alter table Dlib_stat_Time Drop '+ @Pk)  
  19. --重建主键：  
  20. ALTER Table Dlib_stat_Time ADD CONSTRAINT Pk_Dlib_stat_Time_LogDate_LibID PRIMARY KEY (LogDate,LibID)

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

  1. create or replace procedure yx_etl_proc as
  2.  cursor tb is select  'ETL_'||table_name
  3.              from etl_log
  4.              where end_time is null and (table_name,start_time) in  (select table_name,max(start_time)
  5.              from etl_log where start_time>trunc(sysdate)
  6.              group by table_name);
  7.        c_proc_name etl_log.table_name%type;
  8. begin
  10. open tb;
  11. loop
  12. fetch tb into c_proc_name;
  13. exit when tb%notfound;
  14. execute  immediate 'begin '||c_proc_name||';end;';  
  15. commit;
  16. end loop;
  17. close tb;
  18. end yx_etl_proc;

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

  • 二、 insert into select

    SELECT INTO 和 INSERT INTO SELECT 两种表复制语句

    1.   Insert是T-sql中常用语句，Insert INTO table(field1,field2,...) values(value1,value2,...)这种形式的在应用程序开发中必不可少。但我们在开发、测试过程中，经常会遇到需要表复制的情况，如将一个table1的数据的部分字段复制到table2中，或者将整个table1复制到table2中，这时候我们就要使用SELECT INTO 和 INSERT INTO SELECT 表复制语句了。
    2.   1.INSERT INTO SELECT语句
    3.   语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1
    4.   要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。示例如下：

    INSERT INTO SELECT语句复制表数据
    —1.创建测试表

    1. create TABLE Table1
    2. (
    3.     a varchar(10),
    4.     b varchar(10),
    5.     c varchar(10),
    6.     CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
    7.     (
    8.         a ASC
    9.     )
    10. ) ON [PRIMARY]
    11. create TABLE Table2
    12. (
    13.     a varchar(10),
    14.     c varchar(10),
    15.     d int,
    16.     CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
    17.     (
    18.         a ASC
    19.     )
    20. ) ON [PRIMARY]
    21. GO
    22. --2.创建测试数据
    23. Insert into Table1 values('赵','asds','90')
    24. Insert into Table1 values('钱','asds','100')
    25. Insert into Table1 values('孙','asds','80')
    26. Insert into Table1 values('李','asds',null)
    27. GO
    28. select * from Table2
    29. --3.INSERT INTO SELECT语句复制表数据
    30. Insert into Table2(a, c, d) select a,c,5 from Table1
    31. GO
    32. --4.显示更新后的结果
    33. select * from Table2
    34. GO
    35. --5.删除测试表
    36. drop TABLE Table1
    37. drop TABLE Table2
    38.   2.SELECT INTO FROM语句
    39.   语句形式为：SELECT vale1, value2 into Table2 from Table1
    40.   要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。示例如下：

    SELECT INTO FROM创建表并复制表数据
    —1.创建测试表

    1. create TABLE Table1
    2. (
    3.     a varchar(10),
    4.     b varchar(10),
    5.     c varchar(10),
    6.     CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
    7.     (
    8.         a ASC
    9.     )
    10. ) ON [PRIMARY]
    11. GO
    12. --2.创建测试数据
    13. Insert into Table1 values('赵','asds','90')
    14. Insert into Table1 values('钱','asds','100')
    15. Insert into Table1 values('孙','asds','80')
    16. Insert into Table1 values('李','asds',null)
    17. GO
    18. --3.SELECT INTO FROM语句创建表Table2并复制数据
    19. select a,c INTO Table2 from Table1
    20. GO
    21. --4.显示更新后的结果
    22. select * from Table2
    23. GO
    24. --5.删除测试表
    25. drop TABLE Table1
    26. drop TABLE Table2

  • 三、排重

    ALTER TABLE DLib_Reference add rowid_1 int identity(1,1)
    delete from DLib_Reference
    where rowid_1 not in
    ( 

    1.    select   min(rowid_1)   from DLib_Reference group   by metaid,hostmetaid having   count(*)>0

    )

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

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

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

  • 五 重命名字段

    IF COL_LENGTH(‘USP_UserGroup’,’UserGroupSecurity’) IS NOT NULL

    1. EXEC sp_rename 'USP_UserGroup.UserGroupSecurity','UserGroupLevel','COLUMN'

    ELSE IF COL_LENGTH(‘USP_UserGroup’,’UserGroupLevel’) IS NULL

    1. 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)
    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     ,

    1. row_number() over (order by [StaffID]  ) as RowNum
    2. from [StaffInfo]

    ) t
    where RowNum > ( @PageIndex - 1 ) @PageSize AND RowNum <= @PageIndex @PageSize

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

    select from
    (select     ,

    1. row_number() over (partition by stafftype order by [StaffID]  ) as RowNum
    2. from [StaffInfo]

    ) t
    where RowNum=1 —获取各个分组的第一条记录

  • 九、sql操作xml

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

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

十、性能检测

  1. set statistics profile on 
  2. set statistics io on 
  3. set statistics time on 
  4. go 
  5. /*--你的SQL脚本开始*/
  7. SELECT * FROM  T_Name
  9. /*--你的SQL脚本结束*/
  11. go 
  12. set statistics profile off 
  13. set statistics io off 
  14. set statistics time off
  16. declare @d datetime
  17. set @d=getdate()
  18. /*你的SQL脚本开始*/
  19. SELECT [TestCase] FROM [TestCaseSelect] 
  20. /*你的SQL脚本结束*/
  21. select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

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

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

  1. --方法一：使用 not in ,容易理解,效率低  ~执行时间为：1.395秒~
  2. SELECT COUNT(1) FROM ecs_goods WHERE ecs_goods.goods_id NOT IN (SELECT ecs_member_price.goods_id FROM ecs_member_price);
  3. --方法二：使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID 字段为 null的记录  ~执行时间：0.739秒~
  4. 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;
  5. --方法三：逻辑相对复杂,但是速度最快  ~执行时间: 0.570秒~
  6. 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字段顺序

  1.  这几天用SQL2008的时候，想要修改表结构时，报错，提示:"阻止保存要求重新创建表的更改"。以前在SQL2000和SQL2005时代没遇到这个问题，看来是SQL2008新增的功能。经过试验，找到了解决办法，如下：
  3.  点击"工具"菜单中的"选项"，在弹出的对话框中展开"designer"项，去掉"阻止保存要求重新创建表的更改"前面的勾即可。

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

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

十四、优化整理统计信息

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

十五、简单游标

  1. declare m_cursor cursor scroll for
  2. select  Address,PeopleId from  PeopleDetail
  4. open m_cursor
  5. declare   @Address varchar(50), @PeopleId int
  7. fetch next from m_cursor into @Address,@PeopleId
  8. while @@FETCH_STATUS=0
  9. begin
  10.     print @Address + convert(varchar(3), @PeopleId)
  11.     fetch next from m_cursor into @Address,@PeopleId
  12. end
  14. close m_cursor
  15. deallocate m_cursor

十六、拼接字段拆解

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

  1. Select Distinct b.StaffId,
  2.                   1 As Flags
  3.            From   (
  4.                       Select Convert(
  5.                                  Xml,
  6.                                  '<root><v>' + Replace(StaffIds, ',', '</v> <v>')
  7.                                  + ' </v> </root>'
  8.                              ) As StaffIdXml
  9.                       From   DocumentInfo
  10.                       Where  DocumentStatusId = 1
  11.                   ) a
  12.                   Cross Apply (
  13.                Select T.C.value('.', 'int') As StaffId
  14.                From   a.StaffIdXml.nodes('/root/v') T(C)
  15.                )b

发表评论

表情：
评论列表 （有 0 条评论，318人围观）

还没有评论，来说两句吧...

相关阅读

    相关 sql语句集锦

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

    悠悠悠悠/ 0 赞/ 319 阅读