一些 T-SQL 技巧
更新時間：2006年12月21日 00:00:00 作者：
一、 只復制一個表結構，不復制數(shù)據(jù)
select top 0 * into [t1] from [t2]
二、 獲取數(shù)據(jù)庫中某個對象的創(chuàng)建腳本
1、 先用下面的腳本創(chuàng)建一個函數(shù)
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) --數(shù)據(jù)庫名稱
,@objectname varchar(250) --對象名
) returns varchar(8000)
as
begin
declare @re varchar(8000) --返回腳本
declare @srvid int,@dbsid int --定義服務器、數(shù)據(jù)庫集id
declare @dbid int,@tbid int --數(shù)據(jù)庫、表id
declare @err int,@src varchar(255), @desc varchar(255) --錯誤處理變量
--創(chuàng)建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
--獲取數(shù)據(jù)庫集
exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
if @err〈〉0 goto lberr
--獲取要取得腳本的數(shù)據(jù)庫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('服務器名','用戶名','密碼','數(shù)據(jù)庫名','表名或其它對象名')
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、 聲明，此函數(shù)是csdn鄒建鄒老大提供的
三、 分隔字符串
如果有一個用逗號分割開的字符串，比如說“a,b,c,d,1,2,3,4“，如何用t-sql獲取這個字符串有幾個元素，獲取第幾個元素的值是多少呢？因為t-sql里沒有split函數(shù)，也沒有數(shù)組的概念，所以只能自己寫幾個函數(shù)了。
1、 獲取元素個數(shù)的函數(shù)
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、 獲取指定索引的值的函數(shù)
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)
四、 一條語句執(zhí)行跨越若干個數(shù)據(jù)庫
我要在一條語句里操作不同的服務器上的不同的數(shù)據(jù)庫里的不同的表,怎么辦呢?
第一種方法:
select * from OPENDATASOURCE('SQLOLEDB','Data Source=遠程ip;User ID=sa;Password=密碼').庫名.dbo.表名
第二種方法:
先使用聯(lián)結服務器:
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
五、 怎樣獲取一個表中所有的字段信息
蛙蛙推薦：怎樣獲取一個表中所有字段的信息
先創(chuàng)建一個視圖
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'占用字節(jié)數(shù)',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'長度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小數(shù)位數(shù)',
(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
六、 時間格式轉換問題
因為新開發(fā)的軟件需要用一些舊軟件生成的一些數(shù)據(jù)，在時間格式上不統(tǒng)一，只能手工轉換，研究了一下午寫了三條語句，以前沒怎么用過convert函數(shù)和case語句，還有“+“操作符在不同上下文環(huán)境也會起到不同的作用，把我搞暈了要，不過現(xiàn)在看來是差不多弄好了。
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')
七、 分區(qū)視圖
分區(qū)視圖是提高查詢性能的一個很好的辦法
--看下面的示例
--示例表
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
--分區(qū)視圖
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
--插入數(shù)據(jù)
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'
--更新數(shù)據(jù)
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_更新
（所影響的行數(shù)為 3 行）
==*/
八、 樹型的實現(xiàn)
--參考
--樹形數(shù)據(jù)查詢示例
--作者: 鄒建
--示例數(shù)據(jù)
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
九、 排序問題
CREATE TABLE [t] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[GUID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
下面這句執(zhí)行5次
insert t values (newid())
查看執(zhí)行結果
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函數(shù)不能用convert函數(shù)代替，而且轉換的類型必須是varchar，而不能是char，否則就會執(zhí)行出你不希望的結果，這里的“5,6,8,9,10,11“可以是你在頁面上獲取的一個chkboxlist構建成的值，然后用下面的一句就全部刪
除了，比循環(huán)用多條語句高效吧應該。
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 )
十一、獲取子表內的一列數(shù)據(jù)的組合字符串
下面這個函數(shù)獲取05年已經(jīng)注冊了的某個所的律師，唯一一個參數(shù)就是事務所的名稱，然后返回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
數(shù)據(jù)庫里有1,2,3,4,5 共5條記錄,要用一條sql語句讓其排序,使它排列成4,5,1,2,3,怎么寫?
select top 0 * into [t1] from [t2]
二、 獲取數(shù)據(jù)庫中某個對象的創(chuàng)建腳本
1、 先用下面的腳本創(chuàng)建一個函數(shù)
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) --數(shù)據(jù)庫名稱
,@objectname varchar(250) --對象名
) returns varchar(8000)
as
begin
declare @re varchar(8000) --返回腳本
declare @srvid int,@dbsid int --定義服務器、數(shù)據(jù)庫集id
declare @dbid int,@tbid int --數(shù)據(jù)庫、表id
declare @err int,@src varchar(255), @desc varchar(255) --錯誤處理變量
--創(chuàng)建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
--獲取數(shù)據(jù)庫集
exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
if @err〈〉0 goto lberr
--獲取要取得腳本的數(shù)據(jù)庫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('服務器名','用戶名','密碼','數(shù)據(jù)庫名','表名或其它對象名')
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、 聲明，此函數(shù)是csdn鄒建鄒老大提供的
三、 分隔字符串
如果有一個用逗號分割開的字符串，比如說“a,b,c,d,1,2,3,4“，如何用t-sql獲取這個字符串有幾個元素，獲取第幾個元素的值是多少呢？因為t-sql里沒有split函數(shù)，也沒有數(shù)組的概念，所以只能自己寫幾個函數(shù)了。
1、 獲取元素個數(shù)的函數(shù)
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、 獲取指定索引的值的函數(shù)
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)
四、 一條語句執(zhí)行跨越若干個數(shù)據(jù)庫
我要在一條語句里操作不同的服務器上的不同的數(shù)據(jù)庫里的不同的表,怎么辦呢?
第一種方法:
select * from OPENDATASOURCE('SQLOLEDB','Data Source=遠程ip;User ID=sa;Password=密碼').庫名.dbo.表名
第二種方法:
先使用聯(lián)結服務器:
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
五、 怎樣獲取一個表中所有的字段信息
蛙蛙推薦：怎樣獲取一個表中所有字段的信息
先創(chuàng)建一個視圖
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'占用字節(jié)數(shù)',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'長度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小數(shù)位數(shù)',
(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
六、 時間格式轉換問題
因為新開發(fā)的軟件需要用一些舊軟件生成的一些數(shù)據(jù)，在時間格式上不統(tǒng)一，只能手工轉換，研究了一下午寫了三條語句，以前沒怎么用過convert函數(shù)和case語句，還有“+“操作符在不同上下文環(huán)境也會起到不同的作用，把我搞暈了要，不過現(xiàn)在看來是差不多弄好了。
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')
七、 分區(qū)視圖
分區(qū)視圖是提高查詢性能的一個很好的辦法
--看下面的示例
--示例表
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
--分區(qū)視圖
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
--插入數(shù)據(jù)
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'
--更新數(shù)據(jù)
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_更新
（所影響的行數(shù)為 3 行）
==*/
八、 樹型的實現(xiàn)
--參考
--樹形數(shù)據(jù)查詢示例
--作者: 鄒建
--示例數(shù)據(jù)
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
九、 排序問題
CREATE TABLE [t] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[GUID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
下面這句執(zhí)行5次
insert t values (newid())
查看執(zhí)行結果
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函數(shù)不能用convert函數(shù)代替，而且轉換的類型必須是varchar，而不能是char，否則就會執(zhí)行出你不希望的結果，這里的“5,6,8,9,10,11“可以是你在頁面上獲取的一個chkboxlist構建成的值，然后用下面的一句就全部刪
除了，比循環(huán)用多條語句高效吧應該。
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 )
十一、獲取子表內的一列數(shù)據(jù)的組合字符串
下面這個函數(shù)獲取05年已經(jīng)注冊了的某個所的律師，唯一一個參數(shù)就是事務所的名稱，然后返回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
數(shù)據(jù)庫里有1,2,3,4,5 共5條記錄,要用一條sql語句讓其排序,使它排列成4,5,1,2,3,怎么寫?
您可能感興趣的文章:
- sqlserver2005 TSql新功能學習總結(數(shù)據(jù)類型篇)
- 如何在SQL Server 2008下輕松調試T-SQL語句和存儲過程
- SQLServer 2008 新增T-SQL 簡寫語法
- SQL Server 數(shù)據(jù)庫管理常用的SQL和T-SQL語句
- 通過T-SQL語句實現(xiàn)數(shù)據(jù)庫備份與還原的代碼
- SQL Server 數(shù)據(jù)庫管理常用的SQL和T-SQL語句
- T-SQL中使用正則表達式函數(shù)
- T-SQL篇如何防止SQL注入的解決方法
- T-SQL 查詢語句的執(zhí)行順序解析
- SQL Server中T-SQL 數(shù)據(jù)類型轉換詳解
相關文章
這篇文章主要介紹了介紹備份和還原MySQL數(shù)據(jù)的兩種方法,文中的操作環(huán)境基于CentOS,介紹了使用mysqldump和數(shù)據(jù)庫文件兩種方法,需要的朋友可以參考下2015-05-05
SQL Server中的排名函數(shù)與分析函數(shù)詳解
本文詳細講解了SQL Server中的排名函數(shù)與分析函數(shù)，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-05-05
sql語句中如何將datetime格式的日期轉換為yy-mm-dd格式
sql語句中如何將datetime格式的日期轉換為yy-mm-dd格式...2007-10-10
sqlserver substring函數(shù)使用方法小結
在操作sqlserver時候用到了substring函數(shù)，特整理一些實例，需要的朋友可以參考下。2009-12-12
SQL Server格式轉換函數(shù)Cast、Convert介紹
這篇文章介紹了SQL Server中的格式轉換函數(shù)Cast、Convert，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2022-05-05
這篇文章主要介紹了數(shù)據(jù)庫設計三大范式簡析，遵循范式是為了建立冗余較小、結構合理的數(shù)據(jù)庫,需要學習數(shù)據(jù)庫設計三大范式的朋友可以參考下2015-08-08
大數(shù)據(jù)量分頁存儲過程效率測試附測試代碼與結果
在項目中，我們經(jīng)常遇到或用到分頁，那么在大數(shù)據(jù)量（百萬級以上）下，哪種分頁算法效率最優(yōu)呢？我們不妨用事實說話。2010-07-07
SQL SERVER數(shù)據(jù)庫用datetime類型的時候 需要注意的地方2009-03-03
SQL?SERVER數(shù)據(jù)庫登陸錯誤18456解決過程
這篇文章主要介紹了SQL?SERVER數(shù)據(jù)庫登陸錯誤18456解決過程,18456錯誤是因密碼或用戶名錯誤而使身份驗證失敗并導致連接嘗試被拒或者賬戶被鎖定無法sa登錄,按照如下操作依次排查解決,需要的朋友可以參考下2024-10-10
這篇文章主要介紹了SQL數(shù)據(jù)庫連接超時時間已到的問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-04-04