MySQL 5 Certification Study Guide

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.