SQL Server 一些 T-SQL 技巧
2022-11-12 09:52:44
内容摘要
这篇文章主要为大家详细介绍了SQL Server 一些 T-SQL 技巧，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！一、只复制一个表结构，不复制数
文章正文
这篇文章主要为大家详细介绍了SQL Server 一些 T-SQL 技巧，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
一、只复制一个表结构，不复制数据selecttop0*into[t1]from[t2]二、获取数据库中某个对象的创建脚本1、先用下面的脚本创建一个函数ifexists(select1fromsysobjectswhereid=object_id('fgetscript')andobjectproperty(id,'IsInlineFunction')=0)dropfunctionfgetscriptgocreatefunctionfgetscript(@servernamevarchar(50)--服务器名,@useridvarchar(50)='sa'--用户名,如果为nt验证方式,则为空,@passwordvarchar(50)=''--密码,@databasenamevarchar(50)--数据库名称,@objectnamevarchar(250)--对象名)returnsvarchar(8000)asbegindeclare@revarchar(8000)--返回脚本declare@srvidint,@dbsidint--定义服务器、数据库集iddeclare@dbidint,@tbidint--数据库、表iddeclare@errint,@srcvarchar(255),@descvarchar(255)--错误处理变量--创建sqldmo对象exec@err=sp_oacreate'sqldmo.sqlserver',@srvidoutputif@err〈〉0gotolberr--连接服务器ifisnull(@userid,'')=''--如果是Nt验证方式beginexec@err=sp_oasetproperty@srvid,'loginsecure',1if@err〈〉0gotolberrexec@err=sp_oamethod@srvid,'connect',null,@servernameendelseexec@err=sp_oamethod@srvid,'connect',null,@servername,@userid,@passwordif@err〈〉0gotolberr--获取数据库集exec@err=sp_oagetproperty@srvid,'databases',@dbsidoutputif@err〈〉0gotolberr--获取要取得脚本的数据库idexec@err=sp_oamethod@dbsid,'item',@dbidoutput,@databasenameif@err〈〉0gotolberr--获取要取得脚本的对象idexec@err=sp_oamethod@dbid,'getobjectbyname',@tbidoutput,@objectnameif@err〈〉0gotolberr--取得脚本exec@err=sp_oamethod@tbid,'script',@reoutputif@err〈〉0gotolberr--print@rereturn(@re)lberr:execsp_oageterrorinfoNULL,@srcout,@descoutdeclare@errbvarbinary(4)set@errb=cast(@errasvarbinary(4))execmaster..xp_varbintohexstr@errb,@reoutset@re='错误号:'+@re+char(13)+'错误源:'+@src+char(13)+'错误描述:'+@descreturn(@re)endgo2、用法如下用法如下,printdbo.fgetscript('服务器名','用户名','密码','数据库名','表名或其它对象名')3、如果要获取库里所有对象的脚本，如如下方式declare@namevarchar(250)declare#aacursorforselectnamefromsysobjectswherextypenotin('S','PK','D','X','L')open#aafetchnextfrom#aainto@namewhile@@fetch_status=0beginprintdbo.fgetscript('onlytiancai','sa','sa','database',@name)fetchnextfrom#aainto@nameendclose#aadeallocate#aa4、声明，此函数是csdn邹建邹老大提供的三、分隔字符串如果有一个用逗号分割开的字符串，比如说“a,b,c,d,1,2,3,4“，如何用t-sql获取这个字符串有几个元素，获取第几个元素的值是多少呢？因为t-sql里没有split函数，也没有数组的概念，所以只能自己写几个函数了。1、获取元素个数的函数createfunctiongetstrarrlength(@strvarchar(8000))returnsintasbegindeclare@int_returnintdeclare@startintdeclare@nextintdeclare@locationintselect@str=','+@str+','select@str=replace(@str,',,',',')select@start=1select@next=1select@location=charindex(',',@str,@start)while(@location〈〉0)beginselect@start=@location+1select@location=charindex(',',@str,@start)select@next=@next+1endselect@int_return=@next-2return@int_returnend2、获取指定索引的值的函数createfunctiongetstrofindex(@strvarchar(8000),@indexint=0)returnsvarchar(8000)asbegindeclare@str_returnvarchar(8000)declare@startintdeclare@nextintdeclare@locationintselect@start=1select@next=1--如果习惯从0开始则select@next=0select@location=charindex(',',@str,@start)while(@location〈〉0and@index〉@next)beginselect@start=@location+1select@location=charindex(',',@str,@start)select@next=@next+1endif@location=0select@location=len(@str)+1--如果是因为没有逗号退出，则认为逗号在字符串后select@str_return=substring(@str,@start,@location-@start)--@start肯定是逗号之后的位置或者就是初始值1if(@index〈〉@next)select@str_return=''--如果二者不相等，则是因为逗号太少，或者@index小于@next的初始值1。return@str_returnend3、测试SELECT[dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')SELECT[dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5)四、一条语句执行跨越若干个数据库我要在一条语句里操作不同的服务器上的不同的数据库里的不同的表,怎么办呢?第一种方法:select*fromOPENDATASOURCE('SQLOLEDB','DataSource=远程ip;UserID=sa;Password=密码').库名.dbo.表名第二种方法:先使用联结服务器:EXECsp_addlinkedserver'别名','','MSDASQL',NULL,NULL,'DRIVER={SQLServer};SERVER=远程名;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五、怎样获取一个表中所有的字段信息蛙蛙推荐：怎样获取一个表中所有字段的信息先创建一个视图Createviewfielddescasselecto.nameastable_name,c.nameasfield_name,t.nameastype,c.lengthaslength,c.isnullableasisnullable,convert(varchar(30),p.value)asdespfromsyscolumnscjoinsystypestonc.xtype=t.xusertypejoinsysobjectsoono.id=c.idleftjoinsyspropertiesponp.smallid=c.colidandp.id=o.idwhereo.xtype='U'查询时:Select*fromfielddescwheretable_name='你的表名'还有个更强的语句，是邹建写的，也写出来吧SELECT(casewhena.colorder=1thend.nameelse''end)N'表名',a.colorderN'字段序号',a.nameN'字段名',(casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''end)N'标识',(casewhen(SELECTcount(*)FROMsysobjectsWHERE(namein(SELECTnameFROMsysindexesWHERE(id=a.id)AND(indidin(SELECTindidFROMsysindexkeysWHERE(id=a.id)AND(colidin(SELECTcolidFROMsyscolumnsWHERE(id=a.id)AND(name=a.name)))))))AND(xtype='PK'))〉0then'√'else''end)N'主键',b.nameN'类型',a.lengthN'占用字节数',COLUMNPROPERTY(a.id,a.name,'PRECISION')asN'长度',isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)asN'小数位数',(casewhena.isnullable=1then'√'else''end)N'允许空',isnull(e.text,'')N'默认值',isnull(g.[value],'')ASN'字段说明'--into##txFROMsyscolumnsaleftjoinsystypesbona.xtype=b.xusertypeinnerjoinsysobjectsdona.id=d.idandd.xtype='U'andd.name〈〉'dtproperties'leftjoinsyscommentseona.cdefault=e.idleftjoinsyspropertiesgona.id=g.idANDa.colid=g.smallidorderbyobject_name(a.id),a.colorder六、时间格式转换问题因为新开发的软件需要用一些旧软件生成的一些数据，在时间格式上不统一，只能手工转换，研究了一下午写了三条语句，以前没怎么用过convert函数和case语句，还有“+“操作符在不同上下文环境也会起到不同的作用，把我搞晕了要，不过现在看来是差不多弄好了。1、把所有“70.07.06“这样的值变成“1970-07-06“UPDATElvshiSETshengri='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)ASdayFROMlvshiWHERE(zhiyezheng='139770070153')3、把一个时间类型字段转换成“1970-07-06“UPDATElvshiSETshenling=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))ENDWHERE(zhiyezheng='139770070153')七、分区视图分区视图是提高查询性能的一个很好的办法--看下面的示例--示例表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--分区视图createviewv_tasselect*fromtempdb.dbo.t_10unionallselect*frompubs.dbo.t_20unionallselect*fromnorthwind.dbo.t_30go--插入数据insertv_tselect1,'aa'unionallselect2,'bb'unionallselect11,'cc'unionallselect12,'dd'unionallselect21,'ee'unionallselect22,'ff'--更新数据updatev_tsetname=name+'_更新'whereright(id,1)=1--删除测试deletefromv_twhereright(id,1)=2--显示结果select*fromv_tgo--删除测试droptablenorthwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10dropviewv_t/**//*--测试结果idname---------------------1aa_更新11cc_更新21ee_更新（所影响的行数为3行）==*/八、树型的实现--参考--树形数据查询示例--作者:邹建--示例数据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)asbegindeclare@lintset@l=0insert@reselect@id,@lwhile@@rowcount〉0beginset@l=@l+1insert@reselecta.[id],@lfrom[tb]a,@rebwherea.[pid]=b.[id]andb.[level]=@l-1end/**//**//**//*--如果只显示最明细的子(下面没有子),则加上这个删除deleteafrom@reawhereexists(select1from[tb]where[pid]=a.[id])--*/returnendgo--调用(查询所有的子)selecta.*,层次=b.[level]from[tb]a,f_cid(2)bwherea.[id]=b.[id]go--删除测试droptable[tb]dropfunctionf_cidgo九、排序问题CREATETABLE[t]([id][int]IDENTITY(1,1)NOTNULL,[GUID][uniqueidentifier]NULL)ON[PRIMARY]GO下面这句执行5次inserttvalues(newid())查看执行结果select*fromt1、第一种select*fromtorderbycaseidwhen4then1when5then2when1then3when2then4when3then5end2、第二种select*fromtorderby(id+2)%63、第三种select*fromtorderbycharindex(cast(idasvarchar),'45123')4、第四种select*fromtWHEREidbetween0and5orderbycharindex(cast(idasvarchar),'45123')5、第五种select*fromtorderbycasewhenid〉3thenid-5elseidend6、第六种select*fromtorderbyid/4desc,idasc十、一条语句删除一批记录首先id列是int标识类类型，然后删除ID值为5,6,8,9,10,11的列，这里的cast函数不能用convert函数代替，而且转换的类型必须是varchar，而不能是char，否则就会执行出你不希望的结果，这里的“5,6,8,9,10,11“可以是你在页面上获取的一个chkboxlist构建成的值，然后用下面的一句就全部删除了，比循环用多条语句高效吧应该。deletefrom[fujian]wherecharindex(','+cast([id]asvarchar)+',',','+'5,6,8,9,10,11,'+',')〉0还有一种就是deletefromtable1whereidin(1,2,3,4)十一、获取子表内的一列数据的组合字符串下面这个函数获取05年已经注册了的某个所的律师，唯一一个参数就是事务所的名称，然后返回zhuce字段里包含05字样的所有律师。CREATEFUNCTIONfn_Get05LvshiNameBySuo(@p_suoNvarchar(50))RETURNSNvarchar(2000)ASBEGINDECLARE@LvshiNamesvarchar(2000),@namevarchar(50)select@LvshiNames=''DECLARElvshi_cursorCURSORFOR数据库里有1,2,3,4,5共5条记录,要用一条sql语句让其排序,使它排列成4,5,1,2,3,怎么写?注：关于SQL Server 一些 T-SQL 技巧的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
