if exists(select * from sysobjects where name = 'bank')
drop table bank
create table bank
(
customerName char(10), --顾客姓名
currentMoney money --余额
)
go
--增加检查约束 账户余额不能小于1
alter table bank
add constraint CK_currentMoney check(currentMoney >= 1)
go
insert into bank values('张三',1000)
insert into bank values('李四',1)
select * from bank
----------------------------------------------------------
--------------- * * * * 事 * * 务 * * * * ----------------
----------------------------------------------------------
use studb
go
set nocount on --不显示受影响的行数信息
PRint '事务之前的数据：'
select * from bank
go
begin transaction
declare @errorSum int
set @errorSum=0
update bank set currentMoney = currentMoney-1000 where customername='张三'
update bank set currentMoney = currentMoney+1000 where customername='李四'
set @errorSum = @errorSum + 1
print '事务中的数据：'
select * from bank
if @errorSum <> 0
begin
print '交易失败，回滚事务'
rollback transaction
end
else
begin
print '交易成功，提交事务，写入硬盘，永久的保存'
commit transaction
end
go
print '事务后的数据：'
select * from bank
--语法
--create [unique][clustered|nonclustered] index index_name on table_name (column_name[,column_name]...)
--[
-- with fillfactor = x --填充因子 x 为0~100之间的值
--]
代码
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)
--会报错 ：'index' 附近有语法错误。如果它要作为表提示的一部分，则必须有 WITH 关键字和圆括号，如：
select * from stumarks with(index=IX_stuMarks_writtenExam)
select * from stumarks with(index=IX_stuMarks_writtenExam) where writtenExam between 60 and 90