Oracle Certified Professional, MySQL 5.6 Developer Training

Introduction

• MySQL Overview, Products, Services
• MySQL Services and Support
• Supported Operating Services
• MySQL Certification Program
• Training Curriculum Paths
• MySQL Documentation Resources

Client and Server Concepts

• The MySQL client/server model
• Communication protocols
• Storage engines
• How MySQL uses memory and disk space
• The mysql command line client
• Using SQL scripts
• How MySQL uses databases
• Installing and populating the class database

MySQL Clients

• Invoking client programs
• Features of the mysql client
• Modifying client behavior with options
• Configuring client behaviour with option files
• MySQL Workbench
• MySQL Utilities

MySQL Connectors and APIs

• MySQL connectors
• Oracle and community connectors
• Why write custom programs?
• Connecting to MySQL server by using Java and PHP
• Embedding a MySQL database in a program
• MySQL and NoSQL
• InnoDB integration with memcached

Data Types

• The major categories of data types
• Character sets and collation
• Choosing the correct data type
• NULL and NOT NULL
• The spatial data type extensions

SQL Expressions

• Components of expressions
• Applying numeric, string, spatial, and temporal values in expressions
• Pattern matching for string expressions
• The properties of NULL values
• Functions in expressions
• Combining result sets from multiple queries
• Comments in SQL statements

Obtaining Metadata

• Metadata access methods
• The INFORMATION_SCHEMA database
• Metadata commands
• SHOW statements

Databases

• The MySQL data directory
• Database design best practices
• Normalization
• Choosing identifiers for database entities
• Creating a database
• Modifying a database
• Removing a database

Tables

• Table properties
• Column options
• Creating a table
• Modifying a table
• Displaying table information
• Renaming a table
• Removing a table
• Foreign Keys

Manipulating Table Data

• Inserting data into a table
• Deleting data from a table
• Updating data in a table
• Replacing data in a table
• Truncating table data

Transactions

• Using transactions for concurrent updates
• The ACID transaction rules
• Isolating transactions

Joining Tables

• Table join concepts
• The different join methods
• Cartesian joins
• Outer and inner joins
• Joining a table to itself
• Column references and table aliases
• Multi-table UPDATE and DELETE statements

Subqueries

• Nesting queries
• Correct positioning of subqueries
• Using the appropriate type of subquery
• Using the correct SQL syntax to create subqueries
• Quantifiers for subquery comparisons

Views

• What are views?
• Reasons for using views
• Creating a view
• Checking a view
• Changing and removing a view
• Setting view privileges

Prepared Statements

• Reasons for using prepared statements
• Using prepared statements from the mysql command-line client
• Preparing, executing, and deallocating prepared statements
• Using prepared statements in code, with Connectors

Stored Routines

• Stored procedures and stored functions
• Creating and executing stored routines
• Examining an existing stored routine
• Deleting an existing stored routine
• Assigning variables in stored routines
• Flow control statements
• Cursor usage and limitations
• Limitations of stored routines and stored functions

Triggers

• What are triggers?
• When to use triggers
• Creating triggers
• Deleting triggers
• Scheduling events

Handling Errors and Warnings

• The effect of SQL modes on error output
• Dealing with missing or invalid data values
• Interpreting error messages
• Using the SHOW WARNINGS and SHOW ERRORS statements
• MySQL diagnostic information
• The perror utility program
• Handling errors when coding with Connectors

Optimization

• The role of the query optimizer
• Strategies available for optimizing queries
• Using the EXPLAIN statement to predict query performance
• Using indexes for optimization
• Describing the role of MySQL Enterprise Monitor in query optimization