SQL 进阶学习之七

本文深入探讨SQL的高级用法,包括高效插入数据的INSERT策略、书籍管理的查询技巧、安全删除数据的DELETE操作以及灵活管理表结构的方法。通过实例解析,助你提升SQL技能。


-- =========================================================================================
--
 2007/06/06 SQL学习-- 事务
--
 =========================================================================================
use test
go

--
--
 ******************************************************************************************
--
 ===================================================================================================
--
 事务与锁
--
 事务(Transaction)的作用是保证一系列的数据操作可以全部正确完成,不会造成数据操作到一半未完成,
--
 而导致数据的完整性出错。
--
 锁(Lock)的作用是保证数据在操作的过程中不会受到任何的其他影响。
--
 ===================================================================================================
--
 事务是作为单个逻辑工作单元执行的一系列工作。必须具备的四个特性:
--
 1)原子性
--
 2)一致性
--
 3)隔离性:由并发事务所作的修改必须与其他并发事务所作的修改隔离。
--
 4)持久性:在事务完成后,其操作结果对于系统的影响应该是永久的。

-- 
--
 ===================================================================================================
select * from authers
select * from books

delete authers 
where author_id = 4

delete books 
where book_id = 8
--
--
 开始事务
begin tran
declare @bid int
-- 添加一本书
insert books
values(8,'ADO.NET',4,300)
if @@error > 0
goto tranrollback
set @bid = @@identity
-- 添加一个作者
insert authers
(author_id,lastname,firstname,company)
values(4,'bin','song','21CNIT')
--print '添加成功 !'
if @@error > 0
goto tranrollback
--
tranrollback:
if @@error > 0 or @@rowcount <> 1
    
rollback tran -- 如果发生错误则回滚事务
else
    
commit tran -- 如果没有发生错误则提交事务
go



--
--
 事务的执行方式分为三种:
--
 1)显式事务
--
 2)自动提交事务(默认的方式)
--
  每一个T-SQL语句都是一个自动提交的事务
--
 3)隐式事务
--
  当执行 set implicit_transactions on 语句后,进入隐式模式
--
  隐式事务产生的是一个连续的事务链

-- =================================================================================================
--
 编写事务的原则:
--
 1)事务要尽可能简短
--
 2)在事务中访问的数据量要尽量最少
--
 3)浏览数据时尽量不要打开事务 
--
 4)在事务处理期间尽量不要请求用户输入


-- 编写事务
--
select * from books
-- 事务开始
begin tran
-- 
insert books
(book_id,title)
values(10,'wo')
if @@error > 0 or @@rowcount <> 1
goto tranrollback
--
insert books
(book_id,title)
values(11,'me')
if @@error > 0 or @@rowcount <> 1
goto tranrollback
--
select * from books
--
delete books
where book_id = 15
if @@error > 0 or @@rowcount <> 1
begin
    tranrollback:
    
rollback tran     -- 回滚事务
end
else
    
commit tran
-- 事务结束
--
 查看事务结束后的数据集
select * from books




-- 嵌套事务
--
 嵌套事务里并不是将嵌套的语句完全执行完毕,嵌套事务会将内层事务的commit语句忽略,只提交最外层事务的commit语句
--
 在内层事务里,rollback语句仍然有效,但只是回滚到外层事务开始之前的状态
--
begin tran
print '当前事务嵌套层次为: ' + cast(@@trancount as nvarchar(5))
insert books
(book_id,title)
values (20,'qiantao')
if @@error > 0 or @@rowcount <> 1
goto tranrollback
    
-- 嵌套开始
    begin tran
    
print '当前事务嵌套层次为: ' + cast(@@trancount as nvarchar(5))
    
insert books
    (book_id,title)
    
values (25,'qiantao1')
    
if @@error > 0 or @@rowcount <> 1
        
rollback tran
    
else
        
commit tran
    
-- 嵌套结束
print '当前事务嵌套层次为: ' + cast(@@trancount as nvarchar(5))
if @@error > 1
begin
    tranrollback:
    
rollback tran
end
else
    
commit tran
go
--
select * from books
    


-- 事务保存点
save tran 保存点名称
-- 设置好事务保存点之后,可以让事务回滚到该保存点时的状态
--
 在一个事务中可以设置多个事务保存点,可以回滚到任一个事务保存点所保存的状态



-- 事务的隔离级别由低到高分为5个级别:
--
 1)Read uncommitted 不隔离数据
--
 2) Read committed 不允许读取没有提交的数据,这是SQL Server默认的隔离级别
--
 3) Repeatable Read 在事务中锁定所读取的数据不别人修改和删除,如此可以保证在事务中每次读取到的数据都是一致的
--
 4) Snapshot 快速隔离,
--
 5) Serializable 将事务所要用到的数据表全部锁定
    set tran isolation level
    {
read uncommitted
    
|read committed
    
|Repeatable Read
    
|Snapshot
    
|Serializable
    }
    
[;]




-- =======================================================================================================
--
  锁
--
 锁的作用是将数据临时锁定只提供给一个进程或程序使用,并防止其他的进程或程序修改或读取
--
 锁是由并发性控制来决定的
--
 并发性就是多个进程或程序同时对数据进行处理的性质
--
 乐观并发性控制,悲观并发性控制

-- 锁定模式: 
--
 1)共享锁
--
 2)更新锁
--
 3)排他锁
--
 4)意向锁
--
 5)架构锁
--
 6)大容量更新锁
--
 7)键范围锁

-- 死锁(Deadlock)
--
 在两个或多个任务中,如果每个任务都锁定了自己的资源,却又在等待其他事务释放资源,此时就会造成死锁。 

-- =======================================================================================================

 

 

-- 事务的示例:
--
 用事务在类别表里插入两条记录,然后将插入的最后一条记录删除,看看成功提交事务的结果
select * from Categories 
order by Categoryid desc
--
begin tran
--
insert Categories(CategoryName)
values(N'午饭')
if @@error > 0 or @@rowcount <> 1
    
goto rb
--
insert Categories(CategoryName)
values(N'睡觉')
if @@error > 0 or @@rowcount <> 1
    
goto rb
--
select * from Categories 
order by Categoryid desc
--
delete Categories 
where CategoryID = (select max(CategoryID) from Categories)
if @@error > 0 or @@rowcount <> 1
begin
    rb:
    
rollback tran
end
else
    
commit tran

select * from Categories 
order by Categoryid desc




-- error tran
--
select * from Categories 
order by Categoryid desc
--

begin tran
--
insert Categories(CategoryName)
values(N'午饭1')
if @@error > 0 or @@rowcount <> 1
    
goto rb
--
insert Categories(CategoryName)
values(N'睡觉1')
if @@error > 0 or @@rowcount <> 1
    
goto rb
--
select * from Categories 
order by Categoryid desc
--
delete Categories 
where CategoryID = 15
if @@error > 0 or @@rowcount <> 1
begin
    rb:
    
rollback tran
end
else
    
commit tran

select * from Categories 
order by Categoryid desc
--



-- 嵌套的事务
--
begin tran
print N'当前事务的层次为:' + cast(@@trancount as nvarchar(5))

insert Products (ProductName,CategoryID)
values('西瓜汁',1)
if @@error > 0 or @@rowcount <> 1
    
goto rb

insert Products (ProductName,CategoryID)
values('草莓汁',1)
if @@error > 0 or @@rowcount <> 1
    
goto rb

    
-- 开始嵌套    
        begin tran
        
print N'当前事务的层次为:' + cast(@@trancount as nvarchar(5))
        
insert Products (ProductName,CategoryID)
        
values('菠萝汁',1)
        
if @@error > 0 or @@rowcount <> 1
            
goto rb
        
else 
            
commit tran
    
-- 嵌套结束
print N'当前事务的层次为:' + cast(@@trancount as nvarchar(5))

if @@error > 0
    
begin
        rb:
        
rollback tran
    
end
else
    
commit tran

--
select * from products

-- 

-- 事务保存点
--
 save tran
--
select * from products
--
begin tran
insert Products (ProductName,CategoryID)
values(N'西瓜汁1',1)
if @@error > 0 or @@rowcount <> 1
    
goto rb

select * from products
save tran savebyP

insert Products (ProductName,CategoryID)
values(N'草莓汁2',1)
if @@error > 0 or @@rowcount <> 1
    
goto rb

select * from products
save tran savebyP1

    
-- 开始嵌套    
        begin tran
        
update products
        
set ProductName = 'OK!'
        
where ProductID = 115151
        
if @@error > 0 or @@rowcount <> 1
            
rollback tran savebyP
        
else 
            
commit tran savebyP
    
-- 嵌套结束

--
select * from products
--
insert Products (ProductName,CategoryID)
values(N'冰镇可乐1',1)

if @@error > 0 or @@rowcount <> 1
    
rollback tran 
else
    
commit tran

--
select * from products

if @@error > 0
    
begin
        rb:
        
rollback tran
    
end
else
    
commit tran

--
select * from products


/*
当 SET XACT_ABORT 为 ON 时,如果执行 Transact-SQL 语句产生运行时错误,则整个事务将终止并回滚。 

当 SET XACT_ABORT 为 OFF 时,有时只回滚产生错误的 Transact-SQL 语句,而事务将继续进行处理。如果错误很严重,
那么即使 SET XACT_ABORT 为 OFF,也可能回滚整个事务。

编译错误(如语法错误)不受 SET XACT_ABORT 的影响

*/

--
select * from products
set xact_abort on
--
begin tran a
insert Products (ProductName,CategoryID)
values('ok1',1)

insert Products (ProductName,CategoryID)
values('SQL1',1)

select * from products

insert Products (ProductName,CategoryID)
values('SQL3',111)
select * from products

commit tran a

select * from products

-- ===================================================================================================
--
 Trigger
--
 ===================================================================================================
--
 insert trigger
create trigger CategoriesbyInsert
on Categories
after 
insert
as
    
begin
        
print N'类别表里新增了一条记录!'
    
end
go

--
create trigger CategoriesbyInsert_1
on Categories
after 
insert
as
    
begin
        
print N'测试顺序!'
    
end
go
--
insert into Categories
(CategoryName)
values(N'测试触发器4')

--
--
 sp_settriggerorder
exec sp_settriggerorder 'CategoriesbyInsert_1' ,'first','insert'
exec sp_settriggerorder 'CategoriesbyInsert','last','insert'

--
create trigger Categoriesbyupdate
on Categories
after 
update
as
    
begin
        
print N'类别表里更新了一条记录!'
    
end
go
--
select * from Categories
update Categories
set CategoryName = N'测试触发器2'
where CategoryID = 17
--
create trigger Categoriesbydelete
on Categories
after 
delete
as
    
begin
        
print N'类别表里新删除了一条记录!'
    
end
go



-- 
select * from [Order Details]
select * from orders
--
create trigger odbyinsert
on [Order Details]
after 
insert
as
begin
    
if (select discount from inserted)> 0.7
    
begin
        
print N'折扣不能大于70%'
        
rollback tran
    
end
end
go

insert [Order Details]
values(10252,1,$20,50,0.8)




-- 触发器的嵌套
--
create table testlog
(
    id 
int identity(1,1not null,
    tablename 
nvarchar(50not null,
    opsentence 
nvarchar(2000not null,
    opcontent 
nvarchar(2000not null,
    optime 
datetime not null
    
constraint df_testlog_optime default (getdate()),
    
constraint pk_testlog primary key clustered
    (
        id 
asc
    )
    
with (ignore_dup_key = offon [primary]
)
on [primary]
go
--
create trigger testloginsert
on testlog
after 
insert
as
begin
    
print '测试时,表记录有所变动'
end
go
--
create trigger bookinsert3
on books
after 
insert
as
begin
    
declare @name nvarchar(20),@page nvarchar(max)
    
set @name = (select title from inserted)
    
set @page = (select pages from inserted)
    
    
insert into testlog(tablename,opsentence,opcontent)
    
values('books表','插入记录','书名 :' + @name + ' 页数: '+ @page)
end
go
-- 测试
insert books
(book_id,title,pages)
values(8,'HTML',200)

select * from testlog



-- instead of 触发器
create trigger od_insert
on [Order Details]
instead 
of insert
as 
begin
    
declare
    
@oid int,
    
@pid int,
    
@price money,
    
@q smallint,
    
@d real

    
set @oid = (select orderid from inserted)
    
set @pid = (select productid from inserted)
    
set @price = (select UnitPrice from inserted)
    
set @q = (select Quantity from inserted)
    
set @d = (select Discount from inserted)
    
    
if (@d> 0.6
        
print N'折扣不能大于0.6'
    
else
        
insert into [Order Details]
            
values(@oid,@pid,@price,@q,@d)
    
end
go

--
drop trigger od_insert

insert into [Order Details]
            
values(10252,1,$20,50,0.8)

select * from [Order Details]
where Discount > 0.5

exec sp_helptext od_insert

-- 禁用一个触发器
--
 
alter table [Order Details]
disable 
trigger od_insert

-- 启用
alter table [Order Details]
enable 
trigger od_insert


--
create trigger test_table
on database
for drop_table ,alter_table
as 
begin
    
print N'对不起,你不能对数据表进行操作'
    
rollback;
end

drop table [Order Details]

 

评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值