3、DFD需求建模方法——核心是數(shù)據(jù)流。

1）DFD的常用圖形符號

2）數(shù)據(jù)流（數(shù)據(jù)流動的方向）——數(shù)據(jù)在系統(tǒng)內(nèi)傳播的路徑，因此由一組成分固定的數(shù)據(jù)組成；就是流動中的數(shù)據(jù)，由名詞或者名詞短語來命名。

3）數(shù)據(jù)源和數(shù)據(jù)加工（處理）

4）注意：DFD圖（數(shù)據(jù)流程圖）主要是對功能進(jìn)行建模，就是業(yè)務(wù)流程

5）DFD圖（數(shù)據(jù)流程圖）的具體建模的過程和步驟：明確目標(biāo)和確定系統(tǒng)邊界（將用戶對目標(biāo)系統(tǒng)的功能需求完整、準(zhǔn)確、一致的描述出來 ）—>建立頂層DFD圖 （頂層DFD只有一張），頂層DFD是對系統(tǒng)的一個大概的概括，沒有具體的細(xì)節(jié)，說明系統(tǒng)的邊界（系統(tǒng)的輸入和輸出）——>構(gòu)建第一層DFD分解圖（中間層DFD ）——>開發(fā)DFD層次結(jié)構(gòu)圖（底層DFD原則：保持均勻的深度模型，按困難程度進(jìn)行選擇），由不可再分的過程組成。

6）確認(rèn)DFD的五條規(guī)則：

4、常用的IDEF0~IDEF4:

①IDEF0：描述系統(tǒng)功能及其相互關(guān)系

②IDEF1：系統(tǒng)信息及其數(shù)據(jù)的關(guān)系

③IDEF2：系統(tǒng)模擬，動態(tài)建模

④IDEF3：過程描述及獲取方法

⑤IDEF4：面向?qū)ο笤O(shè)計

5、UML用例模型

★例題：

1）教材購銷系統(tǒng)：

解答：頂層DFD

中間層和底層DFD

2）

3）

4）

5）

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

第一節(jié) 數(shù)據(jù)庫概念設(shè)計

數(shù)據(jù)庫概念設(shè)計是數(shù)據(jù)庫設(shè)計的核心環(huán)節(jié)。通過對用戶的需求進(jìn)行綜合、歸納與抽象，形成一個獨(dú)立于DBMS的概念模型。

一、數(shù)據(jù)庫概念設(shè)計的目標(biāo)

二、概念設(shè)計的依據(jù)與過程

一）依據(jù)

? 數(shù)據(jù)庫概念設(shè)計以需求分析的結(jié)果為依據(jù)，即需求說明書、DFD圖以及在需求階段收集到的應(yīng)用領(lǐng)域中的各類報表。

二）結(jié)果

? 概念設(shè)計的結(jié)果是概念模型（E-R圖）以及概念設(shè)計說明書。

三）過程

? ①明確建模目標(biāo)（模型覆蓋范圍）；②定義實(shí)體集（自底向上識別和定義實(shí)體）；③定義聯(lián)系（實(shí)體間的關(guān)聯(lián)關(guān)系）；④建立信息模型（構(gòu)建ER模型）；⑤確定實(shí)體的屬性（屬性描述實(shí)體的特征和性質(zhì)）；⑥對信息模型進(jìn)行集成與優(yōu)化（檢查和消除命名不一致、結(jié)構(gòu)不一致）

? 概念模型是對現(xiàn)實(shí)世界的抽象和模擬。

四）概念模型設(shè)計

? 概念設(shè)計目前采用的最廣泛的是E-R建模方法。將現(xiàn)實(shí)世界抽象為具有屬性的實(shí)體及聯(lián)系。

五）與E-R建模有關(guān)的的概念

? 1）實(shí)體：客觀存在并可相互區(qū)分的事物叫實(shí)體；實(shí)體集：同型實(shí)體的集合稱之為實(shí)體集；屬性：實(shí)體具有的某一特性，一個實(shí)體具有若干個屬性來刻畫，每個屬性的取值范圍稱為域（用來描述實(shí)體的性質(zhì)和特征）；碼或鍵：實(shí)體集中唯一標(biāo)識每個實(shí)體的屬性或?qū)傩越M合；主鍵或主碼：用來區(qū)別同一個實(shí)體集不同實(shí)體的鍵（主碼的值不能相同）。

? 2）聯(lián)系：描述實(shí)體間的相互關(guān)系；聯(lián)系集：同類聯(lián)系的集合稱之聯(lián)系集 ；三類聯(lián)系：一對一（1：1）、一對多（1：n）、多對多（m:n)

IDEF1X是數(shù)據(jù)建模的方法

第二節(jié) 數(shù)據(jù)庫邏輯設(shè)計

一）邏輯設(shè)計的任務(wù)

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

二）數(shù)據(jù)模型

? 有三種：層次模型、網(wǎng)狀模型、關(guān)系模型（當(dāng)前最流行的數(shù)據(jù)模型）

三）關(guān)系模型

? 關(guān)系模型用二維表來表示；關(guān)系的描述稱為關(guān)系模式；關(guān)系模式由五部分組成，即五元組：R（U,D,DOM,F）

? E-R圖到關(guān)系模式的轉(zhuǎn)換:

四）關(guān)系數(shù)據(jù)庫數(shù)據(jù)的核心是關(guān)系模式的設(shè)計

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

六）數(shù)據(jù)依賴的類型

? ①函數(shù)依賴（Functional Dependency，FD）

這種依賴關(guān)系類似于數(shù)學(xué)中的函數(shù)y=f（x），自變量x確定之后，相應(yīng)的函數(shù)值y也就唯一地確定了。

如關(guān)系：公民（身份證號，姓名，地址，工作單位）

身份證號一確定，則其地址就唯一確定，因此地址函數(shù)依賴身份證號。而姓名一確定，不一定能確定地址。

? ②多值依賴（Multivalued Dependency，MD）

教師號可能多值依賴課程號，因?yàn)榻o定一個（課程號，參考書號）的組合，可能有對應(yīng)多個教師號。這是因?yàn)槎鄠€老師可以使用相 同或不同的參考書上同一門課。

簡單點(diǎn)講，函數(shù)就是唯一確定的關(guān)系；多值依賴卻不能唯一確定。

七）函數(shù)依賴的幾種特例

1、平凡函數(shù)依賴與非平凡函數(shù)依賴

如果X→Y，且Y ? X，則X→Y 稱為非平凡函數(shù)依賴。

若Y ? X ，則稱X→Y為平凡函數(shù)依賴。

由于Y ? X 時，一定有X→Y，平凡函數(shù)依賴必然成立，沒有意義，所以一般所說的函數(shù)依賴總是指非平凡函數(shù)依賴。

舉例：

例：Sno代表學(xué)生的學(xué)號，Cno代表課程號，Grade代表成績。

在關(guān)系 SC（Sno, Cno, Grade）中，

非平凡函數(shù)依賴：（Sno, Cno）→Grade 即Grade不包含于（Sno, Cno）

平凡函數(shù)依賴：（Sno, Cno）→Sno 即Sno包含于（Sno, Cno）

? (Sno, Cno）→Cno

2、完全函數(shù)依賴與部分函數(shù)依賴

如果X→Y ，且對于任何X’ ? X，都有X’

Y，則稱y完全依賴于x，記作X->Y（箭頭上有個大寫f）。

如果X→Y，但Y不完全依賴于X，則稱Y部分函數(shù)依賴于X，記作X->Y（箭頭上有個大寫P）。

例：選課（學(xué)號，課程號，課程名，成績）

（學(xué)號，課程號） X->Y（箭頭上有個大寫f） 成績 即（學(xué)號，課程號）兩者都為主鍵且共同決定成績，缺一不可

（學(xué)號，課程號）X->Y（箭頭上有個大寫P） 課程名　因?yàn)檎n程號→課程名 即（學(xué)號，課程號）兩者都為主鍵且課程號可以單獨(dú)決定課程名。

推論：如果X→Y ，且X是單個屬性，則X->Y（箭頭上有個大寫f）

3、傳遞函數(shù)依賴

如果X→Y ， Y→Z，且Y 不包含于X， Y　不依賴　X，則稱Z傳遞函數(shù)依賴于X。記作X 傳遞→Z 。

例：學(xué)生（學(xué)號，姓名，系名，系主任）

顯然系主任傳遞函數(shù)依賴于學(xué)號，因?yàn)閷W(xué)號→系名，系名→系主任

思考題：已知關(guān)系模式R（學(xué)生學(xué)號，課程名，學(xué)生專業(yè)號，專業(yè)名，成績），說出下面是什么關(guān)系？

（學(xué)生學(xué)號，課程名，學(xué)生專業(yè)號）　成績 （部分函數(shù)依賴）

學(xué)生學(xué)號 專業(yè)名 （傳遞函數(shù)依賴）

（學(xué)生學(xué)號，專業(yè)名）　成績 （不是依賴關(guān)系）

（學(xué)生學(xué)號，課程名）　成績 （完全函數(shù)依賴）

（課程名，專業(yè)名，成績） （課程名，成績） （平凡函數(shù)依賴）

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

關(guān)系數(shù)據(jù)庫的設(shè)計主要是關(guān)系模式設(shè)計。關(guān)系模式設(shè)計的好壞直接影響到數(shù)據(jù)庫設(shè)計的成敗。將關(guān)系模式規(guī)范化，是設(shè)計較好的關(guān)系模式的惟一途徑。

關(guān)系模式的規(guī)范化主要是由關(guān)系范式來完成的。

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

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

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

九）范式

范式：關(guān)系模式滿足的約束條件稱為范式。根據(jù)滿足規(guī)范化的程度不同，范式由低到高分為1NF，2NF，3NF，BCNF，4NF，5NF。

①1NF：如果關(guān)系模式R，其所有屬性都是不可再分的基本數(shù)據(jù)項(xiàng)，則稱R屬于第一范式，R∈1NF。1NF要求數(shù)據(jù)庫表中的字段都是單一屬性的，不可再分。即元素具有原子性。

②2NF：如關(guān)系模式R∈1NF，且每個非主屬性完全函數(shù)依賴于主碼，則稱R屬于第二范式，R∈2NF。2NF要求實(shí)體的屬性完全依賴于 主關(guān)鍵字。首先關(guān)系要滿足第一范式。

例：判斷R （學(xué)號，姓名，年齡，課程名稱，成績，學(xué)分）是否屬于第二范式。

? 主碼：（學(xué)號，課程名稱）

非主屬性：姓名，年齡，成績，學(xué)分

? 存在如下決定關(guān)系：
　（學(xué)號, 課程名稱）→（姓名，年齡，成績，學(xué)分）
　 但（課程名稱）→（學(xué)分）

（學(xué)號）→（姓名, 年齡）所以R不屬于2NF

? ③第三范式：如果關(guān)系模式為2NF，并且R中的每個非主屬性不傳遞依賴于R的主鍵，則稱關(guān)系R是是屬于第三范式，即R∈2NF。

? 范式之間的關(guān)系：

?

十）數(shù)據(jù)庫邏輯模型的產(chǎn)生（ER圖轉(zhuǎn)換為關(guān)系模式）

? ①一個實(shí)體轉(zhuǎn)換成一個關(guān)系模式；②一個1：1聯(lián)系可以轉(zhuǎn)換為一個獨(dú)立的關(guān)系模式，也可以于任意一端對應(yīng)的關(guān)系模式合并；

? ③一個1：n聯(lián)系可以轉(zhuǎn)化為一個獨(dú)立的關(guān)系模式，也可以于n端對應(yīng)的關(guān)系模式合并；④一個n：m轉(zhuǎn)換為一個關(guān)系模式；⑤三個或三個以上實(shí)體的多元聯(lián)系轉(zhuǎn)換為一個關(guān)系模式；⑥同一實(shí)體集實(shí)體間的聯(lián)系可以按照1：1、1：n、n：m三種情況進(jìn)行處理。

舉例：

? ①1：1聯(lián)系的E-R圖轉(zhuǎn)換為關(guān)系模式較為簡單，不在列舉，總共有情況；

? ②1：n聯(lián)系的E-R圖轉(zhuǎn)換為關(guān)系模式，有兩種方案；

? 方案一：倉庫（倉庫號、地點(diǎn)、面積）、產(chǎn)品（產(chǎn)品號、產(chǎn)品名、價格）、倉儲（倉庫號、產(chǎn)品號、數(shù)量）

? 方案二：倉庫（倉庫號、地點(diǎn)、面積）、產(chǎn)品（產(chǎn)品號、產(chǎn)品名、價格、倉庫號、數(shù)量）

③m：n聯(lián)系的E-R圖轉(zhuǎn)換為關(guān)系模式，有一種方案；

方案一：學(xué)生（學(xué)號、姓名、年齡、性別）、課程（課程號、課程名、學(xué)時數(shù)）、選修（學(xué)號、課程號、成績）

④三個及三個以上實(shí)體：

⑤同實(shí)體集1：n聯(lián)系轉(zhuǎn)換為關(guān)系模式

第三節(jié) 數(shù)據(jù)庫物理設(shè)計

1、物理設(shè)計概述

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

? 目的：將數(shù)據(jù)的邏輯描述轉(zhuǎn)換為技術(shù)規(guī)范，設(shè)計數(shù)據(jù)存儲方案以便提供足夠的好的性能以便確保數(shù)據(jù)庫數(shù)據(jù)的完整性、安全性、可靠性

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

? 一）①物理設(shè)備上的存儲結(jié)構(gòu)與存取方法稱為數(shù)據(jù)庫的物理結(jié)構(gòu)；②數(shù)據(jù)庫中的數(shù)據(jù)一文件形式存儲在外存儲介質(zhì)上。

? 二）數(shù)據(jù)庫的物理結(jié)構(gòu)設(shè)計需要解決的問題：文件組織、文件結(jié)構(gòu)、文件存取、索引技術(shù)

3、索引——數(shù)據(jù)庫持久化設(shè)計（數(shù)據(jù)的存取）

? 1）①索引是數(shù)據(jù)庫中獨(dú)立的存儲結(jié)構(gòu)其作用是提供一種無須掃描每個頁面（存儲表格數(shù)據(jù)的物理塊）而快速訪問數(shù)據(jù)頁的方案。
? ②索引技術(shù)（Indexing）是一種快速數(shù)據(jù)訪問技術(shù)

? ③索引技術(shù)的關(guān)鍵：建立記錄域取值到記錄的物理地址間的映射關(guān)系，即索引。

? ④索引能夠提高性能（減少時間），但是需要付出額外的空間、在維護(hù)時也需要付出多余的時間

? 2）索引技術(shù)的分類

? 有序索引：又分為聚集索引和非聚集索引、稠密索引和稀疏索引；散列索引（哈希表存儲）；主索引；唯一索引、單層索引和多層索引

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

? 目標(biāo)：略；

? 環(huán)節(jié)：（1）數(shù)據(jù)庫邏輯模式描述（關(guān)系模式-基本表）；（2）文件組織與存取設(shè)計（基本原則：①將易變部分與穩(wěn)定部分、存儲頻率較高的部分與存儲頻率較低的部分分開存放，以提高系統(tǒng)性能；②分析理解數(shù)據(jù)庫事務(wù)訪問特性）；（3）數(shù)據(jù)分布設(shè)計（分布式數(shù)據(jù)庫系統(tǒng)的數(shù)據(jù)劃分：垂直劃分和水平劃分（各自的含義））；（4）確定系統(tǒng)配置；（5）物理模式評估（對數(shù)據(jù)庫的**時間和空間（存取時間、存儲空間）**的效率進(jìn)行評估）

3）什么是存取路徑

選擇存取路徑主要是指確定如何建立索引。對同一個關(guān)系我們要建立多條索引路徑。

4）DBMS常用的存取方法：索引方法（B+樹索引方法）、聚簇方法、HASH方法

第三章 例題講解

1）

2）

3）

4）

5）

6）重點(diǎn)：第三章出現(xiàn)的設(shè)計與應(yīng)用題

第四章 數(shù)據(jù)庫系統(tǒng)功能設(shè)計于是實(shí)施

總述：DBAS功能設(shè)計包括應(yīng)用軟件設(shè)計中的數(shù)據(jù)庫事務(wù)設(shè)計和應(yīng)用程序設(shè)計。

功能設(shè)計過程分為：總體設(shè)計、概念設(shè)計、詳細(xì)設(shè)計；具體到數(shù)據(jù)庫事務(wù)設(shè)計分為：事物概要設(shè)計、事物詳細(xì)設(shè)計

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

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

一）軟件體系結(jié)構(gòu)：又稱為軟件架構(gòu)是軟件系統(tǒng)中最本質(zhì)的東西，良好的軟件體系結(jié)構(gòu)必須是普適、高效和穩(wěn)定的；軟件體系結(jié)構(gòu)={構(gòu)件（軟件系統(tǒng)的各個模塊），連接件（接口和過程調(diào)用）、約束（完整性約束和規(guī)則）}

軟件體系結(jié)構(gòu)類型：分層體系結(jié)構(gòu)、MVC（模型-視圖-控制器）體系結(jié)構(gòu)、客戶端/服務(wù)器（B/S、C/S）體系結(jié)構(gòu)

二）軟件設(shè)計過程

1）軟件開發(fā)有設(shè)計、實(shí)現(xiàn)、測試三個環(huán)節(jié)組成；包含概要設(shè)計(任務(wù)：軟件總體結(jié)構(gòu)圖設(shè)計)和詳細(xì)設(shè)計（任務(wù)：數(shù)據(jù)設(shè)計、過程設(shè)計及人機(jī)界面設(shè)計）

2）設(shè)計原則：模塊化、信息隱藏、抽象和逐步求精

3）軟件設(shè)計可選用的方法：結(jié)構(gòu)化設(shè)計方法、面向?qū)ο蠓椒ā⒚嫦驍?shù)據(jù)設(shè)計方法

第二節(jié) DBAS整體設(shè)計

DBAS整體設(shè)計的任務(wù)：確定系統(tǒng)整體框架；涉及的內(nèi)容包括：DBAS體系結(jié)構(gòu)設(shè)計、軟件體系結(jié)構(gòu)設(shè)計、軟件硬選型與配置設(shè)計、業(yè)務(wù)規(guī)則初步設(shè)計

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

1）常見的DBAS體系結(jié)構(gòu)：客戶服務(wù)器體系結(jié)構(gòu)(C/S)、瀏覽器服務(wù)器體系結(jié)構(gòu)（B/S）

2）三層客戶服務(wù)器體系結(jié)構(gòu)(C/S)：表示層、功能層（業(yè)務(wù)：應(yīng)用服務(wù)器）、數(shù)據(jù)層（DB：數(shù)據(jù)庫服務(wù)器）

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

1）DBAS軟件包括：操作系統(tǒng)、數(shù)據(jù)庫管理系統(tǒng)、開發(fā)環(huán)境中間件、應(yīng)用軟件（數(shù)據(jù)庫事務(wù)和應(yīng)用程序）

2）可用模塊結(jié)構(gòu)圖（模塊+調(diào)用+控制+轉(zhuǎn)接）表示總體結(jié)構(gòu)和分層模塊結(jié)構(gòu)（體系結(jié)構(gòu)的情況）

三、軟件硬選型與配置設(shè)計

總體設(shè)計階段需要對軟硬件設(shè)備做出合理選擇，并進(jìn)行初步配置設(shè)計。

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

關(guān)鍵：業(yè)務(wù)流程圖——總體設(shè)計階段

第三節(jié) DBAS功能概要設(shè)計

1） DBAS功能概要設(shè)計在總體設(shè)計的基礎(chǔ)上進(jìn)一步細(xì)化模塊/子模塊，組成應(yīng)用軟件的系統(tǒng)—子系統(tǒng)—模塊—子模塊結(jié)構(gòu)，并從結(jié)構(gòu)、行為、數(shù)據(jù)三方面進(jìn)行設(shè)計。

2）從功能角度，DBAS系統(tǒng)通常劃分為四個層次：表示層、業(yè)務(wù)邏輯層、數(shù)據(jù)訪問層、數(shù)據(jù)持久層

一、表示層概要設(shè)計

1）人機(jī)界面設(shè)計，影響系統(tǒng)易用性（目前第四代為WIMP與web技術(shù)多任務(wù)處理技術(shù)相結(jié)合）

2）設(shè)計原則：對用戶友好；用戶自主控制；反饋及時上下文感知；容錯與錯誤恢復(fù)；界面標(biāo)準(zhǔn)常規(guī)；輸入靈活；界面簡潔、交互及時。

二、業(yè)務(wù)邏輯層概要設(shè)計

1）設(shè)計原則：高內(nèi)聚低耦合

2）設(shè)計內(nèi)容：結(jié)構(gòu)、行為、數(shù)據(jù)、接口等等

三、數(shù)據(jù)訪問層概要設(shè)計

1）事物概要設(shè)計核心：在于辨識和設(shè)計事物自身的處理邏輯、注意流程

2）一個完整的事物概要設(shè)計包括：事務(wù)名稱、訪問的關(guān)系表和數(shù)據(jù)項(xiàng)、事務(wù)邏輯、事務(wù)用戶

四、事務(wù)

1）事務(wù)的概念（Transaction）：事務(wù)是訪問并可能更新數(shù)據(jù)庫中各種數(shù)據(jù)項(xiàng)的一個程序執(zhí)行單元

2）事務(wù)的特性：原子性、一致性、隔離性、持續(xù)性；稱為ACID特性

? 原子性：一個不可分割的工作單位；一致性：從一個一致性狀態(tài)變到另一個一致性狀態(tài)；隔離性：執(zhí)行不能被其他事務(wù)干擾；持久性：永久性，他對數(shù)據(jù)庫的改變是永久的。

五、數(shù)據(jù)持久層概要設(shè)計

第三章內(nèi)容

第四節(jié) DBAS功能詳細(xì)設(shè)計

一、表示層詳細(xì)設(shè)計

人機(jī)界面采用原型迭代法。

二、業(yè)務(wù)邏輯層詳細(xì)設(shè)計

1）設(shè)計各模塊內(nèi)部處理流程和算法、具體數(shù)據(jù)結(jié)構(gòu)和詳細(xì)接口。

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

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

安全性保護(hù)、完整性保護(hù)、并發(fā)性保護(hù)、數(shù)據(jù)備份與恢復(fù)、數(shù)據(jù)加密傳輸

1）數(shù)據(jù)庫的安全性保護(hù)

? （1）主要保護(hù)方法：用身份鑒別、權(quán)限控制

2）數(shù)據(jù)庫的完整性保護(hù)

? 定義：數(shù)據(jù)庫的完整性保護(hù)是指數(shù)據(jù)庫中的正確性、一致性

及相容性

? 方法：設(shè)置完整性檢查（即對數(shù)據(jù)設(shè)置一些約束條件（實(shí)體完整性、參考完整性、用戶自定義完整性））

? 完整性條件作用對象：列、元組、關(guān)系

? DBAS中的完整性約束功能包括：完整性約束條件設(shè)置和檢查。

3）數(shù)據(jù)庫的并發(fā)控制

? 并發(fā)控制：事務(wù)在空間上重疊執(zhí)行。

? 并發(fā)控制機(jī)制是衡量一個DBAS性能的一個重要標(biāo)志；實(shí)現(xiàn)并發(fā)控制常用的方法是封鎖技術(shù)（就是Java里面的鎖）。

? 基本鎖類型：排他鎖（X鎖）—寫鎖、共享鎖（S鎖）—讀鎖

? 死鎖：是兩個或者兩個以上的事務(wù)之間的循環(huán)等待。

? 避免死鎖的原則： ①按照同一順序訪問資源。（如第一個事務(wù)提交或回滾后第二個事務(wù)進(jìn)行） ②避免事務(wù)交互性 ③采用小事務(wù)模 式，縮短長度和占用時間。 ④盡量使用記錄級別的所（行鎖），少用表級別鎖。 ⑤使用綁定連接，同一用戶打開的兩個或多個連接 可以互相合作。

4）數(shù)據(jù)的備份與恢復(fù)

? 數(shù)據(jù)庫恢復(fù)的基本原理：

? 數(shù)據(jù)庫備份與恢復(fù)的策略：雙機(jī)熱備、數(shù)據(jù)轉(zhuǎn)儲（數(shù)據(jù)備份）、數(shù)據(jù)加密存儲（針對高敏感數(shù)據(jù)）

7）數(shù)據(jù)加密傳輸常見的傳輸手段：數(shù)字安全證書、對稱密鑰加密、數(shù)字簽名、數(shù)字信封

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

漏洞與補(bǔ)丁、計算機(jī)病毒防護(hù)、網(wǎng)絡(luò)環(huán)境安全（防火墻）、物理環(huán)境安全

三、制度安全設(shè)計

管理層安全措施

第六節(jié) DBAS實(shí)施

一、DBAS實(shí)施工作

1）創(chuàng)建數(shù)據(jù)庫、2)數(shù)據(jù)裝載、3）編寫調(diào)試應(yīng)用程序、4）數(shù)據(jù)庫系統(tǒng)運(yùn)行（功能測試、性能測試）

第四章 例題

1、

2）

3）

4）

5）

6）

7）

8）

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

內(nèi)容摘要：

? ①了解DBAS建模方法；②掌握DBAS業(yè)務(wù)流程與需求表達(dá)方法；③掌握DBAS系統(tǒng)內(nèi)部結(jié)構(gòu)的表達(dá)方法；④掌握DBAS微觀設(shè)計的表達(dá)方法；⑤了解DBAS宏觀設(shè)計的表達(dá)方法；⑥了解DBAS系統(tǒng)實(shí)現(xiàn)與部署的表達(dá)方法。

第一節(jié) DBAS建模

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

1）UML是面向?qū)ο蟮目梢暬耐ㄓ谜Z言，他是一種建模語言不是建模方法。

2）建模方法包括建模語言與建模過程；①建模語言：提供這種方法用于表示建模結(jié)果的符號（圖形符號：可視化）；②建模過程：嗎iOS建模時需要遵循的步驟。

二、UML的組成

1）由**語義（自然語言）與表示法（可視化標(biāo)準(zhǔn)符號）**組成。

? 四層建模概念框架：元元模型、元模型、模型層（類模型或類型模型）、用戶模型（實(shí)例模型：具體的對象）

2）UML的五種視圖：結(jié)構(gòu)、實(shí)現(xiàn)、行為、環(huán)境、用例視圖

3）UML 2.0分為靜態(tài)結(jié)構(gòu)圖和行為圖

第二節(jié) DBAS業(yè)務(wù)流程圖與需求表達(dá)

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

? 活動圖最適合描述系統(tǒng)或子系統(tǒng)的工作流程。

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

三、關(guān)系說明

1）角色之間的關(guān)系——通用化關(guān)系（繼承：extends）帶三角符號的箭頭

2）用例與角色之間的關(guān)系——連接關(guān)系（關(guān)聯(lián)關(guān)系）

3）用例之間的關(guān)系——擴(kuò)展（extends）關(guān)系、包含（use、include）（使用）關(guān)系、組合（關(guān)聯(lián)）關(guān)系、泛化關(guān)系

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

一、類圖

1）類與類的關(guān)系：關(guān)聯(lián)關(guān)系、繼承、依賴、精化（實(shí)現(xiàn)）

2）組成：類名、屬性、方法（操作、行為）

二、順序圖-行為建模

1）順序圖主要用于描述系統(tǒng)內(nèi)對象之間的消息發(fā)送和接收序列；

2）順序圖中出現(xiàn)的元素一定是在類圖中出現(xiàn)的；用來強(qiáng)調(diào)時間。

三、通信圖（協(xié)作圖）

第四節(jié) DBAS系統(tǒng)微觀設(shè)計的表達(dá)

一、對象圖

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

矩形框上面的左邊為對象名——后面為所屬的類

二、狀態(tài)圖

1）狀態(tài)圖用來描述有關(guān)事件或?qū)ο竦臓顟B(tài)轉(zhuǎn)移；只能有一個開始狀態(tài)可以有多個結(jié)束狀態(tài)。

2）狀態(tài)圖的轉(zhuǎn)移由事件驅(qū)動。

三、時間圖

當(dāng)狀態(tài)的轉(zhuǎn)換由時間因素決定時，使用時間圖來描述狀態(tài)的變化。

第五章 DBAS系統(tǒng)宏觀設(shè)計的表達(dá)

一、包圖

二、交互概述圖

交互概述圖相當(dāng)于活動圖和順序圖的結(jié)合

三、復(fù)合結(jié)構(gòu)圖

復(fù)合結(jié)構(gòu)圖適用于系統(tǒng)間的溝通接口，進(jìn)行系統(tǒng)架構(gòu)設(shè)計和系統(tǒng)維護(hù)時。

第六節(jié) DBAS系統(tǒng)實(shí)現(xiàn)與部署的表達(dá)

一、組件圖

組件時邏輯中定義的概念和概念在物理架構(gòu)中的實(shí)現(xiàn)。

二、部署圖（配置圖）

1）描述系統(tǒng)中軟硬件的物理配置情況與系統(tǒng)結(jié)構(gòu)。

2）部署圖說明實(shí)體組件如何執(zhí)行程序，將如何部署到實(shí)際的計算機(jī)中——在集成測試之前。

[外鏈圖片轉(zhuǎn)存失敗,源站可能有防盜鏈機(jī)制,建議將圖片保存下來直接上傳(img-EZPS9LIT-1616819653259)(]

第五章例題

1）

2）

3）

4）

5）

6）

7）

第六章 數(shù)據(jù)庫以及數(shù)據(jù)庫對象

第一節(jié) 創(chuàng)建及維護(hù)數(shù)據(jù)庫

一、SQL server數(shù)據(jù)庫分類

1）系統(tǒng)數(shù)據(jù)庫（系統(tǒng)自動創(chuàng)建）：master、msdb、tempdb、model、resource

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

二、SQL server數(shù)據(jù)庫的組成

1）SQL server將數(shù)據(jù)庫映射為一組操作系統(tǒng)文件。

2）數(shù)據(jù)文件：①.mdf：主要數(shù)據(jù)文件，不能小于3M。只有一個；②.ndf：次要數(shù)據(jù)文件，0個或多個；

日志文件：.ldf：事務(wù)日志文件，至少有一個日志文件。

三、數(shù)據(jù)庫存儲空間的發(fā)分配

1）數(shù)據(jù)存儲的最小單位是數(shù)據(jù)頁（也就是頁），一頁是一塊8KB的連續(xù)磁盤空間。

2**）頁的大小決定了數(shù)據(jù)庫表中一行**（一個記錄、元組）數(shù)據(jù)的最大值—不能超過8KB

3）行不能跨頁存儲，一頁可以存放多行數(shù)據(jù)但是不能超過8KB

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

1）類型：主文件組（系統(tǒng)定義，主要數(shù)據(jù)文件和如何沒有明確分配的其他文件）、用戶定義文件組（使用FILEGROUP關(guān)鍵字定義）

五、創(chuàng)建數(shù)據(jù)庫

1）通過圖形可視化工具直接創(chuàng)建

2）通過T-SQL語言創(chuàng)建

舉例：

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

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

一）分離數(shù)據(jù)庫

? 1）作用：就是將數(shù)據(jù)庫從一臺數(shù)據(jù)庫服務(wù)器轉(zhuǎn)移到另一臺數(shù)據(jù)庫服務(wù)器，不需要重建（類似于剪切）

? 2）使用的語句：sp_detach_db實(shí)現(xiàn)系統(tǒng)存儲過程

二）附加數(shù)據(jù)庫

? 將分離的數(shù)據(jù)庫重新安裝到數(shù)據(jù)庫管理系統(tǒng)中，必須指定主要數(shù)據(jù)文件的物理存儲位置和文件名。

? 2）語句：CREATE DATABASE …（數(shù)據(jù)庫名） ON(存儲位置) FOR ATTACH

第二節(jié) 架構(gòu)

一、架構(gòu)（模式）

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

2）定義架構(gòu)語句：CREATE SCHEMA [<架構(gòu)名>] AUTHORIZATION [<用戶名>]

3）刪除架構(gòu)：DROP SCHEMA [<架構(gòu)名>]

第三節(jié) 分區(qū)表

一、基本概念

? 1）分區(qū)表是將表中的數(shù)據(jù)按水平分割成不同的子集，并將數(shù)據(jù)子集存儲在數(shù)據(jù)庫一個或多個文件組中。

? 2）物理上將大表分成好幾個小表，邏輯上還是一個大表；合理使用分區(qū)能提高數(shù)據(jù)庫性能。

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

實(shí)例：

第五節(jié) 索引視圖

1）創(chuàng)建索引

2）刪除索引

一、基本概念

? 1）標(biāo)準(zhǔn)視圖（也稱虛擬表）：返回結(jié)果集與標(biāo)準(zhǔn)表一致，標(biāo)準(zhǔn)視圖的結(jié)果集不永久存放。

? 2）索引視圖（也稱物化視圖）：建立唯一聚集索引的視圖；視圖結(jié)果存放在數(shù)據(jù)庫中。

?

二、索引視圖使用與限制

1）適用場合：①很少更新的基礎(chǔ)數(shù)據(jù)；②基礎(chǔ)數(shù)據(jù)使用批處理定期更新且作為只讀數(shù)據(jù)

2）

三、定義索引視圖

創(chuàng)建索引視圖必須滿足的條件：①視圖只能引用基本表，不能是其他的視圖；②引用的基本表和視圖在同一歌數(shù)據(jù)庫中；③必須用SCHEMABINDING選項(xiàng)創(chuàng)建視圖；④視圖中的表達(dá)式引用的所有函數(shù)必須確定。

第六章例題

1）

[外鏈圖片轉(zhuǎn)存失敗,源站可能有防盜鏈機(jī)制,建議將圖片保存下來直接上傳(img-h5AUySgD-1616819653276)(C:\Users\潘仕毅\AppData\Roaming\Typora\typora-user-images\image-20210224184633675.png)]

2）

3）

4）創(chuàng)建唯一約束后面必須要字段

5）

6）

注意：在數(shù)據(jù)庫設(shè)計題有怎么創(chuàng)建數(shù)據(jù)庫

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

第一節(jié) 數(shù)據(jù)查詢功能擴(kuò)展

一、查詢語句–SELETE（具體看榮瑩數(shù)據(jù)庫-第五章）

1）SELETE語句的格式和含義

SELECT [DISTINCT] [TOP n] select_list ——查詢指定的列、DISTINCT消除重復(fù)元組

[INTO new_table]——將查詢結(jié)果創(chuàng)建到新表

[FROM table_source]——查詢的目標(biāo)表

[WHERE search_conditition]——查詢的條件

[GROUP BY group_by_expression]——指定分組查詢的條件

[HAVING search_condition]——指定組或聚合函數(shù)查詢的條件

[ORDER BY order_expression [ASC|DESC]]——指定表的數(shù)據(jù)的升降序

[COMPUTE expression] ——在結(jié)果集得到末尾生成匯總數(shù)據(jù)

2）WHERE 條件表達(dá)式中可使用下列運(yùn)算符：

?算術(shù)比較運(yùn)算符：＜、＜＝、＞、＞＝、＝、＜＞或！＝。

?邏輯運(yùn)算符：AND、OR、NOT（AND和 OR來聯(lián)結(jié)多個查詢條件可多次出現(xiàn)、 AND的優(yōu)先級高于OR、 可以用括號改變優(yōu)先級）。

?集合運(yùn)算符：IN（DNAME IN ( ‘計算機(jī)’，‘電子’)表示取DNAME為’計算機(jī)’，’電子‘的結(jié)果集）、NOT IN（與前面相反）。

?謂詞：EXISTS(存在量詞)、ALL、SOME、UNIQUE（并）、BETWEEN … AND（在某個范圍）、NOT BETWEEN … AND（不在這個范圍） 。

?聚合函數(shù)：AVG、MIN、MAX、SUM、COUNT。

? F中運(yùn)算對象還可以是另一個SELECT語句，即SELECT語句可以嵌套。

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

1）TOP n [percent] [WITH TIES]

? Top n 前n行

? Top n [percent]前n%行

? [WITH TIES]：包括最后一行取值并列的結(jié)果。

2）TOP的使用：SELECT TOP 3 WITH TIES 。。。。

三、使用CASE函數(shù)

1）語法：

CASE

WHEN 布爾表達(dá)式1 then 結(jié)果表達(dá)式1

WHEN 布爾表達(dá)式2 then 結(jié)果表達(dá)式2

……

WHEN 布爾表達(dá)式n then 結(jié)果表達(dá)式n

[ELSE 結(jié)果表達(dá)式n+1]

2）使用舉例

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 ……

注意：局部臨時表和全局臨時表只在當(dāng)前查詢中一次有效

第二節(jié) 查詢結(jié)果的交、并、差運(yùn)算

①在FROM子句中給出多個表名，即可完成笛卡爾積運(yùn)算。

②建立幾個表連接的方法：在FROM子句中列出需連接的表名，在WHERE子句給出連接條件或連接謂詞

一、并運(yùn)算

1）并運(yùn)算（UNION）：將多個查詢結(jié)果合并為一個結(jié)果集。。

語法：

2）使用UNION注意：

?要進(jìn)行合并的查詢，SELECT中列數(shù)必須相同，語義相同。

?每個相對應(yīng)列的數(shù)據(jù)類型隱式兼容，如char（20）與varchar（40）。

?合并后結(jié)果采用第一個SELECT語句的列標(biāo)題。

?若需排序，則GROUP BY語句寫在最后一個SELECT之后，且排序的一句是第一個SELECT中的列名。

3）舉例：求選修了課程C2或C4的學(xué)生的學(xué)號和姓名

二、交運(yùn)算

1）交運(yùn)算：返回同時在兩個集合中出現(xiàn)的記錄。

語法：

2）舉例：求選修了課程C2和C4的學(xué)生的學(xué)號和姓名

三、差運(yùn)算

1）差運(yùn)算：返回第一個集合中有而第二個集合中沒有的的記錄。

語法：

2）舉例：求選修了課程C2但沒有選修課程C4的學(xué)生的學(xué)號和姓名

第三節(jié) 子查詢

一、單值子查詢

1）WHERE子句中的條件表達(dá)式可以是標(biāo)量數(shù)據(jù) ，也可以是一個SELECT-FROM-WHERE查詢塊構(gòu)成的子查詢。

2）①子查詢的限制：不能使用ORDER BY子句；②層層嵌套方式反映了 SQL語言的結(jié)構(gòu)化，③有些嵌套查詢可以用連接運(yùn)算替代

3）舉例：求選修了數(shù)據(jù)結(jié)構(gòu)的學(xué)生的學(xué)號

SELECT語句：

也可以用自身連接完成前例查詢要求

二、包含多值的子查詢

1）子查詢的結(jié)果不是單一值，而是多個值，即一個集合（記為R）。與子查詢結(jié)果比較，可使用以下運(yùn)算符：

① EXISTS R是一個條件，當(dāng)且僅當(dāng)R非空時，該條件為真。

EXISTS相當(dāng)于離散數(shù)學(xué)中的存在量詞。

② s IN R為真，當(dāng)且僅當(dāng)s等于R中的一個值。類似地，s NOT IN R為真，當(dāng)且僅當(dāng)s不等于R中的值。

IN的含義相當(dāng)于集合論中的“屬于”(∈)。類似地,s NOT IN R,表示s不屬于R。

③ s>ALL R為真，當(dāng)且僅當(dāng)s大于關(guān)系R中的每一個值。同樣可以使用其他五個比較運(yùn)算符(>=、=、<、<=、<>)。

例如,s<>ALL R，表示s NOT IN R。

④ s>ANY R為真，當(dāng)且僅當(dāng)s至少大于關(guān)系R中的一個值。同樣可以使用其他五個比較運(yùn)算符(>=、=、<、<=、<>)。

例如,s = ANY R表示s IN R。

2）例一：查詢選修了課程號為‘C4’的學(xué)生的SNO和SNAME

舉例二：查詢選修了“數(shù)據(jù)結(jié)構(gòu)”的學(xué)生的SNO和SNAME

執(zhí)行過程：

三、使用EXISTS的子查詢

一、EXISTS

1）EXISTS—>①相當(dāng)于離散數(shù)學(xué)中的存在量詞；

②帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。

③若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值；若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回假值

④由EXISTS引出的子查詢，其屬性名表達(dá)式通常都用* ，因?yàn)閹XISTS的子查詢只返回真值或假值，給出屬性名無實(shí)際意義。

2）舉例：查詢所有選修了C2號課程的學(xué)生姓名

執(zhí)行過程：先將S表中的SNO拿出來與 SC中的SNO一一對比；②當(dāng)S.SNO=SC.SNO相等時，再判斷CNO是否為’C2’，若為’C2’則返回真（因?yàn)樽硬樵儓?zhí)行的結(jié)果集不為空）；③所以在父查詢執(zhí)行完畢后會有相應(yīng)的結(jié)果集出現(xiàn)。

3）執(zhí)行結(jié)果為：

二） NOT EXISTS

1）若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回假值；若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回真值

2）例：用NOT EXISTS求沒有選修C3課程的學(xué)生的姓名。

3）執(zhí)行過程與 EXISTS 的執(zhí)行過程差不多，只不過是為空時為真；返回的結(jié)果為沒有選修C3課程的學(xué)生的姓名。

三） NOT EXISTS與 NOT EXISTS 結(jié)合舉例：

思路分析：（將課程表視為一個分組，用來判斷是否全部選修）

①本查詢涉及S、C和SC三個關(guān)系

②在S關(guān)系中依次取每個元組的學(xué)號SNO

③從C關(guān)系中依次取出每個元組的課程號CNO

④在SC關(guān)系中查詢學(xué)號SNO是否選修課程號CNO，若未選修，則將該元組的SNAME放入查詢結(jié)果集

總結(jié)：①EXISTS 和 NOT EXISTS 想象為一個圈，EXISTS 是在圈里面，NOT EXISTS在圈外面（圈指的是子查詢結(jié)果）；②在進(jìn)行設(shè)計時要思考題目中出現(xiàn)的關(guān)鍵字以關(guān)鍵字來確定選擇 EXISTS 或者 NOT EXISTS ；③思考題目中出現(xiàn)的關(guān)鍵字涉及到那些表，以確定在進(jìn)行設(shè)計時需要哪些表。

四、使用ANY（SOME）或ALL的子查詢

1）子查詢返回標(biāo)量值時可以用比較運(yùn)算符

返回多值要使用比較運(yùn)算符，則需要配合ANY或ALL

ANY：任意一個值

ALL：所有值

2）含義：

> ANY 大于子查詢結(jié)果中的某個值 （存在大于）

> ALL 大于子查詢結(jié)果中的所有值（比最大值還大）

< ANY 小于子查詢結(jié)果中的某個值 （存在小于）

< ALL 小于子查詢結(jié)果中的所有值 （比最小值還小）

>= ANY 大于等于子查詢結(jié)果中的某個值 （存在大于或等于）

>= ALL 大于等于子查詢結(jié)果中的所有值 （應(yīng)該為一個與子查詢相同大小的集合）

<= ANY 小于等于子查詢結(jié)果中的某個值 （存在小于或等于）

<= ALL 小于等于子查詢結(jié)果中的所有值 （應(yīng)該為一個與子查詢相同大小的集合）

= ANY 等于子查詢結(jié)果中的某個值 （存在等于關(guān)系）

=ALL 等于子查詢結(jié)果中的所有值（通常沒有實(shí)際意義）

!=（或<>）ANY 不等于子查詢結(jié)果中的某個值

!=（或<>）ALL 不等于子查詢結(jié)果中的任何一個值

3）舉例: 查詢其他系中比計算機(jī)系任意學(xué)生年齡小的SNAME和AGE

第四節(jié) 其他形式的子查詢

一、替代表達(dá)式的子查詢

在SELECT的選擇列表中嵌入了一個只返回一個標(biāo)量值的子查詢。

二、派生表

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

2）實(shí)例：查詢至少買了C001和C002兩種商品的顧客號和顧客名。

復(fù)習(xí)：聚集函數(shù)

原文鏈接：https://blog.csdn.net/u010327460/article/details/80527568

聚集函數(shù)是以值得一個集合（集或多重集）為輸入、返回單個值得函數(shù)。

固有的聚集函數(shù)

SQL提供了五個固有的聚集函數(shù)

平均值 ：avg
最小值： min
最大值：max
總和： sum
計數(shù)： count

一、基本聚集

注意： 在計算平均數(shù)時，保留重復(fù)元組很重要。

有些情況下，計算聚集函數(shù)需要先刪除重復(fù)元組。可以使用distinct。

SQL中不允許使用count(*)的時候使用distinct。使用max和min時，可以使用distinct

二、分組聚集

使用 group by 將聚集函數(shù)作用于一組元組集上。
理解：group by 子句中給出的一個或多個屬性是用來構(gòu)造數(shù)組的。在 group by 子句中的所有屬性上取值相同的元組被分在一個組中。

重要結(jié)論：出現(xiàn)在select語句中但沒有被聚集的屬性，只能是那些出現(xiàn)在group by 子句中那些的屬性。換句話說，任何沒有出現(xiàn)在group by 中的屬性，如果出現(xiàn)在select子句中，它只能出現(xiàn)在聚集函數(shù)內(nèi)部，否則這樣的查詢就是錯誤的。

分析：ID出現(xiàn)在select子句中，且不是聚集函數(shù)的參數(shù)，卻沒出現(xiàn)在group by，查詢錯誤。換個思路，一個分組有多個教師，每個教師有不同的ID，每個分組只輸出一個元組，那么就無法確定輸出哪一個ID。

三、having子句

having子句是對分組的限定(就是where，條件)。

having子句使用 having avg_salary 會有什么不同？

任何出現(xiàn)在having子句中，但沒有被聚集的屬性必須出現(xiàn)在 group by 子句中，否則查詢就被當(dāng)成錯誤的。

包含聚集、group by或having子句的查詢

包含聚集、group by或having子句的查詢的含義可通過下述操作序列來定義：

對空值和布爾值的聚集

四、聚集函數(shù)處理空值規(guī)則如下：

規(guī)定空集的count運(yùn)算值為0 ；其他所有聚集運(yùn)算在輸入為空集的情況下返回一個空值。
SQL:1999 中 引入了布爾數(shù)據(jù)類型，可以取true， false， unknown 三個值。有兩個聚集函數(shù)some 和 every。

開窗函數(shù)

原文鏈接：https://www.cnblogs.com/lihaoyang/p/6756956.html

一、開窗函數(shù)

? 與聚合函數(shù)一樣，開窗函數(shù)也是對行集組進(jìn)行聚合計算，但是它不像普通聚合函數(shù)那樣每組只返回一個值，開窗函數(shù)可以為每組返回多個值，因?yàn)殚_窗函數(shù)所執(zhí)行聚合計算的行集組是窗口。在 ISO SQL 規(guī)定了這樣的函數(shù)為開窗函數(shù)，在 Oracle 中則被稱為分析函數(shù)。

? 數(shù)據(jù)表（Oracle）：T_Person 表保存了人員信息，FName 字段為人員姓名，FCity 字段為人員所在的城市名，FAge 字段為人員年齡，FSalary 字段為人員工資

向 T_Person 表中插入一些演示數(shù)據(jù)：

[](javascript:void(0)😉

[](javascript:void(0)😉

select * from t_person:

要計算所有人員的總數(shù)，我們可以執(zhí)行下面的 SQL 語句：SELECT COUNT(*) FROM T_Person

除了這種較簡單的使用方式，有時需要從不在聚合函數(shù)中的行中訪問這些聚合計算的值。比如我們想查詢每個工資小于 5000 元的員工信息（城市以及年齡），并且在每行中都顯示所有工資小于 5000 元的員工個數(shù)：

[](javascript:void(0)😉

[](javascript:void(0)😉

雖然使用子查詢能夠解決這個問題，但是子查詢的使用非常麻煩，使用開窗函數(shù)則可以大大簡化實(shí)現(xiàn)，下面的 SQL 語句展示了如果使用開窗函數(shù)來實(shí)現(xiàn)同樣的效果：

可以看到與聚合函數(shù)不同的是，開窗函數(shù)在聚合函數(shù)后增加了一個 OVER 關(guān)鍵字。

開窗函數(shù)格式： 函數(shù)名(列) OVER(選項(xiàng))

? 1）OVER 關(guān)鍵字表示把函數(shù)當(dāng)成開窗函數(shù)而不是聚合函數(shù)。SQL 標(biāo)準(zhǔn)允許將所有聚合函數(shù)用做開窗函數(shù)，使用 OVER 關(guān)鍵字來區(qū)分這兩種用法。
? 2）在上邊的例子中，開窗函數(shù) COUNT(*) OVER()對于查詢結(jié)果的每一行都返回所有符合條件的行的條數(shù)。OVER 關(guān)鍵字后的括號中還經(jīng)常添加選項(xiàng)用以改變進(jìn)行聚合運(yùn)算的窗口范圍。如果 OVER 關(guān)鍵字后的括號中的選項(xiàng)為空，則開窗函數(shù)會對結(jié)果集中的所有行進(jìn)行聚合運(yùn)算。

二、PARTITION BY 子句：

開窗函數(shù)的 OVER 關(guān)鍵字后括號中的可以使用 PARTITION BY 子句來定義行的分區(qū)來供進(jìn)行聚合計算。與 GROUP BY 子句不同，PARTITION BY 子句創(chuàng)建的分區(qū)是獨(dú)立于結(jié)果集的，創(chuàng)建的分區(qū)只是供進(jìn)行聚合計算的，而且不同的開窗函數(shù)所創(chuàng)建的分區(qū)也不互相影響。下面的 SQL 語句用于顯示每一個人員的信息以及所屬城市的人員數(shù)：

COUNT(*) OVER(PARTITION BY FCITY) 表示對結(jié)果集按照FCITY進(jìn)行分區(qū)，并且計算當(dāng)前行所屬的組的聚合計算結(jié)果。比如對于FName等于 Tom的行，它所屬的城市是BeiJing，同屬于BeiJing的人員一共有6個，所以對于這一列的顯示結(jié)果為6。

這就不需要先對fcity分組求和，然后再和t_person表連接查詢了，省事兒。

在同一個SELECT語句中可以同時使用多個開窗函數(shù)，而且這些開窗函數(shù)并不會相互干
擾。比如下面的SQL語句用于顯示每一個人員的信息、所屬城市的人員數(shù)以及同齡人的人數(shù)：

[](javascript:void(0)😉

[](javascript:void(0)😉

三、ORDER BY子句：

開窗函數(shù)中可以在 OVER 關(guān)鍵字后的選項(xiàng)中使用ORDER BY子句來指定排序規(guī)則，而且有的開窗函數(shù)還要求必須指定排序規(guī)則。使用ORDER BY子句可以對結(jié)果集按照指定的排序規(guī)則進(jìn)行排序，并且在一個指定的范圍內(nèi)進(jìn)行聚合運(yùn)算。ORDER BY子句的語法為：

RANGE表示按照值的范圍進(jìn)行范圍的定義，而ROWS表示按照行的范圍進(jìn)行范圍的定義；邊界規(guī)則的可取值見下表：

“RANGE|ROWS BETWEEN 邊界規(guī)則1 AND 邊界規(guī)則2”部分用來定位聚合計算范圍，這個子句又被稱為定位框架。

例子程序一：查詢從第一行到當(dāng)前行的工資總和：

[](javascript:void(0)😉

[](javascript:void(0)😉

上述語句的理解：

這里的開窗函數(shù) “SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)” 表示按照FSalary進(jìn)行排序，然后計算從第一行（UNBOUNDED PRECEDING）到當(dāng)前行（CURRENT ROW）的和，這樣的計算結(jié)果就是按照工資進(jìn)行排序的工資值的累積和。

第五節(jié) 其他查詢功能

一、排名函數(shù)

一）RANK（）

? 我們理解的排名為1，2，3，········但是 RANK() 函數(shù)的排名形式可能為：1，2，4·········即可能存在并列的情況，但是 RANK() 函數(shù)不會是連續(xù)； 排名函數(shù)具有不確定性 ， 排名從1開始，不一定是連續(xù)整數(shù)。

二）DENSE_BANK()

? 排名是連續(xù)的整數(shù)

三）NTILE()

? 將有序分區(qū)中的行劃分到指定的數(shù)目的組中，編號從1開始，函數(shù)返回此行所屬的組的編號。

四）ROWNUMBER()

? 返回結(jié)果集中每個分區(qū)的序列號，每個分區(qū)的第一行從1開始。

二、公用表表達(dá)式

? 1）公用表表達(dá)式定義：將查詢結(jié)果集指定一個臨時名字，這些命名的結(jié)果集就是公用表表達(dá)式。

? 2）格式：

3）舉例：定義一個統(tǒng)計每個會員購買商品總次數(shù)的CTE，并利用CTE查詢會員卡號和購買商品的次數(shù)。

第七章 例題講解（重點(diǎn)）

**1．**設(shè)某數(shù)據(jù)庫中有旅客表A（旅客編號，城市）和旅客表B（旅客編號，城市），現(xiàn)將所有旅客的數(shù)據(jù)存儲這兩張表中。請補(bǔ)全如下查詢語句，使得該查詢語句能查詢所有旅客所在的全部的不重復(fù)的城市。

SELECT 城市 FROM 旅客表A

（　　）

SELECT 城市 FROM 旅客表B

答案： UNION

2．設(shè)有購買表（顧客號，商品號，購買時間）。現(xiàn)要查詢顧客A與顧客B購買的相同商品。有下列查詢語句：

Ⅰ．SELECT 商品號 FROM 購買表 WHERE 顧客號 = ‘A’

AND 商品號 IN (SELECT 商品號 FROM 購買表 WHERE 顧客號 = ‘B’)

Ⅱ．SELECT 商品號 FROM 購買表 WHERE 顧客號 = ‘A’

? EXCEPT SELECT 商品號 FROM 購買表 WHERE 顧客號 = 'B‘

Ⅲ．SELECT 商品號 FROM 購買表 WHERE 顧客號 = ‘A’

? INTERSECT SELECT 商品號 FROM 購買表 WHERE 顧客號 = ‘B’

Ⅳ．SELECT 商品號 FROM 購買表 WHERE 顧客號 = ‘A’

? UNION SELECT 商品號 FROM 購買表 WHERE 顧客號 = ‘B’

上述語句中，能夠?qū)崿F(xiàn)該查詢要求的是（　　）。

A．僅Ⅰ和Ⅱ

B．僅Ⅰ和Ⅲ

C．僅Ⅰ和Ⅳ

D．僅Ⅲ

答案：B

**3．**設(shè)有選課表（學(xué)號，課程號，成績），現(xiàn)要統(tǒng)計每門課程的選課人數(shù)，并將結(jié)果保存到新表：選課情況表。下列語句中正確的是（ ）。

A．SELECT 課程號, COUNT(*) 選課人數(shù) FROM 選課表 INTO 選課情況表GROUP BY 課程號

B．SELECT 課程號, COUNT(*) 選課人數(shù) INTO 選課情況表（關(guān)鍵 ） FROM 選課表 GROUP BY 課程號

C．SELECT 課程號, COUNT(*) FROM 選課表 INTO 選課情況表（課程號,選課人數(shù)）GROUP BY 課程號

D．SELECT 課程號, COUNT(*) INTO 選課情況表（課程號, 選課人數(shù)）

FROM 選課表 GROUP BY 課程號

答案：B

4．設(shè)某數(shù)據(jù)庫中有學(xué)生表（學(xué)號，姓名，所在系）和選課表（學(xué)號，課程號，成績）。現(xiàn)要查詢沒選課的學(xué)生姓名和所在系。下列語句中能夠?qū)崿F(xiàn)該查詢要求的是（　　）。

A．SELECT 姓名，所在系FROM學(xué)生表a LEFT JOIN選課表b

ON a．學(xué)號=b．學(xué)號WHERE a．學(xué)號IS NULL

**B．**SELECT 姓名，所在系 FROM 學(xué)生表a LEFT JOIN 選課表b

ON a．學(xué)號=b．學(xué)號 WHERE b．學(xué)號IS NULL

C．SELECT 姓名，所在系FROM學(xué)生表a RIGHT JOIN選課表b

ON a．學(xué)號=b．學(xué)號WHERE a．學(xué)號IS NULL

D．SELECT 姓名，所在系FROM學(xué)生表a RIGHT JOIN選課表b

ON a．學(xué)號=b．學(xué)號WHERE b．學(xué)號IS NULL

答案：B

5．設(shè)在SQL Server2008中，用戶U1在DB1數(shù)據(jù)庫中創(chuàng)建了#Temp表。下列關(guān)于#Temp表的說法中，正確的是（　　）。

A．在所有用戶U1發(fā)起的連接中，都可以查詢#Temp表數(shù)據(jù)

**B．**只有在創(chuàng)建#Temp表的連接中才可以查詢#Temp表數(shù)據(jù)

C．在創(chuàng)建#Temp表的連接未斷開時，DB1數(shù)據(jù)庫的所有用戶都可以查詢#Temp表數(shù)據(jù)

D． 在創(chuàng)建#Temp表的連接斷開時，DB1數(shù)據(jù)庫的所有用戶仍可以查詢#Temp表數(shù)據(jù)

答案：B

6．設(shè)在采用SQL Server 2008數(shù)據(jù)庫的圖書館應(yīng)用系統(tǒng)中有三個基本表，表結(jié)構(gòu)如下所示，請用SQL語句完成下列兩個查詢：

①BORROWER：

②LOANS：

③BOOKS：

（1）檢索至少借了5本書的同學(xué)的借書證號、姓名、系名和借書數(shù)。

（2）檢索借書和王麗同學(xué)所借圖書中的任意一本相同的學(xué)生姓名、系名、書名和借書日期。

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

該部分涉及到應(yīng)用設(shè)計題——大題。

第一節(jié) 存儲過程

存儲過程詳解原文鏈接：https://www.cnblogs.com/mark-chan/p/5384139.html

一、基本概念

1）存儲過程：存儲在數(shù)據(jù)庫中供所有用戶程序調(diào)用的子程序（T-SQL語句）；是一組為了完成特定功能的SQL語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中，用戶通過指定存儲過程的名字并給定參數(shù)（如果該存儲過程帶有參數(shù)）來調(diào)用執(zhí)行它。

2）以子程序存儲在數(shù)據(jù)庫中的代碼（T-SQL語句）的執(zhí)行：有權(quán)限的用戶方可執(zhí)行。

3）存儲過程的分類：①系統(tǒng)存儲過程、②用戶自定義存儲過程（用戶自己創(chuàng)建，完成特定的功能）、③擴(kuò)展存儲過程

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

1）創(chuàng)建語法：CREATE PROCEDURE 過程名 ([[IN|OUT|INOUT] 參數(shù)名 數(shù)據(jù)類型 [ , [IN|OUT|INOUT] 參數(shù)名 數(shù)據(jù)類型…]]) [特性 …] 過程體

2）參數(shù)：存儲過程根據(jù)需要可能會有輸入、輸出、輸入輸出參數(shù)，如果有**多個參數(shù)用","**分割開。MySQL存儲過程的參數(shù)用在存儲過程的定義，共有三種參數(shù)類型：IN,OUT,INOUT

IN參數(shù)的值必須在調(diào)用存儲過程時指定，在存儲過程中修改該參數(shù)的值不能被返回，為默認(rèn)值

OUT:該值可在存儲過程內(nèi)部被改變，并可返回

INOUT：調(diào)用時指定，并且可被改變和返回

3）過程體：過程體的開始與結(jié)束使用 BEGIN 與 END 進(jìn)行標(biāo)識。

4）執(zhí)行語法：EXEC （或EXECUTE）過程名（參數(shù)）+；。使用 DECLARE 進(jìn)行變量的定義，@xxx為用戶變量（在數(shù)據(jù)庫中不可多用）；SELECT 為返回值，相當(dāng)于 print 函數(shù)。

第二節(jié) 用戶自定義函數(shù)

一、用戶自定義函數(shù)

二、調(diào)用標(biāo)量函數(shù)

1）語法結(jié)構(gòu)：

CREATE FUNCTION function_name(@parameter_name parameter_data_type) --（@參數(shù)名 參數(shù)的數(shù)據(jù)類型）

RETURNS date_type – 返回返回值的數(shù)據(jù)類型

[WITH ENCRYPTION] --如果指定了 encryption 則函數(shù)被加密

[AS]

BEGIN

function_body --函數(shù)體

RETURN 表達(dá)式;

END

2）舉例：

3）調(diào)用：

鏈接：https://blog.csdn.net/qq_16055765/article/details/88836520

原文鏈接：https://blog.csdn.net/adreammaker/article/details/68071214

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

一）單表格查詢

1）語法結(jié)構(gòu)：
CREATE FUNCTION function_name(@parameter_name parameter_data_type) --（@參數(shù)名 參數(shù)的數(shù)據(jù)類型）

RETURNS table --返回一張表

[AS]

RETURN( --返回下面空白部分所寫語句中查詢的表

)

特點(diǎn)：內(nèi)斂表值函數(shù)支持在WHERE子句中使用參數(shù)

2）舉例：

鏈接：https://blog.csdn.net/qq_16055765/article/details/88836520

原文鏈接：https://blog.csdn.net/adreammaker/article/details/68071214

二）多表格查詢：

1）語法：create function 函數(shù)名（參數(shù)）

? returns 表格變量名 table (表格變量定義)

as

begin SQL語句 （ 要有函數(shù)返回值 ） end （單表格沒有 begin SQL語句 end 直接 return ）

2）舉例：

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

1）語法結(jié)構(gòu)：DROP function 函數(shù)名（參數(shù) 參數(shù)類型）

第四節(jié) 觸發(fā)器

一、基本概念

? 1）觸發(fā)器：特殊的存儲過程，在對表中的數(shù)據(jù)進(jìn)行 UPDATE、INSTER、DELETE 操作時自動觸發(fā)執(zhí)行，常用于保證業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性，增強(qiáng)數(shù)據(jù)完整性約束能力。

? 2）SQL server支持的三種類型觸發(fā)器：DML（數(shù)據(jù)操縱語言：Insert、Update、Delete）、DDL（數(shù)據(jù)定義語言：Create、Alter、Drop）、登錄觸發(fā)器。

? 3）規(guī)則：

? ① 觸發(fā)器名必須符合標(biāo)識符規(guī)則，并且在數(shù)據(jù)庫中必須唯一。

? ② 觸發(fā)器所基于的對象。ON關(guān)鍵字后面指定在其上執(zhí)行觸發(fā)器的表，也可以稱為觸發(fā)器表。除了表以外，視圖上也可以定義觸發(fā)器。使用 WITH ENCRYPTION 選項(xiàng)可以對 CREATE TRIGGER 語句的文本進(jìn)行加密。 ③觸發(fā)器中使用的特殊表。執(zhí)行觸發(fā)器時，系統(tǒng)創(chuàng)建了兩個特殊的臨時表inserted表和deleted表。當(dāng)向表中插入數(shù)據(jù)時，INSERT觸發(fā)器觸發(fā)執(zhí)行，新的記錄插入到觸發(fā)器表和inserted表中。deleted表用于保存已從表中刪除的記錄，當(dāng)觸發(fā)一個DELETE觸發(fā)器時，被刪除的記錄存放到deleted表中。 ④ INSTEAD OF 表示前觸發(fā)器，在數(shù)據(jù)庫中最多只能有一個。

二、語法

三、刪除觸發(fā)器

語法格式：DROP TRIGGER 觸發(fā)器名 （必須在當(dāng)前數(shù)據(jù)庫進(jìn)行這些操作，即在哪個數(shù)據(jù)庫創(chuàng)建在哪刪除）

四、舉例

原文鏈接：https://blog.csdn.net/weixin_43107805/article/details/103091354

（1）創(chuàng)建一個表table1，其中只有一列a。在表上創(chuàng)建一個觸發(fā)器，每次插入操作時，將變量@str的值設(shè)為“TRIGGER IS WORKING”并顯示。

向table1中插入一行數(shù)據(jù)： INSERT INTO table1 VALUES(10) 執(zhí)行結(jié)果如下所示：

2）創(chuàng)建觸發(fā)器，當(dāng)向CJB表中插入一個學(xué)生的成績時，將XSB表中該學(xué)生的總學(xué)分加上添加的課程的學(xué)分。

（3）創(chuàng)建觸發(fā)器，當(dāng)修改XSB表中的學(xué)號時，同時也要將CJB表中的學(xué)號修改成相應(yīng)的學(xué)號（假設(shè)XSB表和CJB表之間沒有定義外鍵約束）

接著修改XSB表中的一行數(shù)據(jù)，并查看觸發(fā)器執(zhí)行結(jié)果：

（4）創(chuàng)建DELETE觸發(fā)器。在刪除XSB表中的一條學(xué)生記錄時將CJB表中該學(xué)生的相應(yīng)記錄也刪除。

第四節(jié) 游標(biāo)

一、概念

相當(dāng)于指針（數(shù)組的索引）——> 聯(lián)想到數(shù)據(jù)庫的鏈表和Java的數(shù)組的索引

定義：實(shí)現(xiàn)對 SELECT 結(jié)果集的逐行處理。

二、游標(biāo)的語法結(jié)構(gòu)

①聲明游標(biāo)： DECLARE 游標(biāo)名 CURSOR FOR 結(jié)果集

②打開游標(biāo)：OPEN 游標(biāo)名 INTO 結(jié)果集 (游標(biāo)的位置默認(rèn)在結(jié)果集的第一行)

③提取數(shù)據(jù)：FETCH 游標(biāo)名

④關(guān)閉游標(biāo)：CLOSE 游標(biāo)名 可以再次打開

⑤釋放游標(biāo)：DEALLOCATE 游標(biāo)名

三、舉例

1. 對Table_Customer表，定義一個查詢“長沙岳麓區(qū)”姓“王”的顧客姓名和郵箱的游標(biāo)，并輸出游標(biāo)結(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

END

④關(guān)閉：CLOSE Cname_cursor

⑤釋放：DEALLOCATE Cname_cursor

第八章 例題

1、在SQL Server 2008中，對于更新操作的觸發(fā)器，系統(tǒng)將產(chǎn)生2張邏輯工作表，其中存放更新前數(shù)據(jù)的邏輯工作表是（　　）。

答案：DELETE

2、刪除用戶自定義的函數(shù)使用（　　）語句來實(shí)現(xiàn)。

答案：DROP FUNCTION

3、設(shè)在數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計與實(shí)現(xiàn)過程中有下列活動：

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

Ⅱ. 定義事務(wù)隔離性級別

Ⅲ. 數(shù)字簽名

Ⅳ. 定義主碼

上述活動中，用于數(shù)據(jù)庫的完整性保護(hù)的是（　　）

A．僅Ⅰ和Ⅳ B．僅Ⅰ和Ⅱ

C．僅Ⅲ和Ⅳ D．僅Ⅱ和Ⅲ

答案：B

4、利用游標(biāo)機(jī)制可以實(shí)現(xiàn)對查詢結(jié)果集的逐行操作。下列關(guān)于SQL Server 2008中游標(biāo)的說法中，錯誤的是（　　）

A． 每個游標(biāo)都有一個當(dāng)前行指針，當(dāng)游標(biāo)打開后，當(dāng)前行指針自動指向結(jié)果集的第一行數(shù)據(jù)

B．如果在聲明游標(biāo)時未指定INSENSITIVE選項(xiàng)，則已提交的對基表的更新都會反映在后面的提取操作中

C．當(dāng)@@FETCH_STATUS=0時，表明游標(biāo)當(dāng)前行指針已經(jīng)移出了結(jié)果集范圍

D．關(guān)閉游標(biāo)之后，可以通過OPEN語句再次打開該游標(biāo)

答案：C

5、在SQL Server 2008中，用于判斷游標(biāo)數(shù)據(jù)提取狀態(tài)的全局變量是（　　）。

答案：@@FETCH_STATUS

6、設(shè)在SQL Server 2008某數(shù)據(jù)庫中有按如下格式定義的存儲過程首部：

CREATE PROC P1（存儲過程名）

@x int, @y int, @z int output AS …

請補(bǔ)全下列調(diào)用該存儲過程的語句。

DECLARE @S int

EXEC P1 20, 30, @S （　　）

答案：output

7、在SQL Server 2008中，設(shè)有教師表(教師號, 姓名, 所在部門號, 職稱) 和 部門表(部門號, 部門名, 高級職稱人數(shù))。請編寫滿足下列要求的后觸發(fā)型觸發(fā)器 (設(shè)觸發(fā)器名字為tri_zc)。

每當(dāng)在教師表中插入一名具有 高級職稱(“教授"或"副教授”) 的教師時，或者將非高級職稱教師的職稱更改為高級職稱時，均修改部門表中相應(yīng)部門的高級職稱人數(shù)。(假設(shè)一次操作只插入或更改一名教師的職稱) ）——>修改時觸發(fā)（INSERT）

8、設(shè)在SQL Server 2008某數(shù)據(jù)庫中有商品表和銷售表，兩個表的定義如下：

① CREATE TABLE 商品表(

商品號 char(10)PRIMARY KEY,

商品名 varchar(40),

類別 varchar(20),

進(jìn)貨單價 int )

② CREATE TABLE 銷售表(

商品號 char(10),

銷售時間 datetime,

銷售數(shù)量 int,

銷售單價 int,

PRIMARY KEY(商品號,銷售時間）)

下面是一個用戶定義的多語句表值函數(shù)，它接受類別作為輸入?yún)?shù)，返回該類別下的每種商品在2012年的銷售總利潤，并將結(jié)果按照銷售總利潤的降序輸出。請補(bǔ)全該函數(shù)定義代碼。

CREATE FUNCTION f_Profit (@lb char(10)) 【1】@ProfitTable【2】(

商品號 char(10),

總利潤 int )

AS

BEGIN

INSERT INTO @ProfitTable

【3】

【4】

END

【參考答案】

第一空：RETURNS

第二空：table

第三空：a SELECT a.商品號,SUM(銷售數(shù)量(銷售單價-進(jìn)貨單價)) AS總利潤 FROM 銷售表 a JOIN商品表b ON a. 商品號=b. 商品號 WHERE a.商品號 IN(SELECT 商品號 FROM 商品表 WHERE 類別=@lb) GROUP BY a. 商品號ORDER BY 總利潤 DESC*

第四空：RETURN@Rrofit Table

第九章 安全管理

第一節(jié) 安全控制概述

一、數(shù)據(jù)庫安全性與數(shù)據(jù)的完整性。

安全性：保護(hù)數(shù)據(jù)以防止不合法用戶故意造成破壞。

（確保用戶被允許做其想做的事情。）

完整性：保護(hù)數(shù)據(jù)以防止合法用戶無意中造成的破壞。

（確保用戶做的事情是正確的。）

二、安全控制模型

包括四階段：

①身份驗(yàn)證（用戶）

②操作權(quán)限控制（數(shù)據(jù)庫應(yīng)用程序與數(shù)據(jù)庫管理系統(tǒng)）

③文件操作控制（操作系統(tǒng)）

④加密存儲與冗余（數(shù)據(jù)庫）

三、授權(quán)和認(rèn)證

1）認(rèn)證是一種鑒定用戶身份的機(jī)制（相當(dāng)于開門并走進(jìn)去）。授權(quán)（相當(dāng)于對里面的東西進(jìn)行操作（賦予權(quán)限））是將合法訪問數(shù)據(jù)庫或數(shù)據(jù)庫對象的權(quán)限授予用戶的過程。包括認(rèn)證用戶對對象的訪問請求。

2）DBMS（數(shù)據(jù)庫管理系統(tǒng)）通常采用自主存取控制和強(qiáng)制存儲控制兩種方案來解決安全控制問題。

第二節(jié) 存取控制

一、自主存取控制

1）自主存取控制又稱自主安全模式， 通過SQL的**GRANT（授予權(quán)限）,REVOKE（收回權(quán)限）,DENY（拒絕權(quán)限）**語句來實(shí)現(xiàn)。

2）權(quán)限種類：維護(hù)權(quán)限 與 操作權(quán)限（語句權(quán)限與對象權(quán)限）

3）用戶分類：系統(tǒng)管理員（sa）、數(shù)據(jù)庫對象擁有者、普通用戶。

二、強(qiáng)制存取控制

1）目的：為避免自主存取模式下數(shù)據(jù)的“無意泄露”，采取強(qiáng)制存取控制。

2）DBMS將全部實(shí)體分為主體和客體兩大類。

? ?主體：系統(tǒng)活動實(shí)體，實(shí)際用戶和進(jìn)程。

? ?客體：被動實(shí)體，受主體操縱，包括文件、基本表、視圖。

3）敏感度標(biāo)記：DBMS指派，主體為許可證級別，客體為密級。分絕密、秘密、可信和公開等若干級別。

4）強(qiáng)制存取控制遵循如下規(guī)則：

? ?①僅當(dāng)主體許可證級別大于或等于客體密級，主體可以讀取相應(yīng)客體

? ?②僅當(dāng)主體許可證級別等于客體密級，該主體才能寫相應(yīng)客體。

第三節(jié) 審計跟蹤

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

2）審計跟蹤對數(shù)據(jù)安全有輔助作用。

第四節(jié) 統(tǒng)計數(shù)據(jù)庫的安全性

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

一、身份驗(yàn)證模式

1）Windows身份驗(yàn)證模式

SQL Server通過Windows操作系統(tǒng)獲得用戶信息，驗(yàn)證登錄名和密碼，一般推薦。

2）混合身份驗(yàn)證模式

? Windows授權(quán)用戶和SQL授權(quán)用戶可以登錄。

二、登錄帳戶

1）建立登錄賬戶

? CREATE LOGIN login_name（賬戶名）

2）修改登錄帳戶屬性

? ALTER LOGIN login_name

3）刪除登錄帳戶

? DROP LOGIN login_name

舉例“：

CREATE LOGIN SQL_User1 WITH PASSWORD=‘a(chǎn)1b2c3XY’-------設(shè)置用戶名和密碼

ALTER LOGIN SQL_User1 WITH PASSWORD=‘a(chǎn)4b5c6XY’

ALTER LOGIN SQL_User3 WITH NAME=NewUser

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

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

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

? 3）①建立數(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)限管理

? 1）登錄賬戶成為合法用戶后沒有任何操作權(quán)限，就需要為用戶授予數(shù)據(jù)庫數(shù)據(jù)及其對象的操作權(quán)限。

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

? SELECT、INSERT、UPDATE、DELETE、REFERENCES、EXECUTE

? 1）授權(quán)語句：

? 語法結(jié)構(gòu)：GRANT 對象權(quán)限 ON 對象(表、視圖、觸發(fā)器等等) TO （主體：數(shù)據(jù)庫用戶名或角色）[WITH GRANT OPTION]

? 實(shí)例：

? GRANT SELECT ON Addres TO abc

? GRANT EXECUTE ON OBJECT::HR.EI TO abc

? GRANT REFERENCES（EmployeeID）ON vEmp TO abc WITH GRANT OPTION

2）拒絕權(quán)限

? ①語法結(jié)構(gòu)：DENY 對象權(quán)限 ON 對象 TO （主體：數(shù)據(jù)庫用戶名或角色） [CASCADE] [AS主體]

? ②實(shí)例：

? DENY SELECT ON Addres TO abc

? DENY EXECUTE ON OBJECT::HR.EI TO abc

? DENY REFERENCES（EmployeeID）ON vEmp TO abc CASCADE

3）收權(quán)語句

? ①語法結(jié)構(gòu)：REVOKE 對象權(quán)限 ON 對象 TO （主體：數(shù)據(jù)庫用戶名或角色） [CASCADE] [AS角色]

? ②實(shí)例：

? REVOKE SELECT ON Addres TO abc

? REVOKE EXECUTE ON OBJECT::HR.EI TO abc

? REVOKE REFERENCES（EmployeeID）ON vEmp TO abc CASCADE

（2）語句級別的權(quán)限

? 1）語法結(jié)構(gòu)：CREATE DATABASE| PROCEDURE| TABLE| VIEW| FUNCTION | BACKUP DATABASE| LOG

? 2）實(shí)例：

? GRANT CREATE DATABASE TO user0

? GRANT CREATE DATABASE , CREATE VIEW TO user1,user2

? DENY CREATE VIEW TO user1

? REVOKE CREATE DATABASE FROM user0

五、角色

1）定義：一組具有相同權(quán)限的用戶就是角色。

2）SQL Server 2008中，角色分為預(yù)定義的系統(tǒng)角色和用戶角色兩種。

? ?系統(tǒng)角色又分為固定服務(wù)器角色（服務(wù)器級角色）和固定數(shù)據(jù)庫角色（數(shù)據(jù)庫級角色）。

? ?用戶角色均是數(shù)據(jù)庫級角色。

1、固定服務(wù)器角色

1）角色及權(quán)限

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

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

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

?Processadmin管理運(yùn)行進(jìn)程權(quán)限。

?Securtyadmin：專門管理登錄賬戶、讀取錯誤日志執(zhí)行CREATE DATABASE 權(quán)限的賬戶，便捷。

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

?Setupadmin：添加、刪除鏈接服務(wù)器。

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

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

2）例：

（1）為固定服務(wù)器角色添加成員

? Sp_addsrvrolemember

? EXEC Sp_addsrvrolemember ‘user1’（要賦予的用戶），‘sysadmin’（賦予用戶的角色）

（2）刪除固定服務(wù)器角成員

? Sp_dropsrvrolemember

? EXEC Sp_dropsrvrolemember ‘user1’（要賦予的用戶），‘sysadmin’（賦予用戶的角色）

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

1）角色及權(quán)限

定義在數(shù)據(jù)庫級別上，存在于每個數(shù)據(jù)庫中。用戶加入固定數(shù)據(jù)庫角色就具有數(shù)據(jù)庫角色權(quán)限。

?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)限，包括配置、維護(hù)、刪除數(shù)據(jù)庫。

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

2）例：

（1）為固定數(shù)據(jù)庫角色添加成員

? Sp_addrolemember

? EXEC Sp_addrolemember ‘Db_datareader’，‘SQL_User2’

（2）刪除固定服務(wù)器角成員

? Sp_droprolemember

? EXEC Sp_droprolemember ‘Db_datareader’,‘SQL_User2’

3、用戶定義的角色

?用戶定義的角色屬于數(shù)據(jù)庫一級。

?用戶定義的角色成員可以是用戶定義角色或數(shù)據(jù)庫用戶。

注意： 角色中的成員擁有的 權(quán)限=成員自身權(quán)限+所在角色權(quán)限（例如：既是qq成員又是管理員） 。但若某個權(quán)限在角色中被拒絕，則成員不再擁有。

4、創(chuàng)建用戶定義的角色

CREATE ROLE

實(shí)例： CREATE ROLE MathDept [AUTHORIZATION（授權(quán)） Software]

注意：為用戶定義角色授權(quán)（GRANT）、添加、刪除（DENY）用戶定義的角色中的成員與固定數(shù)據(jù)庫角色一致。

5、刪除用戶定義角色

DROP ROLE

實(shí)例：DROP ROLE MathDept

第六節(jié) Oracle的安全管理

Oracle的安全機(jī)制分為數(shù)據(jù)庫級的安全控制、表級、列級、行級的安全控制。

Oracle系統(tǒng)通常設(shè)置兩級安全管理員：全局級（相當(dāng)于中央）、場地級（相當(dāng)于地方 ）

一、用戶與資源管理

按權(quán)限大小劃分為DBA用戶（sys與system用戶，擁有全部系統(tǒng)特權(quán)）和普通用戶（DBA用戶或有相應(yīng)特權(quán)的用戶創(chuà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)限管理

（1）系統(tǒng)特權(quán)

? 三種默認(rèn)特權(quán)：

? ?Connect： 不能建立任何對象，可以查詢數(shù)據(jù)字典及訪問數(shù)據(jù)庫對象。

? ?Recource ：可建立數(shù)據(jù)庫對象（表、視圖、索引……）

? ?DBA ：擁有預(yù)定義的全部權(quán)限 。

（2）對象特權(quán)

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

? 實(shí)例：

? GRANT all ON dep TO user1 （授權(quán)語句）

? GRANT select(tno,tname,sal) ON teacher TO user2

第九章 例題

1．在SQL Server 2008某數(shù)據(jù)庫中，用戶U1是Role1角色中的成員。設(shè)在該數(shù)據(jù)庫中執(zhí)行了下述授權(quán)語句：

GRANT INSERT ON T TO U1

GRANT UPDATE, DELETE ON T TO Role1

GRANT SELECT ON T TO PUBLIC

DENY UPDATE ON T TO PUBLIC （刪除權(quán)限）

則用戶U1對T表具有的操作權(quán)限是（　　）。

A． INSERT

B． INSERT、UPDATE和DELETE

C．INSERT、SELECT和DELETE

D．INSERT和DELETE

答案：C

2．在SQL Server 2008中，設(shè)log1是SQL Server身份驗(yàn)證模式的數(shù)據(jù)庫服務(wù)器登錄帳戶。現(xiàn)要授予log1具有創(chuàng)建數(shù)據(jù)庫的權(quán)限，請補(bǔ)全下列語句：

EXEC sp_addsrvrolemember ′log1′, ′ （　　） ′

答案： dbcreator （服務(wù)器角色）

3．在SQL Server 2008的某數(shù)據(jù)庫中，設(shè)用戶U1同時是角色R1和角色R2中的成員。現(xiàn)已授予角色R1對表T具有SELECT、INSERT和UPDATE權(quán)限，授予角色R2對表T具有INSERT 和 DENY UPDATE權(quán)限，沒有對U1進(jìn)行其他授權(quán)，則U1對表T有權(quán)執(zhí)行的操作是（ ）。

A．SELECT和UPDATE

B．INSERT、UPDATE和SELECT

C．SELECT和INSERT

D．SELECT

答案：C

4．在SQL Server 2008中，設(shè)用戶 U1 是某數(shù)據(jù)庫 db_datawriter 角色中的成員，則U1在該數(shù)據(jù)庫中有權(quán)執(zhí)行的操作是（　　）。

A． SELECT（db_datareader 的權(quán)限）

B．SELECT和INSERT

C． INSERT、UPDATE和DELETE（db_datawriter：沒有查詢功能）

D．SELECT、INSERT、UPDATE和DELETE

答案：C

5．在SQL Server 2008中，只具有修改數(shù)據(jù)庫中全部用戶表數(shù)據(jù)權(quán)限的系統(tǒng)角色是 （　　） 。

答案： db_datawriter

6．設(shè)在SQL Server 2008某數(shù)據(jù)庫中，要設(shè)置不允許用戶U1獲得對表T數(shù)據(jù)的刪除權(quán)限，請補(bǔ)全實(shí)現(xiàn)該授權(quán)操作的SQL語句:

（　　）ON T TO U1;

數(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)限，包括配置、維護(hù)、刪除數(shù)據(jù)庫。

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

2）例：

（1）為固定數(shù)據(jù)庫角色添加成員

? Sp_addrolemember

? EXEC Sp_addrolemember ‘Db_datareader’，‘SQL_User2’

（2）刪除固定服務(wù)器角成員

? Sp_droprolemember

? EXEC Sp_droprolemember ‘Db_datareader’,‘SQL_User2’

3、用戶定義的角色

?用戶定義的角色屬于數(shù)據(jù)庫一級。

?用戶定義的角色成員可以是用戶定義角色或數(shù)據(jù)庫用戶。

注意： 角色中的成員擁有的 權(quán)限=成員自身權(quán)限+所在角色權(quán)限（例如：既是qq成員又是管理員） 。但若某個權(quán)限在角色中被拒絕，則成員不再擁有。

4、創(chuàng)建用戶定義的角色

CREATE ROLE

實(shí)例： CREATE ROLE MathDept [AUTHORIZATION（授權(quán)） Software]

注意：為用戶定義角色授權(quán)（GRANT）、添加、刪除（DENY）用戶定義的角色中的成員與固定數(shù)據(jù)庫角色一致。

5、刪除用戶定義角色

DROP ROLE

實(shí)例：DROP ROLE MathDept

第六節(jié) Oracle的安全管理

Oracle的安全機(jī)制分為數(shù)據(jù)庫級的安全控制、表級、列級、行級的安全控制。

Oracle系統(tǒng)通常設(shè)置兩級安全管理員：全局級（相當(dāng)于中央）、場地級（相當(dāng)于地方 ）

一、用戶與資源管理

按權(quán)限大小劃分為DBA用戶（sys與system用戶，擁有全部系統(tǒng)特權(quán)）和普通用戶（DBA用戶或有相應(yīng)特權(quán)的用戶創(chuà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)限管理

（1）系統(tǒng)特權(quán)

? 三種默認(rèn)特權(quán)：

? ?Connect： 不能建立任何對象，可以查詢數(shù)據(jù)字典及訪問數(shù)據(jù)庫對象。

? ?Recource ：可建立數(shù)據(jù)庫對象（表、視圖、索引……）

? ?DBA ：擁有預(yù)定義的全部權(quán)限 。

（2）對象特權(quán)

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

? 實(shí)例：

? GRANT all ON dep TO user1 （授權(quán)語句）

? GRANT select(tno,tname,sal) ON teacher TO user2

第九章 例題

1．在SQL Server 2008某數(shù)據(jù)庫中，用戶U1是Role1角色中的成員。設(shè)在該數(shù)據(jù)庫中執(zhí)行了下述授權(quán)語句：

GRANT INSERT ON T TO U1

GRANT UPDATE, DELETE ON T TO Role1

GRANT SELECT ON T TO PUBLIC

DENY UPDATE ON T TO PUBLIC （刪除權(quán)限）

則用戶U1對T表具有的操作權(quán)限是（　　）。

A． INSERT

B． INSERT、UPDATE和DELETE

C．INSERT、SELECT和DELETE

D．INSERT和DELETE

答案：C

2．在SQL Server 2008中，設(shè)log1是SQL Server身份驗(yàn)證模式的數(shù)據(jù)庫服務(wù)器登錄帳戶。現(xiàn)要授予log1具有創(chuàng)建數(shù)據(jù)庫的權(quán)限，請補(bǔ)全下列語句：

EXEC sp_addsrvrolemember ′log1′, ′ （　　） ′

答案： dbcreator （服務(wù)器角色）

3．在SQL Server 2008的某數(shù)據(jù)庫中，設(shè)用戶U1同時是角色R1和角色R2中的成員。現(xiàn)已授予角色R1對表T具有SELECT、INSERT和UPDATE權(quán)限，授予角色R2對表T具有INSERT 和 DENY UPDATE權(quán)限，沒有對U1進(jìn)行其他授權(quán)，則U1對表T有權(quán)執(zhí)行的操作是（ ）。

A．SELECT和UPDATE

B．INSERT、UPDATE和SELECT

C．SELECT和INSERT

D．SELECT

答案：C

4．在SQL Server 2008中，設(shè)用戶 U1 是某數(shù)據(jù)庫 db_datawriter 角色中的成員，則U1在該數(shù)據(jù)庫中有權(quán)執(zhí)行的操作是（　　）。

A． SELECT（db_datareader 的權(quán)限）

B．SELECT和INSERT

C． INSERT、UPDATE和DELETE（db_datawriter：沒有查詢功能）

D．SELECT、INSERT、UPDATE和DELETE

答案：C

5．在SQL Server 2008中，只具有修改數(shù)據(jù)庫中全部用戶表數(shù)據(jù)權(quán)限的系統(tǒng)角色是 （　　） 。

答案： db_datawriter

6．設(shè)在SQL Server 2008某數(shù)據(jù)庫中，要設(shè)置不允許用戶U1獲得對表T數(shù)據(jù)的刪除權(quán)限，請補(bǔ)全實(shí)現(xiàn)該授權(quán)操作的SQL語句:

（　　）ON T TO U1;

答案： DENY DELETE

總結(jié)

以上是生活随笔為你收集整理的计算机三级——数据库技术的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。