常用T-Sql技巧

常用T-Sql技巧

一、 只複製一個表結構，不複製資料

select top 0 * into [t1] from [t2]

二、 擷取資料庫中某個對象的建立指令碼

1、 先用下面的指令碼建立一個函數

if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)
 drop function fgetscript
go

create function fgetscript(
 @servername varchar(50)     --伺服器名
 ,@userid varchar(50)='sa'    --使用者名稱,如果為nt驗證方式,則為空白
 ,@password varchar(50)=''    --密碼
 ,@databasename varchar(50)    --資料庫名稱
 ,@objectname varchar(250)    --對象名

) returns varchar(8000)
as
begin
 declare @re varchar(8000)        --返回指令碼
 declare @srvid int,@dbsid int       --定義伺服器、資料庫集id
 declare @dbid int,@tbid int        --資料庫、表id
 declare @err int,@src varchar(255), @desc varchar(255) --錯誤處理變數

--建立sqldmo對象
 exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output
 if @err<>0 goto lberr

--串連伺服器
 if isnull(@userid,'')='' --如果是 Nt驗證方式
 begin
  exec @err=sp_oasetproperty @srvid,'loginsecure',1
  if @err<>0 goto lberr

  exec @err=sp_oamethod @srvid,'connect',null,@servername
 end
 else
  exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password

 if @err<>0 goto lberr

--擷取資料庫集
 exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
 if @err<>0 goto lberr

--擷取要取得指令碼的資料庫id
 exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename
 if @err<>0 goto lberr

--擷取要取得指令碼的對象id
 exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname
 if @err<>0 goto lberr

--取得指令碼
 exec @err=sp_oamethod @tbid,'script',@re output
 if @err<>0 goto lberr

 --print @re
 return(@re)

lberr:
 exec sp_oageterrorinfo NULL, @src out, @desc out
 declare @errb varbinary(4)
 set @errb=cast(@err as varbinary(4))
 exec master..xp_varbintohexstr @errb,@re out
 set @re='錯誤號碼: '+@re
   +char(13)+'錯誤源: '+@src
   +char(13)+'錯誤描述: '+@desc
 return(@re)
end
go

2、 用法如下
用法如下,

print dbo.fgetscript('伺服器名','使用者名稱','密碼','資料庫名','表名或其它對象名')

3、 如果要擷取庫裡所有對象的指令碼，如如下方式

declare @name varchar(250)
declare #aa cursor for
 select name from sysobjects where xtype not in('S','PK','D','X','L')
open #aa
fetch next from #aa into @name
while @@fetch_status=0
begin
 print dbo.fgetscript('onlytiancai','sa','sa','database',@name)
 fetch next from #aa into @name
end
close #aa
deallocate #aa

4、 聲明，此函數是csdn鄒建鄒老大提供的
三、 分隔字串
如果有一個用逗號分割開的字串，比如說"a,b,c,d,1,2,3,4"，如何用t-sql擷取這個字串有幾個元素，擷取第幾個元素的值是多少呢？因為t-sql裡沒有split函數，也沒有數組的概念，所以只能自己寫幾個函數了。
1、 擷取元素個數的函數

create function getstrarrlength (@str varchar(8000))
returns int
as
begin
  declare @int_return int
  declare @start int
  declare @next int
  declare @location int
  select @str =','+ @str +','
  select @str=replace(@str,',,',',')
  select @start =1
  select @next =1
  select @location = charindex(',',@str,@start)
  while (@location <>0)
  begin
    select @start = @location +1
    select @location = charindex(',',@str,@start)
    select @next =@next +1
  end
 select @int_return = @next-2
 return @int_return
end

2、 擷取指定索引的值的函數

create function getstrofindex (@str varchar(8000),@index int =0)
returns varchar(8000)
as
begin
  declare @str_return varchar(8000)
  declare @start int
  declare @next int
  declare @location int
  select @start =1
  select @next =1 --如果習慣從0開始則select @next =0
  select @location = charindex(',',@str,@start)
  while (@location <>0 and @index > @next )
  begin
    select @start = @location +1
    select @location = charindex(',',@str,@start)
    select @next =@next +1
  end
  if @location =0 select @location =len(@str)+1 --如果是因為沒有逗號退出，則認為逗號在字串後
  select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗號之後的位置或者就是初始值1
  if (@index <> @next ) select @str_return = '' --如果二者不相等，則是因為逗號太少，或者@index小於@next的初始值1。
  return @str_return
end

3、 測試

SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')
SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5)

四、 一條語句執行跨越若干個資料庫
我要在一條語句裡操作不同的伺服器上的不同的資料庫裡的不同的表,怎麼辦呢?
第一種方法:

select * from OPENDATASOURCE('SQLOLEDB','Data Source=遠程ip;User ID=sa;Password=密碼').庫名.dbo.表名

第二種方法:
先使用連接伺服器:

EXEC sp_addlinkedserver '別名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=遠程名;UID=使用者;PWD=密碼;'
exec sp_addlinkedsrvlogin  @rmtsrvname='別名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密碼'
GO

然後你就可以如下：

select * from 別名.庫名.dbo.表名
insert 庫名.dbo.表名 select * from 別名.庫名.dbo.表名
select * into 庫名.dbo.新表名 from 別名.庫名.dbo.表名
go

五、 怎樣擷取一個表中所有的欄位資訊
蛙蛙推薦：怎樣擷取一個表中所有欄位的資訊
先建立一個視圖

Create view fielddesc   
as
select o.name as table_name,c.name as field_name,t.name as type,c.length as

length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp
from syscolumns c 
join systypes t on c.xtype = t.xusertype
join sysobjects o on o.id=c.id
left join    sysproperties p on p.smallid=c.colid and p.id=o.id   
where o.xtype='U'

查詢時:

Select * from fielddesc where table_name = '你的表名'
 

還有個更強的語句，是鄒建寫的，也寫出來吧

SELECT
 (case when a.colorder=1 then d.name else '' end) N'表名',
 a.colorder N'欄位序號',
 a.name N'欄位名',
 (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'標識',
 (case when (SELECT count(*)
 FROM sysobjects
 WHERE (name in
           (SELECT name
          FROM sysindexes
          WHERE (id = a.id) AND (indid in
                    (SELECT indid
                   FROM sysindexkeys
                   WHERE (id = a.id) AND (colid in
                             (SELECT colid
                            FROM syscolumns
                            WHERE (id = a.id) AND (name = a.name))))))) AND
        (xtype = 'PK'))>0 then '√' else '' end) N'主鍵',
 b.name N'類型',
 a.length N'佔用位元組數',
 COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'長度',
 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小數位元',
 (case when a.isnullable=1 then '√'else '' end) N'允許空',
 isnull(e.text,'') N'預設值',
 isnull(g.[value],'') AS N'欄位說明'
--into ##tx

FROM  syscolumns  a left join systypes b
on  a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id  and  d.xtype='U' and  d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid 
order by object_name(a.id),a.colorder

六、 時間格式轉換問題
因為新開發的軟體需要用一些舊軟體產生的一些資料，在時間格式上不統一，只能手工轉換，研究了一下午寫了三條語句，以前沒怎麼用過convert函數和case語句，還有"+"操作符在不同上下文環境也會起到不同的作用，把我搞暈了要，不過現在看來是差不多弄好了。

1、把所有"70.07.06"這樣的值變成"1970-07-06"

UPDATE lvshi
SET shengri = '19' + REPLACE(shengri, '.', '-')
WHERE (zhiyezheng = '139770070153')
 

2、在"1970-07-06"裡提取"70","07","06"

SELECT SUBSTRING(shengri, 3, 2) AS year, SUBSTRING(shengri, 6, 2) AS month,
      SUBSTRING(shengri, 9, 2) AS day
FROM lvshi
WHERE (zhiyezheng = '139770070153')

3、把一個時間類型欄位轉換成"1970-07-06"

UPDATE lvshi
SET shenling = CONVERT(varchar(4), YEAR(shenling))
      + '-' + CASE WHEN LEN(MONTH(shenling)) = 1 THEN '0' + CONVERT(varchar(2),
      month(shenling)) ELSE CONVERT(varchar(2), month(shenling))
      END + '-' + CASE WHEN LEN(day(shenling)) = 1 THEN '0' + CONVERT(char(2),
      day(shenling)) ELSE CONVERT(varchar(2), day(shenling)) END
WHERE (zhiyezheng = '139770070153')

七、 分區視圖
分區視圖是提高查詢效能的一個很好的辦法

--看下面的樣本

--樣本表
create table tempdb.dbo.t_10(
id int primary key check(id between 1 and 10),name varchar(10))

create table pubs.dbo.t_20(
id int primary key check(id between 11 and 20),name varchar(10))

create table northwind.dbo.t_30(
id int primary key check(id between 21 and 30),name varchar(10))
go

--分區視圖
create view v_t
as
select * from tempdb.dbo.t_10
union all
select * from pubs.dbo.t_20
union all
select * from northwind.dbo.t_30
go

--插入資料
insert v_t select 1 ,'aa'
union  all select 2 ,'bb'
union  all select 11,'cc'
union  all select 12,'dd'
union  all select 21,'ee'
union  all select 22,'ff'

--更新資料
update v_t set name=name+'_更新' where right(id,1)=1

--刪除測試
delete from v_t where right(id,1)=2

--顯示結果
select * from v_t
go

--刪除測試
drop table northwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10
drop view v_t

/**//*--測試結果

id          name      
----------- ----------
1           aa_更新
11          cc_更新
21          ee_更新

（所影響的行數為 3 行）
==*/

八、 樹型的實現

--參考

--樹形資料查詢樣本
--作者: 鄒建

--樣本資料
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中國'
union  all  select 0,'美國'
union  all  select 0,'加拿大'
union  all  select 1,'北京'
union  all  select 1,'上海'
union  all  select 1,'江蘇'
union  all  select 6,'蘇州'
union  all  select 7,'常熟'
union  all  select 6,'南京'
union  all  select 6,'無錫'
union  all  select 2,'紐約'
union  all  select 2,'舊金山'
go

--查詢指定id的所有子
create function f_cid(
@id int
)returns @re table([id] int,[level] int)
as
begin
 declare @l int
 set @l=0
 insert @re select @id,@l
 while @@rowcount>0
 begin
  set @l=@l+1
  insert @re select a.[id],@l
  from [tb] a,@re b
  where a.[pid]=b.[id] and b.[level]=@l-1
 end
/**//**//**//*--如果只顯示最明細的子(下面沒有子),則加上這個刪除
 delete a from @re a
 where exists(
  select 1 from [tb] where [pid]=a.[id])
--*/
 return
end
go

--調用(查詢所有的子)
select a.*,層次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
go

--刪除測試
drop table [tb]
drop function f_cid
go

 

九、 排序問題
資料庫裡有1,2,3,4,5 共5條記錄,要用一條sql語句讓其排序,使它排列成4,5,1,2,3,怎麼寫?

CREATE TABLE [t] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [GUID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

下面這句執行5次

insert t values (newid())

查看執行結果

select * from t

1、 第一種

select * from t
 order by case id when 4 then 1
                  when 5 then 2
                  when 1 then 3
                  when 2 then 4
                  when 3 then 5 end

2、 第二種

select * from t order by (id+2)%6

3、 第三種

select * from t order by charindex(cast(id as varchar),'45123')

4、 第四種

select * from t
WHERE id between 0 and 5
order by charindex(cast(id as varchar),'45123')

5、 第五種

select * from t order by case when id >3 then id-5 else id end

6、 第六種

select * from t order by id / 4 desc,id asc
 

十、 一條語句刪除一批記錄
首先id列是int標識類類型，然後刪除ID值為5,6,8,9,10,11的列，這裡的cast函數不能用convert函數代替，而且轉換的類型必須是varchar，而不能是char，否則就會執行出你不希望的結果，這裡的"5,6,8,9,10,11"可以是你在頁面上擷取的一個chkboxlist構建成的值，然後用下面的一句就全部刪
除了，比迴圈用多條語句高效吧應該。

delete from [fujian] where charindex(','+cast([id] as varchar)+',',','+'5,6,8,9,10,11,'+',')>0

還有一種就是

delete from table1 where id in(1,2,3,4 )

十一、擷取子表內的一列資料的組合字元串
下面這個函數擷取05年已經註冊了的某個所的律師，唯一一個參數就是事務所的名稱，然後返回zhuce欄位裡包含05字樣的所有律師。

CREATE   FUNCTION fn_Get05LvshiNameBySuo  (@p_suo Nvarchar(50))
RETURNS Nvarchar(2000)
AS
BEGIN 
 DECLARE @LvshiNames varchar(2000), @name varchar(50)
 select @LvshiNames=''
 DECLARE lvshi_cursor CURSOR FOR
 select [name] from [lvshi] where charindex(','+'05'+',',','+zhuce+',')>0 and suo=@p_suo order by id desc
 OPEN lvshi_cursor
 FETCH NEXT FROM lvshi_cursor
 INTO @name
 WHILE @@FETCH_STATUS = 0
 BEGIN
     SELECT @LvshiNames = @LvshiNames + @name + ','
  fETCH NEXT FROM lvshi_cursor
  INTO @name
 END
 CLOSE lvshi_cursor
 DEALLOCATE lvshi_cursor
 RETURN(@LvshiNames)
END

十二、讓0變成1，1變成0

declare @a int
set @a =0 --初始為0
select @a
set @a = @a^1 --把0變成1
select @a
set @a = @a^1 --把1變成0
select @a