[TOC]

數(shù)據(jù)庫復習

數(shù)據(jù)庫應(yīng)用系統(tǒng)開發(fā)方法

數(shù)據(jù)庫基本概念

數(shù)據(jù)

定義：描述事物的符號序列

計算機中數(shù)據(jù)分為兩部分：

• 臨時性數(shù)據(jù)
• 持久性數(shù)據(jù)

數(shù)據(jù)有型（Type）與值（Value）之分

數(shù)據(jù)庫（DB）

數(shù)據(jù)的集合，具有統(tǒng)一的結(jié)構(gòu)形式并存放于統(tǒng)一的存儲介質(zhì)內(nèi)腊脱，是多種應(yīng)用數(shù)據(jù)的集成陕凹，并可被各個應(yīng)用程序所共享

數(shù)據(jù)庫系統(tǒng)（DBS）

組成：

* 數(shù)據(jù)庫（數(shù)據(jù)）
　　* 數(shù)據(jù)庫管理系統(tǒng)（軟件）
　　* 數(shù)據(jù)庫管理員（人員）
　　* 硬件平臺：計算機和網(wǎng)絡(luò)
　　* 軟件平臺：操作系統(tǒng)传趾、數(shù)據(jù)庫系統(tǒng)開發(fā)工具浆兰、接口軟件

數(shù)據(jù)庫應(yīng)用系統(tǒng)（DBAS）

組成：數(shù)據(jù)庫系統(tǒng)+應(yīng)用軟件+應(yīng)用界面

1552808373218.png

軟件工程

由方法、工具和過程三部分組成——軟件工程的三要素

軟件生存周期

定義開始蜕便，經(jīng)過開發(fā)轿腺、使用與維護族壳，直到最后退役

軟件工程與數(shù)據(jù)庫技術(shù)

數(shù)據(jù)庫工程

內(nèi)容

• 數(shù)據(jù)庫設(shè)計
• 相應(yīng)的應(yīng)用的設(shè)計與實現(xiàn)

典型的軟件開發(fā)模型

瀑布模型、快速原型模型坏平、螺旋模型等

DBAS生命周期模型

組成

項目規(guī)劃

• 系統(tǒng)規(guī)劃與定義
  • 任務(wù)陳述令境、確定任務(wù)目標展父、確定范圍和邊界篮绿、確定用戶視圖
• 可行性分析
  • 技術(shù)亲配、經(jīng)濟、操作可行性及開發(fā)方案選擇
• 項目規(guī)劃
  • 項目團隊苍鲜、環(huán)境、活動歹颓，成本預算领跛，進度計劃

需求分析

• 組成
  • 由需求獲取吠昭、需求分析怎诫、需求描述與規(guī)范說明、需求驗證等步驟
• 各種需求的主要工作
  • 數(shù)據(jù)需求分析
  • 功能需求分析
  • 性能需求分析
  • 其他需求

系統(tǒng)設(shè)計

• 概念設(shè)計
  • 數(shù)據(jù)庫概念模型設(shè)計肉津、系統(tǒng)總體設(shè)計
• 邏輯設(shè)計
  • 數(shù)據(jù)庫邏輯結(jié)構(gòu)設(shè)計妹沙、應(yīng)用程序概要設(shè)計距糖、數(shù)據(jù)庫事務(wù)概要設(shè)計
• 物理設(shè)計
  • 數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計、數(shù)據(jù)庫事務(wù)詳細設(shè)計趣斤、應(yīng)用程序詳細設(shè)計

實現(xiàn)與部署

• 建立數(shù)據(jù)庫結(jié)構(gòu)浓领，數(shù)據(jù)加載漫仆，事務(wù)和應(yīng)用程序的編碼及測試盲厌，系統(tǒng)集成狸眼、測試與運行拓萌，系統(tǒng)部署

運行管理與維護

• 日常維護、系統(tǒng)監(jiān)控與分析炕倘、系統(tǒng)性能優(yōu)化調(diào)整罩旋、系統(tǒng)進化升級等

三條設(shè)計主線

數(shù)據(jù)組織與存儲設(shè)計

• 設(shè)計數(shù)據(jù)庫

數(shù)據(jù)訪問與處理設(shè)計

• 數(shù)據(jù)庫事務(wù)

應(yīng)用設(shè)計

• 應(yīng)用程序

設(shè)計階段

概念設(shè)計

邏輯設(shè)計

物理設(shè)計

1552809214219.png

需求分析

需求

用戶對軟件的功能和性能的要求

需求分析

描述待開發(fā)的系統(tǒng)所要完成的功能

目標是深入描述軟件的功能和性能，確定軟件設(shè)計的約束和軟件同其它系統(tǒng)元素的接口細節(jié)浴骂，定義軟件的其它有效性需求

影響因素

• 軟件功能復雜
• 需求的可變性
• 軟件產(chǎn)品的不可見性

通常溯警，一個計算機應(yīng)用系統(tǒng)的需求分析工作是在系統(tǒng)分析人員與用戶不斷交互的過程中完成的

需求獲取的方法

• 面談
• 實地觀察
• 問卷調(diào)查
• 查閱資料

需求分析方法

結(jié)構(gòu)化分析與建模方法（SAD）

• DFD建模
• IDEF建模

面向?qū)ο蠓治雠c建模方法(OOAD)

• UML用例建模

結(jié)構(gòu)化分析任務(wù)

• 建立分析模型

  • 功能模型
  • 數(shù)據(jù)模型
  • 行為模型

• 編寫需求規(guī)格說明書

  • 引言梯轻、信息描述、功能描述蟀悦、行為描述日戈、質(zhì)量保證浙炼、接口描述以及其他需求等

• 結(jié)構(gòu)化分析的指導思想

  • 抽象
  • 分解

數(shù)據(jù)流圖(DFD)

建模方法的核心是數(shù)據(jù)流

1552810530967.png

數(shù)據(jù)流

數(shù)據(jù)在系統(tǒng)內(nèi)傳播的路徑

由于數(shù)據(jù)流是流動中的數(shù)據(jù)，所以必須有流向资厉，除了與數(shù)據(jù)存儲之間的數(shù)據(jù)流不用命名外宴偿，數(shù)據(jù)流應(yīng)該用名詞或名詞短語命名

數(shù)據(jù)源

代表系統(tǒng)之外的實體

對數(shù)據(jù)的加工

加工是對數(shù)據(jù)進行處理的單元

數(shù)據(jù)存儲

表示信息的靜態(tài)存儲

DFD建模過程

1. 明確目標，確定系統(tǒng)范圍
2. 建立頂層DFD圖
   • 頂層DFD只有一張
3. 構(gòu)建第一層DFD分解圖
4. 開發(fā)DFD層次結(jié)構(gòu)圖
5. 檢查確認DFD圖
   • 父圖中描述過的數(shù)據(jù)流必須在相應(yīng)子圖出現(xiàn)舷胜。
   • 一個處理至少有一個輸入流和輸出流翻伺。
   • 一個存儲必定有流入的數(shù)據(jù)流和流出的數(shù)據(jù)流穆趴。
   • 一個數(shù)據(jù)流至少有一端是處理框未妹。
   • 表達描述的信息是全面、完整化戳、正確和一致的

IDEF方法

• IDEF0:描述系統(tǒng)功能及其相互關(guān)系
  • 輸入（Input）箭頭表示完成特定活動所需的數(shù)據(jù)扫尖，置于矩形框的左側(cè)
  • 輸出（Output）箭頭說明由活動產(chǎn)生的結(jié)果及信息换怖，置于矩形框的右側(cè)
  • 控制（Control）箭頭描述了影響這個活動執(zhí)行的事件或約束條件沉颂，置于矩形框的上方
  • 機制（Mechanisms）箭頭表示實施該活動的物理手段或完成活動需要的資源（計算機系統(tǒng)、人或組織）切端，置于矩形框的下方
• IDEF1: 系統(tǒng)信息及其數(shù)據(jù)之間聯(lián)系
• IDEF1X是數(shù)據(jù)建模方法
  • 所有實體集間的聯(lián)系都必須用確定聯(lián)系來描述小压，不允許出現(xiàn)不確定聯(lián)系怠益，多對多聯(lián)系為非確定聯(lián)系蜻牢，非確定聯(lián)系需要分解為若干個一對多的聯(lián)系
  • 實體集（矩形框-獨立實體集、圓角矩形框-從屬實體集）抱虐、聯(lián)系（標定型聯(lián)系恳邀、非標定型聯(lián)系谣沸、分類聯(lián)系、非確定聯(lián)系）赋除。從屬實體集可以有自己的主碼
• IDEF2: 系統(tǒng)模擬茬祷，動態(tài)建模
• IDEF3: 過程描述及獲取方法
• IDEF4：面向?qū)ο笤O(shè)計

UML用例建模方法

• 系統(tǒng)
• 角色
• 用例

數(shù)據(jù)庫結(jié)構(gòu)設(shè)計

數(shù)據(jù)庫概念設(shè)計

目標

• 定義和描述應(yīng)用領(lǐng)域設(shè)計的數(shù)據(jù)范圍
• 獲取信息模型
• 描述數(shù)據(jù)的屬性特征
• 描述數(shù)據(jù)之間的關(guān)系
• 定義和描述數(shù)據(jù)的約束
• 說明數(shù)據(jù)的安全性要求
• 支持用戶的各種數(shù)據(jù)處理需求
• 保證信息模型能轉(zhuǎn)化成數(shù)據(jù)庫的邏輯結(jié)構(gòu)

概念設(shè)計的依據(jù)及過程

依據(jù)

以需求分析的結(jié)果為依據(jù)

結(jié)果

概念模型（ER）與概念設(shè)計說明書

過程

• 明確建模目標（模型覆蓋范圍）
• 定義實體集（自底向上標識和定義實體集）
• 定義聯(lián)系（實體間關(guān)聯(lián)關(guān)系）
• 建立信息模型（構(gòu)造ER模型）
• 確定實體集屬性（屬性描述一個實體集的特征或性質(zhì))
• 對信息模型進行集成與優(yōu)化（檢查和消除命名不一致、結(jié)構(gòu)不一致等）

概念設(shè)計是DB設(shè)計的核心環(huán)節(jié)滚停。概念數(shù)據(jù)模型是對現(xiàn)實世界的抽象和模擬

概念模型設(shè)計(ER)

實體（Entity）或?qū)嵗↖nstance）

客觀存在并可相互區(qū)分的事物叫實體

實體集（Entity Set）

同型實體的集合稱為實體集

屬性（Attribute）

實體所具有的某一特性

每個屬性的取值范圍稱為域

碼（Key）

實體集中唯一標識每一個實體的屬性或?qū)傩越M合

聯(lián)系（Relationship）

描述實體之間的相互關(guān)系

聯(lián)系也可以有屬性

類別

• 一對一聯(lián)系（1：1）
• 一對多聯(lián)系（1：n )
• 多對多聯(lián)系（m：n）

表示

1552812658225.png

數(shù)據(jù)庫邏輯設(shè)計

任務(wù)

將概念模型（如ER圖）轉(zhuǎn)化為DBMS支持的數(shù)據(jù)模型（如關(guān)系模型）键畴，并對其進行優(yōu)化

邏輯設(shè)計的依據(jù)和階段目標

1552812872602.png

相關(guān)概念

關(guān)系模型

• 層次模型
• 網(wǎng)狀模型
• 關(guān)系模型

關(guān)系數(shù)據(jù)庫設(shè)計的核心：<u>關(guān)系模式的設(shè)計</u>

1552813027780.png

數(shù)據(jù)依賴

關(guān)系內(nèi)部屬性與屬性之間的一種約束關(guān)系

類型

• 函數(shù)依賴
  • 平凡函數(shù)依賴與非平凡函數(shù)依賴
  • 完全函數(shù)依賴與部分函數(shù)依賴
  • 傳遞函數(shù)依賴
• 多值依賴

完整性約束

• 列
• 元組-行
• 關(guān)系-表

限定屬性的取值范圍

定義屬性間值的相互關(guān)聯(lián)

候選碼、主碼惹想、外碼

如果某屬性組的值能唯一確定整個元組的值嘀粱，則稱該屬性組為候選碼或侯選關(guān)鍵字

候選碼如果有多個，可以選其中的一個作為主碼

屬性或?qū)傩越MX不是關(guān)系模式R的碼（既不是主碼也不是候選碼）宛篇，但X是另一個關(guān)系模式的碼偷卧，則稱X是R的外部碼涯冠，也稱外碼

數(shù)據(jù)規(guī)范化

關(guān)系模式設(shè)計的好壞直接影響到數(shù)據(jù)庫設(shè)計的成敗

關(guān)系模式的規(guī)范化：把一個低一級的關(guān)系模式分解為高一級關(guān)系模式的過程

關(guān)系數(shù)據(jù)庫的規(guī)范化理論是數(shù)據(jù)庫邏輯設(shè)計的工具

目的：盡量消除插入蛇更、刪除異常砸逊，修改復雜，數(shù)據(jù)冗余的問題

范式

• 1NF
  • 如果關(guān)系模式R豆混，其所有屬性都是不可再分的基本數(shù)據(jù)項
• 2NF
  • 如關(guān)系模式R∈1NF员辩，且每個非主屬性完全函數(shù)依賴于主碼奠滑，則稱R屬于第二范式
• 3NF
  • 如果關(guān)系模式R為2NF宋税，并且Ｒ中的每個非主屬性不傳遞依賴于Ｒ的主碼，則稱關(guān)系R是屬于第3范式的淆攻，R∈3NF
• BCNF
• 4NF
• 5NF
• 1552918580511.png

數(shù)據(jù)庫邏輯設(shè)計方法

1. 將概念結(jié)構(gòu)轉(zhuǎn)化為一般的關(guān)系模型

• 一個實體型轉(zhuǎn)換為一個關(guān)系模式
• 一個1：1聯(lián)系可以轉(zhuǎn)換為一個獨立的關(guān)系模式瓶珊，也可以與任意一端對應(yīng)的關(guān)系模式合并
• 一個1：n聯(lián)系可以轉(zhuǎn)換為一個獨立的關(guān)系模式，也可以與n端對應(yīng)的關(guān)系模式合并
• 一個m：n聯(lián)系轉(zhuǎn)換為一個 關(guān)系模式
• 三個或三個以上實體間的一個多元聯(lián)系轉(zhuǎn)換為一個關(guān)系模式
• 同一實體集的實體間的聯(lián)系蝉娜，也可以按1：1南缓、1：n和m：n三種情況分別處理

2. 將轉(zhuǎn)化來的關(guān)系模型向特定DBMS支持下的數(shù)據(jù)模型轉(zhuǎn)換

3. 對數(shù)據(jù)模型進行優(yōu)化

數(shù)據(jù)庫物理設(shè)計

物理設(shè)計概述

物理數(shù)據(jù)庫設(shè)計是設(shè)計數(shù)據(jù)庫的存儲結(jié)構(gòu)和物理實現(xiàn)方法

目的

將數(shù)據(jù)的邏輯描述轉(zhuǎn)換為實現(xiàn)技術(shù)規(guī)范汉形，設(shè)計數(shù)據(jù)存儲方案概疆，以便提供足夠好的性能并確保數(shù)據(jù)庫數(shù)據(jù)的完整性凯旭、安全性罐呼、 可靠性

數(shù)據(jù)庫的物理結(jié)構(gòu)

內(nèi)容

• 確定數(shù)據(jù)的存儲結(jié)構(gòu)
• 設(shè)計數(shù)據(jù)的存取路徑
• 確定數(shù)據(jù)的存放位置
• 確定系統(tǒng)配置

難題

• 文件組織
• 文件結(jié)構(gòu)
• 文件存取
• 索引技術(shù)

索引

是數(shù)據(jù)庫中獨立的存儲結(jié)構(gòu)弄贿，其作用是提供一種無須掃描每個頁面（存儲表格數(shù)據(jù)的物理塊）而快速訪問數(shù)據(jù)頁的方案

有序索引

數(shù)據(jù)文件（主文件）和索引文件（索引記錄或索引項的集合）是有序索引技術(shù)中的兩個主體

• <u>聚集索引</u>（索引項與數(shù)據(jù)記錄排列順序一致期奔，索引順序文件）和<u>非聚集索引</u>
  • 一個數(shù)據(jù)文件只可建立一個聚集索引馁痴，但可建立多個非聚集索引
• <u>稠密索引</u>（數(shù)據(jù)文件中每個查找碼都對應(yīng)索引記錄）和稀疏索引（部分查找碼的值對應(yīng)索引記錄）
• <u>主索引</u>（主碼屬性集上建立的索引）與<u>輔索引</u>（非主碼上建立的索引）
• <u>唯一索引</u>（索引列不包含重復值）
• <u>單層索引</u>（線性索引罗晕，每個索引項順序排列直接指向數(shù)據(jù)文件中的數(shù)據(jù)記錄）和<u>多層索引</u>（大數(shù)據(jù)量文件中的采用多層樹型（B,B+樹）索引快速定位）

散列索引

利用散列函數(shù)實現(xiàn)記錄域取值到記錄物理地址間的直接映射關(guān)系

建立索引原則

• 一個（組）屬性經(jīng)常在操作條件中出現(xiàn)
• 一個（組）屬性經(jīng)常在連接操作的連接條件中出現(xiàn)
• 一個（組）屬性經(jīng)常作為聚集函數(shù)的參數(shù)

建立聚集索引原則

• 檢索數(shù)據(jù)時小渊，常以某個（組）屬性作為排序、分組條件呐萨。
• 檢索數(shù)據(jù)時谬擦，常以某個（組）屬性作為檢索限制條件惨远，并返回大量數(shù)據(jù)锨络。
• 表中某個（組）的值重復性較大

數(shù)據(jù)庫的物理設(shè)計

目標

得到存儲空間占用少礼患，數(shù)據(jù)訪問效率高和維護代價低的數(shù)據(jù)庫物理模式

環(huán)節(jié)

數(shù)據(jù)庫邏輯模式描述

文件組織與存取設(shè)計

• 基本原則
  • 根據(jù)應(yīng)用情況將易變部分與穩(wěn)定部分缅叠、存取頻率較高部分與存取頻率較低部分分開存放肤粱，以提高系統(tǒng)性能
  • 可以考慮將表和索引分別放在不同的磁盤上保證物理讀寫速度比較快
• 影響數(shù)據(jù)文件存儲結(jié)構(gòu)的因素
  • 存取時間
  • 存儲空間利用率
  • 維護代價
• 解決辦法
  • 適當冗余
  • 增加聚簇功能
• 何謂存取路徑
  • 確定如何建立索引
  • 對同一個關(guān)系要建立多條存取路徑才能滿足多用戶的多種應(yīng)用要求
  • 物理設(shè)計的第一個任務(wù)
• DBMS常用存取方法
  • 索引方法，目前主要是B+樹索引方法
  • 聚簇（Cluster）方法
  • HASH方法

數(shù)據(jù)分布設(shè)計

• 不同類型數(shù)據(jù)的物理分布
  • 將應(yīng)用數(shù)據(jù)（基本表）庶骄、索引单刁、日志羔飞、數(shù)據(jù)庫備份數(shù)據(jù)等合理安排在不同介質(zhì)中
• 應(yīng)用數(shù)據(jù)的劃分與分布
  • 根據(jù)數(shù)據(jù)的使用特征劃分（頻繁使用分區(qū)和非頻繁使用分區(qū)）
  • 根據(jù)時間、地點劃分（時間或地點相同的屬于同一分區(qū)）
  • 分布式數(shù)據(jù)庫系統(tǒng)（DDBS）中的數(shù)據(jù)劃分（水平劃分或垂直劃分）
  • 派生屬性數(shù)據(jù)分布（增加派生列或不定義派生屬性）
  • 關(guān)系模式的去規(guī)范化（降低規(guī)范化提高查詢效率）

確定系統(tǒng)配置

物理模式評估

• 對數(shù)據(jù)庫物理設(shè)計結(jié)果從存取時間恍风、存儲空間朋贬、維護代價等方面進行評估锦募，重點是時間和空間效率

數(shù)據(jù)庫應(yīng)用系統(tǒng)功能設(shè)計與實施

功能設(shè)計過程一般被劃分為總體設(shè)計虐骑、概要設(shè)計和詳細設(shè)計廷没。而具體到數(shù)據(jù)庫事務(wù)設(shè)計部分颠黎，又可分成事務(wù)概要設(shè)計和事務(wù)詳細設(shè)計

軟件體系結(jié)構(gòu)與設(shè)計過程

軟件體系結(jié)構(gòu)

• 軟件體系結(jié)構(gòu)={構(gòu)件狭归，連接件，約束}
• 軟件體系結(jié)構(gòu)是軟件系統(tǒng)中最本質(zhì)的東西疚宇。良好的體系結(jié)構(gòu)必須是普適灰嫉、高效和穩(wěn)定的
• 軟件體系結(jié)構(gòu)有多種風格和類型 ，如分層體系結(jié)構(gòu)股耽、模型-視圖-控制器（MVC）體系結(jié)構(gòu)、客戶端/服務(wù)器體系結(jié)構(gòu)等

軟件設(shè)計過程

• 軟件開發(fā)由設(shè)計敢艰、實現(xiàn)震嫉、測試三個環(huán)節(jié)組成票堵，設(shè)計又包含概要設(shè)計和詳細設(shè)計
• 概要設(shè)計的任務(wù)是進行軟件總體結(jié)構(gòu)設(shè)計悴势，可采用層次結(jié)構(gòu)圖建立軟件總體結(jié)構(gòu)圖军俊。詳細設(shè)計的任務(wù)是進行數(shù)據(jù)設(shè)計粪躬、過程設(shè)計及人機界面設(shè)計
• 設(shè)計原則：模塊化短蜕、信息隱藏朋魔、抽象與逐步求精。
• 軟件設(shè)計可選用結(jié)構(gòu)化設(shè)計方法扇雕、面向?qū)ο笤O(shè)計方法或面向數(shù)據(jù)設(shè)計方法等

DBAS總體設(shè)計

任務(wù)是確定系統(tǒng)總體框架

1. DBAS體系結(jié)構(gòu)設(shè)計

   • 將系統(tǒng)從功能镶奉、層次/結(jié)構(gòu)哨苛、地理分布等角度進行分解，劃分為多個子系統(tǒng)亿蒸，定義各子系統(tǒng)功能边锁；設(shè)計系統(tǒng)的全局控制砚蓬，明確各子系統(tǒng)間的交互和接口關(guān)系
   • 兩種常見的DBAS體系結(jié)構(gòu)
     • 客戶/服務(wù)器體系結(jié)構(gòu)（C/S）
     • 瀏覽器/服務(wù)器體系結(jié)構(gòu) （B/S）

2. 軟件體系結(jié)構(gòu)設(shè)計

3. 軟件硬件選型與配置設(shè)計

   • 總體設(shè)計階段需要對軟硬件設(shè)備做出合理選擇祟剔，并進行初步配置設(shè)計
   • 軟硬件選型涉及的內(nèi)容
     • 網(wǎng)絡(luò)及設(shè)備選型物延；數(shù)據(jù)存儲及備份方案;服務(wù)器選型叛薯；
     • 終端軟件環(huán)境耗溜；軟件開發(fā)平臺和語言、工具阿宅；
     • 系統(tǒng)中間件及第三方軟件選型

4. 業(yè)務(wù)規(guī)則初步設(shè)計

   • DBAS的各項業(yè)務(wù)活動具有邏輯上的先后關(guān)系洒放，可將它們表示成一個操作序列往湿，并用業(yè)務(wù)流程圖表

DBAS功能概要設(shè)計

在總體設(shè)計結(jié)果基礎(chǔ)上，將DBAS應(yīng)用軟件進一步細化為模塊/子模塊日川，組成應(yīng)用軟件的系統(tǒng)-子系統(tǒng)-模塊-子模塊層次結(jié)構(gòu)，并從結(jié)構(gòu)分歇、行為职抡、數(shù)據(jù)三方面進行設(shè)計

四個層次實現(xiàn)

• 表示層

  • 人機界面設(shè)計
  • 設(shè)計原則
    • ‘用戶自主控制’缚甩；反饋及時上下文感知壕探；容錯與錯誤恢復李请；界面標準常規(guī)导盅；輸入靈活；界面簡潔交互及時

• 業(yè)務(wù)邏輯層

  • 設(shè)計原則
    • 高內(nèi)聚低（松）耦合月培，即構(gòu)件單一原則纪蜒；構(gòu)件獨立功能纯续；接口簡單明確猬错；構(gòu)件間關(guān)系簡單，過于復雜逢唤，就細化鳖藕，分解
  • 設(shè)計內(nèi)容
    • 結(jié)構(gòu)著恩，行為邀摆，數(shù)據(jù)隧熙，接口，故障處理躏敢、安全設(shè)計绢彤，系統(tǒng)維護和保障等

• 數(shù)據(jù)訪問層

  • 完整的事務(wù)概要設(shè)計
    • 事務(wù)名稱
    • 訪問的關(guān)系表及其數(shù)據(jù)項
    • 事務(wù)邏輯（事務(wù)描述）
    • 事務(wù)用戶（使用醉者、啟動端壳、調(diào)用該事務(wù)的軟件模塊和系統(tǒng)）
  • 事務(wù)的特性(ACID特性)
    • 原子性（atomicity）
      • 原子性要求事務(wù)所包含的全部操作是一個不可分割的整體损谦，這些操作要么全部提交成功照捡，要么只要其中一個操作失敗，就<u>全部撤消</u>
    • 一致性（consistency）
    • 隔離性（isolation）
    • 持久性（durability）
    • 永久性（permanence）

• 數(shù)據(jù)持久層

  • 數(shù)據(jù)組織與存儲方面

DBAS功能詳細設(shè)計

• 表示層詳細設(shè)計

  • 原型迭代法
    • 初步設(shè)計：設(shè)計人機交互命令系統(tǒng)并優(yōu)化术羔。（總體設(shè)計）
    • 用戶界面細節(jié)設(shè)計。如組織形式叭披、風格嚼贡、彩色粤策，操作方式叮盘。（概要設(shè)計）
    • 原型設(shè)計與改進。（詳細設(shè)計）

• 業(yè)務(wù)邏輯層詳細設(shè)計

  • 計各模塊內(nèi)部處理流程和算法丙唧、具體數(shù)據(jù)結(jié)構(gòu)培漏、對外詳細接口等

應(yīng)用系統(tǒng)安全架構(gòu)設(shè)計

數(shù)據(jù)安全設(shè)計

• 數(shù)據(jù)庫的安全性保護

  • 用戶身份鑒別
    • Windows身份驗證
    • SQL身份驗證
  • 權(quán)限控制
  • 視圖機制

• 數(shù)據(jù)庫的完整性保護

  • 對數(shù)據(jù)設(shè)置一些約束條件（如實體完整性牌柄、參考完整性友鼻、用戶自定義完整性）
  • 約束條件作用對象
    • 列（類型、范圍虫碉、精度敦捧、排序）
    • 元組（記錄中各屬性之間的聯(lián)系約束）
    • 關(guān)系（若干記錄間兢卵、關(guān)系集合與集合之間的聯(lián)系）

• 數(shù)據(jù)庫的并發(fā)性控制

  • 實現(xiàn)數(shù)據(jù)庫并發(fā)控制的常用方法是封鎖技術(shù)
    • 排它鎖
    • 共享鎖
  • 避免死鎖的原則
    • 按照同一順序訪問資源。（如第一個事務(wù)提交或回滾后第二個事務(wù)進行）
    • 避免事務(wù)交互性
    • 采用小事務(wù)模式窃款，縮短長度和占用時間
    • 盡量使用記錄級別的（行鎖）晨继，少用表級別鎖
    • 使用綁定連接蜒茄，同一用戶打開的兩個或多個連接可以互相合作

• 數(shù)據(jù)庫的數(shù)據(jù)備份與恢復

  • 恢復的基本原理
    • 利用存儲在系統(tǒng)其他存儲器上的冗余數(shù)據(jù)（即數(shù)據(jù)備份）來重建
  • 備份與恢復策略
    • 雙機熱備（基于Active/Standby方式的服務(wù)器熱備）
    • 數(shù)據(jù)轉(zhuǎn)儲（也稱為數(shù)據(jù)備份）
    • 數(shù)據(jù)加密存儲（針對高敏感數(shù)據(jù)）

• 數(shù)據(jù)加密傳輸

  • 數(shù)字安全證書
  • 對稱密鑰加密
  • 數(shù)字簽名
  • 數(shù)字信封

環(huán)境安全設(shè)計

• 漏洞與補独┑怼：定期查找漏洞更新補丁
• 計算機病毒防護：殺毒軟件驻谆；實時監(jiān)控
• 網(wǎng)絡(luò)環(huán)境安全：防火墻；入侵檢測系統(tǒng)象对；網(wǎng)絡(luò)隔離（邏輯隔離與物理隔離）
• 物理環(huán)境安全 ：如防盜設(shè)施勒魔；UPS冠绢；溫濕度報警器等

制度安全設(shè)計

DBAS實施

1. 創(chuàng)建數(shù)據(jù)庫
   • 考慮因素：初始空間大小孵户；數(shù)據(jù)庫增量大邢目蕖方庭；訪問性能（如并發(fā)數(shù)，訪問頻率）
2. 數(shù)據(jù)裝載
   • 步驟：篩選數(shù)據(jù)—轉(zhuǎn)換數(shù)據(jù)格式—輸入數(shù)據(jù)—校驗數(shù)據(jù)
3. 編寫與調(diào)試應(yīng)用程序
4. 數(shù)據(jù)庫系統(tǒng)試運行（功能測試與性能測試）

UML與數(shù)據(jù)庫應(yīng)用系統(tǒng)

DBAS建模

統(tǒng)一建模語言（UML）

• UML只是一種建模語言龄减，不是一種建模方法
• 建模方法
  • 建模語言
  • 建模過程

UML的組成

由<u>語義</u>（自然語言）與<u>表示法</u>（可視化標準符號）組成

<u>語義定義在一個四層建模概念框架</u>

• 元元模型（Meta-Meta Model）希停，代表要定義的所有事物宠能。
• 元模型（ Meta Model ）违崇，UML的基本元素 羞延，“事物”概念的實例伴箩。
• 模型層（ Model ）嗤谚，UML的模型，類模型或類型模型渗钉。
• 用戶模型（ User Model ）鳄橘，UML模型的實例瘫怜，對象模型或?qū)嵗Ｐ汀?/li>

組成

• 系統(tǒng)
• 角色
• 用例
  • 關(guān)系包括<u>擴展赠涮、使用和組合</u>等幾種關(guān)系

UML的五種視圖

• 結(jié)構(gòu)笋除、實現(xiàn)垃它、行為国拇、環(huán)境和用例視圖
• 13種圖（UML2.0）：靜態(tài)結(jié)構(gòu)圖及行為圖兩類

DBAS業(yè)務(wù)流程與需求表達

業(yè)務(wù)流程與活動圖

最適合描述系統(tǒng)或子系統(tǒng)的工作流程

一張活動圖中有且只能有一個起點，可有多個結(jié)束點

活動圖用于低層次程序模塊的作用類似于流程圖务热，但<u>活動圖可以描述并行操作陕习，而流程圖只能描述串行操作</u>

1553067713476.png

系統(tǒng)需求與用例圖

• 系統(tǒng)：各種用例的“黑匣子”
• 角色：與系統(tǒng)交互的人或其他實體，可以是系統(tǒng)用戶也可以是其他系統(tǒng)或者硬件設(shè)備
• 用例：完整功能所有動作（一次操作）集合

DBAS系統(tǒng)內(nèi)部結(jié)構(gòu)的表達

系統(tǒng)結(jié)構(gòu)與類圖

在UML中损合，用類圖來描述系統(tǒng)靜態(tài)結(jié)構(gòu)嫁审，用順序圖和通信圖來表示系統(tǒng)動態(tài)結(jié)構(gòu)

• 類圖主要表達的是問題領(lǐng)域的概念模型律适。
• 類圖由類名、屬性及操作組成
• 類與類之間的關(guān)系
  • 關(guān)聯(lián)（聚集（共享聚集厂僧，組成或組合））颜屠，繼承（或叫泛化）甫窟，依賴粗井，精化（或叫實現(xiàn)）

系統(tǒng)結(jié)構(gòu)與順序圖

• 針對每一個特定用例，如何利用類圖規(guī)范的對象來完成用例交付的任務(wù)台夺，必須要利用順序圖
• 順序圖主要用于描述系統(tǒng)內(nèi)對象之間的消息發(fā)送和接收序列
• 順序圖中所有的元素梳星，都必須在類圖中存在冤灾。

系統(tǒng)結(jié)構(gòu)與通信圖

• 通信圖顯示對象間組織交互關(guān)系和鏈接。不側(cè)重交互順序归粉，用序列號來確定消息及其并發(fā)線程的順序糠悼。
• 順序圖強調(diào)時間倔喂，通信圖強調(diào)空間

DBAS系統(tǒng)微觀設(shè)計的表達

微觀設(shè)計與對象圖

對象圖是類圖的實例，描述特定時間中所有對象在系統(tǒng)中的結(jié)構(gòu)班挖，是一個快照

微觀設(shè)計與狀態(tài)機圖

• 狀態(tài)圖用來描述有關(guān)事件或?qū)ο蟮臓顟B(tài)轉(zhuǎn)移萧芙。
• 狀態(tài)圖只能有一個起始狀態(tài)双揪，可有多個結(jié)束狀態(tài)运吓。
• 狀態(tài)間的轉(zhuǎn)移由事件驅(qū)動

微觀設(shè)計與時間圖

時間圖中，整個矩形框就是一個生命線

DBAS系統(tǒng)宏觀設(shè)計的表達

宏觀設(shè)計與包圖

表達系統(tǒng)中不同的包疯趟、命名空間或不同的項目間彼此關(guān)系的圖稱為包圖

宏觀設(shè)計與交互概述圖

將活動圖和順序圖嫁接在一起的圖

宏觀設(shè)計與復合結(jié)構(gòu)圖

復合結(jié)構(gòu)圖適用于系統(tǒng)間的溝通接口

DBAS系統(tǒng)實現(xiàn)與部署的表達

系統(tǒng)實現(xiàn)與組件圖

用來表示系統(tǒng)的靜態(tài)實現(xiàn)視圖

系統(tǒng)實現(xiàn)與部署圖

部署圖說明實體組件拘哨，如可執(zhí)行程序倦青，將如何部署到實際的計算機中，描述系統(tǒng)中硬件和軟件的物理配置情況和系統(tǒng)體系結(jié)構(gòu)

數(shù)據(jù)庫及數(shù)據(jù)庫對象

創(chuàng)建及維護數(shù)據(jù)庫

SQL Server中數(shù)據(jù)庫的分類

• 系統(tǒng)數(shù)據(jù)庫（自動創(chuàng)建）
• master：所有系統(tǒng)級信息癣亚，元數(shù)據(jù)、端點绰咽、連接服務(wù)器和系統(tǒng)配置取募。
• msdb：代理服務(wù)調(diào)度報警和作業(yè)、記錄操作員時使用保存此類信息旺聚。
• tempdb：用于保存臨時對象和中間結(jié)果，每次啟動會重新創(chuàng)建碱璃。
• model: 所有數(shù)據(jù)庫的模板肛真，存放用戶數(shù)據(jù)庫公共信息。
• resource：只讀數(shù)據(jù)庫历极。在對象資源管理器中看不到。
• 用戶數(shù)據(jù)庫（保存與用戶業(yè)務(wù)有關(guān)的數(shù)據(jù)）

SQL Server數(shù)據(jù)庫的組成

• .mdf：主要數(shù)據(jù)文件。只有一個觅赊，大小不得小于3MB
• .ndf ：次要數(shù)據(jù)文件。有0個或多個，可在一個磁盤或多個磁盤存放
• .ldf：事務(wù)日志文件紫岩。至少有一個日志文件

數(shù)據(jù)庫存儲空間的分配

• 創(chuàng)建用戶數(shù)據(jù)庫時，model數(shù)據(jù)庫被自動復制到新建庫勋陪。
• 數(shù)據(jù)存儲的最小單位：數(shù)據(jù)頁（Page，簡稱頁）违孝。
• 1頁是一塊8KB的連續(xù)磁盤空間腮郊。
• 頁的大小決定了數(shù)據(jù)庫表中一行數(shù)據(jù)的最大大小轧飞。
  行不能垮頁存儲

數(shù)據(jù)庫文件組

• 主文件組（PRIMARY）
• 用戶定義文件組

注意：

• 日志文件不在文件組中，日志空間與數(shù)據(jù)空間分開管理掸绞。
• 一個文件不可以是多個文件組成員。
• 若文件組包含多個文件敞映，則在所有文件被填滿后才會自動循環(huán)增長。
• 只能指定一個文件組為默認文件組

數(shù)據(jù)庫文件的屬性

• 文件名及其位置
  • 邏輯文件名冕末，物理文件名
• 初始大小
  • 不能小于model數(shù)據(jù)庫主要數(shù)據(jù)文件的大小

用T-SQL創(chuàng)建數(shù)據(jù)庫

1553135348366.png

修改數(shù)據(jù)庫

• 收縮數(shù)據(jù)庫空間

  • 自動收縮：AUTO_SHRINK憔晒，默認false仅炊。
  • 手工收縮：收縮數(shù)據(jù)庫中某個文件大小呆馁；
    按比例收縮整個數(shù)據(jù)庫大小

• 添加和刪除數(shù)據(jù)庫文件

  • ALTER DATABASE database 
    { ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ] 
    | ADD LOG FILE < filespec > [ ,...n ] 
    | REMOVE FILE logical_file_name 
    | ADD FILEGROUP filegroup_name 
    | REMOVE FILEGROUP filegroup_name 
    | MODIFY FILE < filespec > 
    | MODIFY NAME = new_dbname 
    | MODIFY FILEGROUP filegroup_name {filegroup_property 
    | NAME = new_filegroup_name }
    | SET < optionspec > [ ,...n ] [ WITH < termination > ] 
    | COLLATE < collation_name > 
    }
    

• 擴大指定文件的大小

  • 　　ALTER DATABASE STU_DB 
      　　MODIFY FILE（NAME=student_data,  SIZE=8MB）
    

• 添加新的數(shù)據(jù)文件

  • ALTER DATABASE STU_DB
    　　ADD FILE（NAME=student_data2，
    　　FILENAME=‘E:\Data\student_data2.ndf’,
    　　SIZE=6MB,FILEGROWTH=0）
    

• 收縮整個數(shù)據(jù)庫的大小

  • DBCC SHRINKDATABASE

• 收縮指定文件的大小

  • DBCC SHRINKFILE

• 刪除數(shù)據(jù)庫文件

  • ALTER DATABASE STU_DB
  • REMOVE FILE student_log1

• 分離和附加數(shù)據(jù)庫

  • 分離數(shù)據(jù)庫
    • 實現(xiàn)將數(shù)據(jù)庫從一臺數(shù)據(jù)庫服務(wù)器移到另一臺畔咧，不需要重建誓沸。從實例中刪除拜隧，不刪除數(shù)據(jù)文件和日志文件，保持了數(shù)據(jù)文件和日志文件完整一致
    • EXEC sp_detach_db‘student’，‘true
  • 附加數(shù)據(jù)庫
    • 將分離的數(shù)據(jù)庫重新附加到數(shù)據(jù)庫管理系統(tǒng)中。必須指定主要數(shù)據(jù)文件的物理存儲位置和文件名

CREATE DATABASE students
　　On（FILENAME=‘F：\Data\Students_data1.mdf’）
　　FOR ATTACH

架構(gòu)

是數(shù)據(jù)庫下的一個邏輯命名空間貌矿，是數(shù)據(jù)庫對象的容器，一個數(shù)據(jù)庫包含一個或多個構(gòu)架，同一個數(shù)據(jù)庫內(nèi)架構(gòu)名唯一

定義構(gòu)架

　　CREATE SCHEMA [<構(gòu)架名>]
　　AUTHORIZATION<用戶名>

刪除構(gòu)架

DROP SCHEMA [<構(gòu)架名>]

分區(qū)表

是否創(chuàng)建分區(qū)取決于表當前數(shù)據(jù)量大小枷踏，以及將來數(shù)據(jù)量，還取決于表中數(shù)據(jù)的操作特點

創(chuàng)建分區(qū)表

1. 創(chuàng)建分區(qū)函數(shù)：告訴DBMS以什么方式進行分區(qū)
   　　CREATE PARTITION FUNCTION
2. 創(chuàng)建分區(qū)方案：作用是將分區(qū)函數(shù)生成的分區(qū)映射到文件組中
   　　CREATE PARTITION SCHEME
3. 使用分區(qū)創(chuàng)建表

1553147670365.png

1553147699151.png

索引

創(chuàng)建索引

1553147866081.png

刪除索引

1553147932467.png

索引視圖

建立唯一聚集索引的視圖，稱為索引視圖舌胶，也稱為<u>物化視圖</u>。建立索引后婉烟，視圖的結(jié)果集存放在數(shù)據(jù)庫中。<u>對基本表的修改會反映到索引視圖存儲的數(shù)據(jù)中</u>

基本概念

標準視圖也稱虛擬表，返回結(jié)果集與基本表一致而涉。標準視圖的結(jié)果集不永久存放

適合建立索引視圖的場合

若基礎(chǔ)數(shù)據(jù)以批處理形式定期更新，且主要是作為只讀數(shù)據(jù)進行處理季眷，可考慮在更新前刪除所有索引視圖，然后重建，提高更新性能

• 很少更新基礎(chǔ)數(shù)據(jù)橱赠，索引視圖效果更好
• 索引視圖可以提高這些查詢類型性能
  • 處理大量行的連接和聚合
  • 許多查詢經(jīng)常執(zhí)行的連接和聚合操作
• 索引視圖通常不會提高這些查詢類型性能
  • 具有大量寫操作的OLTP系統(tǒng)
  • 具有大量更新操作的數(shù)據(jù)庫
  • 不涉及聚合或連接的查詢
  • GROUP BY具有高基數(shù)度的數(shù)據(jù)聚合

定義索引視圖

• 定義索引視圖時吓著，視圖只能引用基本表，不能是其他視圖纺裁。

• 引用的所有基本表和視圖同一數(shù)據(jù)庫，所有者相同谚殊。

• 必須用SCHEMABINDING選項建視圖围肥。

• 視圖中表達式引用的所有函數(shù)必須確定置尔。

• 對視圖建立的第一個索引是唯一聚簇索引，之后在創(chuàng)建其他

• 　　CREATE VIEW;WITH SCHEMABINDING; CREATE 
    　　UNIQUE CLUSTERED INDEX …
  

高級數(shù)據(jù)庫查詢

一般數(shù)據(jù)查詢功能擴展

SELECT語句

SELECT [DISTINCT] [TOP n] select_list
查詢指定的列,加DISTINCT表不重復
[INTO new_table]
將查詢結(jié)果復制新表
[FROM table_source]
查詢行所在的表
[WHERE search_conditition]
指定搜索條件
[GROUP BY group_by_expression]
指定查詢結(jié)果分組條件
[HAVING search_condition]
指定組或聚合函數(shù)的搜索條件
[ORDER BY order_expression [ASC|DESC]]
指定結(jié)果集的排序方式
[COMPUTE expression] 
在結(jié)果集的末尾生成匯總數(shù)據(jù)行

使用TOP限制結(jié)果集

TOP n [percent][WITH TIES]
 Top n 前n行
 Top n [percent]前n%行
 [WITH TIES]：包括最后一行取值并列的結(jié)果

SELECT TOP 3 WITH TIES GoodsName， GoodsClassName，SaleUnitPrice泣刹，F(xiàn)ORM Table_Goods a JOIN Table_GoodsClass b
ON a.GoodsClassID=b.GoodsClassID ORDER BY SaleUnitPrice DESC

eg:查詢單價最高的前三種商品的商品名、商品類別和單價掀泳，包括并列情況

使用CASE函數(shù)

• 分情況顯示不同類型的數(shù)據(jù)
• 兩種類型
  • 簡單CASE函數(shù)
  • 搜索CASE函數(shù)

SELECT a.GoodsID,商品銷售類別=CASE 
WHEN COUNT(b.GoodsID)>10 THEN ‘熱門商品’
WHEN COUNT(b.GoodsID)BETWEEN 5 AND 10 THEN ‘一般商品’
WHEN COUNT(b.GoodsID)BETWEEN 1 AND 4 THEN ‘難銷商品’
ELSE ‘滯銷商品’
END
FROM Table_Goods a LEFT JOIN Table_SaleBillDetail b
ON a.GoodsID=b.GoodsID GROUP BY a.GoodsID

將查詢結(jié)果保存到新表中

/*SELECT 查詢列表序列  INTO <新表名>
 FROM 數(shù)據(jù)源……(其他行過濾、分組語句)
注意：表名前加#為局部臨時表庄拇，##為全局臨時表，只有表名為永久表。*/
SELECT * INTO #HD_Customer FROM Table_Customer  WHERE ……

查詢結(jié)果的并屈张、交、差運算

并運算（UNION）

將多個查詢結(jié)果合并為一個結(jié)果集

 SELECT 語句1
    UNION [ALL]
    SELECT 語句2
    UNION [ALL]
     ……

• 要進行合并的查詢笛厦，SELECT中列數(shù)必須相同，語義相同。
• 每個相對應(yīng)列的數(shù)據(jù)類型隱式兼容梦湘，如char（20）與varchar（40）。
• 合并后結(jié)果采用第一個SELECT語句的列標題瓣颅。
• 若需排序檬姥，則GROUP BY語句寫在最后一個SELECT之后，且排序的語句是第一個SELECT中的列名荞雏。

交運算(INTERSECT)

返回同時在兩個集合中出現(xiàn)的記錄

SELECT 語句1
INTERSECT 
SELECT 語句2
INTERSECT ……
SELECT 語句n

差運算

返回第一個集合中有而第二個集合中沒有的記錄

SELECT 語句1
EXCEPT 
SELECT 語句2
EXCEPT……
SELECT 語句n

相關(guān)子查詢

• 子查詢是一條包含在另一條SELECT語句里的SELECT語句蜈彼。外層的SELECT語句叫<u>外層查詢</u>棍辕，內(nèi)層的SELECT語句叫內(nèi)層查詢（或<u>子查詢</u>）。
• 子查詢總是寫在圓括號中

使用子查詢進行基于集合的測試

SELECT Cname,Address FROM Table_Customer
WHERE Address   IN(SELECT Address FROM Table_Customer  WHERE Cname=‘王曉’)
AND Cname抚太！= ‘王曉’

使用子查詢進行比較測試

/*查詢單價最高的商品的名稱和單價*/
SELECT Goodname，SaleUnitPrice FROM Table_Goods a WHERE SaleUnitPrice=
(SELECT MAX(SaleUnitPrice) FROM Table_Goods)

使用子查詢進行存在性測試

/*查詢購買了單價高于2000元商品額顧客的會員卡號。*/
SELECT DISTINCT CardID FROM Table_SaleBill WHERE EXISTS(SELECT * FROM Table_SaleBillDetail WHERE SaleBillID=Table_SaleBill.SaleBillID AND UnitPrice>2000)
insert table_name values(?,?)
update table_name set  ? =?
delete table_name where

其他形式的子查詢

替代表達式的子查詢

在SELECT的選擇列表中嵌入了一個只返回一個標量值的子查詢

SELECT Cname又谋，Address（SELECT COUNT(*) FROM Table_Customer b ON a.CardID=b. CardID WHERE CustomerID=‘C001’）AS TotalTimes FROM Table_Custmer Where CustomerID=‘C001’

派生表

也稱為內(nèi)聯(lián)視圖，是將子查詢作為一個表處理剩愧，產(chǎn)生的新表為“派生表”

/*實例：查詢至少買了C001和C002兩種商品的顧客號和顧客名。*/
SELECT CustomerID,CName 
FROM (SELECT * FROM Table_SaleBill a    JOIN Table_SaleBillDetail b ON  a.SaleBillID=b. SaleBillID WHERE    GoodsID=‘G001’) AS T1
JOIN (SELECT * FROM Table_SaleBill a     JOIN Table_SaleBillDetail b ON     a.SaleBillID=b. SaleBillID WHERE    GoodsID=‘G002’) AS T2
ON T1.CardID=T2.CardID
JOIN Table_Customer c ON c.CardID=T1. CardID

其他一些查詢功能

開窗函數(shù)

與聚合函數(shù)一樣锦积，開窗函數(shù)也是對行集組進行聚合計算，但是它不像普通聚合函數(shù)那樣每組只返回一個值，開窗函數(shù)可以為每組返回多個值橙垢，因為開窗函數(shù)所執(zhí)行聚合計算的行集組是窗口

• 在SQL Server 中，一組行被稱為一個窗口
• 開窗函數(shù)的調(diào)用格式為：函數(shù)名（列）OVER（選項）

/*查詢?nèi)空n程的Cno喂击，CName，Semester， Credit信息及其該學期開設(shè)課程的總秤茅、最高、平均五垮、最低Credit*/
SELECT Cno，CName莉撇，Semester ,Credit
SUM(Credit) OVER(PARTITION BY Semester) AS ‘Total’，
AVG(Credit) OVER(PARTITION BY Semester) AS ‘Avg’涂佃，
MIN(Credit) OVER(PARTITION BY Semester) AS ‘Min’，
MAX(Credit) OVER(PARTITION BY Semester) AS ‘Max’侨拦，
FROM Course

將OVER子句與排名函數(shù)一起使用

SELECT OrderID, ProductID, OrderQty 
    RANK() OVER(PARTITION BY OrderID ORDER　 BY　 OrderQty　DESC ) AS RANK
FROM OrderDetail
ORDER　 BY OrderID 

其他排名函數(shù)

• DENSE_RANK()
  • 排名是連續(xù)整數(shù)
• NTILE()
  • 將有序分區(qū)中的行劃分到指定數(shù)目的組中，編號從1開始，函數(shù)返回此行所屬的組的編號与涡。
• ROW_NUMBER()
  • 返回結(jié)果集中每個分區(qū)內(nèi)的序列號，每個分區(qū)的第一行從1開始酌畜。

公用表表達式

將查詢結(jié)果集指定一個臨時名字，這些命名的結(jié)果集就是公用表表達式

格式： 
WITH <common_table_expression>[,…n]
<common_table_expression>::=
Expression_name[(column_name [,…n])]
AS
(SELECT語句)

/*定義一個統(tǒng)計每個會員購買商品總次數(shù)的CTE，并利用CTE查詢會員卡號和購買商品的次數(shù)缎罢。*/
定義：WITH BuyCount（CardID,Counts）AS(SELECT CardID,Count(*) FROM Table_SaleBill GROUP BY CardID )
使用： AS(SELECT CardID,Counts FROM BuyCount ORDER BY Counts)

數(shù)據(jù)庫后臺編程技術(shù)

存儲過程

基本概念

使用T-SQL語言編寫代碼時，有兩種方式存儲和執(zhí)行代碼

1. 在客戶端存儲代碼，通過客戶端程序或SQL命令向DBMS發(fā)出操作請求，由DBMS將結(jié)果返回給用戶程序范抓。
2. 以子程序的形式將程序模塊存儲在數(shù)據(jù)庫中，供有權(quán)限的用戶通過調(diào)用反復執(zhí)行象泵。

存儲過程：即存儲在數(shù)據(jù)庫中供所有用戶程序調(diào)用的子程序。

類別

• 系統(tǒng)存儲過程
• 用戶自定義存儲過程
• 擴展存儲過程

優(yōu)點

• 極高的執(zhí)行效率。
• 增強代碼的重用性和共享性兄一。
• 使用存儲過程可以減少網(wǎng)絡(luò)流量。
• 使用存儲過程保證安全性蹋盆。
• 在大型數(shù)據(jù)庫中析藕，應(yīng)用程序訪問數(shù)據(jù)庫的最主要方式就是存儲過程竞慢。
• 存儲過程可以在系統(tǒng)啟動時自動執(zhí)行

創(chuàng)建、執(zhí)行和刪除存儲過程

1553151613387.png

1553152185380.png

/*建立查詢某個指定地區(qū)購買了單價高于指定價格商品的顧客購買信息，列出顧客姓名劫扒，購買商品名，單價，購買日期遮晚，會員積分少孝，其中默認地區(qū)是“長沙岳麓區(qū)”*/
CREATE PROCEDURE p_custbuy
@area varchar(20)=‘長沙岳麓區(qū)’血筑，@Price money
AS
SELECT……FROM……JOIN……
WHERE Address=@area AND SaleUnitPrice>@Price
執(zhí)行：EXEC  p_custbuy @Price=1000

刪除存儲過程

DROP PROCEDURE

用戶定義函數(shù)

類似于編程語言中的函數(shù)守问，其結(jié)構(gòu)與存儲過程類似，但函數(shù)必須有一個RETURN子句仿便，用于返回函數(shù)值

兩類用戶定義函數(shù)：量函數(shù)和表值函數(shù)荒勇。前者返回單個數(shù)據(jù)值，表值函數(shù)返回一個表

創(chuàng)建和調(diào)用標量函數(shù)

CREATE  FUNCTION ……RETURNS return_data_type
AS 
BEGIN
【函數(shù)體】
　　RETURN scalar_expression
END

/*創(chuàng)建查詢指定商品類別的商品種類數(shù)的標量函數(shù)*/
CREATE FUNCTION dbo.f_GoodsCount(@class varchar(10))
   RETURN int
AS 
BEGIN
   DECLARE @x int
   SELECT @x=count(*)FROM Table_GoodsClass a JOIN Table_Goods b
      ON a.GoodsClassID=b. GoodsClassID
      WHERE GoodsClassName= @class 
   RETURN @x
END  
/*調(diào)用標量函數(shù)*/
SELECT GoodsName AS 商品名卷玉，dbo.f_GoodsCount(‘服裝’)AS 種類數(shù)
FROM……WHERE……

創(chuàng)建和調(diào)用內(nèi)嵌表值函數(shù)

/*創(chuàng)建內(nèi)聯(lián)表值函數(shù)：*/
CREATE FUNCTION ……RETURNS TABLE
AS 
RETURN [(]select_stmt[)]

/*【實例】創(chuàng)建查詢指定類別的商品名和單價的內(nèi)聯(lián)表值函數(shù)品姓。*/
CREATE FUNCTION f_GoodsInfo(@class char(10))
   RETURNS TABLE
AS 
   RETURN(
     SELECT GoodName,SaleUnitPrice FROM Table_GoodClass a JOIN Table_Goods b ON a.GoodsClassID= B.GoodsClassID
     WHERE GoodClassName=@class)
/*調(diào)用： */
SELECT * FROM dbo.f_GoodsInfo(‘服裝’)

創(chuàng)建和調(diào)用多語句表值函數(shù)

調(diào)用建多語句表值函數(shù):在SELECT的FROM子句中使用

CREATE FUNCTION ……RETURNS @return_variable TABLE<table_type_definition定義返回的表結(jié)構(gòu)>
AS 
BEGIN
【函數(shù)體:SQL語句】
    RETURN 
END

/*【實例】創(chuàng)建查詢指定類別的商品名衬潦、單價、生產(chǎn)日期和商品種類的多語句表值函數(shù)。*/
CREATE FUNCTION f_GoodsDetails(@class varchar(20))
    RETURNS @f_GoodsDetails TABLE（
商品名 varchar（50）走越，
單價 money，
生產(chǎn)日期 datetime谆构，
種類數(shù) int）
AS  
BEGIN
  INSERT INTO @f_GoodsDetails 
  SELECT GoodsName,SaleUnitPrice，ProductionDate何荚，dbo. f_GoodsDetails (@class) FROM      Table_GoodsClass a JOIN  Table_Goods b ON a.GoodsClassID= B.GoodsClassID     WHERE GoodClassName=@class)
   RETURN
END
調(diào)用：SELECT * FROM dbo.f_GoodsDetail(‘服裝’)

刪除用戶自定義函數(shù)

DROP FUNCTION f_GoodsCount
DROP FUNCTION f_GoodsInfo
DROP FUNCTION f_GoodsDetails

觸發(fā)器

基本概念

特殊存儲過程，在對表中的數(shù)據(jù)進行UPDATE、INSERT需纳、DELETE操作時自動觸發(fā)執(zhí)行，常用于保證業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性口蝠，增強數(shù)據(jù)完整性約束能力

在SQL Server 2008中，對于更新操作的觸發(fā)器，系統(tǒng)將產(chǎn)生兩張邏輯工作表禁漓，即DELETED表和INSERTED表。<u>DELETED表用于存儲更新前數(shù)據(jù)</u>秘狞，<u>INSERTED用于更新后的數(shù)據(jù)</u>

SQL Server 2008支持三種類型

• DML
• DDL
• 登錄觸發(fā)器

適用場合:

• 完成比CHECK（只能實現(xiàn)同一表列之間取值約束）約束更復雜的數(shù)據(jù)約束。
• 保證數(shù)據(jù)庫性能而維護的非規(guī)范化數(shù)據(jù)靖诗。
• 可實現(xiàn)復雜的商業(yè)規(guī)則。
• 評估數(shù)據(jù)修改前后的表狀態(tài)促绵，并采取對策

創(chuàng)建觸發(fā)器

CREATE TRIGGER trigger_name ON 
{ table | view }
[WITH ENCRYPTION]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] 
[ DELETE ] }
AS 
sql_statement[…n] 

參數(shù)說明：

• FOR或AFTER：后觸發(fā)型，操作、約束檢查完成后觸發(fā)慢味。
• INSTEAD OF ：前觸發(fā)型辙谜，數(shù)據(jù)操作語句最多定義一個觸發(fā)器罐脊。執(zhí)行觸發(fā)器而非引發(fā)語句。若滿足完整性約束則需要重新執(zhí)行這些數(shù)據(jù)操作

注意

• 一個表可建多個觸發(fā)器上炎，每個觸發(fā)器可由三個操作觸發(fā)。ALTER類型同一操作上建立多個觸發(fā)器，INSTEAD OF類型同一操作上建立一個觸發(fā)器
• 所有建立和更改數(shù)據(jù)庫以及數(shù)據(jù)庫對象的語句诲祸、DROP語句不允許在觸發(fā)器中使用
• 觸發(fā)器不要返回任何結(jié)果

/*維護不同列的取值完整性的觸發(fā)器找田。保證“商品表”中單價列值與“商品價格變動表”中單價列值一致*/
CREATE TRIGGER UnitPriceConsistent
ON Table_PriceHistory FOR INSERT,UPDATE
AS
   DECLARE @NewPrice money
   SELECT @NewPrice=SaleUnitPrice FROM inserted   UPDATE Table_Goods SET SaleUnitPrice=@NewPrice 
    WHERE GoodsID IN (SELECT GoodsID FROM inserted)

/*【實例2】創(chuàng)建只允許刪除會員卡積分低于500分的顧客記錄的觸發(fā)器。*/
CREATE TRIGGER DeleteCust
ON Table_Customer INSTEAD OF DELETE 
AS
   IF NOT EXISTS(SELECT * FROM deleted WHERE CardID IN (SELECT CardID FROM Table_Card WHERE Score>=500))
   DELETE FROM Table_Customer WHERE CardID IN (SELECT CardID FROM deleted)

刪除觸發(fā)器

DROP TRIGGER OperateCon
DROP TRIGGER UnitPriceConsistent
DROP TRIGGER DeleteCust

游標

現(xiàn)對SELECT結(jié)果集的逐行處理

組成

游標結(jié)果集（SELECT返回結(jié)果集）與游標當前行指針（指向結(jié)果集中某一行）

特點

• 定位特定行；
• 從當前位置檢索一行或多行籽懦；
• 支持當前行數(shù)據(jù)修改；
• 對修改結(jié)果提供不同級別的可見性支持

使用游標

• 聲明游標

  • DECLARE cursor_name[1] CURSOR FOR select_statement[2]

• 打開游標

  • OPEN cursor_name

• 提取數(shù)據(jù)

  • FETCH [1]FROM cursor_name [INTO @ variable_name[,…n]]

• 關(guān)閉游標

  • CLOSE cursor_name

• 釋放游標

  • DEALLOCATE cursor_name

/*對Table_Customer表，定義一個查詢“長沙岳麓區(qū)”姓“王”的顧客姓名和郵箱的游標惫恼，并輸出游標結(jié)果。*/
DECLARE @cn VARCHAR(10） @cn VARCHAR(50)
DECLARE Cname_cursor CURSOR FOR
  SELECT Cname,Email FROM Table_Customer
  WHERE Cname LIKE ‘王%’AND Address LIKE ‘長沙岳麓區(qū)’
OPEN Cname_cursor
FETCH NEXT FROM Cname_cursor INTO @cn , @Email
WHILE @@ FETCH_STATUS=0
BEGIN
  PRINT’顧客姓名’+@cn +‘，郵箱：’+ @Email
  FETCH NEXT FROM Cname_cursor INTO @cn , @Email
   /*
NEXT：緊跟當前行返回結(jié)果行簇爆，并且當前行遞增為返回行。如果 FETCH NEXT 為對游標的第一次提取操作安寺，則返回結(jié)果集中的第一行言秸。NEXT 為默認的游標提取選項。
PRIOR：搜索返回緊鄰當前行前面的結(jié)果行，并且當前行遞減為返回行叛买。如果 FETCH PRIOR： 為對游標的第一次提取操作，則沒有行返回并且游標置于第一行之前。
FIRST：返回游標中的第一行并將其作為當前行
LAST：返回游標中的最后一行并將其作為當前行搜索动漾。
ABSOLUTE { n | @nvar}：如果 n 或 @nvar 為正，則返回從游標頭開始向后的第 n 行键思，并將返回行變成新的當前行看蚜。如果 n 或 @nvar 為負渴逻，則返回從游標末尾開始向前的第 n 行，并將返回行變成新的當前行雹洗。如果 n 或 @nvar 為 0，則不返回行螃成。n 必須是整數(shù)常量，并且 @nvar 的數(shù)據(jù)類型必須為 smallint、tinyint 或 int覆醇。
RELATIVE { n | @nvar}：如果 n 或 @nvar 為正，則返回從當前行開始向后的第 n 行常摧，并將返回行變成新的當前行肚豺。如果 n 或 @nvar 為負梗劫，則返回從當前行開始向前的第 n 行，并將返回行變成新的當前行走哺。如果 n 或 @nvar 為 0，則返回當前行对妄。在對游標進行第一次提取時，如果在將 n 或 @nvar 設(shè)置為負數(shù)或 0 的情況下指定 FETCH RELATIVE孝常，則不返回行。n 必須是整數(shù)常量，@nvar 的數(shù)據(jù)類型必須為 smallint半开、tinyint 或 int。*/       END
CLOSE Cname_cursor
DEALLOCATE Cname_cursor

安全管理

安全控制概述

安全性和完整性

• 安全性：保護數(shù)據(jù)以防止不合法用戶故意造成破壞谒拴。
• 完整性：保護數(shù)據(jù)以防止合法用戶無意中造成的破壞茂装。

安全控制的目標

保護數(shù)據(jù)免受意外或故意的丟失城侧、破壞或濫用

數(shù)據(jù)庫安全的威脅

可用性損失豆茫，機密性數(shù)據(jù)損失,私密性數(shù)據(jù)損失，偷竊和欺詐牵舵，意外的損害

安全控制模型

• 身份驗證（用戶）
• 操作權(quán)限控制（數(shù)據(jù)庫應(yīng)用程序與數(shù)據(jù)庫管理系統(tǒng)）
• 文件操作控制（操作系統(tǒng)）
• 加密存儲與冗余（數(shù)據(jù)庫）

授權(quán)和認證

認證是一種鑒定用戶身份的機制。授權(quán)是將合法訪問數(shù)據(jù)庫或數(shù)據(jù)庫對象的權(quán)限授予用戶的過程。包括認證用戶對對象的訪問請求

• 自主存取控制
• 強制存儲控制

存取控制

自主存取控制

通過SQL的GRANT,REVOKE,DENY語句來實現(xiàn)

• 權(quán)限種類：
  • 維護權(quán)限與操作權(quán)限（語句權(quán)限與對象權(quán)限）
• 用戶分類：
  • 系統(tǒng)管理員（sa）送火、數(shù)據(jù)庫對象擁有者、普通用戶

強制存取控制

為避免自主存取模式下數(shù)據(jù)的“無意泄露”

• 將全部實體分為主體和客體兩大類
  • 主體：系統(tǒng)活動實體，實際用戶和進程坦冠。
  • 客體：被動實體，受主體操縱判呕，包括文件犁嗅、基本表园爷、視圖
• 對于主體和客體，DBMS為它們的每個實例指派一個敏感度標記（Label）
• 敏感度標記：DBMS指派叠洗，主體為許可證級別，客體為密級。分絕密案腺、秘密、可信和公開等若干級別
• 規(guī)則：
  • 僅當主體許可證級別大于或等于客體密級，主體可以讀取相應(yīng)客體
  • 僅當主體許可證級別等于客體密級旱函，該主體才能寫相應(yīng)客體

審計跟蹤

實質(zhì)上是一種特殊的文件或數(shù)據(jù)庫。系統(tǒng)自動記錄用戶對常規(guī)數(shù)據(jù)的所有操作

對數(shù)據(jù)安全有輔助作用

統(tǒng)計數(shù)據(jù)庫的安全性

提供基于各種不同標準的統(tǒng)計信息或匯總數(shù)據(jù)

• 用于控制對統(tǒng)計數(shù)據(jù)庫的訪問。
• 統(tǒng)計數(shù)據(jù)庫允許用戶查詢聚合類型的信息纷纫，如總和、平均等，但不允許查詢個人信息

SQL Server 的安全控制

身份驗證模式

• Windows身份驗證模式
• 混合身份驗證模式

登錄帳戶

• 類別

  • SQL Server自身負責身份驗證的賬戶剖笙，內(nèi)置系統(tǒng)賬戶與用戶自己創(chuàng)建。
  • 登錄到SQL Server的Windows網(wǎng)絡(luò)賬戶，可以是組賬戶或用戶賬戶扳躬。

• 建立登錄賬戶

  • CREATE LOGIN login_name

• 修改登錄賬戶屬性

  • ALTER LOGIN login_name

• 刪除登錄賬戶

  • DROP LOGIN login_name

• CREATE LOGIN SQL_User1 WITH PASSWORD=‘a(chǎn)1b2c3XY’
  ALTER LOGIN SQL_User1 WITH PASSWORD=‘a(chǎn)4b5c6XY’
  ALTER LOGIN SQL_User3 WITH NAME=NewUser
  

數(shù)據(jù)庫用戶

用戶有了登錄帳戶亏狰，只能連接到SQL服務(wù)器促脉，并不具有訪問數(shù)據(jù)庫的權(quán)限

• 映射

  • 讓登錄賬戶成為數(shù)據(jù)庫用戶的操作成為映射。一個登錄賬戶可以映射為多個數(shù)據(jù)庫用戶。默認情況下丁逝，新建數(shù)據(jù)庫只有一個用戶：dbo，數(shù)據(jù)庫用戶的擁有者

• 建立數(shù)據(jù)庫用戶

  CREATE USER user_name[|FOR|FROM]
  LOGIN login_name
  

• Guest用戶

  • 特殊數(shù)據(jù)庫用戶，匿名訪問丢间，沒有映射到登錄賬戶的時候使用
  • GRANT CONNECT TO guest
REVOKE CONNECT TO guest

• 刪除數(shù)據(jù)庫用戶

  • DROP USER user_name

權(quán)限管理

• 對象級別的權(quán)限（6種）

  SELECT、INSERT饮六、UPDATE窟扑、DELETE、REFERENCES厂抖、EXECUTE

  • 授權(quán)語句

  /*GRANT 對象權(quán)限 ON 對象 TO （主體：數(shù)據(jù)庫用戶名或角色）[WITH GRANT OPTION]*/
  實例：
  GRANT SELECT ON Address TO abc
  GRANT EXECUTE ON OBJECT::HR.EI TO abc
  GRANT REFERENCES（EmployeeID）ON vEmp TO abc WITH GRANT OPTION
  

  • 拒絕權(quán)限

  /* DENY 對象權(quán)限 ON 對象 TO （主體：數(shù)據(jù)庫用戶名或角色） [CASCADE][AS主體]
  實例：*/
  DENY SELECT ON Address TO abc
  DENY EXECUTE ON OBJECT::HR.EI TO abc
  DENY REFERENCES（EmployeeID）ON vEmp TO abc CASCADE
  

  • 收權(quán)語句

  /* REVOKE 對象權(quán)限 ON 對象 TO （主體：數(shù)據(jù)庫用戶名或角色） [CASCADE][AS角色]*/
  實例：
  REVOKE SELECT ON Address TO abc
  REVOKE EXECUTE ON OBJECT::HR.EI TO abc
  REVOKE REFERENCES（EmployeeID）ON vEmp TO abc CASCADE
  

• 語句級別的權(quán)限

  /* CREATE DATABASE|PROCEDURE|TABLE|VIEW|FUNCTION
  BACKUP DATABASE|LOG */
  實例：
  GRANT CREATE DATABASE TO user0
  GRANT CREATE DATABASE , CREATE VIEW TO user1,user2
  DENY CREATE VIEW TO user1
  REVOKE CREATE DATABASE FROM user0
  

角色

一組具有相同權(quán)限的用戶就是角色

• 系統(tǒng)角色

  • 固定服務(wù)器角色（服務(wù)器級角色）
  • 固定數(shù)據(jù)庫角色（數(shù)據(jù)庫級角色）

• 用戶角色

  • 均是數(shù)據(jù)庫級角色

• 固定服務(wù)器角色

  • Bulkadmin：執(zhí)行BULK INSERT語句權(quán)限墙懂。

  • Dbcreator：創(chuàng)建、修改巧勤、刪除還原數(shù)據(jù)庫權(quán)限迁匠。

  • Diskadmin：具有管理磁盤文件的權(quán)限

  • Processadmin：管理運行進程權(quán)限延曙。

  • Securityadmin：專門管理登錄賬戶傍药、讀取錯誤日志執(zhí)行CREATE DATABASE 權(quán)限的賬戶擦酌，便捷

  • Serveradmin：服務(wù)器級別的配置選項和關(guān)閉服務(wù)器權(quán)限。

  • Setupadmin：添加刪除鏈接服務(wù)器笼平。

  • Sysadmin：系統(tǒng)管理員 ，Windows超級用戶自動映射為系統(tǒng)管理員。

  • Public：系統(tǒng)預定義服務(wù)器角色痛悯，每個登錄名都是這個角色的成員巡扇。沒有授予或拒絕特定權(quán)限斋枢，則將具有這個角色權(quán)限。

  • /* （1）為固定服務(wù)器角色添加成員 */
    Sp_addsrvrolemember
    EXEC Sp_addsrvrolemember ‘user1’轩勘，‘sysadmin’
    /* （2）刪除固定服務(wù)器角色成員 */
    Sp_dropsrvrolemember
    EXEC Sp_dropsrvrolemember ‘user1’，‘sysadmin’
    

• 固定數(shù)據(jù)庫角色

  • Db_accessadmin:添加或刪除數(shù)據(jù)庫權(quán)限

  • Db_backupoperator：備份數(shù)據(jù)庫、日志權(quán)限

  • Db_datareader:查詢數(shù)據(jù)庫數(shù)據(jù)權(quán)限

  • Db_datawriter：具有插入村缸、刪除、更改權(quán)限

  • Db_ddladmin：執(zhí)行數(shù)據(jù)定義的權(quán)限

  • Db_denydatareader：不允許具有查詢數(shù)據(jù)庫中所有用戶數(shù)據(jù)的權(quán)限。

  • Db_denydatawriter：不允許具有插入属提、刪除火惊、更改數(shù)據(jù)庫中所有用戶數(shù)據(jù)權(quán)限尸疆。

  • Db_owner：具有全部操作權(quán)限按灶，包括配置噪矛、維護、刪除數(shù)據(jù)庫势就。

  • Db_securityadmin：具有管理數(shù)據(jù)庫角色、角色成員以及數(shù)據(jù)庫中語句和對象的權(quán)限贼陶。

  • /* （1）為固定數(shù)據(jù)庫角色添加成員 */
    Sp_addsrvrolemember
    EXEC Sp_addrolemember ‘Db_datareader’烘贴，‘SQL_User2’
    /* （2）刪除固定服務(wù)器角色成員 */
    Sp_droprolemember
    EXEC Sp_droprolemember ‘Db_datareader’,‘SQL_User2’
    

• 用戶定義的角色

  • 用戶定義的角色屬于數(shù)據(jù)庫一級。
    用來簡化使用數(shù)據(jù)庫時的權(quán)限管理。

  • 用戶定義的角色成員可以是用戶定義角色或數(shù)據(jù)庫用戶汽纠。注意： <u>角色中的成員擁有的權(quán)限=成員自身權(quán)限+所在角色權(quán)限钓账。但若某個權(quán)限在角色中被拒絕服协，則成員不再擁有</u>

  • CREATE ROLE
    實例： CREATE ROLE MathDept [AUTHORIZATION Software]
    注意：為用戶定義角色授權(quán)、添加张峰、刪除用戶定義的角色中的成員與固定數(shù)據(jù)庫角色一致。 
    刪除用戶定義角色
    DROP ROLE
    實例：DROP ROLE MathDept
    

Oracle的安全管理

Oracle的安全機制

• 數(shù)據(jù)庫級的安全控制

  • 通過用戶身份認證和授予用戶相應(yīng)系統(tǒng)權(quán)限來保證

• 表級、列級敌厘、行級的安全控制

  • 通過授予或回收對象權(quán)限保證曹步。支持集中式尿孔、分布式物赶、跨平臺應(yīng)用

兩級安全管理員

• 全局級

  • 負責管理侵续、協(xié)調(diào)鹉动，維護全局數(shù)據(jù)一致性和安全性缸血；

• 場地級

  • 負責本結(jié)點數(shù)據(jù)庫安全性，用戶管理郎汪、系統(tǒng)特權(quán)與角色管理

用戶與資源管理

• DBA用戶

  • 由DBMS自動創(chuàng)建，sys與system用戶，擁有全部系統(tǒng)特權(quán)凝危。

• 普通用戶

  • 由DBA用戶或有相應(yīng)特權(quán)的用戶創(chuàng)建，并授予系統(tǒng)特權(quán)

• 建立用戶

  CREATE USER use1 IDENTIFIED BY 123456
  DEFAULT TABLESPACE student（存儲在student表空間）
  QUOTA 5M ON student（限制使用空間為5M）
  

• 管理用戶和資源

  DBA特權(quán)用戶可以改變一個用戶資源使用限額昏翰、密碼叔汁、登錄次數(shù)等

  ALTER USER use1
  QUOTA 60M ON student（限制使用空間為60M）
  ALTER USER use1 IDENTIFIED BY 12345678
  

• 刪除用戶

  DROP USER 
  DROP USER user1 CASCADE（刪除用戶及其所擁有的全部對象）
  

權(quán)限管理

• 系統(tǒng)特權(quán)

  • Connect： 不能建立任何對象，可以查詢數(shù)據(jù)字典及訪問數(shù)據(jù)庫對象像屋。
  • Recource ：可建立數(shù)據(jù)庫對象（表奏甫、視圖胜蛉、索引……）
  • DBA ：擁有預定義的全部權(quán)限

• 對象特權(quán)

  • 用于維護表級、行級攘须、列級數(shù)據(jù)的安全性。

    實例：
    GRANT all ON dep TO user1
    GRANT select(tno,tname,sal) ON teacher TO 
    user2
    

數(shù)據(jù)庫運行維護與優(yōu)化

數(shù)據(jù)庫運行維護基本工作

維護工作

• 數(shù)據(jù)庫轉(zhuǎn)儲與恢復
• 數(shù)據(jù)庫安全性、完整性控制
• 檢測并改善數(shù)據(jù)庫性能
• 數(shù)據(jù)庫的重組和重構(gòu)
  • 重組不修改數(shù)據(jù)庫原有設(shè)計的邏輯結(jié)構(gòu)和物理結(jié)構(gòu)
  • 重構(gòu)部分修改模式和內(nèi)模式

運行狀態(tài)監(jiān)控與分析

• 根據(jù)監(jiān)控分析實現(xiàn)不同，分為：
  • 數(shù)據(jù)庫系統(tǒng)建立的自動監(jiān)控機制
  • 由DBMS自動監(jiān)測數(shù)據(jù)庫的運行情況昆著。
  • 管理員手動實施的監(jiān)控機制
• 根據(jù)監(jiān)控對象不同梧宫，分為:
  • 數(shù)據(jù)庫構(gòu)架體系的監(jiān)控
    • 監(jiān)控空間基本信息脓豪、空間使用率與剩余空間大小等。
  • 數(shù)據(jù)庫性能監(jiān)控
    • 監(jiān)控數(shù)據(jù)緩沖區(qū)命中率、庫緩沖印蔬、用戶鎖捐韩、索引使用瞧预、等待事件等

數(shù)據(jù)庫存儲空間管理

SQL Server數(shù)據(jù)庫中 一個邏輯上的數(shù)據(jù)庫直接和一組物理上的數(shù)據(jù)文件對應(yīng)，沒有表空間概念

• 空間使用情況變化帶來的問題
  • 降低數(shù)據(jù)庫系統(tǒng)服務(wù)性能
  • 空間溢出導致災(zāi)難停機事故
• 數(shù)據(jù)的存儲結(jié)構(gòu)分為
  • 邏輯存儲結(jié)構(gòu)
  • 物理存儲結(jié)構(gòu)
• DBMS對空間的管理包括
  • 創(chuàng)建數(shù)據(jù)庫空間、更改空間大小硝枉、刪除空間璃诀、修改空間狀態(tài)裁良，新建、移動犀变、關(guān)聯(lián)數(shù)據(jù)文件等。

數(shù)據(jù)庫性能優(yōu)化

數(shù)據(jù)庫運行環(huán)境

• 外部環(huán)境

  • 數(shù)據(jù)庫性能和外部環(huán)境有很大關(guān)系嚣崭，主要外部條件包括：CPU（CPU的處理能力是衡量計算機性能的一個標志）粗俱、網(wǎng)絡(luò)（大量的SQL數(shù)據(jù)在網(wǎng)絡(luò)上傳輸會導致網(wǎng)速變慢）

• 調(diào)整內(nèi)存分配

  • 調(diào)整相關(guān)參數(shù)控制數(shù)據(jù)庫內(nèi)存分配，很大程度改善數(shù)據(jù)庫系統(tǒng)性能荠卷。

• 調(diào)整磁盤I/O

  • 數(shù)據(jù)庫性能優(yōu)劣的重要度量是響應(yīng)時間
  • 令I(lǐng)/O時間最小化怜姿，減少磁盤上文件競爭帶來的瓶頸

• 調(diào)整資源競爭

  • 修改參數(shù)以控制連接到數(shù)據(jù)庫的最大進程數(shù)。
  • 減少調(diào)度進程的競爭
  • 減少多線程服務(wù)進程競爭
  • 減少重做日志緩沖區(qū)競爭
  • 減少回滾段競爭

參數(shù)調(diào)整

模式調(diào)整

• 規(guī)范化
  • 高效率利用存儲空間披诗，減少數(shù)據(jù)的冗余唱歧，減少數(shù)據(jù)的不一致性
  • 規(guī)范化關(guān)系解決了數(shù)據(jù)維護的異常蕊苗，并使數(shù)據(jù)冗余最小化，但會導致數(shù)據(jù)處理性能下降
• 反規(guī)范化
  • 增加派生冗余列
    • 增加的列由表中的一些數(shù)據(jù)項經(jīng)過計算生成。
    • 作用：查詢時減少連接操作非剃，避免使用聚合函數(shù)
  • 增加冗余列
    • 增加的列由表中的一些數(shù)據(jù)項經(jīng)過計算生成鬓催。
    • 作用：查詢時減少連接操作，避免使用聚合函數(shù)
  • 重新組表
    • 當用戶經(jīng)常查看的某些數(shù)據(jù)是由多個表連接之后才能得到塌西，就可以考慮先把這些數(shù)據(jù)重新組成一個表筹淫，這樣在查詢時會減少連接提高效率
  • 分割表
    • 水平分割：
      • 根據(jù)行的使用特點進行分割庵寞，分割之后所有表的結(jié)構(gòu)都相同逸尖。而存儲的數(shù)據(jù)不同。使用并（Union）操作。
    • 垂直分割：
      • 根據(jù)列的特點分割乞封，分割后所得表除了都包含主碼外其他列都不相同仔戈。通常將常用列與不常用列分別放在不同表中，查詢減少I/O次數(shù)墓卦。缺點是使用連接（Join）操作
  • 新增匯總表
    • 為降低匯總操作的時間山叮，將頻繁使用的統(tǒng)計中間結(jié)果或最終結(jié)果存儲在匯總表中，從而降低數(shù)據(jù)訪問量和匯總操作的CPU計算量

數(shù)據(jù)庫存儲優(yōu)化

• 索引視圖
  • 定義：包括一個查詢結(jié)果的數(shù)據(jù)庫對象问麸，是預先計算并保存表連接或聚集等耗時較多的操作結(jié)果布轿。（一個定期刷新數(shù)據(jù)的視圖福铅，自動刷新或人工刷新）
    適用于多個數(shù)據(jù)量較大的表進行連接操作及分布式數(shù)據(jù)庫中在多站點的表進行連接時使用。
    物化視圖還可以進行遠程數(shù)據(jù)的本地復制（物化視圖的存儲也稱為快照），用于實施數(shù)據(jù)庫間的同步
• 聚集
  • 一個聚集是一組表撞芍，將經(jīng)常一起使用的具有同一公共列值的多個表中的數(shù)據(jù)行存儲在一起衡创，由公共列構(gòu)成聚集碼。
  • 作用：最小化必須執(zhí)行的I/O次數(shù)巢寡。
  • 注意：將記錄插入聚集的表之前舆蝴，必須建立聚集索引，且按聚集碼進行索引叫胖；對于聚集中的多個表，聚集值只存儲一次。
  • 劣勢：聚集表的插入刊侯、更新膝藕、刪除性能差，具體使用要權(quán)衡

查詢優(yōu)化

• 合理使用索引
  • 索引提高查詢效率，索引增加系統(tǒng)開銷
  • 建立索引原則
    • 是否為一個屬性建索引：該屬性是碼或存在某個查詢中被使用
    • 在哪些屬性建立索引：若一個關(guān)系的多個屬性共同出現(xiàn)在若干個查詢中，一般會采用多屬性索引昙篙。
    • 是否建立聚簇索引：聚簇索引適合范圍查詢胰苏，可建立多屬性索引。優(yōu)點體現(xiàn)在數(shù)據(jù)記錄存取過程中埃仪。
    • 使用散列還是樹索引：散列適合等值查詢；關(guān)系數(shù)據(jù)庫多使用B+索引甘有，支持作為搜索碼的屬性上的等值查詢和范圍查詢
  • 索引使用原則：
    • 經(jīng)常在查詢中作為條件被使用的列泛释，應(yīng)為其建立索引。
    • 頻繁進行排序或分組的列魂贬，應(yīng)為其建立索引机蔗。
    • 一個列的值域很大時，應(yīng)為其建立索引牙言。
    • 如果待排列的列有多個蚕断，建復合索引。
    • 可以使用系統(tǒng)工具來檢查索引完整性障陶，必要時進行修復。當數(shù)據(jù)表更新大量數(shù)據(jù)后，刪除并重建索引以提高查詢
    • 索引建立完成后朋凉，運行期間還需調(diào)優(yōu)吓揪。
      優(yōu)的目的：動態(tài)地評估需求
  • 索引調(diào)整和修改的原因：
    • 由于缺少索引，某些查詢語句執(zhí)行時間過長习勤。
    • 某些索引自始至終沒有使用眷唉，卻占用了較多磁盤空間。
    • 某些索引建立在被頻繁改變的屬性上，導致系統(tǒng)開銷過大
• 避免或簡化排序
  • ORDER BY和GROUP BY語句的執(zhí)行涉及排序嚷掠，磁盤排序開銷很大，應(yīng)利用索引自動以適當?shù)拇涡虍a(chǎn)生輸出。
  • 影響優(yōu)化器的因素：
    • 由于現(xiàn)有索引不足踩晶，導致排序索引中不包括一個或幾個待排序的列计济。
    • Group by和order by子句中列的次序與索引次序不一致。
    • 排列的列來自不同的表。
    • 為避免不必要的排序西壮，要正確地增建索引抡草，合理合并數(shù)據(jù)庫表，如排序不可避免，則簡化它赴魁，如縮小排序列的范圍
• 消除對大型表數(shù)據(jù)的順序存取
  • 嵌套查詢中凝颇，對表的順序存取嚴重影響查詢效率。
  • 優(yōu)化方法：對連接列進行索引盔腔，或使用并集來避免順序存取
• 避免復雜正則表達式
  • 消耗較多CPU資源進行字符串匹配
• 使用臨時表加速查詢
  • 將表的一個子集進行排序并創(chuàng)建臨時表
• 用排序來取代非順序磁盤存取
  • 非順序磁盤存取最慢。使用以數(shù)據(jù)庫排序功能為基礎(chǔ)的SQL替代非順序存取
• 不充分的連接條件
  • 左（右）外連接包含與NULL數(shù)據(jù)匹配决左，相比內(nèi)連接继找，代價可能很高
• 存儲過程
  • 盡量使用自帶返回參數(shù)，而非自定義返回參數(shù)，減少不必要參數(shù)，避免數(shù)據(jù)冗余
• 不要隨意使用游標
  • 占用較多系統(tǒng)資源鸣戴。尤其是大規(guī)模并發(fā)情況下半哟，很容易使得系統(tǒng)資源耗盡而崩潰
• 事務(wù)處理
  • 一旦將多個處理放入事務(wù)，會降低系統(tǒng)處理速度体捏。將頻繁操作的多個可分割的處理過程放入多個存儲過程中年栓，這樣就大大提高系統(tǒng)響應(yīng)速度

SQL Server 性能工具

• SQL Server Profiler
• 數(shù)據(jù)庫引擎優(yōu)化顧問

故障管理

故障管理概述

故障類型及其解決方案

事務(wù)內(nèi)部故障

• 預期的事務(wù)內(nèi)部故障
  • 可通過事務(wù)過程本身發(fā)現(xiàn)的惰瓜。解決辦法：事務(wù)回滾
• 非預期的事務(wù)內(nèi)部故障
  • 不能由事務(wù)程序處理的故障，如運算溢出等痹届。事務(wù)故障的恢復由<u>系統(tǒng)自動完成</u>

系統(tǒng)故障

• 運行期間奏篙，由于硬件故障敛熬、數(shù)據(jù)庫軟件及OS漏洞、突然斷電等故障，導致數(shù)據(jù)不一致
• 解決辦法：重啟后，撤銷（UNDO）所有未提交的事務(wù)庸诱；重做（REDO）所有已提交的事務(wù)聚谁；

介質(zhì)故障

• 又稱硬故障习霹，運行期間由于磁頭碰撞伪阶、磁盤損壞、強磁干擾注暗、天災(zāi)人禍等毙沾，使得數(shù)據(jù)丟失的一類故障
• 容錯策略：
  • 軟件容錯
    • 使用數(shù)據(jù)庫備份及事務(wù)日志文件，通過恢復技術(shù)焚刺，恢復數(shù)據(jù)庫到備份結(jié)束時的狀態(tài)蔓姚。若故障導致事務(wù)日志文件丟失房揭，則不能完全恢復
  • 硬件容錯
    • 使用雙物理存儲設(shè)備
      • 如雙硬盤鏡像。缺點：自然災(zāi)害或機房水災(zāi)、火災(zāi)導致雙硬盤同時損壞狱掂，則失去保護作用希柿。
    • 設(shè)計兩套相同的數(shù)據(jù)庫系統(tǒng)端姚，通過數(shù)據(jù)庫軟件機制昏鹃，同步變化數(shù)據(jù)。

計算機病毒故障

• 病毒是惡意的計算機程序，破壞OS及數(shù)據(jù)庫系統(tǒng)（破壞數(shù)據(jù)文件為主）
• 解決辦法：防火墻、殺毒軟件、數(shù)據(jù)庫備份文件。

數(shù)據(jù)庫恢復技術(shù)概述

• 恢復的基本原理是<u>冗余</u>蒜田。
• 數(shù)據(jù)庫恢復：把數(shù)據(jù)庫從錯誤狀態(tài)恢復到某一已知的正確狀態(tài)。
• 在DBMS中窝撵，數(shù)據(jù)庫恢復子系統(tǒng)占<u>10%</u>以上。
• 恢復機制涉及兩個問題：
  • 如何建立冗余數(shù)據(jù)
    • 數(shù)據(jù)備份、登記日志文件、數(shù)據(jù)庫復制、數(shù)據(jù)庫鏡像几晤、為段設(shè)立保存點以及使用后備段與現(xiàn)行頁表來支持對段的保存等
    • 最常用的是<u>數(shù)據(jù)備份和登記日志文件</u>
  • 如何利用這些冗余數(shù)據(jù)實施數(shù)據(jù)庫恢復热芹。

數(shù)據(jù)轉(zhuǎn)儲

指DBA或DBMS定期復制數(shù)據(jù)庫，并將其存放到其他介質(zhì)的過程。這些保存的副本被稱為后援副本或后備副本

靜態(tài)轉(zhuǎn)儲和動態(tài)轉(zhuǎn)儲

• 靜態(tài)轉(zhuǎn)儲：
  • 期間系統(tǒng)不能運行其他事務(wù)，不允許任何存取翻诉、修改活動芦圾。
    靜態(tài)轉(zhuǎn)儲保證數(shù)據(jù)的一致性，但降低了數(shù)據(jù)庫的可用性壳澳。轉(zhuǎn)儲和事務(wù)是互斥的褥紫。
• 動態(tài)轉(zhuǎn)儲：
  • 即允許轉(zhuǎn)儲和事務(wù)并發(fā)執(zhí)行弃酌。動態(tài)轉(zhuǎn)儲不能保證轉(zhuǎn)儲數(shù)據(jù)的一致性
  • <u>動態(tài)轉(zhuǎn)儲+日志文件</u>（記錄轉(zhuǎn)儲期間各事務(wù)對數(shù)據(jù)庫的修改活動記錄）：既保證數(shù)據(jù)一致性又提高了數(shù)據(jù)庫的可用性

數(shù)據(jù)轉(zhuǎn)儲機制

• 完全轉(zhuǎn)儲
  • 對數(shù)據(jù)庫中所有數(shù)據(jù)進行轉(zhuǎn)儲。占據(jù)較多時間和空間，但恢復時間短。
• 增量轉(zhuǎn)儲
  • 只復制上次轉(zhuǎn)儲后發(fā)生變化的文件或數(shù)據(jù)塊。 所需時間及空間短，但只能和完全轉(zhuǎn)儲配合才能對數(shù)據(jù)庫進行恢復永丝。
• 差量轉(zhuǎn)儲
  • 對最近一次數(shù)據(jù)庫完全轉(zhuǎn)儲以來發(fā)生的數(shù)據(jù)變化進行轉(zhuǎn)儲

多種轉(zhuǎn)儲方法結(jié)合使用

• 僅使用完全轉(zhuǎn)儲：占據(jù)時間和空間多闯冷，代價大坎弯。
• 完全轉(zhuǎn)儲+增量轉(zhuǎn)儲：恢復時間較長。
• 完全轉(zhuǎn)儲+差量轉(zhuǎn)儲：恢復時間短

日志文件

日志文件的概念

DBMS運行中，將所有事務(wù)的修改操作登記到日志文件

• 作用：事務(wù)故障恢復和系統(tǒng)故障恢復必須使用日志文件。
• 在動態(tài)轉(zhuǎn)儲方式中<u>必須建立日志文件</u>。
• 在靜態(tài)轉(zhuǎn)儲方式中也可使用日志文件

日志文件的格式與內(nèi)容

• 以記錄為單位的日志文件
  • 各個事務(wù)的開始標記 BEGIN TRANSACTION
  • 各個事務(wù)的結(jié)束標記 COMMIT OR ROLLBACK
  • 各個事務(wù)的所有更新操作
• 以數(shù)據(jù)塊為單位的日志文件
  • 事務(wù)標識和被更新的數(shù)據(jù)塊

登記日志文件的原則

• 登記的次序嚴格按并行事務(wù)執(zhí)行的時間次序。
  • 保證事務(wù)對數(shù)據(jù)庫的操作的可再現(xiàn)性和正確性。
• 必須先寫日志文件，后寫數(shù)據(jù)庫破花。
  后順序?qū)е卤厝恍?/li>

檢查點

• 檢查點的作用
  • 最大限度地減少數(shù)據(jù)完全恢復時必須執(zhí)行的日志部分
• 檢查點的引入
  • 在日志中增加檢查點記錄峭梳，增加一個“重新開始文件”孵运。
  • 檢查點記錄的內(nèi)容：建立檢查點時刻正執(zhí)行的事務(wù)清單；這些事務(wù)最近一個日志記錄的地址。
  • 重新開始文件記錄的內(nèi)容：各個檢查點記錄在日志中的地址
• 基于檢查點的恢復步驟
  1. 從“重新開始文件”中找到最后一個檢查點記錄在日志文件中的地址，從而找到最后一個檢查點記錄。
  2. 由該檢查點記錄得到執(zhí)行的事務(wù)清單ACTIVE_LIST雕憔。
  3. 從檢查點開始正向掃描文件琉苇。
  4. 對UNDO-LIST中的事務(wù)執(zhí)行UNDO抡诞， 對REDO-LIST中的事務(wù)執(zhí)行REDO

硬件容錯方案

概述

由于數(shù)據(jù)庫運行的硬件故障、機房電力故障、機房自然災(zāi)害等，要求從硬件級別對DBMS進行保護

磁盤保護技術(shù)RAID

冗余技術(shù)

• 鏡像冗余
  • 即把所有的數(shù)據(jù)復制到其他設(shè)備漆魔。
  • 額外開銷大：更多的磁盤、控制器、電纜。
• 校驗冗余
  • 對成員磁盤的數(shù)據(jù)執(zhí)行異或（XOR）操作，得到其校驗值格带，并存放在另外的校驗盤上棺亭。
  • 現(xiàn)復雜钟鸵，但比鏡像冗余占據(jù)的空間小

采用的方法

• RAID-0：
  • 將多個磁盤合并成一個大的磁盤，不具有冗余，并行I/O，速度最快约啊。
• RAID-1：
  • 兩組以上的N個磁盤相互作鏡像外傅，在一些多線程操作系統(tǒng)中能有很好的讀取速度奥洼，理論上讀取速度等于硬盤數(shù)量的倍數(shù)估盘，另外寫入速度有微小的降低。只要一個磁盤正常即可維持運作，可靠性最高。
• RAID-5：
  • RAID Level 5是一種儲存性能套媚、數(shù)據(jù)安全和存儲成本兼顧的存儲解決方案本辐。它使用的是Disk Striping（硬盤分區(qū)）技術(shù)调窍。RAID 5至少需要三顆硬盤缔恳，RAID 5不是對存儲的數(shù)據(jù)進行備份扑眉，而是把數(shù)據(jù)和相對應(yīng)的奇偶校驗信息存儲到組成RAID5的各個磁盤上弓千，并且奇偶校驗信息和相對應(yīng)的數(shù)據(jù)分別存儲于不同的磁盤上。
• RAID-10：
  • RAID0與RAID-1的組合體，繼承了前者的快速摄闸，后者的安全熏兄。 RAID-10冗余度為50%

服務(wù)器容錯技術(shù)

• 引入服務(wù)器容錯原因
  • 解決服務(wù)器硬件異常問題硝清。
• 服務(wù)器容錯技術(shù)簡介
  • 采用兩臺相同的服務(wù)器查邢，共享存儲設(shè)備。（雙機熱備，Active-Standby）
  • 兩臺服務(wù)器之間會有私有網(wǎng)絡(luò)進行心跳檢測
• 服務(wù)器接管過程
• 其他服務(wù)器容錯技術(shù)
  • 硬件級別：自行設(shè)計制造的專用軟硬件架構(gòu)。
  • 軟件級別：專門的服務(wù)器級別容錯技術(shù)，如Oracle提供了RAC架構(gòu)

數(shù)據(jù)庫鏡像與數(shù)據(jù)庫容災(zāi)

• 引入數(shù)據(jù)庫鏡像原因
  • 前面幾種恢復技術(shù)都必須及時正確地轉(zhuǎn)儲數(shù)據(jù)庫。
• 數(shù)據(jù)庫鏡像簡介
  • 是一種用于提高數(shù)據(jù)庫可用性的解決方案，它根據(jù)DBA的要求串前，自動把整個數(shù)據(jù)庫或關(guān)鍵數(shù)據(jù)復制到另一個磁盤上坛吁。

數(shù)據(jù)庫鏡像分類

• 雙機互備援模式
  • 兩臺機器均為工作機玫膀。正常狀況下均為系統(tǒng)提供支持，互相監(jiān)視對方的運行情況。
• 雙機熱備份模式
  • 一臺為工作機，一臺為備份機u。工作機為系統(tǒng)提供支持凉蜂，備份機監(jiān)視工作機的運行情況

工作方式

“數(shù)據(jù)庫鏡像會話”中纫雁，主體服務(wù)器和鏡像服務(wù)器作為“伙伴”進行通信和協(xié)作忌愚。在會話中扮演互補角色。一旦出現(xiàn)故障，使用“角色切換”過程來互換主體服務(wù)器和鏡像服務(wù)器

SQL Server數(shù)據(jù)庫鏡像簡介

SQL Server數(shù)據(jù)庫鏡像是將數(shù)據(jù)庫事務(wù)處理從一個SQL Server數(shù)據(jù)庫移到不同的SQL Server數(shù)據(jù)庫

• 鏡像的復制是一個備用的復制，不能直接訪問，只用來進行錯誤恢復。“見證服務(wù)器”使鏡像服務(wù)器自動識別。

  兩種運行模式

  • 高安全性模式
  • 運行模式

  三種實現(xiàn)方式

  • 高可用性
    • 兩臺服務(wù)器同步事務(wù)寫入，支持自動錯誤恢復。
  • 高保護性
    • 兩臺服務(wù)器同步事務(wù)寫入，手工錯誤恢復。
  • 高性能
    • 兩臺服務(wù)器寫入不同步驳糯，手工錯誤恢復

備份與恢復數(shù)據(jù)庫

備份與恢復的概念

備份數(shù)據(jù)庫

• 防止數(shù)據(jù)丟失
• 數(shù)據(jù)轉(zhuǎn)移的一種方式

恢復數(shù)據(jù)庫

• 介質(zhì)故障恢復
• 非介質(zhì)故障恢復

SQL Server的備份與恢復機制

恢復模式

• 簡單恢復模式
  • 此模式簡略地記錄大多數(shù)事務(wù)帘睦，不備份事務(wù)日志古胆。所記錄的信息只是為了確保在系統(tǒng)崩潰或還原數(shù)據(jù)備份之后數(shù)據(jù)庫的一致性
• 完整恢復模式
  • 此模式完整地記錄了所有的事務(wù)棺牧，并保留所有的事務(wù)日志記錄疲牵，直到將它們備份
• 大容量日志恢復模式
  • 此模式簡略地記錄大多數(shù)大容量操作（例如索引創(chuàng)建和大容量加載）妆够，完整地記錄其他事務(wù)鸵荠。大容量日志恢復模式提高了大容量操作的性能，常用作完整恢復模式的補充

備份內(nèi)容

• 用戶數(shù)據(jù)庫
• 系統(tǒng)數(shù)據(jù)庫

備份時間

• 系統(tǒng)數(shù)據(jù)庫：修改之后進行備份
• 用戶數(shù)據(jù)庫：周期性備份

觸發(fā)備份的操作

• 創(chuàng)建數(shù)據(jù)庫之后届慈，或批量加載數(shù)據(jù)之后。
• 創(chuàng)建索引之后。
• 執(zhí)行清理事務(wù)日志的操作之后。
• 執(zhí)行大容量數(shù)據(jù)操作之后。

SQL Server的備份機制

備份設(shè)備

• 永久備份設(shè)備：在備份之前需要先建立。

• 臨時備份設(shè)備：不需要預先建立，在備份時將數(shù)據(jù)庫直接備份到物理文件上

• /*使用系統(tǒng)存儲過程創(chuàng)建備份設(shè)備
  sp_addumpdevice 
  　　[ @devtype = ] 'device_type' ,
  　　[ @logicalname = ] 'logical_name' ,
  　　[ @physicalname = ] 'physical_name‘
  其中，device_type表示設(shè)備類型，其值可為disk和tape。logical_name表示設(shè)備的邏輯名稱。physical_name表示設(shè)備的實際名稱*/
  /*創(chuàng)建一個名為 MYDISKDUMP 的磁盤備份設(shè)備，其物理名稱為 D:\Dump1.bak */
  　　EXEC sp_addumpdevice 'disk', 'mydiskdump', 'D:\dump1.ba
  

備份類型

• 數(shù)據(jù)庫備份

  • 完整數(shù)據(jù)庫備份
    • 完整備份將備份整個數(shù)據(jù)庫，包括事務(wù)日志部分（以便可以恢復整個備份）。完整備份代表備份完成時的數(shù)據(jù)庫。
    • 是恢復的基線
    • 備份數(shù)據(jù)文件、數(shù)據(jù)庫對象和數(shù)據(jù)的信息
    • 備份備份過程中發(fā)生的活動
    • 備份未提交的事務(wù)到日志
  • 差異數(shù)據(jù)庫備份
    • 備份從上次完整備份之后數(shù)據(jù)的改變
    • 備份差異備份過程中發(fā)生的活動
    • 備份未提交的事務(wù)到日志
    • 比完整備份節(jié)約時間
    • 以前一次完整備份為基準點（成為差異基準）娶吞。
    • 在還原差異備份前绣夺，應(yīng)先還原最新的完整備份物臂，再還原基于該完整備份的最新差異備份

• 文件備份

  • 文件備份
    • 可以分別備份和還原數(shù)據(jù)庫中的文件仪媒。使用文件備份使用戶可以僅還原已損壞的文件，而不必還原數(shù)據(jù)庫的其余部分，從而提高恢復速度
  • 差異文件備份
    • 創(chuàng)建文件或文件組的完整備份后窍霞，可以基于該完整備份創(chuàng)建一系列的差異備份冷溃。
    • 差異文件備份只捕獲自上一次文件備份以來更改的數(shù)據(jù)

• 事務(wù)日志備份

  • 備份從上次日志備份之后的日志記錄
    備份完成后要截斷日志
  • 事務(wù)日志備份僅用于完整恢復模式或大容量日志恢復模式
  • 類型
    • 純?nèi)罩緜浞?/li>
    • 大容量操作日志備份
    • 結(jié)尾日志備份

• 常用備份策略

  • 完整備份
    • 適合于數(shù)據(jù)庫數(shù)據(jù)不是很大菠净，而且數(shù)據(jù)更改不是很頻繁的情況攀唯。
    • 完整備份一般可以幾天進行一次或幾周進行一次
    • 當對數(shù)據(jù)庫數(shù)據(jù)的修改次數(shù)不是很頻繁戒幔，而且允許一定量的數(shù)據(jù)丟失時，可以選擇只用完整備份策略
    • 完整備份包括了對數(shù)據(jù)和日志的備份。
  • 完整備份 + 日志備份
    • 如果不允許丟失太多的數(shù)據(jù)，而且又不希望經(jīng)常地進行完全備份（因為完全備份進行的時間比較長），則可以在完全備份中間加一些日志備份。
  • 完整備份 + 差異備份 + 日志備份
    • 完全備份加差異備份和日志備份的策略割去，即在完全備份中間加一些差異備份咖城，在差異備份中間加一些日志備份辐董。

• 實現(xiàn)備份

  • 使用SQL Server管理平臺
  • 使用T-SQL語句

SQL Server的恢復機制

• 支持類型

  • 數(shù)據(jù)庫（數(shù)據(jù)庫完整還原）
  • 數(shù)據(jù)文件（文件還原）

• 還原的順序

  • 恢復最近的完全備份届氢；
  • 恢復最近的差異備份（如有）絮供；
  • 恢復自差異備份之后的所有日志備份（按備份的先后順序）贮乳。
  • 恢復數(shù)據(jù)庫浓恳。

• 實現(xiàn)還原

  • 使用SQL Server管理平臺
  • 使用T-SQL語句

• /*假設(shè)已對pubs數(shù)據(jù)庫進行了完全備份晴圾，并備份到MyBK_1備份設(shè)備上知允，假設(shè)此備份設(shè)備只含有對pubs數(shù)據(jù)庫的完全備份涤垫。則恢復pubs數(shù)據(jù)庫的語句為：*/
  RESTORE DATABASE pubs 
  FROM MyBK_1 
  RESTORE DATABASE Students 
    FROM MyBK_2 
     WITH FILE=1, NORECOVERY
  RESTORE DATABASE Students 
     FROM MyBK_2 
     WITH FILE=2, NORECOVERY 
  RESTORE LOG Students 
     FROM MyBKLog1 
  
  

Oracle的備份與恢復機制

故障類型

• 內(nèi)部恢復機制自動處理
  • SQL語句錯誤
  • 進程失效
  • 實例失效
  • 網(wǎng)絡(luò)失效
• 利用備份數(shù)據(jù)恢復
  • 用戶錯誤
  • 介質(zhì)失效

Oracle數(shù)據(jù)庫物理備份與恢復

• 物理備份
  • 用戶管理的備份
  • Recover Manager(RMAN)備份
  • 第三方備份軟件
• 邏輯備份
  • EXP/IMP
  • EXPDP/IMPDP

恢復數(shù)據(jù)庫

• Restore:
  • 從備份數(shù)據(jù)中找到完整備份文件柄粹，恢復完整文件的過程堪夭。
• Recover:
  • 將日志和增量備份的改變應(yīng)用到數(shù)據(jù)文件爬迟，使數(shù)據(jù)文件恢復到指定時間點上的過程

大規(guī)模數(shù)據(jù)庫架構(gòu)

分布式數(shù)據(jù)庫

概述

• 分布式數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)分布存儲于若干場地，并且每個場地由獨立于其它場地的DBMS進行數(shù)據(jù)管理
• 物理上分散、邏輯上集中的數(shù)據(jù)庫系統(tǒng)
• 由<u>局部數(shù)據(jù)庫管理系統(tǒng) 、全局數(shù)據(jù)庫管理系統(tǒng)GDBMS、全局數(shù)據(jù)字典立镶、通信管理四</u>部分組成

分布式數(shù)據(jù)庫目標

• 本地自治栈顷、非集中式管理蛙卤、高可用性行嗤。（<u>最基本特征</u>）
• 位置獨立性栈雳、數(shù)據(jù)分片獨立性蛀骇、數(shù)據(jù)復制獨立性。（<u>分布透明性</u>）
• 分布式查詢、事務(wù)管理。（<u>復雜性</u>）
• 硬件獨立性、操作系統(tǒng)獨立性、網(wǎng)絡(luò)獨立性、數(shù)據(jù)庫管理系統(tǒng)獨立性

數(shù)據(jù)分布策略

• 數(shù)據(jù)分片（對關(guān)系操作）
  按一定規(guī)則將某一個全局關(guān)系劃分為多個片斷

  • 水平分片——每個分片是原始關(guān)系所有數(shù)據(jù)行的子集合千埃。
  • 垂直分片——每個分片是原始關(guān)系所有數(shù)據(jù)列的子集合蜈缤。
  • 導出分片——導出水平分片叠艳。
  • 混合分片——以上三種的混合

• 數(shù)據(jù)分配（對分片結(jié)果操作）

  將分片產(chǎn)生的片斷分配存儲在各個場地上。解決數(shù)據(jù)分配的方法

  • 集中式——所有數(shù)據(jù)片斷安排在一個場地上。
  • 分割式——所有全局數(shù)據(jù)有且只有一份劝堪，分割成若干被分配在特定場地上的片斷。
  • 全復制式——全局數(shù)據(jù)有多個副本，每個場地上有一個完整的數(shù)據(jù)副本答渔。
  • 混合式—— 介于分割與全復制式 之間

分布式數(shù)據(jù)庫系統(tǒng)的體系結(jié)構(gòu)

1553261177038.png

分布透明性

• 分片透明性
  • 用戶無需考慮數(shù)據(jù)分片。
  • <u>位于全局概念模式與分片模式之間</u>，當分片模式改變時诗良，只需改變?nèi)指拍钅Ｊ降椒制Ｊ街g的映像径荔，而不會影響到全局概念模式和應(yīng)用程序
• 位置透明性
  • 用戶只需考慮數(shù)據(jù)分片情況胧谈，無需考慮數(shù)據(jù)分片位置蔑滓。
  • 位于分片模式與分配模式之間褐健，當存儲場地發(fā)生變化時，只需改變分片模式到分配模式之間的映像
• 局部映像透明性
  • 用戶既要了解全局數(shù)據(jù)的分片情況，還要了解各片斷的副本復制情況及位置分配情況。

1553261294799.png

分布式數(shù)據(jù)庫的相關(guān)技術(shù)

• 分布式查詢
• 分布式事務(wù)管理
  • 恢復控制:
    　　基于兩階段的提交協(xié)議砍濒。
  • 并發(fā)控制:
    　　基于封鎖協(xié)議。

并行數(shù)據(jù)庫

概述

通過并行實現(xiàn)各種數(shù)據(jù)操作，如數(shù)據(jù)載入、索引建立测蹲、數(shù)據(jù)查詢等启泣，可以提高系統(tǒng)的性能顽耳。

優(yōu)勢

增強的可用性：當存儲某個關(guān)系的場地系統(tǒng)崩潰時，可繼續(xù)使用存儲在別的場地的副本。

并行數(shù)據(jù)庫系統(tǒng)結(jié)構(gòu)

• 共享內(nèi)存系統(tǒng)(Shared Memory)

  • 多個cpu通過連接網(wǎng)絡(luò)進行通信旱幼，并能訪問公共的主存敌蚜。 隨著CPU增加，造成內(nèi)存沖突
  • 1553261609153.png

• 共享磁盤系統(tǒng)(Shared Disk)

  • 共享磁盤系統(tǒng)：每個cpu擁有自己的私有內(nèi)存叮阅，并通過連接網(wǎng)絡(luò)直接訪問所有磁盤。通過網(wǎng)絡(luò)實現(xiàn)CPU之間的數(shù)據(jù)交換，增加了通信代價
    

    *
    1553261696875.png

• 無共享資源系統(tǒng)(Shared Nothing)

  • 每個cpu擁有自己的內(nèi)存和磁盤空間飘诗，并無公共區(qū)域比驻，cpu之間所有通信通過連接網(wǎng)絡(luò)來完成蹭秋。存在通信代價盐固，非本地磁盤訪問代價高
  • 1553261771008.png

• 層次結(jié)構(gòu)(Hierachical)

  • 前三種體系的結(jié)合。分為兩層茉继，頂層是無共享結(jié)構(gòu)睬魂，底層是共享內(nèi)存或共享磁盤結(jié)構(gòu)良姆。集成了以上三種結(jié)構(gòu)的優(yōu)缺點
  • 1553261831097.png

數(shù)據(jù)劃分與并行算法

一維數(shù)據(jù)劃分：將大數(shù)據(jù)集水平劃分到多個磁盤上垒玲，可以通過并行讀寫有效地利用多磁盤的I/O帶寬

• 輪轉(zhuǎn)法——如果系統(tǒng)有n個cpu妇汗，將第i條記錄劃分到第i mod n 處理器的方法稱為輪轉(zhuǎn)劃分方法。
• 散列法——使用特定的哈希函數(shù)刮萌，作用于選定的屬性，將記錄劃分到不同的處理機。
• 范圍劃分法——首先對記錄進行排序，然后按照排序碼將其劃分成n個區(qū)域怕篷，使每個區(qū)域中近似含有相同數(shù)目的記錄，處于第i個區(qū)域的記錄分布于處理機i

算法優(yōu)勢劣勢

• 輪轉(zhuǎn)法可有效應(yīng)用于需要訪問整個關(guān)系的查詢處理榛鼎，當需要訪問部分記錄時，散列法和范圍更優(yōu)吐葱。
• 范圍法可能會導致數(shù)據(jù)偏斜，也就是不同分片含有的記錄數(shù)目差別很大貌虾。數(shù)據(jù)偏斜會造成存有大片數(shù)據(jù)分片的處理機的性能瓶頸問題。
• 散列法優(yōu)點是：即使數(shù)據(jù)隨時間增加或減少斥黑，也能保持均勻分布。

多維數(shù)據(jù)劃分

• CMD多維劃分法
• BERD多維劃分法
• MAGIC多維劃分法琐簇。

并行算法

• 并行排序
  • 用區(qū)域劃分法先將關(guān)系的所有記錄重新分布再進行排序。
  • 每個cpu使用排序算法對分配給它的記錄排序箫攀。每個處理機得到分配給它的所有記錄的有序序列绝葡。
  • 通過按照區(qū)域劃分的對應(yīng)次序訪問處理機得到完整的有序關(guān)系航瞭。
• 并行連接
  • 假設(shè)：對關(guān)系A(chǔ)和B進行劃分時，連接屬性為age辜羊，關(guān)系初始分布在若干磁盤上，但不是基于連接屬性分布的萌衬。
  • 方法：對關(guān)系A(chǔ)和B重新劃分：把連接屬性age的取值分成k個區(qū)域

云計算數(shù)據(jù)庫架構(gòu)

云計算

云計算是一種商業(yè)計算模型歌径。它將計算任務(wù)分布在大量計算機構(gòu)成的資源池上，使各種應(yīng)用系統(tǒng)能夠根據(jù)需要獲取計算力您没、存儲空間和信息服務(wù)

1553262188822.png

云計算服務(wù)類型

1553262229664.png

云

云計算提供商的數(shù)據(jù)中心的軟硬件設(shè)施

• 公共云：以即用即付的方式提供給公眾还栓。
• 私有云：不對公眾開放的云。
• 混合云

云數(shù)據(jù)庫體系結(jié)構(gòu)

• AWS（Amazon Web Services）
• GAE（Google AppEngine）
• Hadoop

云計算技術(shù)體系結(jié)構(gòu)

[圖片上傳失敗...(image-7182f3-1558763441920)]

谷歌云 計算基礎(chǔ)架構(gòu)模式

• Google File System文件系統(tǒng)
• Map/Reduce分布式編程環(huán)境
• Chubby分布式鎖機制
• BigTable大規(guī)模分布式數(shù)據(jù)庫
  • 表中的關(guān)鍵字可以是任意的字符串
  • 列族是由列關(guān)鍵字組成的集合烤蜕，是訪問控制的基本單位
  • 時間戳記錄了BigTable是每一個數(shù)據(jù)項所包含的不同版本的數(shù)據(jù)的時間標識堪藐。在BigTable中，不僅可以隨意地增減行的數(shù)量寓搬，在一定的約束條件下唾琼，還可以對列的數(shù)量進行擴展。

云數(shù)據(jù)庫與傳統(tǒng)數(shù)據(jù)庫比較

• 數(shù)據(jù)安全問題
• 對云的管理問題
• 對因特網(wǎng)的依賴

XML數(shù)據(jù)庫

概述

XML,可擴展標識語
XML數(shù)據(jù)庫——支持對XML文檔格式進行存儲和查詢等操作的數(shù)據(jù)庫管理系統(tǒng)蒸辆。

類型

• XML Enabled Database(XEDB)——能處理XML的數(shù)據(jù)庫。
• Native XML Database(NXD)——純XML數(shù)據(jù)庫缀拭。
• Hybird XML Database(HXD)——混合XML數(shù)據(jù)庫

與傳統(tǒng)數(shù)據(jù)庫比較，XML數(shù)據(jù)庫的優(yōu)勢

• 能夠?qū)Π虢Y(jié)構(gòu)化數(shù)據(jù)進行處理棠赛。
• 提供對標簽和路徑的操作怔揩。
• 能清晰地表達數(shù)據(jù)的層次特征实幕。

SQL Server 2008 與 XM

• SQL Server中的XML語句

  • SELECT  * FROM  Table_cus 
    WHERE  …. FOR XML RAW
    

• SQL Server中的XML數(shù)據(jù)類型

  • CREATE  TABLE T_info
    (
    cid int ,
    Content xml
    ) 
    

數(shù)據(jù)倉庫與數(shù)據(jù)挖掘

決策支持系統(tǒng)的發(fā)展

決策支持系統(tǒng)

• 一般將數(shù)據(jù)分為：
  • 分析型數(shù)據(jù)
  • 操作型數(shù)據(jù)
    • 由企業(yè)的基本業(yè)務(wù)系統(tǒng)產(chǎn)生的數(shù)據(jù)裕膀，用于聯(lián)機處理環(huán)境（OLTP）
• 決策支持系統(tǒng)（DSS）：綜合利用大量數(shù)據(jù)蛹含，有機組合眾多模型（數(shù)學模型和數(shù)據(jù)處理模型）妆棒，通過人機交互纠脾，輔助各級決策者實現(xiàn)科學決策的系統(tǒng)。

基于數(shù)據(jù)倉庫的決策支持系統(tǒng)

數(shù)據(jù)倉庫是核心，操作型系統(tǒng)是數(shù)據(jù)源畔师，決策支持系統(tǒng)是數(shù)據(jù)的需求者

[圖片上傳失敗...(image-6b8064-1558763441920)]

數(shù)據(jù)倉庫技術(shù)概述

數(shù)據(jù)倉庫的概念與特性

建立數(shù)據(jù)倉庫的目的

• 根據(jù)決策需求對企業(yè)的數(shù)據(jù)采取適當?shù)氖侄芜M行集成趾诗，形成一個綜合的览效、面向分析的數(shù)據(jù)環(huán)境，用于支持企業(yè)的信息型、決策型的分析應(yīng)用冻晤。

數(shù)據(jù)倉庫的特性

• 面向主題性
  • 數(shù)據(jù)倉庫中的數(shù)據(jù)是以面向主題的方式進行組織的歼捏。
  • 主題也稱為分析主題或分析領(lǐng)域，用于表示某一宏觀的分析領(lǐng)域所涉及的對象泉粉，以及與對象有關(guān)的數(shù)據(jù)集合躏嚎。
  • 目前婆殿，數(shù)據(jù)倉庫主題數(shù)據(jù)的實現(xiàn)采用關(guān)系型數(shù)據(jù)庫技術(shù)
• 集成性
• 不可更新性
• 時間特性等

數(shù)據(jù)倉庫的體系結(jié)構(gòu)與環(huán)境

數(shù)據(jù)層次角度

• 操作型數(shù)據(jù)
• 操作型數(shù)據(jù)存儲（Operation Data Store）
  • ODS是能支持企業(yè)日常的全局應(yīng)用的數(shù)據(jù)集合,是不同于DB的一種新的數(shù)據(jù)環(huán)境, 是DW 擴展后得到的一個混合形式被啼。
  • 四個基本特點：面向主題的( Subject –Oriented )、集成的、可變的、 當前或接近當前的
  • 可實行的全局應(yīng)用
    • 實現(xiàn)企業(yè)全局的OLTP操作
      • ETL是英文 Extract-Transform-Load 的縮寫拙已，用來描述將數(shù)據(jù)從來源端經(jīng)過抽取康谆、轉(zhuǎn)換、加載至目的端的過程。采用數(shù)據(jù)抽取程序從OLTP系統(tǒng)中抽取數(shù)據(jù)的主要原因是解決不同類型應(yīng)用程序之間的性能沖突問題
    • 實現(xiàn)即時的OLAP操作
  • ODS分成4類
    • ODSⅠ：數(shù)據(jù)更新頻率秒級题涨。
    • ODSⅡ：數(shù)據(jù)更新頻率小時級。
    • ODSⅢ：數(shù)據(jù)更新頻率天級狂塘。
    • ODSⅣ：根據(jù)數(shù)據(jù)來源方向和類型區(qū)分
• 數(shù)據(jù)倉庫（Data Warehouse）
• 數(shù)據(jù)集市（Data Mart）也可以包括個體層數(shù)據(jù)
  • 小型的纵刘，面向部門或工作組級數(shù)據(jù)倉庫

功能結(jié)構(gòu)

• 數(shù)據(jù)處理
• 數(shù)據(jù)管理
• 數(shù)據(jù)應(yīng)用

數(shù)據(jù)倉庫的數(shù)據(jù)組織–-粒度

• 數(shù)據(jù)倉庫的數(shù)據(jù)單位中保存數(shù)據(jù)的細化或綜合程度的級別迅腔。細化程度越高，粒度越小
• 粒度影響到數(shù)據(jù)倉庫的數(shù)據(jù)量及系統(tǒng)能回答的查詢的類型嫁蛇。
  進
• 行數(shù)據(jù)倉庫的數(shù)據(jù)組織時暑中，應(yīng)根據(jù)當前應(yīng)用的需求進行多粒度級設(shè)計俄精。滿足多角度帚稠、多層次數(shù)據(jù)查詢要求

數(shù)據(jù)分區(qū)（分割）

數(shù)據(jù)分散到各自的物理單元中去，它們能獨立地處理

• 最常見的是按照時間標準分區(qū)。
• 數(shù)據(jù)分區(qū)的方式可以分成系統(tǒng)層分區(qū)（數(shù)據(jù)庫系統(tǒng)提供的機制）和應(yīng)用層分區(qū)（由應(yīng)用代碼實現(xiàn)）兩種琼腔。
• 如何分區(qū)由開發(fā)者和程序員控制

元數(shù)據(jù)

• 元數(shù)據(jù)描述了數(shù)據(jù)的結(jié)構(gòu)、內(nèi)容赫蛇、鏈和索引等內(nèi)容荡陷。
• 在數(shù)據(jù)倉庫中托享，元數(shù)據(jù)分成技術(shù)型元數(shù)據(jù)和業(yè)務(wù)型元數(shù)據(jù)。
• 元數(shù)據(jù)解決了人在何時何地為了什么原因及如何使用數(shù)據(jù)倉庫的問題侧戴。

設(shè)計與建造數(shù)據(jù)倉庫

數(shù)據(jù)倉庫設(shè)計的需求與方法

• SDLC是典型的需求驅(qū)動開發(fā)生命周期侣背，CLDS是典型的數(shù)據(jù)驅(qū)動開發(fā)生命周期蛇券。

  • 傳統(tǒng)的系統(tǒng)開發(fā)生命周期支持操作型環(huán)境摊鸡，為建立系統(tǒng)经宏，必須首先理解需求冀续，然后進入到設(shè)計和開發(fā)階段。

• CLDS由數(shù)據(jù)開始，一旦數(shù)據(jù)到手式散，就集成數(shù)據(jù)竖席，然后丧失，如果數(shù)據(jù)有偏差甩鳄，就檢驗看看數(shù)據(jù)存在什么偏差疏尿，再針對數(shù)據(jù)寫程序，分析程序執(zhí)行結(jié)果，最后，系統(tǒng)需求才得到理解

• 1553264662052.png
• 1553264682983.png

數(shù)據(jù)倉庫的數(shù)據(jù)模型

概念（E-R圖）铜秆、邏輯（關(guān)系型模型或多維數(shù)據(jù)模型）饲趋、物理三級數(shù)據(jù)模型拐揭。

數(shù)據(jù)倉庫設(shè)計步驟

概念模型設(shè)計、技術(shù)評估與環(huán)境準備工作奕塑、邏輯模型設(shè)計堂污、物理模型設(shè)計、數(shù)據(jù)生成與應(yīng)用實現(xiàn)龄砰、數(shù)據(jù)倉庫運行與維護

數(shù)據(jù)倉庫的運行與維護

數(shù)據(jù)倉庫數(shù)據(jù)的更新與維護

• 基本思路：
  • 根據(jù)某種維護策略盟猖，在一定條件下觸發(fā)維護操作讨衣；維護操作捕捉到數(shù)據(jù)源中的數(shù)據(jù)變化；通過一定策略對數(shù)據(jù)倉庫中的數(shù)據(jù)進行相應(yīng)的更新操作扒披，以保持兩者的一致性
• 維護策略
  • 實時維護
    • 在數(shù)據(jù)源發(fā)生變化時值依，立即更新。
  • 延時維護
    • 在數(shù)據(jù)倉庫中的視圖被查詢時完成更新碟案。
  • 快照維護
    • 定期對數(shù)據(jù)倉庫進行維護愿险，觸發(fā)條件是時間
• 捕捉數(shù)據(jù)源的變化
  • 觸發(fā)器
  • 修改數(shù)據(jù)源應(yīng)用程序
  • 通過日志文件
  • 快照比較法
• 導出數(shù)據(jù)的刷新
  • 根據(jù)維護對象的數(shù)據(jù)源對其進行重新計算。
  • 根據(jù)數(shù)據(jù)源的變化量在維護對象原有數(shù)據(jù)的基礎(chǔ)上進行添加和修改价说，即增量式維護

數(shù)據(jù)倉庫監(jiān)控

對數(shù)據(jù)倉庫的數(shù)據(jù)量辆亏、各種數(shù)據(jù)的使用率、用戶和安全狀況鳖目、網(wǎng)絡(luò)通信情況扮叨、網(wǎng)絡(luò)數(shù)據(jù)流量、數(shù)據(jù)源數(shù)據(jù)變化情況领迈、集成和維護工具運行效率彻磁、查詢響應(yīng)時間、應(yīng)用支持效率等進行必要的監(jiān)控

元數(shù)據(jù)管理

一般采用元數(shù)據(jù)庫來存儲和管理元數(shù)據(jù)

聯(lián)機分析處理與多維數(shù)據(jù)模型（OLAP）

OLAP簡介

• 主要用于支持復雜的分析操作狸捅，側(cè)重對決策人員和高層管理人員的決策支持衷蜓。
• 特點
  • 快速、可分析尘喝、多維

多維分析的基本概念

維——人們觀察數(shù)據(jù)的角度磁浇。
數(shù)據(jù)的多維分析——針對數(shù)據(jù)倉庫中以多維形式組織起來的數(shù)據(jù)，從多個角度朽褪、不同層次置吓，采取各種數(shù)據(jù)分析技術(shù)，對數(shù)據(jù)進行剖析缔赠，以使用戶能從多角度和不同層次觀察和分析數(shù)據(jù)衍锚。

多維分析的基本操作

• 鉆取與卷起
  • OLAP分析最基本的操作。鉆取指對應(yīng)于某一維逐步向更細節(jié)層方向觀察數(shù)據(jù)嗤堰，卷起則反之戴质。
• 切片和切塊
  • 實現(xiàn)局部數(shù)據(jù)的顯示。
• 旋轉(zhuǎn)
  • 改變一個報告或頁面顯示的維方向梁棠，得到不同視角的數(shù)據(jù)置森。最簡單的旋轉(zhuǎn)就是數(shù)據(jù)交叉

OLAP的實現(xiàn)方式

• 基于多維數(shù)據(jù)庫的OLAP（MOLAP）
  • 以多維數(shù)組為基本存儲結(jié)構(gòu)斗埂。
• 基于關(guān)系數(shù)據(jù)庫的OLAP（ROLAP）
  • 采用關(guān)系表表示和存儲符糊。（星形模式或雪花模式）
• 混合型的OLAP（HOLAP）
  • 結(jié)合MOLAP與ROLAP。具有最好的查詢性能

數(shù)據(jù)挖掘技術(shù)

數(shù)據(jù)挖掘步驟

從人工智能機器學習中發(fā)展起來的呛凶。它研究各種方法和技術(shù)男娄，從大量的數(shù)據(jù)中挖掘出有用的信息和知識。

1. 數(shù)據(jù)準備

• 數(shù)據(jù)選取
• 數(shù)據(jù)預處理
• 數(shù)據(jù)變化。

2. 數(shù)據(jù)挖掘

• 首先確定挖掘的任務(wù)模闲，其次決定挖掘算法建瘫。

3. 結(jié)果解釋評估
   剔除冗余或無關(guān)的模式

常見的數(shù)據(jù)挖掘任務(wù)

• 分類預測任務(wù)
  • 常見的方法：決策樹、神經(jīng)網(wǎng)絡(luò)尸折、規(guī)則歸納啰脚、支持向量機、貝葉斯实夹、粗糙集橄浓、回歸分析、K-最近鄰等亮航。
• 描述型任務(wù)
  • 典型的方法：聚類荸实、關(guān)聯(lián)、分析等

關(guān)聯(lián)規(guī)則挖掘--“啤酒和尿布”

• 第一階段必須先從資料集合中找出所有的高頻項目組(Frequent Itemsets)缴淋，
• 第二階段再由這些高頻項目組中產(chǎn)生關(guān)聯(lián)規(guī)則(Association Rules)准给。

分類挖掘

• 通過已知數(shù)據(jù)集，建立分類函數(shù)重抖，構(gòu)造分類器露氮。
• 利用所獲得的分類函數(shù)對未知類別標記的數(shù)據(jù)項進行分類操作

聚類挖掘

聚類挖掘方法——用于對集中的數(shù)據(jù)進行分組，使得每組內(nèi)的數(shù)據(jù)盡量相似而不同組間的數(shù)據(jù)盡可能不同仇哆。

• 聚類方法
  • 統(tǒng)計方法
  • 機器學習方法
    • 通常沦辙，在訓練有監(jiān)督的機器學習模型的時候，會將數(shù)據(jù)劃分為<u>訓練集讹剔、測試集和驗證集合</u>
  • 神經(jīng)網(wǎng)絡(luò)方法和面向數(shù)據(jù)庫的方法等

時間序列分析

• 時間序列——用時間排序的一組隨機變量油讯。
• 時間序列分析——又稱為數(shù)據(jù)演變分析
  • 描述行為隨時間變化的對象的規(guī)律或趨勢，并對其進行建模延欠。
• 從不同角度可分為
  • 一元時間序列和多元時間序列
  • 等間隔時間序列和不等間隔時間序列
  • 平穩(wěn)時間序列和非平穩(wěn)時間序列