Course Description

This 3-day MySQL Database Development training course encourages students to explore general database fundamentals, as well as MySQL features. Students learn the basics of MySQL use and the programming of stored routines and triggers. Students also participate in database design discussions, and learn about optimization. Also included is an exploration of various APIs.

MySQL® is the open source community’s most popular Relational Database Management System (RDBMS) offering, and is a key part of LAMP – Linux™, Apache™, MySQL®, PHP/Perl/Python®. Many Fortune 500 companies adopt MySQL to reap the benefits of an open source, platform-independent RDBMS, such as simplifying conversion from other platforms and lowering database Total Cost of Ownership by 90%.

This course is current to MySQL 5.5 but is suitable for users of other versions of MySQL.

Pre-requisites

Prior experience programming in any language is recommended but not required.

Who is this course for?

For Data Analysts, Data Architects, Application Developers, Web Developers, Database Administrators, and System Administrators.

Further Training

Course content

Introduction to Database Concepts & MySQL
Features of a Relational Database
Where does SQL Fit in?
Database Access
Why MySQL?
The History of MySQL
MySQL Software Features
Database Design
Developing the Design of a Database
Database Entities
The Primary Key
Foreign Key Relationships
Data Models & Normalization
Second Normal Form (2NF)
Third Normal Form (3NF) & Beyond
Translating a Data Model into a Database Design
MySQL Client Software & the mysql Command-Line Tool
Available Client Software
Environment Variable
Running the mysql Client
Customizing the mysql Prompt
mysql Commands
Using the Help Command
Some Useful mysql Options
Working with a Database
Examining Table Definitions
Other SHOW Options
DDL: Data Definition Language
DDL & DML Overview
Building Table Definitions
Identifiers
Column Definitions
Numeric Datatypes
ENUM & SET Types
Date & Time Datatypes
AUTO_INCREMENT
UNIQUE Constraints
Primary Keys
Modifying Tables
Foreign Keys
Renaming & Dropping Tables
DML: Data Manipulation Language
DDL & DML Overview
Data Values: Numbers
Data Values: Strings
Working with NULL Values
Bulk Loading of Data
Bulk Data Format
Working with Special Values in Bulk Data
Adding New Table Rows with INSERT
Copying Rows
UPDATE
REPLACE
Removing Table Rows
Transactions
InnoDB: Using Transactional Processing
Locking Tables
Queries: The SELECT Statement
SELECT Syntax Summary
Choosing Data Sources & Destinations for SELECT
Presentation of Table Data with SELECT
Being Selective about Which Rows are Displayed
User-Defined Variables
Expressions & Functions
Control Flow Operators & Functions
Function Names
Comparison Operators & Functions
String Functions
Numeric Operators & Functions
Date & Time Functions
Forcing Data Interpretation
Miscellaneous Functions
Building a Result Set from Several Sources
UNION
Combining Data from Two Tables
Using WHERE to Choose Matching Rows
INNER JOIN
OUTER JOINs
Multiple Tables, Fields, Joins, & Ordering
SELECT * & USING Columns
Advanced SQL Techniques
MySQL Pattern Matching
Multipliers, Anchors, & Grouping
GROUP BY
Aggregates
Subqueries
Subquery Comparisons & Quantifiers
Other Subqueries
Subquery Alternatives & Restrictions
InnoDB: Multi-Table Updates & Deletes
Building a VIEW
Updatable VIEWs
MySQL Storage Engines
Storage Engine Overview
Other Storage Engine Types
The Basics of Commonly Used Storage Engines
MyISAM Limits & Features
MyISAM Data File Format
InnoDB Data Management
MEMORY & FEDERATED
MERGE & ARCHIVE
Utilities
Client Overview
Specifying Options for Command-Line Clients
Client Option Files
Checking Tables with myisamchk & mysqlchk
Using myisamchk & mysqlchk for Repairs
mysqlshow and mysqlimport
Using mysqldump
The MySQL Workbench – General
MySQL Workbench – Execution
Data Modelling with the Workbench
SQL Development
Third Party Tools
Database Programmability
Stored Routines: Basic Concepts
Routine Creation & Use
Flow Control Statement
Writing Blocks of Code
Triggers
Stored Routines, Triggers, & the Binary Log
Table HANDLERs
Prepared Statements
Optimization & Performance Tuning
Optimizing Your Database
Table Partitioning
Optimizing Queries
The Use of Indexes to Support Queries
Thinking about JOIN Queries
Query Sorts, Indexes, & Short-Circuiting
INSERT, UPDATE, DELETE, & Table Locks
Some General Optimizations
Optimizations Specific to MyISAM
Optimizations Specific to InnoDB