Overview

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.

Full Product Details

Author:   Paul DuBois ,  Stefan Hinz ,  Carsten Pedersen
Publisher:   Pearson Education (US)
Imprint:   Que Corporation,U.S.
Edition:   2nd Revised edition
Dimensions:   Width: 17.80cm , Height: 3.90cm , Length: 23.00cm
Weight:   1.090kg
ISBN:  

9780672328121

ISBN 10:   0672328127
Pages:   672
Publication Date:   01 August 2005
Audience:   College/higher education ,  Tertiary & Higher Education
Format:   Mixed media product
Publisher's Status:   Out of Print
Availability:   Awaiting stock  

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.

Reviews

Author Information

Paul DuBois is the leading author of books on MySQL topics. His MySQL (0672326736, third edition soon to be published), is widely considered the definitive work on MySQL. He is a developer for MySQL AB, where he edits and maintains the official English-language documentation for MySQL. Stefan Hinz is leads the MySQL documentation team, is a MySQL trainer and consultant, and the German translator of the MySQL Reference Manual. He is also the translator of Paul's MySQL Cookbook (O'Reilly and Associates) and translator and author of MySQL-related German books. Stefan passed the MySQL Certification exam before he joined MySQL AB.

Tab Content 6

Author Website:  

Customer Reviews

Recent Reviews

No review item found!

Add your own review!

Countries Available

All regions