INTRODUCTION TO MySQL DATABASES – GTLP6L

Course Description

This MySQL training class teaches the configuration, security management, administration, backup and restore, and optimization of MySQL databases.

^^

Duration

3 days

^^

Target Audience

This course is suitable for IT professionals with basic programming experience.

^^

Course Prerequisites

Experience in the following areas is required: General SQL knowledge and principles, Understanding of DDL objects – tables, Indexes, constraints and others, and Basic knowledge hardware and OS. SQL skills are taught in the following course:

^^

Suggested Follow on Courses

Please contact us for further details.

^^

Course Content

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

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 ShortCircuiting

INSERT, UPDATE, DELETE, and

Table 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

^^

See more Database Technologies courses