>[1]事务
use studb
go
if exists (select * from sysobjects where name = 'bank')
drop table bank
go
create table bank
(
customername char(10),
currentmoney money
)
go
alter table bank
add constraint ck_currentmoney check (currentmoney>=1)
go
insert into bank (customername,currentmoney) values ('张三'，1000)
insert into bank (customername,currentmoney) values ('李四'，1)
select * from bank
go
事务的属性
1. 原子性
2. 一致性
3. 隔离性
4. 持久性
开始事务：begin transaction
提交事务：commit transaction
回滚事务：rollback transaction
use studb
go
update bank set currentmoney = currentmoney - 1000 where customername = '李四'
set nocount on -----不显示受影响的行数信息
print '查看转帐事务前的余额'
select * from bank
go
begin transaction
declare @errorsum int
set @errorsum = 0
update bank set currentmoney = currentmoney - 1000
where customername = '张三'
set @errorsum = @errorsum + @@error -----累计是否有错误
update bank set currentmoney = currentmoney + 1000
set @errorsum = @errorsum + @@error
print '查看转帐事务过程中的余额'
select * from bank
if @errorsum<>0
begin
print '交易失败，回滚事务'
rollback transaction
end
else
begin
print '交易成功，提交事务，写入硬盘，永久的保存'
commit transaction
end
go
print '查看转帐事务后的余额'
select * from bank
go
…………………………………………………………………………………………………………………………
……………
>[2]索引
use studb
go
if exists (select name from sysindexes where name = 'ix_stumarks_writtenexam')
drop index stumarks.ix_stumarks_writtenexam
create
nonclustered index ix_stumarks_writtenexam
on stumarks(writtenexam)
with fillfactor = 30
go
select * from stumarks(index = ix_stumarks_writtenexam)
where writtenexam between 60 and 90
…………………………………………………………………………………………………………………………
……………
>[3]视图
use studb
go
if exists (select * from sysobjects where name = 'view_stuinfo_stumarks')
drop view view_stuinfo_stumarks
go
create view view_stuinfo_stumarks
as
select 姓名 = stuname,学号 = stuinfo.stuno,笔试成绩 = writtenexam,机试成绩 = labexam,平均
分 = (writtenexam+labexam)/2
from stuinfo left join stumarks on stuinfo.stuno = stumarks.stuno
go
select * from view_stuinfo_stumarks