--转账案例
--创建银行户头表 检查户主金额是否大于1
create table bank
(
bid int identity(1,1) primary key,
customername varchar(10),
currentmoney int check(currentmoney>0),
)
insert into bank values('张三',1000)
insert into bank values('李四',1)
--未加事务之前
--张三转1000元给李四
update bank set currentmoney=currentmoney-1000 where bid=1
update bank set currentmoney=currentmoney+1000 where bid=2
select*from bank
delete from bank
--采用事务的方式转账 确保数据的安全性和一致性
begin transaction
declare @errorsum int
set @errorsum=0
update bank set currentmoney=currentmoney-800 where bid=1
set @errorsum=@errorsum+@@error
update bank set currentmoney=currentmoney+800 where bid=2
set @errorsum=@errorsum+@@error
--当操作遇到错误时
if @errorsum<>0
begin
print'转账失败,请确认后再经行下一步操作'
--事务回滚 恢复到数据原样
rollback transaction
end
else
begin
commit transaction
end
go
select*from bank
--创建索引的语法
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
INDEX currentname
ON bank (currentmoney)
--指定索引的填充比重 默认的索引页大小是4kb x(0--100)
[WITH FILLFACTOR=x]
--批量修改
update bank set customername='姓名'+customername
--使用游标
/*
创建游标的语法
declare 游标名称 cursor for select 字段 from表
*/
--将学生姓名通过游标操作的方式将其一行行打印
declare stucursor cursor for select stuname from student
declare @stuname varchar(30)
--开启游标
open stucursor
fetch next from stucursor into @stuname
while @@fetch_status=0
begin
print @stuname
fetch next from stucursor into @stuname
end
--关闭游标
close stucursor
--销毁游标 释放数据库服务器资源
deallocate stucursor
--利用视图排除表中的敏感数据
select*from bank
create view bankinfo
as
select bid,customername from bank
--使用视图中不推荐用来增删改操作 更多的时候用来进行查询动作
select *from bankinfo
--删除视图
if exists(select *from sysobjects where name='bankinfo')
drop view bankinfo
go
create view bankinfo as
select bid,customername from bank