SQL记录

取得字段说明:

SELECT

    [Table Name] = OBJECT_NAME(c.object_id),

    [Column Name] = c.name,

    [Description] = ex.value

FROM

    sys.columnsc

LEFT OUTERJOIN

    sys.extended_propertiesex

ON

    ex.major_id= c.object_id

    AND ex.minor_id= c.column_id

    AND ex.name = 'MS_Description'

WHERE

    OBJECTPROPERTY(c.object_id,'IsMsShipped')=0

    -- AND OBJECT_NAME(c.object_id) = 'your_table'

ORDER

BY OBJECT_NAME(c.object_id),c.column_id

 

取得表说明：

SELECT 表名= casewhena.colorder= 1 then d.name

                  else '' end,

       表说明 = casewhena.colorder= 1 then isnull(f.value,'')

                    else '' end

FROM syscolumnsa

       inner join sysobjects d

          on a.id = d.id

             and d.xtype = 'U'

             and d.name <> 'sys.extended_properties'

       left join sys.extended_properties  f

         on a.id = f.major_id

            and f.minor_id= 0

Where (casewhena.colorder= 1 then d.name else '' end)<>''

增加字段说明

EXEC sp_addextendedproperty 
    'MS_Description', 
    'some description', 
    'user', 
     dbo, 
    'table', 
     table_name, 
    'column', 
     column_name

·        Some Description，是要增加的说明内容

·        table_name,是表名

·        column_name ,是字段名

增加表的说明

EXEC sp_addextendedproperty 
    'MS_Description', 
    'some description', 
    'user', 
     dbo, 
    'table', 
     table_name

--更新表中列a1的描述：

EXEC sp_updateextendedproperty'MS_Description','字段1','user',dbo,'table','表','column',a1

--删除表中列a1的描述：

EXEC sp_dropextendedproperty'MS_Description','user',dbo,'table','表','column',a1

取得字段所在的表名

SELECT sb.name

FROM syscolumnss JOIN sysobjects sb ONs.id=sb.id

WHERE  s.name='字段名'

查询表总记录数

SELECT ROWS FROMsysindexes WHERE id = OBJECT_ID('dbo.TESTINFO') AND indid < 2

求余：%

返回小于或等于指定数值表达式的最大整数：FLOOR

返回大于或等于指定数值表达式的最小整数：ceiling

round是四舍五入

查看sql server 2000的版本

法一：在查询分析器中执行

select @@version

print @@version

法二：企业管理器信息

打开企业管理器→SQL SERVER组→（local）window NT→属性

产品：personal是个人版本，Enterprise是企业版

产品版本：8.00.2039(sp4)

法三：软件版本

C:\Program Files\MicrosoftSQL Server\MSSQL\Binn\sqlservr.exe

点击鼠标右键查看版本，类似8.00.2039就是安装的sql server版本

SQL SERVER打补丁后的版本号VersionNumber Service Pack
8.00.194 Microsoft SQL Server 2000
8.00.384 Microsoft SQL Server 2000 SP1
8.00.532 Microsoft SQL Server 2000 SP2
8.00.760 Microsoft SQL Server 2000 SP3
8.00.818 Microsoft SQL Server 2000 SP3 w/ Cumulative Patch MS03-031
8.00.2039 Microsoft SQL Server 2000 SP4

sql server 2000数据对象的系统范围，在实际应用过程中可以根据需要作适当的改变。
1、数据库：32767个数据库，最小为1MB，最大为1TB。
2、表：每个数据库最多有20亿个表。
3、列：每个表最多1024列，每列的最大字节数为8060(文本和图像列除外)。
4、索引：每个表有一个聚集索引，249个非聚集索引。一个复合索引最多有16个索引关键字。
5、触发器：每个表最多有3个触发器，分别用于插入、修改和删除等操作。
6、存储过程：一个存储过程可以有1024个参数和最多32级嵌套。
7、用户连接：32767个。
8、锁定及打开的对象：20亿个。

 

select top 10 * fromdbo.sysprocesses order by cpu desc--CPU累计使用时间前10

 

--Sql模拟数组

create table #temp

(

ss varchar(200) not null

)

declare @str varchar(200)

declare @curr int

declare @prev int

set @str='aaa,bb,c,d,e,ffffff'

set @curr=1

set @prev=1

 

while @prev < len(@str)

begin

set @curr=charindex(',',@str,@prev)

if @curr>@prev

insert #temp select substring(@str,@prev,@curr-@prev)

else

begin

insert #temp selectsubstring(@str,@prev,len(@str)-@prev+1)

break

end

set @prev=@curr+1

end

 

select * from #temp

模糊匹配

Like关键字用来模糊比较字符串，百分号%匹配0个或多个字符，下划线'_'匹配一个字符。还可以用方括号只匹配指定的某几个字符，例如[a-z]，匹配小写字面。方括号中加入^表示匹配除指定字符以外的字符。

收集的SQL语句

select * from asrock..sysobjects where xtype='u' andstatus>=0--查询指定数据库的表总数

select * from asrock..sysobjects where xtype='u' andstatus>=0 and name = 'asrock_mo'--查询数据库(asrock)里有没有表asrock_mo

delete fromdbo.WarningLog--删除数据未释放空间

truncate table表名--删除数据并释放空间

USE masterSELECT * FROM SYSDATABASES--查询服务器上数据库总数

insert into表名(字段，字段)select 字段，字段from 表名 where 条件

select @@rowcount  返回上一条SQL语句所影响的行数

select @@version  返回当前SQLServer服务器的安装日期、版本、处理器类型

select *from sysfiles ――查看数据库数据文件存放路径

select top 10percent * from 表名--返回表中%的记录

--if…..else       begin…..end的使用:

use pubs

go

declare @message varchar(255)--声明局部变量

--if语句逻辑判断

if exists(selectprice

    from titles

    where title_idlike 'BU%'and price <$20)

    --begin语句块

    begin

     set@message= '下面是一些价格适中的书籍：'--赋值

     print@message

     selecttitle

     fromtitles

     whereprice< $20

    end

else   begin

     set@message= '这里的书都比较贵'

     print@message

     selecttitle

     fromtitles

     wheretitle_id

     like'BU%'and price < 10

    end

go

case的使用:

use pubs

go

select '地址'=

    case state

     when'CA'then '来自加利福尼亚'

     when'KS'then '来自堪萨斯州'

     when'MI'then '来自马萨诸塞州'

     when'IN'then '来自印第安纳州'

     when'TN'then '来自田纳西州'

     else'来自其他州'

    end

    ,'作者'= au_fname + '.' + au_lname

from authors

order bystate

go

 

update 表名 set 字段 = case when字段= '3' then'a' else'b' end where 条件

Exec sp_spaceused--检查当前数据库的使用情况

select *from sysobjects where xtype ='p'--查看用户定义的存储过程

exec sp_recompileprListOrders--强制重编译存储过程

exec sp_recompileOrders  --Orders是表名

sp_depends material_to_machine--查看某个表被存储过程引用

sp_helptext 存储过程名--查看某个存储过程的内容

――begintran表示开始事务

――committran表示提交事务

――rollbacktran表示回滚事物

alter tableasrock_po_90 add po_week varchar(5)--增加字段

alter tableasrock_po_90 dropcolumn po_week--删除字段

alter tableasrock_po_90 altercolumn column_namenew_data_type--修改字段类型

修改字段名(添加新字段，旧字段的值放到新字段，删除旧字段)

sp_rename '表名.原列名','修改后的列名','column'

或

alter tabletable_name

add new_field_nameint

go

update table_name

set new_field_name= old_field_name

go

alter tabletable_name

drop columnold_field_name

go

 

索引的种类：

聚集索引:根据数据行的键值在表或视图中排序和存储这些数据行.

非聚集索引:具有独立于数据行的结构.

唯一索引:确保索引键不包含重复的值.

create index索引名 on 表名(字段１,字段,字段３)--非聚集索引

create indexhsz on dbo.asrock_E80_lend(mo_item,lend_time,op_name)

create uniqueclustered index 索引名 on 表名(字段)--惟一聚集索引

create uniqueclustered index hsz on dbo.asrock_E80_lend(id)

create index索引名 on 表名(字段１,字段２)--复合索引

sp_helpindex '表名' --查看一个表的索引

sp_helpindex 'dbo.asrock_E80_lend'

sp_rename '表名.索引名','新索引名','index'--更改索引名

sp_rename 'dbo.asrock_E80_lend.hsz','abc','index'

drop index表名.索引名--删除索引

drop index dbo.asrock_E80_lend.abc

组合主键：

alter table dbo.asrock_isn_ecnadd constraint pk_name primary key(col1,col2)

时间格式转换

SQL Server中文版的默认的日期字段datetime格式是yyyy-mm-ddThh:mm:ss.mmm

例如:

select getdate()

2004-09-12 11:06:08.177

select CONVERT(varchar,getdate(), 120)

2004-09-12 11:06:08

select replace(replace(replace(CONVERT(varchar,getdate(), 120),'-',''),' ',''),':','')

20040912110608

select CONVERT(varchar(12), getdate(), 111)

2004/09/12

select CONVERT(varchar(12), getdate(), 112)

20040912

select CONVERT(varchar(12), getdate(), 102)

2004.09.12

select CONVERT(varchar(12), getdate(), 101)

09/12/2004

select CONVERT(varchar(12), getdate(), 103)

12/09/2004

select CONVERT(varchar(12), getdate(), 104)

12.09.2004

select CONVERT(varchar(12), getdate(), 105)

12-09-2004

select CONVERT(varchar(12), getdate(), 106)

12 09 2004

select CONVERT(varchar(12), getdate(), 107)

09 12, 2004

select CONVERT(varchar(12), getdate(), 108)

11:06:08

select CONVERT(varchar(12), getdate(), 109)

09 12 2004 1

select CONVERT(varchar(12), getdate(), 110)

09-12-2004

select CONVERT(varchar(12), getdate(), 113)

12 09 2004 1

select CONVERT(varchar(12), getdate(), 114)

11:06:08.177

日期与时间函数

getdate()当前系统日期、时间

dateadd  在向指定日期加上一段时间的基础上，返回新的 datetime 值

   例如：向日期加上天

   select dateadd(day,2,'2004-10-15') --返回：-10-17 00:00:00.000

datediff 返回跨两个指定日期的日期和时间边界数。

   select datediff(day,'2004-09-01','2004-09-18')  --返回：

datepart 返回代表指定日期的指定日期部分的整数。

  SELECT DATEPART(month,'2004-10-15')  --返回10  

day(),month(),year()--可以与datepart对照一下

datediff(interval,date1,date2)以interval指定的方式，返回date2与date1之间的差值date2-date1

DateAdd(interval,number,date)以interval指定的方式，加上number之后的日期

DatePart(interval,date)返回日期date中，interval指定部分所对应的整数值

DateName(interval,date)返回日期date中，interval指定部分所对应的字符串名称

    select datename(week,'2018-2-7')返回本年第几周

    SELECT datename(weekday,'2004-10-15')  --返回：星期五

参数interval的设定值如下：

值  缩写 （SqlServer）(Access和 ASP)说明

Year Yy yyyy 年 1753~ 9999

Quarter Qq q   季 1~ 4

Month Mm m   月 ~ 12

Day ofyear Dy y  一年的日数,一年中的第几日 1-366

Day Ddd   日，-31

Weekday Dw w 一周的日数，一周中的第几日 1-7

Week Wkww  周，一年中的第几周 0~ 51

Hour Hhh   时 ~ 23

Minute Min  分钟 ~ 59

Second Sss 秒 0 ~ 59

Millisecond Ms - 毫秒 0 ~ 999

 

在查询分析器中执行带输入输出参数的存储过程

举例：declare@outtmp varchar(100),@outsumcountint

exec asrock_ADD_QA_BoxBarcode"41M0XD020931","41M0XD020950","ITPC370","ED302","HM1+3C0087/E90-3C0979/01","20",@outtmpoutput,@outsumcountoutput

select @outtmpas 结果,@outsumcountas 工单已存

再举例创建存储过程如下:

    CREATE PROCEDURE [pro_Notice]

(

    @id int,

    @Title varchar(50)output,

    @Content nvarchar(3000)output,

    @Time datetime output

) AS

      select @Title=Title,@Time=Addtime,@Content=Content

      from tbl_Notice

     where id=@id

GO

在查询分析器中输入：

    declare @Title varchar(80),@Contentvarchar(3000),@Timedatetime

    exec pro_Notice"1",@Titleoutput,@Contentoutput,@Timeoutput

select @Title as Title,@Contentas Content,@Time as Time

执行储存过程超时

     在执行存储过程时，我们常遇到执行超时的情况。

     如果是因为要处理的数据过多，修改流程复杂等原因的话，如以用以下方法解决：

     在存储过程的处理工作中加上事务管理:

    SET   TRANSACTION   ISOLATION   LEVEL   REPEATABLE   READ     --->要這行 

     BEGIN   TRAN   

          /* 这里是程序处理代码段*/

    commit   transaction           

    QuitWithRollback:  

    IF   (@@TRANCOUNT  >  0)    

    BEGIN  

        ROLLBACK   TRANSACTION    

END

以下是事务相关的知识:

BEGIN TRANSACTION--开始事务

DECLARE @errorSun INT --定义错误计数器

SET @errorSun=0--没错为

UPDATE aSET id=232WHERE a=1--事务操作SQL语句

SET @errorSun=@errorSun+@@ERROR--累计是否有错

UPDATE aaSET id=2WHERE a=1--事务操作SQL语句

SET @errorSun=@errorSun+@@ERROR--累计是否有错

IF @errorSun<>0BEGIN PRINT '有错误，回滚'

ROLLBACK TRANSACTION--事务回滚语句

END ELSEBEGIN PRINT '成功，提交'

COMMIT TRANSACTION--事务提交语句

END

1.什么是事务：事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时事务是做为最小的控制单元来使用的。他包含的所有数据库操作命令作为一个整体一起向系提交或撤消，这一组数据库操作命令要么都执行，要么都不执行。

2.事务的语句　开始事物：BEGINTRANSACTION 　提交事物：COMMITTRANSACTION 　回滚事务：ROLLBACKTRANSACTION

3.事务的个属性

①原子性(Atomicity)：事务中的所有元素作为一个整体提交或回滚，事务的个元素是不可分的，事务是一个完整操作。

②一致性(Consistemcy)：事物完成时，数据必须是一致的，也就是说，和事物开始之前，数据存储中的数据处于一致状态。保证数据的无损。

③隔离性(Isolation)：对数据进行修改的多个事务是彼此隔离的。这表明事务必须是独立的，不应该以任何方式以来于或影响其他事务。

④持久性(Durability)：事务完成之后，它对于系统的影响是永久的，该修改即使出现系统故障也将一直保留，真实的修改了数据库

4.事务的保存点 SAVE TRANSACTION保存点名称--自定义保存点的名称和位置ROLLBACK TRANSACTION保存点名称--回滚到自定义的保存点二事例所谓事务是指一组逻辑操作单元，它使数据从一种状态变换到另一种状态。包括四个特性:

1、原子性 就是事务应作为一个工作单元,事务处理完成，所有的工作要么都在数据库中保存下来，要么完全回滚，全部不保留

2、一致性 事务完成或者撤销后，都应该处于一致的状态

3、隔离性 多个事务同时进行，它们之间应该互不干扰.应该防止一个事务处理其他事务也要修改的数据时，不合理的存取和不完整的读取数据

4、持久性 事务提交以后，所做的工作就被永久的保存下来示例：创建一个存储过程,向两个表中同时插入数据

Create procRegisterUser

(

@usrName varchar(30),

@usrPasswd varchar(30),

@age int,@sexvarchar(10),

@PhoneNum varchar(20),

@Address varchar(50))

as begin

begin tran

insert intouserinfo(userName,userPasswd)

values(@usrName,@usrPasswd)

if @@error<>0

begin rollbacktran

return -1

end

insert intouserdoc(userName,age,sex,PhoneNumber,Address)

values(@Usrname,@age,@sex,@PhoneNum,@Address)

if @@error<>0

begin rollbacktran

return -1

end

commit tran

return 0

end

    事务的分类按事务的启动与执行方式，可以将事务分为类:

显示事务 也称之为用户定义或用户指定的事务，即可以显式地定义启动和结束的事务。分布式事务属于显示事务自动提交事务默认事务管理模式。如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句。

隐性事务当连接以此模式进行操作时，sql将在提交或回滚当前事务后自动启动新事务。无须描述事务的开始，只需提交或回滚每个事务。它生成连续的事务链。

一、显示事务通过begintransacton、committransaction、commitwork、rollback transaction或rollbackwork等语句完成。

1、启动事务格式:begintran 事务名或变量 with mark描述

2、结束事务格式:committran 事务名或变量(事务名与begintran中的事务名一致或commitwork 但此没有参数

3、回滚事务 rollbacktran 事务名或变量 | savepoint_name|savepoint_variable或rollbackwork 说明:清除自事务的起点或到某个保存点所做的所有数据修改

4、在事务内设置保存点格式:savetran savepoint_name| savepoint_variable示例:

use bookdb

go

begin tranmytran

insert intobook values(9,"windows2000',1,22,'出版社')

save tranmysave

delete bookwhere book_id=9

rollback tranmysave

commit tran

go

select * frombook

go

可以知道，上面的语句执行后，在book中插入了一笔记录，而并没有删除。因为使用rollbacktran mysave语句将操作回滚到了删除前的保存点处。

5、标记事务格式:withmark例：使用数据库标记将日志恢复到预定义时间点的语句在事务日志中置入一个标记。请注意，被标记的事务至少须提交一个更新，以标记该日志。

BEGIN TRANMyMark WITH MARK UPDATE pubs.dbo.LastLogMark SET MarkTime = GETDATE() COMMITTRAN MyMark按照您常用的方法备份事务日志。

BACKUP LOG pubsTO DISK='C:/Backups/Fullbackup.bak' WITH INIT现在您可以将数据库恢复至日志标记点。首先恢复数据库，并使其为接受日志恢复做好准备。RESTORE DATABASE pubs FROMDISK=N'C:/Backups/Fullbackup.bak' WITH NORECOVERY现在将日志恢复至包含该标记的时间点，并使其可供使用。请注意，STOPAT在数据库正在执行大容量日志时禁止执行。RESTORE LOG pubs FROMDISK=N'C:/Backups/Logbackup.bak' WITH RECOVERY, STOPAT='02/11/2002 17:35:00'

6、不能用于事务的操作创建数据库create database修改数据库alter database删除数据库drop database恢复数据库restoredatabase加载数据库load database备份日志文件backup log 恢复日志文件restorelog 更新统计数据update statitics 授权操作grant复制事务日志dumptran磁盘初始化disk init 更新使用sp_configure后的系统配置reconfigure

二、自动提交事务sql连接在begin tran语句启动显式事务，或隐性事务模式设置为打开之前，将以自动提交模式进行操作。当提交或回滚显式事务，或者关闭隐性事务模式时，将返回到自动提交模式。示例:由于编译错误，使得三个insert都没执行

use test

go

create tabletestback(cola int primary key ,colb char(3))

go

insert intotestback values(1,'aaa')

insert intotestback values(2,'bbb')

insert intotestback value(3,'ccc')

go

select * fromtestback

go 没有任何结果返回

三、隐式事务通过API函数或Transact-SQL SETIMPLICIT_TRANSACTIONS ON语句，将隐性事务模式设置为打开。下一个语句自动启动一个新事务。当该事务完成时，再下一个Transact-SQL语句又将启动一个新事务。当有大量的DDL和DML命令执行时会自动开始，并一直保持到用户明确提交为止，切换隐式事务可以用SET IMPLICIT_TRANSACTIONS为连接设置隐性事务模式.当设置为ON时，SETIMPLICIT_TRANSACTIONS将连接设置为隐性事务模式。当设置为OFF时，则使连接返回到自动提交事务模式语句包括:alter table insert open create delete revoke drop select fetch truncate tablegrant update示例: 下面使用显式与隐式事务。它使用@@tracount函数演示打开的事务与关闭的事务:

use test

go

set nocount on

create tablet1(a int)

go

insert into t1values(1)

go

print '使用显式事务'

begin tran

insert into t1values(2)

print '事务外的事务数目:'+cast(@@trancountas char(5))

commint tran

print '事务外的事务数目:'+cast(@@trancountas char(5))

go

print

go

setimplicit_transactions on go print '使用隐式事务'

go

insert into t1values*4)

print'事务内的事务数目:'+cast(@@trancountas char(5))

commint tranprint'事务外的事务数目:'+cast(@@trancount as char(5))

go

执行结果: 使用显示事务事务内的事务数目:2 事务外的事务数目:1 使用隐式事务事务内的事务数目:1事务外的事务数目:0

四、分布式事务跨越两个或多个数据库的单个sql server中的事务就是分布式事务。与本地事务区别:必须由事务管理器管理，以尽量避免出现因网络故障而导致一个事务由某些资源管理器成功提交，但由另一些资源管理器回滚的情况。sql server 可以由DTcmicrosoft distributed transaction coordinator来支持处理分布式事务,可以使用BEgindistributed transaction命令启动一个分布式事务处理分二阶段:

A 准备阶段B提交阶段

执行教程:

1、sql脚本或应用程序连接执行启动分布式事务的sql语句

2、执行该语句的sql在为事务中的主控服务器

3、脚本或应用程序对链接的服务器执行分布式查询，或对远程服务器执行远程存储过程。

4、当执行了分布式查询或远程过程调用后，主控服务器将自动调用msdtc以便登记分布式事务中链接的服务器和远程服务器

5、当脚本或应用程序发出commit或rollback语句时，主控sql将调用msdtc管理两阶段提交过程，或者通知链接的服务器和远程服务器回滚其事务。

--查询第20到第39条记录，ID可能不连接

select top 10* from A where IDnot in(selecttop 19 ID from A)--效率低

select top 10* from (selecttop 39 * from A) as t orderby t.IDdesc--效率好一点

select *from (selectrow_number() over(orderby ID)as 'sequence',A.*from A)as t

where t.sequencebetween 20 and 39--sqlserver2005及以上版本才可用

SQL Server 的数据类型

VarChar(50)

最多放个非UNICODE字符，但如果你输入的是UNICODE字符（如中文字符），则最多只能放个UNICODE字符（因为一个UNICODE字符要占两个字节）。如果这个字段主要存放的是中文，那么最好改用nvarchar(50)，这样就可以最多保存个UNICODE字符了。

 

访问数据库可以用“数据访问对象”或者“数据访问API(应用程序接口)”,常用的对象有：ADO(ActiveXDatabase Objects),RDO(RemoteDatabase Object),DB-Library,ODBC,OLEDB和DAO(DatabaseAccess Objects)

建表删表建索引删索引

Create table表名（字段名 类型，字段名 类型，…）//创建表

例：createtable ”stu.dbf”(s_namechar(10),s_agenumeric(10,2),s_addrchar(40))

当表名包含SQL语法关键字时，要用引号引起来

Drop table表名//删除表

Alter table表名 add 字段名 类型，…//修改表，加字段

Alter table表名 drop 字段名，…//删除字段

例：altertable “stu.dbf”adds_class char(5)

Create index索引名 on 表名（字段，…）//创建索引

例：createindex sid on “stu.dbf”(s_name)

Drop index表名．索引名 //删除索引

循环更新

DECLARE @SSNRight INT,@ISNRightINT,@SSNVARCHAR(15),@ISNVARCHAR(15)

SET @ISNRight= 113

SET @SSNRight= 381

WHILE @SSNRight<= 460

 BEGIN

  SET @ISNRight= @ISNRight + 1

  SET @ISN= 'E8T-20140307'+CONVERT(VARCHAR,@ISNRight)

  IF LEN(CONVERT(VARCHAR,@SSNRight))= 1 BEGIN

   SET @SSN= '43M0X2'+'00000'+CONVERT(VARCHAR,@SSNRight)

  END

  ELSE IFLEN(CONVERT(VARCHAR,@SSNRight))= 2 BEGIN

   SET @SSN= '43M0X2'+'0000'+CONVERT(VARCHAR,@SSNRight)

  END

  ELSE IFLEN(CONVERT(VARCHAR,@SSNRight))= 3 BEGIN

   SET @SSN= '43M0X2'+'000'+CONVERT(VARCHAR,@SSNRight)

  END

  ELSE IFLEN(CONVERT(VARCHAR,@SSNRight))= 4 BEGIN

   SET @SSN= '43M0X2'+'00'+CONVERT(VARCHAR,@SSNRight)

  END

  ELSE IFLEN(CONVERT(VARCHAR,@SSNRight))= 5 BEGIN

   SET @SSN= '43M0X2'+'0'+CONVERT(VARCHAR,@SSNRight)

  END

  ELSE IFLEN(CONVERT(VARCHAR,@SSNRight))= 6 BEGIN

   SET @SSN= '43M0X2'+CONVERT(VARCHAR,@SSNRight)

  END

  UPDATE dbo.asrock_isn_infoSET isn_isn = @ISN WHEREisn_ssn = @SSN and ISNULL(del,'')<>'1'

  SET @SSNRight= @SSNRight + 1

END

exists用法

有则Update，否则Insert

if exists(select* from tbxx where id=xxx)

   update tbxx set fld1='xxxxx'where id=xxx

else

   insert tbxx(fld1)values ('xxxxx')

不存在就插入，存在就不变

insert intotask_fav(taskid,opid,opdate)

select @taskid,@opid,getdate()

where notexists(selecttaskid from task_fav where taskid=@taskidand opid=@opid)

死锁处理

exec p_lockinfo1,1--杀掉死锁的进程

exec p_lockinfo0,1--显示死锁的进程

 

create   proc   p_lockinfo  

@kill_lock_spid   bit=0, --是否杀掉死锁的进程,1  杀掉,   0   仅显示 

@show_spid_if_nolock   bit=0 --如果没有死锁的进程,是否显示正常进程信息,1  显示,0   不显示 

as  

declare   @count   int,@s  nvarchar(1000),@i  int  

select   id=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_address  

into   #t  from(  

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=0  

from   master..sysprocesses  a   join  (  

select   blocked   from   master..sysprocesses  group   by   blocked  

)b  on   a.spid=b.blocked  where   a.blocked=0  

union   all  

select   '阻塞的进程',  

spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,  

status,hostname,program_name,hostprocess,nt_domain,net_address,  

s1=blocked,s2=1  

from   master..sysprocesses  a   where   blocked<>0  

)a  order   by   s1,s2 

 

select   @count=@@rowcount,@i=1 

 

if   @count=0  and   @show_spid_if_nolock=1  

begin  

insert   #t  

select   标志='正常的进程',  

spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,  

open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address  

from   master..sysprocesses  

set   @count=@@rowcount  

end 

 

if   @count>0  

begin  

create   table   #t1(id  int   identity(1,1),a  nvarchar(30),b  Int,EventInfo  nvarchar(2048))  

if   @kill_lock_spid=1  

begin  

declare   @spid   varchar(10),@标志  varchar(10)  

while   @i<=@count  

begin  

select   @spid=进程ID,@标志=标志  from   #t   where   id=@i  

insert   #t1   exec('dbcc   inputbuffer('+@spid+')')  

if   @标志='死锁的进程'  exec('kill   '+@spid)  

set   @i=@i+1  

end  

end  

else  

while   @i<=@count  

begin  

select   @s='dbcc   inputbuffer('+cast(进程ID  as   varchar)+')'  from   #t   where   id=@i  

insert   #t1   exec(@s)  

set   @i=@i+1  

end  

select   a.*,进程的SQL语句=b.EventInfo  

from   #t  a   join   #t1   b   on  a.id=b.id  

end  

GO

 

 

--下面方法类似

exec sp_who_lock

kill 55--杀掉

 

CREATE proceduresp_who_lock 

as    

begin    

   declare @spid int    

   declare @blk int    

   declare @count int    

   declare @index int    

   declare @lock tinyint    

   set @lock=0    

   create table #temp_who_lock    

 (    

  id intidentity(1,1),    

  spid int,    

  blk int    

 )    

 if @@error<>0return @@error    

 insert into #temp_who_lock(spid,blk)    

 select 0 ,blocked     

 from (select* from master..sysprocesseswhere blocked>0)a    

 where notexists(select * from  master..sysprocesseswhere a.blocked=spid and blocked>0)    

 union selectspid,blockedfrom  master..sysprocesseswhere blocked>0    

 if @@error<>0return @@error    

 select @count=count(*),@index=1from #temp_who_lock    

 if @@error<>0return @@error    

 if @count=0    

 begin    

  select '没有阻塞和死锁信息'    

  return 0    

 end    

 while @index<=@count     

 begin    

  if exists(select 1from #temp_who_locka where id>@index and exists(select 1from #temp_who_lockwhere id<=@indexand a.blk=spid))    

  begin    

   set @lock=1    

   select @spid=spid,@blk=blkfrom #temp_who_lockwhere id=@index    

   select '引起数据库死锁的是: '+CAST(@spidAS VARCHAR(10))+ '进程号,其执行的SQL语法如下'    

   select  @spid, @blk  

   dbcc inputbuffer(@spid)    

   dbcc inputbuffer(@blk)    

  end    

  set @index=@index+1    

 end    

 if @lock=0     

 begin    

  set @index=1    

  while @index<=@count     

  begin    

   select @spid=spid,@blk=blkfrom #temp_who_lockwhere id=@index    

   if @spid=0    

    select '引起阻塞的是:'+cast(@blkas varchar(10))+'进程号,其执行的SQL语法如下'    

   else     

    select '进程号SPID：'+CAST(@spidAS VARCHAR(10))+'被'+ '进程号SPID：'+CAST(@blkAS VARCHAR(10))+'阻塞,其当前进程执行的SQL语法如下'    

   dbcc inputbuffer(@spid)  

   dbcc inputbuffer(@blk)    

   set @index=@index+1    

  end    

 end    

 drop table#temp_who_lock    

 return 0    

end          

GO

--替换字段中某部分值(replace)

update dbo.asrock_isn_info

set createdat=

convert(varchar(19),replace(convert(varchar(19),createdat,120),'2014-12-09','2014-03-07'),121)

where idin(1,2,3,4)]