Database之SQLSever：SQLSever資料庫管理人員國家職業資格證書中級、進階考試知識點總結

SQLSever資料庫管理人員中級、進階考試知識點總結

基礎概念

        SQL語言具有資料定義語句、資料操縱語句、資料控制語句的功能。互動式SQL、嵌入式SQL兩種使用方式。其中between and表示包括兩邊參數。

資料操作語言DML：增删改查

資料控制語言DCL：

資料模型的三要素：資料結構、資料操作、資料的限制條件。資料模型是現實世界資料特征的抽象。

資料庫使用者與伺服器角色、資料庫角色的關系：如果資料庫使用者User1是固定伺服器角色sysadmin(若db_owner)的成員，并建立表T1，則T1屬于dbo，并dbo.T1(則User1.T1)進行限定；

觸發器：觸發器的特點與表相關聯、自動激活、不能直接調用和作為事務的一部分；觸發器不需要人工激活而是自動激活；

資料庫中的封鎖機制：資料庫中的封鎖機制是并發控制的主要方法；對并發操作若不加以控制，可能會帶來資料的死鎖問題；資料庫防止死鎖的方法是禁止兩個使用者同時操作資料庫；預防死鎖通常有一次封鎖法和順序封鎖法；死鎖的診斷和解除方法是逾時法和等待圖法。

安全級别：B1級強制存取控制(MAC)；C1級是非常初級的自主存取，C2是安全産品的最低檔次(DAC)，

資料庫安全性控制常用的方法和技術：實作資料庫安全性控制常用的方法和技術有使用者辨別和鑒别、存取控制、視圖機制、審計、資料加密，還有授權與回收。

流式檔案：構成檔案的基本機關是字元，這一類檔案稱為流式檔案。檔案系統的多級目錄結構是樹形結構。

資料庫的完整性和實體完整性：資料庫的完整性是指資料的正确性和相容性；實體完整性要求主屬性不能取空值，可以通過定阿義主碼保證；實體完整性和參照完整性屬于靜态關系限制；

DBMS：資料庫的核心是資料庫管理系統DBMS(DB+User)；從子產品來看，DBMS由查詢處理器和存儲管理器兩大部分組成；對資料庫經常性維護工作DBA；RDBMS查詢處理的4個階段查詢分析、查詢檢查、查詢優化(包括代數優化、實體優化)、查詢執行。為了提高效率，關系資料庫必須進行查詢優化處理。

(1)、基于DBMS的資料庫完整性設計大體分為需求分析階段、概念結構設計階段(最常用的資料模型是實體聯系模型ER圖、橢圓代表屬性類型)、邏輯結構設計階段(如将ER圖轉為關系模式模型、選擇某種索引方法等)。

(2)、事務是DBMS進行的基本工作機關；事務的原子性指事務中包括所有的操作要麼都做、要麼都不做。事務日志的用途是資料恢複。

資料庫系統的資料獨立性：指實體獨立性、邏輯獨立性。資料庫系統達到了資料獨立性是因為采用了三級模式結構。在資料庫三級模式間引入二級映像的主要作用是提高資料與程式的獨立性。

常考知識點

注冊一個SQLServer資料庫服務時，需要指定其各方面的屬性包括所要連接配接的資料庫伺服器名稱、網絡協定。

索引是為了加速對資料表中資料行的檢索而建立的一種分散的存儲結構。在select語句中使用group by 字句(或外鍵限制)時，SQLServer不會自動建立索引；但是建立unique限制(或主鍵限制)時，SQLServer會自動建立索引。

安全控制技術的審計，分為使用者級審計和系統級審計。

資料加密的方法有替換方法和置換方法。

資料庫中隻存放視圖的定義。

資料庫鏡像可用于進行資料庫恢複或并發操作。鏡像既能提高安全性也能提高速度。

資料庫鏡像需要兩個資料庫，主體資料庫、鏡像資料庫，兩個資料庫駐留在不同的伺服器上，可同時進行不同的事務處理。

%取餘，/取商

資料庫的三種模型

1、網狀模型資料庫

      用有向圖表示實體和實體之間的聯系的資料結構模型稱為網狀資料模型。 可以存在兩個或者多個節點(即一個以上的結點)沒有父節點、允許單個節點存在多于一個父節點；

2、層次模型資料庫

      資料庫中有且僅有一個記錄型無雙親，稱為根節點。其他記錄型有且僅有一個雙親。在層次模型中從一個節點到其雙親的映射是惟一的，是以對每一個記錄型（除根節點外）隻需要指出它的雙親，就可以表示出層次模型的整體結構。

3、關系模型資料庫

      網狀資料庫和層次資料庫已經很好地解決了資料的集中和共享問題，但是在資料獨立性和抽象級别上仍有很大欠缺。使用者在對這兩種資料庫進行存取時，仍然需要明确資料的存儲結構，指出存取路徑。而後來出現的關系資料庫較好地解決了這些問題。關系資料庫理論出現于60年代末到70年代初。關系資料模型提供了關系操作的特點和功能要求，但不對DBMS的語言給出具體的文法要求。對關系資料庫的操作是高度非過程化的，使用者不需要指出特殊的存取路徑，路徑的選擇由DBMS的優化機制來完成。        1970年，IBM的研究員E.F.Codd博士發表《大型共享資料銀行的關系模型》一文提出了關系模型的概念，論述了範式理論和衡量關系系統的12條标準，如定義了某些關系代數運算，研究了資料的函數相關，定義了關系的第三範式，進而開創了資料庫的關系方法和資料規範化理論的研究，他為此獲得了1981年的圖靈獎。

(1)、同一個關系模型的任意兩個元祖值不能全同。

(2)、關系模型中的候選碼，若關系中的某一屬性組的值能唯一辨別一個元組,則稱該屬性組為候選碼。外碼不一定要與相應的主碼同名。

(3)、關系模型資料庫管理系統能實作的專門的關系運算包括選擇、投影、連接配接。

(4)、關系是關系模型的一個執行個體。

(5)、關系模型的參照完整性規則，要求關系中不允許引用不存在的元祖，即元祖非空。

(6)、關系資料庫規範化理論主要解決是如何構造處一個合适的資料邏輯結構。

(7)、關系資料庫設計中的陷阱是指資訊重複和不能表示特定的資訊。

(8)、關系演算是用謂詞來表達查詢要求的方式。

傳統的集合運算、專門的關系運算

傳統的集合運算

傳統的集合運算是二目運算，包括并、交、差、廣義笛卡爾積四種運算，其中笛卡兒積用時最長。

專門的關系運算

專門的關系運算(Specific relation operations)，包括選擇、投影、連接配接、除等。

σF(R) = {t|t∈R ∧ F(t)='真'}

ΠA(R) = { t[A] | t∈R }

R— (R—S)

R÷S={t_r [X]|t_r∈R?π_r （S）?Y_x}

在資料庫三級模式

模式：在資料庫三級模式(是以資料獨立性)結構中，對資料庫中全部的資料邏輯結構、特征進行描述。

内模式：最接近于實體儲存設備一級的結構，是對資料庫實體存儲方式的描述。

外模式：模式的邏輯子集通常稱為外模式。對資料庫不同使用者能夠看得見和使用的局部資料的邏輯結構和特征的描述。

(1)、要保證資料庫的資料邏輯獨立性：需要修改模式與外模式的映射。

資料依賴

        指在程式引用資料之前處理過的資料的狀态；在編譯學中，資料依賴是資料分析的一部分；資料依賴有三種即流依賴、反依賴、輸出依賴。

在關系模式R中，函數依賴X→Y的語義是在R的每一個關系中，若兩個元祖的X值相等，則Y值也相等。

多值依賴毛病在于插入異常、删除異常、資料備援太大。

資料庫範式1NF、2NF、3NF、BCNF、4NF、5NF

資料庫範式分為1NF2NF3NFBCNF4NF5NF，因為從1NF開始向右條件是要消除函數，故第一個最内容最大。

1、模式分解的算法包括合成法(3NF，保持函數依賴)、分解法(BCNF無損；4NF無損)、

範式NF：“符合某一種級别的關系模式的集合，表示一個關系内部各屬性之間的聯系的合理化程度”，實際上粗略地了解為一張資料表的表結構所符合的某種設計标準的級别，但資料的規範化不會提高資料庫的安全性。就像家裡裝修買建材，最環保的是E0級，其次是E1級，還有E2級等等。符合高一級範式的設計，必定符合低一級範式，例如符合2NF的關系模式，必定符合1NF。

1NF：所有的屬性均有原子性 ；符合1NF的關系中的每個屬性都不可再分。 1NF是所有關系型資料庫的最基本要求，你在關系型資料庫管理系統（RDBMS），例如SQL Server，Oracle，MySQL中建立資料表的時候，如果資料表的設計不符合這個最基本的要求，那麼操作一定是不能成功的。也就是說，隻要在RDBMS中已經存在的資料表，一定是符合1NF的。 但是僅僅符合1NF的設計，仍然會存在資料備援過大，插入異常，删除異常，修改異常的問題

2NF：合更高一級的範式（2NF），這就是所謂的“規範化”。滿足1NF的前提上，如果依賴于主鍵，則需要依賴于所有主鍵，不能存在依賴部分主鍵的情況 。1NF→2NF，，消除了非主屬性對于碼的部分函數依賴。根據2NF的定義，判斷的依據實際上就是看資料表中是否存在非主屬性對于碼的部分函數依賴。若存在，則資料表最高隻符合1NF的要求，若不存在，則符合2NF的要求。

3NF：滿足2NF，非主鍵外的所有字段必須互不依賴，非鍵屬性不能依賴于非鍵屬性。2NF→3NF，消除了非主屬性對于碼的傳遞函數依賴。某些特殊情況下，即使關系模式符合 3NF 的要求，仍然存在着插入異常，修改異常與删除異常的問題，仍然不是 ”好“ 的設計。

(1)、當關系模式R中的屬性全部是主屬性，則R的最高範式必定是3NF。

(2)、若函數依賴集中所有候選關鍵字都是決定因素，則R的最高範式是4NF。

BCNF範式： 所有屬性（包括非鍵屬性與鍵鼠性）不能依賴于非鍵屬性。3NF→BCNF，部分函數依賴與傳遞函數依賴。在函數依賴中，已消除了插入和删除異常。

4NF：删除了不必要的資料結構：多值依賴。要求分解具有無損連接配接性，可以達到4NF。

多種資料庫恢複模式：簡單恢複、批日志恢複、完全恢複

SQL Server2005支援多種資料庫恢複模式，如簡單恢複、批日志恢複、完全恢複，不包含增量恢複。

簡單恢複：資料恢複性能最好的模式。僅使用資料庫備份将資料恢複到上一次備份的時刻模式；僅使用了資料庫備份或差異備份，而不涉及事物日志備份的模式。

批日志恢複：批量日志恢複模式類似完整恢複模式，都預期會有大批量的資料修改操作(例如，建立索引，SELECT INTO，INSERT SELECT，BCP，BULKINSERT)，在這種情況下可以最小化日志記錄量。

完全恢複：通過使用資料庫備份和事物日志備份将資料恢複到發生失敗的時刻，進而不造成資料丢失的模式。

啟動SQLServer資料庫服務時，可設定一些啟動選項

參考文章：

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms190737(v=sql.105)?redirectedfrom=MSDN

-n       記憶no

不要使用 Windows 應用程式日志來記錄 SQL Server 事件。如果使用 -n 啟動 SQL Server 執行個體，則建議您同時使用 -e 啟動選項。否則，将不會記錄 SQL Server 事件。

-c       記憶指令command

縮短從指令提示符啟動 SQL Server 時的啟動時間。通常，SQL Server 資料庫引擎通過調用服務控制管理器作為服務啟動。由于從指令提示符啟動時，SQL Server 資料庫引擎不作為服務啟動，是以請使用 -c 跳過此步驟。

-m       記憶Single user mode

在單使用者模式下啟動 SQL Server 執行個體。在單使用者模式下啟動 SQL Server 執行個體時，隻能連接配接一個使用者，并且不啟動 CHECKPOINT 程序。CHECKPOINT 保證将已完成的事務定期從磁盤緩存寫入資料庫裝置。

-s       記憶Naming instance

用于啟動 SQL Server 的命名執行個體。如果 -s 參數未設定，則将嘗試啟動預設執行個體。必須在指令提示符下切換到執行個體相應的 BINN 目錄，然後才能啟動 sqlservr.exe

-e       記憶error

error_log_path，錯誤日志檔案的完全限定路徑

-I        記憶maser主人即我I

master_log_path，master 資料庫日志檔案的完全限定路徑

bcp 實用工具

      bcp 實用工具可以在 Microsoft SQL Server 執行個體和使用者指定格式的資料檔案間大容量複制資料。 使用 bcp 實用工具可以将大量新行導入 SQL Server 表，或将表資料導出到資料檔案。

bcp用法: bcp {dbtable | query} {in | out | queryout | format} 資料檔案

 [-m 最大錯誤數]             [-f 格式化檔案]         [-e 錯誤檔案]

 [-F 首行]                   [-L 末行]             [-b 批大小]

 [-n 本機類型]               [-c 字元類型]         [-w 寬字元類型]

 [-N 将非文本保持為本機類型] [-V 檔案格式版本]     [-q 帶引号的辨別符]

 [-C 代碼頁說明符]           [-t 字段終止符]       [-r 行終止符]

 [-i 輸入檔案]               [-o 輸出檔案]         [-a 資料包大小]

 [-S 伺服器名稱]             [-U 使用者名]           [-P 密碼]

 [-T 可信連接配接]               [-v 版本]             [-R 允許使用區域設定]

 [-k 保留白值]               [-E 保留辨別值]

 [-h"加載提示"]              [-x 生成xml 格式化檔案]