MySQL Developer Techniques with Advanced Stored Procedures

Attendance to the MySQL for Developers course, or an
equivalent mastery of database concepts, SQL and the MySQL

server.

Be able to create user defined variables, prepared
statements and stored procedures

Have experience with complex queries like joins and
subqueries

Have experience with simple SQL queries

 Audience

Database Administrators

Database Designers

Developer

Utilize multiple indexing options

Improve the design of joins

Design and optimize queries

Design complex queries

Create report queries

Improve the process of importing data

Design data structures

Utilize tips and tricks

Circumvent MySQL stored procedure limitations

Develop personal best practices in relation to the
creation and maintenance of stored procedures

Improve the performance of your applications by
optimizing stored procedures

Improve upon cursor creation for processing individual
rows of data by learning the best practices associated with them

Improve upon the design and structure of your compound
stored procedure statements

Improve upon the integration of SQL statements in MySQL
stored procedures by taking advantage of the continuity

built-in to the two entities

Improve upon the management of MySQL stored procedures by
utilizing best practices in their creation, modification

and deletion

Improve upon your applications by utilizing the various
data items allowed in MySQL stored procedures

Introduction

MySQL Overview, Products and Services

Supported Operating Systems

MySQL Certification Program

Training Curriculum Paths

MySQL Website

Installing MySQL

Installing the world database

Improving Performance with Indexes

Query Executions in MySQL

Why Indexes?

When MySQL Uses an Index

Optimize Indexes

Improving Searches of Date and Text Fields

Strings in Multiple Columns

Case Sensitivity

INET_ATON and INET_NTOA functions

Searching Dates

String Dates to SQL Dates

Dates to Integer Values

Dates to Strings

Nonspecific Date Searches

Improving Inserts and Updates

INSERT Process

Storage Engine Specifics

MySQL Extensions

Improving Calculations

Aggregate Multiplication Functions

Running Total Queries

Avoiding Division by Zero

Median Values

Simulating RANK

Solving Complex Problems

Miscellaneous

Improving Joins

Query Optimizer

EXPLAIN

Overview of JOINs

Improving JOIN performance

Trees and Hierarchical Data

Graph, Trees and Hierarchies

Adjacent List Structures

Nested Set Structures

Path Enumeration

Utilizing Advanced Index Structures

MySQL Index Types

FULL TEXT Indexes

Simulating Function Based Indexes in MySQL

Optimizing End of Field Searches

Working with Locking Levels

Locks

Explicit Table Locks

Storage Engine Locking Techniques

Locking Issues

Creating Reports

Calculate Multiple Conditions

Create a Calculated Report

Quarterly Reports

SQL Bar Chart

Decision Tables

Materialized Views

Producing Sequential or Missing Data

MySQL Stored Procedures Foundation

Compound Statements

Assigning Variables

Parameter Declarations

MySQL Supported Operators and Built-in Functions in
Stored Routines

Obtain Information on Stored Routines

Alteration and deletion of Stored Routines

Backing Up/Recovery Stored Routines

Binary Logging of Stored Routines

SQL and MySQL Stored Procedures

Flow Control Statements

SQL in Stored Routines

Dynamic SQL

Handling Exceptions and Errors

Error Handling Capabilities and Limitations

Implementing Error Handlers

Other Handler Issues

Error Handling Limitations

Managing Triggers

Creating Triggers

Obtain Information on Triggers

Delete Triggers

Trigger Limitations

Data Security

Set Permissions

View or Stored Routine?

Invoker Rights Error

Preventing Code Injection

Best Practices

Fundamental Programming Best Practices

Coding Style and Conventions

Variable Use Best Practices

Flow Control Best Practices

Dynamic SQL Best Practices

Conclusion

 Course Overview

Training and Certification Website

 Course Evaluation

Thank You!

QA Session

Description:

This MySQL Developer Techniques with Advanced Stored
Procedures training guides developers through advanced

level MySQL Developer topics. Lessons from the MySQL
Developer Techniques and MySQL Advanced Stored

Procedures courses will be covered in one week.

Learn To:

Improve the quality of stored procedures, functions and
triggers in your applications.

Understand how to debug and optimize them.

Participate in hands-on labs that will help you maximize
the use of stored procedures.

Develop the knowledge to discern when an application
should contain stored procedures and when it should not.

Live Virtual Class Format

A Live Virtual Class (LVC) is exclusively for registered
students; unregistered individuals may not view an LVC at any

time. Registered students must view the class from the
country listed in the registration form. Unauthorized recording,

copying, or transmission of LVC content may not be made.