表、触发器、事务、存储过程



下面以示例的方式，列出数据库中的表，触发器，事务，存储过程

 表-----Table

 1.创建表

Ifexists(select * from sysobjects where name='MyTable' and Type ='u')

 drop Table MyTable

go

  Create Table MyTable

  (

      UserID int identity(1,1) primary key, --identity:自动增长1，并设置为主键

      UserName nvarchar(10) foreign key references MyTable_1(UserName) ,

      [Password] varchar(10) not null,

      LoginTime DateTimeDefault(getDate()) not null

   )

2.修改表

Alter Table MyTable To NewTable  --修改表名

Alter Table MyTable Add Column NewCol nvarchar(10)not null --增加列

AlterTable MyTable Alter Column NewCol int --修改列的数据类型

AlterTable MyTable drop Column NewCol --删除列

AlterTable MyTable Add Constraint MyCons Check(col>0) --增加约束

Alter TableMyTable Drop Constraint MyCons  --删除约束

3.删除表

Drop Table MyTable

4.插入记录

Insert into MyTable Values('Value1','Value2','Value3',..);

Insert intoMyTable(col1,col2,col3,…,coln) Value('Value1','Value2'+Convert(char(1),@i),'Value3',…,default)

5.修改记录

Update MyTable Set col1 =‘Value1’where col2='Value2'[ and col3 in (select子查询)]

6.删除记录

Delete MyTable where col1='value1'[or col2=value2 or col3 between value 3 and value 4 or  col4 is null]

7.查询记录

Select T.col1,t3.col1,avg(T.col5) asavg,max(t3.col6) as max,min(t3.col7) as min,count(*) as count

From (Select top 8 *

From MyTable1 t1 inner join MyTable2 t2

On t1.col1 =t2.col1

WhereT.col2=value1 and t3.col2 > value2

) T left join MyTable3 t3

On T.col3 =t3.col1

Group by T.col1,t3.col1

Having avg(T.col5)>value1

Order byT.col1 desc

触发器-----trigger

相当于C#.net中的事件

1.创建触发器

If exists(select * from sysobjects where name='MyTrigger'and type='tr')

 drop trigger MyTrigger

go

  create trigger MyTrigger on MyTable for Update  --for Delete 只有deleted表；for insert只有inserted表，instead Update:不执行更新操作

 as

   insert into MyTable_1

  Select when i.Balence >d.Balence then i.Balence -d.Balence                          

            else null

            end as InAccount

            when d.Balence>i.Balence thend.Balence- i.Balence

            else null

            end as OutAccount

            else null

From inserted I innerjoin deleted d   --inserted：更新之前表；deleted:更新之后表，不是整个表，是更新记录组合的表，

oni.AccountID = d.AccountID   

2.修改触发器

 altertrigger MyTrigger on MyTable for Update

 as

   insert into MyTable_1 Value('Value1',Value2,…)

3.删除触发器

   drop trigger MyTrigger

事务 -----Tran

要么一起成功，要么一起失败

1.创建事务

Declare@i

Set @i=0

Begintran

    update MyTable set balence =balence+100where CountID='958801'

    set @i=@i+1

    update MyTable set balance =balance-100where CountID='958802'

    set @i = @i+1

    if(@i=2)

    begin

    commit tran

    end

    else

    begin

    Rollback tran

    end

2.开始事务

Begintran

3.提交事务

Committran

4.回滚事务

Rollbacktran

存储过程--proc

相当于C#.Net中的方法，有返回值和无返回值，有参数和无参数，有输入参数和输出参数

1.创建存储过程

Ifexists(select * from sysobjects where name ='MyProc' and type ='p')

  drop proc MyProc

go

 create proc MyProc(@ref1varchar(10),@ref2 varchar(10),@ref3 int ouput)

 as

Begincommit

Begin try

   update MyTable set balence =balence+100where AccountID=@ref1

    set @ref3 =@ref3 +1

    update MyTable set balance =balance-100where AccountID=@ref2

    set @ref3 =@ref3 +1

    if(@ref3 =2)

    begin

    commit tran print 'success'

    end

    else

    begin

    rollback tran print '账户错误'

    end

End try

Begin

  rollback tran print '余额不足'

end

Begincatch

End catch

2.修改存储过程

 alter proc MyProc(@ref1varchar(10),@ref2 varchar(10),@ref3 int output)

 as

Declare @total

Set@total =0

Begintran

Begin try

   update MyTable set balence=balence+100 where CountID=@ref1 //会执行触发器

    set@total =@total+@@ROWCOUNT

    update MyTable set balance =balance-100where CountID=@ref2

   set@total =@total +@@ROWCOUNT

    if(@total=2)

    begin

    set @ref3=SCOPE_IDENTITY()

    commit tran print 'success'

    end

    else

    begin

    rollback tran print '账户错误'

    end

End try

Begin

  rollback tran print '余额不足'

end

Begincatch

End catch

3.删除存储过程

Drop proc MyProc

4.调用存储过程

Declare@ref

Set @ref

Exec MyProc value1,value2,@ref output

Print @ref