| |
| |
| Foreword | |
| |
| |
| Acknowledgments | |
| |
| |
| Introduction | |
| |
| |
| |
| Installation | |
| |
| |
| |
| Introduction to Databases | |
| |
| |
| The Big Picture | |
| |
| |
| ...And the Little Database that Could | |
| |
| |
| History and Evolution | |
| |
| |
| Features | |
| |
| |
| Applications | |
| |
| |
| Summary | |
| |
| |
| |
| A Technical Tour of MySQL | |
| |
| |
| An Overview of MySQL Architecture | |
| |
| |
| Primary Subsystems | |
| |
| |
| Support Components | |
| |
| |
| Subsystem/Component Interaction and Control Flow | |
| |
| |
| The MySQL Engine | |
| |
| |
| Connectivity | |
| |
| |
| SQL | |
| |
| |
| Data Integrity | |
| |
| |
| Transactions | |
| |
| |
| Extensibility | |
| |
| |
| Symmetric Multiprocessing with MySQL | |
| |
| |
| Security | |
| |
| |
| Replication | |
| |
| |
| Application Programming Interfaces | |
| |
| |
| Add-On Tools | |
| |
| |
| Summary | |
| |
| |
| |
| MySQL Installation and Configuration | |
| |
| |
| Obtaining MySQL | |
| |
| |
| Choosing Which Version to Install | |
| |
| |
| Choosing Between Binary and Source Distributions | |
| |
| |
| Installing and Configuring MySQL | |
| |
| |
| Installing and Configuring MySQL on UNIX | |
| |
| |
| Installing and Configuring MySQL on Windows | |
| |
| |
| Testing MySQL | |
| |
| |
| Post-Installation Steps | |
| |
| |
| Summary | |
| |
| |
| |
| Usage | |
| |
| |
| |
| SQL Basics | |
| |
| |
| A Brief History of SQL | |
| |
| |
| An Overview of SQL | |
| |
| |
| A (My)SQL Tutorial | |
| |
| |
| Understanding an RDBMS | |
| |
| |
| Creating a Database | |
| |
| |
| Adding Tables | |
| |
| |
| Adding Records | |
| |
| |
| Removing and Modifying Records | |
| |
| |
| Executing Queries | |
| |
| |
| Summary | |
| |
| |
| |
| MySQL Data Types | |
| |
| |
| The Need and Rationale for Data Types | |
| |
| |
| MySQL Data Types | |
| |
| |
| Numeric Types | |
| |
| |
| String Types | |
| |
| |
| Date and Time Types | |
| |
| |
| Complex Types | |
| |
| |
| Data Type Selection | |
| |
| |
| Summary | |
| |
| |
| |
| MySQL Operators | |
| |
| |
| Arithmetic Operators | |
| |
| |
| Comparison Operators | |
| |
| |
| Logical Operators | |
| |
| |
| Bit Operators | |
| |
| |
| Summary | |
| |
| |
| |
| MySQL Functions | |
| |
| |
| Math Functions | |
| |
| |
| Aggregate Functions | |
| |
| |
| String Functions | |
| |
| |
| Date and Time Functions | |
| |
| |
| Encryption Functions | |
| |
| |
| Control Flow Functions | |
| |
| |
| Formatting Functions | |
| |
| |
| Type Conversion Functions | |
| |
| |
| System Information Functions | |
| |
| |
| Summary | |
| |
| |
| |
| Working with Databases and Tables | |
| |
| |
| Creating Databases | |
| |
| |
| Selecting Databases for Use | |
| |
| |
| Deleting Databases | |
| |
| |
| Creating Tables | |
| |
| |
| Field Types | |
| |
| |
| Field Constraints | |
| |
| |
| Indexes | |
| |
| |
| Primary Keys | |
| |
| |
| Foreign Keys | |
| |
| |
| Table Types | |
| |
| |
| Other Table Modifiers | |
| |
| |
| Copying Tables | |
| |
| |
| Modifying Tables | |
| |
| |
| Deleting Tables | |
| |
| |
| Obtaining Information About Databases, Tables, Fields, and Indexes | |
| |
| |
| Summary | |
| |
| |
| |
| Working with Data | |
| |
| |
| Inserting, Updating, and Deleting Records | |
| |
| |
| Inserting Records | |
| |
| |
| Updating Records | |
| |
| |
| Deleting Records | |
| |
| |
| Retrieving Records | |
| |
| |
| Retrieving Specific Rows and Columns | |
| |
| |
| Using Built-In Functions | |
| |
| |
| Aliasing Table and Column Names | |
| |
| |
| Limiting Query Results | |
| |
| |
| Sorting Query Results | |
| |
| |
| Grouping Query Results | |
| |
| |
| Using Variables | |
| |
| |
| Using Subqueries | |
| |
| |
| Controlling SELECT Behavior | |
| |
| |
| Copying, Importing, and Exporting Records | |
| |
| |
| Copying Records | |
| |
| |
| Importing Records | |
| |
| |
| Exporting Records | |
| |
| |
| Summary | |
| |
| |
| |
| Joins | |
| |
| |
| What Is a Join? | |
| |
| |
| Types of Joins | |
| |
| |
| Cross Joins | |
| |
| |
| Inner Joins | |
| |
| |
| Outer Joins | |
| |
| |
| Self Joins | |
| |
| |
| Unions | |
| |
| |
| Summary | |
| |
| |
| |
| Subqueries | |
| |
| |
| What Is a Subquery? | |
| |
| |
| Types of Subqueries | |
| |
| |
| Subqueries and the Where/Having Clause | |
| |
| |
| Subqueries and the From Clause | |
| |
| |
| Subqueries and Joins | |
| |
| |
| Subqueries and Other DML Statements | |
| |
| |
| Summary | |
| |
| |
| |
| Transactions | |
| |
| |
| What Is a Transaction? | |
| |
| |
| Transactions and the ACID Properties | |
| |
| |
| Life Cycle of a Transaction | |
| |
| |
| Controlling Transactional Behavior | |
| |
| |
| Automatic Commits | |
| |
| |
| Transaction Isolation Levels | |
| |
| |
| Transactions and Performance | |
| |
| |
| Pseudotransactions with Nontransactional Tables | |
| |
| |
| Table Locks as a Substitute for Transactions | |
| |
| |
| Implementing a Pseudotransaction with Table Locks | |
| |
| |
| Summary | |
| |
| |
| |
| Administration | |
| |
| |
| |
| Administration and Configuration | |
| |
| |
| Database Administration and MySQL | |
| |
| |
| Uptime | |
| |
| |
| Data Backup | |
| |
| |
| Security and Access Control | |
| |
| |
| Performance Optimization | |
| |
| |
| Basic Server Administration and Configuration Tasks | |
| |
| |
| Starting and Stopping the Server | |
| |
| |
| Checking MySQL Server Status | |
| |
| |
| Managing MySQL Client Processes | |
| |
| |
| Altering the Server Configuration | |
| |
| |
| Troubleshooting with the Error Log | |
| |
| |
| Summary | |
| |
| |
| |
| Security, Access Control, and Privileges | |
| |
| |
| The MySQL Grant Tables | |
| |
| |
| The user Table | |
| |
| |
| The db and host Tables | |
| |
| |
| The tables_priv and columns_priv Tables | |
| |
| |
| Granting, Revoking, and Viewing User Privileges | |
| |
| |
| Using the Grant and Revoke Commands | |
| |
| |
| Using the Insert, Update, and Delete Commands | |
| |
| |
| Viewing Privileges | |
| |
| |
| Reloading the Grant Tables | |
| |
| |
| Resetting the Grant Tables | |
| |
| |
| Changing User Passwords | |
| |
| |
| Setting the root Password | |
| |
| |
| Resetting the root Password | |
| |
| |
| Summary | |
| |
| |
| |
| Maintenance, Backup, and Recovery | |
| |
| |
| Maintenance | |
| |
| |
| Logging | |
| |
| |
| Checking and Repairing Tables | |
| |
| |
| Backup and Restore | |
| |
| |
| Backing Up Databases and Tables | |
| |
| |
| Restoring Databases and Tables from Backup | |
| |
| |
| Summary | |
| |
| |
| |
| Performance Optimization | |
| |
| |
| Indexing | |
| |
| |
| Query Caching | |
| |
| |
| Query Analysis | |
| |
| |
| Optimizing Multi-Table Queries | |
| |
| |
| Using Temporary Tables | |
| |
| |
| Optimizing Table Design | |
| |
| |
| Adjusting Server Settings | |
| |
| |
| Benchmarking | |
| |
| |
| Summary | |
| |
| |
| |
| MySQL Replication | |
| |
| |
| Replication Basics | |
| |
| |
| The Master-Slave Relationship | |
| |
| |
| Replication Threads | |
| |
| |
| Configuring MySQL for Replication | |
| |
| |
| Managing the Replication Process | |
| |
| |
| Summary | |
| |
| |
| |
| Development | |
| |
| |
| |
| The MySQL APIs | |
| |
| |
| The MySQL APIs In Context | |
| |
| |
| Components of the MySQL API | |
| |
| |
| Language Support | |
| |
| |
| Selecting an API | |
| |
| |
| Summary | |
| |
| |
| |
| MySQL and C | |
| |
| |
| C | |
| |
| |
| History and Evolution | |
| |
| |
| Installation | |
| |
| |
| MySQL and C | |
| |
| |
| Connection Management | |
| |
| |
| Query Execution | |
| |
| |
| Result Set Processing | |
| |
| |
| Error Handling | |
| |
| |
| Ancillary Functions | |
| |
| |
| Real-World Usage | |
| |
| |
| The Interactive SQL Client | |
| |
| |
| The Expense Tracker | |
| |
| |
| Summary | |
| |
| |
| |
| MySQL and Perl | |
| |
| |
| Perl | |
| |
| |
| History and Evolution | |
| |
| |
| Installation | |
| |
| |
| MySQL and the Perl DBI | |
| |
| |
| Connection Management | |
| |
| |
| Query Execution | |
| |
| |
| Result Set Processing | |
| |
| |
| Error Handling | |
| |
| |
| Ancillary Functions | |
| |
| |
| A Real-World Example | |
| |
| |
| Designing the Database | |
| |
| |
| Building an Article Index | |
| |
| |
| Retrieving Article Contents | |
| |
| |
| Adding Comments | |
| |
| |
| Building a Threaded Comment Index | |
| |
| |
| Viewing Comments | |
| |
| |
| Summary | |
| |
| |
| |
| MySQL and PHP | |
| |
| |
| PHP: History and Evolution | |
| |
| |
| Installation | |
| |
| |
| MySQL and PHP | |
| |
| |
| Connection Management | |
| |
| |
| Query Execution | |
| |
| |
| Result Set Processing | |
| |
| |
| Error Handling | |
| |
| |
| Ancillary Functions | |
| |
| |
| A Real-World Example | |
| |
| |
| Designing the Database | |
| |
| |
| Retrieving Data | |
| |
| |
| Adding Data | |
| |
| |
| Removing Data | |
| |
| |
| Summary | |
| |
| |
| Index | |