selecttop0*into[t1]from[t2] 二、獲取數(shù)據(jù)庫中某個(gè)對(duì)象的創(chuàng)建腳本 1、先用下面的腳本創(chuàng)建一個(gè)函數(shù) ifexists(select1fromsysobjectswhereid=object_id('fgetscript')andobjectproperty(id,'IsInlineFunction')=0)
dropfunctionfgetscript
go createfunctionfgetscript(
@servernamevarchar(50)--服務(wù)器名
,@useridvarchar(50)='sa'--用戶名,如果為nt驗(yàn)證方式,則為空
,@passwordvarchar(50)=''--密碼
,@databasenamevarchar(50)--數(shù)據(jù)庫名稱
,@objectnamevarchar(250)--對(duì)象名 )returnsvarchar(8000)
as
begin
declare@revarchar(8000)--返回腳本
declare@srvidint,@dbsidint--定義服務(wù)器、數(shù)據(jù)庫集id
declare@dbidint,@tbidint--數(shù)據(jù)庫、表id
declare@errint,@srcvarchar(255),@descvarchar(255)--錯(cuò)誤處理變量 --創(chuàng)建sqldmo對(duì)象
exec@err=sp_oacreate'sqldmo.sqlserver',@srvidoutput
if@err〈〉0gotolberr --連接服務(wù)器
ifisnull(@userid,'')=''--如果是Nt驗(yàn)證方式
begin
exec@err=sp_oasetproperty@srvid,'loginsecure',1
if@err〈〉0gotolberr exec@err=sp_oamethod@srvid,'connect',null,@servername
end
else
exec@err=sp_oamethod@srvid,'connect',null,@servername,@userid,@password if@err〈〉0gotolberr --獲取數(shù)據(jù)庫集
exec@err=sp_oagetproperty@srvid,'databases',@dbsidoutput
if@err〈〉0gotolberr --獲取要取得腳本的數(shù)據(jù)庫id
exec@err=sp_oamethod@dbsid,'item',@dbidoutput,@databasename
if@err〈〉0gotolberr --獲取要取得腳本的對(duì)象id
exec@err=sp_oamethod@dbid,'getobjectbyname',@tbidoutput,@objectname
if@err〈〉0gotolberr --取得腳本
exec@err=sp_oamethod@tbid,'script',@reoutput
if@err〈〉0gotolberr --print@re
return(@re) lberr:
execsp_oageterrorinfoNULL,@srcout,@descout
declare@errbvarbinary(4)
set@errb=cast(@errasvarbinary(4))
execmaster..xp_varbintohexstr@errb,@reout
set@re='錯(cuò)誤號(hào):'+@re
+char(13)+'錯(cuò)誤源:'+@src
+char(13)+'錯(cuò)誤描述:'+@desc
return(@re)
end
go
2、用法如下
用法如下, printdbo.fgetscript('服務(wù)器名','用戶名','密碼','數(shù)據(jù)庫名','表名或其它對(duì)象名') 3、如果要獲取庫里所有對(duì)象的腳本，如如下方式 declare@namevarchar(250)
declare#aacursorfor
selectnamefromsysobjectswherextypenotin('S','PK','D','X','L')
open#aa
fetchnextfrom#aainto@name
while@@fetch_status=0
begin
printdbo.fgetscript('onlytiancai','sa','sa','database',@name)
fetchnextfrom#aainto@name
end
close#aa
deallocate#aa 4、聲明，此函數(shù)是csdn鄒建鄒老大提供的
三、分隔字符串
如果有一個(gè)用逗號(hào)分割開的字符串，比如說“a,b,c,d,1,2,3,4“，如何用t-sql獲取這個(gè)字符串有幾個(gè)元素，獲取第幾個(gè)元素的值是多少呢？因?yàn)閠-sql里沒有split函數(shù)，也沒有數(shù)組的概念，所以只能自己寫幾個(gè)函數(shù)了。
1、獲取元素個(gè)數(shù)的函數(shù) createfunctiongetstrarrlength(@strvarchar(8000))
returnsint
as
begin
declare@int_returnint
declare@startint
declare@nextint
declare@locationint
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ù) createfunctiongetstrofindex(@strvarchar(8000),@indexint=0)
returnsvarchar(8000)
as
begin
declare@str_returnvarchar(8000)
declare@startint
declare@nextint
declare@locationint
select@start=1
select@next=1--如果習(xí)慣從0開始則select@next=0
select@location=charindex(',',@str,@start)
while(@location〈〉0and@index〉@next)
begin
select@start=@location+1
select@location=charindex(',',@str,@start)
select@next=@next+1
end
if@location=0select@location=len(@str)+1--如果是因?yàn)闆]有逗號(hào)退出，則認(rèn)為逗號(hào)在字符串后
select@str_return=substring(@str,@start,@location-@start)--@start肯定是逗號(hào)之后的位置或者就是初始值1
if(@index〈〉@next)select@str_return=''--如果二者不相等，則是因?yàn)槎禾?hào)太少，或者@index小于@next的初始值1。
return@str_return
end 3、測(cè)試 SELECT[dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')
SELECT[dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5) 四、一條語句執(zhí)行跨越若干個(gè)數(shù)據(jù)庫
我要在一條語句里操作不同的服務(wù)器上的不同的數(shù)據(jù)庫里的不同的表,怎么辦呢?
第一種方法:
select*fromOPENDATASOURCE('SQLOLEDB','DataSource=遠(yuǎn)程ip;UserID=sa;Password=密碼').庫名.dbo.表名 第二種方法:
先使用聯(lián)結(jié)服務(wù)器:
EXECsp_addlinkedserver'別名','','MSDASQL',NULL,NULL,'DRIVER={SQLServer};SERVER=遠(yuǎn)程名;UID=用戶;PWD=密碼;'
execsp_addlinkedsrvlogin@rmtsrvname='別名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密碼'
GO 然后你就可以如下： select*from別名.庫名.dbo.表名
insert庫名.dbo.表名select*from別名.庫名.dbo.表名
select*into庫名.dbo.新表名from別名.庫名.dbo.表名
go 五、怎樣獲取一個(gè)表中所有的字段信息
蛙蛙推薦：怎樣獲取一個(gè)表中所有字段的信息
先創(chuàng)建一個(gè)視圖 Createviewfielddesc
as
selecto.nameastable_name,c.nameasfield_name,t.nameastype,c.lengthas length,c.isnullableasisnullable,convert(varchar(30),p.value)asdesp
fromsyscolumnsc
joinsystypestonc.xtype=t.xusertype
joinsysobjectsoono.id=c.id
leftjoinsyspropertiesponp.smallid=c.colidandp.id=o.id
whereo.xtype='U'
查詢時(shí): Select*fromfielddescwheretable_name='你的表名'
還有個(gè)更強(qiáng)的語句，是鄒建寫的，也寫出來吧 SELECT
(casewhena.colorder=1thend.nameelse''end)N'表名',
a.colorderN'字段序號(hào)',
a.nameN'字段名',
(casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''end)N'標(biāo)識(shí)',
(casewhen(SELECTcount(*)
FROMsysobjects
WHERE(namein
(SELECTname
FROMsysindexes
WHERE(id=a.id)AND(indidin
(SELECTindid
FROMsysindexkeys
WHERE(id=a.id)AND(colidin
(SELECTcolid
FROMsyscolumns
WHERE(id=a.id)AND(name=a.name)))))))AND
(xtype='PK'))〉0then'√'else''end)N'主鍵',
b.nameN'類型',
a.lengthN'占用字節(jié)數(shù)',
COLUMNPROPERTY(a.id,a.name,'PRECISION')asN'長(zhǎng)度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)asN'小數(shù)位數(shù)',
(casewhena.isnullable=1then'√'else''end)N'允許空',
isnull(e.text,'')N'默認(rèn)值',
isnull(g.[value],'')ASN'字段說明'
--into##tx FROMsyscolumnsaleftjoinsystypesb
ona.xtype=b.xusertype
innerjoinsysobjectsd
ona.id=d.idandd.xtype='U'andd.name〈〉'dtproperties'
leftjoinsyscommentse
ona.cdefault=e.id
leftjoinsyspropertiesg
ona.id=g.idANDa.colid=g.smallid
orderbyobject_name(a.id),a.colorder
六、時(shí)間格式轉(zhuǎn)換問題 因?yàn)樾麻_發(fā)的軟件需要用一些舊軟件生成的一些數(shù)據(jù)，在時(shí)間格式上不統(tǒng)一，只能手工轉(zhuǎn)換，研究了一下午寫了三條語句，以前沒怎么用過convert函數(shù)和case語句，還有“+“操作符在不同上下文環(huán)境也會(huì)起到不同的作用，把我搞暈了要，不過現(xiàn)在看來是差不多弄好了。 1、把所有“70.07.06“這樣的值變成“1970-07-06“ UPDATElvshi
SETshengri='19'+REPLACE(shengri,'.','-')
WHERE(zhiyezheng='139770070153')
2、在“1970-07-06“里提取“70“,“07“,“06“ SELECTSUBSTRING(shengri,3,2)ASyear,SUBSTRING(shengri,6,2)ASmonth,
SUBSTRING(shengri,9,2)ASday
FROMlvshi
WHERE(zhiyezheng='139770070153') 3、把一個(gè)時(shí)間類型字段轉(zhuǎn)換成“1970-07-06“ UPDATElvshi
SETshenling=CONVERT(varchar(4),YEAR(shenling))
+'-'+CASEWHENLEN(MONTH(shenling))=1THEN'0'+CONVERT(varchar(2),
month(shenling))ELSECONVERT(varchar(2),month(shenling))
END+'-'+CASEWHENLEN(day(shenling))=1THEN'0'+CONVERT(char(2),
day(shenling))ELSECONVERT(varchar(2),day(shenling))END
WHERE(zhiyezheng='139770070153') 七、分區(qū)視圖 分區(qū)視圖是提高查詢性能的一個(gè)很好的辦法 --看下面的示例 --示例表
createtabletempdb.dbo.t_10(
idintprimarykeycheck(idbetween1and10),namevarchar(10)) createtablepubs.dbo.t_20(
idintprimarykeycheck(idbetween11and20),namevarchar(10)) createtablenorthwind.dbo.t_30(
idintprimarykeycheck(idbetween21and30),namevarchar(10))
go --分區(qū)視圖
createviewv_t
as
select*fromtempdb.dbo.t_10
unionall
select*frompubs.dbo.t_20
unionall
select*fromnorthwind.dbo.t_30
go --插入數(shù)據(jù)
insertv_tselect1,'aa'
unionallselect2,'bb'
unionallselect11,'cc'
unionallselect12,'dd'
unionallselect21,'ee'
unionallselect22,'ff' --更新數(shù)據(jù)
updatev_tsetname=name+'_更新'whereright(id,1)=1 --刪除測(cè)試
deletefromv_twhereright(id,1)=2 --顯示結(jié)果
select*fromv_t
go --刪除測(cè)試
droptablenorthwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10
dropviewv_t /**//*--測(cè)試結(jié)果 idname
---------------------
1aa_更新
11cc_更新
21ee_更新 （所影響的行數(shù)為3行）
==*/
八、樹型的實(shí)現(xiàn) --參考 --樹形數(shù)據(jù)查詢示例
--作者:鄒建 --示例數(shù)據(jù)
createtable[tb]([id]intidentity(1,1),[pid]int,namevarchar(20))
insert[tb]select0,'中國'
unionallselect0,'美國'
unionallselect0,'加拿大'
unionallselect1,'北京'
unionallselect1,'上海'
unionallselect1,'江蘇'
unionallselect6,'蘇州'
unionallselect7,'常熟'
unionallselect6,'南京'
unionallselect6,'無錫'
unionallselect2,'紐約'
unionallselect2,'舊金山'
go --查詢指定id的所有子
createfunctionf_cid(
@idint
)returns@retable([id]int,[level]int)
as
begin
declare@lint
set@l=0
insert@reselect@id,@l
while@@rowcount〉0
begin
set@l=@l+1
insert@reselecta.[id],@l
from[tb]a,@reb
wherea.[pid]=b.[id]andb.[level]=@l-1
end
/**//**//**//*--如果只顯示最明細(xì)的子(下面沒有子),則加上這個(gè)刪除
deleteafrom@rea
whereexists(
select1from[tb]where[pid]=a.[id])
--*/
return
end
go --調(diào)用(查詢所有的子)
selecta.*,層次=b.[level]from[tb]a,f_cid(2)bwherea.[id]=b.[id]
go --刪除測(cè)試
droptable[tb]
dropfunctionf_cid
go 九、排序問題 CREATETABLE[t](
[id][int]IDENTITY(1,1)NOTNULL,
[GUID][uniqueidentifier]NULL
)ON[PRIMARY]
GO 下面這句執(zhí)行5次
inserttvalues(newid()) 查看執(zhí)行結(jié)果
select*fromt 1、第一種 select*fromt
orderbycaseidwhen4then1
when5then2
when1then3
when2then4
when3then5end 2、第二種 select*fromtorderby(id+2)%6 3、第三種 select*fromtorderbycharindex(cast(idasvarchar),'45123') 4、第四種 select*fromt
WHEREidbetween0and5
orderbycharindex(cast(idasvarchar),'45123') 5、第五種 select*fromtorderbycasewhenid〉3thenid-5elseidend 6、第六種 select*fromtorderbyid/4desc,idasc
十、一條語句刪除一批記錄
首先id列是int標(biāo)識(shí)類類型，然后刪除ID值為5,6,8,9,10,11的列，這里的cast函數(shù)不能用convert函數(shù)代替，而且轉(zhuǎn)換的類型必須是varchar，而不能是char，否則就會(huì)執(zhí)行出你不希望的結(jié)果，這里的“5,6,8,9,10,11“可以是你在頁面上獲取的一個(gè)chkboxlist構(gòu)建成的值，然后用下面的一句就全部刪
除了，比循環(huán)用多條語句高效吧應(yīng)該。 deletefrom[fujian]wherecharindex(','+cast([id]asvarchar)+',',','+'5,6,8,9,10,11,'+',')〉0 還有一種就是 deletefromtable1whereidin(1,2,3,4) 十一、獲取子表內(nèi)的一列數(shù)據(jù)的組合字符串
下面這個(gè)函數(shù)獲取05年已經(jīng)注冊(cè)了的某個(gè)所的律師，唯一一個(gè)參數(shù)就是事務(wù)所的名稱，然后返回zhuce字段里包含05字樣的所有律師。
CREATEFUNCTIONfn_Get05LvshiNameBySuo(@p_suoNvarchar(50))
RETURNSNvarchar(2000)
AS
BEGIN
DECLARE@LvshiNamesvarchar(2000),@namevarchar(50)
select@LvshiNames=''
DECLARElvshi_cursorCURSORFOR
數(shù)據(jù)庫里有1,2,3,4,5共5條記錄,要用一條sql語句讓其排序,使它排列成4,5,1,2,3,怎么寫?

國外服務(wù)器租用

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。