Effective SQL:編寫高質量SQL語句的61個有效方法 ( 簡體 字) |
作者:[法] 約翰· L. 維卡斯(John L. Viescas)[加] 道格拉斯· J. 斯蒂爾(Douglas J. Steele) 著 | 類別:1. -> 資料庫 -> SQL語言 |
譯者: |
出版社:機械工業出版社 | 3dWoo書號: 49358 詢問書籍請說出此書號!【缺書】 NT售價: 345 元 |
出版日:6/1/2018 |
頁數:235 |
光碟數:0 |
|
站長推薦: |
印刷:黑白印刷 | 語系: ( 簡體 版 ) |
|
加入購物車 │加到我的最愛 (請先登入會員) |
ISBN:9787111600664 |
作者序 | 譯者序 | 前言 | 內容簡介 | 目錄 | 序 |
(簡體書上所述之下載連結耗時費功, 恕不適用在台灣, 若讀者需要請自行嘗試, 恕不保證) |
作者序: |
譯者序: |
前言:結構化查詢語言(Structured Query Language)簡稱SQL,是與大多數數據庫系統通信的標準語言。如果你正在閱讀本書并希望從數據庫系統中獲取信息,那么就需要使用SQL。
本書面向從事SQL工作的開發人員和初級數據庫管理員(DBA),適合對SQL的基本語法比較熟悉并且希望再獲得一些有用的技巧以便更高效地使用SQL語言的人。而且我們發現,當從計算機編程慣用的基于過程的方式轉變為基于集合的方式來解決問題時,所需的思維方式是截然不同的。
關系數據庫管理系統(RDBMS)是一種軟件應用程序,用于創建、維護、修改和操作關系數據庫。許多關系數據庫系統也提供了用于操作數據庫中數據的客戶端工具。關系數據庫系統自出現以來一直在不斷發展,并隨著硬件技術和操作系統環境的進步變得更加完善和強大。
SQL簡史
IBM研究員Edgar F. Codd博士(1923—2003)在1969年首次提出了關系數據庫模型。他在20世紀60年代后期研究了處理大量數據的新方法,并開始思考如何應用數學原理解決遇到的各種問題。
自1970年Codd博士向世界提出了關系數據庫模型之后,許多組織(如大學和研究實驗室)開始致力于開發一種語言,用作支持關系數據庫的基礎。20世紀70年代中期幾種不同的語言出現,其中一個正是來自位于加利福尼亞州圣何塞的IBM圣特雷莎研究實驗室的努力。
20世紀70年代初,IBM啟動了一個名為System/R的重大科研項目,旨在證明關系模型的可行性,并希望在設計和實現關系數據庫方面獲得一些經驗。1974∼1975年,他們的初次實驗獲得成功,創建了一個關系數據庫的迷你原型。
在開發關系數據庫的同時,研究人員也在努力定義數據庫語言。1974年,Donald Chamberlin博士和他的同事發明了結構化英語查詢語言(Structured English Query Language,SEQUEL),這門語言允許用戶使用清晰易懂的英語句子操作關系數據庫。原型數據庫SEQUEL-XRM的初步成功,激勵著Chamberlin和他的同事,他們決定繼續研究。1976∼1977年,他們把語言名稱從SEQUEL修改為SEQUEL/2,但是不巧,SEQUEL縮寫已經被別人使用了,出于法律原因,他們不得不將SEQUEL更名為SQL(結構化查詢語言或SQL查詢語言)。時至今日,雖然大家已經廣泛接受了官方發音“ess-cue-el”,但是許多人還是將SQL讀作“sequel”。
雖然IBM的System/R項目與SQL語言證明關系數據庫是可行的,但是由于當時的硬件技術水平太低,這款產品并沒有商用。
1977年,加利福尼亞州門羅公園的一群工程師創辦了Relational Software公司,他們開發了一套基于SQL的關系數據庫產品并命名為Oracle。1979年,Relational Software公司發布了這款產品,使之成為第一個商業化的關系數據庫產品。Oracle的一大優勢是能運行在Digital的VAX小型機上,而不是昂貴的IBM大型機。Relational Software公司此后更名為Oracle公司,成為RDBMS軟件領域領先的廠商之一。
大約在同一時間,來自加利福尼亞大學伯克利分校計算機實驗室的Michael Stonebraker、Eugene Wong和其他幾位教授也在研究關系數據庫技術。他們也開發了一個關系數據庫的原型,命名為Ingres。Ingres使用一種稱為查詢語言(QUEL)的數據庫語言,它比SQL結構更為清晰,而且使用了更少的類似英語的詞句。但是,很明顯當時SQL正在慢慢變成數據庫標準語言,所以Ingres最終改為基于SQL的關系數據庫。1980年,這幾位教授離開伯克利,成立了Relational Technology公司。1981年,他們發布了Ingres的第一個商業版本。Relational Technology公司之后經歷了幾次變革。Ingres之前為Computer Associates International公司所有,現在屬于Actian公司,但不管怎么樣,Ingres仍然是行業數一數二的數據庫產品。
1981年,IBM也宣布開發自己的關系數據庫,名為SQL/DataSys(SQL / DS),并于1982年發布。1983年,IBM公司推出了一種名為Database 2(DB2)的新型關系數據庫產品,它可以在安裝IBM的主流MVS操作系統的IBM大型機上運行。1985年第一次發布之后,DB2就成為IBM首屈一指的關系數據庫,其技術已經融入IBM整個產品線中。
隨著數據庫語言的不斷發展,語言標準化的想法在數據庫社區中呼聲漸長。但是,由誰來制定標準,或者應該參照哪個方言來制定一直沒有達成共識,所以每個開發商還在不斷開發與改進他們自己的數據庫產品,并希望有朝一日自己的數據庫方言能夠成為行業標準。
許多開發商都會根據用戶的反饋和需求在自己的數據庫方言中增加新的元素,從而形成了早期非正規的標準。相對現在的標準來說它只能算是很小的一部分,因為它只包含各種SQL方言中相似的部分。然而,正是這一小部分規范(盡管不是很完善)為數據庫用戶提供了一套權威的評判標準,通過這些標準可以判斷市場上的各種數據庫,并且用戶可以在不同數據庫間切換。
1982年,為了滿足關系數據庫語言標準化日益增長的需求,美國國家標準協會(ANSI)委托旗下X3組織數據庫技術委員會X3H2制定一個標準。一番周折之后(包括對SQL的大量改進),委員會才意識到新的標準與市面上主要的SQL方言不兼容,對SQL的修改也并沒有帶來顯著提高,不兼容是肯定的。最后,他們只保留了數據庫廠商都能夠遵循的最小集合。
1986年,美國國家標準協會(ANSI)正式采納了“ANSI X3.135-1986數據庫語言SQL”這個標準,也就是我們熟知的SQL/86標準。本質上,它就是將各種SQL方言之間的相似部分提取出來并標準化,其實許多數據庫廠商早就實現了。盡管這個標準還不太完善,但是至少為數據庫語言的未來奠定了堅實的基礎。
1987年,國際標準化組織(ISO)正式批準了自己的數據庫標準(幾乎和ANSI SQL/86一樣)作為國際標準,標準文件為“ISO 9075:1987數據庫語言SQL”(這兩個標準通常都簡稱為SQL/86)。國際數據庫社區廠商與美國數據庫廠商使用了相同的數據庫標準,他們可以聯合工作。盡管SQL已經標準化了,但是語言離完成還很遠。
很快SQL/86受到輿論、政府和行業專家的多方批評,例如著名的數據庫大師C. J. Date認為SQL語法很冗余(相同的查詢有不同的寫法),缺乏對某些關系運算符以及引用完整性的支持。
為了解決這個問題,ISO和ANSI修改了標準,只采用了原來標準中的核心部分,而且特別增加了對數據引用完整性的支持。1989年中期,ISO發布了名為“ISO 9075:1989數據庫語言SQL與完整性增強”的標準文件。同年末期,ANSI也發布了名為“X3.135-1989數據庫語言SQL與完整性增強”的標準文件。這兩個標準通常簡稱為SQL/89標準。
人們普遍認為,SQL/86和SQL/89標準缺乏數據庫系統應有的最基本功能。例如,標準中沒有定義如何修改或刪除數據庫結構,也沒有定義修改數據庫的安全權限的方法,即便所有數據庫廠商都已經在自己的產品中實現了這些功能(例如,使用CREATE語法創建了數據庫對象,但是標準中沒有定義ALTER或DROP語法來修改這個數據庫對象)。
ANSI和ISO不想再制定一個所有數據庫廠商都能支持的“最小集合”,他們把工作重點放在了SQL的修訂上,這也許能讓SQL變得更加完善。新標準(SQL/92)包括了大多數數據庫廠商已經廣泛支持的功能,但是也包括一些只有少數數據庫廠商支持的功能,甚至還有一些從未實現過的功能。
1992年10月,ANSI與ISO分別發布了新的SQL標準文件“X3.135-1992數據庫語言SQL”和“ISO/IEC 9075:1992數據庫語言SQL”。SQL/92標準的內容遠多于SQL/89,而且涉及的范圍更廣。例如,新標準中定義了創建數據庫結構之后修改它的方法,增加了對字符串、日期和時間的操作運算符,并定義了額外的安全特性。相比之前的標準,SQL/92算是個重要的里程碑。
當數據庫廠商致力于實現SQL/92標準中的功能時,他們也在開發和實現自己的功能,從而在SQL標準的基礎上擴展新的功能。雖然這些擴展(例如支持更多的數據類型,在SQL/92中只有6種數據類型)能為產品提供更多的功能,而且還可以區分其他的數據庫產品,但同時也存在很多隱患。最主要的問題是,擴展導致每個數據庫廠商的SQL方言與原始標準相差甚遠,這就破壞了應用程序在不同數據庫之間運行的可移植性。
1997年,ANSI的X3組織更名為國家信息技術標準委員會(NCITS),原來負責SQL標準的技術委員會現在稱為ANSI NCITS-H2。由于SQL標準復雜性的激增,ANSI和ISO標準委員會將標準分成12個有獨立編號的部分和一個附錄,這樣做的原因是,他們已經開始SQL3的工作(這樣命名是因為它是標準的第三個主要修訂版本),分開之后每個部分的工作可以同時進行。自1997年至今,共制定了兩個新的部分。
本書中所有的內容都是基于最新版的ISO SQL標準:數據庫語言—SQL/基礎(文檔ISO/IEC 9075-2:2011)—目前已經在大多數商業數據庫系統中實現。ANSI也采用了ISO標準,這個標準因此成了真正的國際化標準。我們還參考了IBM DB2、Microsoft Access、Microsoft SQL Server、MySQL、Oracle和PostgreSQL的最新文檔,在需要解釋不同數據庫產品的不同語法時會用到。雖然你在本書中學習的大多數SQL不是針對某個特定的數據庫軟件產品,但是我們會在必要的時候向你展示與特定數據庫產品相關的示例。
我們關注的數據庫
雖然上一節提到了SQL標準,但是這并不意味著所有數據庫系統都是一樣的。DB-Engines網站收集整理了大量的數據庫系統,并按照流行度每月公布一次排名,網址為http://db-engines.com/en/ranking/relational+dbms。
有6個數據庫系統持續保持排行榜最熱門DBMS好幾個月,按字母順序依次為(括號里的版本是書中測試使用的數據庫版本):
1)IBM DB2(Linux、UNIX和Windows版的DB2,v10.5.700.368)
2)Microsoft Access(Microsoft Access 2007,向上兼容2010、2013、2016或更高版本)
3)Microsoft SQL Server(Microsoft SQL Server 2012 11.0.5343.0)
4)MySQL(MySQL社區版5.7.11)
5)Oracle數據庫(Oracle Database 11g Express版本11.2.0.2.0)
6)PostgreSQL(PostgreSQL 9.5.2)
這并不意味著書中的代碼不能在這6種數據庫之外的數據庫中運行,我們只是沒有在其他數據庫或其他版本上測試而已。閱讀本書時,你可能會注意到,在針對不同數據庫時,我們都添加了說明(標有注意的部分)。注意的內容只涵蓋這6種數據庫。如果你使用的是其他數據庫,運行示例遇到任何問題時,請查閱相關數據庫文檔。
示例數據庫
為了闡明書中的概念,我們創建了一些用于舉例的數據庫,如下:
1)啤酒風格:這是一個很有趣的游戲,根據Michael Larson的書《Beer: What to Drink Next》(Sterling Epicure,2014)中提到的,對89種不同風格的啤酒進行分類。
2)演出代理:這個數據庫用來管理演員、代理、客戶和預訂。你將使用類似的設計來處理待辦事項和酒店預訂。
3)菜譜:這個數據庫可以用來保存和管理所有喜愛的食譜。
4)銷售訂單:這個是典型的商店訂單數據庫,銷售自行車、滑板以及相關配件。
5)學生課程:這個數據庫用來管理學生的信息、他們參加的課程,以及課程成績。
我們還為某些條目提供了特殊的示例數據庫,在這些條目里面就可以找到與這些示例數據庫相關的創建代碼。你可以在GitHub上找到與本書相關的數據庫表結構和示例數據。
在GitHub上查找示例
很多技術書籍都會附帶一個包含全書代碼的光盤。我們覺得這太局限了,所以決定把書中的所有示例都放在GitHub上,網址為https://github.com/TexanInParis/Effective-SQL。
GitHub上,最上層的6個文件夾是我們關注的幾個數據庫系統。6個文件夾中分別包含與書中章節相對應的10個文件夾和1個示例數據庫文件夾。
在這10個文件夾中分別包含一些SQL代碼文件,文件名與對應章節內的代碼清單的編號一致。注意,這些代碼不一定適用于所有數據庫。如果存在不兼容某些數據庫的情況,在每個章節文件夾下面我們都創建了一個名為README的文件,用來記錄不同數據庫之間的差異。對于Microsoft Access,README文件記錄的是哪個示例數據庫包含對應章節的代碼清單。
GitHub根目錄還有一個Listings.xlsx文件,這個文件記錄了每個示例數據庫對應哪些代碼清單,同時還記錄了這些代碼清單能夠適用于6個數據庫系統中的哪幾個。
每個示例數據庫文件夾中都包含許多SQL文件,除了Microsoft Access示例數據庫,Microsoft Access文件夾下面包含的都是Microsoft Access 2007格式的.accdb文件。我們使用Microsoft Access 2007格式的原因是它能兼容版本12(2007)以上的所有版本。示例數據庫文件夾下面的某些文件是用來創建數據庫表結構的,另外一些是用來插入示例數據庫的數據的。(注意,書中的某些條目依賴于某些特定的數據。這些條目需要的數據庫結構和數據有時就包含在章節的代碼清單中。)
在準備本書的代碼清單時,有些時候會遇到每行63個字符的限制,這個限制是紙質書規定的。某些代碼清單可能存在編輯錯誤。當你不確定的時候,請參考我們在GitHub上的代碼清單,上面的代碼清單都測試過,我們確信都是正確的。
章節概要
正如本書的書名所示,書中共包含了61個方法。每個方法條目都是相對獨立的。如果你使用某個條目里面的內容,不需要閱讀其他條目。當然,也有特例,有些條目里的內容依賴于其他條目。在這種情況下,我們盡可能地提供它們之間的依賴關系,但絕大多數情況下,我們都提供了它們之間的引用關系,所以你可以自行查閱。
雖然每個條目正如我們說的那樣相互獨立,但是仍然可以分成不同的主題。我們把它們分成以下10個主題:
1)數據模型設計:如果你面對的是糟糕的數據模型設計,想要編寫高效的SQL語句是不太可能的,該章包含一些基本的數據模型設計原則。如果你的數據庫設計違反了該章中討論的任何原則,你應當找出問題所在并修復。
2)可編程性與索引設計:僅靠良好的邏輯數據模型設計是寫不出高效的SQL語句的。你還必須保證設計被正確實施,否則你可能會發現使用SQL從數據庫中高效查詢數據的能力會大打折扣。該章將幫助你了解索引的重要性,以及如何正確地使用它。
3)當你不能改變設計時:有時,就算你使出渾身解數,也沒辦法處理在你控制之外的外部數據。該章將幫助你解決這個難題。
4)過濾與查找數據:從數據庫中查詢或過濾數據的能力是SQL的主要功能之一。該章探討了幾種不同的方法,可以用來查詢所需的數據。
5)聚合:SQL標準一直都有聚合數據的功能。但是,通常你得提供諸如“按客戶統計總數”“按天統計訂單數”或者“按月統計每個類型的平均銷售額”等的統計方式。它總是跟在“按”“以”或者“每”后面。該章將介紹高效聚合數據的技術,其中還會介紹如何使用窗口函數解決更為復雜的聚合問題。
6)子查詢:使用子查詢有很多種方法。本章將介紹在SQL語句中使用子查詢獲得更多靈活性的各種方法。
7)獲取與分析元數據:有時候會覺得信息不夠。你需要有關數據的信息,甚至可能需要有關數據如何被查詢到的信息。這種情況下,使用SQL獲取數據庫元數據可能會有幫助。該章可能會針對某個數據庫產品,但我們希望書中提供了足夠的信息,以便你可以將這些原則應用到數據庫系統中。
8)笛卡兒積:笛卡兒積是將一個表中的所有行與第二個表中的所有行組合在一起的結果。雖然可能不像其他表連接查詢那樣常用,但該章中展示的案例如果不使用笛卡兒積是沒辦法解決的。
9)計數表:另外一個有用的工具就是計數表,通常是具有單列連續遞增數字的表、單列連續遞增日期,或者更復雜一點的用作記錄行列轉換函數所需列名的表。笛卡兒積依賴于表中實際存儲的數據,而計數表則不受此限制。該章介紹了幾種只有使用計數表才能解決的問題。
10)層次數據建模:在關系數據庫中,層次數據建模并不罕見。糟糕的是,它恰好是SQL的一個軟肋。該章將幫助你如何在數據規范化和查詢與維護元數據的便利性之間進行權衡。
每種數據庫系統都有一些用來計算或者操作日期與時間的函數。同樣,每種數據庫系統也有自己的日期與時間的數據庫類型和計算規則。由于這些差異的存在,我們特意提供了一個附錄,在使用日期和時間的時候提供給你一些幫助。雖然我們確信已經總結了所有的數據類型和運算符,但還是強烈建議你參考相關的數據庫文檔,了解你使用的函數的特定語法。 |
內容簡介:本書整合了實用的解決方案與洞見,以幫助讀者解決復雜問題并設計出能簡化數據管理的數據庫。書中涵蓋各種SQL版本:IBM DB2、Microsoft Access、Microsoft SQL Server、MySQL、Oracle Database和PostgreSQL。
借助世界頂級數據庫顧問和講師的豐富經驗,作者在書中歸納了已經過證實的編寫高質量SQL語句的61個具體方法。當不同的SQL版本之間有差異時,書中會指出關鍵的不同點,這樣無論你是使用何種SQL版本都能從中獲益。
本書解釋清晰、務實,并提供了專家建議和大量有用的代碼。除語法外,書中還討論了優化數據庫設計以管理層次結構和元數據等主題。若你已了解SQL的基本知識,本書將幫助你提升為解決SQL問題的世界級專家。
本書包括以下內容:
打造更有邏輯的數據模型并修正有問題的模型
實現提升查詢性能的索引
處理你不能控制的外部數據源
高效地提取并整理你所需的信息
編寫更有彈性的子查詢
獲取與分析各種數據庫的元數據
使用笛卡兒積與計數表,解決傳統JOIN無法處理的問題
創建層次數據模型:管理SQL的優缺點 |
目錄:本書贊譽 譯者序 序 前言 致謝 關于作者 關于技術編輯 第1章 數據模型設計1 第1條:確保所有表都有主鍵1 第2條:避免存儲冗余數據4 第3條:消除重復數據組7 第4條:每列只存儲一個屬性9 第5條:理解為什么存儲計算列通常有害無益13 第6條:定義外鍵以確保引用完整性16 第7條:確保表間關系的合理性19 第8條:當第三范式不夠時,采用更多范式22 第9條:非規范化數據倉庫27 第2章 可編程性與索引設計30 第10條:創建索引時空值的影響30 第11條:創建索引時謹慎考慮以最小化索引和數據掃描35 第12條:索引不只是過濾37 第13條:不要過度使用觸發器41 第14條:使用過濾索引包含或排除數據子集45 第15條:使用聲明式約束替代編碼校驗47 第16條:了解數據庫使用的SQL方言并編寫相應的代碼48 第17條:了解何時在索引中使用計算結果51 第3章 當你不能改變設計時55 第18條:使用視圖來簡化不能更改的內容55 第19條:使用ETL將非關系數據轉換為有用的信息60 第20條:創建匯總表并維護64 第21條:使用UNION語句將非規范化數據列轉行66 第4章 過濾與查找數據72 第22條:了解關系代數及其如何在SQL中實現72 第23條:查找不匹配或缺失的記錄78 第24條:了解何時使用CASE解決問題79 第25條:了解解決多條件查詢的技術83 第26條:如需完美匹配,先對數據進行除操作88 第27條:如何按時間范圍正確地過濾日期和時間的列91 第28條:書寫可參數化搜索的查詢以確保引擎使用索引94 第29條:正確地定義“左”連接的“右”側97 第5章 聚合100 第30條:理解GROUP BY的工作原理100 第31條:簡化GROUP BY子句106 第32條:利用GROUP BY或HAVING解決復雜的問題109 第33條:避免使用GROUP BY來查找最大值或最小值113 第34條:使用OUTER JOIN時避免獲取錯誤的COUNT()117 第35條:測試HAVING COUNT(x) <某數時包含零值記錄121 第36條:使用DISTINCT獲取不重復的計數123 第37條:知道如何使用窗口函數126 第38條:創建行號與排名129 第39條:創建可移動聚合函數131 第6章 子查詢136 第40條:了解在何處使用子查詢136 第41條:了解關聯和非關聯子查詢的差異140 第42條:盡可能使用公共表表達式而不是子查詢145 第43條:使用連接而非子查詢創建更高效的查詢151 第7章 獲取與分析元數據154 第44條:了解如何使用系統的查詢分析器154 第45條:學習獲取數據庫的元數據164 第46條:理解執行計劃的工作原理168 第8章 笛卡兒積175 第47條:生成兩張表所有行的組合并標示一張表中間接關聯另一張表的列175 第48條:理解如何以等分量排名177 第49條:知道如何對表中的行配對181 第50條:理解如何列出類別與前三偏好185 第9章 計數表189 第51條:根據計數表內定義的參數生成空行189 第52條:使用計數表和窗口函數生成序列193 第53條:根據計數表內定義的范圍生成行196 第54條:根據計數表定義的值范圍轉換某個表中的值199 第55條:使用日期表簡化日期計算205 第56條:創建在某個范圍內所有日期的日程表209 第57條:使用計數表行轉列212 第10章 層次數據建模217 第58條:從鄰接列表模型開始218 第59條:對不常更新的數據使用嵌套集以提升查詢性能220 第60條:使用存儲路徑簡化設置與搜索222 第61條:使用祖先遍歷閉包做復雜搜索224 附錄 日期與時間類型、運算符和函數228 |
序: |