• Developers who want to create applications using MySQL
• Students wishing to prepare for the MySQL 5.0 Developer Certification Exams

PREREQUISITES

Have attended the MySQL for Beginners course or some experience with Relational Databases and SQL

SUGGESTED PREREQUISITES:

• MySQL For Beginners (SQL-4401)

At the completion of this course, you should be able to:

• Understand the steps necessary to invoke MySQL client programs
• Utilize the general-purpose MySQL client program and the MySQL Query Browser graphical user interface (GUI) to run queries
• Select the best data type for representing information in MySQL
• Manage the structural characteristics of your databases and the tables within your databases
• Utilize the SELECT statement to retrieve information from database tables
• Utilize expressions in SQL statements to retrieve more detailed information
• Utilize SQL statements to modify the contents of database tables
• Write join expressions in your SQL statements to obtain information from multiple tables
• Utilize subqueries in your SQL statements
• Create views utilizing SELECT statements to produce virtual tables of specific data
• Perform bulk data import and export operations
• Create user defined variables, prepared statements and stored routines
• Create and manage triggers
• Use the INFORMATION_SCHEMA database to access metadata
• Debug MySQL applications
• Configure and Optimize MySQL

• MySQL General Architecture
• How MySQL uses Disk Space
• How MySQL uses Memory

• Invoking Client Programs
• Using Option Files
• The MySQL Client
• MySQL Query Browser
• MySQL Connectors
• Third-Party APIs

• The SELECR Statement
• Aggregating Query Results
• Using UNION

• SQL Modes
• Handling Missing or invalid Data Values
• Interpreting Error Messages

• SQL Comparisons
• Functions in SQL Expressions
• Comments in SQL Statements

• Data Type Overview
• Numeric Data Types
• Character String Data Types
• Binary String Data Types
• Temporal Data Types
• NULLs

• Metadata Access Methods
• The INFORMATION_SCHEMA Database / Schema
• Using SHOW and DESCRIBE
• The mysqlshow Command

• Database Properties
• Good Design Practices
• Identifiers
• Creating Databases
• Altering Databases
• Dropping Databases

• Creating Tables
• Table Properties
• Column Options
• Creating Tables based in Existing Tables
• Altering Tables
• Dropping Tables
• Foreign Keys

• The INSERT Statement
• The DELETE Statement
• The UPDATE Statement
• The REPLACE Statement
• INSERT with ON DUPLICATE KEY UPDATE
• The TRUNCATE TABLE Statement

• What is a Transaction?
• Transaction Commands
• Isolation Levels
• Locking

• What is a Join?
• Joining Tables in SQL
• Basic Join Syntax
• Inner Joins
• Outer Joins
• Other Types of Joins
• Joins in UPDATE and DELETE Statements

• Types of Subqueries
• Table Subquery Operators
• Correlated and Non-Correlated Subqueries
• Converting Subqueries to Joins

• What are Views?
• Creating Views
• Updatable Views
• Managing Views
• Obtaining View Metadata

• Why use Prepared Statements?
• Using Prepared Statements from the mysql Client
• Preparing a Statement
• Executing a Prepared Statement
• De-allocating a Prepared Statement

• Exporting and Importing Data
• Exporting ad Importing Data using SQL
• Exporting and Importing Data using MySQL Client Programs
• Import Data with the SOURCE Command

• What is a Stored Routine?
• Creating, Executing and Deleting Stored Routines
• Compound Statements
• Assign Variables
• Parameter Variables
• Parameter Declarations
• Flow Control Statements
• Declare and use Handlers
• Cursors

• What are Triggers?
• Delete Triggers
• Restrictions on Triggers

• SQL Parser and Storage Engine Tiers
• Storage Engines and MySQL
• The MyISAM Storage Engine
• The InnoDB Storage Engine
• The MEMORY Staorage Engine
• Other Storage Engines

• Overview of Optimization Principles
• Using Indexes for Optimization
• Using EXPLAIN to Analyze Queries
• Query Rewriting Techniques
• Optimizing Queries by Limiting Output
• Using Summary Tables
• Optimizing Updates
• Choosing Appropriate Storage Engines