新觀念 MicrosoftVisual Basic 程式設計 15-3 建立資料表與資料欄位 : 本節將先介紹 SQL Server 的資料型別, 接著即練習建立資料表與不同資料型別的欄位 15-4 建立資料表間的關聯 : 當資料庫有多個資料表時, 其間的欄位可以有參考關係, 也稱為關聯, 本節

Size: px

Start display at page:

Download "新觀念 MicrosoftVisual Basic 程式設計 15-3 建立資料表與資料欄位 : 本節將先介紹 SQL Server 的資料型別, 接著即練習建立資料表與不同資料型別的欄位 15-4 建立資料表間的關聯 : 當資料庫有多個資料表時, 其間的欄位可以有參考關係, 也稱為關聯, 本節"

驯祥倪
7 years ago
Views:

1 新觀念 Microsoft Visual Basic 程式設計 CHAPTER 15 建立資料庫資料表與關聯本章閱讀建議前一章學會在 VB 中以拉曳的方式, 由既有的資料庫, 建立資料庫應用程式本章則回頭來認識 SQL Server 資料庫, 並學習如何在 VB 中建立資料庫資料表, 以及設定資料表間的關聯 15-1 認識 SQL Server:VB 所附的資料庫系統包括 SQL Server 2005 Express Edition 及 Compact Edition 兩種版本, 本節先來瞭解一下其主要功能與版本差異 15-2 建立資料庫 : 在 VB 的整合開發環境中, 即可建立資料庫及資料表, 並輸入資料除了做以上的練習外, 本節也會介紹 VB 所提供的基本資料庫管理功能

2 新觀念 MicrosoftVisual Basic 程式設計 15-3 建立資料表與資料欄位 : 本節將先介紹 SQL Server 的資料型別, 接著即練習建立資料表與不同資料型別的欄位 15-4 建立資料表間的關聯 : 當資料庫有多個資料表時, 其間的欄位可以有參考關係, 也稱為關聯, 本節即要學習如何建立資料表間的關聯 15-1 認識 SQL Server 由於資料庫的應用相當普遍, Visual Basic 和.Net Framework 在這方面的支援也很廣泛, 可連線大部份的資料庫系統並存取資料不過微軟公司基於市場區隔的考量, 限制了在 VB Express 版 ( 以下簡稱 VB) 的圖形介面中, 使用資料庫的能力也就是說在 VB 中用精靈建立資料來源時, 僅支援連線下列 3 種資料來源 : Microsoft Office Access 資料庫檔 Microsoft SQL Server Compact 資料庫檔 Microsoft SQL Server 資料庫檔第一項必須已有 Microsoft Office Access 資料庫檔才能使用, 因為 VB 不支援建立全新的 Access 資料庫檔 ; 而後兩者都是 VB 本身即可支援建立, 因此本章就只以 SQL Server 資料庫檔的應用為例來說明 15-2

建立資料庫資料表與關聯 15 那麼 Microsoft SQL Server Compact 和 Microsoft SQL Server

要說明此點, 我們先簡單認識一下 SQL Server 的運作方式若要使用其它類型的資料來源時, 例如 Oracle 其它版本的 Microsoft SQL Server

集中管理, 並對外部提供資料存取與查詢的服務在這種運作模式下, 實際存放資料的資料庫檔案, 都是由 SQL Server 管理系統負責讀寫, 應用程式要存取某個資料庫

3 建立資料庫資料表與關聯 15 那麼 Microsoft SQL Server Compact 和 Microsoft SQL Server 資料庫檔案這兩種資料來源又有何不同? 要說明此點, 我們先簡單認識一下 SQL Server 的運作方式若要使用其它類型的資料來源時, 例如 Oracle 其它版本的 Microsoft SQL Server 等等, 則需自行撰寫程式, 而沒有精靈支援集中管理的 SQL Server 管理系統圖 -1 SQL Server 的資料庫是由 SQL Server 管理系統集中管理, 並對外部提供資料存取與查詢的服務在這種運作模式下, 實際存放資料的資料庫檔案, 都是由 SQL Server 管理系統負責讀寫, 應用程式要存取某個資料庫資料表的內容時, 也是向管理系統提出要求, 由管理系統依據要求的內容, 從資料庫中取出資料, 並傳給應用程式使用應用程式資料庫系統人事資料庫 A B C 使用者 SQL Server 管理系統產品資料庫進銷存資料庫資料庫管理者 15-3

4 新觀念 MicrosoftVisual Basic 程式設計在商業的應用中, 資料庫內的資料都相當多 ( 例如每日的交易產品客戶等資料 ), 因此為了提高資料存取效率便於維護, SQL Server 管理系統也提供相關的功能, 例如 : 提供較多的資料存取技術完整的安全性管理提供資料庫備份與還原不同伺服器間的資料庫同步等管理功能雖然這樣完整的功能很適合商業應用環境, 但對於較小型的工作環境以及像我們要練習資料庫程式開發, 這些額外的功能反而造成使用上的負擔或是造成整體效能的降低 ( 因為較強的功能意謂著用掉較多的系統資源 ) 為了提供一個較簡易的資料庫環境, 給較小的組織及程式設計人員來使用及測試, 微軟特別提供兩個免費的 SQL Server 軟體, 也就是 SQL Server Express Edition 與 SQL Server Compact Edition SQL Server Express 版本 SQL Server Express 算是陽春版的 SQL Server 管理系統, 基本上它仍具備 SQL Server 的主要功能, 只是硬體支援限制較多 ( 例如不支援多處理器 ), 也缺少一些進階的資料庫管理功能在 VB 中即隨附 SQL Server Express 軟體, 而在 VB 中建立資料來源的選項 Microsoft SQL Server 資料庫檔案, 指的就是使用 SQL Server Express 15-4

5 建立資料庫資料表與關聯 15 雖然選項中是稱為資料庫檔案, 但其實程式在讀寫資料時, 仍是向在本機中已啟動的 SQL Server Express 管理系統提出要求, 並由管理系統實際讀寫檔案, 而不是由應用程式自行讀寫檔案若在安裝 VB 時未選擇安裝 SQL Server Express, 在選擇資料來源建立資料庫時, 就不能選用 Microsoft SQL Server 資料庫檔案 SQL Server Compact 版 SQL Server Compact 則是比 SQL Server Express 更精簡的版本, 適用於單機使用的小型資料庫環境除了功能精簡外, SQL Server Compact 的管理系統是隨著資料庫應用程式一起啟動執行結束 ; 而其它 SQL Server 版本則是隨作業系統啟動時一起啟動 ( 在 Windows 作業系統中稱之為服務 - Service) 由於 SQL Server Compact 版使用方便, 不需額外管理, 因此本書在建立資料來源時, 都以它來示範不過本章及前後兩章的內容, 原則上也都適用於 SQL Server Express 15-2 建立資料庫 A B C VB 的整合環境內含建立資料庫的介面, 我們可直接在 VB 中建立 SQL Server Compact ( 或 SQL Server Express) 的資料庫檔從 VB 建立 SQL Server 資料庫檔 15-5

新觀念 MicrosoftVisual Basic 程式設計 step 1 在 VB 中執行工具 / 連接至資料庫命令 ( 不必先建立專案 ), 此時即會出現加入連接交談窗 : 圖 -2 若不是 Microsoft SQL Server Compact, 請按變更鈕更改設定 ( 參見後面方框 ) 按此鈕以便建立新資料庫

6 新觀念 MicrosoftVisual Basic 程式設計 step 1 在 VB 中執行工具 / 連接至資料庫命令 ( 不必先建立專案 ), 此時即會出現加入連接交談窗 : 圖 -2 若不是 Microsoft SQL Server Compact, 請按變更鈕更改設定 ( 參見後面方框 ) 按此鈕以便建立新資料庫我們要建立使用 Microsoft SQL Server Compact 的資料庫, 所以直接按鈕即可建立資料庫若交談窗中目前的資料來源是 Microsoft SQL Server 資料庫檔案 (Sqlclient), 而非 Microsoft SQL Server Compact 3.5, 請參見稍後方框的說明更改其設定, 再來建立資料庫 15-6

$C:\VB2008\Ch15\Ch15 練習 ( 副檔名預設為.$

7 建立資料庫資料表與關聯 15 圖 -3 step 2 按鈕後會出現建立新的 SQL Server Compact 資料庫交談窗, 在此必須設定新資料庫的路徑和檔名 ( 請注意, 路徑中的資料夾必須已經存在, 否則建立時會出現失敗訊息 ), 以及排序方式 : 1 輸入資料庫檔的路徑檔名 C:\VB2008\Ch15\Ch15 練習 ( 副檔名預設為.sdf, 不需輸入 ) 2 選擇中文 - 台灣的排序方式 3 按此鈕暫不設定密碼 ( 可在稍後介紹的管理介面中設定 ) A B C 因剛才未設密碼, 所以 VB 會提示此訊息 4 按此鈕使用空白密碼 15-7

新觀念 MicrosoftVisual Basic 程式設計 step 3 建立成功後,

若在加入連接交談窗中列出的資料來源是 Microsoft SQL Server 資料庫檔案,

8 新觀念 MicrosoftVisual Basic 程式設計 step 3 建立成功後, 會自動回到先前的加入連接交談窗此時可按鈕測試一下資料庫是否可正常連線, 或直接按鈕建立指向新資料庫的資料連接 : 圖 -4 自動填入剛才建立的資料庫路徑按此鈕可測試 VB 與資料庫的連線 1 按此鈕 2 出現新的資料連結常見錯誤提醒如何變更資料來源種類若在加入連接交談窗中列出的資料來源是 Microsoft SQL Server 資料庫檔案, 可用如下方式將之設為 Microsoft SQL Server Compact 3.5: 接下頁 15-8

9 建立資料庫資料表與關聯 15 3 勾選此項, 以後預設就會選用 Microsoft SQL Server Compact 選擇 Microsoft SQL Server Compact 按此鈕 4 按此鈕 A B C 變成 Microsoft SQL Server Compact 3.5 了此部份的設定, 會隨資料來源種類自動調整 15-9

可在資料庫檔名欄輸入新的資料庫檔名, 按鈕即可建立新資料庫檔 : 1 選用 Microsoft SQL Server

10 新觀念 MicrosoftVisual Basic 程式設計補充說明建立 SQL Server Express 資料庫檔若在加入連接交談窗中的資料來源欄選擇 Microsoft SQL Server 資料庫檔案, 可在資料庫檔名欄輸入新的資料庫檔名, 按鈕即可建立新資料庫檔 : 1 選用 Microsoft SQL Server 資料庫檔案 (SqlClient) 2 輸入新資料庫檔的檔名路徑 ( 副檔名預設為.mdf, 可不輸入 ) 3 按此鈕 4 按此鈕就會建立資料庫與資料連接 15-10

11 建立資料庫資料表與關聯 15 在 VB 建立指向新資料庫的資料連接後, 也可在 VB 中看到 Express 版支援的資料庫物件比陽春的 Compact 版要多 ( 各項功能請參見本公司 SQL Server 2005 設計實務一書 ): 除了功能較多外, 在 Express 版的資料庫中建立資料表的方式, 與本節稍後用 Compact 版示範的內容大同小異此外, Express 版的資料庫檔其實有兩個檔案,.mdf 為資料庫檔,.ldf 則為記錄存取資料庫動作的記錄檔, 這兩個檔案都必須存在, 資料庫才能運作, 所以請不要隨意移動或刪除這兩個檔案 : Compact 版的資料庫檔 Express 版的資料庫支援這些物件 A B C Express 版的資料庫記錄檔可由此欄分辨檔案類型 Express 版的資料庫檔 15-11

在資料庫名稱按滑鼠右鈕, 執行資料庫屬性命令即可開啟資料庫屬性交談窗進行管理 : 圖 -5 執行此命令目前檢視的資料庫檔路俓會列在此處有 3

12 新觀念 MicrosoftVisual Basic 程式設計新建好的資料庫中, 尚無任何資料, 我們必須先建立資料表才能在其中輸入資料不過以下先來認識資料庫總管所提供的基本資料庫管理功能資料庫的管理在 VB 的資料庫總管中, 可對資料庫檔進行一些簡單的管理動作, 在資料庫名稱按滑鼠右鈕, 執行資料庫屬性命令即可開啟資料庫屬性交談窗進行管理 : 圖 -5 執行此命令目前檢視的資料庫檔路俓會列在此處有 3 個頁次一般頁次可以檢視資料庫檔屬性, 但不能修改如圖所示, 一般頁次只能檢視資料庫檔屬性, 但不能修改, 不過另兩個壓縮與修復密碼頁次則可對資料庫進行一些處理及密碼設定 15-12

13 建立資料庫資料表與關聯 15 圖 -6 壓縮與修復在壓縮與修復頁次中包含數個可用以壓縮或是修復資料庫的功能選項, 在說明壓縮的功能前, 要先簡單說明一下 SQL Server 使用檔案的方式 SQL Server 使用檔案儲存資料時, 會將檔案以 8KB (8192 位元組 ) 為單位分成一個個的儲存單元, 稱為資料頁 (Page), 而資料庫的資料就是存放在這些資料頁之中當我們刪除資料庫中的某一筆記錄時, 為了提高處理的效率, SQL Server 並不會把資料從資料頁中清除, 而只是將該記錄標示為已刪除因此當資料庫使用一段時間後, 因為資料的增增減減, 可能會使資料庫檔案中, 出現一些未使用的資料頁, 或者說, 檔案中部分資料頁僅含已刪除的記錄 : SQL Server 資料庫檔 A B C 以 8K 為單位的資料頁只含已刪除記錄的資料頁然而當這種未使用的資料頁變多時, 意謂著資料庫檔佔用了額外的空間, 同時也會拖慢讀取資料的效率在這種情況下我們就可用壓縮與修復頁次中的選項來壓縮資料庫檔, 也就是將這些沒用的資料頁自資料庫檔中移除 15-13

14 新觀念 MicrosoftVisual Basic 程式設計圖 -7 SQL Server 資料庫檔壓縮檔案變小除了壓縮資料庫檔, 壓縮與修復頁次也包含修復資料庫的選項 : 圖 -8 選擇壓縮及修復頁次, 就可看到相關選項 15-14

建立資料庫資料表與關聯 15 圖 -9 無 : 不做壓縮和修復處理刪除可用頁面來壓縮資料庫 : 將未使用的資料頁移除但既有的資料則不動, 如果資料頁同時含未刪除及已刪除的資料, 將不受影響完整壓縮資料庫 : 會將現有的資料往檔案前面移動, 讓資料全部連續存放, 再移除多餘未使用的資料頁修復已損毀的資料庫 : 有時因系統運作不正常, 可能導致資料庫損毀, 此時可利用此選項,

15 建立資料庫資料表與關聯 15 圖 -9 無 : 不做壓縮和修復處理刪除可用頁面來壓縮資料庫 : 將未使用的資料頁移除但既有的資料則不動, 如果資料頁同時含未刪除及已刪除的資料, 將不受影響完整壓縮資料庫 : 會將現有的資料往檔案前面移動, 讓資料全部連續存放, 再移除多餘未使用的資料頁修復已損毀的資料庫 : 有時因系統運作不正常, 可能導致資料庫損毀, 此時可利用此選項, 來修復資料庫選用此功能時, 可勾選其下的復原損毀資料列, 表示要修復損毀的記錄 ; 若不勾選此項, 則修復時會直接刪除受損的資料, 只保留沒有損毀的部份當我們選用完整壓縮資料庫和修復已損毀的資料庫這兩項時, 交談窗中間的檔案選項也會變成可以使用, 此處可設定壓縮或修復的結果, 是要覆蓋現有的資料庫檔, 或是另存新檔 : 選此項表示覆蓋舊檔 A B C 選此項則是用以此檔名建立新資料庫欄輸入的路徑, 將壓縮或修復後的結果存成新資料庫檔若輸入的路徑已存在時, 則覆蓋舊檔 15-15

16 新觀念 MicrosoftVisual Basic 程式設計密碼在密碼頁次可為資料庫設定或變更密碼, 設了密碼之後, SQL Server 會將資料庫以指定的密碼加密, 因此程式要連線資料庫時, 都必須告訴 SQL Server 正確的密碼, 才能連線本章範例的產品資料庫並未設定密碼, 所以我們就練習為它加上密碼保護替資料庫加上密碼保護 step 1 在資料庫總管中建立指向 Ch15 資料夾產品資料庫的資料連接, 再開啟其資料庫屬性交談窗圖建立產品資料庫的資料連接 2 在產品資料庫上執行此命令 15-16

建立資料庫資料表與關聯 15 圖 -11 圖 -12 step 3 切換到密碼頁次,

其中 "0" 為數字 ) 3 再輸入一次 4 按此鈕鈕確定使用此簡單密碼, 或是按 01 02

17 建立資料庫資料表與關聯 15 圖 -11 圖 -12 step 3 切換到密碼頁次, 並輸入新的密碼 : 1 選此頁次如果輸入的密碼太簡單 ( 例如 "123" "pass" 之類 ), 則會出現如下訊息, 此時可按因先前未設密碼, 故此處留白 ( 若日後要修改密碼, 則需輸入舊密碼 ) 鈕回前一畫面修改 : 2 在此輸入密碼, 例如 "p@ssw0rd" ( 其中 "0" 為數字 ) 3 再輸入一次 4 按此鈕鈕確定使用此簡單密碼, 或是按 A B C 15-17

新觀念 MicrosoftVisual Basic 程式設計 step 4 設好密碼後, 程式連線資料庫時就必須提供密碼 VB 本身也是個應用程式, 所以現在它就必須知道密碼, 才能繼續讓我們使用資料庫總管操作資料庫, 因此 VB 會自動顯示如下畫面詢問密碼 : 圖 -13 1 輸入剛才設定的密碼 2 勾選此項讓 VB 記住密碼 3 按此鈕完成如前所述, 設定密碼後,

18 新觀念 MicrosoftVisual Basic 程式設計 step 4 設好密碼後, 程式連線資料庫時就必須提供密碼 VB 本身也是個應用程式, 所以現在它就必須知道密碼, 才能繼續讓我們使用資料庫總管操作資料庫, 因此 VB 會自動顯示如下畫面詢問密碼 : 圖輸入剛才設定的密碼 2 勾選此項讓 VB 記住密碼 3 按此鈕完成如前所述, 設定密碼後, 應用程式必須知道密碼才能連上資料庫在 VB 建立指向需密碼的資料庫之資料來源時, 即會詢問是否要將密碼存於應用程式的連接字串 ( 內含要連線的資料庫名稱及連線方式等資訊 ) 之中 : 將密碼存於連接字串 : 此方法的優點是使用較方便, 缺點是較不安全, 因為連接字串會存於程式執行檔中, 用功能較強的文字編輯器開啟執行檔, 即可搜尋到密碼字串 ( 例如 Password = p@ssw0rd ) 不儲存密碼 : 此方法較安全, 但必須自行撰寫程式, 將密碼加到連接字串後, 再進行連線 15-18

19 建立資料庫資料表與關聯 15 圖 -14 測試將密碼存於連接字串, 並連上資料庫的效果 step 1 建立專案 Ch15-01 step 2 建立指向 Ch15 資料夾產品資料庫的資料來源, 由於資料庫已設定密碼, 所以在建立時, 精靈會詢問是否要將敏感性資料 ( 也就是密碼 ) 存於連接字串中, 請選擇要儲存 : 1 選用已加上密碼保護的資料庫檔 A B C 2 選此項, 表示要將密碼資訊存於連接字串 3 按此鈕繼續後續的步驟完成設定 15-19

20 新觀念 MicrosoftVisual Basic 程式設計 step 3 依上一章介紹的方式建立將書籍資料表拉曳到表單產生資料控制項 : 將書籍資料表拉曳到表單產生資料控制項圖 -15 step 4 按以可連上資料庫取得資料 : 執行程式, 即可發現程式因為已知正確的密碼, 所圖 -16 可正常讀到資料 15-20

建立資料庫資料表與關聯 15 圖 -17 我們可也測試一下當程式不知道資料庫密碼時, 連線資料庫會有什麼問題設定不要將密碼存於連接字串, 並測試程式執行效果接著在程式中替連接字串加上密碼設定 step 1 建立專案 Ch15-02 step 2 建立指向產品資料庫的資料來源,

21 建立資料庫資料表與關聯 15 圖 -17 我們可也測試一下當程式不知道資料庫密碼時, 連線資料庫會有什麼問題設定不要將密碼存於連接字串, 並測試程式執行效果接著在程式中替連接字串加上密碼設定 step 1 建立專案 Ch15-02 step 2 建立指向產品資料庫的資料來源, 這次選擇不要將敏感性資料 ( 也就是密碼 ) 存於連接字串中 : 1 選用已加上密碼保護的資料庫檔 A B C 2 選此項, 表示不要將密碼資訊存於連接字串 3 按此鈕繼續後續的步驟完成設定 15-21

22 新觀念 MicrosoftVisual Basic 程式設計 step 3 將書籍資料表拉曳到表單產生資料控制項 : 圖 -18 將書籍資料表拉曳到表單產生資料控制項 step 4 按執行程式, 即可發現程式因為不知道密碼, 所以引發了例外, 在 VB 會看到如下畫面 : 圖 -19 例外訊息指出密碼不對 ( 因為程式根本沒設密碼 ), 所以連線失敗按此鈕停止程式 15-22

23 建立資料庫資料表與關聯 15 step 5 要讓程式可正常執行, 需在連接字串中加入密碼設定請在表單的 Load 事件程序中輸入下列程式 : 01 Private Sub Form1_Load(...) Handles MyBase.Load 02 Try 使用 Try/Catch 處理密碼錯誤連線失敗的例外 03 Dim pass = "p@ssw0rd" A 04 Me. 書籍 TableAdapter.Connection.ConnectionString &= _ 05 ";Password=" & pass B 06 Me. 書籍 TableAdapter.Fill(Me.TestDataSet.product) C 07 Catch ex As Exception 08 MsgBox(ex.Message) ' 顯示例外訊息 09 End ' 結束程式 10 End Try 11 End Sub A 此處將密碼設給變數 pass 若想將程式設計成只有知道密碼的人才能執行, 可使用第 4 章介紹的 InputBox() 顯示輸入交談窗, 並請使用者輸入密碼 B 連線字串存於 TableAdapter 物件的 Connection.ConnectionString 屬性中, 連接字串的結構是以分號 (;) 分隔多個設定資訊, 每一組設定的格式為設定名稱 = 設定值, 例如密碼的設定就是 Password= 密碼字串, 所以此處就用 &= 運算子, 將密碼設定以 ;Password= 密碼字串的形式附加到連接字串最後面 A B C C 此為建立資料控制項時, VB 自動產生的敘述在設妥密碼後, 此敘述才能正常由資料庫中取得所需資料 15-23

24 新觀念 MicrosoftVisual Basic 程式設計 step 6 再次按執行程式, 這次程式即可正常讀到資料 : 圖 -20 可以讀到資料了 15-3 建立資料表與資料欄位 SQL Server 是用二維式的資料表 (Table) 來存放資料資料表的每一橫列即為一筆記錄 (Record, 在 VB 中稱為資料列 ), 每一直行則為同性質資料的集合, 一般稱為欄位 (Field, 在 VB 中稱為資料行 ): 圖 -21 欄位書籍編號書籍名稱價格欄位名稱 1 會聲會影記錄 2 筆記型電腦全應用存於欄位中的資料, 和變數一樣, 需區分為不同的資料型別, 以下就先來認識 SQL Server Compact 的資料型別 15-24

25 建立資料庫資料表與關聯 15 SQL Server 的資料型別 SQL Server 有許多內建的資料型別, 以下摘要介紹幾個常用資料型別 : 資料型別大小可表示範圍整數與浮點數 integer 4 位元組 2,147,483,648 到 2,147,483,647 real 4 位元組 3.40e+38 到 3.40e+38, 最多可表示 7 位數 float 8 位元組 1.79e+308 到 1.79e+308, 最多可表示 15 位數貨幣 ( 可精確到小數第 4 位 ) money 8 位元組 922,337,203,685, 到 922,337,203,685, 日期與時間 datetime 8 位元組 1753/1/1 到 9999/12/31, 時間可表示到毫秒 Unicode 字串 nchar(n) nvarchar(n) 每個字元 2 個 byte, n 表示欄位寬度 ( 字數 ), 其值可為 1~4000, 例如 nchar(20) 表示可放二十個字的欄位同上, 但非固定欄寬, 例如 nvarchar(20) 的欄位, 若只存兩個字元, 該欄位就只佔兩個字元的空間 1~4000 個字元 1~4000 個字元二元資料 (Binary) image 輸入多少資料就佔多少空間 1~ 個位元組 A B C 在此稍微說明一下 real/float 與 money 之間的差異, real/float 相當於 Visual Basic 中的 single/double 浮點數資料型別, 其特色就是所存的數值僅是近似值 ( 參見第 5 章 ) 但 money 則是儲存精確的小數至小數第 4 位, 此外在 money 資料中可含貨幣符號, 而非只有數字, 例如 "$3.5" " 500" ( 英磅 ) 等 15-25

26 新觀念 MicrosoftVisual Basic 程式設計另外 image 型別的英文意思雖然是影像, 但它並非只能儲存圖形及影像資料, 所有非純文字的資料型式, 都可以存於 image 型別的欄位中, 像是 Word 的 doc 文件 Excel 的 xls 試算表等基於版本的限制, SQL Server Compact 支援的資料型別, 比其它 SQL Server 版本缺少幾項特殊的資料型別, 關於 SQL Server 的所有資料型別, 可參見本公司 SQL Server 2005 設計實務一書欄位的屬性除了資料型別外, 欄位也有一些特別的屬性, 這些屬性是用以表示資料欄位的行為及使用上的限制等以下介紹 3 種常見的屬性 : NULL 值 : 日常生活中在填寫一些表單時, 可能有些欄位不一定要填寫 ; 但有些欄位則是一定要填寫例如在網路商店註冊, 其中的性別年齡可能是設為不一定要填寫, 但姓名密碼電子郵件則一定要有, 才能完成註冊當欄位的值為未輸入的狀態, 在資料庫中就是用 NULL 值來表示上例中性別年齡這種可不填寫的欄位, 就稱為允許為 NULL 的欄位; 而名稱密碼電子郵件則是不允許為 NULL 的欄位被設為不允許為 NULL 的欄位, 在輸入資料時, 該欄不可未輸入資料, 否則整筆記錄都不允許存到資料庫中識別碼 : 就像學生有學號可用以識別每一位學生, 許多資料都會用到數字編號來分辨每一筆記錄, 而在整個資料表中, 每個數字編號都只能出現一次, 不可重複要建立此種資料欄位, 可將該欄位設為識別碼欄位 15-26

27 建立資料庫資料表與關聯 15 建立資料表對於識別碼欄位, 在使用者輸入資料時, SQL Server 就會自動將識別碼欄位的值, 依序設為的數值, 不需也不允許使用者輸入, 也可避免編號重複, 此功能我們稱為自動編號主索引鍵 (Primary Key): 書本後面的索引, 可幫助讀者快速找到想看的資訊, 資料表中的索引功能也是如此, 當程式要查某一筆資料時, SQL Server 就會先查看索引, 看看該資料是在哪一個資料頁, 再直接到目的地將資料取出, 因此可加快存取的效率要建立資料表的索引, 最常用的作法, 就是將某個欄位設為主索引鍵, 表示要以此欄位建立唯一性的索引 ( 限制欄位值不可重複的索引 ), 並做為資料表預設的排序方式上一章在 VB 中拉曳資料來源, 即會自動建立資料控制項及相關程式的功能, 如果所拉曳的資料表沒有設定主索引鍵, VB 將無法自動產生更新資料表的相關程式以下我們就來練習如何在 VB 中建立存放書籍資料的資料表, 此資料表的欄位包括 : A B C 欄位名稱資料型別 ( 長度 ) 其它書號 int 主索引鍵識別碼書名 nvarchar(100) 訂價 int 建立儲存書籍資料的書籍資料表 15-27

執行此命令 step 2 此時會出現如下的交談窗, 讓我們輸入資料表的名稱以及定義資料表中的欄位 : 1 輸入資料表名稱

28 新觀念 MicrosoftVisual Basic 程式設計 step 1 在資料庫總管中按 Ch15 練習前的, 展開其下的資料表項目, 並於其上按滑鼠右鈕執行建立資料表命令 : 圖展開資料庫 2 在資料表上按滑鼠右鈕, 執行此命令 step 2 此時會出現如下的交談窗, 讓我們輸入資料表的名稱以及定義資料表中的欄位 : 1 輸入資料表名稱 " 書籍 " 圖輸入第 1 個欄位名稱 " 書號 " 一輸入欄位名稱時, 即會自動出現此部份設定, 稍後仍可修改 15-28

NULL 值 ) 將識別碼設為 True 後, 這兩欄會自動設為 1 4 選擇是 01 02 03 04 05 06 07 08 09 10 11 12 13

29 建立資料庫資料表與關聯 15 圖 -24 step 3 輸入欄位名稱後, 首先要設定其資料型別, 此外我們也要將 " 書號 " 欄位設為主索引鍵及識別碼 : 1 按此鈕 2 選擇 int 3 按此鈕此欄表示系統設定的識別碼值, 每次都是遞增 1 設為主索引鍵後, 此項會自動設為否 ( 因主索引鍵不允許為 NULL 值 ) 將識別碼設為 True 後, 這兩欄會自動設為 1 4 選擇是 A B C 此欄表示系統設定的識別碼值, 是從 1 開始 ( 所以第 1 筆資料的 " 書號 " 欄位會是 1) 5 將此欄設為 True 15-29

30 新觀念 MicrosoftVisual Basic 程式設計 step 4 接著繼續輸入其它欄位的資訊 : 圖 -25 step 5 完成後按鈕即可將資料表存檔 : 圖 -26 按此鈕就會建立資料表剛才建立的資料表鑰匙圖示表示此欄位為主索引鍵展開資料表下的資料行項目, 即可看到資料表欄位 15-30

建立資料庫資料表與關聯 15 輸入資料請建立 Ch15 練習 2 資料庫, 並新增一個 TShirt 資料表, 其中含以下欄位欄位名稱資料型別 ( 長度 ) 其它商品編號 int 主索引鍵識別碼商品名稱 nvarchar(100) 顏色 nvarchar(100) 商品說明 nvarchar(100) 價格 int 照片 image 類別序號 int 已有資料表後,

31 建立資料庫資料表與關聯 15 輸入資料請建立 Ch15 練習 2 資料庫, 並新增一個 TShirt 資料表, 其中含以下欄位欄位名稱資料型別 ( 長度 ) 其它商品編號 int 主索引鍵識別碼商品名稱 nvarchar(100) 顏色 nvarchar(100) 商品說明 nvarchar(100) 價格 int 照片 image 類別序號 int 已有資料表後, 我們就可在其中輸入資料, 以下就複習一下上一章介紹過的資料輸入方式另外要提醒讀者, VB 本身的資料編輯功能, 不支援輸入編修 image 欄位的資料, 這部份只能自行撰寫相關程式來輸入 ( 參見上一章 ) 在資料表中輸入資料 A B C step 1 VB 是以類似 DataGridView 的表格式欄位, 讓我們瀏覽及輸入資料表資料要輸入資料, 請在資料表上按滑鼠右鈕, 執行顯示資料表資料命令 : 在資料表上執行此命令 15-31

新觀念 MicrosoftVisual Basic 程式設計 step 2 接著會出現如下的輸入欄位,

輸入書籍名稱鍵切圖 -27 輸入後, 就會多出這一列此欄為自動編號的識別碼欄位, 不用輸入 (

出現此圖示表示資料尚未寫入資料庫 3 輸入訂價資料自動填入數字 "1" ( 因為是第 1 筆資料 )

32 新觀念 MicrosoftVisual Basic 程式設計 step 2 接著會出現如下的輸入欄位, 在此需依先前定義的資料型別輸入資料, 輸入過程中, 可使用滑鼠選擇欄位, 或是用換欄位 : 1 輸入書籍名稱鍵切圖 -27 輸入後, 就會多出這一列此欄為自動編號的識別碼欄位, 不用輸入 ( 文字呈棕色顯示 ) 一開始沒有資料的欄位會顯示 "NULL" 2 按鍵跳到下一欄 4 輸入後按鍵出現此圖示表示資料尚未寫入資料庫 3 輸入訂價資料自動填入數字 "1" ( 因為是第 1 筆資料 ) 驚嘆號圖示消失, 表示資料已寫入資料庫 5 繼續在此列輸入第 2 筆資料第 2 筆記錄的書號欄會自動填入數字 "2"

33 建立資料庫資料表與關聯 15 請繼續練習輸入數筆資料, 在輸入過程中有輸入錯誤, 也可隨時用滑鼠選擇要修改的欄位, 再進行更正 step 4 只要各欄位的驚嘆號都消失, 即表示資料均已寫入資料庫, 不必另行存檔所以全部資料輸入完畢, 即可按鈕關閉此窗格, 日後若需檢視修改資料, 也可依第 1 步介紹的方式, 重新開啟資料表內容 15-4 建立資料表間的關聯圖 -28 在規劃建立資料表時, 通常我們會將資料適度地分割到不同資料表將資料表分割, 有助於節省儲存空間減少輸入錯誤方便資料修改等優點, 例如假設我們剛才的範例資料表要增加一個書籍類別的欄位 : 書號書名訂價書籍類別 1 Windows 重灌 - 通吃 Vista/XP 32/64 系統 198 作業系統 2 FOXY BT 飆速下載王 198 網際網路 3 正確學會 Photoshop CS3 的 16 堂課 580 攝影美術 % 抓住你的 Photoshop CS3 720 攝影美術類別名稱會重複出現 A B C... 9 Fedora Linux 實務應用 500 作業系統由上例可發現, 許多書籍都屬於同一類別, 使得類別名稱會在資料中不斷重複出現, 因此這樣的設計會有如下的問題 : 15-33

34 新觀念 MicrosoftVisual Basic 程式設計造成空間的浪費輸入時容易造成錯誤 : 由於每本書都要輸入一次類別名稱, 無形中增加輸入錯誤的發生率例如一不小心將程式設計打成程氏設計, 對電腦來說, 就變成兩個不同的類別了維護不易 : 如果有一天公司決定將網頁設計類別更名為網頁技術, 那麼要將幾百筆同類書籍的類別資料全部修改, 也是件頗耗費人力的工作因此在設計資料表時, 就會將重複性的書籍類別資訊分割出來放在另一個類別資料表, 並讓兩者間建立參考關係 : 圖 -29 將類別名稱分割出來, 存於另一個資料表參考關係改成只儲存類別序號 1 Windows 重灌 - 通吃 Vista/XP 32/64 系統 FOXY BT 飆速下載王正確學會 Photoshop CS3 的 16 堂課作業系統 2 程式設計 3 攝影美術 4 網頁設計 5 網際網路 6 硬體玩家在 SQL Server 資料庫中, 這種參考關係稱為關聯, 而書籍資料表中的類別序號, 因為參考到類別資料表中的主索引鍵類別序號, 所以被稱為 Foreign Key ( 外部索引鍵 ) 我們需先設計好兩個資料表的結構, 才能建立其間的關聯, 所以底下練習會先在剛才建立的資料庫中加入一個類別資料表, 並在書籍資料表加入一個類別序號欄, 再建立其間的關聯 15-34

建立資料庫資料表與關聯 15 圖 -30 建立含類別名稱的類別資料表, 並在書籍

3 輸入儲存類別編號的類別序號欄位 01 02 03 04 05 06 07 08

35 建立資料庫資料表與關聯 15 圖 -30 建立含類別名稱的類別資料表, 並在書籍資料表中加入類別序號欄位, 最後再設定其間的關聯 step 1 在 Ch15 練習資料庫中建立新的類別資料表, 並建立下列欄位 : 1 在資料表項目上按滑鼠右鈕, 執行建立資料表命令 2 輸入資料表名稱類別 4 使用 int 型別 5 設為主索引鍵 3 輸入儲存類別編號的類別序號欄位 A B C 6 將識別碼設為 True 15-35

36 新觀念 MicrosoftVisual Basic 程式設計 7 輸入儲存類別名稱的類別名稱欄位 8 使用 nvarchar 型別 9 按此鈕建立資料表 step 2 在類別資料表中加入幾筆類別資料 : 圖 -31 剛才新建的資料表 1 執行此命令 3 完成後按此處關閉窗格 2 輸入幾個類別項目 15-36

37 建立資料庫資料表與關聯 15 圖 -32 step 3 在書籍資料表上按滑鼠右鈕, 執行編輯資料表結構描述命令, 並加入類別序號欄位 : 1 執行此命令現有的欄位 2 在空白欄位上按一下 A B C 3 在此輸入新的欄位名稱類別序號 4 設為 int 5 按此鈕完成 15-37

可自訂 ) 4 這兩欄都選擇 SET NULL ( 後詳 ) 6 選擇類別序號 5 選擇類別資料表 (

38 新觀念 MicrosoftVisual Basic 程式設計 step 4 接著我們要設定兩個資料表間的關聯, 設定方式是由資料表屬性交談窗設定 : 圖在書籍資料表執行此命令 2 選此頁次 3 輸入關聯名稱 ( 可自訂 ) 4 這兩欄都選擇 SET NULL ( 後詳 ) 6 選擇類別序號 5 選擇類別資料表 ( 因在此資料表中, 類別序號是主索引鍵 ) 8 按此鈕此欄維持書籍不動 ( 因在此資料表中, 類別序號是外部索引鍵 ) 7 選擇類別序號 15-38

39 建立資料庫資料表與關聯 15 剛才設定的關聯會出現在此 11 切換到此頁次, 可檢視剛才建立的關聯 9 按此鈕建立關聯 10 按此鈕 A B C 日後若要切斷兩資料表的關聯, 可由此刪除之 12 按此鈕關閉交談窗 15-39

40 新觀念 MicrosoftVisual Basic 程式設計 step 5 建好關聯後, 在書籍資料表輸入資料時, 就不能輸入不在類別資料表中的類別序號這種限制, 在 SQL Server 中稱為參考完整性, 因為若書籍資料表中出現的類別序號不在類別資料表中, 兩者的關聯性就失去意義, 也造成資料的錯誤 ( 因為這樣就不能得知某筆書籍屬於什麼類別 ) 我們可測試此功能: 圖在書籍資料表執行此命令 2 試著輸入非類別資料表中的類別序號, 例如 9 3 按鍵新建的欄位, 資料目前都是 NULL 15-40

41 建立資料庫資料表與關聯 15 因為輸入的數字非類別資料表中的主索引鍵, 所以無法輸入 4 按此鈕 5 輸入合法的類別序號 6 按鍵 A B C 輸入成功! 15-41

42 新觀念 MicrosoftVisual Basic 程式設計參考完整性上面的練習, 我們建立了新的書籍與類別資料表間的關聯, 也體驗了參考完整性在輸入資料時, 所帶來的一項限制 : 在書籍資料表中輸入的類別序號, 一定要已出現在類別資料表之中才能成功輸入但反過來, 如果我們將類別資料表中的某個序號刪除了, 那書籍資料表中, 用到該序號的資料不是變成無效了嗎? 其實在剛才建立關聯的交談窗中, 我們設定了更新規則與刪除規則兩個選項, 它們分別設定當類別資料表中的類別序號被更改和刪除時, 在書籍資料表中參考到該序號的資料要如何處理可設定的選項有 : NO ACTION: 不做任何處理, 亦即可能會讓書籍資料表中出現未定義在類別資料表的類別序號 CASCADE: 表示更改和刪除類別序號時, 要將書籍資料表中參考到該序號的欄位資料也做相同的處理 ( 更改或刪除 ) SET NULL: 表示更改和刪除類別序號時, 要將書籍資料表中參考到該序號的欄位資料都設為 NULL 值 SET DEFAULT: 表示更改和刪除類別序號時, 要將書籍資料表中參考到該序號的欄位資料都設為欄位的預設值 ( 在建立欄位時, 可在預設值欄輸入欄位預設值 ) 15-42

43 建立資料庫資料表與關聯 15 例如我們剛才將關聯的更新規則與刪除規則都設為 SET NULL, 所以若在類別資料表更改某一筆記錄的序號或刪除某一筆記錄, 則在書籍資料表中參考到該序號的欄位, 都會自動變成 NULL 值透過這樣的設定, 我們就能讓 SQL Server 替我們維護關聯, 讓不同資料表的資料能保有一致性, 不會出現參考別的資料表的外部索引鍵, 發生參考不到的情形補充說明資料完整性除了參考完整性外, SQL Server 還提供下列可維護資料完整性的其它完整性, 及相關的維護對策 : 實體完整性 (Entity Integrity): 即維持每筆記錄的唯一性, 例如設定主索引鍵識別碼就是實現實體完整性的方法區域完整性 (Domain Integrity): 即維持欄位資料的正確性, 例如設定欄位的預設值使用外部索引鍵不允許 NULL 值都算是維護區域完整性的方法 A B C 使用者定義的完整性 : 在 SQL Server Compact 以外的 SQL Server 版本, 還可使用諸如預存程序觸發程序等功能, 來實現我們自己所需的資料完整性若要對資料完整性有更深入的認識, 可參考本公司 Microsoft SQL Server 2005 設計實務或其它資料庫專門書籍 15-43

44 新觀念 MicrosoftVisual Basic 程式設計習題 1. 是非題 : ( ) 在 VB 只能連上 SQL Server, 而不能使用其它類型的資料來源 ( ) VB 隨附了 SQL Server Express Edition 和 SQL Server Compact Edition 兩種資料庫軟體 ( ) SQL Server Express 不支援多處理器 ( ) SQL Server Compact 的管理系統會隨著開機時一起啟動 ( ) SQL Server Express 資料庫檔副檔名為.sdf Compact 版資料庫檔副檔名為.mdf 2. 是非題 : ( ) SQL Server 資料庫檔內部, 是以資料頁 (Page) 為單位來管理其使用空間 ( ) 壓縮資料庫檔後, 使用資料庫前需先將檔案解壓縮 ( ) 替 SQL Server Compact 資料庫設定密碼後, 程式必須知道密碼才能連線 ( ) SQL Server 的 float 資料型別, 其大小為 4 個位元組 ( ) SQL Server 的 money 資料型別, 除了儲存數字外也可包含貨幣符號 3. 以下何種資料型別適用於存放圖片資料? (1) float (2) picture (3) nvarchar(100) (4) image 15-44

45 建立資料庫資料表與關聯以下何種資料型別適用於存放非固定長度的字串? (1) float (2) char(0) (3) nvarchar(100) (4) char(max) 5. 關於主索引鍵的描述, 何者錯誤? (1) 主索引鍵欄位一定要設成識別碼欄位 (2) 主索引鍵欄位一定不可以為 NULL 值 (3) 資料表不一定要有主索引鍵 (4) 主索引鍵有助於提升資料存取效率 6. 以下關於資料庫密碼的描述, 何者錯誤? (1) 設定資料庫密碼後, 仍可隨時變更之 (2) 資料庫一旦設定密碼後, 程式必須知道正確密碼才能連線 (3) 在 VB 設定資料來源時, 可選擇是否將密碼存於連接字串中 (4) 若要用程式設定連接字串中的密碼, 可由 DataSet 物件的 ConnectionString 屬性設定之 A B C 7. 建立 A B 二個資料表的關聯時, 在 A 資料表中, 參考到 B 資料表主索引鍵的欄位稱為索引鍵 8. 呈上題, 設定關聯時可指定刪除規則, 將規則設為 SET 表示: 當 B 資料表中的主索引鍵被刪除時, A 資料表中參考到它的欄位就自動設為 NULL 15-45

46 新觀念 MicrosoftVisual Basic 程式設計 9. 請練習在 VB 中建立一個學生資料表, 其中含如下的資料欄位, 並輸入數筆測試資料欄位名稱資料型別 ( 長度 ) 其它學號 int 主索引鍵姓名 nvarchar(100) 身高 decimal 10. 承上題, 建立另一個地區資料表, 並輸入數筆測試資料欄位名稱資料型別 ( 長度 ) 其它地區編號 int 主索引鍵地區 nvarchar(100) 接著在學生資料表中加入一個戶籍地欄位 (int), 並建立它與地區資料表中的地區編號欄位的關聯 15-46

投影片 1

投影片 1 資料庫管理程式 ( 補充教材 -Part2) 使用 ADO.NET 連結資料庫 ( 自行撰寫程式碼以實現新增刪除修改等功能 ) Private Sub InsertButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InsertButton.Click ' 宣告相關的 Connection