sql-server-存储过程及应用实例笔记

('2015—EASYMAN学习笔记（sql）1、CREATEproc新建存储过程ALTERPROC（EDURE–可省）修改存储过程列：ALTERPROCEDURE[dbo].[getcrdh]@dhnvarchar(12)output--输出ASdeclare@zhintdeclare@xhvarchar(4)BEGINEndDORPPROC删除存储过程2、插入数据的两种方式：A.InsertINTOtable(field1,field2,...)values(value1,value2,...)B.SELECTINTOFROM语句语句形式为：SELECTvale1,value2intoTable2fromTable1要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。示例如下：SELECTINTOFROM语句创建表Table2并复制数据selecta,cINTOTable2fromTable1GO3、新建索引createindexindex_nameontable_nameview_name(colume_name[1,,,n])index_name是索引名称table_nameview_name是表的名称或视图名称colume_name是表或视图的哪些列上建索引4、如何对一批记录，加上顺序递增的行号呢？方法一selectt.,px=(selectcount(1)fromtbwhereid60breakelsecontinueend10、按姓氏笔画排序方法：CollateChinese_PRC_Stroke_ci_asselectfromemployeeorderbyEmpNameCollateChinese_PRC_Stroke_ci_as11、字符截取函数a为字符串，n为数字,\'xxx\'字符串中包含的XXX字符left(a,n)截取字符串a从左边开始n位数。right(a,n)截取字符串a从右边开始n位数。substring(a,\'xxx\',n)从字符串的\'xxx\'位置开始截取n位数len(a)字符串a的位数patindex(\'%xxx%\',a)模糊查询xxx在字符串a的第一次出现的位置，还可以写成\'%xxx\',\'xxx%\',\'xxx\'等charindex(\'xxx\',a)xxx在字符串a中的位置replace(a,\'xxx\',需要替换成xxx)全部替换，将字符a中的xxx全部替换成xxxstuff(a,开始位置，n,需要替换成xxx)指定替换位置，将字符串a从开始位置到n位数替换成xxxreverse()反转函数列：Declare@S1varchar(100)Select@S1=\'http://www.163.com\'SelectSubstring(@S1,CHARINDEX(\'www\',@S1)+1,Len(@S1))SelectSubstring(@S1,PATINDEX(\'%www%\',@S1)+1,Len(@S1))SelectREPLACE(@S1,\'http://\',\'\')SelectSTUFF(@S1,CHARINDEX(\'http://\',@S1),Len(\'http://\'),\'\')selectright(@S1,charindex(\'\\\',REVERSE(@S1))-1)12、CAST/CONVERT类型转换函数CAST(expressionASdata_type[(length)])CONVERT(data_type[(length)],expression[,style])expression任何有效的表达式；data_type目标数据类型。这包括xml、bigint和sql_variant。不能使用别名数据类型。length指定目标数据类型长度的可选整数。默认值为30。style指定CONVERT函数如何转换expression的整数表达式。如果style为NULL，则返回NULL。该范围是由data_type确定的（日期详细请见百度sql日期的转换）常用转换：SelectCONVERT(varchar(100),GETDATE(),20)返回2014-05-1610:57:47SelectCONVERT(varchar(100),GETDATE(),23)返回2014-05-1613、随机函数rand()在查询分析器中执行：selectrand()，可以看到结果会是类似于这样的随机小数：0.36361513486289558。如果是返回整数：方法一：A:selectfloor(rand()N)---生成的数是类似这样的：12.0B:selectcast(floor(rand()N)asint)---生成的数是类似这样的：12方法二：A：selectceiling(rand()N)---生成的数是类似这样的：12.0B：selectcast(ceiling(rand()N)asint)---生成的数是类似这样的：12方法一和方法二的区别：floor()返回小于或等于所给数字表达式的最大整数ceiling()返回大于或等于所给数字表达式的最小整数方法一取值范围：0至N-1之间方法二取值范围：1至N之间14、DATEADD(datepart,number,date)日期函数date参数是合法的日期表达式。number是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。DATEDIFF()函数返回两个日期之间的天数。DATEDIFF(datepart,startdate,enddate)startdate和enddate参数是合法的日期表达式。datepart参数可以是下列的值：（祥见列8）计算一个月的第一天和最后一天：这是计算一个月第一天的SQL脚本：SELECTDATEADD(mm,DATEDIFF(mm,0,getdate()),0)--当月的第一天SELECTDATEADD(mm,DATEDIFF(mm,0,getdate())-1,0)--上个月的第一天在SqlServer中时间是精确到3毫秒。这就是为什么我需要减去3毫秒来获得我要的日期和时间。SELECTDATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))--当月的最后一天SELECTDATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate()),0))--上个月的最后一天这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数，然后把它加到“1900-01-01”上来获得特殊的日期，0代表“1900-01-01”。15、round(n,n)保留小数函数。16WAITFORDELAY\'00:00:10\'意思是等待10秒后再执行后面的代码。17大小写转换函数：将大写转换成小写LOWER()，将小写转换成大写UPPER()18、更新索引脚本：DECLARE@table_namesysnameDECLARE@index_namesysnameDECLARE@syntaxsysnameDECLAREROY_tableCURSORFORSELECTnameFROMsysobjectswherextype=\'u\'OPENROY_tableFETCHNEXTFROMROY_tableINTO@table_nameWHILE@@FETCH_STATUS=0BEGINDECLAREROY_indexCURSORFORselectsysindexes.namefromsysindexes,sysobjectswheresysobjects.id=sysindexes.idandsysobjects.name=@table_nameandkeycnt>0OPENROY_indexFETCHNEXTFROMROY_indexINTO@index_nameWHILE@@FETCH_STATUS=0BEGINSELECT@syntax=\'DBCCINDEXDEFRAG(0,\'+@table_name+\',\'+@index_name+\')\'EXEC(@syntax)PRINT\'数据表\'+@table_name+\'索引\'++@index_name+\'碎片整理完成\'FETCHNEXTFROMROY_indexINTO@index_nameENDCLOSEROY_indexDEALLOCATEROY_indexFETCHNEXTFROMROY_tableINTO@table_nameENDCLOSEROY_tableDEALLOCATEROY_table注：--DBCCSHOWCONTIG：显示指定的表或视图的数据和索引的碎片信息。--DBCCINDEXDEFRAG：指定表或视图的索引碎片整理。19、常用函数casewhen……else……end用法Case具有两种格式。简单Case函数和Case搜索函数。--简单Case函数CASEsexWHEN\'1\'THEN\'男\'WHEN\'2\'THEN\'女\'ELSE\'其他\'END--Case搜索函数CASEWHENsex=\'1\'THEN\'男\'WHENsex=\'2\'THEN\'女\'ELSE\'其他\'END这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。20、关于sqlserver的死锁问题。SQLServer死锁的查询方法:execp_lockinfo0,0---显示死锁的进程,不显示正常的进程execmaster.dbo.p_lockinfo1,0---杀死死锁的进程,不显示正常的进程.SQLServer死锁的解除方法:Createprocp_lockinfo@kill_lock_spidbit=1,--是否杀掉死锁的进程,1杀掉,0仅显示@show_spid_if_nolockbit=1--如果没有死锁的进程,是否显示正常进程信息,1显示,0不显示asdeclare@countint,@snvarchar(1000),@iintselectid=identity(int,1,1),标志,进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,登陆时间=login_time,打开事务数=open_tran,进程状态=status,工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,域名=nt_domain,网卡地址=net_addressinto#tfrom(select标志=\'死锁的进程\',spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address,s1=a.spid,s2=0frommaster.dbo.sysprocessesajoin(selectblockedfrommaster.dbo.sysprocessesgroupbyblocked)bona.spid=b.blockedwherea.blocked=0unionallselect\'_牺牲品_>\',spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address,s1=blocked,s2=1frommaster.dbo.sysprocessesawhereblocked<>0)aorderbys1,s2select@count=@@rowcount,@i=1if@count=0and@show_spid_if_nolock=1begininsert#tselect标志=\'正常的进程\',spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_addressfrommaster.dbo.sysprocessesset@count=@@rowcountendif@count>0begincreatetable#t1(idintidentity(1,1),anvarchar(30),bInt,EventInfonvarchar(255))if@kill_lock_spid=1begindeclare@spidvarchar(10),@标志varchar(10)while@i<=@countbeginselect@spid=进程ID,@标志=标志from#twhereid=@iinsert#t1exec(\'dbccinputbuffer(@spid)\')if@标志=\'死锁的进程\'exec(\'kill\'+@spid)set@i=@i+1endendelsebeginwhile@i<=@countbeginselect@s=\'dbccinputbuffer(\'+cast(进程IDasvarchar)+\')\'from#twhereid=@iinsert#t1exec(@s)set@i=@i+1endselecta.,进程的SQL语句=b.EventInfofrom#tajoin#t1bona.id=b.idendend认真研究以上脚本。（我反正疯过）21、利用游标修改某字段某部分值declare@svarchar(300)declare@idvarchar(100)declarezf_thcursorforselectssql,idfromFieldsTablewhereDisplayFieldNamelike\'%薪资%\'openzf_thfetchnextfromzf_thinto@s,@idwhile@@FETCH_STATUS=0beginifcharindex(\'FROMdbo.EmployeeepLEFTOUTERJOINdbo.payinputecpONep.sid=ecp.EmpNosidandecp.empnosid=\',@s)>0--修改@s中含有\'FROMdbo.EmployeeepLEFTOUTERJOINdbo.payinputecpON---ep.sid=ecp.EmpNosidandecp.empnosid=\'这样一句话的的ssql,将其中的一个and---替换----成whereset@s=replace(@s,\'FROMdbo.EmployeeepLEFTOUTERJOINdbo.payinputecpONep.sid=ecp.EmpNosidandecp.empnosid=\',\'FROMdbo.EmployeeepLEFTOUTERJOINdbo.payinputecpONep.sid=ecp.EmpNosidwhereecp.empnosid=\')updateFieldsTablesetsSQL=@swhereid=@idfetchnextfromzf_thinto@s,@idendclosezf_thdeallocatezf_th',)