SQLserver基礎(chǔ)理論學(xué)習(xí)

1、SQL server基礎(chǔ)理論學(xué)習(xí) ：信息體系結(jié)構(gòu)原則 1、信息體系結(jié)構(gòu)原則1.1信息管理的3個主要方面：數(shù)據(jù)庫設(shè)計和開發(fā)、企業(yè)數(shù)據(jù)中心的管理、商業(yè)智能分析1.2信息體系結(jié)構(gòu)原則首先必須有詳細(xì)的信息清單，包括信息的位置、來源、敏感性、當(dāng)前和未來信息的價值以及當(dāng)前的擁有者1.3要確保數(shù)據(jù)能夠在未來使用，必須降低當(dāng)前設(shè)計的耦合程度。1.4數(shù)據(jù)存儲目標(biāo)1.4.1簡單性要對復(fù)雜性感到敬畏，應(yīng)不斷調(diào)整設(shè)計，直到整個設(shè)計小組都認(rèn)為它是能夠滿足需求的最簡單解決方案。字典中“困難”的同義詞包含“復(fù)雜”是有道理的。簡單的解決方案通常是優(yōu)雅而容易理解的。（應(yīng)讓事情盡可能簡單，且不能再簡單愛因斯坦）做到簡單性的要求：

2、（1）對需求有全面認(rèn)識（2）掌握大量可供參考的模式和解決方案（3）對技術(shù)規(guī)則和行業(yè)術(shù)語有全面認(rèn)識（4）創(chuàng)造性掌握行業(yè)知識，直到何時及如何改善技術(shù)系統(tǒng)（5）對用于實現(xiàn)設(shè)計的工具和設(shè)備以及解決方案的運行環(huán)境有深入認(rèn)識（6）設(shè)計人員互相信任、分享想法而不考慮個人得失（7）不斷優(yōu)化設(shè)計，直到設(shè)計出優(yōu)雅而簡單的設(shè)計方案（8）對復(fù)雜性抱有健康的敬畏心態(tài)1.4.2有用性有用性包括三點：（1）滿足組織需求（2）適應(yīng)性：設(shè)計與數(shù)據(jù)存儲的范圍和用途相稱數(shù)據(jù)存儲設(shè)計模型包括：關(guān)系DBMS、對象數(shù)據(jù)庫、對象/關(guān)系數(shù)據(jù)庫、通用模式、數(shù)據(jù)倉儲數(shù)據(jù)庫的范圍包含數(shù)據(jù)在組織內(nèi)的使用度（個人、部門、營業(yè)單位、公司、企業(yè)）以及數(shù)據(jù)

3、的持續(xù)性（當(dāng)前、當(dāng)年、永遠(yuǎn)）（3）數(shù)據(jù)格式可供應(yīng)用程序使用以及易于提取數(shù)據(jù)存儲配置包括5種類型：（1）主數(shù)據(jù)存儲：運營數(shù)據(jù)庫或聯(lián)機(jī)數(shù)據(jù)處理數(shù)據(jù)庫，用于收集第一代交易數(shù)據(jù)。在組織中必不可少，且是唯一的。主數(shù)據(jù)庫存儲需要在方便數(shù)據(jù)檢索和數(shù)據(jù)更新之間取得平衡。每個運營單位都可能有個主數(shù)據(jù)存儲。（2）緩存數(shù)據(jù)存儲：可選，是主數(shù)據(jù)存儲的只讀拷貝，用于提供數(shù)據(jù)，減輕主數(shù)據(jù)庫的負(fù)載。優(yōu)化它，可提供數(shù)據(jù)庫檢索性能。（3）引用數(shù)據(jù)存儲：主要為只讀，存儲組織中較少變更的數(shù)據(jù)。類似圖書館的目錄區(qū)。優(yōu)化它，也可很高檢索性能。（4）數(shù)據(jù)倉庫：從企業(yè)多個主數(shù)據(jù)存儲收集大量數(shù)據(jù)，并使用提取、轉(zhuǎn)化和加載過程，將格式和架構(gòu)轉(zhuǎn)為

4、通用，簡化數(shù)據(jù)檢索。同時存儲歷史記錄，減輕運營數(shù)據(jù)存儲的負(fù)載。組織必須有一個通用的數(shù)據(jù)倉庫。主要任務(wù)是數(shù)據(jù)檢索和分析。（5）數(shù)據(jù)集市：數(shù)據(jù)倉庫的子集，包含經(jīng)過預(yù)先匯總的數(shù)據(jù)。主數(shù)據(jù)存儲的設(shè)計方法（1）關(guān)系DBMS傳統(tǒng)的數(shù)據(jù)庫，它講類似或相關(guān)的數(shù)據(jù)放到一個表中。適合滿足穩(wěn)定的數(shù)據(jù)架構(gòu)需求，這種需求只包含少量的is_a關(guān)系。（2）面向?qū)ο蟮腄BMSOO DBMS必須存儲對象，因此判斷OO DBMS優(yōu)劣的一個重要標(biāo)準(zhǔn)就是它能否存儲復(fù)雜的對象，如XML文件、.net文件OO DBMS非常適合這樣的程序：架構(gòu)可能發(fā)生重大變化、包括復(fù)雜數(shù)據(jù)類型、類之間涉及多個is-a關(guān)系、包括復(fù)雜的多關(guān)聯(lián)以及要求將數(shù)據(jù)同

5、應(yīng)用程序輕松關(guān)聯(lián)起來。有三種主要的面向?qū)ο蟮臄?shù)據(jù)庫A對象持久化數(shù)據(jù)存儲（OP DBMS）:相當(dāng)于一個存儲對象的狀態(tài)倉庫B面向?qū)ο蟮臄?shù)據(jù)存儲（OO DBMS）C對象/關(guān)系數(shù)據(jù)存儲（O/R DBMS）（3）通用模式DBMS也稱為動態(tài)鉆石模式，有時被用作關(guān)系數(shù)據(jù)庫產(chǎn)品中的面向?qū)ο蟮臄?shù)據(jù)庫設(shè)計。在應(yīng)用程序需要動態(tài)屬性時，這種設(shè)計非常有用。1.4.3完整性數(shù)據(jù)實際上是實體和屬性，因此數(shù)據(jù)完整性由實體完整性和域完整性組成。（1）實體完整性：本質(zhì)就是規(guī)范化（2）域完整新：保證屬性只取有效值，包括為空性。（3）引用完整性：外鍵域的完整性（4）用戶定義完整性（5）事務(wù)完整性：事務(wù)是一個邏輯工作單元，如插入100

6、行。數(shù)據(jù)庫產(chǎn)品的質(zhì)量是根據(jù)其事務(wù)對ACID屬性的遵循程度衡量的。A（atomicity）原子性：事務(wù)必須是原子的，這意味著在事務(wù)結(jié)束時，耀目所有操作成功，要么都失敗。C(consistency)一致性：這意味著在事務(wù)執(zhí)行前數(shù)據(jù)庫處于一致性狀態(tài)，事務(wù)結(jié)束后，數(shù)據(jù)庫回到一致性狀態(tài)。一致性意味著每行和每個值都與描述一致且滿足所有的約束。I（isolation）隔離性：每個事務(wù)都必須是隔離的，不受其他事務(wù)的影響，不管是否有其他事務(wù)執(zhí)行，它都必須使用它開始時的數(shù)據(jù)集繼續(xù)執(zhí)行下去。D（durability）持久性：不管系統(tǒng)是否發(fā)生故障，事務(wù)的結(jié)果都是永久性的。（6）事務(wù)缺陷臟讀：如果事務(wù)能夠讀取其他商務(wù)尚

7、未提交的更新，則被稱為臟讀。臟讀可能導(dǎo)致在讀取的數(shù)據(jù)未提交前，而寫入該數(shù)據(jù)的事務(wù)可能被回滾。不可重復(fù)讀：類似臟讀，指事務(wù)能看到其他事務(wù)已提交的數(shù)據(jù)更新。如果兩次讀取得到的結(jié)果不同，則存在不可重復(fù)讀性事務(wù)缺陷。虛幻行：危害最小，指事務(wù)的更新操作不僅影響結(jié)果集的數(shù)據(jù)值，還導(dǎo)致select語句返回不同記錄集。丟失更新：兩個用戶同時更新一行數(shù)據(jù)時，第二個覆蓋了第一個的更新，導(dǎo)致更新丟失。死鎖：多個項目的多個事務(wù)爭用一個數(shù)據(jù)資源時容易發(fā)生死鎖，需要一個事務(wù)主動放棄或退出。（7）隔離級別：未提交讀、已提交讀、可重復(fù)讀和可序列化，2005還引入了快照隔離，但容易導(dǎo)致更新丟失。1.4.4性能物理磁盤的性能是最

8、嚴(yán)重的瓶頸，因此提高性能的關(guān)鍵之處在于減少執(zhí)行任務(wù)時需要讀取的物理頁數(shù)量。提高性能的技術(shù)有：（1）設(shè)計：簡化設(shè)計，不要包含過多的表，過多游標(biāo)等（2）基于集合的處理：基于集合的優(yōu)秀查詢能夠?qū)φ麄€數(shù)據(jù)集進(jìn)行處理，且只讀取一次所需數(shù)據(jù)頁，而基于游標(biāo)的解決方案分別對每一行進(jìn)行處理。（3）索引是查詢和數(shù)據(jù)之間的橋梁，它能減少執(zhí)行表讀取操作時需要讀取的物理頁數(shù)。要更精確滴設(shè)計索引，必須了解數(shù)據(jù)庫引擎的查詢優(yōu)化器和索引結(jié)果的工作原理。索引有主力提高數(shù)據(jù)讀取的性能，但給寫入性能帶來負(fù)面影響，插入和更新數(shù)據(jù)時，要確保索引數(shù)據(jù)同步。（4）分區(qū)：將數(shù)據(jù)分散到多個磁盤中。（5）緩存：預(yù)先從磁盤中讀取數(shù)據(jù)，以便數(shù)據(jù)庫操

9、作需求時能夠在內(nèi)存中找到它們。1.4.5可用性恢復(fù)計劃、冗余性、存檔方式以及網(wǎng)絡(luò)速度都將影響可用性，可用性通常用幾個9表示，7個9表示在99.99999%情況下可用，5個9表示在99.999%可用。以此類推。（1）冗余：找出可存在的故障點，并通過過程備用解決方案來避免或降低故障帶來的影響。（2）恢復(fù)：利用備份文件還原數(shù)據(jù)。1.4.6可擴(kuò)展性（1）抽象層：降低數(shù)據(jù)存儲和應(yīng)用程序之間的耦合程度，一種最主要的方法是使用抽象層。這樣可以避免因為缺乏封裝導(dǎo)致兩者之間的耦合過深。（2）歸一化：將類似的尸體組合成一個實體。1.4.7安全性（1）數(shù)據(jù)中心的物理安全性（2）操作系統(tǒng)的安全性信息安全還包括：（1）

10、限制訪問（2）信息所有權(quán)（3）審核跟蹤1.5優(yōu)化理論和SQL server(1)架構(gòu)設(shè)計避免過于復(fù)雜精心挑選鍵處理可選數(shù)據(jù)實現(xiàn)抽象層（2）查詢問題最佳解決方案復(fù)雜的業(yè)務(wù)邏輯查詢、子查詢、CTE動態(tài)生成DDL游標(biāo)對列表進(jìn)行逆規(guī)范化多賦值標(biāo)量和游標(biāo)交叉表包含透視和case表達(dá)式的查詢導(dǎo)航層次結(jié)構(gòu)UDF或CTE計算累計總計和動態(tài)和游標(biāo)（3）索引 （4）并發(fā)性：鎖定和阻塞問題。nolock并不是很好的解決方式，很危險?？梢圆捎每s短事務(wù)的持續(xù)時間來實現(xiàn)。（5）高級伸縮性快照隔離分區(qū)表索引視圖service brokerSQL server基礎(chǔ)理論學(xué)習(xí) ：關(guān)系數(shù)據(jù)庫建模3.關(guān)系數(shù)據(jù)庫建模3.1模擬現(xiàn)實數(shù)據(jù)

11、建模過程：（1）手機(jī)觀察結(jié)果和需求（2）現(xiàn)實世界的邏輯表示（3）可見實體的識別和設(shè)計（4）架構(gòu)設(shè)計（輔助和支持實體）（5）應(yīng)用程序結(jié)構(gòu)設(shè)計3.2可見實體可見實體通常是大多數(shù)人能夠識別的對象。（1）每個元組（行）都是獨立的（2）主鍵（3）表、行和列3.3識別多個實體（1）多個對象 （2）對象之間的關(guān)系 （3）組織對象 （4）一致的查找值 （5）復(fù)雜對象3.4關(guān)系模式關(guān)系使用鍵將實體關(guān)聯(lián)起來，包含如下兩個主要屬性：基數(shù)：在關(guān)系的每一端可能存在的對象數(shù)可選性：關(guān)系是可選的也是強(qiáng)制的（1）輔助實體和外鍵兩個對象彼此相關(guān)聯(lián)時，通常一個實體為主實體，另一個實體為輔助實體。主實體中的一個對象將關(guān)聯(lián)到輔助實體

12、中的多個對象或元組。（2）關(guān)系基數(shù)指在關(guān)系的每一端有多少元組。在關(guān)系的主鍵端只能有一個元組，而在外鍵端可以有多個元組。（3）關(guān)系的可選性：可選關(guān)系和強(qiáng)制關(guān)系對于數(shù)據(jù)庫的完整性非常重要。（4）繪制數(shù)據(jù)模型關(guān)系圖Chen ER關(guān)系圖繪制方法非常簡單，用sisio即可。（5）關(guān)系種類一對多 一對一 超類/子類 多對多（6）類別實體有時也稱為查找表，（7）自反關(guān)系在兩個相同類型的實體之間建立關(guān)系，比如人與人之間的上下級關(guān)系，家庭成員之間的關(guān)系等。3.5規(guī)范化規(guī)范化是減少更新異常的工具。（1）實體/屬性的設(shè)計原則：每個數(shù)據(jù)庫實體必須描述一個“事務(wù)”屬性和事務(wù)之間必須存在一對一的關(guān)系每個屬性必須描述其所屬

13、實體，而不能描述其相關(guān)的實體。（2）范式規(guī)范化通常以范式的方式定義。每種范式都描述了設(shè)計實體和屬性時可能出現(xiàn)的錯誤，并提供了糾正這種錯誤的規(guī)則。因此范式類似于書面語言的語法規(guī)則。規(guī)范化數(shù)據(jù)庫與平面文件數(shù)據(jù)庫比，優(yōu)點如下：消除重復(fù)存儲減少鎖的爭用縮小文件數(shù)據(jù)建模人員應(yīng)該一開始就按照第三范式設(shè)計邏輯架構(gòu)。(3)第一范式符合條件A每個數(shù)據(jù)單元都是用標(biāo)量屬性表示的，標(biāo)量值指的是“可以用標(biāo)尺上的一個點表示”的值B所有數(shù)據(jù)必須用唯一屬性表示，每個屬性都必須有唯一的名稱和用途。C所有的數(shù)據(jù)都必須用唯一的元組表示。（4）第二范式確保每個屬性是實體屬性。每個屬性都必須有對應(yīng)的主鍵。如果其他屬性只依賴于主鍵的一個

14、屬性，而不依賴于主鍵的其他屬性，這被稱為部分依賴，違反了第二范式。（5）第三范式檢查傳遞依賴。傳遞依賴和部分依賴的相似之處在于，他們值得都是屬性不完全依賴于主鍵。當(dāng)A依賴于B，B依賴于主鍵時，就發(fā)生了傳遞依賴。同第二范式一樣，解決方式是將非依賴屬性移到新實體中。*如果實體有很好的主鍵，每個屬性都是標(biāo)量且都完全依賴于主鍵，則邏輯設(shè)計符合第三方式，大多數(shù)數(shù)據(jù)庫設(shè)計滿足第三范式即可。（6）BCNF范式位于三四范式之間，用于處理實體有兩組主鍵的問題。BCNF規(guī)定，在這種情況下，應(yīng)將實體拆分為兩個實體，每個實體包含一個主鍵。（7）第四范式用于處理復(fù)雜的符合主鍵導(dǎo)致的問題，如果將兩個相互獨立的屬性組合成主

15、鍵，同時還要有第三個屬性，如果沒有第三個屬性，僅靠著兩個屬性不能唯一標(biāo)識實體，則違反了第四范式。（8）第五范式用于設(shè)計涉及多個（3個以上）實體的復(fù)雜關(guān)系，如果設(shè)計正確可以設(shè)計出三元關(guān)系，其中每個關(guān)系的基數(shù)都可以是一或多。3.6關(guān)系代數(shù)關(guān)系代數(shù)有8個關(guān)系運算符組成（1）限制：返回符合制定條件的行（2）投影：從數(shù)據(jù)集中返回指定列（3）積：關(guān)系乘法，返回兩個數(shù)據(jù)集的所有可能的數(shù)據(jù)集合。（4）聯(lián)合：關(guān)系加法和減法，將一個表垂直堆疊到另一個表上，將兩個表合并（5）交集：返回兩個表中都有的行（6）差集：返回一個數(shù)據(jù)集中特有的行（7）連接：返回兩個表的水平合并結(jié)果，合并是根據(jù)都有的行來匹配（8）除：返回兩個

16、數(shù)據(jù)集中完全匹配的內(nèi)容另外sql server還開發(fā)以下功能：子查詢在關(guān)系代數(shù)中，使用以下術(shù)語：表或數(shù)據(jù)集被稱為關(guān)系或?qū)嶓w行被稱為元組列被稱為屬性SQL server基礎(chǔ)理論學(xué)習(xí) ：SQL server 2005體系結(jié)構(gòu)4 SQL server 2005體系結(jié)構(gòu)4.1數(shù)據(jù)訪問體系結(jié)構(gòu)sql server 首先是一個數(shù)據(jù)庫服務(wù)器（1）客戶/服務(wù)器數(shù)據(jù)庫模型客戶/服務(wù)器數(shù)據(jù)庫介紹：客戶進(jìn)程請求服務(wù)器進(jìn)程提供服務(wù)，后者處理客戶發(fā)出的請求。所有的數(shù)據(jù)庫工作都是在數(shù)據(jù)庫服務(wù)器中執(zhí)行的，完成后講數(shù)據(jù)集的拷貝發(fā)送到客戶端，實際的數(shù)據(jù)和索引不會離開服務(wù)器。相對于桌面數(shù)據(jù)庫（acess），它性能更高，更安全可靠

17、?？蛻?服務(wù)器數(shù)據(jù)庫職能數(shù)據(jù)庫服務(wù)器職責(zé)：A處理數(shù)據(jù)修改和檢索要求B執(zhí)行數(shù)據(jù)密集型處理C實施所有的數(shù)據(jù)庫規(guī)則和約束D實現(xiàn)數(shù)據(jù)安全性數(shù)據(jù)庫客戶端職責(zé)：A以易于識別、引人入勝和適用的格式將數(shù)據(jù)呈現(xiàn)給用戶B提供使用各種工具、數(shù)據(jù)和報表的界面C將請求提交給服務(wù)器（2）N層設(shè)計在客戶/服務(wù)器應(yīng)用程序中，除了客戶端和服務(wù)器進(jìn)程外，還涉及其他進(jìn)程，經(jīng)常使用中間層來處理連接、連接池和業(yè)務(wù)邏輯。使用中間層可以讓多個用戶使用為數(shù)不多的數(shù)據(jù)庫服務(wù)器連接，到要考慮用戶的身份驗證方式。在有多個服務(wù)器可用的情況下，通過使用通用連接對象，在服務(wù)器A發(fā)生故障時，將用戶從服務(wù)器A切換到服務(wù)器B將更容易，連接對象成為能夠檢測到故

18、障并在服務(wù)器之間切換的單個點。這種解決方案在使用數(shù)據(jù)庫鏡像時非常有用。（3）面向服務(wù)的體系結(jié)構(gòu)面向服務(wù)的體系結(jié)構(gòu)（SOA）是客戶/服務(wù)器數(shù)據(jù)庫模型的替代品，SOA使用標(biāo)準(zhǔn)的HTTP調(diào)用和XML，而不是多個系統(tǒng)之間編寫自定義的客戶/服務(wù)器接口，這樣多個系統(tǒng)可以用相同的連接口。SOA使用與有多個超大型系統(tǒng)的大企業(yè)，適應(yīng)用作輔助數(shù)據(jù)訪問方法，以簡化同其他超大型系統(tǒng)的通信，除此，不要使用SOA4.2 SQL server中的服務(wù)SQL server不僅僅是一個關(guān)系數(shù)據(jù)庫引擎，它是一組與數(shù)據(jù)庫相關(guān)的服務(wù)和組件，可結(jié)合使用它們來創(chuàng)建功能強(qiáng)大的數(shù)據(jù)庫后端解決方案。（1）關(guān)系引擎關(guān)系引擎有時也被稱為數(shù)據(jù)庫引擎

19、，它是sql server的核心，該進(jìn)程負(fù)責(zé)處理所有關(guān)系數(shù)據(jù)庫的工作。sql是一種描述性語言，這意味著SQL向引擎描述要處理的查詢，引擎負(fù)責(zé)完成其他工作。SQL server支持在同一臺物理服務(wù)器中安裝多達(dá)50個關(guān)系引擎實例，雖然它們共享一些組件，但每個實例都是一個獨立的SQL server(2)Transact-SQLSQL 小組擴(kuò)展了ANSI定義，在很多方面進(jìn)行了改進(jìn)，添加了新命令，刪了一些命令，這就是Transact-SQL(T-SQL),SQL server只理解一種語言，那就是T-SQL。（3）Visual Studio 和CLRSQL server最激動人心的功能之一是集成了vis

20、ual Studio.Management Studio是基于Visual Studio集成開發(fā)環(huán)境（IDE），SQL server內(nèi)部操作系統(tǒng)（SQLOS）實際上是在SQL server內(nèi)部托管（host）了.net的共同語言運行時（CLR）名詞解釋：CLR是公共語言運行時，Common Language Runtime)和Java虛擬機(jī)一樣也是一個運行時環(huán)境，它負(fù)責(zé)資源管理（內(nèi)存分配和垃圾收集），并保證應(yīng)用和底層操作系統(tǒng)之間必要的分離。IDE，即Integrated Development Environment，是“集成開發(fā)環(huán)境”的英文縮寫，可以輔助開發(fā)程序的應(yīng)用軟件。軟件是用于程序開發(fā)

21、環(huán)境的應(yīng)用程序，一般包括代碼編輯器、編譯器、調(diào)試器和圖形用戶界面工具。就是集成了代碼編寫功能、分析功能、編譯功能、debug功能等一體化的開發(fā)軟件套。所有具備這一特性的軟件或者軟件套（組）都可以叫做IDE。如微軟的VisualStudio系列，Borland的C+Builder,Delphi系列等。該程序可以獨立運行，也可以和其它程序并用。（4）Service Brokersql server2005新增的功能，通過一個托管數(shù)據(jù)隊列，通過平滑負(fù)載，提高了性能和伸縮性。創(chuàng)建面向服務(wù)的體系結(jié)構(gòu)時，Service Broker是一個重要服務(wù)。（5）復(fù)制服務(wù)（6）全文搜索服務(wù) 通過windows 管理

22、工具>服務(wù)來啟動和停止（7）Notification Services可通過編程使其根據(jù)數(shù)據(jù)變更發(fā)送信息，消息幾乎可以發(fā)送給任何設(shè)備。（8）服務(wù)器管理對象（SMO）它是暴露sql server的配置和管理功能的一組對象，使得能夠在.net前段代碼中使用它們。（9）SQL 代理可選進(jìn)程，可配置為系統(tǒng)啟動時自動運行。（10）分布式事務(wù)協(xié)調(diào)器（DTC）負(fù)責(zé)以兩階段的方式提交跨越多個SQL server的事務(wù)。通過windows 管理工具>服務(wù)來啟動和停止（11）SQL mail讓sql server能夠通過SMTP將電子與偶見發(fā)送到外部郵箱。4.3 Business Intelligen

23、ce Services(BI)商業(yè)智能（BI）是SQL server2005最擅長的領(lǐng)域之一。SQL server2005提供了三種智能服務(wù)：（1）Integration Services(IS or SSIS，它表示SQL Server Integration Services)：幾乎可以在任何數(shù)據(jù)源之間傳輸數(shù)據(jù)，它是SQL server的提取-轉(zhuǎn)換-加載（ETL）工具。BI使用圖形工具來定義如何將數(shù)據(jù)從一個數(shù)據(jù)源移到另一個數(shù)據(jù)源。IS包能夠復(fù)制數(shù)據(jù)列，執(zhí)行復(fù)雜的轉(zhuǎn)換和處理數(shù)據(jù)移動發(fā)生的異常。IS非常適用于數(shù)據(jù)轉(zhuǎn)換。（2）Reporting Services(RS)（3）Analysis S

24、ervices(AS)：是一種提供商業(yè)智能或聯(lián)機(jī)分析處理的服務(wù)。能讓開發(fā)人員定義包含多維的數(shù)據(jù)集。多維數(shù)據(jù)集中包含根據(jù)超大型數(shù)據(jù)庫預(yù)先計算的匯總數(shù)據(jù)，讓用戶快捷瀏覽多維數(shù)據(jù)集中的總計和小計。這三種服務(wù)都是使用Business Intelligence Development Studio開發(fā)的。4.4 SQL server 2005的版本這里說的版本（edition）指的是包含的功能集不能的安裝包。而不是SQL server 2000 SQL server 2005等版本（version）企業(yè)（開發(fā)）版標(biāo)準(zhǔn)版工作組版SQL server速成版SQL server everywhere 版4.5

25、 SQL server的工具和組件（1）SQL server management studio供管理員和程序開發(fā)人員使用的類似visual studio的集成環(huán)境，其核心是強(qiáng)大的對象資源管理器。（2）SQL Server Configuration Manager這個工具用于啟動和停止各種服務(wù)器，可以通過開始菜單或management studio啟動它。（3）外圍應(yīng)用配置器默認(rèn)狀況下，很多sql server的功能都是被禁用，以減少sql server的暴露程度，這些功能大部分都可以使用T-SQL來啟用。（4）Business Intelligence Development Studio

26、類似management studio，但針對BI進(jìn)行優(yōu)化。（5）SQL集成幫助（6）SQL Profiler監(jiān)視sql server的流量和事件（7）性能監(jiān)視器（8）數(shù)據(jù)庫優(yōu)化顧問（9）命令行實用程序：SQLCmd Bulk Copy這些能夠讓開發(fā)人員在DOS提示符下或命令行調(diào)度程序中執(zhí)行SQL代碼或大容量復(fù)制操作。4.6 SQL server 2005功能包下載包提供了二十多個驅(qū)動程序以及支持向后兼容及其他系統(tǒng)兼容的代碼，還包含以下有用的工具有實用程序：SQL server升級顧問數(shù)據(jù)庫遷移助理最佳實踐工具4.7 AdventureWorks示例數(shù)據(jù)庫，替代以前的Northwind 和Pu

27、bs4.8探索元數(shù)據(jù)SQL server使用四個系統(tǒng)數(shù)據(jù)庫進(jìn)行自我管理，另外還有一個model數(shù)據(jù)庫。（1）Master:包含有關(guān)服務(wù)器中數(shù)據(jù)庫的信息（2）MSDB：包含諸如備份和作業(yè)等活動列表（3）Tempdb:包含用戶、批處理、存儲過程和SQL server引擎本書使用的臨時表。（4）Reference:該數(shù)據(jù)庫被隱藏，從master中分離出來，旨在使得升級服務(wù)包更容易。（5）Model:用于創(chuàng)建新數(shù)據(jù)庫的模板數(shù)據(jù)庫。元數(shù)據(jù)是有關(guān)數(shù)據(jù)的信息，SQL server支持4種元數(shù)據(jù)：目錄視圖：提供有關(guān)靜態(tài)元數(shù)據(jù)的信息動態(tài)管理視圖和函數(shù)：通過探索服務(wù)器的當(dāng)前狀態(tài)，提供有關(guān)諸如內(nèi)存、線程、緩存中的存

28、儲過程和連接等的數(shù)據(jù)。兼容性視圖：模擬以前版本中的表，用于向后兼容系統(tǒng)架構(gòu)視圖：ANSI SQL 92標(biāo)準(zhǔn)視圖?？捎糜诓榭慈魏螖?shù)據(jù)庫產(chǎn)品的架構(gòu)。SQL補(bǔ)充學(xué)習(xí)（一）：CROSS JOIN1.CROSS JOIN除了內(nèi)連接(inner jion)，外連接（left join ,right join,full join），還支持交叉連接。交叉連接返回被連接兩個表的卡迪爾乘積，即：1.2 CROSS JOIN 語法select 列名from 表名1 cross join 表名2where 表名1.列名=表名2.列名1.3 CROSS JOIN 實例樣例數(shù)據(jù)：要求：用cross join 語句找出兩表

29、中學(xué)生序號相同的學(xué)生，并將其所有字段組合在一個表中。select a.序號,b.姓名,a.班級,a.年齡,b.語文,b.數(shù)學(xué),b.地理,b.歷史from Table_4 as b cross join Table_5 as awhere a.序號=b.序號執(zhí)行結(jié)果：我們可以發(fā)現(xiàn)，用下面語句，效果是一致的：select a.序號,b.姓名,a.班級,a.年齡,b.語文,b.數(shù)學(xué),b.地理,b.歷史from Table_4 as b , Table_5 as awhere a.序號=b.序號注：實際上將cross join替換成“,”，效果相同。如果不加后面的where條件語句，將會返回32行。

30、SQL補(bǔ)充學(xué)習(xí)（二）：自身連接自身連接是指數(shù)據(jù)表與其自身產(chǎn)生連接，因此在實現(xiàn)時，需要使用別名來配合使用。2.1自身連接實例樣例數(shù)據(jù)：用自身連接得出歷史成績大于70的人的信息。語句：select a.序號,a.姓名,a.語文,a.數(shù)學(xué),a.地理,a.歷史from Table_4 as a,Table_4 as bwhere a.序號=b.序號and b.歷史>70執(zhí)行結(jié)果：可以看出，它與下面語句效果一致：select * from Table_4where 歷史>70注：實際上自身連接的操作對象是數(shù)據(jù)表本身。SQL補(bǔ)充學(xué)習(xí)（三）：INTERSECT語句和EXCEPT語句3.INTER

31、SECT語句和EXCEPT語句intersect語句可以用來查兩個表中的“公共”數(shù)據(jù)信息，即“交叉”數(shù)據(jù)信息。INTERSECT 返回 INTERSECT 操作數(shù)左右兩邊的兩個查詢都返回的所有非重復(fù)值。EXCEPT語句則是用來查詢上述“公共”信息“除外”才信息。EXCEPT 從左查詢中返回右查詢沒有找到的所有非重復(fù)值。而與UNION聯(lián)合查詢語句一樣，INTERSECT語句和EXCEPT語句查詢的兩個表必須具有相同的結(jié)構(gòu)，即他們的列必須相似，數(shù)目相等，順序相同，且數(shù)據(jù)類型兼容。3.1 INTERSECT語句和EXCEPT語句select 列名 from 表名1intersectselect 列名

32、 from 表名2select 列名 from 表名1exceptselect 列名 from 表名23.2intersect實例樣例數(shù)據(jù)要求：求出上面兩表中的共同部分。SELECT id,姓名,學(xué)校,地址,時間FROM mydata.dbo.Table_6intersectSELECT id,name,school,address,dataFROM mydata.dbo.Table_1執(zhí)行結(jié)果：要求：輸出上面兩表中共同信息除外的信息SELECT id,姓名,學(xué)校,地址,時間FROM mydata.dbo.Table_6exceptSELECT id,name,school,address,d

33、ataFROM mydata.dbo.Table_1執(zhí)行結(jié)果：注：輸出結(jié)果列名與第一個select 語句中的表的列名一致。SQL補(bǔ)充學(xué)習(xí)（四）：在select語句中使用子查詢4.子查詢子查詢是指將查詢結(jié)果作為另一個查詢參數(shù)的查詢。4.1 在select語句中使用子查詢實例要求：要求求出每個學(xué)生與平均年齡的差距語句：select姓名,年齡,(select AVG(年齡)from Table_2) as 平均年齡,(年齡-(select AVG(年齡)from Table_2)as 與平均年齡差距from Table_2執(zhí)行結(jié)果：注：如果要對結(jié)果進(jìn)行排序，后面可以加 order by 與平均年齡差

34、距SQL補(bǔ)充學(xué)習(xí)（五）：在from語句中使用子語句5.在from語句中使用子語句在select語句中的from子句中，也經(jīng)常會用到子查詢。5.1在from語句中使用子語句實例樣例數(shù)據(jù)：要求：輸出學(xué)生各科成績總分，按從高到底排select *from (select 姓名,sum(語文+數(shù)學(xué)+歷史+地理)as 總分 from Table_4 group by 姓名) tab1order by 總分執(zhí)行結(jié)果：SQL補(bǔ)充學(xué)習(xí)（六）：在where語句中使用子查詢6.在where語句中使用子查詢6.1實例：樣例數(shù)據(jù)要求：要求求出年齡大于平均年齡的每個學(xué)生與平均年齡的差距語句：select姓名,年齡,(s

35、elect AVG(年齡)from Table_2) as 平均年齡,(年齡-(select AVG(年齡)from Table_2)as 與平均年齡差距from Table_2where 年齡>=(select AVG(年齡)from Table_2)執(zhí)行結(jié)果：SQL補(bǔ)充學(xué)習(xí)（七）：IN語句的子查詢7.in語句的子查詢7.1 in語句的子查詢實例樣例數(shù)據(jù)要求：用in語句子查詢查詢出年齡小于20的語句如下：select 姓名,學(xué)校,地址,年齡from Table_2where 姓名 in(select 姓名 from Table_2 where 年齡<20)執(zhí)行結(jié)果：SQL補(bǔ)充學(xué)習(xí)

36、（八）：在多表查詢中使用子查詢8.在多表查詢中使用子查詢8.1在多表查詢中使用子查詢實例樣例數(shù)據(jù)：要求：輸出語文成績高于70分的學(xué)生的序號 班級 年齡語句如下：SELECT 序號,班級,年齡FROM Table_5where 序號 in (select 序號 from Table_4 where 語文>70)執(zhí)行結(jié)果：SQL補(bǔ)充學(xué)習(xí)（九）：使用EXISTS語句的子查詢9.使用EXISTS語句的子查詢EXISTS語句用來測試集合是否為空，在子查詢中返回true和false。通常情況下，EXISTS語句與子查詢結(jié)合使用，并且只要子查詢至少返回一個值，則EXISTS語句的值為true。9.1使

37、用EXISTS語句的子查詢樣例查詢在Table_4中有成績的學(xué)生信息select 序號,班級,年齡from Table_5 as awhere exists (select * from Table_4 where 序號=a.序號)執(zhí)行結(jié)果：EXISTS表達(dá)式前面還可以加上NOT運算符，表示不存在某個子查詢條件中。比如上面的代碼前加notselect 序號,班級,年齡from Table_5 as awhere not exists (select * from Table_4 where 序號=a.序號)執(zhí)行結(jié)果：SQL補(bǔ)充學(xué)習(xí)（十）：ANY運算符和ALL運算符的子查詢10.ANY運算符的子

38、查詢和ALL運算符的子查詢在子查詢中經(jīng)過會用到any運算符，表示跟子查詢中的每一個值進(jìn)行比較。在比較過程中，如果有一個值為真，則整個子查詢結(jié)果集的比較值就為真。all運算符與any不同在于，它需要所有比較結(jié)果都為真，整個子查詢結(jié)果集才為真。10.1 ANY運算符的子查詢實例要求：從表中查詢除了語文成績最低的人以為的所有人的各科成績。select * from Table_4where 語文>any (select 語文 from Table_4)執(zhí)行結(jié)果：10.2 比較運算符與any連用時的取值比較運算符+any所取子查詢的結(jié)果“>、>=、!<”+“any”取最小值“=

39、”+“any”取所有值，相當(dāng)于in“<>”+“any”相當(dāng)于not in“<、<=、!>” +“any”最大值因為any表示認(rèn)識一個的意思，比如大于，大于任何一個值即可，所以只要大于最小值就可以了。10.3 ALL運算符的子查詢實例樣例數(shù)據(jù)：要求：查出分?jǐn)?shù)scroe大于所有BEIJING地區(qū)學(xué)生score的學(xué)生信息語句：SELECT *FROM mydata.dbo.Table_1where score>all(select score from Table_1 where address='BEIJING')執(zhí)行結(jié)果：SQL補(bǔ)充學(xué)習(xí)（十一）

40、：Having語句的子查詢10.Having語句的子查詢having只能與select語句一起使用，通常在group by子句中使用，否則跟where子句一樣。10.Having語句的子查詢樣例數(shù)據(jù)要求：查詢出表中每個人的購物總花費，并輸出大于人平均花費的人的信息SELECT 姓名,sum(購物花費)as 消費總額from Table_3group by 姓名having sum(購物花費)>(select AVG(購物花費)from Table_3)執(zhí)行結(jié)果：SQL補(bǔ)充學(xué)習(xí)（十二）：嵌套子查詢12.嵌套子查詢子查詢也可以出現(xiàn)在其他子查詢語句中，通常將位于其他子查詢的子查詢稱為嵌套子查詢

41、。12.1嵌套子查詢實例樣例數(shù)據(jù)：要求：查詢出學(xué)生編號、班級、年齡，并且要求該學(xué)生的數(shù)學(xué)成績大于平均數(shù)學(xué)成績語句如下：SELECT 序號,班級,年齡from Table_5where 序號 in (select 序號 from Table_4 where 數(shù)學(xué)>(select AVG(數(shù)學(xué))from Table_4)執(zhí)行結(jié)果：要求：Table_5中的查詢出序號、班級、年齡以及Table_4中的語文的平均成績，并要求學(xué)生的語文成績要低于平均語文成績。select distinct a.序號,a.班級,a.年齡,(select AVG(語文) from Table_4)as 語文平均成績fr

42、om Table_4 as b,Table_5 as awhere a.序號 in(select 序號 from Table_4 where 語文>(select AVG(語文)from Table_4)執(zhí)行結(jié)果：SQL補(bǔ)充學(xué)習(xí)（十三）：CASE語句交叉表查詢 CASE13.交叉表查詢使用交叉表查詢不僅使數(shù)據(jù)容易管理，而且能生成一種方便閱讀的表格數(shù)據(jù)。13.1 CASE語句CASE語句可以返回多個結(jié)果的表達(dá)式，將某個表達(dá)式與一組簡單表達(dá)式進(jìn)行比較，從而得到所需結(jié)果。13.1.1CASE語句語法Case input_expressionWHEN when_expressionTHEN re

43、sult_expressionnELSE else_expressionEND13.1.2CASE語句實例樣例數(shù)據(jù)：要求：查詢出時間中三個時間段，每個人的總消費金額。語句：SELECT 時間,sum(case 姓名 when 'LI ming' Then 購物花費 else null end)as 'LI ming',sum(case 姓名 when 'wangzi' Then 購物花費 else null end)as 'wangzi',sum(case 姓名 when 'xiaodong' Then 購物花費

44、else null end)as 'xiaodong'FROM mydata.dbo.Table_3group by 時間執(zhí)行結(jié)果：要求：查詢出三個人在每個時間段的消費金額SELECT 姓名,sum(case 時間 when '2011-03-23 00:00:00.000' Then 購物花費 else null end)as '2011-03-23 00:00:00.000',sum(case 時間 when '2011-04-03 00:00:00.000' Then 購物花費 else null end)as '2

45、011-04-03 00:00:00.000',sum(case 時間 when '2011-04-30 00:00:00.000' Then 購物花費 else null end)as '2011-04-30 00:00:00.000'FROM mydata.dbo.Table_3group by 姓名執(zhí)行結(jié)果：SQL補(bǔ)充學(xué)習(xí)（十五）：select和select into語句插入數(shù)據(jù)15. select和select into語句插入數(shù)據(jù)15.1 select 語句插入數(shù)據(jù)用insert into select 語句插入數(shù)據(jù)的一個重要用途就是備份表。1

46、5.1.1 select 語句插入數(shù)據(jù)語法insert into 表名1select 列名 from 表名2where 條件15.1.2 select 語句插入數(shù)據(jù)實例要求：創(chuàng)建新表，將上面數(shù)據(jù)中語文大于60分的人的成績備份。語句：create table 表4備份表(序號 int,姓名 varchar(20)null,語文 int null,數(shù)學(xué) int null,地理 int null,歷史 int null)goinsert into 表4備份表select * from Table_4where 語文>60select * from 表4備份表執(zhí)行結(jié)果：15.2 select i

47、nto 語句插入數(shù)據(jù)select into也可向表中插入數(shù)據(jù)。15.2.1 select into 語法select 列名,列名,into 表名2from 表名1where 條件語句15.2.2 select into 實例樣例數(shù)據(jù)：要求：將序號1-5的人成績備份到新表 1-5中select 序號,姓名,語文,數(shù)學(xué),地理,歷史into 前5from Table_4where 序號 between 1 and 5select * from 前5執(zhí)行結(jié)果：注：每次最后的select *只是為了查看操作的效果。SQL補(bǔ)充學(xué)習(xí)（十六）：使用子查詢插入數(shù)據(jù)信息16使用子查詢插入數(shù)據(jù)信息使用子查詢插入數(shù)據(jù)

48、信息實際上是insert intoselect 語句，并將子查詢放置在查詢條件中。16使用子查詢插入數(shù)據(jù)信息實例樣例數(shù)據(jù)：要求：將在表4中有成績的同學(xué)的序號、班級和年齡備份到新表中。create table 備份表3(序號 int,班級 int null,年齡 int null)insert into 備份表3select 序號,班級,年齡 from Table_5 as Twhere EXISTS(select 序號 from Table_4 where 序號=T.序號)order by 序號select * from 備份表3執(zhí)行結(jié)果：SQL補(bǔ)充學(xué)習(xí)（十七）：使用SQL刪除數(shù)據(jù)17 使用S

49、QL刪除數(shù)據(jù)樣例數(shù)據(jù)：17.1 刪除指定條件的單行數(shù)據(jù)要求：刪除Table_5中序號為11的記錄行delete from Table_5where 序號=11select * from Table_5執(zhí)行結(jié)果：17.2刪除指定條件的多行記錄要求：在上述基礎(chǔ)上，刪除Table_5中序號在8-10之間的記錄。delete from Table_5where 序號 between 8 and 10select * from Table_5執(zhí)行結(jié)果：17.3刪除所有記錄刪除前面?zhèn)浞莸?前5語句如下：delete from 前5select * from 前5執(zhí)行結(jié)果：17.4使用TRUNCATE TA

50、BLE語句刪除數(shù)據(jù)truncate table 語句用于刪除表中所有數(shù)據(jù)，它比用delete 省略where刪除所有數(shù)據(jù)要快得多。區(qū)別在于：delete 是一次一行刪除，在事務(wù)處理日志中記錄相關(guān)刪除操作及刪除行中的列值，這樣在刪除失敗時可以用事務(wù)處理日志回復(fù)數(shù)據(jù)。但truncate talbe則一次性刪除所有數(shù)據(jù)，事務(wù)處理日志不能回復(fù)。17.5 子查詢刪除數(shù)據(jù)信息刪除Table_4中數(shù)學(xué)成績小于平均分的人的信息語句：delete from Table_4where 數(shù)學(xué)<(select AVG(數(shù)學(xué))from Table_4)select * from Table_4SQL補(bǔ)充學(xué)習(xí)（十七

51、）：使用SQL修改數(shù)據(jù)17.使用SQL修改數(shù)據(jù)17.1使用SQL修改數(shù)據(jù)語法update 表名set 列名1,列名2=值,where 條件樣例數(shù)據(jù)：17.2修改制定條件的單行數(shù)據(jù)要求：將Table_5中序號為5的學(xué)生的班級更改為1語句：update Table_5set 班級=1where 序號=5select * frome Table_5執(zhí)行結(jié)果：17.3改制定條件的多行數(shù)據(jù)將上表中年齡<6的人的年齡編到1班去update Table_5set 班級=1where 年齡<6select * from Table_5執(zhí)行結(jié)果：17.4使用子查詢修改數(shù)據(jù)記錄蟲蟲的語文成績加上語文成

52、績*0.2語句：update Table_4set 語文=語文+語文*0.2where 語文<(select AVG(語文)from Table_4)select * from Table_4執(zhí)行結(jié)果：17.5修改所有數(shù)據(jù)updata 表名set 列名=值,要求：將表5中的年齡全部設(shè)置為8歲語句：update Table_5set 年齡=8select * from Table_5SQL補(bǔ)充學(xué)習(xí)（十八）：SQL server中的事務(wù)18.SQL server中的事務(wù)事務(wù)是指一系列語句組成的邏輯工作單元，其中可以包括許多操作，但是它們在邏輯上是一個整體，要么全部完成，要么全部失敗。關(guān)鍵詞說

53、明：begin tran 開始事務(wù)commit tran 提交事務(wù)start transaction 執(zhí)行開始事務(wù)set constrains 在當(dāng)前事務(wù)中設(shè)置約束set transaction 設(shè)置下一個要執(zhí)行的事務(wù)屬性savepoint 在事務(wù)中設(shè)置保存點，可以理解為斷點，用來標(biāo)識重新開始事務(wù)的位置。release savepoint 釋放保存點rollback 標(biāo)識中止事務(wù)18.1 SQL server中的事務(wù)語法begin tran 事務(wù)名稱SQL語句操作commit tran 事務(wù)名稱18.2 使用事務(wù)修改數(shù)據(jù)信息樣例數(shù)據(jù)：要求：使用事務(wù)將語文成績小于30分的人語文成績給30分begin tran update_scoreupdate Table_4 set 語文=30where 語文<30commit tran update_scoreselect * from Table_4執(zhí)行結(jié)果：18.3 使用事務(wù)刪除信息刪除歷史成績>70的人的信息語句：begin tran DEL_scoredelete from Table_4where 歷史<70commit tran DEL_scoreselect * from Table_4執(zhí)行結(jié)果：18.4 使用事務(wù)保存數(shù)據(jù)信息要求：用