Table of Contents

MySQL® 5 Certification Study Guide
By Paul DuBois, Stefan Hinz, Carsten Pedersen
...............................................
Publisher: Sams Publishing
Pub Date: August 24, 2005
ISBN: 0-672-32812-7
Pages: 672

Table of Contents  | Index

---

		Copyright
		Foreword
		About the Authors
		Acknowledgments
		References
		We Want to Hear from You!
		Introduction
			About This Book
			Sample Exercises
			Other Required Reading
			Conventions Used in This Book
			About the Exams
			Interpreting DESCRIBE Output
			Sample Tables
		MySQL Developer Exams
		MySQL Developer I Exam
			Chapter 1.  Client/Server Concepts
				Section 1.1.  General MySQL Architecture
				Section 1.2.  Invoking Client Programs
				Section 1.3.  Server SQL Modes
			Chapter 2.  The mysql Client Program
				Section 2.1.  Using mysql Interactively
				Section 2.2.  Statement Terminators
				Section 2.3.  The mysql Prompts
				Section 2.4.  Using Editing Keys in mysql
				Section 2.5.  Using Script Files with mysql
				Section 2.6.  mysql Output Formats
				Section 2.7.  Client Commands and SQL Statements
				Section 2.8.  Using Server-Side Help
				Section 2.9.  Using the --safe-updates Option
			Chapter 3.  MySQL Query Browser
				Section 3.1.  MySQL Query Browser Capabilities
				Section 3.2.  Using MySQL Query Browser
				Section 3.3.  Using the Query Window
				Section 3.4.  The MySQL Table Editor
				Section 3.5.  Connection Management
				Section 3.6.  The Options Dialog
			Chapter 4.  MySQL Connectors
				Section 4.1.  MySQL Client Interfaces
				Section 4.2.  MySQL Connector/ODBC
				Section 4.3.  MySQL Connector/J
				Section 4.4.  MySQL Connector/NET
			Chapter 5.  Data Types
				Section 5.1.  Data Type Overview
				Section 5.2.  Numeric Data Types
				Section 5.3.  The BIT Data Type
				Section 5.4.  String Data Types
				Section 5.5.  Temporal Data Types
				Section 5.6.  Column Attributes
				Section 5.7.  Using the AUTO_INCREMENT Column Attribute
				Section 5.8.  Handling Missing or Invalid Data Values
			Chapter 6.  Identifiers
				Section 6.1.  Identifier Syntax
				Section 6.2.  Case Sensitivity of Identifiers
				Section 6.3.  Using Qualified Names
				Section 6.4.  Using Reserved Words as Identifiers
			Chapter 7.  Databases
				Section 7.1.  Database Properties
				Section 7.2.  Creating Databases
				Section 7.3.  Altering Databases
				Section 7.4.  Dropping Databases
				Section 7.5.  Obtaining Database Metadata
			Chapter 8.  Tables and Indexes
				Section 8.1.  Table Properties
				Section 8.2.  Creating Tables
				Section 8.3.  Altering Tables
				Section 8.4.  Dropping Tables
				Section 8.5.  Emptying Tables
				Section 8.6.  Indexes
				Section 8.7.  Dropping Indexes
				Section 8.8.  Obtaining Table and Index Metadata
			Chapter 9.  Querying for Data
				Section 9.1.  Using SELECT to Retrieve Data
				Section 9.2.  Specifying Which Columns to Retrieve
				Section 9.3.  Specifying Which Rows to Retrieve
				Section 9.4.  Aggregating Results
				Section 9.5.  Grouping Results
				Section 9.6.  Using UNION
			Chapter 10.  SQL Expressions
				Section 10.1.  Components of SQL Expressions
				Section 10.2.  Numeric Expressions
				Section 10.3.  String Expressions
				Section 10.4.  Temporal Expressions
				Section 10.5.  NULL Values
				Section 10.6.  Functions in SQL Expressions
				Section 10.7.  Comments in SQL Statements
			Chapter 11.  Updating Data
				Section 11.1.  Update Operations
				Section 11.2.  The INSERT Statement
				Section 11.3.  The REPLACE Statement
				Section 11.4.  The UPDATE Statement
				Section 11.5.  The DELETE and TRUNCATE TABLE Statements
				Section 11.6.  Privileges Required for Update Statements
		MySQL Developer II Exam
			Chapter 12.  Joins
				Section 12.1.  Overview
				Section 12.2.  Writing Inner Joins
				Section 12.3.  Writing Outer Joins
				Section 12.4.  Resolving Name Clashes Using Qualifiers and Aliases
				Section 12.5.  Multiple-Table UPDATE and DELETE Statements
			Chapter 13.  Subqueries
				Section 13.1.  Types of Subqueries
				Section 13.2.  Subqueries as Scalar Expressions
				Section 13.3.  Correlated Subqueries
				Section 13.4.  Comparing Subquery Results to Outer Query Columns
				Section 13.5.  Comparison Using Row Subqueries
				Section 13.6.  Using Subqueries in the FROM Clause
				Section 13.7.  Converting Subqueries to Joins
				Section 13.8.  Using Subqueries in Updates
			Chapter 14.  Views
				Section 14.1.  Reasons to Use Views
				Section 14.2.  Creating Views
				Section 14.3.  Altering Views
				Section 14.4.  Dropping Views
				Section 14.5.  Checking Views
				Section 14.6.  Obtaining View Metadata
				Section 14.7.  Privileges Required for Views
			Chapter 15.  Importing and Exporting Data
				Section 15.1.  Import and Export Operations
				Section 15.2.  Importing and Exporting Using SQL
				Section 15.3.  Importing and Exporting Data from the Command Line
			Chapter 16.  User Variables
				Section 16.1.  User Variable Syntax
				Section 16.2.  User Variable Properties
			Chapter 17.  Prepared Statements
				Section 17.1.  Benefits of Prepared Statements
				Section 17.2.  Using Prepared Statements from the mysql Client
				Section 17.3.  Preparing a Statement
				Section 17.4.  Executing a Prepared Statement
				Section 17.5.  Deallocating Prepared Statements
			Chapter 18.  Stored Procedures and Functions
				Section 18.1.  Benefits of Stored Routines
				Section 18.2.  Differences Between Stored Procedures and Functions
				Section 18.3.  The Namespace for Stored Routines
				Section 18.4.  Defining Stored Routines
				Section 18.5.  Creating Stored Routines
				Section 18.6.  Altering Stored Routines
				Section 18.7.  Dropping Stored Routines
				Section 18.8.  Invoking Stored Routines
				Section 18.9.  Obtaining Stored Routine Metadata
				Section 18.10.  Stored Routine Privileges and Execution Security
			Chapter 19.  Triggers
				Section 19.1.  Reasons to Use Triggers
				Section 19.2.  Trigger Concepts
				Section 19.3.  Creating a Trigger
				Section 19.4.  Restrictions on Triggers
				Section 19.5.  Referring to Old and New Column Values
				Section 19.6.  Destroying a Trigger
				Section 19.7.  Privileges Required for Triggers
			Chapter 20.  Obtaining Database Metadata
				Section 20.1.  Overview of Metadata Access Methods
				Section 20.2.  Using INFORMATION_SCHEMA to Obtain Metadata
				Section 20.3.  Using SHOW and DESCRIBE to Obtain Metadata
				Section 20.4.  Using mysqlshow to Obtain Metadata
			Chapter 21.  Debugging MySQL Applications
				Section 21.1.  Interpreting Error Messages
				Section 21.2.  The SHOW WARNINGS Statement
				Section 21.3.  The SHOW ERRORS Statement
				Section 21.4.  The perror Utility
			Chapter 22.  Basic Optimizations
				Section 22.1.  Overview of Optimization Principles
				Section 22.2.  Using Indexes for Optimization
				Section 22.3.  General Query Enhancement
				Section 22.4.  Choosing Appropriate Storage Engines
				Section 22.5.  Normalization
		MySQL DBA Exams
		MySQL DBA I Exam
			Chapter 23.  MySQL Architecture
				Section 23.1.  Client/Server Overview
				Section 23.2.  Communication Protocols
				Section 23.3.  The SQL Parser and Storage Engine Tiers
				Section 23.4.  How MySQL Uses Disk Space
				Section 23.5.  How MySQL Uses Memory
			Chapter 24.  Starting, Stopping, and Configuring MySQL
				Section 24.1.  Types of MySQL Distributions
				Section 24.2.  Starting and Stopping MySQL Server on Windows
				Section 24.3.  Starting and Stopping MySQL Server on Unix
				Section 24.4.  Runtime MySQL Configuration
				Section 24.5.  Log and Status Files
				Section 24.6.  Loading Time Zone Tables
				Section 24.7.  Security-Related Configuration
				Section 24.8.  Setting the Default SQL Mode
				Section 24.9.  Upgrading MySQL
			Chapter 25.  Client Programs for DBA Work
				Section 25.1.  Overview of Administrative Clients
				Section 25.2.  MySQL Administrator
				Section 25.3.  mysql
				Section 25.4.  mysqladmin
				Section 25.5.  mysqlimport
				Section 25.6.  mysqldump
				Section 25.7.  Client Program Limitations
			Chapter 26.  MySQL Administrator
				Section 26.1.  MySQL Administrator Capabilities
				Section 26.2.  Using MySQL Administrator
				Section 26.3.  Server Monitoring Capabilities
				Section 26.4.  Server Configuration
				Section 26.5.  Backup and Restore Capabilities
				Section 26.6.  MySQL Administrator System Tray Monitor
			Chapter 27.  Character Set Support
				Section 27.1.  Performance Issues
				Section 27.2.  Choosing Data Types for Character Columns
			Chapter 28.  Locking
				Section 28.1.  Locking Concepts
				Section 28.2.  Explicit Table Locking
				Section 28.3.  Advisory Locking
			Chapter 29.  Storage Engines
				Section 29.1.  MySQL Storage Engines
				Section 29.2.  The MyISAM Engine
				Section 29.3.  The MERGE Engine
				Section 29.4.  The InnoDB Engine
				Section 29.5.  The MEMORY Engine
				Section 29.6.  The FEDERATED Engine
				Section 29.7.  The Cluster Storage Engine
				Section 29.8.  Other Storage Engines
			Chapter 30.  Table Maintenance
				Section 30.1.  Types of Table Maintenance Operations
				Section 30.2.  SQL Statements for Table Maintenance
				Section 30.3.  Client and Utility Programs for Table Maintenance
				Section 30.4.  Repairing InnoDB Tables
				Section 30.5.  Enabling MyISAM Auto-Repair
			Chapter 31.  The INFORMATION_SCHEMA Database
				Section 31.1.  INFORMATION_SCHEMA Access Syntax
				Section 31.2.  INFORMATION_SCHEMA Versus SHOW
				Section 31.3.  Limitations of INFORMATION_SCHEMA
			Chapter 32.  Data Backup and Recovery Methods
				Section 32.1.  Introduction
				Section 32.2.  Binary Versus Textual Backups
				Section 32.3.  Making Binary Backups
				Section 32.4.  Making Text Backups
				Section 32.5.  Backing Up Log and Status Files
				Section 32.6.  Replication as an Aid to Backup
				Section 32.7.  MySQL Cluster as Disaster Prevention
				Section 32.8.  Data Recovery
		MySQL DBA II Exam
			Chapter 33.  Using Stored Routines and Triggers for Administration
				Section 33.1.  Using Stored Routines and Triggers for Security Purposes
				Section 33.2.  Using Stored Routines to Enhance Performance
			Chapter 34.  User Management
				Section 34.1.  User Account Management
				Section 34.2.  Client Access Control
			Chapter 35.  Securing the MySQL Installation
				Section 35.1.  Security Issues
				Section 35.2.  Operating System Security
				Section 35.3.  Filesystem Security
				Section 35.4.  Log Files and Security
				Section 35.5.  Network Security
				Section 35.6.  FEDERATED Table Security
			Chapter 36.  Upgrade-Related Security Issues
				Section 36.1.  Upgrading the Privilege Tables
				Section 36.2.  Security-Related SQL Mode Values
			Chapter 37.  Optimizing Queries
				Section 37.1.  Identifying Candidates for Query Analysis
				Section 37.2.  Using EXPLAIN to Analyze Queries
				Section 37.3.  Using SHOW WARNINGS for Optimization
				Section 37.4.  MyISAM Index Caching
			Chapter 38.  Optimizing Databases
				Section 38.1.  General Table Optimizations
				Section 38.2.  Normalization
				Section 38.3.  MyISAM-Specific Optimizations
				Section 38.4.  InnoDB-Specific Optimizations
				Section 38.5.  MERGE-Specific Optimizations
				Section 38.6.  MEMORY-Specific Optimizations
			Chapter 39.  Optimizing the Server
				Section 39.1.  Interpreting mysqld Server Information
				Section 39.2.  Measuring Server Load
				Section 39.3.  Tuning Memory Parameters
				Section 39.4.  Using the Query Cache
			Chapter 40.  Interpreting Diagnostic Messages
				Section 40.1.  Sources of Diagnostic Information
				Section 40.2.  Using the Error Log for Diagnostic Purposes
				Section 40.3.  Using The Slow Query Log for Diagnostic Purposes
			Chapter 41.  Optimizing the Environment
				Section 41.1.  Choosing Hardware for MySQL Use
				Section 41.2.  Configuring Disks for MySQL Use
				Section 41.3.  Network Issues
				Section 41.4.  Optimizing the Operating System for MySQL Use
			Chapter 42.  Scaling MySQL
				Section 42.1.  Using Multiple Servers
				Section 42.2.  Replication
		Appendixes
			Appendix A.  References
			Appendix B.  Other Offers
		Index