开发MS-SQL数据库得方法
MS-SQL数据库开发常用汇总
转自：http://www.cnblogs.com/ghd258/archive/2006/03/20/354147.html
0.把长日期转换为短日期 Convert(char(10),getdate(),120)
1.按姓氏笔画排序:
Select*FromTableNameOrderByCustomerNameCollateChinese_PRC_Stroke_ci_as
2.数据库加密:
selectencrypt('原始密码')
selectpwdencrypt('原始密码')
selectpwdcompare('原始密码','加密后密码')=1--相同；否则不相同encrypt('原始密码')
selectpwdencrypt('原始密码')
selectpwdcompare('原始密码','加密后密码')=1--相同；否则不相同
3.取回表中字段:
declare@listvarchar(1000),@sqlnvarchar(1000)
select@list=@list+','+b.namefromsysobjectsa,syscolumnsbwherea.id=b.idanda.name='表A'
set@sql='select'+right(@list,len(@list)-1)+'from表A'
exec(@sql)
4.查看硬盘分区:
EXECmaster..xp_fixeddrives
5.比较A,B表是否相等:
if(selectchecksum_agg(binary_checksum(*))fromA)
=
(selectchecksum_agg(binary_checksum(*))fromB)
print'相等'
else
print'不相等'
6.杀掉所有的事件探察器进程:
DECLAREhcforeachCURSORGLOBALFORSELECT'kill'+RTRIM(spid)FROMmaster.dbo.sysprocesses
WHEREprogram_nameIN('SQLprofiler',N'SQL事件探查器')
EXECsp_msforeach_worker'?'
7.记录搜索:
开头到N条记录
SelectTopN*From表
-------------------------------
N到M条记录(要有主索引ID)
SelectTopM-N*From表WhereIDin(SelectTopMIDFrom表)OrderbyIDDesc
----------------------------------
N到结尾记录
SelectTopN*From表OrderbyIDDesc
8.如何修改数据库的名称:
sp_renamedb'old_name','new_name'
9：获取当前数据库中的所有用户表
selectNamefromsysobjectswherextype='u'andstatus>=0
10：获取某一个表的所有字段
selectnamefromsyscolumnswhereid=object_id('表名')
11：查看与某一个表相关的视图、存储过程、函数
selecta.*fromsysobjectsa,syscommentsbwherea.id=b.idandb.textlike'%表名%'
12：查看当前数据库中所有存储过程
selectnameas存储过程名称fromsysobjectswherextype='P'
13：查询用户创建的所有数据库
select*frommaster..sysdatabasesDwheresidnotin(selectsidfrommaster..sysloginswherename='sa')
或者
selectdbid,nameASDB_NAMEfrommaster..sysdatabaseswheresid＜>0x01
14：查询某一个表的字段和数据类型
selectcolumn_name,data_typefrominformation_schema.columns
wheretable_name='表名'
[n].[标题]:
Select*FromTableNameOrderByCustomerName
[n].[标题]:
来自http://dev.csdn.net/develop/article/83/83138.shtm
1.按姓氏笔画排序:
Select*FromTableNameOrderByCustomerNameCollateChinese_PRC_Stroke_ci_as2.数据库加密:selectencrypt('原始密码')selectpwdencrypt('原始密码')selectpwdcompare('原始密码','加密后密码')=1--相同；否则不相同encrypt('原始密码')selectpwdencrypt('原始密码')selectpwdcompare('原始密码','加密后密码')=1--相同；否则不相同3.取回表中字段:declare@listvarchar(1000),@sqlnvarchar(1000)select@list=@list+','+b.namefromsysobjectsa,syscolumnsbwherea.id=b.idanda.name='表A'set@sql='select'+right(@list,len(@list)-1)+'from表A'exec(@sql)4.查看硬盘分区:EXECmaster..xp_fixeddrives5.比较A,B表是否相等:if(selectchecksum_agg(binary_checksum(*))fromA)=(selectchecksum_agg(binary_checksum(*))fromB)print'相等'elseprint'不相等'6.杀掉所有的事件探察器进程:DECLAREhcforeachCURSORGLOBALFORSELECT'kill'+RTRIM(spid)FROMmaster.dbo.sysprocessesWHEREprogram_nameIN('SQLprofiler',N'SQL事件探查器')EXECsp_msforeach_worker'?'7.记录搜索:开头到N条记录SelectTopN*From表-------------------------------N到M条记录(要有主索引ID)SelectTopM-N*From表WhereIDin(SelectTopMIDFrom表)OrderbyIDDesc----------------------------------N到结尾记录SelectTopN*From表OrderbyIDDesc8.如何修改数据库的名称:sp_renamedb'old_name','new_name'9：获取当前数据库中的所有用户表selectNamefromsysobjectswherextype='u'andstatus>=010：获取某一个表的所有字段selectnamefromsyscolumnswhereid=object_id('表名')11：查看与某一个表相关的视图、存储过程、函数selecta.*fromsysobjectsa,syscommentsbwherea.id=b.idandb.textlike'%表名%'12：查看当前数据库中所有存储过程selectnameas存储过程名称fromsysobjectswherextype='P'13：查询用户创建的所有数据库select*frommaster..sysdatabasesDwheresidnotin(selectsidfrommaster..sysloginswherename='sa')或者selectdbid,nameASDB_NAMEfrommaster..sysdatabaseswheresid＜>0x0114：查询某一个表的字段和数据类型selectcolumn_name,data_typefrominformation_schema.columnswheretable_name='表名'[n].[标题]:Select*FromTableNameOrderByCustomerName[n].[标题]:一、只复制一个表结构，不复制数据
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)endgo
2、用法如下
用法如下,
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#aa
4、声明，此函数是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@retab本文地址：http://www.45fan.com/a/question/71212.html