SQL伺服器管理

SQL伺服器管理

黃添修 93.10.29修訂

李忠憲 94/11/4刪改

---

　

一、校務行政SQL2000的安裝

點選【自訂安裝】(若是以典型安裝的話，在裝完後以ODBC測試皆可連線成功；但以校務行政軟體去連時可能會失敗)

輸入密碼(空白或自訂)

　

【定序方式】 Chinese_Taiwan_Stroke【排序方式】更改為【二進位編碼】

安裝後請重新開機.

重開機後請更新SQL2K SP3a(http://www.microsoft.com/sql/downloads/2000/sp3.asp)

  

二、SQL防護之道

• 保持永遠最新SERVICE PACK版本：從上述新聞我們可以得知，當我們使用微軟SQL Server 2000開始就必須面臨它成為眾矢之的(駭客的攻擊目標)的威脅，而我們惶惶子民們並非功力高深的電腦專家，無法做到兵來將檔、水來土淹的英雄氣概(當然電腦高手例外，我可沒辦法哦)，那麼就該做一個勤奮的補丁工，修正到應該有的SERVICE PACK版本(目前為SP3a)，以降低減少被駭的機會。當修正完SP3後，可在SQL Query Analyzer 的 [Query] 視窗中鍵入 SELECT @@VERSION 或 SERVERPROPERTY(‘ProductVersion‘)來確定版本。

• 資料庫備份：同一份日期的資料庫備份，永遠要有二至三份以上，分別放置於不同儲存媒體上，以利有意外狀況時做資料庫的還原恢復。

• 檔案系統選擇

  • 安全性與執行效能考量：NTFS

    • Ex. Format E: /FS:NTFS /A:64K – 僅適用於 .mdf(主資料庫) 與*.ndf (次資料庫) 檔案。

    • 絕對不要做壓縮。

  • 關閉不必要的服務

    • IIS Web Server、SMTP Virtual Server …。

    • 不要與有大量網路資料流量的服務裝在同一台機器中。

      • 網域主控站（DC、PDC）、Exchange Server、MSMQ(Microsoft Message Queue Server 訊息序列服務)、MTS(Microsoft Transaction Server 元件/磋商服務)、FTP Server 、Printer Server …。

      　

• SQL 安全認證方式有二(在機器名稱上按右鍵點選內容選安全性就可以看見)

  

  • WINDOWS 認證：就是採用WINDOWS 2000的網域認證的特性，也就經過網域中的 DC (Domain Control) 上 AD (Active Directory) 的認證通過就可以存取網域中的任何經信任SQL伺服器。

    • 好處：可以設定使用者的密碼長度、密碼的組成、登入失敗時做如何處理、不必多記一組SQL帳號和密碼、可以用群組的觀念快速對不同使用者授與存取相同的SQL伺服器的資源能力。

  • SQL Server 和 WINDOWS 混合認證：也就是經過 WINDOWS 2000 的網域或是SQL Server認證通過者就可以存取SQL伺服器的資源。

    • 好處：非WINDOWS 2000 的網域的使用者，只要通過SQL Server認證就可以使用，便利性較高。

  • 目前的校務行政是採用SQL Server 和 WINDOWS 混合認證。

  • 在各校的校務行政主機SQL的sa密碼是由宜佳資訊設定，當無法得知sa密碼時，改用WINDOWS 認證，以administrator身份即可直接登入，但在日前發版的校務行政4.0版時，業已要求各校改掉sa的密碼，不妨自己做一下密碼的修正更新。

  　

• 帳號管理：資料庫的建立者給予db_owner，使用者或群組則給予public的角色就可以了。

  • 帳號的新增：在安全性的登入選項去做 → 新增登入，以校務行政的帳號為例：(SYSUSER和SYSVIEW)

    

    • 一般選項：名稱為 SYSUSER (大寫)，驗證請選SQL的帳戶驗證並輸入密碼，改變資料庫的預設值(預設為master)為 JSDB。

      

    • 伺服器角色：授與登入者在全伺服器的安全權限，若非必須可以不設。

      

　

　

固定伺服器角色	說明
sysadmin	在 SQL Server 中執行任何活動。這個角色的權限會跨越所有其他的伺服器固定角色。
serveradmin	設定伺服器的所有設定值。
setupadmin	新增/移除連結伺服器，並執行部分系統預存程序，如 sp_serveroption。
securityadmin	管理伺服器登入。
processadmin	管理 SQL Server 執行個體中執行的處理。
database creators	建立與更改資料庫。
diskadmin	管理磁碟檔案。
bulkadmin	執行 BULK INSERT 陳述式，亦即可以執行大量插入作業。

• 資料庫存取：請點選 JSDB 資料庫， JSDB 資料庫角色請勾選(public和dbo_owner，若是SYSVIEW只要用public就可以)。

• 資料庫角色其權限與內容。

  • public：基本的權限設定。

  • db_owner：擁有資料庫內所有權限，也就是說其可以執行所有資料庫角色的活動，以及資料庫中其他維護與組態活動。這個角色的權限會跨越所有其他的資料庫固定角色。。

  • db_accessadmin：可以新增、刪除任何使用者(Windows NT 4.0 或 Windows 2000 群組和使用者，以及資料庫中的 SQL Server 使用者)、群組及自定角色。

  • db_securityadmin：可以設定使用者存取，用於管理角色與 SQL Server 2000 資料庫角色的成員，也可以管理陳述式與資料庫中的物件權限。。

  • db_ddladmin：可以新增、修改及刪除資料庫內表格等任何物件。

  • db_backupoperator：擁有備份、還原資料庫的權限。

  • db_datareader：查看資料庫中所有使用者資料表中的所有資料，也就是可以讀取任何資料表格的內容。

  • db_datawriter：可以新增、刪除及修改資料庫中所有使用者資料表的表格內容。

  • db_denydatareader：不允許讀取任何資料表格的內容。

  • db_denydatawriter：不允許新增、刪除及修改資料表格內容。

• 一般而言，只有資料庫的建立者給予db_owner，而資料庫的使用者或群組則給予public的角色。

• SQL SERVER 安裝完妥後，會自動預設有下列幾個資料庫：

  • master：負責記錄所有有關SQL SERVER的系統資訊(登入帳戶、系統組態設定，以及目前有哪些資料庫在系統中。

  • model：範本資料庫(當新增一個資料庫時，SQL SERVER便以model為範本，將其內容複製到新增資料庫中)。

  • msdb：專供SQL SERVER代理程式所使用的資料庫(放有關警告、作業、資料備份、資料複製與資料維護等排程事宜)。

  • Northwind：範例資料庫(食品進出口公司的資料庫，包括人員、產品、客戶、銷售等資料庫)。

  • pubs：書籍出版公司的資料庫系統。

  • tempdb：存放所有暫時的資料表及預存程序，並提供SQL SERVER放目前使用中的資料表，其檔案大小會自動成長，但當SQL SERVER重新啟動時，會將其內容全部清掉，並以預設的大小重新建立。

• 對於管理者 sa 的密碼管理宜增加長度到 128 字元，且絕對不可以將 sa 的密碼設為空白或過於簡單，或是任意外漏密碼內容，一般除了 sa 是加入到 System Administrators 固定伺服器角色中，其他的 SQL Server 使用者帳號，則端視其使用何資料庫而給予適當的權限。

• 使用 SQL Profiler 監看(Microsoft SQL Server →  Profiler)

  • 允許系統管理員監看SQL Server執行個體中的事件的圖形化工具。

  • 您可以擷取並儲存每一個事件的相關資料至檔案或 SQL Server 資料表中，以供稍後分析。

  • 例如，您可以監看系統以便查看哪些使用者的執行動作妨礙效能。

  • 請利用不同機器來監控 SQL Server，若在同一台將會嚴重拖垮SQL Server的效能。

  

　

三、資料庫備份與資料匯入、匯出

　

資料庫備份可以分做手動與自動化排程兩種：

• 手動備份做法：(用於更新程式或升級資料庫...等動作之前)

  • 在資料庫上方按右鍵 → 所有工作 →  備份資料庫 →  新增 →  檔案名稱(資料庫備份所要放置的位置，例如：c:\SQL資料庫備份\JSDB920209.dat) →  確定。

    

    

    　

    • 一般：備份選擇資料庫 - 完整備份，目的地中備份目的移除目前不做的選項，覆寫部分選擇覆寫現有的儲存媒體。

    

    • 選項：選擇完成時確認備份，其他的選項皆不勾選。

    

    • 當備份完成後，可在c:\SQL資料庫備份\JSDB920209.dat發現。

    

    

• 自動備份做法：(用於每天放學後自動備份，以防意外狀況發生時，可以立即做災難復原)

  • 首先進SQL Enterprise Manager, 並連進所要備份的資料庫.。 

  

  • SQL伺服機器名稱內的管理 → 資料庫維護計劃新增維護計劃。

  

  •   點滑鼠右鍵,點選新增維護計劃，出現畫面，並按下一步。

   

  •   選擇資料庫時，請點選所有資料庫，然後按下一步(若是只想單一備份資料庫則是指定該資料庫名稱，例如：JSDB)。

  

  • 此畫面直接按下一步，不做任何設定。

  

  ※重新組織資料及索引表：當資料及索引頁被填滿時，做搜尋一筆資料時會花費較長時間；而重新組織資料及索引表可以重做索引，以加速檢索用。
  ※更新查詢最佳化所使用的統計資料：當多個資料表同時要去做檢索時，會萃取成一個View表，以利檢索；而勾選此項就是在重做多個表格的交叉檢索，以萃成一個View表，以利加速檢索之用。
  ※從資料庫檔案中移除尚未使用的空間：即在移除資料庫中的未使用的磁碟空間。
  ※資料庫維護計劃並不只是適用於備份而已，也可以做資料檢索的加速使用。

• 在資料庫完整性檢查，勾選檢查資料庫完整性及備份之前先執行這些檢查，然後在排程選項上按變更，來設定備份的星期。

• 將時間變更為星期二、星期三、星期四、星期五、星期六、其他不必更動，然後點選確定回到上一個畫面，並按下一步。(如此設只要是配合校務資料而定，若是資料是可以透過Internet更新的話，則考量每天排程備份)

• 在指定備份磁碟目錄，請點選使用這個目錄，並指定一個已存在的目錄(例如：C碟的SQL資料庫自動備份，最好是在另一顆硬碟上)，然後如圖點選為每個資料庫建立個別的子目錄和指定移除檔案的期限為四週(當然是硬碟大小而定) → 下一步。

• 勾選將交易記錄檔的備份視為維護計劃的一部份及備份完成後確認備份的完整性，在排程選項按 變更，將時間變更為星期二、星期三、星期四、星期五、星期六(如此設只要是配合校務資料而定，若是資料是可以透過Internet更新的話，則考量每天排程備份)，其他不必更動，然後點選確定回到上一個畫面，並按下一步。

• 在指定交易紀錄檔備份磁碟目錄，請點選使用這個目錄，並指定一個已存在的目錄(例如：C碟的SQL資料庫自動備份\db，最好是在另一顆硬碟上)，然後如圖點選為每個資料庫建立個別的子目錄和指定移除檔案的期限為四週(當然是硬碟大小而定) → 下一步。

• 此畫面直接按下一步。

• 最後給一個計劃名稱(例如：A00資料庫維護計劃)，並確認計劃內容， 然後按完成。

• 這個警告訊息是告訴我們要設定啟動SQL Server Agent(應其預設值是停止的，必須加以設定成開機就啟動)，這樣維護計劃才會自動執行，最後按確定產生計劃；並可發現有A00資料庫維護計劃產生在右側視窗。

• 設定SQL Server Agent 方法是請點選視窗右下角的SQL Server 圖案，開啟 SQL Server 服務管理員。

• 將服務選擇成SQL Server Agent，因為其預設值是開機不啟動，所以要勾選開機時自動啟動服務，並按三角形啟動執行SQL Server Agent 。

　

• 隔天到指定的目錄中看是否有照計劃備存檔案，確認是否執行；一星期後在到指定的目錄中看是否有照計劃備存檔案，確認設定是否完全執行成功，但是如此備份只不過是備份在本機而已，為確保安全我們會自動備份到另一台機器。

  • 建一個copy.bat檔，內容如下：
    @echo off
    xcopy c:\SQL資料庫自動備份\*.* /Y /E \\sppspdc\sql_temp 
    @echo on
  • 在控制台的排定工作，將copy.bat做成每天晚上12:50自動執行(避過SQL資料庫維護計劃的時間，且一定要在其執行完之後)

  

資料匯入、匯出

• 在SQL Server中有提供DTS的資料匯入/出精靈工具，以利資料的匯出、匯入以及轉換異質資料的功能。現將資料匯入或匯出方式與做法說明如下：

• 資料的匯入方式，以Microsoft Access資料庫匯入JSDB資料庫為例檔說明之。

  • 萬芳的最新消息的mdb檔為board1.mdb，其資料格式如下：

  

  • 現將此一以board1.mdb匯入SQL Server中，成為NEWS的資料表；先在SQL Server機器名稱(A00) → 選擇資料庫(JSDB) → 新增資料表。

  

  • 將資料表的格式設計如下，原則上資料行的名稱和原資料表相同為宜，當然也可不同，處理就比較麻煩，並將資料表名稱存成NEWS。

  

  

  • 在SQL Server機器名稱(A00) → 選擇資料庫(JSDB) → 新增資料表 → 校務行政的資料庫(JSDB) → 按滑鼠右鍵 → 選擇所有工作 → 選擇匯入資料。

  

  • 此刻畫面會出現資料轉換服務 - 匯入/匯出精靈 → 下一步。

  

  • 選擇資料來源為Microsoft Access，並給予檔案位置與名稱，若資料庫有做密碼保護，就必須輸入帳號密碼解除 → 下一步。

  

  

  • 匯入檔案的目的地為SQL Server，給予伺服器名稱(A00)同認證方式及資料庫位置(JSDB) → 下一步。

  

  • 決定為來源資料庫的資料表和檢視表匯入，還是合乎SQL查詢結果的才匯入 → 下一步。

  

  • 勾選來源資料表和匯入哪一個資料，若有特殊需求則按轉換去設定 → 下一步。

  

  

  

  • 當設定後，在此是決定為立即執行匯入或是排程固定時間匯入 → 下一步。

  

  • 在此讓您再次檢視剛才的設定是否有誤，以便再次更改或按完成去執行匯入。

  

  

  

  • 匯入完成後我們可以開啟NEWS資料表，看到下列的結果。

  

• 資料的匯出方式，以校務行政的SEMP資料表格匯出成Excel檔說明之。

  • 在SQL Server機器名稱(A00) → 選擇資料庫 → 校務行政的資料庫(JSDB) → 按滑鼠右鍵 → 選擇所有工作 → 選擇匯出工作。

  

  • 資料轉換服務 - 匯出 / 匯入精靈的宣告畫面會出現在螢冪 → 下一步。

  

  • 因為資料是從SQL Server匯出的，所以資料來源為 Microsoft OLE DB Provider for SQL Server ，伺服器為各校的SQL Server名稱(例如：A00)，採用哪一種認證(WINDOWS或SQL)，現以SQL的sa帳號及密碼輸入認證，並選用JSDB資料庫。(此項是基本上會帶出上述值，若無變動可以不必改)

  

  • 匯出要在資料來源的地方，選擇匯出何種資料格式；其他選項基本上是可以不用變的 → 下一步。

  

  • 目的地為剛才所選的資料格式名稱，給予存檔的位置與檔案名稱後按下一步。

  

  • 此刻會讓你來選擇是整個資料表格內容複製出來或是利用SQL指令查詢合乎結果的資料才輸出，當選擇完妥後按下一步。

  

  • 此刻會出現讓你選擇所要匯出的資料表格[可以一個(含)以上]，即到何目的地同做何種資料轉換狀況的改變。

  

  • 當選擇完上述條件後，再讓你做立刻執行或排程時間固定去轉換資料表的匯出工作 → 下一步。

  

  • 當設定好DTS匯入 / 匯出後，會將剛才設定值秀出，讓你判斷有無錯誤；若有誤則按上一步去重新設定，若有則按完成去執行工作。

  

  • 此時會秀出轉換的歷程，當轉換完畢會秀出成功的畫面。

  

  

  • 開啟剛才所匯出的SEMP表格，內容如下：

  

  　

四、新增資料庫、資料表與欄位設定

　

資料庫的觀念學習

• 資料庫的網路架構

  • 單機架構

  • 大型主機 / 終端機模式

  • 主從架構

  • 分散式架構

• 資料庫的類型

  • 階層式資料庫(Hierarchical Database)

  • 網狀資料庫(Network Database)

  • 關聯式資料庫(Relational Database)

  • 物件導向式資料庫(Object Oriented Database)

• 資料庫的效能調教-- 何時開始呢？ 

  • 就由系統分析、資料庫設計開始

  • 資料庫設計的考量：

    • 以最有效率的方法將資料存放在硬碟上：成本上的考量

    • 最短回應時間來儲存與取得資料：高效率的考量

• 正規化：

  • 特性

    • 去除重複不必要的資料

    • 資料的完整性易於維持

  • 缺點：

    • 資料分散到數個表格上，存取時必須進行關連（Join）

    • 關連（Join）會造成效能的下降

    • 若有過多的查詢需要超過四個以上的 Join ，考慮作反正規化(denormalized)

資料庫正規化(或稱標準化)

所謂的資料庫正規化就是在一個資料庫中去減少重複或多餘的資料的過程，亦即減少一個資料庫中多餘的資訊；因為一個資料庫會有異常性其原因往往是其資料庫的內容有不良的資料庫結構所導致，因此資料庫的正規化有其必要。在沒有正規化下的資料庫中，資料會沒有理由的出現在一個或多個資料表中，如此一來對於資料庫的安全性、磁碟空間、資料查詢速度、資料庫更新、資料庫備分及重要的資料完整性皆有不良的影響。

在設計資料庫時，正規化（Normalize）是必經過程，它可以儘可能減少資料重覆問題。當資料庫上線後，才可能發現某些查詢慢的離譜，這時可以“選擇性”將某些必要的欄位做反正規化（Denormalize），以期達到提高查詢的速度，但前題必須在不造成資料庫系統錯亂的原則下進行。

正規化的目的在於將資料的重覆降至最低，一旦消除了大部分的資料重覆問題，卻衍生出另一個問題：即資料查詢速度變慢！通常正規化，我們會將資料表由一個細分成數個表格，若要找出其中一筆資料，很可能需要join相關表格，而“join”動作，將直接影響系統效率，造成查詢速度變慢；相對的要新增資料、修改或刪除資料時，也要找出相關表格而予以新增、修改或刪除，更要確保交易時，資料是全部新增／修改／刪除成功，倘有一個表格作任何動作失敗，則須rollback或回至安全點（checkpoint）的狀態中，此作業牽涉到資料庫的安全與保護政策。

故所謂的正規化就是：當我們將自己所設計的資料庫中所有的資料細分成多個資料表格，且使每一個資料表格皆只描述一種事實為止，其間的過程就稱為正規化。

當設計一個資料庫被設計時，使用者的需求是被視為最先的考量點，畢竟資料庫的設計是為了提供使用者能便利的使用為目的，就像校務行政資料庫是提供教師可以透過使用者的前端工具(按地球圖示)啟動校務行政程式來開啟遠端SQL資料庫，因此設計資料庫時，我們是依使用者的需求來設計資料庫，讓使用者可以操作十分簡易及達到最佳的效能。

當使用一個好的邏輯設計後，資料庫的實體結構是看不到的

正規化(Normal Forms )的步驟  (引用淺談資料庫正規化 台北縣教育網路中心 劉仁宇小姐)

正規化的理念包括下列四點：

• 欄位唯一性 (Field Uniqueness)

• 主關鍵欄位 (Primary Key)

• 功能關聯性 (Function Dependence)

• 欄位獨立性 (Field Independence)

STEP 1：將表格中的變動項目展開來，其結果即為一級正規化。

STEP 2：其次移去部分相關性(Partial dependency)得到二級正規化。

STEP 3：再來消除關連間之遞移相關(Transitive dependency)最後得到三級正規化。

正規化的步驟如下：

第一正規化（１ＮＦ）形式

第一正規化的目的是將所有的資料分成資料表(Table)的若干邏輯化單位(Logical Units)，當資料表設計完成後，主鍵(Primary )會被分配到多數或所有的資料中。而主鍵是使資料表中的每一列資料為一個或多個的唯一資料欄。

第一正規化的表格必須合以下條件：
1. 必須為row-column的二維式table
2.  table的每一筆資料（row）只描述一件事情
3. 每一欄位只含有單一事物的特性（欄位的唯一性）
4. 每一筆row的欄位內只允許存放單一值
5. 每個欄位名稱必須是獨一無二的
6. 沒有任何兩筆資料是相同的
7. row或欄位的先後順予是無關緊要的

→　結論：消除重覆性資料

第二正規化（2ＮＦ）

第二正規化的目的是將部份相依於主鍵的資料取出 ，並將該資料存入另一個資料表中。

第二正規化的表格必須合下面條件：

移去部分相關性

→　結論：消除功能相依（Functional Dependency）

所謂功能相依是指表格和表格之間的相互關係，若某個表格中有兩個欄位Ａ及Ｂ，當Ａ欄位值可推導出Ｂ欄位值，稱功能相依性。
即若一關連R，其屬性Y功能相關於屬性X，記作R.X→R.Y；若且唯若R中有二個X值相同時，其Y值亦相同。

第三正規化（3ＮＦ）

第三正規化的目的是將不相依於主鍵的資料移除。

第三正規化的表格必須合下面條件：

消除遞移相依（Transitive Dependency）

所謂遞移相依是指在一個表格中，如果某一欄位值可決定其他欄位值，但這些欄位中又存在某一欄位可以決定剩餘欄位值，稱遞移相依性。若有上述情況存在．如果在刪除資料時，可能會造成其他資料損毀。
一個FD若R.A→R.B且R.B→R.C則，R.A→R.C成立，此種相關性稱為遞移相關。

BCNF正規化（BCNF）

一般資料庫進行至第三正規化時，幾乎可以滿足資料修正異常狀況，倘若仍有異常出現，則需再進行BCNF正規化，BCNF請參考所列書籍。

正規化 的好處

1. 組織比較完整的資料庫。
2. 縮減多餘的資料(即去除多餘的資料)。
3. 在資料庫裡面的資料是是一致性的。
4. 更便利於資料料庫的設計。
5. 較好處理資料庫的安全性。(可做分區授權給相關人使用)

正規化 的缺點

正規化的資料庫的實質上有其缺點：也就是會降低資料庫的效能。當資料庫的效能降低時，必須去了解其涉及許多的因素：如CPU的使用、記憶體使用、輸入及輸出(I/O)。正規化的資料庫比起反正規化的資料庫，需要較多的CPU、記憶體、I/O，以利處理交易和資料查詢；此乃因為它必須先定為所要求的 資料表，再由這些資料表去做合併資料，以獲取所要求的資訊或處理所想要的資料。

反正規化（Denormalize）

有時，在詳細的正規化，反而會造成資料處理速度上的困擾，因此在執行速度考量之下，有時候不得不做適當的反向正規化，當我們在進行資料庫正規化的同時，可能也要測試系統執行效率，當效率不理想時，必須做適當的反正規化，但必須小心控制反正規化所造成的資料重覆性問題，以期達成最佳的資料庫設計。

所謂的「反正規化」就是處理正規化資料庫的過程，並允許將被控制多餘資料表結構予以修改。反正規化資料庫的理由是為了增進效能，但是反正規化資料庫和尚未正規化資料庫是不一樣的。

反正規化資料庫是一種 將資料庫內正規化層次下降一些的過程，反正規化乃在重新組合分離的資料表，或在資料表內建立重複的資料，以降低在擷取資料時，所需要合併資料表的數量。以降低CPU的使用、記憶體使用、輸入及輸出(I/O)，因而使其效能提高了。但是反正規化資料庫，也因原分開的資料表再次的合併起來。造成多餘的資料再次的產生了。

正規化（Normalize）的實例

　

原始資料

員工 編號 ten_no

員工 姓名 ten_name

部門 編號 dept_id

部門 名稱 dept_name

課程 編號1 train _id1

課程 名稱1 train_name1

訓練 日期 train _date1

課程 編號2 train _id2

課程 名稱2 train_name2

訓練 日期 train _date2

課程 編號3 train _id3

課程 名稱3 train_name3

訓練 日期 train _date3

T0001

張一

101

教務處

SQL

SQL.Server_Admin

910203

VB

Visual Basic

920506

WEB

WEB.Server_Admin

920702

T0002

張二

101

教務處

WEB

WEB.Server_Admin

920702

ACC

Access

920908

T0003

張三

102

總務處

SQL

SQL.Server_Admin

910203

T0004

張四

103

訓導處

C++

Visual C++

920809

SQL

SQL.Server_Admin

910203

VB

Visual Basic

920506

T0005

張五

102

總務處

VB

Visual Basic

920506

　

↓(第一正規化)

　

老師資料表 員工編號 ten_no 員工姓名 ten_name 部門編號 dept_id 部門名稱 dept_name T0001 張一 101 教務處 T0002 張二 101 教務處 T0003 張三 102 總務處 T0004 張四 103 訓導處 T0005 張五 102 總務處

老師訓練課程紀錄表 員工編號 ten_no 課程編號 train_id 課程名稱 train_name 訓練日期 train_date T0001 SQL SQL.Server_Admin 910203 T0001 VB Visual Basic 920506 T0001 WEB WEB.Server_Admin 920702 T0002 WEB WEB.Server_Admin 920702 T0002 ACC Access 920908 T0003 SQL SQL.Server_Admin 910203 T0004 C++ Visual C++ 920809 T0004 SQL SQL.Server_Admin 910203 T0004 VB Visual Basic 920506 T0005 VB Visual Basic 920506

　

↓(第三正規化)	↓(第二正規化)  (理論上同一位老師同樣課程不會去上兩次， ten_no + tain_id 為主鍵)
員工編號 ten_no 員工姓名 ten_name 部門編號 dept_id T0001 張一 101 T0002 張二 101 T0003 張三 102 T0004 張四 103 T0005 張五 102 　 部門編號 dept_id 部門名稱 dept_name 101 教務處 102 總務處 103 訓導處	員工編號 ten_no 課程編號 train_id 訓練日期 train_date T0001 SQL 910203 T0001 VB 920506 T0001 WEB 920702 T0002 WEB 920702 T0002 ACC 920908 T0003 SQL 910203 T0004 C++ 920809 T0004 SQL 910203 T0004 VB 920506 T0005 VB 920506 　 課程編號 train_id 課程名稱 train_name SQL SQL.Server_Admin VB Visual Basic WEB WEB.Server_Admin ACC Access C++ Visual C++

　

　

資料表的設計考量

• 資料表格是資料儲存在資料庫中的基本元件，所有的讀取、新增、修改、刪除動作，都會是以表格來作處理依據。

• 做資料表格時先考慮到資料的分割問題

  • 水平分割：每個子資料表的結構皆相同，但存放不同的資料內容

    • 例如：有一個包含一億筆記錄的資料表，可以考慮依其特性分成數個小資料表

  

  • 垂直分割：

    • 以資料表格欄做切割成為數個小表格，以減少表格寬度，降低I/O存取次數

    • 故當資料表越寬時，其所佔用的資料頁（Data Page）會更多，將使I/O存取次數大增

    • 例如：包含有過多欄位的資料表，可以考慮將不常用的欄位存到第二張資料表中。

  

• 新增一個資料表格時，只要在資料表上方按右鍵就可以新增一個資料表，資料表的名稱是可以用中文名稱，但是為了配合遠端程式的讀取問題，仍應以英文為主。

• 資料表一般可以分做四個欄位選項

  • 資料行名稱：每一欄位必須有不同的名稱，且適合該欄資料性質的名稱。例如：校務行政帳號用EMPNO。

  • 資料型態：資料型態是依資料行名稱所用的值為何來做決定。一般資料型態可分下列幾種：(var表示可以改變長度的型態)

  型態名稱	資料型態	所佔空間大小(bytes)
  二進位	Binary  [unicode] Varbinary  [unicode]	0-8000
  字元	Char  [unicode] Varchar  [unicode]	0-8000
  國際字元	Nchar  [unicode] Nvarchar  [unicode]	0-8000 (4000字)
  日期 / 時間	Datetime Smalldatetime	8 4
  數值	Decimal [(p,s)] Numeric [(p,s)]	2-17
  浮點數	Float [unicode]	8 4
  通用	Uniqueidentifier	16
  整數	Int Smallint，tinyint	4 2，1
  貨幣	Money Smallmoney	8 4
  位元	bit	1
  文字	text	0-2GB
  圖片	image	0-2GB
  國際文字	ntext	0-2GB

  • 寬度：為資料內容最大的長度值

  • 是否允許Null：Null值不是0也不是空白，更不是填入字串"NULL"，而是表示"不知道"、"不確定"或"暫時沒有資料"的意思，即也就是說指該格資料是否允許為空值，允許即可暫不填任何資料。

實例操作：

• 新增資料庫(以新灌一台Win2000伺服器，將校務行政JSDB資料庫為例說明)。

  • Microsoft SQL Server →  Enterprise Manager ，在機器名稱上的資料庫上按右鍵 → 新增資料庫 → 在資料庫的屬性的一般選項輸入資料庫名稱(校務行政為名稱JSDB) → 確定。

    

    

    　

    1. 將校務行政資料庫(JSDB)匯入，在JSDB上方按右鍵 → 所有工作 → 還原資料庫。

       

       　

    2. 在一般選項，還原成資料庫為JSDB，還原選擇來源裝置，還原備份集選擇資料庫 - 完整備份，按選擇裝置。

       

       　

    3. 選擇還原裝置 → 新增 → 檔案名稱 → 按 .. 位置選取所要還原的檔案位置(例如：c:\910925\JSDB910925.dat)。

       

    4. 選項部份 → 將資料庫檔案還原成的實體路徑要對(例如：C:\Program Files\Microsoft SQL Server\MSSQL\Data\JSDB.mdf 和 C:\Program Files\Microsoft SQL Server\MSSQL\Data\JSDB-Log.LDF) → 其他選項視需要選擇之 → 確定。

       

       

       　

    5. 當還原成功之後可以在JSDB資料庫中的資料表看到許多資料，如下：

       

    6. 還原後的JSDB資料庫，請將SYSUSER和SYSVIEW刪除，從安全性的登入將此二帳號重建。

• 新增表格實例

  • 建一個員工資料表(EMPDATA)，如下：
    

    資料行名稱	資料型態	寬度	是否允許Null
    EMP_NO	char	5	Not Null
    EMP_NAME	Varchar	8	Not Null
    EMP_BIRTH	datetime	8	Null
    EMP_SEX	char	1	Not Null
    EMP_ADDR	varchar	50	Null
    EMP_FAX	varchar	20	Null

  • 做法：

    • 在所要新增的資料庫名稱(JSDB)中，選擇資料表的位置，按右鍵做新增資料表。

    

    • 做資料表格的內容名稱輸入及型態選擇，若允許不輸資料的部分則打勾。

  

  • 設計好資料表後，並存成EMPDATA。在EMPDATA上方按右鍵傳回所有資料行後，點選格子內就可輸入資料。

  

五、Client 端 ODBC設定

　

ODBC是一個設計提供底層的資料庫系統使用的通用API(Application Programming Interface)函數庫程式庫。它透過一個程式庫驅動程式和資料庫溝通(有如Windows和印表機的溝通是透過印表機的驅動程式)，至於網路驅動程式是否需要與遠端資料庫連線，則取決於正被使用的資料庫。

以校務行政在win98機器上做說明範例

• 在開始 → 設定 → 控制台  → ODBC 資料來源(32位元)

• 在系統資料名稱部分去新增JSR(大寫)和JSW(大寫)兩個選項 → 

• 在建立新資料來源上選擇使用SQL Server → 完成

• 輸入名稱為JSW(或JSR)，伺服器選項選擇各校的SQL伺服器名稱(例如：SQL) → 下一步

• 建立新的資料來源至SQL Server時，選擇遊使用者所輸入的登入識別碼及密碼進行SQL Server帳戶認證(JSR用SYSVIEW，JSW用SYSUSER) → 下一步

• 勾選變更預設資料庫為JSDB，其他選項不動 → 下一步

• 勾選變更SQL Server 系統訊息的語言為Traditonal Chinese ， 其他選項不動 → 完成。

• 當設定完成之後，會將剛才的ODBC所有設定值秀出，若是不放心還可以按一下測試資料來源，看是否有成功。

• 若是成功將秀出以下的訊息。

六、以 Access 作為 SQL 後端平台(以Access 2000為例)

• 在 Microsoft Access 開一個新檔，存成db1.mdb。

• 開啟db1.mdb，檔案 →  取得外部資料 →  連結資料表 →  檔案類型選擇ODBC資料庫。

• 機器資料來源(例如 ：A00)或是系統資料來源 →  選擇 File Data Source →  new → 選擇SQL Server  →  給一個位置及檔名(例如：sql.dsn) → 存檔  →  下一步 →  完成

• 輸入SQL Server的名稱(例如：A00)  →  下一步 →  使用SQL Server認證，輸入sa帳號和密碼。

• 改變預設的資料庫(master)成JSDB  →  下一步 →  勾選預設語言為Traditional Chinese  →  下一步 →  完成

• 可以按Test Data Source鍵測試與SQL Server是否連線，若連線成功則按OK鍵。

• 選擇剛才所存的檔名(sql.dsn) → 確定 → 此刻會叫你輸入sa的密碼。

• 當出現SQL server(A00)上的資料表時，可以按全選將所有的資料表做連結過來，連結的過程中有時會叫你選擇索引鍵(可選一到十個)。

• 以後當要查詢時，只要開啟db1.mdb檔，並登入sa帳號及密碼，在開啟所要查看的資料表即可；同時從Acess修改資料也會同步寫回SQL資料表。

七、使用Query Analyzer 工具查詢資料

• 查詢指令

  • 格式：SELECT 欄位(欄位間用,隔開，若是想查全部則用*代替) FROM 資料表名稱 WHERE 條件

    • 實例：

      • SELECT * FROM SEMP  (看所有SEMP表格的資料，SEMP帳號檔)

      • SELECT EMPNO,IDNO,NAME FROM SEMP(看SEMP表格的EMPNO,IDNO,NAME三欄資料，並將欄位依EMPNO,IDNO,NAME順序排列)

      • SELECT EMPNO,IDNO,NAME FROM SEMP WHERE NAME=‘黃添修‘，(看SEMP表格的EMPNO,IDNO,NAME三欄資料，且名字是黃添修)

• 模糊查詢

  • 格式：SELECT 欄位(欄位間用,隔開，若是想查全部則用*代替) FROM 資料表名稱 WHERE 條件 LIKE 值

    • 實例：

      • SELECT EMPNO,IDNO,NAME FROM SEMP WHERE NAME LIKE ‘黃%‘(看SEMP表格的EMPNO,IDNO,NAME三欄資料，且名字是黃帶頭的)

      • SELECT EMPNO,IDNO,NAME FROM SEMP WHERE NAME LIKE ‘%黃‘(看SEMP表格的EMPNO,IDNO,NAME三欄資料，且名字是*黃的)

      • SELECT EMPNO,IDNO,NAME FROM SEMP WHERE NAME LIKE ‘%黃%‘(看SEMP表格的EMPNO,IDNO,NAME三欄資料，且名字是‘%黃‘和‘黃%‘的聯集)

      • SELECT * FROM BITEM_SCORE WHERE SCORE>90

      • SELECT * FROM BITEM_SCORE WHERE SCORE<90

      • SELECT * FROM BITEM_SCORE WHERE SCORE=90

      • SELECT * FROM BITEM_SCORE WHERE SCORE>=90(不可=>)

      • SELECT * FROM BITEM_SCORE WHERE SCORE<=90(不可=<)

      • SELECT * FROM BITEM_SCORE WHERE SCORE!= 90(不等於90者)

• 排序查詢

  • 升冪排序查詢格式：SELECT 欄位(欄位間用,隔開，若是想查全部則用*代替) FROM 資料表名稱 ORDER BY 欄位

    • 實例：

      • SELECT * FROM SEMP ORDER BY EMPNO (看所有SEMP表格的資料，並以EMPNO帳號做升冪排序)

      • SELECT IDNO,NAME FROM SEMP ORDER BY EMPNO(看所有SEMP表格中的IDNO和NAME資料，並以EMPNO帳號做升 冪排序)

      • SELECT IDNO,NAME FROM SEMP ORDER BY EMPNO,IDNO(看所有SEMP表格中的IDNO和NAME資料，並以EMPNO和IDNO帳號做升 冪排序)

  • 降冪排序查詢格式：SELECT 欄位(欄位間用,隔開，若是想查全部則用*代替) FROM 資料表名稱 ORDER BY 欄位 DESC

    • 實例：

      • SELECT * FROM SEMP ORDER BY EMPNO DESC(看所有SEMP表格的資料，並以EMPNO帳號做降冪排序)

      • SELECT IDNO,NAME FROM SEMP ORDER BY EMPNO DESC(看所有SEMP表格中的IDNO和NAME資料，並以EMPNO帳號做降 冪排序)

      • SELECT IDNO,NAME FROM SEMP ORDER BY EMPNO,IDNO(看所有SEMP表格中的IDNO和NAME資料，並以EMPNO和IDNO帳號做升 冪排序)

  • 升降冪排序混合查詢：

    • 實例：

      • SELECT IDNO,NAME FROM SEMP ORDER BY EMPNO,IDNO DESC(看所有SEMP表格中的IDNO和NAME資料，並以EMPNO做升 冪排序且IDNO帳號做降冪排序)

      • SELECT IDNO,NAME FROM SEMP ORDER BY EMPNO DESC,IDNO (看所有SEMP表格中的IDNO和NAME資料，並以EMPNO做降 冪排序且IDNO帳號做升冪排序)

八、 SQL其他的指令

1. 新增紀錄(Insert)

   • 語法：INSERT   table_name [( column_list )]  VALUES ( data_values )

   • 說明：

     • table_name：要新增紀錄的資料表名稱

     • column_list：要輸入值的欄位名稱，欄位名稱之閒請用逗號相隔，若是此處沒有指定任何欄位，則表示資料表中的所有欄位。

     • data_values：要填入欄位中的值，值和值之間必須用逗號相隔，此處必須與column_list相應，column_list若是列出三個欄位名稱，則此處也必須有三筆的欄位值。欄位值可以用NULL或DEFAULT來指定。

   • 範例：INSERT   員工資料表 (員工編號，姓名，身分證字號)
                                 VALUES (‘T0030‘，‘張三子‘，‘Y120072567‘)

   　

2. 更新紀錄(Update)

   • 語法：UPDATE   table_name
                 SET{ column_name = { expression | DEFAULT | NULL} )  [,...n]
                [WHERE  search_condition]

   • 說明：

     • table_name：要更新紀錄的資料表名稱

     • column_name：要指定欲變更的欄位名稱。

     • expression：指定新的欄位值，expression可以是一個常數、運算式、變數...等。

     • DEFAULT：使用DEFAULT會將column_name的欄位重新設為預設值。

     • NULL：使用NULL，可將column_name的欄位值重新設為NULL。

     • WHERE  search_condition：表欲更新紀錄的條件值。

   • 範例：UPDATE   員工資料表
                 SET 員工編號     =  ‘T00521‘,
                        姓名              =  ‘劉名聲‘,
                        身分證字號  =  ‘Y120075891‘
                 WHERE 員工編號     =  ‘T00521‘

   　

3. 刪除紀錄(DELETE)

   • 語法：DELETE   table_name
                WHERE  search_condition]

   • 說明：

     • table_name：要刪除紀錄的資料表名稱

     • WHERE  search_condition：表欲刪除紀錄的條件值。

   • 範例：DELETE   員工資料表
                 WHERE 員工編號     =  ‘T00521‘

九、 SQL的檢視表的建立

SQL的檢視表其實是執行了SELECT的查詢結果，而此一結果是模擬成資料表的形式存在，但其又不是一個真實的資料表，所以也可稱為虛擬的資料表。檢視表和資料表的最大不同點為，檢視表示儲存查詢的敘述語法，但不儲存資料記錄；而資料表是真正儲存資料記錄的地方。

檢視表的優點有四：

1. 增加可讀性：可使用了解的名稱來做欄位名稱，方便使用者檢視查詢結果。

2. 資料安全：為不同的使用者建立不同的檢視表，並以權限方式限制其所能編輯及檢視的資料，以確保資料的安全性。

3. 降低查詢的複雜：使用者可以透過檢視表來做複雜的查詢，不需要學習複雜的查詢技巧；就可以找到其所要查詢的資料。

4. 方便程式的維護：以應用程式使用檢視表來存取資料時，當資料表的結構改變時；只要修改檢視表中的設定，即可以繼續查詢，不需要修改應用程式的內容。

建立檢視表的方法有二：

1. 利用工具→精靈→資料庫→建立檢表精靈→再依其所詢問的回答即可

2. 利用Enterprise Manger中，資料庫→JSDB→檢視表→右鍵→新增檢視表→進入檢視表設計視窗中去建立。

建立檢視表的實作：略過不寫，課堂上實作。

　

　

參考資料：

1.  2003年1月29日趨勢科技毒賣新聞「SQL警戒病毒」專刊

2. SQL2000效能監控與最佳化   陳俊宇 恆逸資訊

3. Microsoft SQL Server 7 企業寶典  王牌講座作者聯誼會 姜建志 編著  第三波出版

4. 微軟網站http://www.microsoft.com

5. 系統分析與設計理論與實務應用 ------- 吳仁和、林信惠

6. 資料庫系統理論與實務 -------------------- 曾守正

7. SQL的奧祕 ------------------------------------- 蔡國瑞 （原著：Allen G. Taylor）

8. SAMS Teach Yourself SQL 21天自學手冊   原著 Ryan K. Stephens & Ronald R. Plew   編譯 284  大新資訊

9. 資料庫正規化 ------ http://ccis./access/item02-3-10.htm

10. 淺談資料庫正規化 台北縣教育網路中心 劉仁宇

from:  http://newweb.syups./documents/memo/SQL%E4%BC%BA%E6%9C%8D%E5%99%A8%E7%AE%A1%E7%90%86/SQL%E4%BC%BA%E6%9C%8D%E5%99%A8%E7%AE%A1%E7%90%86.htm