Code: 
SQL-4501
Duration: 
0.5-5 Day(s)
Price: 
1495-4770

Overview

The MySQL for Developers course is the foundation course for developers planning on designing and implementing applications that make use of MySQL 5.0. This course covers essential SQL statements for data design, querying, and programming. In addition, it will prepare students for the MySQL Developer certification

Audience

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

Skills Gained

  • 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 and retrieve results
  • 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

Prerequisites

Required Prerequisites:

  • Having attended the MySQL for Beginners course or some experience with Relational Databases and SQL
  • MySQL for Beginners (SQL-4401)

Course Specifics

Course Outline

Introduction

  • MySQL Overview, Products and Services
  • MySQL Enterprise Services
  • Supported Operating Services
  • MySQL Certification Program
  • Training Curriculum Paths
  • MySQL Website
  • Installing MySQL and the World Database

MySQL Client/Server Concepts

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

MySQL Clients

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

Querying for Table Data

  • The SELECT Statement
  • Aggregating Query Results
  • Using UNION

Handling Errors and Warnings

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

SQL Expressions

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

Data Types

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

Obtaining Metadata

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

Databases

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

Tables

  • Creating Tables
  • Table Properties
  • Column Options
  • Creating Tables Based on Existing Tables
  • Altering Tables
  • Dropping Tables
  • Foreign Keys

Manipulating Table Data

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

Transactions

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

Joins

  • 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

Subqueries

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

Views

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

Prepared Statements

  • Why Use Prepared Statements?
  • Using Prepared Statements from the mysql Client
  • Preparing a Statement
  • Executing a Prepared Statement
  • Deallocating a Prepared Statement

Exporting and Importing Data

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

Stored Routines

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

Triggers

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

Storage Engines

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

Optimization

  • 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 TOPIC22 Conclusion

Thinking about Onsite?

If you need training for 3 or more people, you should ask us about onsite training. Putting aside the obvious location benefit, content can be customised to better meet your business objectives and more can be covered than in a public classroom. It's a cost effective option.

Submit an enquiry from any page on this site, and let us know you are interested in the requirements box, or simply mention it when we contact you.

Upcoming Dates

a Location Cost Days Date  
London 1980321 May 18
London 1980314 May 18
London 1495523 Apr 18
London 1980312 Mar 18
Edinburgh 4295512 Mar 18
Leeds 4295512 Mar 18
London 1495522 Jan 18
Leeds 4295511 Dec 17
London 1675511 Dec 17
London 1495511 Dec 17
London 1980329 Nov 17
London 4295527 Nov 17
London 4295527 Nov 17
London 527 Nov 17
London 520 Nov 17
Virtual 520 Nov 17
London 4295520 Nov 17
London 4295520 Nov 17
London 4295513 Nov 17
London 4295513 Nov 17
Helsinki 513 Nov 17
Aberdeen 1980313 Nov 17
Alleroed 513 Nov 17
London 513 Nov 17
London 530 Oct 17
London 4295530 Oct 17
London 1980330 Oct 17
London 4295530 Oct 17
London 4295523 Oct 17
London 4295523 Oct 17
London 1495523 Oct 17
London 523 Oct 17
London 1495523 Oct 17
Liverpool 518 Oct 17
Exeter 516 Oct 17
Virtual 516 Oct 17
Virtual 515 Oct 17
Milan 515 Oct 17
Staines 4770525 Sep 17
Bracknell 525 Sep 17
Brentford 525 Sep 17
Edinburgh 1980325 Sep 17
London 1995118 Sep 17
Virtual 511 Sep 17
Munchen 511 Sep 17
Wandsworth 511 Sep 17
Brentford 511 Sep 17
London 3400511 Sep 17
London 511 Sep 17
London 4295511 Sep 17
London 4295511 Sep 17
London 1675521 Aug 17
London 521 Aug 17
Bath 4770521 Aug 17
London 1675521 Aug 17
Milan 520 Aug 17
Cardiff 516 Aug 17
London 4295514 Aug 17
Bristol 514 Aug 17
Oxford 514 Aug 17
London 514 Aug 17
Birmingham 1675514 Aug 17
Manchester 4295514 Aug 17
London 4295514 Aug 17
London 1495524 Jul 17
London 1495524 Jul 17
Reading 517 Jul 17
Bracknell 517 Jul 17
Brentford 517 Jul 17
London 3400517 Jul 17
Clifton 517 Jul 17
TBC 526 Jun 17
London 1995126 Jun 17
Helsinki 526 Jun 17
Hvidovre 526 Jun 17