《數據庫原理及應用》第6章 數據庫管理

第六章數據庫管理理解數據庫管理必要性學習數據庫管理的各種方法理解并發(fā)控制、安全性、備份和恢復的必要性學習解決多用戶同時處理數據庫時出現的常見問題學習鎖定概念和理解ACID事務了解基本的DBA管理功能理解分布式數據庫處理【學習目標】一、為什么需要數據庫管理6.1數據庫管理基礎

數據庫系統(tǒng)隨規(guī)模增大,系統(tǒng)會變得異常復雜多用戶數據庫應用帶來數據庫訪問復雜性數據安全和數據隱私對機構和用戶都非常重要數據庫性能隨系統(tǒng)使用發(fā)生變化,需要定期維護最大程度地發(fā)揮數據庫對其所屬機構的作用二、數據庫管理系統(tǒng)（DBMS）功能數據庫定義定義數據庫完整性、安全保密、存取路徑等數據存取提供數據的操縱語言以便對數據進行查找和增刪改數據庫運行管理事務管理、自動恢復、并發(fā)控制、死鎖檢測或防止、安全性檢查、存取控制、完整性檢查、日志記錄等數據組織、存儲和管理數據字典、用戶數據、存取路徑的組織存儲和管理，以便提高存儲空間利用率，并方便存取數據庫的建立和維護數據庫初建、轉儲、恢復、重組、重構以及性能檢測等數據傳輸網絡通信、數據轉換、異構數據庫互訪等三、數據庫管理系統(tǒng)（DBMS）功能模塊DBMS功能模塊數據庫定義數據庫存取數據庫運行處理數據組織、存儲和管理數據庫建立、維護和其他DDL翻譯程序模塊安全性定義程序模塊完整性定義程序模塊查詢處理程序模塊數據更新程序模塊交互式查詢程序模塊嵌入式查詢程序模塊系統(tǒng)初啟程序模塊安全性控制程序模塊完整性控制程序模塊事務管理程序模塊并發(fā)控制程序模塊運行日志管理程序模塊緩沖區(qū)管理程序模塊數據組織、維護程序模塊存取路徑(索引)管理程序模塊批量數據裝入模塊數據轉儲模塊數據庫恢復模塊數據庫重組模塊數據轉換模塊通信模塊四、數據庫管理員（DBA）職責

負責數據庫系統(tǒng)開發(fā)與運行

數據庫用戶管理數據備份與數據恢復管理數據庫性能調優(yōu)管理五、數據庫管理系統(tǒng)（DBMS）層次數據庫操作系統(tǒng)數據存儲層數據存取層語言翻譯處理層應用層數據庫應用程序對數據庫語言(如SQL)進行語法分析、視圖轉換、授權檢查、完整性檢查、查詢優(yōu)化等對數據記錄進行存取、維護存取路徑(如索引)、并發(fā)控制、事務管理、日志登記等緩沖區(qū)管理、內外存交換、外存數據管理等對物理文件進行讀寫，為DBMS的存儲層提供接口六、數據庫管理系統(tǒng)（DBMS）結構七、SQLSERVER數據庫管理系統(tǒng)簡介1988年，Microsoft、Sybase和Ashton-Tate公司聯合，開發(fā)出運行于OS/2操作系統(tǒng)上的SQLServer1.0。1989年，Ashton-Tate公司退出SQLServer的開發(fā)。1990年，SQLServer1.1產品面世。1992年，SQLServer4.2產品面世。1994年，Microsoft公司和Sybase公司分道揚鑣。1995年，Microsoft公司發(fā)布了SQLServer6.0產品，隨后的SQLServer6.5產品取得了巨大的成功。1998年，Microsoft公司發(fā)布了SQLServer7.0產品，開始進入企業(yè)級數據庫市場。2000年，Microsoft公司發(fā)布了SQLServer2000產品。2005年，Microsoft公司發(fā)布了SQLServer2005產品。2008年，Microsoft公司發(fā)布了SQLServer2008產品。1.SQLSERVER2005版本SQLServer2005Express(學習版)SQLServer2005Workgroup(工作組版)

SQLServer2005Standard(標準版)

SQLServer2005Enterprise(企業(yè)版)SQLSERVER2005各版本——性能比較2.SQLSERVER2005各版本功能比較SQLSERVER2005各版本——高可用性比較SQLSERVER2005各版本——管理性比較SQLSERVER2005各版本——安全性比較SQLSERVER2005各版本——可編程性比較SQLSERVER2005各版本——集成和互操作性比較SQLSERVER2005各版本——商業(yè)智能比較3.SQLSERVER數據庫系統(tǒng)結構主數據庫文件次數據庫文件事務曰志文件4.SQLSERVER數據庫組成系統(tǒng)元數據、配置信息臨時表、臨時存儲過程提供存儲復制、作業(yè)調度和管理報警等為創(chuàng)建新數據庫提供模板5.SQLSERVER數據庫對象數據庫對象：表、視圖、索引、存儲過程、觸發(fā)器八、SQLSERVER2005數據庫管理工具SQLServerManagementStudioExpress功能組件是管理SQLServer數據庫的圖形工具。1.數據庫創(chuàng)建命名新建數據庫HSD數據庫創(chuàng)建完成2.數據庫表創(chuàng)建執(zhí)行SQL語句創(chuàng)建數據庫表數據庫表創(chuàng)建完成3.數據插入數據插入SQL語句數據插入完成4.數據查詢SQL查詢數據5.數據庫用戶管理用戶對象一、為什么需要并發(fā)控制6.2并發(fā)控制無論當前是否有其他用戶訪問訂票系統(tǒng)，用戶都能夠提交訂單即使同時有上百個用戶訪問訂票系統(tǒng)，用戶訪問訂單所得到結果都相同。用戶隨時可獲得最新的票源信息。例訂票系統(tǒng)并發(fā)控制目的支持并發(fā)事務處理，使更多用戶并行操作，提高系統(tǒng)的處理能力。保證一個用戶的工作不會對另一個用戶的工作產生不合理的影響。保證用戶與其他用戶同時操作數據庫時，與用戶單獨操作數據庫所得到的結果相同。二、事務概念事務(Transaction)完成單一邏輯工作單元的操作集合操作序列要么完整執(zhí)行,或者任何操作都不執(zhí)行，以保證數據完整性。例將賬戶A轉賬1000元到賬戶B，其操作流程如下：Read(A);A:=A-1000;Write(A);Read(B);B:=B+1000;Write(B)轉賬處理操作序列語句三、事務定義模式1.顯式定義方式BEGINTRAN|TRANSACTION[tran_name] ；事務開始SQL語句1SQL語句2。。。。COMMIT ;事務提交BEGINTRAN|TRANSACTION[tran_name] ；事務開始SQL語句1SQL語句2。。。。ROLLBACK ;事務取消SAVETRANsave_name在事務中設置一個保存點。它可以使一個事務內的部分操作回退。BEGINTRANSACTION;DELETEFROMJobCandidate

WHEREJobCandidateID=13;DELETEFROMJobCandidate

WHEREJobCandidateID=17;COMMITTRANSACTION;例1事務執(zhí)行使用的全局變量@@rowcount上一語句影響的行數；@@error檢測上一條語句執(zhí)行時的錯誤代碼。@@error=0表示執(zhí)行成功；@@trancount當前連接的活動事務數事務全局變量的使用方法：begintransaction……/*A組語句序列*/savetransaction保存點1

……/*B組語句序列*/if@@error<>0rollbacktransaction保存點1/*回滾到保存點1*/elsecommittransaction

/*提交A和B組語句*/

例2使用事務向表book中插入數據USECourseDB；BEGINTRANtran_exam；INSERTINTObook(book_id,book_name,publish_company)VALUES(‘dep04_s006_01‘,‘C++程序設計‘,’機械工業(yè)出版社‘)；SAVETRANint_point；INSERTINTObook(book_id,book_name,publish_company)VALUES(‘dep04_s006_02‘,‘C++實驗指導書‘,’電子工業(yè)出版社‘)；INSERTINTObook(book_id,book_name)VALUES(‘dep04_s006_03‘,‘C++課程設計指導書’)；IF@@error<>0

ROLLBACKTRANint_point；elseCOMMITTRANtran_exam；事務中不可使用的sql語句創(chuàng)建數據庫：createdatabase；修改數據庫：alterdatabase；刪除數據庫：dropdatabase；備份數據庫：dumpdatabase、backupdatabase；還原數據庫：loaddatabase、restoredatabase；日志備份：dumptransaction、backuplog；日志還原：loadtransaction、restorelog；配置：reconfigure； 磁盤初始化：diskinit；統(tǒng)計：updatestatistics；顯示或設置數據庫選項：sp_dboption。在關系數據庫中，一個事務可以是一條或多條SQL語句,也可以包含一個或多個程序。一個程序通常包含多個事務2.隱式方式當用戶沒有顯式地定義事務時，DBMS按缺省規(guī)定每執(zhí)行一個sql語句將自動劃分事務。四、事務與程序關系五、事務特性事務的ACID特性：原子性（Atomicity）：事務所有操作在數據庫中要么全部執(zhí)行，要么 全部不執(zhí)行。一致性（Consistency）：事務多次執(zhí)行，其結果應一致。隔離性（Isolation）：事務與事務之間隔離，并發(fā)執(zhí)行透明。持續(xù)性（Durability）：事務完成后，數據改變必須是永久的。六、并發(fā)事務處理為什么需要并發(fā)事務處理：提高吞吐和資源利用率減少等待時間支持用戶的數據庫訪問程序并行運行七、事務并發(fā)處理需解決問題A=16讀讀A=A-1A=A-1A=15A=15A=16A=16出售1出售1事務T1事務T2最后結果：數據有錯1.丟失更新數據A=15售票點2機票數量A售票點1出錯原因：在并發(fā)操作情況下，對T1、T2兩個事務的操作序列的調度是隨機的。當事務T1和T2讀入同一數據并修改，T2的提交結果破壞了T1提交的結果，導致T1的修改被丟失。2.不可重復讀取問題:(1)事務T1讀取某一數據后，事務T2對其做了修改，當事務T1再次讀該數據時，得到與前一次不同的值。(2)事務T1按一定條件從數據庫中讀取了某些數據記錄后，事務T2刪除了其中部分記錄，當T1再次按相同條件讀取數據時，發(fā)現某些記錄消失了。也稱為不可重復讀取。(3)事務T1按一定條件從數據庫中讀取某些數據記錄后，事務T2插入了一些記錄，當T1再次按相同條件讀取數據時，發(fā)現多了一些記錄。稱為幻象讀取。同類問題:3.臟數據讀取問題:T1已修改過的數據恢復原值，T2讀到的數據就與數據庫中的數據不一致,即存在所謂〝臟數據〞。最終結果：C恢復為100T2的C為200八、并發(fā)控制的主要方法1.資源鎖定在將要修改某些數據行或表時，通過加鎖禁止多個事務同時獲取這些行或表的數據，這種方法稱為“資源鎖定”。并發(fā)控制就是要用正確的方式調度并發(fā)操作，使一個事務的執(zhí)行不受其他事務的干擾，從而避免造成數據的不一致性。事務并發(fā)執(zhí)行帶來問題原因：當多個事務同時存取同一數據時，由于不同操作時序，可能出現存取不正確的數據，破壞數據庫的一致性。隱式鎖定——DBMS缺省執(zhí)行顯式鎖定——加鎖命令顯式執(zhí)行鎖定執(zhí)行：數據庫——粒度最大表——粒度較大頁面——粒度中等行——粒度小鎖定粒度：排它鎖定——鎖定后，不允許其它事務讀寫鎖定資源數據共享鎖定——鎖定后，允許其它事務讀取鎖定資源數據鎖定訪問：2.鎖定分類九、串行事務在并發(fā)處理兩個或多個事務時，如果數據庫操作結果在邏輯上同事務以串行方式處理結果保持一致。這種處理并發(fā)事務的模式稱為“串行模式”。并發(fā)事務正確調度準則：一個給定的并發(fā)調度，當且僅當它是可串行化的，才能保證正確調度。保證可串行化的一個協(xié)議是：二階段鎖定協(xié)議兩階段鎖定協(xié)議規(guī)定每個事務必須分兩個階段提出加鎖和解鎖申請:增長階段，事務可以獲得鎖，但不能釋放鎖?？s減階段，事務可以釋放鎖，但不能獲得新鎖。事務T1增長階段縮減階段事務T2事務T3判斷下列事務是否可串行化？十、死鎖當多個用戶同時鎖定兩個以上資源時，可能會出現誰都無法運行的死鎖狀態(tài)。死鎖防范策略：允許用戶一次發(fā)出當前所需全部資源的鎖定，使用完成后，再釋放給其它用戶訪問。規(guī)定所有應用程序鎖定資源的順序必須完全相同。死鎖解決辦法：當發(fā)生死鎖時，回滾其中的一個事務，并取消它對數據庫所做的改動。十一、鎖定方法1.樂觀鎖定讀取數據處理事務執(zhí)行更新沖突檢查如果有沖突，回滾事務，重新開始事務否則，提交事務樂觀鎖定示例2.悲觀鎖定鎖定所需資源讀數據處理事務執(zhí)行提交釋放鎖定悲觀鎖定示例樂觀鎖定與悲觀鎖定比較：樂觀鎖定的持續(xù)時間比悲觀鎖定更短悲觀鎖定可以一次性鎖定多個資源十二、事務隔離級別隔離級別臟讀不可重復讀幻讀讀未提交（Readuncommitted）可能可能可能讀已提交（Readcommitted）不可能可能可能可重復讀（Repeatableread）不可能不可能可能可串行化（Serializable）不可能不可能不可能說明：事務隔離級別越高，出現數據不一致的可能性越小，但系統(tǒng)吞吐量也越小。

一、游標概念6.3游標類型游標（cursor）是一種指針，它指向從sqlselect語句得到的結果集。游標定義DECLARE

cursor_name

CURSOR

FOR

select_statement

[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]FORREADONLY：指出游標的結果集是只讀的，不能修改。FORUPDATE[OFcolumn_name[,...n]]：指出游標的結果集是可以被修改的。如果指定OFcolumn_name[,...n]參數，則只允許修改所列出的列。如果在UPDATE中未指定列的列表，則可以更新所有列。二、為什么使用游標游標可以用來指向sql查詢結果集，并可對其中某行（或某些行）的數據進行操作。由于結果集數據是保存在內存中，從其中提取數據的速度要比從數據表中直接提取數據的速度要快得多。三、游標基本操作定義游標打開游標從游標中操作數據關閉游標DECLAREauthors_cursorCURSORFORSELECT*FROMauthors;OPENauthors_cursor;FETCHNEXTFROMauthors_cursorINTO@au_id,@au_fname,@au_lname;CLOSEauthors_cursor;例四、游標類型向前游標靜態(tài)游標鍵集游標動態(tài)游標一、數據庫安全含義6.4數據庫安全數據庫安全是一種確保數據庫授權訪問的機制。二、用戶賬戶用戶訪問數據庫，需要創(chuàng)建用戶賬戶，賦予登錄名和密碼。三、處理權限和責任1.DBMS安全模型2.HSD公司數據庫的處理權限3.數據庫用戶名稱創(chuàng)建4.數據庫用戶角色賦予四、DBMS安全指南在防火墻后運行應用最新操作系統(tǒng)以及DBMS服務包和補丁將DBMS的功能限定為所需要的功能保護運行DBMS的計算機管理賬戶與密碼五、應用程序級別的安全DBMS只能實現常規(guī)性的數據安全功能,對于一些應用安全特性需求,則只有通過應用程序本身來處理。例應用要求每個員工只能看自己的數據，則可以采用如下SQL語句完成SELECT*FROM EMPLOYEEWHERE EMPLOYEE.Name=‘<%SESSION(“EmployeeName”)%>’SELECT*FROM EMPLOYEEWHERE EMPLOYEE.Name=“JackFran