第6章 資料庫管理系統 6-1 關聯式資料庫管理系統 6-2 SQL Server資料庫管理系統

Presentation on theme: "第6章 資料庫管理系統 6-1 關聯式資料庫管理系統 6-2 SQL Server資料庫管理系統"— Presentation transcript:

1 第6章 資料庫管理系統 6-1 關聯式資料庫管理系統 6-2 SQL Server資料庫管理系統
6-5 系統目錄 6-6 物件與資料類型 6-7 建立SQL Server資料庫

2 6-1 關聯式資料庫管理系統 6-1-1 關聯式資料庫管理系統 6-1-2 關聯式資料庫管理系統的架構

3 6-1-1 關聯式資料庫管理系統 關聯式資料庫管理系統（Relational Database Management System, 簡稱RDBMS）是一個軟體程式，可以讓使用者定義、新增、更新和管理關聯式資料庫（Relational Database）。大部份關聯式資料庫管理系統都是使用SQL結構化查詢語言，做為標準的資料庫語言來存取資料庫儲存的資料。 E. F. Codd定義的關聯式資料庫管理系統只需滿足兩個條件，如下所示： 能夠存取關聯式資料庫。 提供關聯式代數的選擇（Select）、投影（Project）和合併（Join）等運算。

4 6-1-1 關聯式資料庫管理系統 選擇（Select）: 在關聯表中選取符合條件的值組。
投影（Project） : 在關聯表中選取符合條件的屬性欄。 合併（Join） : 使用相同屬性值集合為基礎來合併兩個關聯表的值組。 關聯表A 選擇(Select) 合併(Join) 投影(Project) 關聯表B 合併欄位

5 6-1-2 關聯式資料庫管理系統的架構-圖例 IBM DB2 甲骨文 Oracle 微軟 SQL Server
關聯式資料庫模型 SQL Server資料庫 管理系統 關聯表(Relations) 資料表(Tables) 屬性(Attributes) 欄位(Fields) 或 資料行(Columns) 值組(Tuples) 記錄(Records) 或 資料列(Rows)

6 6-1-2 關聯式資料庫管理系統的架構-說明1 查詢剖析器（Query Parser）：處理DML語言的指令敘述，檢查語法正確後轉換成低階指令，以關聯式資料庫來說是轉換成關聯式代數，然後送到查詢最佳化模組處理。 查詢最佳化模組（Query Optimizer）：最佳化查詢指令的處理程序，使用查詢最佳化技術參考系統目錄的Meta-data進行「查詢轉換」（Query Transformation），將外部綱要查詢轉換成內部綱要的查詢，或是使用索引加速資料查詢，產生「執行計劃」（Execution Plan）。 程式碼產生器（Code Generator）：使用RDBMS提供的演算法，將最佳化後產生的執行計劃轉換成程式碼。

7 6-1-2 關聯式資料庫管理系統的架構-說明2 DDL編譯器（DDL Compiler）：將DDL語言的指令編譯成資料綱要Meta-data存入系統目錄。 執行引擎（Execution Engine）：當查詢指令在經過最佳化處理後，就是在執行引擎實際執行資料庫的查詢來存取實體資料庫，如果是交易，就交給交易管理來處理。 鎖定管理（Lock Manager）：也稱為「並行控制管理」（Concurrency-Control Manager），負責保證各交易處理能夠分開和獨立執行，提供鎖定功能鎖定資料，只允許一個交易的操作可以存取這些資料。

8 6-1-2 關聯式資料庫管理系統的架構-說明3 交易管理（Transaction Manager）：負責配合記錄管理（Log Manager）和鎖定管理（Lock Manager）處理資料庫的交易，交易管理負責送出SQL指令COMMIT和ROLLBACK來完成交易，並且避免「死結」（Deadlock）情況的發生。 回復管理（Recovery Manager）：負責回復資料庫系統到最後的穩定狀態，主要是透過記錄管理（Log Manager）的交易記錄來執行回復程序。 記錄管理（Log Manager）：負責記錄資料庫系統執行的每一項操作，這些記錄是透過緩衝區管理（Buffer Manager）存入磁碟。

9 6-1-2 關聯式資料庫管理系統的架構-說明4 存取模組（Access Module）：轉換成低階檔案系統指令後，透過緩衝區管理存取資料庫資料。 緩衝區管理（Buffer Manager）：負責電腦記憶體的管理，系統會配置一塊資料庫緩衝區（Database Buffer）記憶體對應磁碟儲存的區塊，換句話說，資料庫的資料都是暫存在緩衝區，需要透過緩衝區管理來存取。 檔案管理（File Manager）：本來屬於作業系統的功能，配置磁碟空間將資料存入外部儲存裝置，不過為了效率考量，資料庫管理系統通常自行配置磁碟空間，將資料存入外部儲存裝置的資料庫，或是從資料庫讀取資料。

10 6-2 SQL Server資料庫管理系統 6-2-1 微軟的SQL Server 6-2-2 SQL Server資料庫系統處理架構

11 6-2-1 微軟的SQL Server 在2008年8月推出SQL Server 2008版，提供多種全新資料類型，支援微軟LINQ（Language Integrated Query）、FILESTREAM檔案資料流和更強XML支援，並且提供多種全新Transact-SQL語法，包含疏鬆欄位（Sparse Column）、GROUPING SETS、篩選索引、MERGE指令和初始變數等。 2010年的SQL Server 2008 R2（版本10.5），增強多伺服器的管理和更多商業智慧功能。在2012年4月推出SQL Server 2012版，支援檔案資料表（FileTable）和順序物件（Sequences），更強的全文檢索搜尋和可用性支援，在T-SQL語言部分，支援更多全新內建函數，例如：IIF()和CHOOSE()邏輯函數，OFFSET和FETCH NEXT分頁查詢，和錯誤處理的THROW指令敘述等。

12 6-2-2 SQL Server資料庫系統處理架構- 系統架構(說明)
一般來說，因為資料庫存取需要使用大量電腦的系統資源，我們通常是將SQL Server安裝在一台專屬或多台電腦作為SQL Server資料庫伺服器。在客戶端電腦是使用網路連接來建立主從架構或分散式資料庫系統架構。

13 6-2-2 SQL Server資料庫系統處理架構- 主從架構(圖例)

14 6-2-2 SQL Server資料庫系統處理架構- 執行個體(說明)
SQL Server 2000之後版本支援同一台實體電腦安裝多個執行個體（Instances），每一個執行個體擁有獨立SQL Server服務和SQL Server Agent，可以提供不同的服務和用途。 簡單的說，執行個體如同是在同一台電腦安裝多個SQL Server資料庫伺服器。

15 6-2-2 SQL Server資料庫系統處理架構- 執行個體(圖例)
例如:安裝SQL Server Express 是在電腦安裝執行個體，Express 版預設安裝成 名為 SQLEXPRESS 的具名執行實體，如果電腦名稱是 MyComputer ，客戶端程式連接執行個體的完整名稱為「 MyComputer\ SQLEXPRESS 」。

16 6-2-2 SQL Server資料庫系統處理架構- 執行個體(種類)
預設執行個體（Default Instance）：預設執行個體是一個不需名稱的執行個體，在每一台電腦只能安裝一個預設執行個體。因為是電腦預設的執行個體，客戶端只需指名電腦名稱，就可以連接此SQL Server執行個體。 具名執行個體（Named Instance）：在電腦安裝的SQL Server不是預設或不具名的執行個體，就是具名執行個體，我們需要替此執行個體命名。因為同一台電腦可以安裝多個具名執行個體，所以連接SQL Server時，除了電腦名稱外，還需指明執行個體名稱。

17 6-2-3 SQL Server的版本-主要版本 企業版（Enterprise Edition）：提供SQL Server 2012版的所有功能，它是一個完備的資料管理和商業情報平台，可以幫助我們建立大型和跨國企業的資料庫系統或分散式資料庫系統，提供進階商業智慧分析、更強大資料轉換功能和更高的可用性（High Availability）。 標準版（Standard Edition）：此版本適合使用在中型至小型企業組織建構完整資料管理和分析平台，提供核心資料庫引擎、報表和資料分析功能。 商業智慧版（Business Intelligence Edition）：提供核心資料庫引擎、完整報表和分析能力，和支援完整的商業智慧，如同標準版，商業智慧版支援較少的CPU數，而且沒有提供完整的可用性、安全性和資料倉儲功能。

18 6-2-3 SQL Server的版本-特殊版本 開發版（Developer Edition）：提供軟體開發商開發建立各種應用SQL Server的資料庫應用程式，其功能和企業版完全相同，不過，只能授權使用在系統開發、展示與軟體測試用途。 Web版（Web Edition）：此版本是針對需要在Windows Server作業系統建立Web環境所提供的解決方案，能夠支援建立低成本、大規模和立即使用的網際網路應用程式。 Express版（Express Edition）：免費版本的SQL Server，限制單一CPU、1GB記憶體和最大資料庫尺寸10GB，可以用來建立桌上型或小型伺服器的資料庫應用程式，作為個人、SOHO族或小型公司的資料庫解決方案。此版本只提供資料庫引擎、用戶端工具、Management Studio管理工具、全文檢索搜尋和部分Reporting Services報表服務功能。

19 6-2-3 SQL Server的版本- Express的版本
SQL Server 2012 Express with Tools：除了資料庫引擎外，還包含Management Studio Express圖形化客戶端管理工具。 SQL Server 2012 Express with Advanced Services：Express進階服務版除了標準版功能外，還包含Management Studio Express管理工具、全文檢索搜尋和Reporting Services報表服務。

20 6-3 安裝SQL Server資料庫管理系統 SQL Server 2012 Express版是微軟資料庫系統的入門產品，在本書是使用Express版來實作資料庫、測試執行SQL查詢指令、建立視界、預存程序、實作安全性管理和備份與還原資料庫。 安裝SQL Server資料庫管理系統就是在安裝SQL Server 2012執行個體。SQL Server 2012 Express with Tools版除了資料庫引擎外，還包含Management Studio Express圖形化客戶端管理工具。

21 6-4 SQL Server的管理工具 6-4-1 SQL Server組態管理員 6-4-2 啟用網路通訊協定
6-4-3 Management Studio整合管理工具

22 6-4-1 SQL Server組態管理員-說明 SQL Server組態管理員可以管理SQL Server服務、設定網路組態和SQL Native Client。 SQL組態管理員管理的項目說明，如下所示： SQL Server服務：顯示SQL Server伺服器提供的服務清單，依安裝和版本而有所不同。 SQL Server網路組態：顯示支援的網路通訊協定清單（分為32和64位元）。 SQL Native Client 11.0組態：顯示SQL Native Client連線的相關設定，包含客戶端通訊協定和別名（分為32和64位元）。

23 6-4-1 SQL Server組態管理員-圖例

24 6-4-1 SQL Server組態管理員-服務說明
SQL Server：SQL Server最主要的服務，啟動此服務才能執行SQL指令存取資料庫。預設【自動】啟動，即當開機啟動Windows作業系統後，就會自動啟動此服務。 SQL Server Browser：此服務可以讓客戶端連接正確的執行個體，而不用指明使用的通訊埠號，預設已停止此服務。 SQL Server Agent：SQL Server代理程式，能夠建立工作排程和產生警示，可以幫助我們建立SQL Server的自動化管理功能，預設已停止此服務。

25 6-4-2 啟用網路通訊協定-說明 在SQL Server組態管理員可以檢視支援的網路通訊協定，在左邊選【SQLEXPRESS的通訊協定】，可以看到支援的通訊協定清單，狀態欄是目前狀態。執行【右】鍵快顯功能表的指令，即可啟用或停用通訊協定。

26 6-4-2 啟用網路通訊協定-種類 Shared Memory（共用記憶體）：一種不需要任何設定的通訊協定，主要是使用在本機電腦，可以在同一台電腦以安全方式讓客戶端程式連接SQL Server執行個體。 具名管道（Named Pipes）：微軟替Windows區域網路開發的通訊協定，源於UNIX作業系統的管道觀念，客戶端是使用IPC（Inter-process Communication）連接SQL Server執行個體，使用部分記憶體來傳遞資訊至本機或其他網路上的電腦。 TCP/IP：此為Internet網際網路使用的通訊協定，可以讓不同硬體架構和作業系統的遠端電腦使用IP位址方式，來連接SQL Server執行個體。

27 6-4-3 Management Studio整合管理工具- 說明
SQL Server Management Studio管理工具是SQL Server圖形介面的整合管理環境，可以使用同一個工具來存取、設定、管理和開發SQL Server元件。在Express版稱為SQL Server Management Studio Express管理工具，一個簡化版的整合管理工具。 請執行「開始/所有程式/Microsoft SQL Server 2012/SQL Server Management Studio」指令啟動 SQL Server Management Studio。

28 6-4-3 Management Studio整合管理工具- 圖例

29 6-5 系統目錄 6-5-1 系統目錄的基礎 6-5-2 SQL Server的系統目錄

30 6-5-1 系統目錄的基礎-說明 系統目錄（System Catalog）的內容是資料庫儲存資料所衍生的一些資料，例如：資料庫系統目前有哪些資料庫、使用者和每一個資料庫綱要的定義資料等。 系統目錄的主要目的是提供資料庫管理系統維護資料庫所需的相關資訊。換句話說，資料庫管理師需要系統目錄提供的資訊，才能正確管理和維護資料庫。

31 6-5-1 系統目錄的基礎-內容1 系統資訊：系統內建的資料型態、緩衝區尺寸和分頁尺寸等系統資訊。
資料庫綱要（Database Schema）：包含每一個關聯表名稱、建立者、欄位名稱、資料型態、限制條件、主鍵和外來鍵等資料。 索引（Indexing）資訊：包含所有索引名稱、結構、搜尋屬性等。 視界（Views）資訊：資料庫定義的視界，它是使用SQL語言CREATE VIEW指令建立的虛擬關聯表，包含視界名稱、欄位、資料型態，其來源關聯表或其他視界。

32 6-5-1 系統目錄的基礎-內容2 程式化物件資訊：目前的SQL語言大都提供程式化功能，一些程式化物件預存程序（Stored Procedure）和觸發程序（Trigger）的相關資訊，包含程序名稱、相關關聯表等資訊也是儲存在系統目錄。 安全管理資訊：儲存使用者帳號、密碼和使用權限，即允許使用者存取哪些關聯表、是否可以新增、更新或刪除值組，是否可以建立資料庫、關聯表、更改關聯表綱要、匯出或備份資料庫等權限的資料。

33 6-5-2 SQL Server的系統目錄-系統資料庫
關聯式資料庫管理系統的系統目錄在SQL Server是儲存在系統資料庫之中，我們可以使用目錄檢視來查詢SQL Server的系統目錄。 在安裝好SQL Server執行個體後，SQL Server預設建立數個系統資料庫（System Databases），這是一些系統所需和維持SQL Server正常操作的資料庫。 master系統資料庫 Resource資料庫 model資料庫 msdb資料庫 tempdb資料庫

34 6-5-2 SQL Server的系統目錄- master系統資料庫
master資料庫記錄SQL Server所有系統層級的資訊，包含：每位登入的使用者帳戶、系統組態設定、其他資料庫的狀態和使用者資料庫初始化資訊的檔案位置。 SQL Server的master資料庫是一個十分重要的系統資料庫，如果master資料庫損壞，SQL Server就沒有辦法正常運作。所以資料庫管理者一定要定時備份master資料庫。

35 6-5-2 SQL Server的系統目錄- model資料庫
model資料庫是建立SQL Server使用者資料庫的範本，內含使用者資料庫的基本關聯表綱要，當在SQL Server建立資料庫時，就是直接複製model資料庫來建立新資料庫。

36 6-5-2 SQL Server的系統目錄- msdb資料庫
msdb資料庫是提供給SQL Server代理程式（SQL Server Agent）使用的資料庫，其內容是儲存警告（Alert）或作業（Jobs）等排程資料，例如：資料庫備份的相關工作排程。

37 6-5-2 SQL Server的系統目錄- tempdb資料庫
tempdb資料庫包含所有暫存資料表和預存程序，可以儲存目前SQL Server使用中的暫存資料。例如：SQL Server執行查詢時產生的一些中間結果。tempdb資料庫是一種全域資源，連線SQL Server的所有使用者都可以使用此資料庫來儲存暫存資料表和預存程序。

38 6-5-2 SQL Server的系統目錄- 目錄檢視
SQL Server系統目錄是使用一組檢視（即視界）來提供中繼資料（Meta-data）的資訊，可以讓應用程式，透過檢視來取得連接資料庫物件的結構與屬性。 在SQL Server查詢系統目錄，因為原來的系統資料表已經隱藏，所以新版是建議使用系統檢視下的目錄檢視（Catalog Views）來查詢，這是一些以sys結構描述開頭的系統檢視。

39 6-6 物件與資料類型 6-6-1 資料庫物件 6-6-2 資料類型

40 6-6-1 資料庫物件-說明 SQL Server系統或使用者資料庫都是由物件組成，在Management Studio的「物件總管」視窗可以檢視資料庫的物件清單。例如: School資料庫(後續建立的)

41 6-6-1 資料庫物件- 資料庫常用物件的說明 物 件 說 明 資料庫圖表 使用圖形方式來顯示關聯表綱要 資料表
物 件 說 明 資料庫圖表 使用圖形方式來顯示關聯表綱要 資料表 即關聯表(Relations) 檢視 即視界(Views) 的虛擬關聯表 同義字 替本機或遠端伺服器的資料庫物件件立別名 可程式性 ㄧ些可程式化的相關物件 儲存體 全文檢索、資料分割配置和函數等相關物件 安全性 安全性管理的相關物件

42 6-6-1 資料庫物件-可程式性物件說明

43 6-6-1 資料庫物件-安全性物件說明 物 件 說 明 使用者 角色 結構描述 非對稱金鑰 憑證 對稱金鑰
物 件 說 明 使用者 資料庫的使用者，即允許存取資料庫的使用者清單 角色 角色是將使用者資料庫的權限以扮演的角色來進行分類，我們可以直接使用角色快速指定使用者的角色 結構描述 結構描述(Schema) 可以替資料庫物件新增分類名稱，i.e. SQL Server資料庫物件名稱全名是「結構描述.物件名稱」 非對稱金鑰 資料加密建立的非對稱金鑰 憑證 資料加密建立的憑證 對稱金鑰 資料加密建立的對稱金鑰

44 6-6-2 資料類型-說明 SQL Server資料類型（Data Type）也稱為資料型別或資料型態，相當於是關聯式資料庫模型的定義域（Domains），它是下列值的集合，如下所示： 空值（NULL Value）：沒有指定或未知值。 非空值（Non-NULL Value）：字元、字串、數值、日期/時間、布林值和二進位資料。 SQL Server預設資料類型是Transact-SQL語言使用的資料類型。Transact-SQL允許使用者擴充資料類型來自訂資料類型。

45 6-6-2 資料類型-預設資料類型

46 6-6-2 資料類型-日期/時間資料類型1 Transact-SQL日期/時間的資料類型和ANSI有些不同，在ANSI-SQL 92提供三種日期/時間的資料類型，如下表所示：

47 6-6-2 資料類型-日期/時間資料類型2 SQL Server的Transact-SQL提供SMALLDATETIME和DATETIMESQL Server的Transact-SQL提供smalldatetime和datetime資料類型，都可以儲存ANSI-SQL的date、time和timestamp三種資料類型的日期/時間資料。 在2008之後版本新增ANSI-SQL的date與time，和datetime2、datetimeoffset共四種日期/時間的資料類型。

48 6-7 建立SQL Server資料庫 6-7-1 在SQL Server建立資料庫 6-7-2 執行SQL指令碼檔案
6-7-3 資料庫的卸離與附加

49 6-7-1 在SQL Server建立資料庫- 在Management Studio建立與刪除資料庫

50 6-7-1 在SQL Server建立資料庫- 使用SQL指令建立與刪除資料庫
Transact-SQL語言的CREATE DATABASE指令（這並不是ANSI-SQL標準的指令）可以建立資料庫，其語法如下所示： CREATE DATABASE database_name 上述語法可以建立名為database_name的資料庫。Transact-SQL刪除資料庫是使用DROP DATABSE指令，其語法如下所示： DROP DATABASE database_name 上述語法的database_name是刪除的資料庫名稱。

51 6-7-2 執行SQL指令碼檔案 對於現存的SQL指令碼檔案，我們可以在Management Studio直接開啟檔案來執行SQL指令。

52 6-7-3 資料庫的卸離與附加-說明 SQL Server可以同時管理多個資料庫，為了避免沒有使用的資料庫平白佔用系統資源，或者需要將資料庫移至其他SQL Server。我們可以先卸離指定資料庫後，再在其他SQL Server將它附加回去。

53 6-7-3 資料庫的卸離與附加-卸離資料庫 卸離資料庫並不是刪除資料庫，卸離只是將資料庫定義資料從master資料庫刪除，如此使用者就可以複製資料庫的.MDF（Master Data File）資料檔和.LDF（Log Data File）交易記錄兩個檔案。

54 6-7-3 資料庫的卸離與附加- 附加資料庫 在複製好資料庫檔案後，我們可以在另一台電腦的SQL Server使用附加（Attach）方式來回存資料庫。

55 End