《Effective SQL》全稱《Effective SQL：編寫高質量SQL語句的61條有效方法（英文版）》，作者John L. Viescas（約翰·威克斯）、Douglas J.Steele（道格拉斯·斯蒂爾）、Ben G.Clothier（本·克洛希爾），電子工業出版社2017年8月出版
基本介紹
- 書名：Effective SQL：編寫高質量SQL語句的61條有效方法（英文版）
- 作者：【法】John L. Viescas 【加】Douglas J.Steele【美】Ben G.Clothier
- ISBN：978-7-121-32284-6
- 頁數：348
- 定價：89.00
- 出版社：電子工業出版社
- 出版時間：2017年8月
- 開本：16
內容提要,目錄,
內容提要
《Effective SQL：編寫高質量SQL語句的61條有效方法（英文版）》了對案例問題的分析與解決方案的實現，向讀者展示了如何通過SQL 編寫解決複雜問題，以及如何通過資料庫設計簡化數據管理。這是一本將SQL 高級功能實踐與SQL 實際套用示例完美結合的、面向所有版本SQL 方言（其中包括IBM DB2、Access、SQL Server、MySQL、Oracle、PostgreSQL）的編程指南。此外，SQL 語法只是本書的一小部分內容，從資料庫設計與最佳化，到分層數據與元數據管理，本書都有所涉及。如果讀者有一定的SQL 基礎，那么《Effective SQL：編寫高質量SQL語句的61條有效方法（英文版）》將助你成為頂級SQL 專家。
目錄
Introduction 1
A Brief History of SQL 1
Database Systems We Considered 5
Sample Databases 6
Where to Find the Samples on GitHub 7
Summary of the Chapters 8
Chapter 1: Data Model Design 11
Item 1: Verify That All Tables Have a Primary Key 11
Item 2: Eliminate Redundant Storage of Data Items 15
Item 3: Get Rid of Repeating Groups 19
Item 4: Store Only One Property per Column 21
Item 5: Understand Why Storing Calculated Data Is Usually a Bad Idea 25
Item 6: Define Foreign Keys to Protect Referential Integrity 30
Item 7: Be Sure Your Table Relationships Make Sense 33
Item 8: When 3NF Is Not Enough, Normalize More 37
Item 9: Use Denormalization for Information Warehouses 43
Chapter 2: Programmability and Index Design 47
Item 10: Factor in Nulls When Creating Indexes 47
Item 11: Carefully Consider Creation of Indexes to Minimize Index and Data Scanning 52
Item 12: Use Indexes for More than Just Filtering 56
Item 13: Don’t Go Overboard with Triggers 61
Item 14: Consider Using a Filtered Index to Include or Exclude a Subset of Data 65
Item 15: Use Declarative Constraints Instead of Programming Checks 68
Item 16: Know Which SQL Dialect Your Product Uses and Write Accordingly 70
Item 17: Know When to Use Calculated Results in Indexes 74
Chapter 3: When You Can’t Change the Design 79
Item 18: Use Views to Simplify What Cannot Be Changed 79
Item 19: Use ETL to Turn Nonrelational Data into Information 85
Item 20: Create Summary Tables and Maintain Them 90
Item 21: Use UNION Statements to “Unpivot”Non-normalized Data 94
Chapter 4: Filtering and Finding Data 101
Item 22: Understand Relational Algebra and How It Is Implemented in SQL 101
Item 23: Find Non-matches or Missing Records 108
Item 24: Know When to Use CASE to Solve a Problem 110
Item 25: Know Techniques to Solve Multiple-Criteria Problems 115
Item 26: Divide Your Data If You Need a Perfect Match 120
Item 27: Know How to Correctly Filter a Range of Dates on a Column Containing Both Date and Time 124
Item 28: Write Sargable Queries to Ensure That the Engine Will Use Indexes 127
Item 29: Correctly Filter the “Right” Side of a “Left” Join 132
Chapter 5: Aggregation 135
Item 30: Understand How GROUP BY Works 135
Item 31: Keep the GROUP BY Clause Small 142
Item 32: Leverage GROUP BY/HAVING to Solve Complex Problems 145
Item 33: Find Maximum or Minimum Values Without Using GROUP BY 150
Item 34: Avoid Getting an Erroneous COUNT() When Using OUTER JOIN 156
Item 35: Include Zero-Value Rows When Testing for HAVING COUNT(x) < Some Number 159
Item 36: Use DISTINCT to Get Distinct Counts 163
Item 37: Know How to Use Window Functions 166
Item 38: Create Row Numbers and Rank a Row over Other Rows 169
Item 39: Create a Moving Aggregate 172
Chapter 6: Subqueries 179
Item 40: Know Where You Can Use Subqueries 179
Item 41: Know the Difference between Correlated and Non-correlated Subqueries 184
Item 42: If Possible, Use Common Table Expressions Instead of Subqueries 190
Item 43: Create More Efficient Queries Using Joins Rather than Subqueries 197
Chapter 7: Getting and Analyzing Metadata 201
Item 44: Learn to Use Your System’s Query Analyzer 201
Item 45: Learn to Get Metadata about Your Database 212
Item 46: Understand How the Execution Plan Works 217
Chapter 8: Cartesian Products 227
Item 47: Produce Combinations of Rows between Two Tables and Flag Rows in the Second That Indirectly Relate to the First 227
Item 48: Understand How to Rank Rows by Equal Quantiles 231
Item 49: Know How to Pair Rows in a Table with All Other Rows 235
Item 50: Understand How to List Categories and the Count of First, Second, or Third Preferences 240
Chapter 9: Tally Tables 247
Item 51: Use a Tally Table to Generate Null Rows Based on a Parameter 247
Item 52: Use a Tally Table and Window Functions for Sequencing 252
Item 53: Generate Multiple Rows Based on Range Values in a Tally Table 257
Item 54: Convert a Value in One Table Based on a Range of Values in a Tally Table 261
Item 55: Use a Date Table to Simplify Date Calculation 268
Item 56: Create an Appointment Calendar Table with All Dates Enumerated in a Range 275
Item 57: Pivot Data Using a Tally Table 278
Chapter 10: Modeling Hierarchical Data 285
Item 58: Use an Adjacency List Model as the Starting Point 286
Item 59: Use Nested Sets for Fast Querying Performance with Infrequent Updates 288
Item 60: Use a Materialized Path for Simple Setup and Limited Searching 291
Item 61: Use Ancestry Traversal Closure for Complex Searching 294
Appendix: Date and Time Types, Operations, and Functions 299
IBM DB2 299
Microsoft Access 303
Microsoft SQL Server 305
MySQL 308
Oracle 313
PostgreSQL 315
Index 317
