MySQL Training: MySQL 5 Administration and Development

Course Number: SQL-172
Duration: 4 days
view class outline

MySQL Administration and Development Training Overview

Accelebrate's MySQL training teaches attendees MySQL 5 administration and development skills.

Location and Pricing

Most Accelebrate courses are taught on-site at our clients' locations worldwide for groups of 3 or more attendees and are customized to their specific needs. Please visit our client list to see organizations for whom we have recently delivered training. These courses can also be delivered as live, private online classes for groups that are geographically dispersed or wish to save on the instructor's or students' travel expenses. To receive a customized proposal and price quote private training at your site or online, please contact us.

In addition, some courses are available as live, online classes for individuals. To see a schedule of online courses, please visit http://www.accelebrate.com/online_training/?action=category&page=mysql.

MySQL Administration and Development Training Prerequisites

All attendees should be experienced systems administrators with general relational database and SQL experience.

Hands-on/Lecture Ratio

This MySQL training class is 65% hands-on, 35% lecture, with the longest lecture segments lasting for 20 minutes. Students "learn by doing," with immediate opportunities to apply the material they learn to real-world problems.

MySQL Administration and Development Training Materials

All attendees receive comprehensive, illustrated courseware.

Software Needed on Each Student PC

MySQL classes on the Windows platform require:

At least 2GB RAM and 10GB free hard drive space
Windows XP, Vista, or 7 with the latest service packs (or Server 2003 or 2008)
Local admin rights
MySQL Community Server 5.1.x, as freely downloaded from http://www.mysql.com/downloads/mysql/
Related lab files and databases that the students extract and install at the start of class

MySQL classes on Linux or Solaris:

At least 2GB RAM and 15GB free hard drive space
VMware Player 3.1.x, a free download from http://downloads.vmware.com/d/info/desktop_downloads/vmware_player/3_0
A virtual machine that Accelebrate would provide for download, extraction, and testing prior to class. This VM would be for Linux or Solaris and could run on Windows or Linux with VMware Player or Mac OSX with VMware Fusion.

MySQL Administration and Development Training Objectives

Install, configure, and upgrade MySQL
Design MySQL databases
Create MySQL queries and execute them at the command line
Use advanced SQL skills such as unions, joins, subqueries, and aggregate queries
Choose an appropriate storage engine that balances a table's specific need for performance versus transaction safety
Use other MySQL table types
Explore and utilize a variety of standard MySQL utilities for checking the integrity of tables, dumping SQL for individual tables or the entire database, browsing queries, and administering the database
Administer MySQL and its users
Create stored procedures and triggers
Optimize and performance tune MySQL
Understand the various programming interfaces for controlling MySQL from popular scripting/programming languages

Note: MySQL training students interested in learning how to use MySQL to build database-driven Web sites are also encouraged to consider our PHP Training: Introduction to PHP and MySQL.

MySQL Administration and Development Training Outline

Introduction to Database Concepts and MySQL

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

Installation, Configuration, and 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 and Normalization
Second Normal Form (2NF)
Third Normal Form (3NF) and beyond
Translating a Data Model into a Database Design

Using the mysql Command-Line Tool

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 and DML Overview
Building Table Definitions
Identifiers
Column Definitions
Numeric Datatypes
ENUM and SET Types
Date and Time Datatypes
AUTO_INCREMENT
UNIQUE Constraints
Primary Keys
Modifying Tables
Foreign Keys
Renaming and Dropping Tables

DML - Data Manipulation Language

DDL and 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 and Destinations for SELECT
Presentation of Table Data with SELECT
Being Selective About Which Rows are Displayed
User-Defined Variables
Expressions and Functions
Control Flow Operators and Functions
Function Names
Comparison Operators and Functions
String Functions
Numeric Operators and Functions
Date and 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, and Ordering
SELECT * and USING Columns

Advanced SQL Techniques

MySQL Pattern Matching
Multipliers, Anchors, and Grouping
GROUP BY
Aggregates
Subqueries
Subquery Comparisons and Quantifiers
Other Subqueries
Subquery Alternatives and Restrictions
InnoDB Multi-Table Updates and 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 and Features
MyISAM Data File Format
InnoDB and Hardware Limitations
InnoDB Shared Tablespace Configuration
InnoDB Per-Table Tablespaces
InnoDB Data Management
MEMORY and FEDERATED
MERGE and ARCHIVE

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 Query Browser
MySQL Query Browser: Deeper
MySQL Administrator: Basic Operations
MySQL Administrator: Monitoring the Server and User Administration
Third Party Tools

Administering a Database and Users

The Server-Side Programs
Starting the MySQL Server
Using SET for Server Options
Table Management
Server Log Files
mysqladmin
Backup and 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 and Use
Flow Control Statement
Writing Blocks of Code
Triggers
Stored Routines, Triggers, and the Binary Log
Table HANDLERs
Prepared Statements

Optimization and Performance Tuning

Hardware Limitations
Optimizing the MySQL Server's Interaction with the External World
Adjusting the MySQL Server Configuration
Optimizing Your Database
Optimizing Queries
The Use of Indexes to Support Queries
Thinking about JOIN Queries
Query Sorts, Indexes, and Short-Circuiting
INSERT, UPDATE, DELETE, and Talkie Locks
Some General Optimizations
Optimizations Specific to MyISAM
Optimizations 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

Conclusion

VB.NET, C#, ASP, & ASP.NET Training

Java Training

SQL Server Training
SharePoint Training

Adobe Training

“Accelebrate,” “Accelerated Learning Celebrated Results,” and the Accelebrate logo are registered trademarks of Accelebrate, Inc. All other product names and trademarks are property of their respective owners. Course outline content may mirror or be derived from the book that each student receives with the course. Otherwise, all content is copyright © 2003- by Accelebrate, Inc. of Atlanta, Georgia, USA. To contact us, please use the contact form, email info@accelebrate.com us, or call 1 877 849 1850 in the US and Canada, 0808 234 2132 in the UK, 1 800 550 311 in Ireland, or +1 678 648 3113 from all other locations.