轉帖雜和資料庫------- SQL-server 資料庫相關知識 事務、索引、視圖

-- SQL-server 資料庫相關知識 事務、索引、視圖

2007年12月20日 星期四 18:34

/******************************************************************************************************************************************/

--       SQL-server 資料庫相關知識   事務、索引、視圖  

--( 一 問題闡述 舉例:銀行的轉賬業務就是典型的一個事務事例)

-- 一、二部分的代碼建議分開執行，更容易了解

use master

go

--判斷是否已經存在銀行資料庫Bank 如果存在了就删除那個資料庫,

if exists(select *from sysdatabases where name='bank')

begin

drop database bank

end

--在E盤建立一個檔案夾存放資料庫檔案

exec xp_cmdshell 'md e:\ChinaBank'

--建立一個簡單的銀行資料庫

create database Bank

on primary

(

name='Bank_Date',

filename='e:\ChinaBank\bank_date.MDF',

SIZE=1MB,

MAXSIZE=100MB,

FILEGROWTH=1%

)

LOG ON

name='Bank_LOG',

filename='e:\ChinaBank\bank_log.LDF',

GO

use Bank

--建立銀行使用者資訊表

create table userInfo

customerName char(10) not null,

customerBalance money not null

/*--添加限制:根據銀行規定,賬戶餘額不能低于1元,否則視于銷戶---*/

alter table userInfo

add constraint ck_userInfo_customerBalance check(customerBalance>=1)

/*---插入測試資料-----*/

insert userInfo(customerName,customerBalance)

select '張三',1000 union

select '李四',1

select *from userInfo

/*現在開始模拟轉賬,張三轉賬一千元到李四賬戶裡,那麼張三的賬戶裡應該隻剩下1元,李四賬戶應該有1001元*/

update userInfo set customerBalance=CustomerBalance-1000 where customerName='張三'

update userInfo set customerBalance=CustomerBalance+1000 where customerName='李四'

--問題出來了,看見沒?張三的賬戶餘額并沒有減少,而李四卻多出1000元??????????

--這裡就展現了事務的概念,事務是單個的工作單元,如果某一事務成功,則在該事務中進行的所有資料更改均會送出,成為資料庫

--中永久組成部份,如果事務中遇到了錯誤且必須取消和復原,則所有資料更改均被清除,換句話說,你所作的操作均會被撤銷,

--事務也是一種機制,是一個操作序列,它包含了一組資料庫操作指令,并且所有的指令作為一個整體一起向系統送出或撤消操作請求,

--即這一組操作指令,要麼都執行要麼都不執行..

--上面的代碼執行一次.看看表記錄.你會發現我所說的.....

--下面的代碼執行一次.證明事務是不可分割的工作邏輯單元

---( 二 )下面重新來操作剛才的轉賬行為

--恢複賬戶資料 張三1000元,李四1元

update userInfo set customerBalance=1000 where customerName='張三'

update userInfo set customerBalance=1 where customerName='李四'

select *from UserInfo

--開始事務(表示事務從此開始.面的T-SQL語句都是一個整體)

begin transaction

--定義一個變量,用于累積事務執行過程中的錯誤

declare @errorSum int

set @errorSum=0 --(計數器)初始化為0表示沒有錯誤發生!

/*現在開始轉賬操作*/

--累積是否有錯,(在張三的賬戶中減去1000元,看是否執行成功,如果失敗,那麼@@error就能捕捉到一個錯誤,@@error是SQL-Server系統中定義的全局變量

set @errorSum=@errorSum+@@error

/*根據事務過程中的錯誤,來确認事務是送出還是撤消*/

--如果計數器不為0就說明這項轉賬事務中有錯誤

if @errorSum<>0

   --交易失敗!復原事務..

   rollback transaction

else

   --交易成功,送出事務

   commit transaction

--再次檢視表記錄,發現資料依然是原來的.沒有改變,再次證明了事務是一個不可分割的工作邏輯單元

--如果出現如下提示:是正常的.因為我們的表限制了賬戶餘額必須大于1.否則認為是銷戶,

--張三的賬戶中轉賬1000到李四賬戶中.那麼張三賬戶就隻有0元了.是以就有如下出錯提示,如果我們改為轉賬800,就不會出錯了

--伺服器: 消息 547，級别 16，狀态 1，行 1

--UPDATE 語句與 COLUMN CHECK 限制 'ck_userInfo_customerBalance' 沖突。該沖突發生于資料庫 'Bank'，表 'userInfo', column 'customerBalance'。

--語句已終止。

--        下面講解索引

--索引是提供指針以指向存儲在表中指定列的資料值，然後然後根據指定的排序次序排列這些指針，就好像一本書的目錄一樣，通過搜尋索引找到特定的值，然後跟随指針

--到達包含該值的行。

--索引：是SQL-Server編排資料的内部方法，它為SQL-Server提供一種方法來編排查詢資料的路由.

-- 在此借用上面建好的bank資料庫做講解

--建立一張表存儲學号成績

create table studentScore

name varchar(10) not null,

score float not null

--初始化資料

insert studentScore(name,score)

select '張小',90 union

select '張三',84 union

select '李三',29 union

select '五三',45 union

select '王三',90 union

select '趙三',82 union

select '李四',20 union

select '陳三',84 union

select '何五',30

select *from studentScore

--下面以 為成績表 建立非聚集索引 為例 ,因為成績有可能重複

/*先來檢測一下是否已經存在該索引，如果存在就删除該索引，重建立一個*/

if exists(select *from sysindexes where name='ix_studentScore_score')

drop index studentScore.ix_studentScore_score

create nonclustered index ix_studentScore_score

on studentScore(score)

with fillfactor=10

--指定索引查詢,按ix_studentScore_score ,查出成績在40到90之間的學員姓名

select name from studentScore

(index=ix_studentScore_score)

where score between 40 and 90

--這裡我們可以指定按那個索引進行查詢，但是一般情況下不需要人工指定，SQL-server将會自動根據所建立的索引，優化查詢

--另外SQL-Server中還包括唯一索引，主鍵索引，聚集索引等，上例中使用的是非聚集索引

--唯一索引：不允許兩行具有相同的索引值，如果建有唯一限制，那麼自動就建立了唯一索引。

--主鍵索引：在資料庫關系圖中為表定義一個主鍵将自動建立主鍵索引，主鍵索引是唯一索引的特殊類型.

--聚集索引: 在聚集索引中,表中各行的實體順序與鍵值的邏輯(索引)順序相同,表中隻能有一個聚集索引.但可以有多個非聚集索引,

--         設定某列為主鍵,該列預設為聚集索引.

--                                                  下面講解 視圖

--視圖:使用視圖的好處:1、出于安全考慮,使用者不必看到整個資料庫結構，而隐藏部分資料；2、符合使用者日常業務邏輯，使他們對資料更容易了解

--視圖是一種虛拟表，通常是作為來自一個或多個表的行或列的子集建立的，同時也可以包含全部列和行。

--我們依然采用T-SQL語句來建立視圖

--

---檢測是否存在該視圖，如果存在就删除該視圖

if exists (select *FROM SYSOBJECTS WHERE name='view_studentScore_score')

drop view view_studentScore_score

/**建立視圖   這裡用的内聯接 涉及到了上面銀行客戶資訊表*/

create view view_studentScore_score

as select 姓名=name,成績=studentScore.score from studentScore

inner join userInfo

on studentScore.name=userInfo.customerName

--視圖是一個虛拟表，可以像實體表一樣打開，同樣支援增删改查操作，但在實際應用中僅做查詢使用....

select *from view_studentScore_score

select * from authors

--聲明遊标

declare @au_lname varchar(20)

declare mycursor cursor for select au_lname from authors

--打開

S2-ATM取款機SQL 2000腳本

2007年12月20日 星期四 18:33

--判斷是否存在bankDB資料庫，如果存在就删除它，另外建立一個

if exists (select *from sysdatabases where name='bankDB')

drop database ChinaBank

--在E盤建立一個bankDB檔案夾

exec xp_cmdshell 'md e:\bankDB'

create database ChinaBank

filename='e:\bankDB\bank_date.MDF',

FILEGROWTH=15%

filename='e:\bankDB\bank_log.LDF',