MySQL Release 5.0+ Developer

Course:  MYSQLD
Duration:  4 Days
Level:  I
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 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.

This course combines our separate "Part 1" and "Part 2" courses for MySQL Development.

« Hide The Details
Topics Covered In This Course

Architecture

  • Client Server Basics
  • Using MySQL Client Programs
  • Option Syntax
  • Option Files
  • Default Database Selection
  • GUI Client Connections
  • MySQL SQL Modes

MySQL Command Line

  • Interactive Use
  • Statement Terminators
  • Understanding Prompts
  • Using Editing Keys
  • Using Script Files
  • Output Formatting
  • Commands / SQL Statements
  • Options : --safe_updates

MySQL Graphical Interface

  • Capabilities
  • Using the GUI Tools
  • Entering Queries
  • Viewing Results
  • Editing Scripts
  • Working With Stored Routines
  • Browsers
  • The Table Editor
  • Managing Connections
  • Connection Profiles

MySQL Connectors

  • Interfaces
  • ODBC
  • Java
  • Net
  • Other Connectors

MySQL Datatypes

  • Numeric Types
  • The BIT Datatype
  • String Datatypes
  • Temporal Datatypes
  • Column Attributes
  • AUTO_INCREMENT
  • Handling Data Issues

Using Strict Mode

MySQL Identifiers

  • Syntax
  • Case Sensitivity
  • Qualified Naming
  • Reserve Word Use

Databases / Schemas

  • Database Properties
  • Database Creation
  • Database Alteration
  • Dropping Databases
  • Getting Metadata

Tables / Indexes

  • Table Properties
  • Table Creation
  • Storage Engines
  • Tables From Existing Tables

Table Modification

  • Adding / Dropping Columns
  • Column Modification
  • Renaming Tables
  • Multiple Alterations
  • Dropping Tables
  • Deleting From Tables
  • Index Algorithms
  • Creating Indexes
  • Dropping Indexes
  • Table & Index Metadata

The SELECT Statement

  • Column Specification
  • Column Aliases
  • USE Database Command
  • The WHERE Clause
  • The ORDER BY Clause
  • Sort Order / Collation
  • The LIMIT Clause
  • DISTINCT / ALL
  • Aggregating Data
  • The GROUP BY Clause
  • The HAVING Clause
  • ROLLUP
  • Set Operators

SQL Expressions

  • Components
  • Numerical Expressions
  • Character String Expressions
  • Pattern Matching: LIKE
  • Date / Time Expressions
  • Functions For Comparisons
  • Functions For Control Flow
  • Aggregations
  • Math Functions
  • String Functions
  • Date Functions
  • Dealing With NULL
  • Commenting

Data Manipulation Language

  • The UPDATE Statement
  • The INSERT Statement
  • INSERT / ON DUPLICATE KEY UPDATE
  • The REPLACE Statement
  • UPDATE With LIMIT / ORDER BY
  • UPDATE Safety
  • Multi-Table Updates
  • The DELETE Statement
  • The TRUNCATE Statement
  • Privileges For DML

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.

Training Style

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

« Hide The Details
Related Courses
Code Course Title Duration Level
MYSQLD1
MySQL Release 5.0+ Developer, Part 1
2 Days
I
Details
MYSQLA
MySQL Release 5.0+ Database Administrator
4 Days
I
Details
MYSQLD2
MySQL Release 5.0+ Developer, Part 2
2 Days
II
Details
MYSSTOR
MySQL Release 5.0+: Stored Routines
2 Days
II
Details
MYSCLUS
MySQL Release 5.0+: MySQL Cluster
2 Days
II
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 info@verhoef.com