Introduction
Last year I wrote a two-part series entitled the Database Journal Guide to MySQL certification. Part one covered the core certification, and part two the professional certification. Those two articles covered the MySQL 4 certification. Although it is still possible to achieve MySQL 4 certification, MySQL development has been rapid over the last year, and MySQL 5 is now the latest stable version. MySQL AB has released their MySQL 5 certification as beta, and many developers and DBA′s are choosing the newer certification. Consequently, it is time this month for a Database Journal Guide to MySQL 5 Certification. 数据挖掘研究院
Developer and DBA
MySQL has changed the exam structure. Rather than having a simple hierarchical structure, with a core exam aimed at developers, and a professional exam aimed at DBA′s, with it only being possible to sit for the professional after obtaining the core, there is now a separate DBA and a Developer stream. If a candidate already has the MySQL 4 core certification, they can take a single upgrade exam to achieve the MySQL 5 Developer certification. If a candidate has the MySQL Professional Certification, they can take a single upgrade exam to achieve MySQL 5 DBA certification. 数据挖掘研究院
Everyone else though has to sit through two exams to obtain either certification. There are the Developer 1 and Developer 2 exams, and the DBA 1 and DBA 2 exams. There is no forced hierarchy though, and the exams can be taken in any order, although logically the concepts tested in the second exam follow on from those tested in the first exam. This month′s article focuses on the MySQL Developer Stream. 数据挖掘研究院
Why certify?
In brief, the benefits to the employee are that they are more attractive to an employer. As someone who hires staff, I can say that it is often little things that make the difference on that resume, and certified developer/DBA does stand out. Perhaps the true benefit for all parties is that the process of studying for the certification results in a greater understanding of MySQL. Developers I know who have taken the certification have commented that they feel more comfortable doing their MySQL-related work after studying for the test. An employee feels more confident working with MySQL, and an employer more confident that their staff can do the job required. With the separation of the two certifications into two streams, there is less pressure for a developer to do the more impressive-sounding professional certification, when really the developer stream may be more suitable. Developers are no less professional than DBA′s, but their needs differ.
MySQL Developer 1
This section, and the next, summarize all topics covered in the MySQL 5 developer exams (as of June 2006), as well as links to relevant resources. The percentage figure indicates how much of the total exam is devoted to that particular topic. 数据挖掘研究院
-
Client/Server Concepts (5%). This section tests your knowledge of how MySQL works according to the client server model, the different SQL server modes, MySQL architecture and the different configurations such as MySQL-Max, invoking client applications and the connection parameters. 数据挖掘研究院
-
The mysql Client Program (5%). Covers general issues about the mysql client, mysql prompts, use of editing keys in MySQL, client commands and SQL statements, statement terminators, use of the --safe-updates option, mysql output format, using script files with MySQL, using mysql interactively and getting help on the server-side.
-
MySQL Query Browser (5%). Covers use and capabilities of the query browser, the MySQL Table editor, use of the query window, the options dialog and connection management. 数据挖掘研究院
-
Connectors (5%), Covers an overview of the MySQL client interfaces and the most high-profile Connectors: Connector/J, Connector/ODBC and Connector/NET.
-
Data Types (15%). Tests knowledge of column attributes, the BIT Data Type, temporal, numeric and string data types, use of the AUTO_INCREMENT column attribute, and how MySQL handles handle missing or invalid data values 数据挖掘实验室
-
Identifiers (5%). Covers use of reserved words as identifiers, identifier syntax, use of qualified names and case sensitivity.
-
Databases (5%). Covers database properties, creating, dropping and altering databases, and obtaining database metadata. 数据挖掘研究院
-
Tables and Indexes (15%). Covers indexes in general, creating and dropping indexes, table properties, how to obtain metadata about tables and indexes, creating, dropping, altering and emptying tables
-
Querying for Data (15%). Includes the use of SELECT statements, aggregating results, the UNION statement, specifying which rows and columns to retrieve and grouping results. 数据挖掘研究院
-
SQL expressions (15%). Includes string, numeric and temporal expressions, the components of SQL expressions, null values, comments in SQL statements and functions in SQL expressions. 数据挖掘研究院
-
Updating Data (10%). Covers an overview of update operations, as well as the INSERT, UPDATE, DELETE, TRUNCATE and REPLACE statements specifically, and the privileges required to perform update statements.
MySQL Developer 2
There is no need to take the MySQL Developer 2 exam after MySQL Developer 1 - the exams can be taken in either order. However, Developer 2′s topics are more advanced, and when learning MySQL should only be tackled after grasping the topics covered in Developer 1.
- Joins (15%). This module tests your knowledge of SQL joins, in particular multi-table UPDATE and DELETE statements, outer joins, inner joins, and how to resolve name clashes using qualifiers and aliases.
- Subqueries (10%). Covers types of subqueries, subqueries as scalar expression, converting subqueries to joins, comparison using row subqueries, correlated subqueries, use of subqueries in updates as well as the FROM clause, and comparing subquery results to outer query columns.
- Views (10%). Includes creating, dropping, altering and checking views, displaying information about views, when and why views should be used, and privileges required for using views
- Importing and Exporting Data (10%). Covers importing and exporting using SQL, using the command line, and general import/export operations.
- User Variables (5%). Covers user variable syntax and properties.
- Prepared Statements (5%). Covers executing and preparing prepared statements, use of prepared statements from the mysql client, deallocating prepared statements, and the benefits of prepared statements.
- Connecting and prepared statements with the mysqli extension (Database Journal) (only the general parts about prepared statements are relevant)
- SQL syntax for prepared statements
- Stored Routines (15%). Includes stored routine definition, creating, altering and dropping stored routines, finding out more information about a stored routine, differences between stored routines and functions, stored routine privileges and security, invoking stored routines.
- MySQL Stored Procedures, Part 1 (Database Journal)
- MySQL Stored Procedures, Part 2 (Database Journal)
- MySQL Stored Procedures, Part 3 (Database Journal)
- MySQL Stored Functions (Database Journal)
- Stored Procedures and Functions
- CREATE PROCEDURE and CREATE FUNCTION Syntax
- ALTER PROCEDURE and ALTER FUNCTION Syntax
- DROP PROCEDURE and DROP FUNCTION Syntax
- CALL Statement Syntax
- BEGIN ... END Compound Statement Syntax
- DECLARE Statement Syntax
- Variables in Stored Routines
- Conditions and Handlers
- Cursors
- Flow Control Constructs
- Stored Procedure example
- Triggers (5%). Covers creating and destroying triggers, reasons to use triggers, trigger concepts and restrictions, privileges required to use triggers, how to refer to old and new column values.
- Obtaining Database Metadata (5%). Includes an overview of metadata access methods, the use of mysqlshow, the INFORMATION_SCHEMA database, SHOW and DESCRIBE to obtain metadata,
- Debugging MySQL Applications (5%). Covers the SHOW ERRORS and SHOW WARNINGS statements, the perror utility and interpreting error messages.
- Basic Optimizations (15%). Covers an overview of the principles of optimization, the use of indexes for optimization, choosing the most suitable storage engines, normalization and general query enhancements.
Conclusion
If you have sufficient practical experience in using MySQL, and with such a host of online and offline resources, there is no reason you can′t succeed in achieving MySQL certification the first time. Even if you decide not to take the test, preparing for it can be a useful way to improve your skills. Good luck!
