SQL Clearly Explained - Elsevier

Transcription

SQL ClearlyExplained

SQL ClearlyExplainedThird EditionJan L. Harrington

Morgan Kaufmann Publishers is an imprint of Elsevier.30 Corporate Drive, Suite 400, Burlington, MA 01803, USAThis book is printed on acid-free paper. 2010 ELSEVIER INC. All rights reserved.No part of this publication may be reproduced or transmitted in any form or by any means, electronicor mechanical, including photocopying, recording, or any information storage and retrieval system,without permission in writing from the publisher. Details on how to seek permission, furtherinformation about the Publisher’s permissions policies and our arrangements with organizations suchas the Copyright Clearance Center and the Copyright Licensing Agency, can be found at ourwebsite: www.elsevier.com/permissions.This book and the individual contributions contained in it are protected under copyright by thePublisher (other than as may be noted herein).NoticesKnowledge and best practice in this field are constantly changing. As new research and experiencebroaden our understanding, changes in research methods, professional practices, or medicaltreatment may become necessary.Practitioners and researchers must always rely on their own experience and knowledge in evaluatingand using any information, methods, compounds, or experiments described herein. In using suchinformation or methods they should be mindful of their own safety and the safety of others, includingparties for whom they have a professional responsibility.To the fullest extent of the law, neither the Publisher nor the authors, contributors, or editors, assumeany liability for any injury and/or damage to persons or property as a matter of products liability,negligence or otherwise, or from any use or operation of any methods, products, instructions, orideas contained in the material herein.Library of Congress Cataloging-in-Publication DataHarrington, Jan L.SQL clearly explained / Jan L. Harrington. -- 3rd ed.p. cm.Includes indexes.ISBN 978-0-12-375697-81. SQL (Computer program language) I. Title.QA76.73.S67H37 2010005.13'3--dc222010009181British Library Cataloguing-in-Publication DataA catalogue record for this book is available from the British Library.For information on all Morgan Kaufmann publications,visit our Web site at www.mkp.com or www.elsevierdirect.comPrinted in the United States of America10 11 12 13 145 4 3 2 1

ContentsPart I: IntroductionChapter 1: The Relational Data Model 3Schemas and Entities 3Relations and Tables 5Columns and Rows 5Domains 7Primary Keys 7Nulls 9Base Versus Virtual Tables10Representing Relationships 10Types of Relationships 10Foreign Keys and Referential Integrity16Views 18The Design of the Sample Database 19Chapter 2: Relational Algebra 29Making Vertical Subsets: Project 31Making Horizontal Subsets: Restrict 33Choosing Columns and Rows: Restrict and Then Project 35Union 37Join 39A Non-Database Example 39The Equi-Join 40What’s Really Going On: Product and RestrictEqui-Joins Over Concatenated Keys 4542v

viContentsQ-Joins 49Outer Joins 50Valid versus Invalid Joins54Difference 58Intersect 59Divide 61Chapter 3: Introduction to SQL 65A Bit of SQL History 65Conformance Levels 67SQL Environments 69Interactive SQL Command ProcessorsGUI Environments 71The Embedded SQL Dilemma 7370Elements of a SQL Statement 73Part II: Interactive SQLChapter 4: Simple SQL Retrieval 77Choosing Columns 78Retrieving All Columns 78Retrieving Specific Columns 80Removing Duplicates 80Ordering the Result Table 82Choosing Rows 84Predicates 86Performing Row Selection Queries 96Nulls and Retrieval: Three-Valued Logic 101Chapter 5: Retrieving Data From More Than One Table 107SQL Syntax for Inner Joins 107Traditional SQL Joins 107SQL-92 Join Syntax 109Joining Using Concatenated Keys 111Joining More Than Two Tables 114Finding Multiple Rows in One Table: Joining a Table to Itself 116Correlation Names 117Performing the Same-Table JoinOuter Joins 121120

ContentsTable Constructors in Queries 124Avoiding Joins with Uncorrelated Subqueries 125Using the IN Operator 126Using the ANY Operator 127Nesting Subqueries 127Replacing a Same-Table Join with Subqueries129Chapter 6: Advanced Retrieval Operations 131Union 131Performing Union Using the Same Source Tables 132Performing Union Using Different Source Tables 134Alternative SQL-92 Union Syntax 135Negative Queries 136Traditional SQL Negative Queries 136Negative Queries Using the EXCEPT Operator1430The EXISTS Operator 140The INTERSECT Operator 142Performing Arithmetic 143Arithmetic Operators 145Operator Precedence 146String Manipulation 147Concatenation 147UPPER and LOWERTRIM 149SUBSTRING 149148Date and Time Manipulation 151Date and Time System Values 151Date and Time Interval Operations 152OVERLAPS 155EXTRACT 156CASE Expressions 156Chapter 7: Working with Groups of Rows 161Set Functions 161COUNT 163SUM 164AVG 166MIN and MAX 166Set Functions in Predicates167Changing Data Types: CAST 167vii

viiiContentsGrouping Queries 169Forming Groups 170Restricting Groups 175Windowing and Window Functions 178Ordering the PartitioningSpecific Functions 184179Chapter 8: Data Modification 197Inserting Rows 197Inserting One Row 197Copying Existing Rows 189Updating Data 201Deleting Rows 202Deletes and Referential Integrity203MERGE 204Part III: Managing Database StructureChapter 9: Schemas and Tables 211Database Object Hierarchy 211Naming and Identifying Objects213Schemas 214Creating a Schema 215Identifying the Schema You Want to Use 215Domains 216Tables 218Column Data Types 219Default Values 223NOT NULL Constraints 223Primary Keys 223Foreign Keys 223Additional Column Constraints233Assertions 233Determining When Constraints are Checked 234Changing the Constraint Mode 235

ContentsChapter 10: Views, Temporary Tables, CTEs, and Indexes 237Views 237Why Use Views 237Creating Views 238Querying Views 239View Updatability Issues240Temporary Tables 241Creating Temporary Tables 245Loading Temporary Tables with Data 245Disposition of Temporary Table Rows 246Common Table Expressions (CTEs) 244Indexes 248Deciding Which Indexes to CreateCreating Indexes 252250Chapter 11: Keeping the Design Up to Date 255Modifying Tables 255Adding New Columns 256Adding Table Constraints 256Modifying Columns 257Deleting Table Elements 258Renaming Table Elements 259Modifying Domains 259Deleting Database Elements 260Chapter 12: Users and Access Rights 263Managing User Accounts 263Granting and Revoking Access Rights 265Types of Access Rights 266Storing Access Rights 267Granting Rights 268Revoking Rights 269Roles 270Chapter 13: Users, Sessions, and Transaction Control 273The Concurrent Use Data Environment 273Muddying the Waters: Isolation Levels277Database Sessions and Connections 278SQL for Connecting and Disconnecting 278Session Length Considerations 279ix

xContentsTransaction Control 280Transaction Read/Write Permissions 280Transaction Termination 281Starting Transactions 281Ending Transactions 282Transaction Length Considerations 282Part IV: SQL ProgrammingChapter 14: Writing and Executing SQL Routines and Modules—Triggers and StoredProcedures 287SQL Programming Elements 288Variables and Assignment 290Selection 292Iteration 295Example #1: Interactive Retrievals 297Example #2: Nested Modules 298Executing Modules as Triggers 298Executing Modules as Stored Procedures 300Chapter 15: Embedded SQL 301The Embedded SQL Environment 301Using Host Language Variables 304DBMS Return Codes 305Retrieving a Single Row 306Indicator Variables308Retrieving Multiple Rows: Cursors 309Declaring a Cursor 311Opening a Cursor 314Fetching Rows 314Closing a Cursor 317Embedded SQL Data Modification 317Direct Modification 317Indicator Variables and Data Modification 318Integrity Validation with the Match Predicate 320Modification Using Cursors 321Deletion Using Cursors 322

ContentsChapter 16: Dynamic SQL 323Immediate Execution 323Dynamic SQL with Dynamic Parameters 326Dynamic Parameters with Cursors 327Dynamic Parameters and without a Cursor334Part V: Non-Relational SQL ExtensionsChapter 17: XML Support 339XML Basics 340XML Structure 340XML Document StructureXML Schemas 346SQL/XML 348XML Publishing Functions343348The XML Data Type 358XMLSERIALIZE361Chapter 18: The Object-Relational Data Model 363Getting Started: Object-Orientation Without Computing 364Basic OO Concepts 366Objects 367Classes 371Class Relationships376Benefits of Object-Orientation 384Where Objects Work Better Than Relations384Pure Object-Oriented Databases 390Representing Data Relationships 390Navigating the Relationships 391The Object-Relational Data Model 392ER Diagrams for Object-Relational DesignsFeatures of the OR Data Model 398392Chapter 19: Object-Relational Support 401An Additional Sample Database 402SQL Data Types for Object-Relational Support 402Row Type 402Array Type 404Multiset Type 406xi

xiiContentsUser-Defined Data Types and Typed Tables 409UDTs as Domains 409UDTs as Classes 410Creating Typed Tables Using UDTs 410Inheritance 411Reference (REF) Type 412Methods 414Defining Methods 415Executing Methods 415Part VI: AppendicesAppendix A: Common Acronyms and Abbreviations 419Appendix B: SQLSTATE Return Codes 421Appendix C: SQL Syntax Summary 433Glossay 445Index 455

Preface to the ThirdEditionIf you have had any contact with a relational database, then itis very likely that you have seen the letters “SQL.” SQL (Structured Query Language) is a computer language designed tomanipulate relational databases. You can use it to define a database’s structure, to modify data, and to retrieve data.This book has been written to give you an in-depth introduction to using SQL, providing a gentle but complete approachto learning the language. You will learn not only SQL syntax,but also how SQL works. Understanding the “how” as well asthe “what” will help you create SQL statements that execute asquickly as possible.The elements of the SQL language covered in the first fourparts of this book are based on those parts of the SQL standardthat are for use with pure relational databases. Part V coverstwo non-relational extensions (XML and object-relational capabilities) that have been part of SQL since 2003. Virtually alldatabase management systems that support SQL will providethe bulk of what you will find in Parts I–IV; implementationsof the features in Part V are less common and tend to varyfrom the standard.There have been some substantial enhancements to the SQLstandard since the second edition of this book, both in thexiii

xivPrefacerelational core features and the non-relational features. Thesefeatures have been integrated throughout this third edition.Organization of This BookThe five parts of this book take you from theory to practice: Part I: The theoretical material underlying relationaldatabases and SQL has been moved into two chaptersat the beginning of the book. In previous editions, thematerial in Chapter 2 (relational algebra) was scatteredthroughout the book. This organization should make iteasier to find. The third chapter in Part I provides anoverview of SQL environments. Part II: Part II covers interactive SQL retrieval. At first,this might seem backwards. Why discuss retrieving databefore creating a database and getting data into that database? There is actually a very good reason for this.SQL presents someone trying to learn the language witha bit of a catch-22. You need to know how to retrievedata before you can modify it, because modifying datameans finding the data you want to change. On theother hand, you need to be able to create a database andenter some data before you have some data on whichyou can perform retrievals. Like Yossarian trying to meetwith Major Major, it doesn’t seem that you can win!The best alternative is to have someone who knows howto do it create a sample database and load it with datafor you. Then you can learn to query that database andcarry those techniques over to modifying data. At thatpoint, you’ll have an understanding of SQL basics andwill be ready to learn to create databases.

Preface Part III: Part III discusses creating and managing database structure. It also covers non-data elements in thedatabase environment, such as managing users/ user accounts and transaction control. Part IV: When SQL-based database environments arebeing developed, programmers and database administrators do a lot of work using a command-line interface.There are, however, at least two reasons why SQL programming is very common:o The typical end-user should not (or cannot) workdirectly from the SQL command line. We therefore create application programs to isolate themfrom direct interaction with the SQL commandprocessor by writing application programs forthem to use.o In many cases, there are actions the database shouldperform in specific circumstances. We don’t wantto require users to remember to do these actions,so we write blocks of program code that are storedwithin the database to be executed automatically atthe appropriate time.Part IV introduces several techniques for SQL programming: embedded SQL (using a high-level host language), dynamic SQL, and triggers/stored procedures.These chapters teach you syntax of SQL programmingconstructs, but do not teach programming. Part V: Part V discusses the non-relational extensionsthat have been added to the SQL standard: XML andobject-relational capabilities. Just as Chapter 1 presents a brief introduction to the relational data model,Chapter 18 covers object-oriented concepts, includingthe differences between pure object-oriented databasesxv

xviPrefaceand object-relational databases. Chapter 19 then looksat SQL’s object-relational features.Database SoftwareMuch of today’s commercial database software is very expensive and requires expensive hardware on which to run. Ifyou are looking for a database management system for yourown use, you needn’t purchase anything should you choosenot to. There are at least two open-source products that willrun on reasonable hardware configurations: mySQL (http://www. mysql.com) and PostgreSQL (http://www.postgresql.org). Both are certainly used in commercial settings, but canalso function well as learning environments. Distributions areavailable for Windows, Linux, and Mac OS X.The SQL commands to create the sample database used in thefirst four parts of this book and the SQL commands to insertdata into those tables can be downloaded from the MorganKaufmann Web site.Teaching MaterialsIf you are using this book as a college text (perhaps jointly withits companion volume, Relational Database Design and Implementation Clearly Explained), you can find teaching supportmaterials on the Morgan Kaufmann Web site. These includea sample syllabus, assignments (and where appropriate, solutions), a project description, and exams.AcknowledgementsAlthough an author spends a lot of time alone in front of thecomputer, no book can come into being without the cooperation and hard work of many people. It may be my name on

Prefacethe cover, but without the people at Morgan Kaufmann, youwouldn’t be holding this book right now.First I’d like to thank the editorial staff, Rick Adams (SeniorAcquisitions Editor) and Heather Scherer (Assistant Editor).You’re a joy to work with (as always). Second, I am forevergrateful for the production staff, who have done everythingthey can to make my life easier and to produce a great volume:Anne McGee (Project Manager), Joanne Blank (Designer),and Carol Lewis (Copyeditor).I also can’t forget my support staff: my mother, my son, andthe four fur kids. (Now, if the kittens could just distinguishbetween my leg and a scratching post, my world would be atpeace.)xvii

SQL clearly explained / Jan L. Harrington. -- 3rd ed. p. cm. Includes indexes. ISBN 978-0-12-375697-8 . The Design of the Sample Database 19 Chapter 2: Relational Algebra 29 Making Vertical Subsets: Project 31 . If you have had any contact with a relational database, then it is