Course Benefits

This MySQL Training course allows you to take advantage of the leading open source relational database management system that is extensively deployed around the world. MySQL runs on a variety of platforms, including UNIX/Linux, Windows and MacOS X. The combination of its low cost, ease of maintenance and high speed makes MySQL ideal for many application areas. This course provides a comprehensive introduction to the MySQL RDBMS.

You Will Learn How To

  • Understand the features and benefits of MySQL
  • Understand the basics of relational databases
  • Understand the SQL and MySQL languages
  • Understand Data/Column types with regard to efficient database design
  • View a database design structure
  • Extract basic database information using the SELECT command
  • Delete or modify a database
  • Delete or modify table row data
  • Grouping query data using aggregation
  • Connect data from multiple table rows using JOIN
  • Perform nested sub-queries
  • Use simple functions (String, Date, Numerical)
  • Backup and restore the database
  • Understand the MySQL storage engines, transactions and concurrency
  • Administer databases with native tools like MySQL Administrator and MySQL Query Browser
  • Enhance database performance using stored procedures and triggers

Who Should Attend

This course is valuable for anyone involved in administering and developing on a MySQL relational database management system (RDBMS). Basic familiarity with database concepts is helpful, but not essential.

Course Outline

Introduction to MySQL

  • Relational Database Systems
  • MySQL Licensing
  • Downloading MySQL
  • MySQL Command Line Client
  • Basic MySQL Commands
  • MySQL Query Browser

Database Design Basics

  • Tables and Relational Databases
  • Primary Keys and Foreign Keys
  • Relations
  • Creating a Data Model
  • Normalization
  • Data Types

Data Definition Language

  • The Structured Query Language
  • Creating, Altering, and Deleting Databases
  • Creating Tables
  • Creating Columns
  • Defining Primary Keys
  • Foreign Keys and Column Constraints

The SELECT Statement

  • Basic Form
  • Displaying Specific Columns
  • Using Aliases
  • Arithmetic Expressions
  • Filtering Data using the WHERE Clause
  • Conditional and Logical Expressions

Altering Data

  • Inserting Data into Tables (INSERT)
  • Replacing Table Rows (REPLACE)
  • Updating Table Rows (UPDATE)
  • Deleting Table Rows (DELETE)

Working with Multiple Tables

  • Queries on Multiple Tables
  • Table Aliases
  • Joins
  • Updating and Deleting Multiple Tables

Advanced Queries

  • Retrieving Unique Values (DISTINCT)
  • Grouping and Aggregating Data (GROUP BY)
  • Filtering Aggregated Results (HAVING)
  • Sorting Results (ORDER BY)
  • Limiting the Results (LIMIT)

Subqueries, Indexes, and Views

  • Using Subquery Operators
  • Query Optimization using Indexes
  • Creating and Deleting Indexes
  • Creating and Editing Views

MySQL Functions

  • Character Strings
  • Using the Date, Time, DateTime Functions
  • Numeric Functions
  • Full Text Search Functions

Concurrency and Transactions

  • ACID Principles
  • Transactions
  • Table Locking

MySQL Administration

  • Creating Users
  • Setting Passwords
  • Access Privileges
  • Backup and Restore
  • Introduction to Migration
  • Using the MySQL Administrator

Stored Procedures and Triggers

  • Basic Concepts
  • Functions and Procedures
  • Variables and Parameters
  • Control Flow Statements
  • Loops
  • Error Handling
  • Cursors
  • Stored Procedure Administration
  • Triggers