视图，事务，索引的sql

/*索引
缺点:
 1、增加存储空间
 2、降低IDU操作的性能
优点:
 1、提升SELECT操作的性能

语法:
 create index ix_name
 on tblName(col_name)

==================================================================
  视图
适用范围
1、大表
2、经常查询的字段

不适用范围
1、小表
2、很少查询的字段
3、字段值为有限值

聚集索引:
 特点:会影响记录的物理排序
  只能有一个
  针对表创建主键时会自动增加聚集索引
 优点:会进一步提高查询性能

非聚集索引:可以有多个
 [默认]

代码重用:
  多条T-SQL语句需重用，则封装到存储过程中
  如果一条select 语句重用,则封装到视图中
=============================================================================
事务:一个业务逻辑包含多个步骤,要么全部执行所有步骤,要么全部不执行.

begin transaction :开始事务
rollback tran 回滚事务
commit transaction:提交事务

特性:ACID
原子性:不可分割
一致性:
隔离性:
持久性:

分类:
显示事务:以begin tran开头
隐式事务:省略begin tran,
自动提交事务:单条sql语句，本身就是一个事务

重点注意:
begin tran之后只能跟一次commit tran 或rollback tran
,不能同时既commit tran , 又rollback tran

不带 保存点 和 事务名称 的 ROLLBACK TRANSACTION 回滚到事务的起点。
嵌套事务时，该语句将所有内层事务回滚到最远的 BEGIN TRANSACTION 语句
*/

 

 

--图书信息表
create table tbl_book(
	book_id varchar(20) primary key ,--书的编号
	book_name varchar(20) ,--书名
	book_status bit --0表示已还，1表示已借
)

--借阅表
create table tbl_borrow
(
	borrow_id varchar(20) primary key, --借书编号
	book_id varchar(20) ,	--书的编号
	borrow_person varchar(20) ,	--借书人
	borrow_date datetime		--借书日期
)

delete from tbl_book
delete from tbl_borrow

insert into tbl_book
values('bk0001','红楼梦',0)
insert into tbl_borrow
values('js02','bk0001','张三','2009-03-10')

--因为表tbl_borrow中的借书人会经常被查询，请进行优化  
create index ix_tbl_borrow_borrowperson
on tbl_borrow(borrow_person)
--select * from tbl_book
--创建视图
create view v_book_borrow
as 
    select  borrow_id as 借书编号,    book_name as 书名, borrow_person as 借书人 ,borrow_date as 借书日期
     from tbl_book,tbl_borrow  where tbl_book.book_id=tbl_borrow.book_id
go
select * from v_book_borrow

/*三、借书业务
现有一笔借书记录,
编号为 'brw001',书的编号为'bk0001', 借书人为张三,日期为系统当前日期，
注1：借书时需修改图书的标识为已借
注2:如果该书已借则不能完成业务
*/

--EXISTS用于检查子查询是否至少会返回一行数据,空返回false
--select * from tbl_borrow
 begin transaction
     if(exists(select * from tbl_book where book_id='bk0001'and book_status='0'))
	begin 
	    insert into tbl_borrow values('brw001','bk0001','张三',getdate())
            update tbl_book set book_status='1' where book_id='bk0001'
	end

     if(exists(select * from tbl_book where book_id='bk0001'and book_status='1'))
       begin
		print '书本已经被借出,等归还后再借'
		rollback transaction
		return
	end
 commit transaction


/*
测试索引的效率
往表中插入20万条记录,判断有索引与无索引时的查询效率
*/


delete from tbl_stu_for_loop--删除表中数据
drop table tbl_stu_for_loop--删除表
create table tbl_stu_for_loop
(
	stu_id int 
	
)

declare @i int 

set @i=1

while(@i<=200000)
begin 
        insert into tbl_stu_for_loop values (@i)
        set @i=@i+1
end


--创建索引
create index ix_tbl
on tbl_stu_for_loop(stu_id)

drop index tbl_stu_for_loop.ix_tbl--删除索引


declare @beginTime datetime
set @beginTime=getdate()
select *from tbl_stu_for_loop
declare @endTime datetime
set @endTime=getdate()
select datediff(ms,@beginTime,@endTime) as '所需时间(毫秒)'


--------------------------------------------------------------------
--帐户表
create table tbAccount
(
	code varchar(20) primary key ,
	balance decimal(10,2) 
)

insert into tbAccount values('zs',2000) 
insert into tbAccount values('ls',5000) 

--delete from tbAccount
--select * from tbAccount

declare @from varchar(20)
declare @to varchar(20)

begin tran
	set @from='zs'
	set @to='ls'

	if(not exists(select * from tbAccount where code=@from))
	begin
		print '转出者不存在,转帐失败'
		return
	end

	if(not exists(select * from tbAccount where code=@to))
	begin
		print '转入者不存在, 转帐失败'
		return
	end

	update tbAccount
	set balance=balance-1000
	where code=@from
	waitfor delay '0:0:01'

	if((select balance from tbAccount where code=@from)<0)
	begin
		print '金额不足, 转帐失败'
		rollback tran
		return
	end

	update tbAccount
	set balance=balance+1000
	where code=@to

	print '转帐成功'
commit tran