【网学网提醒】：网学会员为大家收集整理了1000SQL经典语句sql语法提供大家参考，希望对大家有所帮助!
一、SQL基础
1、说明：创建数据库说明：databaseCREATEDATABASEdatabase-name说明：2、说明：删除数据库dropdatabasedbname说明：3、说明：备份sqlserver---创建备份数据的deviceUSEmaster'c:\mssql7backup\EXECsp_addumpdevice'disk','testBack','c:\mssql7backup\MyNwind_1.dat'---开始备份BACKUPDATABASEpubsTOtestBack说明：创建新表4、说明：创建新表createtabletabname(col1type1[notnull][primarykey],col2type2null],..)[notnull],..)根据已有的表创建新表：根据已有的表创建新表：(使用旧表创建新表使用旧表创建新表)A：createtabletab_newliketab_old(使用旧表创建新表)B：createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly5、说明：删除新表说明：droptabletabname说明：增加一个列6、说明：增加一个列Altertabletabnameaddcolumncoltype注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。说明：添加主键：Altertabletabnameaddprimarykey(col)7、说明：添加主键说明：删除主键：Altertabletabnamedropprimarykey(col)说明：创建索引：createtabname(col….)8、说明：创建索引create[unique]indexidxnameontabname(col….)删除索引：dropindexidxnamedrop注：索引是不可更改的，想更改必须删除重新建。说明：创建视图：createviewviewnameasselectstatement9、说明：创建视图删除视图：dropviewviewname删除视图10、说明：10、说明：几个简单的基本的sql语句选择：选择：select*fromtable1where范围插入：插入：insertintotable1(field1,field2)values(value1,value2)删除：删除：deletefromtable1where范围更新：updatetable1setfield1=value1where范围更新查找：select*fromtable1wherefield1like’%value1%’---like的语查找法很精妙，查资料!排序：select*fromtable1orderbyfield1,field2[desc]排序总数：selectcountastotalcountfromtable1总数
1
求和：selectsum(field1)assumvaluefromtable1求和平均：selectavg(field1)asavgvaluefromtable1平均最大：selectmax(field1)asmaxvaluefromtable1最大最小：selectmin(field1)asminvaluefromtable1最小11、说明：11、说明：几个高级查询运算词A：UNION运算符UNION运算符通过组合其他两个结果表（例如TABLE1和TABLE2）并消去表中任何重复行而派生出一个结果表。当ALL随UNION一起使用时（即UNIONALL），不消除重复行。两种情况下，派生表的每一行不是来自TABLE1就是来自TABLE2。B：EXCEPT运算符EXCEPT运算符通过包括所有在TABLE1中但不在TABLE2中的行并消除所有重复行而派生出一个结果表。当ALL随EXCEPT一起使用时(EXCEPTALL)，不消除重复行。C：INTERSECT运
算符INTERSECT运算符通过只包括TABLE1和TABLE2中都有的行并消除所有重复行而派生出一个结果表。当ALL随INTERSECT一起使用时(INTERSECTALL)，不消除重复行。注：使用运算词的几个查询结果行必须是一致的。12、说明：12、说明：使用外连接A、left（outer）joinouter）join：left左外连接（左连接）结果集几包括连接表的匹配行，：也包括左连接表的所有行。SQL:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.couter）B：right（outer）join:右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。full/crossouter）join：C：full/cross（outer）join全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。12、分组:Group12、分组:Groupby:一张表，一旦分组完成后，查询后只能得到组相关的信息。组相关的信息：分组的标准)组相关的信息：（统计信息）count,sum,max,min,avg分组的标准)中分组时：text,ntext,在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据统计函数中的字段不能和普通的字段放在一起中的字段，的字段放在一起；在selecte统计函数中的字段，不能和普通的字段放在一起；13、对数据库进行操作：对数据库进行操作：分离数据库：附加数据库：sp_attach_db分离数据库sp_detach_db;附加数据库sp_attach_db后接表明，附加需要完整的路径名14.如何修改数据库的名称:14.如何修改数据库的名称:sp_renamedb'old_name','new_name'
2
二、提升
可用)1、说明：复制表(只复制结构,源表名：a新表名：b)(Access可用)说明：复制表(只复制结构,源表名：新表名：法一：法一：select*intobfromawhere1<>1（仅用于SQlServer）法二：法二：selecttop0*intobfroma说明：拷贝表(拷贝数据,源表名：目标表名：可用)2、说明：拷贝表(拷贝数据,源表名：a目标表名：b)(Access可用)insertintob(a,b,c)selectd,e,ffromb;可用)3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径)(Access可用)说明：跨数据库之间表的拷贝(具体数据使用绝对路径)insertintob(a,b,c)selectd,e,ffrombin‘具体数据库’where条件例子：..frombin'"&;Server.MapPath(".")&;"\data.mdb"&;"'where..4、说明：子查询(表名1：a表名2：b)说明：子查询(selecta,b,cfromawhereaIN(selectdfromb)或者:selecta,b,cfromawhereaIN(1,2,3)5、说明：显示文章、提交人和最后回复时间说明：显示文章、selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b6、说明：外连接查询(表名1：a表名2：b)说明：连接查询(se
lecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c7、说明：在线视图查询(表名1：a)说明：在线视图查询(select*from(SELECTa,b,cFROMa)Twheret.a>1;的用法,between限制查询数据范围时包括了边界值,not8、说明：between的用法,between限制查询数据范围时包括了边界值,notbet说明：ween不包括select*fromtable1wheretimebetweentime1andtime2selecta,b,c,fromtable1whereanotbetween数值1and数值29、说明：in的使用方法说明：select*fromtable1wherea[not]in(‘值1’值2’值4’值6’,’,’,’)10、说明：两张关联表，10、说明：两张关联表，删除主表中已经在副表中没有的信息deletefromtable1wherenotexists(select*fromtable2wheretable1.field1=table2.field1)11、说明：四表联查问题：11、说明：四表联查问题：select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....
3
12、说明：12、说明：日程安排提前五分钟提醒SQL:select*from日程安排wheredatediff('minute',f开始时间,getdate())>513、说明：一条sql语句搞定数据库分页、说明：selecttop10b.*from(selecttop20主键字段,排序字段from表名orderby排序字段desc)a,表名bwhereb.主键字段=a.主键字段orderbya.排序字段具体实现：具体实现：关于数据库分页：declare@startint,@endint@sqlnvarchar(600)set@sql=’selecttop’+str(@end-@start+1)+’+fromTwhereridnotin(selecttop’+str(@str-1)+’RidfromTwhereRid>-1)’execsp_executesql@sql注意：后不能直接跟一个变量，注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊为一个标识列，后还有具体的字段，的处理。的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的字段如果是逻辑索引的，的。因为这样可以避免top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）索引则首先查询索引）14、说明：14、说明：前10条记录selecttop10*formtable1where范围15、说明：最大的记录的所有信息(15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)等等.)selecta,b,cfromtablenametawherea=(selectmax(a)fromtablenametbwheretb.b=ta.b)16、说明：16、说明：包括所有在TableA中但不在
TableB和TableC中的行并消除所有重复行而派生出一个结果表(selectafromtableA)except(selectafromtableB)except(selectafromtableC)17、说明：17、说明：随机取出10条数据selecttop10*fromtablenameorderbynewid()18、说明：18、说明：随机选择记录selectnewid()19、说明：19、说明：删除重复记录1),deletefromtablenamewhereidnotin(selectmax(id)fromtablena1),megroupbycol1,col2,...)2),selectdistinct*intotempfromtablename2)
4
deletefromtablenameinsertintotablenameselect*fromtemp评价：这种操作牵连大量的数据的移动，评价：这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作3),例如在一个外部表中导入数据，由于某些原因第一次只导入了一部分，例如：3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段altertabletablename--添加一个自增列addcolumn_bintidentity(1,1)deletefromtablenamewherecolumn_bnotin(selectmax(column_b)fromtablenamegroupbycolumn1,column2,...)altertabletablenamedropcolumncolumn_b20、说明：20、说明：列出数据库里所有的表名selectnamefromsysobjectswheretype='U'//U代表用户21、说明：列出表里的所有的列名21、说明：列出表里的所有的列名selectnamefromsyscolumnswhereid=object_id('TableName')22、说明：type、vender、字段，字段排列，22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，case。实现多重选择，类似select中的case。selecttype,sum(casevenderwhen'A'thenpcselse0end),sum(casevenderwhen'C'thenpcselse0end),sum(casevenderwhen'B'thenpcselse0end)FROMtablenamegroupbytype显示结果：显示结果：typevenderpcs电脑A1电脑A1光盘B2光盘A2手机B3手机C323、说明：23、说明：初始化表table1TRUNCATETABLEtable124、说明：24、说明：选择从10到15的记录selecttop5*from(selecttop15*fromtableorderbyidasc)table_别名orderbyiddesc
三、技巧
5
的使用，1、1=1，1=2的使用，在SQL语句组合时用的较多1=1，1=2”全部不选全部不选，“where1=1”是表示选择全部“where1=2”全部不选，如：if@strWhere!=''beginset@strSQL='selectcount(*)asTotalfrom['+@tblName+']where'+@strWhereendelsebeginset@strSQL='selectcount(*)asTotalfrom['+@tblName+']'end我们可以直接写成错误！未找到目录项。set@strSQL='selectcount(*)asTotalfrom['+@tblName+']where1=1安定'+@strWhere2、收缩数据库--
重建索引DBCCREINDEXDBCCINDEXDEFRAG--收缩数据和日志DBCCSHRINKDBDBCCSHRINKFILE3、压缩数据库dbccshrinkdatabase(dbname)4、转移数据库给新用户以已存在用户权限execsp_change_users_login'update_one','newname','oldname'go5、检查备份集RESTOREVERIFYONLYfromdisk='E:\dvbbs.bak'6、修复数据库ALTERDATABASE[dvbbs]SETSINGLE_USERGODBCCCHECKDB('dvbbs',repair_allow_data_loss)WITHTABLOCKGOALTERDATABASE[dvbbs]SETMULTI_USERGO
6
7、日志清除SETNOCOUNTONDECLARE@LogicalFileNamesysname,@MaxMinutesINT,@NewSizeINT
USEtablename--要操作的数据库名SELECT@LogicalFileName='tablename_log',--日志文件名@MaxMinutes=10,--Limitontimeallowedtowraplog.@NewSize=1--你想设定的日志文件的大小(M)Setup/initializeDECLARE@OriginalSizeintSELECT@OriginalSize=sizeFROMsysfilesWHEREname=@LogicalFileNameSELECT'OriginalSizeof'+db_name()+'LOGis'+CONVERT(VARCHAR(30),@OriginalSize)+'8Kpagesor'+CONVERT(VARCHAR(30),(@OriginalSize*8/1024))+'MB'FROMsysfilesWHEREname=@LogicalFileNameCREATETABLEDummyTrans(DummyColumnchar(8000)notnull)
DECLARE@CounterINT,@StartTimeDATETIME,@TruncLogVARCHAR(255)SELECT@StartTime=GETDATE(),@TruncLog='BACKUPLOG'+db_name()+'WITHTRUNCATE_ONLY'DBCCSHRINKFILE(@LogicalFileName,@NewSize)EXEC(@TruncLog)--Wrapthelogifnecessary.WHILE@MaxMinutes>DATEDIFF(mi,@StartTime,GETDATE())--timehasnotexpiredAND@OriginalSize=(SELECTsizeFROMsysfilesWHEREname=@LogicalFileName)AND(@OriginalSize*8/1024)>@NewSizeBEGIN--Outerloop.SELECT@Counter=0WHILE((@Counter<@OriginalSize/16)AND(@Counter<50000))BEGIN--updateINSERTDummyTransVALUES('FillLog')DELETEDummyTrans
7
SELECT@Counter=@Counter+1ENDEXEC(@TruncLog)ENDSELECT'FinalSizeof'+db_name()+'LOGis'+CONVERT(VARCHAR(30),size)+'8Kpagesor'+CONVERT(VARCHAR(30),(size*8/1024))+'MB'FROMsysfilesWHEREname=@LogicalFileNameDROPTABLEDummyTransSETNOCOUNTOFF8、说明：更改某个表说明：execsp_changeobjectowner'tablename','dbo'9、存储更改全部表CREATEPROCEDUREdbo.User_ChangeObjectOwnerBatch@OldOwnerasNVARCHAR(128),@NewOwnerasNVARCHAR(128)ASDECLARE@NameasNVARCHAR(128)DECLARE@OwnerasNVARCHAR(128)DECLARE@OwnerNameasNVARCHAR(128)DECLAREcurObjectCURSORFORselect'Name'=name,'Owner'=user_name(uid)fromsysobjectswhereuser_name(uid)=@OldOwnerorderbynameOPENcurObjectFETCHNEXTFROMcurObjectINTO@Name,@OwnerWHILE(@@FETCH_STATUS=0)BEGINif@Owner=@OldOwnerbeginset@OwnerName=@OldOwner+'.'+rtrim(@Name)execsp_changeobjectowner@OwnerName,@NewOwnerend--select@name,@NewOwner,@OldOwner
8
FETCHNEXTFROMcurObjectINTO@Name,@OwnerENDclosecurObjectdeallocatecurObject
GO
10、10、SQLSERVER中直接循环写入数据
declare@iintset@i=1while@i<30begininsertin
totest(userid)values(@i)set@i=@i+1end
案例：
有如下表，要求就裱中所有沒有及格的成績，的基礎上，使他們剛好及格:有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:NamescoreZhangshan80Lishi59Wangwu50Songquan69min(score)tb_table)<6060)while((selectmin(score)fromtb_table)<60)beginscore*1.01updatetb_tablesetscore=score*1.01score<60wherescore<60min(score)tb_table)>60if(selectmin(score)fromtb_table)>60breakelsecontinueend
数据开发数据开发-经典
9
1.按姓氏笔画排序:1.按姓氏笔画排序:按姓氏笔画排序Select*FromTableNameOrderByCustomerNameCollateChinese_PRC_Stroke_ci_as//从少到多2.数据库加密:2.数据库加密:数据库加密selectencrypt('原始密码')selectpwdencrypt('原始密码')selectpwdcompare('原始密码','加密后密码')=1--相同；否则不相同encrypt('原始密码')selectpwdencrypt('原始密码')selectpwdcompare('原始密码','加密后密码')=1--相同；否则不相同3.取回表中字段: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.查看硬盘分区:4.查看硬盘分区:查看硬盘分区EXECmaster..xp_fixeddrives5.比较表是否相等:5.比较A,B表是否相等:if(selectchecksum_agg(binary_checksum(*))fromA)=(selectchecksum_agg(binary_checksum(*))fromB)print'相等'elseprint'不相等'6.杀掉所有的事件探察器进程: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
10
---------------------------------N到结尾记录SelectTopN*From表OrderbyIDDesc案例
一张表有一万多条记录，是自增长字段，语句，例如1：一张表有一万多条记录，表的第一个字段RecID是自增长字段，写一个SQL语句，个记录。找出表的第31到第40个记录。selecttop10recidfromAwhererecidnotin(selecttop30recidfromA)分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。selecttop10recidfromAwhere……是从索引中查找，而后面的selecttop30recidfromA则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的
欲得到的数据。解决方案order如果该字段不是自1，用orderbyselecttop30recidfromAorderbyricid如果该字段不是自增长，增长，就会出现问题2，在那个子查询中也加条件：selecttop30recidfromAwhererecid>-1，在那个子查询中也加条件：以及表结构。例2：查询表中的最后以条记录，并不知道这个表共有多少数据以及表结构。：查询表中的最后以条记录，并不知道这个表共有多少数据,以及表结构set@s='selecttop1*fromTwherepidnotin(selecttop'+str(@count-1)+'pidfromT)'print@sexecsp_executesql@s
9：获取当前数据库中的所有用户表selectNamefromsysobjectswherextype='u'andstatus>=010：10：获取某一个表的所有字段selectnamefromsyscolumnswhereid=object_id('表名')selectnamefromsyscolumnswhereidin(selectidfromsysobjectswheretype='u'andname='表名')两种方式的效果相同11：查看与某一个表相关的视图、存储过程、11：查看与某一个表相关的视图、存储过程、函数selecta.*fromsysobjectsa,syscommentsbwherea.id=b.idandb.textlike'%表名%'12：12：查看当前数据库中所有存储过程selectnameas存储过程名称fromsysobjectswherextype='P'13：13：查询用户创建的所有数据库select*frommaster..sysdatabasesDwheresidnotin(selectsidfrommaster..sysloginswherename='sa')或者selectdbid,nameASDB_NAMEfrommaster..sysdatabaseswheresid<>0x01
11
14：14：查询某一个表的字段和数据类型
selectcolumn_name,data_typefrominformation_schema.columnswheretable_name='表名'
15：15：不同服务器数据库之间的数据操作
--创建链接服务器--创建链接服务器
execsp_addlinkedserver
'ITSV','','SQLOLEDB','远程服务器名或ip地址'
execsp_addlinkedsrvlogin
'ITSV','false',null,'用户名','密码'
--查询示例
select*fromITSV.数据库名.dbo.表名
--导入示例
select*into表fromITSV.数据库名.dbo.表名
--以后不再使用时删除链接服务器--以后不再使用时删除链接服务器
execsp_dropserver'ITSV','droplogins'
--连接远程/局域网数据--连接远程/局域网数据(openrowset/openquery/opendatasource)连接远程
--1、openrowset
--查询示例
select*fromopenrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)
--生成本地表
select*into表fromopenrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)
--把本地表导入远程表
insertopenrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)
12
select*from本地表
--更新本地表
updateb
setb.列A=a.列A
fromopenrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)asainnerjoin本地表b
ona.colu
mn1=b.column1
--openquery用法需要创建一个连接
--首先创建一个连接创建链接服务器
execsp_addlinkedserver
'ITSV','','SQLOLEDB','远程服务器名或ip地址'
--查询
select*
FROMopenquery(ITSV,'SELECT*FROM数据库.dbo.表名')
--把本地表导入远程表
insertopenquery(ITSV,'SELECT*FROM数据库.dbo.表名')
select*from本地表
--更新本地表
updateb
setb.列B=a.列B
FROMopenquery(ITSV,'SELECT*FROM数据库.dbo.表名')asa
innerjoin本地表bona.列A=b.列A
--3、opendatasource/openrowset
SELECT
*
13
FROMopendatasource('SQLOLEDB','DataSource=ip/ServerName;UserID=登陆名;Password=密码').test.dbo.roy_ta
--把本地表导入远程表
insertopendatasource('SQLOLEDB','DataSource=ip/ServerName;UserID=登陆名;Password=密码').数据库.dbo.表名
select*from本地表
SQLServer基本函数
SQLServer基本函数
1.字符串函数1.字符串函数
长度与分析用
1,datalength1,datalength(Char_expr)返回字符串包含字符数,但不包含后面的空格2,substring2,substring(expression,start,length)取子串，字符串的下标是从“1”，
start为起始位置，length为字符串长度，实际应用中以len(expression)取得其长度
3,right3,right(char_expr,int_expr)返回字符串右边第int_expr个字符，还用le
ft于之相反
4,isnull4,isnull(check_expression,replacement_value)如果check_expressio
n為空，則返回replacement_value的值，不為空，就返回check_expression字符操作类5,S5,Sp_addtype自定義數據類型例如：EXECsp_addtypebirthday,datetime,'NULL'6,set{on|offon|off}6,setnocount{on|off}使返回的结果中不包含有关受Transact-SQL语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。SETNOCOUNT设置是在执行或运行时设置，而不是在分析时设置。SETNOCOUNT为ON时，不返回计数（表示受Transact-SQL语句影响的行数）。SETNOCOUNT为OFF时，返回计数
常识
14
查询中：在SQL查询中：from后最多可以跟多少张表或视图：256256by,查询时，先排序，在SQL语句中出现Orderby,查询时，先排序，后取8000，nvarchar(4000),在SQL中，一个字段的最大容量是8000，而对于nvarchar(4000),由于nvarchar是Unicode码。
SQLServer2000SQLServer2000同步复制技术实现步骤
一、预备工作1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户--管理工具--计算机管理--用户和组--右键用户--新建用户--建立一个隶属于administrator组的登陆windows的用户（SynUser）2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放
目录,操作:我的电脑--D:\新建一个目录,名为:PUB--右键这个新建的目录--属性--共享--选择"共享该文件夹"--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser)具有对该文件夹的所有权限
--确定3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)开始--程序--管理工具--服务--右键SQLSERVERAGENT--属性--登陆--选择"此账户"--输入或者选择第一步中创建的windows登录用户名（SynUser）--"密码"中输入该用户的密码4.设置SQLServer身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)企业管理器--右键SQL实例--属性--安全性--身份验证--选择"SQLServer和Windows"--确定5.在发布服务器和订阅服务器上互相注册企业管理器--右键SQLServer组--新建SQLServer注册...--下一步--可用的服务器中,输入你要注册的远程服务器名--添加--下一步--连接使用,选择第二个"SQLServer身份验证"
15
--下一步--输入用户名和密码（SynUser）--下一步--选择SQLServer组,也可以创建一个新组--下一步--完成6.对于只能用IP,不能用计算机名的,为其注册服务器别名（此步在实施中没用到）(在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)开始--程序--MicrosoftSQLServer--客户端网络实用工具--别名--添加--网络库选择"tcp/ip"--服务器别名输入SQL服务器名--连接参数--服务器名称中输入SQL服务器ip地址--如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号二、正式配置1、配置发布服务器打开企业管理器，在发布服务器（B、C、D）上执行以下步骤:(1)从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导(2)[下一步]选择分发服务器可以选择把发布服务器自己作为分发服务器或者其他sql的服务器（选择自己）(3)[下一步]设置快照文件夹采用默认\\servername\Pub(4)[下一步]自定义配置可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置否,使用下列默认设置（推荐）(5)[下一步]设置分发数据库名称和位置采用默认值(6)[下一步]启用发布服务器选择作为发布的服务器
(7)[下一步]选择需要发布的数据库和发布类型
(8)[下一步]选择注册订阅服务器(9)[下一步]完成配置2、创建出版物发布服务器B、C、D上(1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令(2)选择要创建出版物的数据库，然后单击[创建发布](3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制
的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)(4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,SQLSERVER允许在不同的数据库如orACLE或ACCESS之间进行数据复制。但是在这里我们选择运行"SQLSERVER2000"的数据库服务器(5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表注意:如果前面选择了事务发布则再这一步中只能选择带有主键的表(6)选择发布名称和描述(7)自定义发布属性向导提供的选择:是我将自定义数据筛选,启用匿名订阅和或其他自定义属性否根据指定方式创建发布（建议采用自定义的方式）(8)[下一步]选择筛选发布的方式
16
(9)[下一步]可以选择是否允许匿名订阅1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器方法:[工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器]中添加否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅如果仍然需要匿名订阅则用以下解决办法[企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项]选择允许匿名请求订阅2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示(10)[下一步]设置快照代理程序调度(11)[下一步]完成配置当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库有数据srv1.库名..author有字段:id,name,phone,srv2.库名..author有字段:id,name,telphone,adress
要求：srv1.库名..author增加记录则srv1.库名..author记录增加srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新--*/
--大致的处理步骤--1.在srv1上创建连接服务器,以便在srv1中操作srv2,实现同步execsp_addlinkedserver'srv2','','SQLOLEDB','srv2的sql实例名或ip'execsp_addlinkedsrvlogin'srv2','false',null,'用户名','密码'go--2.在srv1和srv2这两台电脑中,启动msdtc(分布式事务处理服务),并且设置为自动启动。我的电脑--控制面板--管理工具--服务--右键DistributedTransactionCoordinator-属性--启动--并将启动类型设置为自动启动go
--然后创建一个作业定时调用上面的同步处理存储过程就行了
企业管理器--管理--SQLServer代理--右键作业--新建作业--"常规"项中输入作业名称--"步骤"项--新建--"步骤名"中输入步骤名--"类型"中选择"Transact-SQL脚本(TSQL)"--"数据库"选择执行命令的数据库
17
--"命令"中输入要执行的语句:execp_process--确定--"调度"项--新建调度--"名称"中输入调度名称--"调度类型"中选择你的作业执行安排--如果选择"反复出现"--点"更改"来设置你的时间安排
然后将SQLAgent服务启动,并
设置为自动启动,否则你的作业不会被执行
设置方法:我的电脑--控制面板--管理工具--服务--右键SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
--3.实现同步处理的方法2,定时同步
--在srv1中创建如下的同步处理存储过程createprocp_processas--更新修改过的数据updatebsetname=i.name,telphone=i.telphonefromsrv2.库名.dbo.authorb,authoriwhereb.id=i.idand(b.name<>i.nameorb.telphone<>i.telphone)
--插入新增的数据insertsrv2.库名.dbo.author(id,name,telphone)selectid,name,telphonefromauthoriwherenotexists(select*fromsrv2.库名.dbo.authorwhereid=i.id)
--删除已经删除的数据(如果需要的话)deletebfromsrv2.库名.dbo.authorbwherenotexists(select*fromauthorwhereid=b.id)
go
SQL函数说明大全
Postedon2010-08-2323:37moss_tan_jun阅读(6250)评论(0)编辑收藏
一旦成功地从表中检索出数据，就需要进一步操纵这些数据，以获得有用或有意义的结果。这些要求包括：执行计算与数学运算、转换数据、解析数值、组合值和聚合一个范围内的值等。
18
下表给出了T-SQL函数的类别和描述。函数类别作用执行的操作是将多个值合并为一个值。例如COUNT、SUM、MIN和MAX。是一种标量函数，可返回有关配置设置的信息。将值从一种数据类型转换为另一种。支持加密、解密、数字签名和数字签名验证。返回有关游标状态的信息。可以更改日期和时间的值。执行三角、几何和其他数字运算。返回数据库和数据库对象的属性信息。是一种非确定性函数，可以返回分区中每一行的排名值。返回可在Transact-SQL语句中表引用所在位置使用的行集。返回有关用户和角色的信息。可更改char、varchar、nchar、nvarchar、binary和varbinary的值。对系统级的各种选项和对象进行操作或报告。返回有关SQLServer性能的信息。可更改text和image的值。
聚合函数配置函数转换函数加密函数游标函数日期和时间函数数学函数元数据函数排名函数行集函数安全函数字符串函数系统函数系统统计函数文本和图像函数
函数的组成
函数的目标是返回一个值。大多数函数都返回一个标量值(scalarvalue)，标量值代表一个数据单元或一个简单值。实际上，函数可以返回任何数据类型，包括表、游标等可返回完整的多行结果集的类型。本章不准备讨论到这个深度，第12章将讲解如何创建和使用用户自定义函数，以返回更复杂的数据。函数己经存在很长时间了，它的历史比SQL还要长。在几乎所有的编程语言中，函数调用的方式都是相同的：Result=Function()在T-SQL中，一般用SELECT语句来返回值。如果需要从查询中返回一个值，
就可以把SELECT当成输出运算符，而不用使用等号：SELECTFunction()一个论点对于SQL函数而言，参数表示输入变量或者值的占位符。函数可以有任意个参数，有些参数是必须的，而有些参数是可选的。可选参数通常被置于以逗号隔开的参数表的末尾，以便于在函数调用中去除不需要的参数。在SQLServer在线图书或者在线帮助系统中，函数的可选参数用方括号表示。在下列的CONVERT()函数例子中，数据类型的length和style参数是可选的:19
CONVERT(data-type[(length)],expression[,style])可将它简化为如下形式，因为现在不讨论如何使用数据类型：CONVERT(date_type,expression[,style])根据上面的定义，CONVERT()函数可接受2个或3个参数。因此，下列两个例子都是正确的：
SELECTCONVERT(Varchar(20),GETDATE())SELECTCONVERT(Varchar(20),GETDATE(),101)
这个函数的第一个参数是数据类型Varchar(20)，第2个参数是另一个函数GETDATE()。GETDATE()函数用datetime数据类型将返回当前的系统日期和时间。第2条语句中的第3个参数决定了日期的样式。这个例子中的101指以mm/dd/yyyy格式返回日期。本章后面将详细介绍GETDATE()函数。即使函数不带参数或者不需要参数，调用这个函数时也需要写上一对括号，例如GETDATE()函数。注意在书中使用函数名引用函数时，一定要包含括号，因为这是一种标准形式。确定性函数由于数据库引擎的内部工作机制，SQLServer必须根据所谓的确定性，将函数分成两个不同的组。这不是一种新时代的信仰，只和能否根据其输入参数或执行对函数输出结果进行预测有关。如果函数的输出只与输入参数的值相关，而与其他外部因素无关，这个函数就是确定性函数。如果函数的输出基于环境条件，或者产生随机或者依赖结果的算法，这个函数就是非确定性的。例如，GETDATE()函数是非确定性函数，因为它不会两次返回相同的值。为什么要把看起来简单的事弄得如此复杂呢？主要原因是非确定性函数与全局变量不能在一些数据库编程对象中使用(如用户自定义函数)。部分原因是SQLServer缓存与预编译可执行对象的方式。例如，即席查询可以使用任何函数，不过如果打算构建先进的、可重用的编程对象，理解这种区别很重要。以下这些函数是确定性的：AVG()(所有的聚合函数都是确定性的)CAST()CONVERT()DATEADD()DATEDIFF()ASCII()CHAR()SUBSTRING()以下这些函数与变量是非确定性的：GETDATE()@@ERROR@@SERVICENAMECURSORSTATUS()RAND()在函数中使用用户变量20
变量既可用于输入，也可用于输出。在T-SQL中，用户变量以@符号开头，用于声明为特定的数据类型。可以使用SET
或者SELECT语句给变量赋值。以下的例子用于将一个int类型的变量@MyNumber传递给SQRT()函数：
DECLARE@MyNumberintSET@MyNumber=144SELECTSQRT(@MyNumber)
结果是12，即144的平方根。
用SET给变量赋值
以下例子使用另一个int型的变量@MyResult，来捕获该函数的返回值。这个技术类似于过程式编程语言中的函数调用样式，即把SET语句和一个表达式结合起来，给参数赋值：
DECLARE@MyNumberint,@MyResultintSET@MyNumber=144--Assignthefunctionresulttothevariable:SET@MyResult=SQRT(@MyNumber)--ReturnthevariablevalueSELECT@MyResult
用SELECT给变量赋值使用SELECT的另一种形式也可以获得同样的结果。对变量要在赋值前要先声明。使用SELECT语句来替代SET命令的主要优点是，可以在一个操作内同时给多个变量赋值。执行下面的SELECT语句，通过SELECT语句赋值的变量就可以用于任何操作了。
DECLARE@MyNumber1int,@MyNumber2int,@MyResult1int,@MyResult2intSELECT@MyNumber1=144,@MyNumber2=121--Assignthefunctionresulttothevariable:SELECT@MyResult1=SQRT(@MyNumber1),@MyResult2=SQRT(@MyNumber2)--ReturnthevariablevalueSELECT@MyResult1,@MyResult2
上面的例子首先声明了4个变量，然后用两个SELECT语句给这些变量赋值，而不是用4个SELECT语句给变量赋值。虽然这些技术在功能上是相同的，但是在服务器的资源耗费上，用一个SELECT语句给多个变量赋值一般比用多个SET命令的效率要高。将一个甚至多个值选进参数的限制是，对变量的赋值不能和数据检索操作同时进行。这就是上面的例子使用SELECT语句来填充变量，而用另外一个SELECT语句来检索变量中数据的原因。例如，下面的脚本就不能工作:
DECLARE@RestockNamevarchar(50)SELECTProductId
21
,@RestockName=Name+':'+ProductNumberFROMProduction.Product
这个脚本会产生如下错误:消息141，级别15，状态1，第2行向变量赋值的SELECT语句不能与数据检索操作结合使用。
在查询中使用函数
函数经常和查询表达式结合使用来修改列值。这只需将列名作为参数传递给函数即可，随后函数将引用插入到SELECT查询的列的列表中，如下所示：
SELECTTitle,NationalIDNumber,YEAR(BirthDate)ASBirthYearFROMHumanResources.Employee
在这个例子中，BirthDate列的值被作为参数传递给YEAR()函数。函数的结果是别名为BirthYear的列。嵌套函数
我们需要的功能常常不能仅由一个函数来实现。根据设计，函数应尽量简单，用于提供特定的功能。如果一个函数要执行许多不同的操作，就变得复杂和难以使用。因此，每个函数通常仅执行一个操作，要实现所有的功能，可以将一个函数的返回值传递给另一个函数，
这称为嵌套函数调用。以下是一个简单的例子：GETDATE()函数的作用是返回当前的日期与时间，但不能返回经过格式化的数据，因为这是CONVERT()函数的功能。要想同时使用这两个函数，可以把GETDATE()函数的输出作为CONVERT()函数的输入参数。
SELECTCONVERT(Varchar(20),GETDATE(),101)
聚合函数
报表的典型用途是从全部数据中提取出代表一种趋势的值或者汇总值，这就是聚合的意义。聚合函数回答数据使用者的如下问题:上个月鸡雏的总销售量是多少?19～24岁之间的巴西男性在食品调味品上的平均支出是多少?上季度所有订单中从订购到运输的最长时间是多少?收发室里仍在工作的最老的员工是谁?聚合函数应用特定的聚合操作并返回一个标量值(单一值)。返回的数据类型对应于该列或者传递到函数中的值。聚合经常和分组、累积以及透视等表运算一起使用，生成数据分析结果。第7章将详细介绍这个主题，这里仅讨论简单SELECT查询中的一些常用函数。聚合函数不仅可用在SELECT查询中，还可以和标量输入值一起使用。那么，这样做的意义是什么呢？在下列代码中，将值15传递给下列聚合函数，每个函数的返回值都相同：
SELECTAVG(15)SELECTSUM(15)SELECTMIN(15)
22
SELECTMAX(15)
它们都返回15。虽然，对同一个值求平均、求和、求最小值、求最大值，所得的结果还是那个值。如果对一个值计数，又会产生什么结果呢？
SELECTCOUNT(15)
得到的值是1，因为函数只计数了一个值。现在做一些有意义的事。聚合函数只有在处理结果集合中的一组数据时才有意义。每个函数都处理某列的非空值。除非使用分组操作(详见第7章)，否则不能在同一个SELECT语句中既返回聚合的值，又返回常规的列值。AVG()函数函数AVG()函数用于返回一组数值中所有非空数值的平均值。例如，表6-2包含了体操成绩。表6-2体操运动员SaraCassieDelaneySammiErikaSaraCassieDelaneySammiErika对这些数据执行以下查询:SELECTAVG(Score)结果是8.965。如果有三个女孩没有完成一些项目，在表中没有记录成绩，则可用NULL来表示(见表6-3)。表6-3体操运动员SaraCassieDelaney项目成绩项目成9.258.759.258.058.609.709.009.258.958.85绩
跳马跳马跳马跳马跳马平衡木平衡木平衡木平衡木平衡木
跳马跳马跳马
9.258.75NULL
23
SammiErikaSaraCassieDelaneySammiErika脚本：
跳马跳马平衡木平衡木平衡木平衡木平衡木
8.058.609.70NULL9.25NULL8.85
createtable#GymEvent(Playervarchar(10),[Subject]nvarchar(5),Scoredecimal(4,2))goinsertinto#GymEventvalues('Sara','跳马',9.25)insertinto#GymEventvalues('Cassie','跳马',8.75)inser
tinto#GymEventvalues('Delaney','跳马',NULL)insertinto#GymEventvalues('Sammi','跳马',8.05)insertinto#GymEventvalues('Erika','跳马',8.60)insertinto#GymEventvalues('Sara','平衡木',9.70)insertinto#GymEventvalues('Cassie','平衡木',NULL)insertinto#GymEventvalues('Delaney','平衡木',9.25)insertinto#GymEventvalues('Sammi','平衡木',NULL)insertinto#GymEventvalues('Erika','平衡木',8.85)godroptable#GymEvent
在这种情况下，计算平均值时只考虑实际的数值，NULL不参与运算，结果是8.921429。但是，如果把缺少的成绩也算在内，即用数值0代替NULL，则会严重影响最终成绩(6.245)，她们能不能进入国家级的比赛就难说了。COUNT()函数函数COUNT()函数用于返回一个列内所有非空值的个数，这是一个整型值。比如，在上一个例子中，体操数据被保存在#GymEvent表中，要确定Sammi参加的项目数，则可以执行下列查询：
SELECTCOUNT(Score)FROM#GymEventWHEREPlayer='Sammi'
结果是1，因为Sammi只参加了跳马比赛，她的平衡木成绩是NULL。如果需要确定表中的行数，无论这些行是不是NULL值，都可以使用以下语法：
SELECTCOUNT(*)FROM#GymEvent
以Sammi为例，COUNT(*)查询如下所示:24
SELECTCOUNT(*)FROM#GymEventWHEREPlayer='Sammi'
由于COUNT(*)函数会忽略NULL值，所以这个查询的结果是2。MIN()与MAX()函数与函数MIN()函数用于返回一个列范围内的最小非空值；MAX()函数用于返回最大值。这两个函数可以用于大多数的数据类型，返回的值根据对不同数据类型的排序规则而定。为了说明这两个函数，假设有一个表包含了两列值，一列是整型值，另一列是字符型值，如表6-4所示。表6-4IntegerColumn(int类型类型)241219脚本：241219VarCharColumn(varChar类型类型)
createtable#Temp(IntegerColumnint,VarCharColumnvarchar(10))goinsertinto#Tempvalues(2,'2')insertinto#Tempvalues(4,'4')insertinto#Tempvalues(12,'12')insertinto#Tempvalues(19,'19')godroptable#Temp
如果分别调用MIN()与MAX()函数将会返回什么值呢？
selectMIN(IntegerColumn),MAX(IntegerColumn)from#TempselectMIN(VarCharColumn),MAX(VarCharColumn)from#Temp
因为VarCharColumn中值的存储类型为字符类型，而不是数字，所以结果以每个字符的ASCII值为顺序从左到右排序。这就是12比其他值小、而4比其他值大的原因。SUM()函数函数
25
SUM()函数是最常用的聚合函数之一，它的功能很容易理解：和AVG()函数一样，它用于数值数据类型，返回一个列范围内所有非空值的总和。
配置变量
配置变量不是函数，不过它们的用法和系统函数相同。每个全局变量都能够返回SQLServer执行环境的标量信息。以下是一些常见的例子。@@ERROR变量
这个变量包
含当前连接发生的最后一次错误的代码。在执行的语句没有错误时，@@ERROR变量的值是0。出现标准错误时，错误是由数据库引擎引发的。所有的标准错误代码与消息都保存在sys.messages系统视图中，可以使用如下脚本查询：
SELECT*FROMsys.messages
定制错误可以通过调用RAISERROR语句来手动引发，并调用sp_addmessage系统存储过程将其添加到sysmessages表中。以下是一个@@ERROR变量的简单例子。先试着将一个数除以0，数据库引擎会引发标准错误号为8134的错误。注意查看Results选项卡中的查询结果。在发生错误时，ManagementStudio的Messages选项卡将默认显示在Results选项卡的上面：
SELECT5/0SELECT@@ERROR
在成功检索@@ERROR的值后，@@ERROR的值将返回0，因为@@ERROR只保存了上次执行的语句的错误代码。如果希望检索更多的错误信息，可以使用如下脚本从sysmessages视图中得到：
SELECT5/0SELECT*FROMmaster.dbo.sysmessagesWHEREerror=@@ERROR
本节的后面部分内容将说明如何通过使用错误函数来更高效地返回错误数据。除了美国英语之外，SQLServer还默认安装了其他语言。每种语言专用的错误消息都有一个语言标识符(mslangid)，对应于syslanguages表中的一种语言，如下图所示。error8134813481348134813481348134severity16161616161616dlevel0000000descriptionDividebyzeroerrorencountered.FehleraufgrundeinerDivisiondurchNull.Divisionparzéro.0除算エラーが発生しました。Errordedivisiónentrecero.Erroredidivisioneperzero.Обнаруженаошибка:делениенаноль.8134160Errodedivis?oporzero.1046msglangid1033103110361041308210401049
26
813481348134
161616
000
發現除以零的錯誤。0??????????????.遇到以零作除数错误。
102810422052
属性名mslangid被非正式地定义为MicrosoftGlobalLanguageIdentifier。微软公司用这个标识符来标识一种语言或语言和国家的组合，微软公司把语言和国家的组合定义为地区。例如，在随SQLServer安装的英语中，美国英语的mslangid是1033，英国英语的mslangid是2057。要检索出所有已安装的、支持的语言，可以执行下面的查询：
SELECTalias,name,msglangidFROMsys.syslanguages
@@SERVICENAME变量这个变量是用于执行和维护当前SQLServer实例的Windows服务名。它通常返回SQLServer默认实例MSSQLSERVER，但SQLServer的指定实例有唯一的服务名。例如在名为WoodVista的计算机上有两个SQLServer实例：默认实例和指定实例AughtEight。如在默认实例上检索@@SERVICENAME全局变量的内容，将返回MSSQLSERVER，但在指定实例上检索，会返回AUGHTEIGHT。@@TOTAL_ERR
ORS变量
这个变量用于记录从打开当前连接开始发生的总错误次数。和@@ERROR变量一样，它对每个用户会话是唯一的，并将在连接关闭时被重置。@@TOTAL_READ变量
这个变量记录从打开当前连接时开始计算的磁盘读取总数。DBA使用这个变量查看磁盘读取活动的情况。@@VERSION变量这个变量包含当前SQLServer实例的完整版本信息。
SELECT@@VERSION
比如，对于运行在Windows7上的SQLServer2008开发版实例，以上脚本能够返回如下信息:MicrosoftSQLServer2008(RTM)-10.0.1600.22(IntelX86)14:43:34Jul92008
Copyright(c)1988-2008MicrosoftCorporationEnterpriseEditiononWindows
NT6.1
(Build7600:)实际的版本号是一个简单的整型值，它在微软公司内部使用。而发行的产品可能有其他的商标名。在本例中，SQLServer2005的版本是9，SQLServer2008的版本是10。WindowsXPProfessional显示为WindowsNT5.l版，而Vista显示为6.0版。构建号用于内部控制，反映beta版和预览版以及正式发行后的补丁包的变化。错误函数
27
前面学习了如何使用@@ERROR全局变量来检索错误信息。而返回所有错误数据的更好方法是使用错误函数。这些函数返回的信息可以存储在错误跟踪表中，以供错误审核。错误函数嵌套在错误处理例程中。第11章将详细讨论错误处理，其实通过使用嵌套在TRY和ENDTRY语句中的代码块，后跟一个放在CATCH和ENDCATCH语句中的代码块就可以实现错误处理。
--TrytodosomethingBEGINTRYSELECT5/0ENDTRY--Ifitcausesanerror,dothisBEGINCATCHPRINTERROR_MESSAGE()ENDCATCH
所谓的错误捕获，其实就是这个意思。如果运行上面的示例，将不会出现可识别的错误，因为错误将被捕获并在CATCH语句块中进行处理。在编写错误处理代码时，SQL程序员必须把这些代码放在会引发系统错误的catch代码块中。下列几个错误函数用于返回错误的特定信息：函数说明
ERROR_MESSAGE()ERROR_NUMBER()ERROR_SEVERITY()ERROR_STATE()ERROR_LINE()ERROR_PROCEDURE()
返回错误的描述。返回错误号。返回错误的严重级别。错误的严重级别是一个从0到25的整数。返回错误的状态号。错误状态是一个整数，可以唯一地表示系统错误的原因。返回例程中导致出错的行号。返回发生错误的存储过程名或触发器名。
下表简要描述了严重级别。严重级别0～1011～161718192021说明
信息性消息。不会引发系统错误用户可以更正的错误，例如违反了外键或主键规则非致命的、不重要的资源错误非致命的内部错误致命的、不重要的资源错误当前进程中的致命错误所有进程中的致命数据库错误
28
22232425
致命的表完整性错误致命
的数据库完整性错误致命的硬件错误致命的系统错误下面脚本使用T-SQL的内置错误处理功能，来捕获和输出遇到除0错误时返回的错误数据。SELECT
命令的结果将显示在ManagementStudio的消息选项卡中。
--TrytodosomethingBEGINTRYSELECT5/0ENDTRY--Ifitcausesanerror,dothisBEGINCATCHSELECTERROR_MESSAGE(),ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE(),ERROR_PROCEDURE()ENDCATCH
可以看出，执行这个脚本会在消息选项卡中返回有关错误的更多详细信息，而不仅仅是错误号本身。
ERROR_PROCEDURE()函数不能返回过程名，因为错误是在ad-hoc查询中生成的。
转换函数
数据类型转换可以通过CAST()和CONVERT()函数来实现。大多数情况下，这两个函数是重叠的，它们反映了SQL语言的演化历史。这两个函数的功能相似，不过它们的语法不同。虽然并非所有类型的值都能转变为其他数据类型，但总的来说，任何可以转换的值都可以用简单的函数实现转换。CAST()函数函数CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。以下例子用于将文本字符串'123'转换为整型:
SELECTCAST('123'ASint)
返回值是整型值123。如果试图将一个代表小数的字符串转换为整型值，又会出现什么情况呢？
SELECTCAST('123.4'ASint)
CAST()函数和CONVERT()函数都不能执行四舍五入或截断操作。由于123.4不能用int数据类型来表示，所以对这个函数调用将产生一个错误:Server:Msg245,Level16,State1,Line1Syntaxerrorconvertingthevarcharvalue'123.4'toacolumnofdatatypeint.在将varchar值'123.4'转换成数据类型int时失败。29
要返回一个合法的数值，就必须使用能处理这个值的数据类型。对于这个例子，存在多个可用的数据类型。如果通过CAST()函数将这个值转换为decimal类型，需要首先定义decimal值的精度与小数位数。在本例中，精度与小数位数分别为9与2。精度是总的数字位数，包括小数点左边和右边位数的总和。而小数位数是小数点右边的位数。这表示本例能够支持的最大的整数值是9999999，而最小的小数是0.01。
SELECTCAST('123.4'ASdecimal(9,2))
decimal数据类型在结果网格中将显示有效小数位:123.40精度和小数位数的默认值分别是18与0。如果在decimal类型中不提供这两个值，SQLServer将截断数字的小数部分，而不会产生错误。
SELECTCAST('123.4'ASdecimal)
结果是一个整数值：123在表的数据中转换数据类型是很简单的。下面的例子使用Product表，首先执行如下查询：
SELECTProductNumber,ProductLine,ProductModelIDFROMProduction.ProductWHEREProductSubcategoryID<4
假定产品经理已经创建了一个系统，
用于唯一地标识生产出来的每辆自行车，以便跟踪其型号、类型和类别。他决定合并产品号、产品生产线标识符、产品型号标识符和一个顺序号，为生产出来的每辆自行车创建一个唯一的序列号。在这个过程的第一步，他要求提供包括除顺序号之外的所有属性的所有可能产品的根标识符。如果使用下面的表达式，就不能得到希望的结果，如图6-2所示。
SELECTProductNumber+'-'+ProductLine+'-'+ProductModelIDASBikeSerialNumFROMProduction.ProductWHEREProductSubcategoryID<4消息245，级别16，状态1，第1行在将nvarchar值'BK-R93R-62-R-'转换成数据类型int时失败。
我们没有得到希望的结果，而得到了有点奇怪的错误消息：请把nvarchar值转换为int。因为之前我们没有要求进行任何转换，所以这个错误很奇怪。这个查询的问题在于我们试图利用第一个连接符来连接字符值ProductNumber，利用第二个连接符连接另一个字符值ProductLine，最后连接的是ProductModelID字符值(它是一个整数)。查询引擎会把连接符当成一个数学运算符，而不是一个字符。不管结果是什么，都需要更正这个表达式，以确保使用正确的数据类型。以下表达式执行了必要的类型转换，返回如图6-3所示的结果：
30
SELECTProductNumber+'-'+ProductLine+'-'+CAST(ProductModelIDASchar(4))ASBikeSerialNumFROMProduction.ProductWHEREProductSubcategoryID<4
如果把整型值转换为字符类型就不会增加多余的空格了。查询引擎将把这些值用加号和连接符组合在一起，进行字符串连接运算，而不是和前面的数值进行加法或者减法运算了。
CONVERT()函数函数对于简单类型转换，CONVERT()函数和CAST()函数的功能相同，只是语法不同。CAST()函数一般更容易使用，其功能也更简单。CONVERT()函数的优点是可以格式化日期和数值，它需要两个参数：第1个是目标数据类型，第2个是源数据。以下的两个例子和上一节的例子类似：
SELECTCONVERT(int,'123')SELECTCONVERT(decimal(9,2),'123.4')
CONVERT()函数还具有一些改进的功能，它可以返回经过格式化的字符串值，且可以把日期值格式化成很多形式。有28种预定义的符合各种国际和特殊要求的日期与时间输出格式。下表列出了这些日期格式。如果expression为date或time数据类型，则style可以为下表中显示的值之一。其他值作为0进行处理。SQLServer使用科威特算法来支持阿拉伯样式的日期格式。yy(1)1yyyy0或100(1,2)101标准输入/输出输入输出(3)monddyyyyhh:miAM（或PM）mm/dd/yyyy
默认美国
31
2345678101112
102103104105106(1)107(1)1089或109(1,2)110111112
ANSI英国/法国德国意大利默
认设置+毫秒美国日本ISO
yy.mm.dddd/mm/yyyydd.mm.yydd-mm-yyddmonyymondd,yyhh:mi:ssmonddyyyyhh:mi:ss:mmmAM（或PM）mm-dd-yyyy/mm/ddyymmddyyyymmdd
14-
13或113(1,2)11420或120(2)21或121(2)126(4)127(6,7)
欧洲默认设置+毫秒ODBC规范ODBC规范（带毫秒）ISO8601带时区Z的ISO8601。
ddmonyyyyhh:mi:ss:mmm(24h)hh:mi:ss:mmm(24h)yyyy-mm-ddhh:mi:ss(24h)yyyy-mm-ddhh:mi:ss.mmm(24h)yyyy-mm-ddThh:mi:ss.mmm（无空格）yyyy-mm-ddThh:mi:ss.mmmZ（无空格）
-
130(1,2)131(2)1.
回历(5)回历(5)
ddmonyyyyhh:mi:ss:mmmAMdd/mm/yyhh:mi:ss:mmmAM
这些样式值将返回不确定的结果。包括所有(yy)（不带世纪数位）样式和一部分(yyyy)（带世纪数位）样式。
2.
默认值（style0或100、9或109、13或113、20或120以及21或121）始终返回世纪数位(yyyy)。
3.4.
转换为datetime时输入；转换为字符数据时输出。为用于XML而设计。对于从datetime或smalldatetime到字符数据的转换，其输出格式如上一个表所述。
5.
回历是有多种变体的日历系统。SQLServer使用科威特算法。a)默认情况下，SQLServer基于截止年份2049年来解释两位数的年份。换言之，就是将两位数的年份49解释为2049，将两位数的年份50解释为1950。许多客户端应用程序（如基于自动化对象的应用程序）都使用截止年份2030年。SQLServer提
32
供了“两位数年份截止”配置选项，可通过此选项更改SQLServer使用的截止年份，从而对日期进行一致处理。建议您指定四位数年份。6.仅支持从字符数据转换为datetime或smalldatetime。仅表示日期或时间成分的字符数据转换为datetime或smalldatetime数据类型时，未指定的时间成分设置为00:00:00.000，未指定的日期成分设置为1900-01-01。7.使用可选的时间区域指示符(Z)更便于将具有时区信息的XMLdatetime值映射到没有时区的SQLServerdatetime值。Z是时区UTC-0的指示符。其他时区则以+或-方向的HH:MM偏移量来指示。例如：2006-12-12T23:45:12-08:00。从smalldatetime转换为字符数据时，包含秒或毫秒的样式将在这些位置上显示零。使用相应的char或varchar数据类型长度从datetime或smalldatetime值转换时，可截断不需要的日期部分。从样式包含时间的字符数据转换为datetimeoffset时，将在结果末尾追加时区偏移量。这个函数的第三个参数是可选的，该参数用于接收格式代码整型值。表中的例子用于对DateTime数据类型进行转换。在转换SmallDateTime数据类型时，格式不变，但一些元素会显示为0，因为该数据类型不支持毫秒。以下的脚本例子将输出格式化的日期：
SELECT'DefaultDate:'+CONVERT(Varchar(50),GETDATE(),100)
Default
Date:Apr2520051:05PM
SELECT'USDate:'+CONVERT(Varchar(50),GETDATE(),101)
USDate:04/25/2005
SELECT'ANSIDate:'+CONVERT(Varchar(50),GETDATE(),103)
ANSIDate:2005.04.25
SELECT'UK/FrenchDate:'+CONVERT(Varchar(50),GETDATE(),103)
UK/FrenchDate:25/04/2OO5
SELECT'GermanDate:'+CONVERT(Varchar(50),GETDATE(),104)
GermanDate:25.04.2005格式代码0，1和2也可用于数字类型，它们对小数与千位分隔符格式产生影响。而不同的数据类型所受的影响是不一样的。一般来说，使用格式代码0(或者不指定这个参数的值)，将返回该数据类型最惯用的格式。使用1或者2通常显示更为详细或者更精确的值。以下例子使用格式代码0：
DECLARE@NumMoneySET@Num=1234.56SELECTCONVERT(varchar(50),@Num,0)
返回结果如下:1234.56使用值1则返回如下结果:1,234.56使用值2则返回如下结果:1234.5600以下例子和上例相同，但是使用Float类型:33
DECLARE@NumfloatSET@Num=1234.56SELECTCONVERT(varchar(50),@Num,2)
使用值0不会改变所提供的格式，但是使用值1或2将返回以科学计数法表示的数字，后者使用了15位小数：1.23456000000000e+003STR()函数函数这是一个将数字转换为字符串的快捷函数。这个函数有3个参数：数值、总长度和小数位数。如果数字的整数位数和小数位数(要加上小数点占用的一个字符)的总和小于总长度，对结果中左边的字符将用空格填充。在下面第1个例子中，包括小数点在内一共是5个字符。结果显示在网格中，显然左边的空格被填充了。这个调用指定，总长度为8个字符，小数位为4位：
SELECTSTR(123.4,8,4)
结果值的右边以0填充：123.4000。下面给函数传递了一个10字符的值，并指定结果包含8个字符，有4个小数位：
SELECTSTR(123.456789,8,4)
只有将这个结果截断才能符合要求。STR()函数对最后一位进行四舍五入：123.4568。现在，如果为函数传递数字1，并指定结果包含6个字符，有4个小数位，STR()函数将用0补足右边的空位：
SELECTSTR(1,6,4)
1.0000然而，如果指定的总长度大于整数位数、小数点和小数位数之和，结果值的左边将用空格补齐：
SELECTSTR(1,6,4)
1.0000
SELECTSTR(1,12,4)
---------1.0000
游标函数与变量
游标可以处理多行数据，在过程循环中一次访问一行。和基于集合的高效操作相比，这个功能对系统资源的消耗更大。可以用一个函数和两个全局变量来管理游标操作。CURSOR_STATUS()函数函数
这个函数返回一个整型值，表示传递给这个函数的游标类型变量的状态。有很多不同类型的游标会影响这个函数的操作。为简单起见，下表列出了这个函数的常见返回值。返回值10说明
游标包含一行或多行(动态
游标包含0行或者多行)游标不包含行
34
-1-2-3
游标已关闭游标未分配游标不存在
@@CURSOR_ROWS全局变量
这个变量是一个整型值，表示在当前连接中打开的游标中的行数。根据游标类型，这个值也能不代表结果集中的实际行数。@@FETCH_STATUS全局变量
这个变量是一个标记，用于表示当前游标指针的状态。这个变量主要用来判断某行是否存在，以及在执行了FETCHNEXT语句后，是否已执行到结果集的尾部。打开游标时，@@FETCH_STATUS变量值为-1。一旦把第一个值放在游标中，@@FETCH_STATUS变量值就变成0。当不再把更多的行放在游标中时，该变量的值将变回-1。
日期函数
这些函数可以操作DateTime与SmallDateTime类型的值。有些函数可用于解析日期值的日期与时间部分，有些函数可用于比较、操纵日期/时间值。日期数据类型的区别如下表所示。数据类型timedatesmalldatetimedatetimedatetime2datetimeoffset12:35:29.12345672007-05-082007-05-0812:35:002007-05-0812:35:29.1232007-05-0812:35:29.12345672007-05-0812:35:29.1234567+12:15输出
DATEADD()函数函数DATEADD()函数用于在日期/时间值上加上日期单位间隔。比如，要得到2007年4月29日起90天后的日期，可以使用下列语句：
SELECTDATEADD(DAY,90,'4-29-2007')
结果：2007-07-2800:00:00.000可以把下表的值作为时间间隔参数传递给DATEADD()函数。datepartyearyy,yyyy缩写
35
quartermonthdayofyeardayweekweekdayhourminutesecondmillisecondmicrosecondnanosecond
qq,qmm,mdy,ydd,dwk,wwdw,whhmi,nss,smsmcsns
在下面列出的例子中，我们使用和上一个例子一样的日期，并且在这些例子中还包含了时间数据。每个操作的结果将显示在查询的下一行中。18年后：
SELECTDATEADD(YEAR,18,'4-29-198810:30AM')
2006-04-2910:30:00.00018年前：
SELECTDATEADD(YY,-18,'4-29-198810:30AM')
1970-04-2910:30:00.0009000秒后：
SELECTDATEADD(SECOND,9000,'4-29-198810:30AM')
1988-04-2913:00:00.0009000000毫秒前:
SELECTDATEADD(MS,-9000000,'4-29-198810:30AM')
1988-04-2908:00:00.000可以将CONVERT()函数和DATEADD()函数组合在一起，来对1989年9月8日9个月前的日期值进行格式化。
SELECTCONVERT(varchar(20),DATEADD(M,-9,'9-8-1989'),101)
12/08/1988这将返回一个可变长度的字符值，比前面例子结果中的默认日期更易容易理解。这是一个函数嵌套调用，DATEADD()函数的返回值(一个DateTime类型的值)被作为值参数传递给CONVERT()函数。
36
DATEDIFF()函数函数DATEADD()和DATEDIFF()函数可以看作一对表兄弟，有点像乘法与除法。在等式的两端有4个元素：起始日期、时间间隔(datepart)、差值和最终日期。如果已知其中的三个值，就可以求出第4个
值。如果在DATEADD()函数中使用起始日期、一个整型值和一个时间间隔，就可返回与起始日期相关的最终日期值。如果提供了起始日期、时间间隔和最终日期，DATEDIFF()函数就可以返回差值。为了说明这一点，我们选择任意两个日期与一个时间间隔作为参数。这个函数将以所提供的时间间隔为单位返回两个日期之间的差值。要知道1989年9月8日和1991年10月17日之间差了几个月，可编写如下查询代码：
SELECTDATEDIFF(MONTH,'9-8-1989','10-17-1991')
结果是25个月。如果以日期为单位呢?
SELECTDATEDIFF(DAY,'9-8-1989','10-17-1991')
结果是769天。1996年7月2日和1997年8月4日之间差几个星期?
SELECTDATEDIFF(WEEK,'7-2-1996','8-4-1997')
57星期。甚至可以算出自己的年龄是多少秒：
DECLARE@MyBirthDatedatetimeSET@MyBirthDate='7-16-1962'SELECTDATEDIFF(SS,@MyBirthDate,GETDATE())
结果显示有些人已经活了15亿秒了!可以将列名作为参数，把这个函数用在查询中。首先建立一个简单的表，其中包含一些人的和生日：
SELECTc.FirstName,c.LastName,e.BirthDate,DATEDIFF(YEAR,e.BirthDate,GETDATE())ASApproximateAgeFROMHumanResources.EmployeeaseinnerjoinPerson.Contactascone.ContactID=c.ContactIDorderbyc.LastName
下图显示了结果：
37
初看起来结果是对的，但存在的问题是年龄值没有精确到日。比如，根据表中的数据，Nancy的生日是12月21日，他今年将庆祝第32个生日(这个查询在2010年8月运行)。如果依据上述计算结果来确定他的年龄何时变化，就应在一月份的某天给他发生日卡片，这比实际日期提前了11个月。除非用更小的时间单位来计算这些日期的差，否则结果只在雇员实际生日的一年以内是精确的。以下例子将用差值除以一年(包括闰年)的天数，并将结果值转换为int类型，进行取整运算，而不是四舍五入。
SELECTc.FirstName,c.LastName,e.BirthDate,DATEDIFF(YEAR,e.BirthDate,GETDATE())ASApproximateAge,CONVERT(int,DATEDIFF(DAY,e.BirthDate,GETDATE())/365)ASAgeFROMHumanResources.EmployeeaseinnerjoinPerson.Contactascone.ContactID=c.ContactIDorderbyc.LastName
比较这次的结果和上一个例子的结果，看看有什么不同。
可以看到，Nancy是31岁，其他雇员的年龄也精确到了天。表中的BirthDate列存储雇员的生日，并以午夜(00:00:00AM)为界，这是一天中的第一秒。GETDATE()函数返回当前的时间与日期。当前两个日期相差约8小时(写这段文字时是上午8点)。如果希望这个计算更精确，就需要在当前日期的午夜把GETDATE()函数的结果转换为datetime类型。DATEPART()与DATENAME()函数与函数这两个函数用于返回datetime或者shortdatetime值的日期部分
。DATEPART()函数返回一个整型值；DATENAME()函数返回一个包含描述性文字的字符串。比如，将日期4-29-1988传递给DATEPART()函数，如指定返回月份值，则返回数字4：
SELECTDATEPART(MONTH,'4-29-1988')
38
而使用相同的参数，DATENAME()函数返回04（这取决于你的机器的本地语言，如果是英文版，那么将返回April）：
SELECTDATENAME(MONTH,'4-29-1988')
这两个函数都接收和DATEADD()函数一样的时间间隔参数常量。GETDATE()与GETUTCDATE()函数与函数这两个函数都用于返回datetime类型的当前日期与时间。GETUTCDATE()函数使用服务器上的时区设置来求出UTC时间，这和格林威治标准时间或飞行员所说的"祖鲁时"(ZuluTime)是一样的。两个函数都能精确到3.33毫秒。
SELECTGETDATE()SELECTGETUTCDATE()
执行这两个函数，都将返回未经格式化的结果，见下图：
我在北京，和UTC时间相差8个小时，和标准时间相差9个小时。可以使用如下DATEDIFF()函数来验证这个时间差值：
SELECTDATEDIFF(HOUR,GETDATE(),GETUTCDATE())
SYSDATETIME()和SYSUTCDATETIME()函数和函数这两个SQLServer2008函数等价于GETDATE()和GETUTCDATE()函数，但不是返回datetime数据类型的结果，而是返回SQLServer2008新的datetime2数据类型的结果，该数据类型可以精确到100纳秒，当然这取决于服务器安装的硬件。
SELECTSYSDATETIME()SELECTSYSUTCDATETIME()
DAY()、MONTH()和YEAR()函数、和函数这三个函数分别返回以整数表示的datetime或者smalldatetime类型值的日、月、年。它们的用途很广泛，如可以创建独特的个性化日期格式。假设需要创建一个自定义的日期值作为字符串，通过将这三个函数的输出结果转换成字符类型，然后进行连接操作，就可以对输出结果以任何形式进行组合了：39
SELECT'Year:'+CONVERT(varchar(4),YEAR(GETDATE()))+',Month:'+CONVERT(varchar(2),MONTH(GETDATE()))+',Day:'+CONVERT(varchar(2),DAY(GETDATE()))
这个脚本生成下列结果：Year:2008,Month:2,Day:20下一节将讨论字符串操纵函数，并使用相似的技术来构建一个紧凑的定制时间戳。
字符串操纵函数
字符串函数可以解析、替换、操纵字符型值。在处理原始字符数据时，最大的挑战之一是如何可靠地提取出有意义的信息。有很多字符串解析函数可用于标识和解析子字符串(一个大字符型值的一部分)。我们一直在做这种事，在我们阅读文件、发票或者书面材料时，就会本能地标识、分离出有意义的信息片段。这个过程的自动化非常困难，即使是处理不太复杂的文本，也很困难。这些函数包含几乎所有必需的工具，而挑战在于如何找出最简单、最高效的方法。ASCII()、CHAR()、UNICODE()和NCHAR()
函数、、和函数
这四个函数是相似的，它们都可以在字符和字符的标准数字表示之间转换。美国标准信息交换码(AmericanStandardCodeforInformationInterchange，ASCII)标准字符集包含128个字母、数字和标点符号。这个字符集是IBMPC体系结构的基础，虽然有些字符现在看来已经很古老了，但还是被保留了下来，且仍是现代计算机技术的核心。如果在计算机上使用英语，则键盘上的每个字符都是用ASCII码表示的。这对说英语(至少以英语打字)的计算机用户来说是有利的，但是其他人又该怎么办呢?在计算机的发展过程中，ASCII字符集发布没多长时间便过时了。人们很快将它扩展成为256个字符的ANSI字符集，一个字符用一个字节来保存。这个扩展的字符列表满足了许多其他用户的需求，可以支持主要的欧洲语言字符，不过仍是美国标准(由美国国家标准学会持有)，仍建立在最初的英语字符集的基础上。为了支持所有可印刷的语言，人们制订了Unicode标准，它支持多种语言特定的字符集。每个Unicode字符需要2个字节的存储空间，是ASCII与ANSI字符的两倍。但是使用2个字就可以表示超过65000个不同的字符，完全能够支持东欧和亚洲字符。SQLServer同时支持ASCII与Unicode两种标准。ASCII()和CHAR()是两个基于ASCII的函数，这两个函数可将计算机上应用的每个字符表示为数字。要确定代表一个字符的数字是什么，就应给ASCII()函数传送只包含一个字符的字符串，如下：
SELECTASCII('A')
结果是65。如要将一个已知数字转换为字符，又该怎么办？使用CHAR()函数即可：
SELECTCHAR(65)
结果是字母A。要得到完整的ASCII字符值列表，可以对一个临时表填充从0到127的数字，然后调用CHAR()函数返回相应的字符。为了节省空间，我们对以下这个脚本进行了删节，但包含整个结果集，并以多栏格式给出。
--创建一个临时表来保存ASCII码:CreateTable#ASCIIVals(ASCIIValuesmallint)
40
--插入数字0-127到临时表中:declare@Numberintset@Number=0while(@Number<128)beginInsertInto#ASCIIVals(ASCIIValue)Select@Numberset@Number=@Number+1end--查询所有的整型数字与其对应的ASCII码:SELECTASCIIValue,CHAR(ASCIIValue)ASCharacterFROM#ASCIIValsdroptable#ASCIIVals
表6-12是以多栏网格重新格式化的结果集。需要注意的是这里将不可印刷的控制字符以方括号表示。由于许多因素限制，如所安装的字体或语言不同，下表的显示可能会有稍许差异。
UNICODE()函数是ASCII()的Unicode等价函数，NCHAR()函数和CHAR()函数的功能相同，只不过NCHAR()是用于Unicode字符的。SQLServer的nchar与nvarchar类型能
存储任何Unicode字符，可以和这两个函数一起使用。对于特别大的值，ntext类型和nvarchar(max)类型也支持Unicode字符。要返回扩展字符编码集中的字符，可以将字符编码传递给NCHAR()函数：
SELECTNCHAR(220)
返回字母ü。
SELECTNCHAR(233)
返回带重音符号的小写e：é。
SELECTNCHAR(241)
返回西班牙语的"enya"，或者带有发音符号的n：
。
当然，ASCII标准也支持所有的欧洲字符，所以使用CHAR()函数也可以返回这些扩展字符。如果对256~65536之间的值使用CHAR()函数，返回值就很有趣了。例如，下面的查询返回希腊字符Ω：
SELECTNCHAR(433)
下面的查询返回西里尔字母Ya(Я)。
41
SELECTNCHAR(1071)
CHARINDEX()和PATINDEX()函数和函数CHARINDEX()是原始的SQL函数，用于寻找在一个字符串中某子字符串第一次出现的位置。如函数名所示，这个函数返回一个整型值，表示某子字符串的第一个字符在整个字符串中的位置索引。以下脚本用于在字符串Washington中寻找子字符串sh的出现位置:
SELECTCHARINDEX('sh','Washington')
返回的结果是3，表明s是字符串Washington中的第3个字符。这说明CHARINDEX函数匹配字符的索引是从1开始的。如果没有匹配到任何结果，函数将返回0。在这个例子中使用两个字符作为子字符串并没有特别意义，但是如果字符串包含多个s字符，就有意义了。PATINDEX()函数和CHARINDEXO函数类似，它执行相同的操作，但方法稍许不同，该函数增加了对通配符(即Like运算符中使用的字符)的支持。顾名思义，它将返回一个字符模式的索引。这个函数也可以和ntext、nchar(max)和nvarchar(max)等大字符类型一起使用。注意，如果和这些大字符类型一起使用，PATINDEX()函数将返回bigint类型的值，而不是int类型的值。以下是一个例子：
SELECTPATINDEX('%M_r, s%','ThestarsnearMarsarefarfromours')
注意，如果想找到一个字符串，在所比较的字符串的前后各有0个或者多个字符，则两个百分符都是必须的。下划线表明这个位置上的字符不必匹配，它可以是任意字符。和使用相同字符串的CHARINDEX()函数作一下比较：
SELECTCHARINDEX('Mars','ThestarsnearMarsarefarfromours')
这两个函数都返回索引值16。请注意这些函数的执行过程。下一节将把这两个函数和SUBSTRING()函数组合在一起，演示如何使用界定符解析字符串。LEN()函数函数LEN()函数用于返回一个代表字符串长度的整型值。这是一个简单、有用的函数，经常与其他函数一起使用，来应用业务规则。以下例子将月份和日期转换为字符类型，然后测试它们的长度。如果月份日期只有一个字符，就填充字符0，然后组合成一个8字符
的美国格式的日期字符串(MMDDYYYY)。
DECLARE@MonthCharvarchar(2),@DayCharvarchar(2),@DateOutchar(8)SET@MonthChar=CAST(MONTH(GETDATE())ASvarchar(2))SET@DayChar=CAST(DAY(GETDATE())ASvarchar(2))--Makesuremonthanddayaretwocharlong:IFLEN(@MonthChar)=1SET@MonthChar='0'+@MonthCharIFLEN(@DayChar)=1SET@DayChar='0'+@DayChar--Builddatestring:SET@DateOut=@MonthChar+@DayChar+CAST(YEAR(GETDATE())ASchar(4))SELECT@DateOutASOutputDate
42
这个脚本将返回代表日期的8个字符:08152010LEFT()和RIGHT()函数和函数LEFT()与RIGHT()函数是相似的，它们都返回一定长度的子字符串。这两个函数的区别是，它们返回的分别是字符串的不同部分。LEFT()函数返回字符串最左边的字符，顺序从左数到右。RIGHT()函数正好相反，它从最右边的字符开始，以从右到左的顺序返回特定数量的字符。看一看使用这两个函数返回"GeorgeWashington"这个字符串的子字符串的例子。如果使用LEFT()函数返回一个5字符的子字符串，则函数先定位最左边的字符，向右数5个字符，然后返回这个子字符串，如下所示。
DECLARE@FullNamevarchar(25)SET@FullName='GeorgeWashington'SELECTLEFT(@FullName,5)
结果为：Georg如果使用RIGHT()函数返回一个5字符的子字符串，则函数先定位最右边的字符，向左数5个字符，然后返回这个子字符串，如下所示。
DECLARE@FullNamevarchar(25)SET@FullName='GeorgeWashington'SELECTRIGHT(@FullName,5)
结果为：ngton要想返回字符串中有意义的部分，这两个函数都不是特别有用。如果想返回全名中的姓氏或者名字，该怎么办？这需要多做一点工作。如果能确定每个中空格的位置，就可以使用LEFT()函数在全名中读取名字。在这种情况下，可以使用CHARINDEX()或者PATINDEX()函数来定位空格，然后使用LEFT()函数返回空格前的字符。下面是第一个用过程方法编写的例子，它将处理过程分解成以下步骤：
DECLARE@FullNamevarchar(25),@SpaceIndextinyintSET@FullName='GeorgeWashington'--Getindexofthedelimitingspace:SET@SpaceIndex=CHARINDEX('',@FullName)--Returnallcharacterstotheleftofthespace:SELECTLEFT(@FullName,@SpaceIndex-1)
结果为：George如果不想在结果中包含空格，就需要从@SpaceIndex值中减去1，这样结果中就只有名字了。SUBSTRING()函数函数SUBSTRING()函数能够从字符串的一个位置开始，往右数若干字符，返回一个特定长度的子字符串。和LEFT()函数不同之处是，该函数可以指定从哪个位置开始计数，这样就可以在字符串的任何位置摘取子字符串了。这个函数需要三个参数：要解析的字符串、起始位置索引、要返回的子字符串长度。如果要返
43
回到所输入字符串
尾部的所有字符，可以使用比所需长度更大的长度值。SUBSTRING()函数将返回最大可能长度的字符数，而不会将多出的长度以空格填充。只要指定字符串最左边的字符(1)为起始索引，就可以用SUBSTRING()函数替代LEFT()函数。继续上一节的例子。可以设置起始位置与长度，返回字符串中间的值。在这个例子中，从位置4开始，返回一个6字符的子字符串"rgeWa"。
DECLARE@FullNamevarchar(25)SET@FullName='GeorgeWashington'SELECTSUBSTRING(@FullName,4,6)
现在将上述各函数组合在一起，即可从名字+空格+姓氏格式的全名字符串中解析出名字和姓氏。使用先前的逻辑，通过函数嵌套来减少脚本的行数，并去掉@SpaceIndex变量。下面用SUBSTRING()函数替代LEFT()函数：
DECLARE@FullNamevarchar(25)SET@FullName='GeorgeWashington'--Returnfirstname:SELECTSUBSTRING(@FullName,1,CHARINDEX('',@FullName)-1)
类似的逻辑可以用于解析姓氏，但是必须将起始位置更改为空格后的那个字符。如果空格在第7个位置上，那么姓氏将从第8个位置开始。这就意味着起始位置是CHARINDEX()的返回结果加上1。
DECLARE@FullNamevarchar(25)SET@FullName='GeorgeWashington'--Returnlastname:SELECTSUBSTRING(@FullName,CHARINDEX('',@FullName)+1,LEN(@FullName))
把上述步骤组合在一起，就可以运行下面的查询，从全名变量中提取出名字和姓氏：
DECLARE@FullNamevarchar(25)SET@FullName='GeorgeWashington'--Returnfirstname:SELECTSUBSTRING(@FullName,1,CHARINDEX('',@FullName)-1)ASFirstName,SUBSTRING(@FullName,CHARINDEX('',@FullName)+1,LEN(@FullName))ASLastName
结果为：
44
传递给SUBSTRING()函数的值是空格所在位置加上1，并将该值作为起始位置，这将是姓氏的第1个字母。由于不可能总是知道名字的长度，所以将LEN()函数的结果作为子字符串长度参数传递进来，当SUBSTRING()函数到达这个位置时，就到达了字符串的末尾，这样就可以将字符串中从空格后面开始的所有字符都包含进来了。为了举例方便，先创建并填充一个临时表:
CREATETABLE#MyNames(FullNamevarchar(50))GOINSERTINTO#MyNames(FullName)SELECT'FredFlintstone'INSERTINTO#MyNames(FullName)SELECT'WilmaFlintstone'INSERTINTO#MyNames(FullName)SELECT'BarneyRubble'INSERTINTO#MyNames(FullName)SELECT'BettyRubble'INSERTINTO#MyNames(FullName)SELECT'GeorgeJetson'INSERTINTO#MyNames(FullName)SELECT'JaneJetson'go--droptable#MyNames
下面执行一个使用函数调用来解析名字和姓氏值的单行查询表达式。这里对@FullName变量的引用被表中的FullName列所替代:
SELECTSUBSTRING(FullName,1,CHARINDEX('',FullName)-1)ASFirstName,SUBSTRING(FullName,CHARINDEX('',FullName)+1,LEN(FullName))ASLastNameFROM#
MyNames
在下图所示的结果中，显示了两个不同的列，分别是名字和姓氏。
LOWER()和UPPER()函数和函数
这两个函数很容易理解，它们用于将字符串中所有字符分别都转换为小写和大写，这在比较用户输入或者存储用于比较的字符串时是非常有用的。字符串比较通常是区分大小写的，这取决于SQLServer安装时的设置。如果和其他的字符串操纵函数一起使用，就可以将字符串转换为合适的大小写，以便存储或显示。以下例子说明混合大小写的名字，假设名字中的第2个大写子字符串前只包含一个空格，但在特殊情况下也有一些名字是没有空格的。这个例子很容易通过扩展来处理包含其他类型的混合大小写名字(如以MC开头的名字，带连接号的名字等)。
45
DECLARE@LastNamevarchar(25),@SpaceIndextinyintSET@LastName='mcdonald'--Findspaceinname:SET@SpaceIndex=CHARINDEX('',@LastName)IF@SpaceIndex>0substringSELECTUPPER(LEFT(@LastName,1))+LOWER(SUBSTRING(@LastName,2,@SpaceIndex-1))+UPPER(SUBSTRING(@LastName,@SpaceIndex+1,1))+LOWER(SUBSTRING(@LastName,@SpaceIndex+2,LEN(@LastName)))ELSEchar.SELECTUPPER(LEFT(@LastName,1))+LOWER(SUBSTRING(@LastName,2,LEN(@LastName)))
这个脚本将返回MCDonald。还可以对这个例子进行扩展，以处理姓氏包含撇号的情况。在这个例子的业务规则中，空格是不考虑的。如果找到了撇号，就将后面的字符全部转为大写。请注意如果要在脚本中测试撇号，就必须输入两次撇号('')，以表明这是一个文字，而不是一对单引号。姓氏中只存储一个撇号。
--Testvalue
--Space:Capitalizefirst&;
--Nospace:Caponlyfirst
DECLARE@LastNamevarchar(25),@SpaceIndextinyint,@AposIndextinyintSET@LastName='o''malley'--Findspaceinname:SET@SpaceIndex=CHARINDEX('',@LastName)--Findliteral'inname:SET@AposIndex=CHARINDEX('''',@LastName)IF@SpaceIndex>0substringSELECTUPPER(LEFT(@LastName,1))+LOWER(SUBSTRING(@LastName,2,@SpaceIndex-1))+UPPER(SUBSTRING(@LastName,@SpaceIndex+1,1))+LOWER(SUBSTRING(@LastName,@SpaceIndex+2,LEN(@LastName)))ELSEIF@AposIndex>0substringSELECTUPPER(LEFT(@LastName,1))+LOWER(SUBSTRING(@LastName,2,@AposIndex-1))+UPPER(SUBSTRING(@LastName,@AposIndex+1,1))+LOWER(SUBSTRING(@LastName,@AposIndex+2,LEN(@LastName)))ELSE--Nospace:Caponlyfirst
46
--Testvalue
--Space:Capitalizefirst&;
--Apostrophe:Capfirst&;
char.SELECTUPPER(LEFT(@LastName,1))+LOWER(SUBSTRING(@LastName,2,LEN(@LastName)))
这个脚本返回O'Malley。LTRIM()和RTRIM()函数和函数
这两个函数分别返回将字符串的左边和右边的空白修剪掉之后的字符串:
DECLARE@Value1char(10),@Value2char(10)SET@Value1='One'SET@Value2='Two'SELECT@Value1+@Value2SELECTCONVERT(
varchar(5),LEN(@Value1+@Value2))+'characterslong.'SELECTRTRIM(@Value1)+RTRIM(@Value2)SELECTCONVERT(varchar(5),LEN(RTRIM(@Value1)+RTRIM(@Value2)))+'characterslongtrimmed.'
结果如下：
REPLACE()函数函数REPLACE()函数可以把字符串中的某个字符或某个子字符串替换为另一个字符或者子字符串，该函数可以用于全局查找和替换工具中。
DECLARE@Phrasevarchar(1000)SET@Phrase='IaintgunnausepoorgrammarwhencommentingscriptandIaintgunnacomplainaboutit.'SELECTREPLACE(@Phrase,'aint','amnot')
REPLICATE()和SPACE()函数和函数
47
在需要将一些字符重复填充进一个字符串时，这两个函数是非常有用的。这里也使用SUBSTRING()例子中的临时表为每个名字填满20个字符，然后将20减去各个字符串的长度，以便将正确的值传递给REPLICATE()函数:
SELECTFullName+REPLICATE('*',20-LEN(FullName))FROM#MyNames
结果是每个名字后面都填满了星号，各个名字的总长度都是20个字符:FredFlintstorle*****WilrnaFlintstone****BarneyRubble*******BettyRubble********GeorgeJetson********JaneJetson**********SPACE()函数与上述函数类似，区别在于该函数使用空格进行填充。它返回一个由空格组成的字符串，空格的个数由参数定义。
SELECTFullName+SPACE(20-LEN(FullName))FROM#MyNames
如果返回"#MyNames表不存在"的错误，只需再次运行本文前面"SUBSTRING()函数"一节的CREATETABLE脚本即可。REVERSE()函数函数
顾名思义，这个函数用于将字符串中的字符颠倒过来。这在处理连接列表中的单个字符值时将会被用到。
SELECTREVERSE('ThestarsnearMarsarefarfromours.')
结果为：.sruomorfraferasraMraensratsehTSTUFF()函数函数
这个函数可将字符串中的一部分替换为另一个字符串。它本质上是将一个字符串以特定的长度插入另一个字符串中的特定位置上。这对于源值与目的值的长度不一样的字符串替换是很有用的。下列代码用于将字符串中的价格替换为109.95:Pleasesubmityourpaymentfor99.95immediately.价格值是从第32个字符开始的，有5个字符长。在这个位置上插入的子字符串有多长并不重要，只需要知道需要删除多少个字符就可以了。
SELECTSTUFF('Pleasesubmityourpaymentfor99.95immediately.',32,5,'109.95')
结果为：Pleasesubmityourpaymentfor109.95immediately.QUOTENAME()函数函数
48
这个函数和SQLServer对象名组合使用，以将结果传递给表达式。它只用于给输入的字符串加一对方括号，并返回新形成的字符串。如果参数包含保留的分隔符或者封装字符(比如引号或括号)，这个函数将修改字符串，以便SQLServer能将结果字符串中的这类字符当成文本字符。如下面的例子所示，查询的结果如图
6-10所示。
SELECTQUOTENAME(COLUMN_NAME)ASColumnNameFROMINFORMATION_SCHEMA.COLUMNS
数学函数
下表中列出的函数用于执行多种普通与特殊的数学运算，可以执行代数、三角、统计、估算与财政运算等运算。函数ABS()ACOS()ASIN()ATAN()ATN2()CEILING()COS()COT()DEGREES()EXP()FLOOR()LOG()LOG10()说明
返回一个数的绝对值计算一个角的反余弦值，以弧度表示计算一个角的反正弦值，以弧度表示计算一个角的反正切值，以弧度表示计算两个值的反正切，以弧度表示返回大于或等于一个数的最小整数计算一个角的正弦值，以弧度表示计算一个角的余切值，以弧度表示将一个角从弧度转换为角度指数运算返回小于或等于一个数的最大整数计算以2为底的自然对数计算以10为底的自然对数
49
PI()POWER()RADIANS()RAND()
返回以浮点数表示的圆周率幂运算将一个角从角度转换为弧度返回以随机数算法算出的一个小数，可以接收一个可选的种子值
ROUND()
对一个小数进行四舍五入运算，使其具备特定的精度
SIGN()SIN()SQRT()SQUARE()TAN()
根据参数是正还是负，返回–1或者1计算一个角的正弦值，以弧度表示返回一个数的平方根返回一个数的平方计算一个角正切的值，以弧度表示
元数据函数
这是一些工具函数，它们返回SQLServer配置细节、服务器与数据库设置细节的信息，包括一组用于返回不同对象的属性状态的通用以及专用函数，这些函数把对Master数据库中系统表以及用户数据库的查询封装在函数中。建议读者使用这些函数以及其他的系统函数，而不是自己创建对系统表的查询，以防今后SQLServer版本对模式进行更改。
排列函数
这些函数被用于以与结果集顺序无关的特定顺序，枚举已排序的或排在前面的结果集。ROW_NUMBER()函数函数ROW_NUMBER()函数根据作为参数传递给这个函数的ORDERBY子句的值，返回一个不断递增的整数值。如果ROW_NUMBER的ORDERBY的值和结果集中的顺序相匹配，返回值将是递增的，以升序排列。如果ROW_NUMBER的ORDERBY子句的值和结果集中的顺序不同，这些值将不会按顺序列出，但它们表示ROW_NUMBER函数的ORDERBY子句的顺序。如下面的例子和结果所示：
SELECTProductCategoryID,Name,ROW_NUMBER()OVER(ORDERBYName)ASRowNumFROMProduction.ProductCategoryORDERBYName
由于ROW_NUMBER()调用中的ORDERBY子句和查询结果的顺序匹配，所以对这些结果按顺序列出，如下图所示：
50
不过，在函数调用中使用另一个ORDERBY子句时，这些值就是无序的了。
SELECTProductCategoryID,Name,ROW_NUMBER()OVER(ORDERBYName)ASRowNumFROMProduction.ProductCategoryORDERBYProductCategoryID
这是了
解如何使用ORDERBY子句对结果进行排序的有效方法。如下图所示：
RANK()与DENSE_RANK()函数与函数这两个函数与ROW_NUMBER()函数类似，因为它们都返回一个基于ORDERBY子句的值。不过这些值不一定永远是唯一的。排列值对于所提供的ORDERBY子句中的重复结果而言也是重复的，而且唯一性是仅仅基于ORDERBY列表中的唯一值的。这些函数用不同的方法来处理重复的值。RANK()函数保留列表中行的位置序号，对于每个重复的值，该函数会跳过下面与其相邻的值，于是就可以将下一个不重复的值保留在正确的位置上。其行为类似于短跑比赛中的并列成绩。例如刘翔与DayronRobles（古巴）在110栏的比赛中都跑出了12’92的成绩，那他们就是并列第一，而其后的一名选手将会获得第三名的成绩。
SELECTProductID,Name,ListPrice,RANK()OVER(ORDERBYListPriceDESC)AS[Rank]FROMProduction.ProductORDERBY[Rank]
注意在下图的结果列表中，重复的价格值所对应的结果是相同的，而每个连接之后的值都被跳过了。比如，产品"Road-150Red,52"和"Road-150Red,56"都排在第1，而接下来的行"Mountain-100Silver,38"就排在第6了。
51
DENSE_RANK()函数的工作方式与RANK()函数相同，不过它不会跳过每个连接后的值，这样就不会有值被跳过了，但是在连接处排列序号位置将会丢失。
SELECTProductID,Name,ListPrice,DENSE_RANK()OVER(ORDERBYListPriceDESC)AS[Rank]FROMProduction.ProductORDERBY[Rank]
下图的结果重复了排列值，但是不会跳过列中的任何数字。
NTILE(n)函数函数
这个函数也用于对结果进行排列，并返回一个整型的排列值，但是它不会对结果以唯一的排列顺序进行枚举，而是将结果切分为有限数量的排列组。比如，一个表有10000行，使用1000为参数值调用NTILE()函数，即NTILE(1000)，并将结果分成以10为单位的1000个组，每个组赋予相同的排列值。和本节讨论的其他排列函数一样，NTILE()函数也支持OVER(ORDERBY…)语法。下面的例子根据产品价格，按照从高到低的顺序把Product表分为50组产品：
SELECTProductID
52
,Name,ListPrice,NTILE(50)OVER(ORDERBYListPriceDESC)ASGroupedProductsFROMProduction.ProductORDERBYGroupedProducts
结果为：
安全函数
与安全相关的函数返回SQLServer用户的角色成员和权限信息。这类函数也包括一组管理事件与跟踪的函数。下表显示了这些函数：函数说明
fn_trace_geteventinfo()fn_trace_getfilterinfo()fn_trace_getinfo()fn_trace_getable()HAS_DBACCESS()IS_MEMBER()IS_SRVROLEMEMBER()SUSER_SID()
为指定的跟踪ID返回一个填充事件信息的表类型值为指定的跟踪ID返回一个填充与过滤器有关的信息的表类型值为指
定的跟踪ID返回一个填充跟踪信息的表类型值为指定的跟踪ID返回一个填充文件信息的表类型值返回一个表明当前用户是否有访问指定数据库权限的标志返回一个表明当前用户是Windows组用户还是SQLServer用户的标志返回一个表明当前用户是否是数据库服务器角色成员的标志返回指定用户的登录名的安全ID，或者(如果参数被忽略)返回当前用户的安全ID。返回指定用户的用户ID，或者(如果参数被忽略的话)返回当前用户的用户ID
SUSER_SNAME()
返回指定安全ID的登录名。如果不提供任何安全ID，则返回当前用户的登录名
USER_ID()
返回指定用户名的用户ID，或者(如果参数被忽略的话)返回当前用户的用户ID
53
USER_NAME()
返回指定用户ID的用户名
系统函数与系统变量
本节讨论具有多种用途的工具函数，包括值比较、值类型测试等功能。这个类别的函数也包罗了其他函数：函APP_NAME()COALESCE()COLLATIONPROPERTY()数说明
返回与当前连接相关联的应用程序的名字从以逗号分隔的表达式列表中返回第一个非空值返回一个特定字符集排序规则的特定属性的值。这些属性包括CodePage、LCID、ComparisonStyle
CURRENT_TIMESTAMP()
返回当前日期与时间。和GETDATE()函数是同义的。这个函数的存在只是为了与ANSI-SQL兼容
C1UJRRENT_USER()DATALENGTH()
返回当前用户的名字。与USER_NAME()函数相同返回存储或处理一个值所需的字节数。对于ANSI字符串类型，这个函数返回的值与LEN()函数相同，但对于其他数据类型而言就可能不一定相同了
fn_helpcollations()fn_servershareddrives()fn_virtualfilestats()FORMATMESSAGE()
返回一个填充有由当前SQLSewer版本支持的字符集排序规则的表类型值返回一个填充有服务器共享的驱动列表的表类型值返回一个填充有包括日志文件在内数据库文件的I/O状态的表类型值从sysmessages表中为指定的信息代码和以逗号分隔的参数列表返回错误信息
GETANSINULL()
根据ANSLNULL_DFLT_ON与ANSLNULL_DFLT_OFF数据库设置返回数据库的可空性设置
HOST_ID()HOST_NAME()IDENT_CURRENT()IDENT_INCR()IDENT_SEED()IDENTITY()ISDATE()ISNULL()ISNUMERIC()
返回当前会话的工作站ID返回当前会话的工作站名返回最后一个为指定的表生成的标识(ID)值。与会话、范围无关返回最后一次创建的标识(ID)列中定义的增量值返回最后一次创建的标识(ID)列中定义的种子值用在SELECT…INTO语句中，在一个列中插入自动生成的标识值返回一个表明指定的值是否可被转换为日期值的标志判断指定的值是否是空值，然后返回一个事先提供的替代值返回一个表明指定的值是否可被转换为数字值的标志
54
NEWID()
返回
一个新生成的UniqueIdentifier类型的值。这是一个128位的整型、全球唯一的值，通常以字母或数字十六进制来表示(如89DE6247·C2E242DB-8CE8·A787E505D7EA)。这个类型经常被用作复制的和半连接系统中的主键.
NULLIF()PARSENAME()PERMISSIONS()
两个特定的参数的值如果是相同的，则返回NULL返回一个具有4部分对象名的特定部分返回一个整型值，该值是一个表示当前用户在指定的数据库对象上权限或者权限组合的位映像
ROWCOUNT_BIG()
与@@RowCount变量一样，这个函数返回被最后一条语句修改或返回的行数量。返回值类型是bigint
SCOPE_IDENTITY()
与@@IDENTIY变量一样，这个函数返回限制在当前会话与范围内的最后一次生成的标识值
SERVERPROPERTY()
返回一个表示服务器属性状态的标记。属性包括Collation、Edition、EngineEdition、InstanceName、IsClustered、IsFullTextInstalled、IsIntegrated-SecurityOnly、IsSingleUser、IsSyncWithBackup、LicenseTYpe、MachineName、NumLicenses、ProcessID、ProductLevel、ProductVersion、ServerName
SESSION_USERSESSIONPROPERTY()
返回当前用户名。调用本函数不需要括号返回表示一个会话属性状态的标记。属性包括:ANSL_NULLS，ANSI_PADDING，ANSL_WARNINGS，ARITHABORT，CONCAT_NULL_YIELDS_NULL，NUMERIC_ROUNDABORT，QUOTED_IDENTIFIER
STATS_DATE()SYSTEM_USERUSER_NAME()
返回指定的索引统计信息最后一次被更新的时间返回当前用户名。调用本函数不需要括号为一个指定的用户ID返回用户名。如果没有提供ID号则返回当前的数据库用户
COALESCE()函数函数COALESCE()函数是非常有用的，它返回其参数中第一个非空表达式。它能够节省颇多IF或者CASE分支逻辑。以下例子用产品数据填充一个表，每个产品最多有3种价格:
CREATETABLE#ProductPrices(ProductNamevarchar(25),SuperSalePriceMoneyNULL,SalePriceMoneyNULL,ListPriceMoneyNULL)GOINSERTINTO#ProductPricesVALUES('StandardWidget',NULL,NULL,15.95)INSERTINTO#ProductPricesVALUES('EconomyWidget',NULL,9.95,12.95)INSERTINTO#ProductPricesVALUES('DeluxeWidget',19.95,20.95,22.95)INSERTINTO#ProductPricesVALUES('SuperDeluxeWidget',29.45,32.45,
55
38.95)INSERTINTO#ProductPricesVALUES('ExecutiveWidget',NULL,45.95,54.95)GO
所有的产品都有定价，有些有销售价，有些还有促销价。一项产品的当前价格是所有己有价格的最低价，或者在读取每个价格列时以列出顺序读到的第一个非空值:
SELECTProductName,COALESCE(SuperSalePrice,SalePrice,ListPrice)ASCurrentPriceFROM#ProductPrices
这个方法比使用多行分支与判断逻辑要简洁得多，而结果也是同样简单，如下图所示：
DATALENGTH()函数函数DATALENGTH()函数返回一个用于对值进行管理的字节数，这有助于揭示不
同数据类型间的一些有趣差别。当把varchar类型传递给DATALENGTH()和LEN()函数时，它们将返回相同的值：
DECLARE@Valuevarchar(20)SET@Value='abc'SELECTDATALENGTH(@Value)SELECTLEN(@Value)
这些语句的返回值都为3。因为varchar类型使用了3个单字节字符来存储三个字符的值。然而，如果使用nVarchar类型来管理相同长度的值，就要占用多一倍的字节:
DECLARE@Valuenvarchar(20)SET@Value='abc'SELECTDATALENGTH(@Value)SELECTLEN(@Value)
DATALENGTH()函数返回值为6，因为每个使用Unicode字符集的字符都要占用2个字节。LEN()函数返回值为3，因为这个函数返回字符数，不是字节数。以下是一个有趣的测试：要存储一个值为2的整型变量，要占用多少个字节？而如果要存储一个值为20亿的整型变量，又将占用多少个字节呢？试一下：
DECLARE@Value1int,@Value2intSET@Value1=2SET@Value2=2000000000
56
SELECTDATALENGTH(@Value1)SELECTLEN(@Value1)SELECTDATALENGTH(@Value2)SELECTLEN(@Value2)
在这两种情况下，DATALENGTH()函数都返回4。因为int类型不论值是多少，总是使用4个字节。LEN()函数本质上将整型值当成已转换成字符型的数据来处理，所以，在这个例子中，它分别返回1和10，即值的位数。在下表中的全局系统变量都将返回int类型的值。这些变量可用于存储过程和其他实现定制业务逻辑的编程对象。变量说明
@@ERROR@@IDENTITY@@ROWCOUNT@@TRANCOUNT
当前会话最后一次发生的错误代码当前会话最后一次生成的标识值当前会话中最后一次返回结果集的执行操作所返回的行数当前会话中活动的事务数。这是在执行相关的COMMITTRANSACTION或者ABORTTRANSACTION语句之前嵌套的多个BEGINTRANSACTION语句的结果
系统统计变量
下表描述了用于确定数据库系统使用信息与环境信息的管理工具：变量说明
@@CONNECTIONS@@CPU_BUSY@@IDLE@@IO_BUSY@@PACK_RECEIVED@@PACK_SENT@@PACKET_ERRORS@@TIMETICKS@@TOTAL_ERRORS@@TOTAL_READ@@TOTAL_WRITE
返回打开连接的次数从上次启动服务器开始，SQLServer一共工作的毫秒数从上次启动服务器开始，SQLServer一共空闲的毫秒数从上次启动服务器开始，SQLServer一共处理I/0的毫秒数从上次启动服务器开始，SQLServer一共收到的网络数据包数从上次启动服务器开始，SQLServer一共发送的网络数据包数从上次启动服务器开始，SQLServer一共收到的网络数据包错误数每个时钟滴答有多少毫秒从上次启动服务器开始，SQLServer一共收到的磁盘I/O错误数从上次启动服务器开始，SQLServer一共进行的物理磁盘读取次数从上次启动服务器开始，SQLServer一共进行的物理磁盘写入次数
小结
函数用于实现业务逻辑
，并且能够将编程功能带入查询中。许多有用而且强大的函数是T-SQL的标准功能。和面向过程、面向对象语言中的函数一样，SQL函数也将程序功能封装到一个简单的可重用的包中，这就减少了查询设计人员的很多工作。由于Transact-SQL是面向任务的语言，而不是过程语言。虽57
然函数可以进行过程编程，可以在查询中构建颇为复杂的逻辑，但是SQL语言的优势在于让设计人员表达出设计意图，而不是完成一项任务的确切步骤与方法。只要使用方法正确，这些步骤和方法都可以由函数来实现。在T-SQL中，参数用于将值传递给函数，大多数函数的返回结果是一个标量，或者说单一值。函数分为确定性函数与非确定性函数。在使用相同的参数时，确定性函数总是返回相同的值，而非确定性函数的返回值则与其他资源有关，所以SQLServer必须显式地执行这种函数。因此，在定制的SQL编程对象中，对非确定性函数的使用是有限制的。SQL函数执行种类繁多的重要任务，包括数学运算、比较、日期解析与操纵、高级字符串操纵等。绿色通道：好文要顶关注我收藏该文与我联系好文要顶关注我收藏该文与我联系好文要顶关注我收藏该文
58
