MySQL Release 5.0+ Developer, Part 2

Course:  MYSQLD2
Duration:  2 Days
Level:  II
Course Summary

This course is designed to give you hands-on practice working with a MySQL database. Although the focus is to enable students to meet the standards of MySQL 5.0 OCP Certification. The enhancements in versions 5.1, 5.5 and 5.6 will also be discussed and the class will be conducted using a MySQL Version 5.5 database.

« Hide The Details
Topics Covered In This Course

Joining Multiple Tables

  • Join Overview
  • ANSI 92 INNER Joins
  • ANSI 99 INNER Joins
  • LEFT OUTER JOINs
  • RIGHT OUTER JOINs
  • Resolving Column Ambiguity
  • Table Aliases
  • Multi-Table UPDATE / DELETE

SubQueries

  • Subquery Types
  • Scalar Expressions
  • Correlated Subqueries
  • ANY, ALL And SOME Keywords
  • The IN Keyword
  • The EXISTS Keyword
  • FROM Clause Subqueries
  • Subquery Conversion
  • UPDATE Subqueries

Working With Views

  • View Use
  • Creating Views
  • Updatable Views
  • View Metadata
  • View Privileges

Data Import & Export

  • LOAD DATA INFILE
  • SELECT INTO OUTFILE
  • Format Specification
  • NULLS In Import / Export
  • MySQLImport Utility
  • MySQLDump Utility

User Defined Variables

  • Syntax
  • Properties

Prepared Statements

Benefits

Use With MySQL Client

  • Statement Preparation
  • Statement Execution
  • Statement Deallocation

Stored Routines

  • Benefits
  • Procedures Vs. Functions
  • Stored Routine Namespace
  • Stored Routine Creation
  • Compound Statements
  • Parameters
  • Variable Declaration
  • Error Handlers
  • Creating / Using CURSORs
  • Multiple Result Sets
  • Flow Control
  • Stored Routine Alteration
  • DROP With Stored Routines
  • Executing Stored Routines
  • Stored Routine Metadata
  • Stored Routine Privileges
  • DEFINER Vs. INVOKER Models

DML Triggers

  • Using Triggers In DML
  • BEFORE Vs. AFTER
  • Trigger Creation
  • Trigger Restrictions
  • OLD / NEW Column Values
  • Dropping A Trigger
  • Trigger Privileges

Database Metadata

  • Access Methods
  • INFORMATION_SCHEMA
  • SHOW / DESCRIBE
  • MySQLShow

Application Debugging

  • Error Message Interpretation
  • The Perror Utility

Optimizing MySQL

  • Optimization Principles
  • Rules For Index Creation
  • Indexing Prefixes
  • Using The Query Cache
  • Rewriting Queries
  • The EXPLAIN Command
  • Interpreting EXPLAIN Output
  • Summary Table Use
  • Update Optimization
  • Storage Engine Selection
  • Data Normalization
Who Should Take This Course

Application Developers, Database Administrators, Support Staff, Management and anyone seeking an OCA or OCP Certification.

Recommended Prerequisites

Participants should be well familiar with IT terminology and computer use. They should also be familiar with relational database concepts. Participants should have previously taken MySQL Developer, Part 1 or have at least 3 years working with a MySQL database and be completely familiar with the concepts taught in the  Part 1 course before taking this course.

Training Style

Short lecture sessions (60%) along with hands-on lab sessions (40%).

« Hide The Details
Related Courses
Code Course Title Duration Level
MYSQLD
MySQL Release 5.0+ Developer
4 Days
I
Details
MYSQLD1
MySQL Release 5.0+ Developer, Part 1
2 Days
I
Details
MYSQLA
MySQL Release 5.0+ Database Administrator
4 Days
I
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]