MySQL Online Training

MySQL Training Course Content:

• 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

MySQL 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 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