快速入門系列--TSQL-01基礎概念

????作為一名程序員,對于SQL的使用算是基礎中的基礎,雖然也寫了很多年的SQL,但常常還是記不清一些常見的命令,故而通過一篇博文鞏固相關的記憶,并把T-SQL本身的一些新特性再進行一次學習。

????首先回顧基礎的概念,這部分可以跳過哈,比較枯燥。結構化查詢語言SQL是基于集合理論和謂詞邏輯的,大學課程中數字邏輯和離散數學主要會涉及這部分的內容。

集合理論是數學家Georg Cantor創建,是基于關系模型的數學分支。集合的定義為,任意集合體是我們感知或者想到的,能夠確定的、互異對象m的整體。

謂詞邏輯的淵源可以追溯到古希臘,也是基于關系模型的一個數學分支。創建關系模型領域的Edgar F. Codd博士提出通過謂詞邏輯來管理和查詢數據。一般來說,謂詞是一個屬性或是一個表示"持有"或者"不持有"的表達式,也就是"真"或者"假"。關系模型就是依靠謂詞來維護數據的邏輯完成性并定義其結構的。在C#中,可以經?？吹絧redicate,也是這個邏輯謂詞。

關系模型涉及命題、謂詞、關系、元組和屬性等概念。在集合理論中,關系是集合的表現形式。在關系模型中,關系是相關的信息的集合,與SQL中相對應的就是表(而不是表間的關系)。需要注意的是,單個關系代表單個集合,而多個關系經過操作(基于關系代數)的結果會是一個關系,如聯接操作。一般來說,在我們看來,謂詞不是"真",就是"假"。但讓我們回憶一下,物理學中的一個著名實驗"薛定諤之貓",其主要思想就涉及"未可知態",因此在數據庫中,我們可以看到"true","false","unknown"三種情況。

同時,關系模型通過約束規則將定義數據完整性作為模型的一部分。常見的有提供實體完整性的候選鍵和提供引用完整性的外鍵。候選鍵是定義了一個和多個屬性的鍵,防止關系中出現多個相同的元組,基于候選鍵的謂詞可以唯一的標識行,可以在關系上定義多個候選鍵,通常會選擇一個候選鍵作為主鍵,其他的候選鍵稱為備用鍵。這兒可以補充的是,其實主鍵(候選鍵)的概念和索引的概念是分離的,只是數據庫在實現時往往把主鍵作為聚集索引。外鍵用于強制引用完整性,外鍵定義了關系的一個或多個屬性引用另一關系的候選鍵,此約束限定了引用關系的外鍵屬性中的值,應該出現在被引用關系的候選鍵屬性中的值。這兒想補充的是,在高并發環境下,一般不使用數據庫外鍵,而是在業務層進行控制。此外還有非空約束、唯一約束等。

接下來介紹一個很常見但時間一長就容易忘記的概念,規范化規則(也叫范式)。規范化是一個常規的數學過程,用于確保每一個實體都由單一關系表示,以避免數據修改過程中的異常,并在不犧牲完整性的前提下保持最低的冗余。最常見的三個范式分別是:

1NF,表示關系中的元組必須是唯一的,而且屬性是原子化的(這個往往根據需求而定,不絕對)。

2NF,在滿足第一范式的基礎上,對于每個候選鍵,每個非鍵屬性必須是對整個候選鍵的完全函數依賴。簡而言之,就是如果要獲得任何非鍵屬性值,需要提供相同元組候選鍵中所有屬性的值,如果知道候選鍵的所有屬性的值,就可以檢索到任意元組的任何屬性的任何值。

3NF,在滿足第二范式的基礎上,所有非鍵屬性必須依賴于非傳遞的候選鍵。簡單來說,就是所有非鍵屬性間必須相互獨立,一個非鍵屬性不能依賴于另一個非鍵屬性。

接下來簡單介紹數據的生命周期,常見的業務系統一般僅僅包含聯機事務處理一個階段,但是隨著業務規模的發展,會慢慢衍生出很多大數據方向的階段,如圖1所示,其中縮略詞所代表的意思為:OLTP,聯機事務處理;DSA,數據準備區;DW(OLAP),數據倉庫;BISM,商業智能語義模型;DM(Data Mining),數據挖掘;ETL,提取、轉換和加載;MDX,多維表達式;DAX,數據分析表達式;

圖 1數據生命周期

????其中,數據倉庫主要是為了支持數據檢索需求進行的數據模型設計和優化, 模型故意進行了冗余、減少表和簡化關系。最簡單DW結構是星型架構,包括多個維度表和一個事實表,每個維度表表示要分析的數據主題。例如在訂單和銷售系統中,可能要分析客戶、產品、雇員、時間以及類似主題數據。星型架構中,每一個維度以冗余數據方式填充單個表(例如將Product、ProductSubCategory、ProductCategory合成一個ProductDim)。此外,如果想要規范化一個維度表,就會產生多個表來表示該維度,得到一個雪花維度,這種的結構也被稱為雪花架構。

從源系統提取數據、處理數據并加載到數據倉庫的過程,被稱為提取、轉換和加載ETL,SQL Server相關的產品就是我們常見SSIS,此過程常常涉及OLTP和DW之間數據準備區DSA的使用。

商業智能語義模型BISM提供豐富靈活的分析和報告功能,其體系結構包含三層,數據模型、業務邏輯和查詢數據訪問。模型可以部署在Analysis Services和PowerPivot上,前者針對BI專業人員,使用多維數據模型或表格,而后者針對企業用戶,使用表格數據模型。業務和查詢使用兩種語言, 基于多維概念的多維表達式(MDX)和基于表格概念的數據分析表達式(DAX)。數據訪問層可以從不同的數據來源獲取數據:如DW這樣的關系型數據庫、文件、云服務、Odata訂閱等。數據訪問層既可以在本地緩存數據,也可以作為連接數據源的直通層。緩存模式下可以有兩種存儲引擎方式選擇,一種被稱為MOLAP的預先聚合方式,當初設計它是為了支持多維模型,而另一種稱為VertiPaq的新引擎,它實現了列存儲概念,具有很高的壓縮級別和非?？焖俚奶幚硪?不在需要預先聚合、索引等。

BISM為用戶提供可能的答案,而DM為客戶提供正確的答案。也就是說,數據挖掘算法梳理數據并篩選有用信息,Analysis Services支持的數據挖掘算法包括:聚類分析、決策樹等。

????關于SQL Server的一些例如ABC等產品概念就跳過,畢竟使用性不強,不過最近微軟的云服務,包括數據庫云服務(Window Azure SQL Database)還是有一些亮點的。技術概念比較多,之后一一介紹。

SQL Server實例的通過機器名實例名唯一標示,例如Server1Inst1。數據庫實例中多個不同的數據庫,系統數據庫包括:master數據庫存儲實例范圍的元數據信息、服務器配置等;model數據庫用于創建數據的模板;tempdb數據庫是存儲臨時數據的地方,如工作表、排序空間、行版本控制信息等;msdb數據庫是一個稱為"SQL Server代理"的服務存儲數據的地方,SQL Server代理提供自動操作(包括作業、計劃和警報)、復制服務、Database Mail、Service Broker、備份等。

數據庫有數據文件和日志文件組成,在創建數據庫時,可以為每個文件定義各種屬性,包括文件名、位置、初始大小、最大大小和一個自動增長的增量。數據文件存儲數據,日志文件維護事務信息。雖然SQL Server可以并行寫入多個數據文件,但只能以連續的方式一次寫入一個日志文件。因此,與數據文件不同,多個日志文件并不會帶來性能的提升,同時如果日志所在磁盤空間不足,那么可能還需要添加日志文件,這點在實際工作中也經常遇到日志文件過大占滿磁盤空間而造成數據庫服務不可用。數據文件被組織在一個叫做"文件組"的邏輯組中,primary文件組包含XXX.mdf的主數據庫文件,以及數據庫系統目錄,可以添加次要數據文件XXX.ndf到primary,同時XXX.ldf表示日志文件。此外,數據庫提供schema架構一層用于數據庫對象的管理。

圖 2數據庫文件布局

主要供自己復習參考使用,如有疏漏,深表歉意。

參考資料:

1. (美)本咁. SQL Server 2012 T-SQL基礎教程[M]. 北京:人民郵電出版社, 2013.