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 [email protected]