Course Description

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 companies adopt MySQL to reap the benefits of an open source, platform-independent RDMS, such as simplifying conversion from other platforms and lowering database Total Cost of Ownership by 90%.

This 4-day training course encourages the student to explore 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, perform administrative functions, learn about optimization and performance tuning, and explore various APIs.

Pre-requisites

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

Who is this course for?

For application and web developers, or system and database administrators.

Course content

Introduction

Features of a Relational Database
Where does SQL Fit in?
Database Access
Why MySQL?
The History of MySQL

Installation, Configuration & Upgrading

MySQL Software
MySQL Software Features
Preparing to Install MySQL
Available Client Software
After the Download
Configuring the Server
Starting the Server
The Initial User Accounts
Verifying Server Operation
Upgrading
Copying a Database Between Architectures
Environment Variables

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

The mysql Command-Line Tool

Running the mysql Client
Customising the mysql Prompt
mysql Commands
Using the Help Command
Some Useful mysql Options
Working with a Database
Examining Table Definitions
Other SHOW Options

Data Definition Language

DDL & DML Overview
Building Table Definitions
Identifiers
Column Definitions
Numeric Datatypes
ENUM and SET Types
Date & Time Datatypes
AUTO_INCREMENT
UNIQUE Constraints
Primary Keys
Modifying Tables
Foreign Keys
Renaming & Dropping Tables

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
Presentation of Table Data with SELECT
Filtering 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

Result Sets 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 * and 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 & Hardware Limitations
InnoDB Shared Tablespace Configuration
InnoDB Per-Table Tablespaces
InnoDB Data Management
MEMORY and FEDERATED Engines
MERGE and ARCHIVE Engines

Utilities

Client Overview
Specifying Options for Command-Line Clients
Client Option Files
Checking Tables with myisamchk and mysqlchk
Using myisamchk and mysqlchk for Repairs
mysqlshow and mysqlimport
Using mysqldump
The MYSQL Workbench – General
MYSQL Workbench – Execution
MySQL Administration via the Workbench
Data Modelling with the Workbench
SQL Dvelopment
Third Party Tools

Administering a Database & Users

The Server-Side Programs
Starting the MySQL Server
Using SET for Server Options
Table Management
Server Log Files
mysqladmin
Backup & Restore
Miscellaneous Functions
User Account Management
Understanding User Privileges
User Account Rights Management
User Account Privileges
Managing Access to the Database Environment

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

Organisation & Performance Tuning

Hardware Limitations
Optimising the MySQL Server’s Interaction with the External World
Adjusting the MySQL Server Configuration
Optimising Your Database
Table Partioning
Optimising 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
Optimisations Specific to MyISAM
Optimisations Specific to InnoDB

MySQL Programming Interfaces

Database Application Architectures
Connecting MySQL to ODBC
Connecting MySQL to MS/Office and MS/Access
Connecting to MySQL from Perl
Programming Perl to MySQL
Connecting to MySQL from PHP
Programming PHP to MySQL