logo资料库

Effective SQL：编写高质量SQL语句的61个有效方法.pdf

发布时间：2022-06-24 发布人：admin 分类：说明书 资料大小：24.27M 资料格式：pdf 举报 版权申诉
第1页 / 共546页
第2页 / 共546页
第3页 / 共546页
第4页 / 共546页
第5页 / 共546页
第6页 / 共546页
第7页 / 共546页
第8页 / 共546页
资料共546页，剩余部分请下载后查看
    About This E-Book
    Title Page
    Copyright Page
    Dedication Page
    Contents
    Foreword
    Acknowledgments
    About the Authors
    About the Technical Editors
    Introduction
    A Brief History of SQL
    Database Systems We Considered
    Sample Databases
    Where to Find the Samples on GitHub
    Summary of the Chapters
    1. Data Model Design
    Item 1: Verify That All Tables Have a Primary Key
    Things to Remember
    Item 2: Eliminate Redundant Storage of Data Items
    Things to Remember
    References
    Item 3: Get Rid of Repeating Groups
    Things to Remember
    Item 4: Store Only One Property per Column
    Things to Remember
    Item 5: Understand Why Storing Calculated Data Is Usually a Bad Idea
    Things to Remember
    Item 6: Define Foreign Keys to Protect Referential Integrity
    Things to Remember
    Item 7: Be Sure Your Table Relationships Make Sense
    Things to Remember
    Item 8: When 3NF Is Not Enough, Normalize More
    Things to Remember
    Item 9: Use Denormalization for Information Warehouses
    Things to Remember
    2. Programmability and Index Design
    Item 10: Factor in Nulls When Creating Indexes
    IBM DB2
    Microsoft Access
    Microsoft SQL Server
    MySQL
    Oracle
    PostgreSQL
    Things to Remember
    Item 11: Carefully Consider Creation of Indexes to Minimize Index and Data Scanning
    Things to Remember
    Item 12: Use Indexes for More than Just Filtering
    Things to Remember
    Item 13: Don’t Go Overboard with Triggers
    Things to Remember
    Item 14: Consider Using a Filtered Index to Include or Exclude a Subset of Data
    Things to Remember
    Item 15: Use Declarative Constraints Instead of Programming Checks
    Things to Remember
    Item 16: Know Which SQL Dialect Your Product Uses and Write Accordingly
    Ordering Result Sets
    Limiting Result Sets
    The BOOLEAN Data Type
    SQL Functions
    The UNIQUE Constraint
    Things to Remember
    Item 17: Know When to Use Calculated Results in Indexes
    Things to Remember
    3. When You Can’t Change the Design
    Item 18: Use Views to Simplify What Cannot Be Changed
    Things to Remember
    Item 19: Use ETL to Turn Nonrelational Data into Information
    Things to Remember
    Item 20: Create Summary Tables and Maintain Them
    Things to Remember
    Item 21: Use UNION Statements to “Unpivot” Non-normalized Data
    Things to Remember
    4. Filtering and Finding Data
    Item 22: Understand Relational Algebra and How It Is Implemented in SQL
    Select (Restrict)
    Project
    Join
    Intersect
    Cartesian Product
    Union
    Divide
    Difference
    Things to Remember
    Item 23: Find Non-matches or Missing Records
    Things to Remember
    Item 24: Know When to Use CASE to Solve a Problem
    Things to Remember
    Item 25: Know Techniques to Solve Multiple-Criteria Problems
    Things to Remember
    Item 26: Divide Your Data If You Need a Perfect Match
    Things to Remember
    Item 27: Know How to Correctly Filter a Range of Dates on a Column Containing Both Date and Time
    Things to Remember
    Item 28: Write Sargable Queries to Ensure That the Engine Will Use Indexes
    Things to Remember
    Item 29: Correctly Filter the “Right” Side of a “Left” Join
    Things to Remember
    5. Aggregation
    Item 30: Understand How GROUP BY Works
    Things to Remember
    Item 31: Keep the GROUP BY Clause Small
    Things to Remember
    Item 32: Leverage GROUP BY/HAVING to Solve Complex Problems
    Things to Remember
    Item 33: Find Maximum or Minimum Values Without Using GROUP BY
    Things to Remember
    Item 34: Avoid Getting an Erroneous COUNT() When Using OUTER JOIN
    Things to Remember
    Item 35: Include Zero-Value Rows When Testing for HAVING COUNT(x) < Some Number
    Things to Remember
    Item 36: Use DISTINCT to Get Distinct Counts
    Things to Remember
    Item 37: Know How to Use Window Functions
    Things to Remember
    Item 38: Create Row Numbers and Rank a Row over Other Rows
    Things to Remember
    Item 39: Create a Moving Aggregate
    Things to Remember
    6. Subqueries
    Item 40: Know Where You Can Use Subqueries
    Table Subquery
    Table Subquery with One Column
    Scalar Subquery
    Things to Remember
    Item 41: Know the Difference between Correlated and Non-correlated Subqueries
    Non-correlated Subqueries
    Correlated Subqueries
    Things to Remember
    Item 42: If Possible, Use Common Table Expressions Instead of Subqueries
    Using a CTE to Simplify a Query
    Using a Recursive CTE
    Things to Remember
    Item 43: Create More Efficient Queries Using Joins Rather than Subqueries
    Things to Remember
    7. Getting and Analyzing Metadata
    Item 44: Learn to Use Your System’s Query Analyzer
    IBM DB2
    Microsoft Access
    Microsoft SQL Server
    MySQL
    Oracle
    PostgreSQL
    Things to Remember
    Item 45: Learn to Get Metadata about Your Database
    Things to Remember
    Item 46: Understand How the Execution Plan Works
    Things to Remember
    8. Cartesian Products
    Item 47: Produce Combinations of Rows between Two Tables and Flag Rows in the Second That Indirectly Relate to the First
    Things to Remember
    Item 48: Understand How to Rank Rows by Equal Quantiles
    Things to Remember
    Item 49: Know How to Pair Rows in a Table with All Other Rows
    Things to Remember
    Item 50: Understand How to List Categories and the Count of First, Second, or Third Preferences
    Things to Remember
    9. Tally Tables
    Item 51: Use a Tally Table to Generate Null Rows Based on a Parameter
    Things to Remember
    Item 52: Use a Tally Table and Window Functions for Sequencing
    Things to Remember
    Item 53: Generate Multiple Rows Based on Range Values in a Tally Table
    Things to Remember
    Item 54: Convert a Value in One Table Based on a Range of Values in a Tally Table
    Things to Remember
    Item 55: Use a Date Table to Simplify Date Calculation
    Things to Remember
    Item 56: Create an Appointment Calendar Table with All Dates Enumerated in a Range
    Things to Remember
    Item 57: Pivot Data Using a Tally Table
    Things to Remember
    10. Modeling Hierarchical Data
    Item 58: Use an Adjacency List Model as the Starting Point
    Things to Remember
    Item 59: Use Nested Sets for Fast Querying Performance with Infrequent Updates
    Things to Remember
    Item 60: Use a Materialized Path for Simple Setup and Limited Searching
    Things to Remember
    Item 61: Use Ancestry Traversal Closure for Complex Searching
    Things to Remember
    Appendix. Date and Time Types, Operations, and Functions
    IBM DB2
    Data Types Supported
    Arithmetic Operations Supported
    Functions
    Microsoft Access
    Data Types Supported
    Arithmetic Operations Supported
    Functions
    Microsoft SQL Server
    Data Types Supported
    Arithmetic Operations Supported
    Functions
    MySQL
    Data Types Supported
    Arithmetic Operations Supported
    Functions
    Oracle
    Data Types Supported
    Arithmetic Operations Supported
    Functions
    PostgreSQL
    Data Types Supported
    Arithmetic Operations Supported
    Functions
    Index
    Code Snippets
    About This E-Book EPUB is an open, industry-standard format for e-books. However, support for EPUB and its many features varies across reading devices and applications. Use your device or app settings to customize the presentation to your liking. Settings that you can customize often include font, font size, single or double column, landscape or portrait mode, and figures that you can click or tap to enlarge. For additional information about the settings and features on your reading device or app, visit the device manufacturer’s Web site. Many titles include programming code or configuration examples. To optimize the presentation of these elements, view the e-book in single-column, landscape mode and adjust the font size to the smallest setting. In addition to presenting code and configurations in the reflowable text format, we have included images of the code that mimic the presentation found in the print book; therefore, where the reflowable format may compromise the presentation of the code listing, you will see a “Click here to view code image” link. Click the link to view the print-fidelity code image. To return to the previous page viewed, click the Back button on your device or app.
    Effective SQL 61 Specific Ways to Write Better SQL John L. Viescas Douglas J. Steele Ben G. Clothier Boston • Columbus • Indianapolis • New York • San Francisco • Amsterdam • Cape Town Dubai • London • Madrid • Milan • Munich • Paris • Montreal • Toronto • Delhi • Mexico City São Paulo • Sydney • Hong Kong • Seoul • Singapore • Taipei • Tokyo
    Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and the publisher was aware of a trademark claim, the designations have been printed with initial capital letters or in all capitals. The authors and publisher have taken care in the preparation of this book, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for incidental or consequential damages in connection with or arising out of the use of the information or programs contained herein. For information about buying this title in bulk quantities, or for special sales opportunities (which may include electronic versions; custom cover designs; and content particular to your business, training goals, marketing focus, or branding interests), please contact our corporate sales department at corpsales@pearsoned.com or (800) 382-3419. For government sales inquiries, please contact governmentsales@pearsoned.com. For questions about sales outside the U.S., please contact intlcs@pearson.com. Visit us on the Web: informit.com/aw Library of Congress Control Number: 2016955468 Copyright © 2017 Pearson Education, Inc. All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions, request forms and the appropriate contacts within the Pearson Education Global Rights & Permissions Department, please visit www.pearsoned.com/permissions/. Some of the examples used in this book originally appeared in SQL Queries for Mere Mortals®: A Hands-On Guide to Data Manipulation in SQL, Third Edition (Addison-Wesley, 2014). These examples appear with permission from the authors and Pearson Education Inc. ISBN-13: 978-0-13-457889-7 ISBN-10: 0-13-457889-9 1 16 Editor-in-Chief Greg Wiegand Senior Acquisitions Editor Trina MacDonald Development Editor Songlin Qiu Technical Reviewers Richard Anthony Broersma Jr.
    Craig S. Mullins Vivek Sharma Dave Stokes Morgan Tocker Managing Editor Sandra Schroeder Full-Service Production Manager Julie B. Nahil Project Editor Anna Popick Copy Editor Barbara Wood Indexer Richard Evans Proofreader Anna Popick Editorial Assistant Olivia Basegio Cover Designer Chuti Prasertsith Compositor The CIP Group
    Praise for Effective SQL “Given the reputation of the authors, I expected to be impressed. Impressed doesn’t cover it, though. I was blown away! Most SQL books tell you ‘how.’ This one tells you ‘why.’ Most SQL books separate database design from implementation. This one integrates design considerations into every facet of SQL use. Most SQL books sit on my shelf. This one will live on my desk.” —Roger Carlson, Microsoft Access MVP (2006–2015) “It can be easy to learn the basics of SQL, but it is very difficult to build accurate and efficient SQL, especially for critical systems with complex requirements. But now, with this great new book, you can get up to speed and write effective SQL much more quickly, no matter which DBMS you use.” —Craig S. Mullins, Mullins Consulting, Inc., DB2 Gold Consultant and IBM Champion for Analytics “This is a great book. It is written in language that can be understood by a relative beginner and yet contains tips and tricks that will benefit the most hardened workhorse. It will therefore appeal to readers across the whole range of expertise and should be in the library of anybody who is seriously concerned with designing, managing, or programming databases.” —Graham Mandeno, database consultant and Microsoft MVP (1996–2015) “This book is an excellent resource for database designers and developers working with relational and SQL-based databases—it’s an easy read with great examples that combine theory with practical examples seamlessly. Examples for top relational databases Oracle, DB2, SQL Server, MySQL, and PostgreSQL are included throughout. The book walks the reader through sophisticated techniques to deal with things such as hierarchical data and tally tables, along with explanations of the inner workings and performance implications of SQL using GROUP BY, EXISTS, IN, correlated and non-correlated subqueries, window functions, and joins. The tips you won’t find anywhere else, and the fun examples help to make this book stand out from the crowd.” —Tim Quinlan, database architect and Oracle Certified DBA “This book is good for those who need to support multiple dialects of SQL. It’s divided up into stand-alone items that you just grab and go. I have been doing SQL in various flavors since 1992 and even I picked up a few things.” —Tom Moreau, Ph.D., SQL Server MVP (2001–2012) “This book is a powerful, compact, and easily understandable presentation of how to use SQL—it shows the application of SQL to real-world questions in order to teach the construction of queries, and it explains the relationship of ‘how data is stored’ to ‘how data is queried’ so that you obtain results successfully and effectively.”
    —Kenneth D. Snell, Ph.D., database consultant and former Microsoft Access MVP “It has been problematic for many that there is no book on going from a novice database administrator to a much more advanced status until now. Effective SQL is a road map, a guide, a Rosetta Stone, and a coach on moving from basic Structured Query Language (SQL) to much more advanced uses to solve real-world problems. Rather than stumble around reinventing the wheel or catching glimpses of the proper ways to use a database, do yourself a favor and buy a copy of this book. Not only will you see many different approaches it would take years to see as a database consultant, but you will get a detailed understanding of why the databases of many vendors do what they do. Save time, effort, and wear and tear on your walls from banging your head against them and get this book.” —Dave Stokes, MySQL Community Manager, Oracle Corporation “Effective SQL is a ‘must have’ for any serious database developer. It shows how powerful SQL can be in solving real-world problems in a step-by-step manner. The authors use easy- to-understand language in pointing out every advantage and disadvantage of each solution presented in the book. As we all know, there are multiple ways of accomplishing the same thing in SQL, but the authors explain why a particular query is more efficient than others. The part I liked best about the book is the summary at the end of each section, which reemphasizes the take-away points and reminds the reader which pitfalls to avoid. I highly recommend this book to all my fellow database developers.” —Leo (theDBguy™), UtterAccess Moderator and Microsoft Access MVP “I think this is the book that is relevant not only for developers, but also for DBAs, as it talks about writing efficient SQL and various ways of achieving a desired result. In my opinion, this is a must-have book. Another reason to have this book is that it covers most of the commonly used RDBMSs, and so if someone is looking to transition from one RDBMS to another, this is the book to pick up. The authors have done a fantastic job. My heartiest congratulations to them.” —Vivek Sharma, technologist, Hybrid Cloud Solutions, Core Technology and Cloud, Oracle Asia Pacific
    分享到：
    赞 收藏

    相关推荐

    资料库

    数据库

    共收录1218份资料，累计14个分类，关注成员有19位，主要包括：NoSQL,MongoDB,Redis,SQLite,Memcached,DB2,Sybase,Informix,PostgreSQL,MySQL,SQLServer,Access,Oracle,其它

    热门标签

    NoSQL MongoDB Redis SQLite Memcached DB2 Sybase Informix PostgreSQL MySQL SQLServer Access Oracle 其它

    最新资料