sql server 的T-SQL 学习笔记（九）

/*******************  事务 索引 视图 ****************************/
-- 理解什么是事务 怎样创建事务
  -- eg:使用银行卡进行消费时候
  -- 事务的特性
    -- ACID
    -- 原子性(atomicity)：事务中各元素不可分隔，全部执行成功或者撤销所有的操作
    -- 一致性(consistency)：事务完成后事务保持一致的状态
    -- 隔离性(isoiation)：事务是相对独立的，对某数据进行修改时，其他事物不变
    -- 持久性(durability)：事务完成后对系统的影响是永久性的
  -- 事务隔离性级别
    -- 未提交读(read uncommited)(脏读):事务之间没有隔离，能够读取其他事物正在修改但尚未提交的数据，
        -- 该隔离级别无法确保数据的正确性
    -- 已提交读(read commited):SQL server 默认的隔离级别，能够确保其他事物不能读取当前的事务操作的数据
    -- 重复读
-- 使用事务
-- 创建索引
-- 创建和使用视图

-- 事务分类
  -- 显示事务: begin transaction 语句明确指定事务开始(一条或多条) 
  -- 隐式事务 : set implicit_transactions on 
  -- 自动提交事务 : sql server 默认的事务模式 (默认每一条SQL语句都是事务)
-- 事务管理
  -- 开始  bengin transaction
  -- 提交事务 commit transaction
  -- 回滚事务 rollback transaction
-- 语句执行是否成功
  -- @@error 全局变量
  -- @@error是判断当前执行SQL语句是否有错误，为了判断事务中所有SQL语句是否有错误
     -- 定义局部变量保存事务当中每一条SQL语句执行的错误@@error 最后再判断@@error 从而确定事务是否成功

-- 转账
  -- 给大乔（2,6225972472383739） 转账
    use BANK
    select * from ACCOUNT
    select * from ALL_PURPOSE_CARD
    select * from ALL_CARD_EXCHANGE
    go
  -- 开始事务
    begin transaction   -- 开始事务
    declare @tError int = 0   -- 定义局部变量保存SQL语句执行错误
    declare @money int = 200 -- 转账金额
    declare @card_id int = 5
    -- 转账： 添加交易记录 更新收账人金额 减少转账人金额
    insert into TRANSFER values ('6225972472383739','6225678934623552',@money,GETDATE())
    set @tError = @tError + @@ERROR  -- 更新错误信息
    select * from TRANSFER

    -- card_id  5 号
    insert ALL_CARD_EXCHANGE(CARD_ID,EXTRANSFER,BALANCE,EXTIME,EXADDRESS) 
      values (@card_id,@money,1000-@money,GETDATE(),'银行交易')
    set @tError = @tError + @@ERROR

     insert ALL_CARD_EXCHANGE(CARD_ID,EXTRANSFER,BALANCE,EXTIME,EXADDRESS)
       values (2,@money,6000+@money,GETDATE(),'银行交易')
    set @tError = @tError + @@ERROR
    select * from ALL_CARD_EXCHANGE

    -- 更新银行账户记录
    update ALL_PURPOSE_CARD set BALANCE -= @money ,LAST_USED_DATE = GETDATE() 
      where CARD_ID = 5
    set @tError = @tError + @@ERROR

    update ALL_PURPOSE_CARD set BALANCE += @money ,LAST_USED_DATE = GETDATE()
      where CARD_ID = 2
    set @tError = @tError + @@ERROR
    select * from ALL_PURPOSE_CARD
   --判断是否执行成功
   if(@tError <> 0)
       begin 
         print '交易失败，事务回滚'
         rollback transaction
       end
   else
       begin
         print '交易成功，事务提交'
         commit transaction 
       end



/******************** 索引 ****************************/
  -- 索引是表或者视图关联的硬盘上的结构，可以加快从表或者视图中查找数据的速度
    -- 唯一索引 : 不允许两行相同的索引值
    -- 聚集索引
    -- 非聚集索引

  -- 创建索引 
  -- 判断索引是否存在 存在就删除
  if exists (select * from sys.indexes where name = 'ix_card_no') 
    drop index ALL_PURPOSE_CARD.ix_card_no
  go
  -- 创建索引
  create nonclustered index ix_card_no on ALL_PURPOSE_CARD(CARD_NO) with fillfactor = 1
  go
  -- 查询索引
  select * from sys.indexes where name = 'ix_card_no'
  -- 使用索引 显示使用索引进行查询
  select * from ALL_PURPOSE_CARD with (index = ix_card_no) 


/**************** 视图 *********************/
  -- 视图 类似于VO
  --使用代码创建视图
  if exists (select * from sys.objects where name = 'view_account')
    drop view  view_account
  go 
  create view view_account as 
    select 
      ac.ACCOUNT_NAME,
      ap.CARD_NO,
      ap.BALANCE,
      ap.LAST_USED_DATE
    from ACCOUNT as ac  inner join ALL_PURPOSE_CARD as ap
      on ac.ACCOUNT_ID = ap.ACCOUNT_ID
    where ap.BALANCE < 10000
  go
  -- 使用视图
  select * from view_account
  update view_account set ACCOUNT_NAME = '陆逊' where ACCOUNT_NAME = '陆逊1'


  /*************** 课程总结 ******************/
  /**
    事务具有ACID4个特性：原子性、一致性、隔离性、持久性
    事务可以分为3类：显示事务、隐式事务、自动提交事务
      T-SQL管理事务使用3条语句：
        BEGIN TRANSACTION
        COMMIT TRANSACTION
        ROLLBACK TRANSACTION
    建立索引有助于快速检索数据。索引分为：唯一索引、主键索引、聚集索引、非聚集索引。
    聚集索引用于决定表中数据的物理存放顺序，所以一个表只能有一个聚集索引；非聚集索引用于决定表的逻辑顺序，一个表可以有多个非聚集索引。
    视图是查看表中数据的方法，它是一种虚拟表，是作为执行查询结果而创建的；视图充当筛选器，可以按要求对表数据进行行和列的筛选。
  */