炫“庫”行動——超全資料庫疑難知識總結（解說+案例）建議收藏！

本文正在參與炫“庫”行動-人大金倉有獎征文：快來點擊活動鏈接參與投稿吧https://marketing.csdn.net/p/98bd30353e7cb998b6070a89e8b91edbhttps://marketing.csdn.net/p/98bd30353e7cb998b6070a89e8b91edb

目錄

一、資料庫分析與設計

二、UML與資料庫應用系統

三、高級查詢

四、資料庫后臺編程技術

五、資料庫及資料庫物件

六、安全管理

七、運行維護與優化

八、故障管理、備份與恢復

每文一語

話不多說，直接上干糧，各位C站的小伙伴，不要吃的太飽了喲，要記得消化，太撐了容易長肉肉喲！

一、資料庫分析與設計

1、資料庫應用系統的資料處理需求可以表示為事務規范，

事務規范： 事務規范包括了事務名稱、事務描述、事務所訪問的資料項、事務用戶，

2、改變資料庫應用系統體系結構、升級DBMS版本、修改程式以適應新需求都屬于資料庫應用系統升級作業；而創建新的索引屬于資料庫應用系統優化內容，

3、IDEF1X側重分析、抽象和概括應用領域中的資料需求，被稱為資料建模方法，而UML，DFD，IDEFO都屬于需求分析建模方法，

4、資料持久層負責保存和管理應用資料系統資料，而資料的完整性維護屬于資料持久層功能中的資料管理的內容，

5、多個小事務合并成大事務，會增加占有鎖的時間，可能會造成大量阻塞事務，甚至會產生死鎖，

6、資料庫應用系統的實施作業包括創建資料庫、裝載資料、撰寫與除錯應用程式、資料庫試運行，

7、關鍵字unique表示唯一索引，clustered表示聚集索引，

8、資料庫應用系統日常維護包括：備份與恢復、完整性維護、安全性維護、存盤空間管理和并發控制，

9、模式調整屬于資料庫應用系統的性能優化調整的范圍，

10、在資料流圖中，一個處理至少有一個輸入流和一個輸出流；一個存盤必須有流入的資料流和流出的資料流 ；父圖中描述過的資料流必須在相應的子圖中出現；一個資料流至少有一端是處理框，

11、通過資料庫概念設計和邏輯設計已經得到了規范化的關系模式，盡管關系資料庫模式是按照關系資料庫管理系統支持的結構進行資料的描述和組織的，然而，這些符號表示并不能解釋如何處理和存盤資料，資料庫物理設計的目的是將資料的邏輯描述轉換為實作技術規范，其目標是設計資料存盤方案，以便提供足夠好的性能并確保資料庫資料的完整性、安全性和可恢復性，

從功能角度資料庫應用系統可以劃分為四個層次來實作：
①表示層：用于顯示資料和接收用戶輸入的資料，（一般為Windows應用程式或Web應用程式)，
②業務邏輯層：是表示層和資料訪問層之間的橋梁，主要負責資料的傳遞和處理，
③資料訪問層：負責與DBMS系統進行互動，提取或存入應用系統所需的資料，
④資料持久層：負責保存和管理應用系統資料，

12、在IDEF1X中一個非確定聯系又稱為多對多關系或m:n 聯系，非標定型聯系 ：一對多

13、系統規劃與定義的內容包括：任務陳述、確定任務目標、確定系統范圍和邊界、確定用戶視圖，

14、IDEF0需求建模方法，描述功能活動示意圖中：

左邊的輸入箭頭表示完成活動需要的資料；

矩形框上方的控制箭頭描述了影響這個活動執行的事件或約束條件；

右邊的輸出箭頭說明由活動產生的結果及資訊，

下方進入的機制箭頭表示實施該活動的物理手段或完成活動需要的資源，

15、降低事務隔離級別可以提高事務的吞吐量，提高活鎖的可能性，降低發生死鎖、發生阻塞的可能性，

事務的隔離性是指多個并發事務同時訪問一個資料庫時，一個事物不應被另一個事物所干擾，即保證并發讀取資料的正確性，降低發生資料不一致的可能性，

16、在進行DBAS設計時，為了避免死鎖，可以考慮以下原則：
（1）按同一順序訪問資源，
（2）避免事務中的用戶互動，
（3）采用小事務模式，盡量縮短事務的長度，減少占有鎖的時間，
（4）盡量使用記錄級別的鎖（行鎖），少用表級別的鎖，
（5）使用系結連接，使同一應用程式所打開的兩個或多個連接可以相互合作，

17、完整性約束條件的作用物件分為列、元組和關系三種級別

其中對列的約束主要指對其值型別、范圍、精度、排序等的約束條件，對元組的約束主要指對記錄中各個屬性之間的聯系的約束條件，對關系的約束是指對若干紀錄間、關系集合上以及關系之間聯系的約束條件，

18、論證是否具備資料庫應用系統開發所需的人力資源，這屬于資料庫應用系統的操作可行性分析，

19、兩個物體集間存在多對多聯系，利用引入的第三個物體集，轉化后得到的兩個一對多的聯系，符合標定型聯系的定義，

20、關系模式個數=物體集數+多對多關系數

21、業務邏輯層概要設計

1、一個構件或模塊只負責完成一項任務，也就是單一責任原則，
2、各個構件均應具有獨立的功能，并最大限度地減少與其他構件功能重疊，
3、構件之間的介面應盡量簡單明確，
4、如果兩個構件間的關系比較復雜，應考慮進一步進行模塊劃分，
5、如果構件過于復雜，可以考慮將其細分，

22、關系模式的設計、規范化處理屬于資料庫邏輯結構設計的內容

二、UML與資料庫應用系統

1、狀態圖中只能有一個起始狀態，可以有多個結束狀態，

2、物件圖主要用于解釋類圖以及驗證所設計的類圖是否符合實際狀況，也可用于協助程式設計人

員理解復雜的類圖結構，

3、一般情況下，時間圖作為狀態機圖的輔助說明工具，一張時間圖通常會對應至少一張狀態圖，

4、角色是與系統進行互動的外部物體，可以是系統用戶也可以是其他系統或者硬體設備，

5、在UML中，用類圖來描述系統的靜態結構，而用順序圖和通信圖來表示系統的動態結構，順序圖和通信圖都描述互動，但是順序圖強調的是時間，通信圖強調的是空間，

6、UML中，橢圓表示用例，位于系統邊界內部，

7、根據事務-基本表交叉參考矩陣可以確定哪些事務對哪些表列執行了何種操作，基于該矩陣可以幫助資料庫設計人員建立合適的索引，

8、 資料庫安全性保護的主要保護方式有用戶身份鑒別、權限控制、視圖機制，

三、高級查詢

1、在SQLserver2008版本的查詢語法之中，我們的限制查詢top，應該放在*的前面，

2、使用FOR或者AFTER選項定義的觸發器為后觸發型觸發器，即只有在引發觸發器執行的陳述句中的操作都已成功執行，并且所有的約束檢查也成功完成后，才執行該觸發器，

3、使用INSTEAD OF選項定義的觸發器為前觸發型觸發器，在這種模式的觸發器中，指定執行觸發器而不是執行引發觸發器執行的SQL陳述句，從而替代觸發的操作，

4、創建磁區函式的sql陳述句是：

CREATE PARTITION FUNCTION partition_function_name(input_parameter_type)
AS RANGE [LEFT|RIGHT]
FOR VALUES([boundary_value[,…n]])
[;]

釆用了LEFT關鍵字，在指定磁區間隔值是由資料庫引擎按升序從左到右排列時，指定邊界值間隔處于左側磁區；而關鍵字RIGHT是指定邊界值間隔在右側磁區，

如果：磁區的邊界值分別是100和200，那么將被分為3個區間，分別是<=100、>=101 and <=200、>200，

四、資料庫后臺編程技術

1、使用FOR或者AFTER選項定義的觸發器為后觸發型觸發器，即只有在引發觸發器執行的陳述句中的操作都已成功執行，并且所有的約束檢查也成功完成后，才執行該觸發器，

2、使用INSTEAD OF選項定義的觸發器為前觸發型觸發器，在這種模式的觸發器中，指定執行觸發器而不是執行引發觸發器執行的SQL陳述句，從而替代觸發的操作，

3、 在對游標資料進行提取的程序中，可以使用@@FETCH_STATUS全域變數判斷資料提取的狀態，由于@@FETCH_STATUS對于在一個連接上的所有游標是全域性的，不管是對哪個游標，只要執行一次FETCH陳述句，系統都會對@@FETCH_STATUS賦一次值，以表明該FETCH陳述句的執行情況，

4、對于前觸發器，在一個表上針對同一個資料操作只能定義一個前觸發器；對于后觸發器，可以在同一種操作上建立多個觸發器，

5、

NEXT關鍵字的作用是回傳緊跟在當前行之后的資料行，并且當前行遞增為結果行，

PRIOR關鍵字回傳緊臨當前行前面的資料行，并且當前行遞減為結果行，

ABSOLUTE n和RELATIVE n關鍵字用于指定特定行，

FIRST關鍵字回傳游標中的第一行并將其作為當前行，

LAST關鍵字回傳游標中的最后一行并將其作為當前行，

6、執行有多個輸入引數的存盤程序時，引數的傳遞方式有兩種：
①按引數位置傳遞值
按引數位置傳遞值指執行存盤程序的EXEC陳述句中的實參的排列順序必須與定義存盤程序時定義的引數的順序一致，
②按引數名傳遞值
按引數名傳遞值指的是執行存盤程序的EXEC陳述句中要指明定義存盤程序時指定的引數的名字以及此引數的值，而不關心引數的定義順序，

7、資料庫創建后可以手工縮小資料檔案和日志檔案的空間

8、db_datawriter角色屬于資料庫級角色，具有插入、洗掉和更改資料庫中所有用戶資料的權限，

8、在T1表上定義了一個由插入或洗掉操作引發的后觸發型觸發器

CREATE TRIGGER tri_1
ON T1 FOR INSERT,DELETE
AS ……

五、資料庫及資料庫物件

1、model資料庫用作SQL Server實體上創建的所有資料庫的模板，對model資料庫進行的修改（如資料庫大小、排序規則、恢復模式和其他資料庫選項）將應用于以后創建的所有用戶資料庫，當用戶創建一個資料庫時，系統自動將model資料庫中的全部內容復制到新建資料庫中，

2、日志檔案不包括在檔案組內，日志檔案和資料檔案是分開管理的，

3、 tempdb是臨時資料庫，用于保存臨時物件或中間結果集，并為資料的排序等操作提供一個臨時作業空間，每次啟動SQL Server時都會重新創建tempdb資料庫，

4、唯一索引的作用是保證索引鍵值的不重復，在多列唯一索引的情況下，可以確保索引列中每個值的組合都是唯一的，唯一索引和非聚集索引之間沒有限定關系，

5、一個表上只能有一個聚集索引，在表上建立聚集索引會引起表中資料的物理排序、在一個表上可以同時有聚集索引和非聚集索引，

6、唯一索引既可以是聚集的也可以是非聚集的

7、一個表或視圖只允許同時有一個聚集索引，

六、安全管理

1、 guest是SQL Server中一個特殊的資料庫用戶，

2、GRANT UPDATE ON T1 TO U1陳述句的作用是用戶U1具有對表T1各列資料進行修改的權利，在SQL Server中，擁有UPDATE權限的用戶并不意味著一定擁有SELECT權限，

3、Oracle的安全控制機制可分為資料庫級的安全控制、表級、行級和列級的安全控制，資料庫級的安全性通過用戶身份認證和授予用戶相應系統權限來保證，而表級、行級、列級的安全性是通過授予或回收物件特權保證的，Oracle資料庫中的用戶按其操作權限大小可分DBA用戶和普通用戶，

4、REVOKE SELECT, INSERT ON T1 FROM U1 ：要識訓用戶U1對T1表的查詢和插入權限

5、SQL Server 2008的身份驗證模式有"Windows身份驗證模式"和"混合身份驗證模式"兩種，混合身份驗證模式是指SQL Server允許Windows授權用戶和SQL授權用戶登錄到SQL Server資料庫服務器，

6、 資料庫級角色，db_datereader具有查詢資料庫中所有用戶資料的權限，

七、運行維護與優化

1、資料庫重組是指按照系統設計要求對資料庫存盤空間進行全面調整，如調整磁盤磁區方法和存盤空間、重新安排資料的存盤、整理回收碎塊等，以提高資料庫性能，

2、資料庫的重構是指由于資料庫應用環境的不斷變化，增加了新的應用或新的物體，取消了某些應用，有的物體與物體間的聯系也發生了變化等，使得原有的資料庫設計不能滿足新的需求，此時需要調整資料庫的模式和內模式，

3、資料庫的重組并不修改資料庫原有設計的邏輯結構和物理結構，而資料庫的重構則不同，它可以部分修改資料庫的模式和內模式，

4、增加資料庫記憶體數量可以在一定程度上降低資料庫服務器IO操作

5、 派生性冗余列是指表中增加的列由表中的一些資料項經過計算生成，它的作用是在查詢時減少連接操作，避免使用聚合函式，由于增加了派生性冗余欄位，破壞了規范化規則，

6、 除了洗掉或創建索引，以及非聚簇索引和聚簇索引的相互轉換外，通過重建索引可以改進系統的性能，

7、 水平分割是根據資料行的使用特點進行分割，分割之后所得的所有表的結構都相同，而存盤的資料不同，

水平分割會給應用增加復雜度，特別在查詢所有資料時需要Union（并）操作；大部分情況是區域使用，只有很少情況才全域查詢時適合使用此方法，

而垂直分割是根據列的特點進行分割，分割之后所得的所有表中除了都含有主碼列外其余列都不同，其查詢時會減少I/O次數，但其缺點是查詢所有資料時需要Join (連接）操作，

8、在資料庫設計階段，主要強調的是高效率利用存盤空間，減少資料的冗余，減少資料的不一致，這個程序也就是規范化的程序，

規范化關系解決了資料維護的例外，并使冗余最小化，然而，可能會出現資料處理性能下降的問題，

在大多數情況下，資料庫系統需要考慮的第二個目標是高效率的資料處理，反規范化處理，如增加派生冗余列，增加冗余列，重新組表、分割表和新增匯總表可以有效地提高查詢效率，

9、 資料庫運行維護作業主要包括資料庫的轉儲和恢復；資料庫的安全性和完整性控制；資料庫性能的監控分析和改進；資料庫的重組和重構，

10、資料庫設計程序中，提高資料庫安全性的同時不一定能提高資料庫性能，資料庫應用系統正式投入運行后，開發團隊還要參與此應用后期作業，如資料庫的優化需要開發團隊的配合，

資料庫維護的主要作業內容是日常維護、系統監控與分析、系統性能優化調整、系統進化（升級）等作業，資料庫資料備份只是屬于資料庫日常維護的一小部分，

11、維護作業主要包括：資料庫的轉儲和恢復；資料庫的安全性和完整性控制；資料庫性能的監控分析和改進；資料庫的重組和重構，

12、將資料檔案和索引檔案放置在同一磁盤上不利于提高查詢效率，將資料檔案與日志檔案放在不同的磁盤有利于查詢效率，

13、當資料庫表更新大量資料后，洗掉并重建索引可以提高查詢速度，

14、SQL Server提供了一套綜合的工具，用于監視SQLServer中的事件和優化物理資料庫的設計，

15、集中式資料庫的查詢處理中，主要代價是CPU代價和I/O代價，在分布式資料庫系統中，由于資料分布在多個不同的場地上，需要在各站點中傳輸資料，所以通信代價尤為關鍵，

16、水平分割是對一張表在水平方向分割，分割后得到的表結構相同，查詢時需要使用UNION運算；

垂直分割則是對一張表在垂直方向分隔，分割后每張表中除主碼列外，其他列均不同，查詢時需要使用JOIN運算，

分割的目的是提高性能

八、故障管理、備份與恢復

1、在制定備份策略時，除考慮使用備份恢復時丟失的資料量外，還需考慮資料庫備份所需的時間，

2、檢查點技術大幅度減少了資料庫完全恢復時所必須執行的日志部分，

3、靜態轉儲雖然保證了資料的有效性，但是卻是以降低資料庫的可用性為代價；動態轉出雖然提高了資料庫的可用性，但資料的有效性卻可能得不到保障，

4、差量轉儲和增量轉儲相比，速度慢，占用空間較多，但恢復速度比增量轉儲快，

5、創建備份設備的 T-SQL存盤程序為sp_addumpdevice，

6、事務日志備份僅用于完整恢復模式和大容量日志恢復模式，它并不備份資料庫本身，只備份日志記錄，而且只備份從上次備份之后到當前備份時間發生變化的日志內容，

但是不允許對大容量操作日志備份進行時點恢復，結尾日志備份在出現故障時進行，用于防止丟失資料，可以包含純日志記錄或者大容量操作日志記錄，

7、日志檔案記錄每個事務對資料庫的修改操作，主要用來實作資料恢復，

8、大容量日志恢復模式只對大容量操作進行最小記錄，一般只作為完整恢復模式的附加模式，

9、在SQL Server 2008系統資料庫中，只有tempdb資料庫不需要備份，其他的都需要備份，

10、事務日志備份并不備份資料庫本身，它只備份日志記錄，而且只備份從上次備份之后到當前備份時間發生變化的日志內容，

11、簡單恢復模式不支持事務日志備份，

12、對于大型資料庫，差異備份的間隔通常比完整資料庫備份的時間更短，這可降低資料丟失風險，

13、比如對master資料庫的備份，通常在執行了創建、修改或洗掉資料庫的操作，或是更改了服務器或資料庫的配置、建立或更改登錄賬戶等操作后，都應對它進行備份，

14、簡單恢復模式可最大限度的減少事務日志的管理開銷，因為這種模式不備份事務日志，

15、事務日志備份僅用于完整恢復模式和大容量日志恢復模式，不備份資料庫本身，只備份日志記錄，而且只備份從上次備份之后到當前備份時間發生變化的日志內容，

1、聚集函式的并行化計算可以釆取"先分后合"的方法，

2、位置獨立性、資料分片獨立性和資料復制獨立性是使分布式資料庫具有分布式透明性的主要因素，

分布式資料庫最基本特征是本地自治、非集中式管理及高可用性，

3、SaaS，即Software as a Service，中文意思是軟體即服務，

每文一語

準備是機會的前提