| |
| |
| Preface to the Second Edition | |
| |
| |
| The Organization of This Book | |
| |
| |
| The CD and the Sample Database | |
| |
| |
| Acknowledgments | |
| |
| |
| Getting Started | |
| |
| |
| Installing and Running MySQL | |
| |
| |
| Linux | |
| |
| |
| Installing Under Linux | |
| |
| |
| Launching the Server Under Linux | |
| |
| |
| Windows | |
| |
| |
| Installing a Windows Distribution | |
| |
| |
| Starting the Server Under Windows | |
| |
| |
| Mac OS X | |
| |
| |
| Creating and Loading Databases | |
| |
| |
| Creating a New Database | |
| |
| |
| Starting the MySQL Daemon | |
| |
| |
| Running the Client | |
| |
| |
| Creating New Users | |
| |
| |
| Creating and Using the Database | |
| |
| |
| Creating the Tables | |
| |
| |
| Populating the Tables | |
| |
| |
| Introduction to SQL | |
| |
| |
| A Bit of SQL History | |
| |
| |
| Conformance Levels | |
| |
| |
| Relational Database Terminology and the Sample Database | |
| |
| |
| Basic Relational Database Concepts | |
| |
| |
| The Online Bookstore Database | |
| |
| |
| Manipulating Relations | |
| |
| |
| SQL Environments | |
| |
| |
| Interactive SQL Command Processors | |
| |
| |
| The Embedded SQL Dilemma | |
| |
| |
| Performing Interactive Data Manipulation | |
| |
| |
| Simple SQL Retrieval | |
| |
| |
| Choosing Columns | |
| |
| |
| Retrieving All Columns | |
| |
| |
| Retrieving Selected Columns | |
| |
| |
| Removing Duplicates | |
| |
| |
| The Relational Algebra Project Operation | |
| |
| |
| Ordering the Result Table | |
| |
| |
| Choosing Rows | |
| |
| |
| Predicates | |
| |
| |
| Performing Row Selection Queries | |
| |
| |
| The Relational Algebra Restrict Operation | |
| |
| |
| Nulls and Retrieval: Three-Valued Logic | |
| |
| |
| Retrieving Data from More Than One Table: Joins | |
| |
| |
| The Relational Algebra Join Operation | |
| |
| |
| A Non-Database Example | |
| |
| |
| Equi-Joins | |
| |
| |
| What's Really Going On: Product and Restrict | |
| |
| |
| SQL Syntax for Inner Joins | |
| |
| |
| Traditional SQL Joins | |
| |
| |
| SQL-92 Join Syntax | |
| |
| |
| Joining Using Concatenated Keys | |
| |
| |
| Joining More Than Two Tables | |
| |
| |
| Finding Multiple Rows in One Table: Joining a Table to Itself | |
| |
| |
| Correlation Names | |
| |
| |
| Performing the Same-Table Join | |
| |
| |
| Outer Joins | |
| |
| |
| The Relational Algebra Outer Join | |
| |
| |
| SQL Syntax for Outer Joins | |
| |
| |
| Valid versus Invalid Joins | |
| |
| |
| Avoiding Joins with Uncorrelated Subqueries | |
| |
| |
| Using the IN Operator | |
| |
| |
| Using the ANY Operator | |
| |
| |
| Nesting Subqueries | |
| |
| |
| Replacing a Same-Table Join with Subqueries | |
| |
| |
| Advanced Retrieval Operations | |
| |
| |
| Union | |
| |
| |
| The Relational Algebra Union Operation and Union Compatibility | |
| |
| |
| Performing a Query That Includes a Union | |
| |
| |
| Alternative SQL-92 Union Syntax | |
| |
| |
| Negative Queries | |
| |
| |
| The Relational Algebra Difference Operation | |
| |
| |
| Performing Queries That Require a Difference | |
| |
| |
| Performing Negative Queries Using MySQL | |
| |
| |
| The EXISTS Operator | |
| |
| |
| CASE Expressions | |
| |
| |
| Calculations and Grouping Queries | |
| |
| |
| Performing Arithmetic | |
| |
| |
| Arithmetic Operators | |
| |
| |
| Operator Precedence | |
| |
| |
| String Manipulations | |
| |
| |
| Concatenation | |
| |
| |
| UPPER and LOWER | |
| |
| |
| TRIM | |
| |
| |
| SUBSTRING | |
| |
| |
| Date and Time Manipulation | |
| |
| |
| Date and Time System Values | |
| |
| |
| Date and Time Arithmetic | |
| |
| |
| Set Functions | |
| |
| |
| COUNT | |
| |
| |
| SUM | |
| |
| |
| AVG | |
| |
| |
| MIN and MAX | |
| |
| |
| Set Functions in Predicates | |
| |
| |
| Changing Data Types: Using CAST | |
| |
| |
| Grouping Queries | |
| |
| |
| Forming Groups | |
| |
| |
| Restricting Groups | |
| |
| |
| Data Modification | |
| |
| |
| Inserting Rows | |
| |
| |
| Inserting One Row | |
| |
| |
| Copying Multiple Rows | |
| |
| |
| Placement of New Rows | |
| |
| |
| Updating Data | |
| |
| |
| Deleting Rows | |
| |
| |
| Deletes and Referential Integrity | |
| |
| |
| Managing Database Structure | |
| |
| |
| Schemas and Tables | |
| |
| |
| Database Object Hierarchy | |
| |
| |
| Naming and Identifying Objects | |
| |
| |
| Schemas | |
| |
| |
| Creating a Schema | |
| |
| |
| Identifying the Schema You Want to Use | |
| |
| |
| Domains | |
| |
| |
| Tables | |
| |
| |
| Column Data Types | |
| |
| |
| Default Values | |
| |
| |
| NOT NULL Constraints | |
| |
| |
| Primary Keys | |
| |
| |
| Foreign Keys | |
| |
| |
| Additional Column Constraints | |
| |
| |
| Views, Temporary Tables, and Indexes | |
| |
| |
| Views | |
| |
| |
| Why We Use Views | |
| |
| |
| Creating Views | |
| |
| |
| Querying Views | |
| |
| |
| View Updatability Issues | |
| |
| |
| Temporary Tables | |
| |
| |
| Creating Temporary Tables | |
| |
| |
| Loading Temporary Tables with Data | |
| |
| |
| Disposition of Temporary Table Rows | |
| |
| |
| Indexes | |
| |
| |
| Deciding Which Indexes to Create | |
| |
| |
| Creating Indexes | |
| |
| |
| Modifying Database Elements | |
| |
| |
| Adding New Columns | |
| |
| |
| Adding Table Constraints | |
| |
| |
| Modifying Columns | |
| |
| |
| Deleting Elements | |
| |
| |
| Renaming Elements | |
| |
| |
| Deleting Database Elements | |
| |
| |
| Granting and Revoking Access Rights | |
| |
| |
| Types of Access Rights | |
| |
| |
| Storing Access Rights | |
| |
| |
| Granting Rights | |
| |
| |
| Revoking Rights | |
| |
| |
| Program-Based Data Manipulation | |
| |
| |
| Users, Sessions, and Transaction Control | |
| |
| |
| The Concurrent Use Data Environment | |
| |
| |
| Muddying the Waters: Isolation Levels | |
| |
| |
| Database User IDs | |
| |
| |
| Database Sessions and Connections | |
| |
| |
| SQL for Connecting and Disconnecting | |
| |
| |
| Session Length Considerations | |
| |
| |
| Transaction Control | |
| |
| |
| Transaction Read/Write Permissions | |
| |
| |
| Transaction Termination | |
| |
| |
| Starting Transactions | |
| |
| |
| Ending Transactions | |
| |
| |
| Transaction Length Considerations | |
| |
| |
| Embedded SQL | |
| |
| |
| The Embedded SQL Environment | |
| |
| |
| Using Host Language Variables | |
| |
| |
| DBMS Return Codes | |
| |
| |
| Retrieving a Single Row | |
| |
| |
| Indicator Variables | |
| |
| |
| Retrieving Multiple Rows: Cursors | |
| |
| |
| Declaring a Cursor | |
| |
| |
| Opening a Cursor | |
| |
| |
| Fetching Rows | |
| |
| |
| Closing a Cursor | |
| |
| |
| Embedded SQL Data Modification | |
| |
| |
| Direct Modification | |
| |
| |
| Indicator Variables and Data Modification | |
| |
| |
| Modification Using Cursors | |
| |
| |
| Deletion Using Cursors | |
| |
| |
| Dynamic SQL | |
| |
| |
| Immediate Execution | |
| |
| |
| Dynamic SQL with Dynamic Parameters | |
| |
| |
| Creating the Statement String | |
| |
| |
| Setting up Parameter Descriptor Areas | |
| |
| |
| Preparing the Statement | |
| |
| |
| Describing, Setting, and Accessing Parameters | |
| |
| |
| Executing the Statement | |
| |
| |
| Removing Dynamic SQL Elements from Main Memory | |
| |
| |
| Additional Language Features | |
| |
| |
| Unimplemented SQL-92 Features | |
| |
| |
| Additional Relational Algebra Operations | |
| |
| |
| The Union Join | |
| |
| |
| Except | |
| |
| |
| Intersect | |
| |
| |
| Additional Predicate Operators | |
| |
| |
| UNIQUE | |
| |
| |
| OVERLAPS | |
| |
| |
| MATCH | |
| |
| |
| Table Constructors in Queries | |
| |
| |
| Additional Foreign Key Options | |
| |
| |
| Assertions | |
| |
| |
| Determining When Constraints Are Checked | |
| |
| |
| Changing the Constraint Mode | |
| |
| |
| Object-Oriented Features of the SQL:1999 Standard | |
| |
| |
| The Basics of Object Orientation | |
| |
| |
| Writing Instructions | |
| |
| |
| Objects | |
| |
| |
| Classes | |
| |
| |
| Class Relationships | |
| |
| |
| Benefits of Object Orientation | |
| |
| |
| SQL:1999 versus Pure Object-Orientation | |
| |
| |
| Object-Relational Support | |
| |
| |
| Typed Tables | |
| |
| |
| Creating Classes | |
| |
| |
| Creating and Using Classes for Domains | |
| |
| |
| Creating Typed Tables | |
| |
| |
| SQL:1999 Inheritance | |
| |
| |
| Querying with Classes | |
| |
| |
| Modifying Data for Classes as Domains | |
| |
| |
| Appendices | |
| |
| |
| The Online Bookstore Database | |
| |
| |
| SQLSTATE Return Codes | |
| |
| |
| SQL Syntax Summary | |
| |
| |
| SQL:1999 Core Features | |
| |
| |
| Glossary | |
| |
| |
| Index | |