This is the official guide to passing the two MySQL certification tests for MySQL 5, the long-awaited major revision of MySQL. The number of MySQL certification exams taken has doubled in the last six months.   Certcities.com lists the MySQL certification as one of the top 10 certifications to grow in 2005. MySQL professionals need a way to distinguish themselves from the vast majority of database administrators and developers. With more than 4 million active installations, MySQL is the world's most popular open-source database. Known for its speed, reliability and case of use, MySQL has become a low-cost alternative to expensive database systems such as Oracle, IBM and Microsoft. MySQL AB has aggressively improved the feature set of MySQL with MySQL 5, making it more suitable for enterprise-level applications and uses. The MySQL certification tests, available at over 3,000 PearsonVUE testing centers, is a key component of this enterprise growth strategy, establishing a base level of skills for database users, administrators and programmers.   The MySQL Core Certification is aimed at the database user who wants proof of his or her abilities in such fundamental areas as SQL, data entry and maintenance, and data extraction. The MySQL Professional Certification test is designed for the advanced user who wants to prove his or her knowledge in such areas as database management, installation, security, disaster prevention and optimization. Both tests are thoroughly covered in the MySQL 5.0 Certification Study Guide. Written by Paul DuBois, the leading author of books on MySQL topics, and reviewed for technical accuracy by MySQL AB, this book is the fastest, most reliable way for MySQL users, developers, and administrators to prepare for either of the MySQL tests.

Table of Contents:
Introduction. About This Book.     Sample Exercises     Other Required Reading       Manuals       Sample Data       Study Guide Errata       Certification Information at www.mysql.com       The MySQL Certification Candidate Guide       The Certification Mailing List     Conventions Used in This Book       Running MySQL on Microsoft Windows     About the Exams       Registering for an Exam       Going to the Exam       Taking the Exam       Reading Questions       Answering Questions       After the Exam       Retaking Exams       Warning     Interpreting DESCRIBE Output     Sample Tables MYSQL DEVELOPER EXAMS. MySQL Developer I Exam. 1. Client/Server Concepts.     General MySQL Architecture     Invoking Client Programs       General Command Option Syntax       Connection Parameter Options       Using Option Files       Selecting a Default Database       Establishing a Connection with a GUI Client     Server SQL Modes 2. The mysql Client Program.     Using mysql Interactively     Statement Terminators     The mysql Prompts     Using Editing Keys in mysql     Using Script Files with mysql     mysql Output Formats     Client Commands and SQL Statements     Using Server-Side Help     Using the --safe-updates Option 3. MySQL Query Browser.     MySQL Query Browser Capabilities     Using MySQL Query Browser     Using the Query Window       Entering Queries       The Result Area       The Script Editor       Stored Routine Management       The Object and Information Browsers     The MySQL Table Editor     Connection Management       Using the Connection Dialog       Editing Connection Profiles     The Options Dialog 4. MySQL Connectors.     MySQL Client Interfaces     MySQL Connector/ODBC     MySQL Connector/J     MySQL Connector/NET 5. Data Types.     Data Type Overview     Numeric Data Types       Integer Data Types       Floating-Point Data Types       Fixed-Point Data Types     The BIT Data Type     String Data Types       Character Set Support       Non-Binary String Data Types: _CHAR, VARCHAR, TEXT       Binary String Data Types: BINARY, VARBINARY, BLOB       The ENUM and SET Data Types     Temporal Data Types       The DATE, TIME, DATETIME, and YEAR Data Types       The TIMESTAMP Data Type       Per-Connection Time Zone Support     Column Attributes       Numeric Column Attributes       String Column Attributes       General Column Attributes     Using the AUTO_INCREMENT Column Attribute     Handling Missing or Invalid Data Values       Handling Missing Values       Handling Invalid Values in Non-Strict Mode       Handling Invalid Values in Strict Mode       Enabling Additional Input Data Restrictions       Overriding Input Data Restrictions 6. Identifiers.     Identifier Syntax     Case Sensitivity of Identifiers     Using Qualified Names     Using Reserved Words as Identifiers 7. Databases.     Database Properties     Creating Databases     Altering Databases     Dropping Databases     Obtaining Database Metadata 8. Tables and Indexes.     Table Properties     Creating Tables       Creating Tables Using an Explicit Definition       Specifying the Storage Engine for a Table       Creating Tables Based on Existing Tables       Using TEMPORARY Tables     Altering Tables       Adding and Dropping Columns       Modifying Existing Columns       Renaming a Table       Specifying Multiple Table Alterations     Dropping Tables     Emptying Tables     Indexes       Types of Indexes       Creating Indexes       Choosing an Indexing Algorithm     Dropping Indexes     Obtaining Table and Index Metadata 9. Querying for Data.     Using SELECT to Retrieve Data     Specifying Which Columns to Retrieve       Renaming Retrieved Columns       Identifying the Database Containing a Table     Specifying Which Rows to Retrieve       Using ORDER BY to Sort Query Results       The Natural Sort Order of Data Types       Limiting a Selection Using LIMIT       Using DISTINCT to Eliminate Duplicates     Aggregating Results       The MIN() and MAX() Aggregate Functions       The SUM() and AVG() Aggregate Functions       The COUNT() Aggregate Function       The GROUP_CONCAT() Function       Aggregation for NULL Values or Empty Sets     Grouping Results       GROUP BY and Sorting       Selecting Groups with HAVING       Using GROUP BY and WITH ROLLUP     Using UNION 10. SQL Expressions.     Components of SQL Expressions     Numeric Expressions     String Expressions       Case Sensitivity in String Comparisons       Using LIKE for Pattern Matching     Temporal Expressions     NULL Values     Functions in SQL Expressions       Comparison Functions       Control Flow Functions       Aggregate Functions       Mathematical Functions       String Functions       Temporal Functions       NULL-Related Functions     Comments in SQL Statements 11. Updating Data.     Update Operations     The INSERT Statement       Adding Multiple Records with a Single INSERT Statement       Handling Duplicate Key Values       Using INSERT ... ON DUPLICATE KEY UPDATE     The REPLACE Statement     The UPDATE Statement       Using UPDATE with ORDER BY and LIMIT       Preventing Dangerous UPDATE Statements       Multiple-Table UPDATE Statements     The DELETE and TRUNCATE TABLE Statements       Using DELETE with ORDER BY and LIMIT       Multiple-Table DELETE Statements     Privileges Required for Update Statements MySQL Developer II Exam. 12. Joins.     Overview     Writing Inner Joins       Writing Inner Joins with the Comma Operator       Writing Inner Joins with INNER JOIN     Writing Outer Joins       Writing LEFT JOIN Queries       Writing RIGHT JOIN Queries     Resolving Name Clashes Using Qualifiers and Aliases       Qualifying Column Names       Qualifying and Aliasing Table Names     Multiple-Table UPDATE and DELETE Statements 13. Subqueries.     Types of Subqueries     Subqueries as Scalar Expressions     Correlated Subqueries     Comparing Subquery Results to Outer Query Columns       Using ALL, ANY, and SOME       Using IN       Using EXISTS     Comparison Using Row Subqueries     Using Subqueries in the FROM Clause     Converting Subqueries to Joins       Converting Subqueries to Inner Joins       Converting Subqueries to Outer Joins     Using Subqueries in Updates 14. Views.     Reasons to Use Views     Creating Views       Restrictions on Views       View Algorithms       Updatable Views     Altering Views     Dropping Views     Checking Views     Obtaining View Metadata     Privileges Required for Views 15. Importing and Exporting Data.     Import and Export Operations     Importing and Exporting Using SQL       Importing Data with LOAD DATA INFILE       Exporting Data with SELECT ... INTO OUTFILE       Data File Format Specifiers       Importing and Exporting NULL Values     Importing and Exporting Data from the Command Line       Importing Data with mysqlimport       Exporting Data with mysqldump 16. User Variables.     User Variable Syntax     User Variable Properties 17. Prepared Statements.     Benefits of Prepared Statements     Using Prepared Statements from the mysql Client     Preparing a Statement     Executing a Prepared Statement     Deallocating Prepared Statements 18. Stored Procedures and Functions.     Benefits of Stored Routines     Differences Between Stored Procedures and Functions     The Namespace for Stored Routines     Defining Stored Routines     Creating Stored Routines       Compound Statements       Declaring Parameters       The DECLARE Statement       Variables in Stored Routines       Conditions and Handlers       Cursors       Retrieving Multiple Result Sets       Flow Control     Altering Stored Routines     Dropping Stored Routines     Invoking Stored Routines     Obtaining Stored Routine Metadata     Stored Routine Privileges and Execution Security 19. Triggers.     Reasons to Use Triggers     Trigger Concepts     Creating a Trigger     Restrictions on Triggers     Referring to Old and New Column Values     Destroying a Trigger     Privileges Required for Triggers 20. Obtaining Database Metadata.     Overview of Metadata Access Methods     Using INFORMATION_SCHEMA to Obtain Metadata     Using SHOW and DESCRIBE to Obtain Metadata     Using mysqlshow to Obtain Metadata 21. Debugging MySQL Applications.     Interpreting Error Messages     The SHOW WARNINGS Statement     The SHOW ERRORS Statement     The perror Utility 22. Basic Optimizations.     Overview of Optimization Principles     Using Indexes for Optimization       Types of Indexes       Principles for Index Creation       Indexing Column Prefixes       Leftmost Index Prefixes     General Query Enhancement       Query Rewriting Techniques       Using EXPLAIN to Obtain Optimizer Information       Optimizing Queries by Limiting Output       Using Summary Tables       Optimizing Updates     Choosing Appropriate Storage Engines     Normalization MYSQL DBA EXAMS. MySQL DBA I Exam. 23. MySQL Architecture.     Client/Server Overview     Communication Protocols     The SQL Parser and Storage Engine Tiers     How MySQL Uses Disk Space     How MySQL Uses Memory 24. Starting, Stopping, and Configuring MySQL.     Types of MySQL Distributions       MySQL Binary Distributions       MySQL Source Distributions     Starting and Stopping MySQL Server on Windows       Server Startup Prerequisites on Windows       Running MySQL Server Manually on Windows       Running MySQL Server as a Windows Service     Starting and Stopping MySQL Server on Unix       Server Startup Prerequisites on Unix       Choosing a Server Startup Method on Unix     Runtime MySQL Configuration     Log and Status Files       The General Query Log       The Binary Log       The Slow Query Log       The Error Log       Status Files     Loading Time Zone Tables     Security-Related Configuration     Setting the Default SQL Mode     Upgrading MySQL 25. Client Programs for DBA Work.     Overview of Administrative Clients     MySQL Administrator     mysql     mysqladmin     mysqlimport     mysqldump     Client Program Limitations 26. MySQL Administrator.     MySQL Administrator Capabilities     Using MySQL Administrator       Starting MySQL Administrator       Selecting an Operational Mode     Server Monitoring Capabilities       Server Information       Server Connections       Health       Server Logs       Replication Status       Catalogs     Server Configuration       Service Control       Startup Variables       User Administration     Backup and Restore Capabilities       Making Backups       Restoring Backups     MySQL Administrator System Tray Monitor 27. Character Set Support.     Performance Issues     Choosing Data Types for Character Columns 28. Locking.     Locking Concepts     Explicit Table Locking     Advisory Locking 29. Storage Engines.     MySQL Storage Engines     The MyISAM Engine       MyISAM Locking Characteristics       MyISAM Row-Storage Formats     The MERGE Engine       MERGE Locking Characteristics     The InnoDB Engine       The InnoDB Tablespace and Logs       InnoDB and ACID Compliance       The InnoDB Transaction Model       InnoDB Locking Characteristics        InnoDB Isolation Levels, Multi-Versioning, and Concurrency       Using Foreign Keys       Configuring and Monitoring InnoDB     The MEMORY Engine       MEMORY Indexing Options     The FEDERATED Engine     The Cluster Storage Engine     Other Storage Engines 30. Table Maintenance.     Types of Table Maintenance Operations     SQL Statements for Table Maintenance       CHECK TABLE       REPAIR TABLE       ANALYZE TABLE       OPTIMIZE TABLE     Client and Utility Programs for Table Maintenance       The mysqlcheck Client Program       The myisamchk Utility       Options for mysqlcheck and myisamchk     Repairing InnoDB Tables     Enabling MyISAM Auto-Repair 31. The INFORMATION_SCHEMA Database.     INFORMATION_SCHEMA Access Syntax     INFORMATION_SCHEMA Versus SHOW     Limitations of INFORMATION_SCHEMA 32. Data Backup and Recovery Methods.     Introduction     Binary Versus Textual Backups     Making Binary Backups       Making Binary MyISAM Backups       Making Binary InnoDB Backups       Other Binary Backup Tools       Conditions for Binary Portability     Making Text Backups       Making Text Backups via SQL       Making Text Backups with mysqldump       Making Text Backups with MySQL Administrator     Backing Up Log and Status Files     Replication as an Aid to Backup     MySQL Cluster as Disaster Prevention     Data Recovery       Reloading mysqldump Output       Reloading Dumps with MySQL Administrator       Processing Binary Log Contents 33. Using Stored Routines and Triggers for Administration.     Using Stored Routines and Triggers for Security Purposes     Using Stored Routines to Enhance Performance MySQL DBA II EXAM. 34. User Management.     User Account Management       Types of Privileges That MySQL Supports       The Grant Tables       Approaches to Account Management       Creating and Dropping User Accounts       Specifying Account Names       Granting Privileges       Revoking Privileges       Changing Account Passwords       When Privilege Changes Take Effect       Specifying Resource Limits       Privileges Needed for Account Management     Client Access Control       Connection Request Checking       Statement Privilege Checking       Resource Limit Checking       Disabling Client Access Control 35. Securing the MySQL Installation.     Security Issues     Operating System Security     Filesystem Security     Log Files and Security     Network Security       Securing the Initial MySQL Accounts       General Privilege Precautions       MySQL Cluster Network Security     FEDERATED Table Security 36. Upgrade-Related Security Issues.     Upgrading the Privilege Tables     Security-Related SQL Mode Values 37. Optimizing Queries.     Identifying Candidates for Query Analysis     Using EXPLAIN to Analyze Queries       How EXPLAIN Works       Analyzing a Query       EXPLAIN Output Columns     Using SHOW WARNINGS for Optimization     MyISAM Index Caching 38. Optimizing Databases.     General Table Optimizations     Normalization     MyISAM-Specific Optimizations       MyISAM Row-Storage Formats       Keep Optimizer Information Up to Date       FULLTEXT Indexes       Specifying MyISAM Maximum Row Count     InnoDB-Specific Optimizations     MERGE-Specific Optimizations     MEMORY-Specific Optimizations 39. Optimizing the Server.     Interpreting mysqld Server Information       Accessing Server System Variables       Accessing Server Status Variables     Measuring Server Load     Tuning Memory Parameters       Global (Server-Wide) Parameters       Per-Client Parameters     Using the Query Cache       Enabling the Query Cache       Measuring Query Cache Utilization 40. Interpreting Diagnostic Messages.     Sources of Diagnostic Information     Using the Error Log for Diagnostic Purposes     Using The Slow Query Log for Diagnostic Purposes 41. Optimizing the Environment.     Choosing Hardware for MySQL Use     Configuring Disks for MySQL Use       Moving Databases Using Symbolic Links       MyISAM Table Symlinking     Network Issues     Optimizing the Operating System for MySQL Use 42. Scaling MySQL.     Using Multiple Servers     Replication       Setting Up Replication       The Binary and Relay Logs       Replication-Related Threads       Replication Troubleshooting       Replication Compatibility and Upgrading APPENDIXES. A. References. B. Other Offers. Index.