面試準備之SQL 5 —— 事務、索引和視圖

1. 什麼是事務？

事務是一種機制、一種操作序列，它包含了一組資料庫操作命令，並且所有的命令作為一個整體一起向系統提交

或撤銷操作請求，即這一組資料庫要麼都執行，要麼都不執行。特別適用於多使用者同時操作的資料庫系統。

 

事務是作為單個邏輯工作單元執行的一系列操作。

一個邏輯工作單位必須有4個屬性：

原子性：事務是一個完整的操作，事務的各元素不可再分。所有元素必須作為一個整體提交或復原。

一致性：當事務完成時，資料必須處於一致狀態。

隔離性：對資料進行修改時所有並發事務是彼此隔離的。

持久性：事務完成後，對系統影響是永久性的。

 

2.建立事務

開始事務：begin transaction

提交事務：commit transaction

復原（撤銷）事務：rollback transaction

  

代碼

use studb
go

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

 

----------------------------------------------------------
--------------- * * * * 索 * * 引 * * * * ----------------
----------------------------------------------------------
--索引：它是SQL Server編排資料的內部方法
--索引可以分為以下三種;
--唯一索引：不允許兩行具有相同的索引值
--主鍵索引：為表定義主鍵時自動建立 唯一索引的特殊類型 主鍵索引要求主鍵中的每一個值都是唯一的
--叢集索引：表中各行的物理順序與索引值的邏輯（索引）順序相同，表中只能包含一個叢集索引（可以理解為字典的拼音）。
--非叢集索引：資料和索引包含指向資料存放區的相應位置 表中的各行的物理順序與索引值的邏輯順序不匹配。（可以理解為MAP）
--叢集索引比非叢集索引速度更快
--在一個表中只能有一個叢集索引，但是可以有多個非叢集索引，設定某列為主鍵該列就預設為叢集索引了。
--表是可以沒有索引的，主鍵索引不一定就是叢集索引。

--索引運用到哪裡？
--該列頻繁被搜尋，該列用於對資料排序
--劣種就幾個不同的值，表中就幾行資料就沒必要使用索引

 

--文法
--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

 

----------------------------------------------------------
--------------- * * * * 視 * * 圖 * * * * ----------------
----------------------------------------------------------

視圖：是一種虛擬表，基於一個表或多個表的資料的查詢方法。

一般作用：篩選表中的行、防止未經許可的使用者訪問敏感性資料、將多個物理資料表抽象為一個邏輯資料表

--文法：
--create view view_name
--as
--<select 語句>

代碼

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