Introduction to MySQL Development and Administration Training (5 days)

Introduction to MySQL Development and Administration Training Overview

This is an intensive MySQL training course with a solid coverage of various MySQL programs, development, technologies, concepts and administration.

Introduction to MySQL Development and Administration Training Course Goals

• Understand the basics of Relational Databases.
• Learn the features and benefits of MySQL.
• Use various Data types and database design.
• Discover a database design structure/content.
• Use expressions in SQL statements for more functional and flexible retrieval.
• Learn bulk data import and export operations.
• Understand MySQL storage engines, transactions and features of the common engines.
• Create views to reuse SELECT statements
• Learn data retrieval using SELECT statement.
• Troubleshoot typical warnings and errors.
• Change or add data.
• Underdstand MySQL data validation.
• Delete data from tables.
• Generate aggregated query data using various criteria.
• Connect data from multiple table rows using various types of JOIN constructs.
• Use several different types of sub-queries.
• Extensive coverage of MySQL Functions and expressions.
• Use expressions in SQL statements for more functional and flexible retrieval.
• Learn to export and import data.
• Understand the MySQL Architecture.
• General characteristics and resources used.
• Common Database Administration tasks.
• Utilize the various MySQL administration programs.
• Learn the MySQL Administrator Graphical User Interface.
• Use the INFORMATION_SCHEMA database to access metadata.
• Install and Upgrade MySQL 5.0 for the most common operating systems.
• Start and shutdown MySQL.
• Configure MySQL server options at runtime.
• Setup and manage numerous server logs.

Introduction to MySQL Development and Administration Training Course Prerequisites

Experience in the following areas is required:

• General RDBMS and SQL knowledge and principles
• Understanding of DDL objects - tables, Indexes, constraints and others
• Understanding of database installation and deployment
• Basic knowledge hardware and OS

Experience in the following areas would be beneficial.

• General administration of database installations.
• Understanding of database installation and deployment
• Basic knowledge hardware and OS

Introduction to MySQL Development and Administration Training Course Outline

1. MySQL Programs and Executables
   1. MySQL Programs and Executables
   2. Common Options to Various Commands
   3. Specifying Options
   4. Options on the Command Line
   5. my_print_defaults: Display options from files
   6. perror: Explain Error Codes
2. User Interfaces MySQL
   1. Overview of User Interfaces
      1. MySQL Help - Information Browser
   2. Establishing a Connection
      1. Object Browser
   3. MySQL Query Browser
      1. The Script Editor
   4. MySQL Administrator
   5. MySQL Migration Toolkit
   6. phpMyAdmin
3. MySQL Monitor
   1. Using MySQL Monitor (SQL Command Interpreter)
   2. Some Useful Options
   3. Formatting and Output Options
   4. Commands for Interactive Mode
   5. Executing a Script from the Monitor
   6. Using the mysql Command to run a Script
   7. See Database Status
4. Designing Databases
   1. Relational Database Concepts
      1. Physical Model: Mapping The ER Model To A Relational Database
   2. Database Design Principles
      1. First Normal Form
   3. Film Rentals - sakila
      1. Warning: Abnormal Normal Form!
   4. Basic Modeling Process: Creating a Data Model
      1. Second Normal Form
   5. Database Normalization
      1. Third Normal From
5. Creating Databases and Components
   1. Creating Databases
      1. Deferred Checking
   2. Creating Tables
      1. Defining Default Values
   3. Exercise: Confirm a Table is temporary
   4. Creating Column Definitions
      1. Defining Indexes when Creating Tables
   5. Data Types in MySQL
      1. CREATE INDEX Statement
   6. Exercise: Timestamp Data Type
   7. Constraints
      1. Full-Text Indexes
   8. Exercise: Creating Tables
   9. Exercise: Altering Tables
   10. Managing Indexes
       1. Deleting Indexes
   11. Exercise: Creating and Indexing Tables
6. Identifiers in MySQL
   1. Identifiers in MySQL
      1. Identifier Names
   2. Schema Object Names
      1. Using Double Quotes
   3. Some Restrictions
   4. Identifier Qualifiers
   5. Mapping of Identifiers to Filenames
      1. lower_case_table_names Setting
   6. Identifier Case Sensitivity
      1. Avoiding Data Transfer Problems
7. Show Database Information
   1. SHOW and DESCRIBE Statements
      1. Using DESCRIBE Statement
   2. INFORMATION_SCHEMA Tables
      1. Showing Databases
8. MySQL Table Types
   1. MySQL Table Types (Storage Engines)
      1. Using Full-Text Search
   2. Setting/Changing Engine
      1. InnoDB - Limitations and Drawbacks
   3. MyISAM Tables
      1. InnoDB Disk Management
   4. InnoDB Tables
      1. Preventing Problems
   5. FOREIGN KEY Constraints in InnoDB
   6. Choosing - MyISAM or InnoDB?
   7. HEAP or MEMORY Tables
      1. ARCHIVE or Compressed Tables (since MySQL 4.1)
   8. Temporary Tables
      1. CSV Type Tables(since MySQL 4.1)
   9. MERGE Tables
      1. NDB or Cluster Tables (since MySQL 4.1)
   10. Other Table Types
       1. FEDERATED or External Tables (since MySQL 5.0)
   11. Table Files Storage
9. Basic Select Statements
   1. Basic SQL Syntax Rules
   2. SELECT Basics
   3. Introduction to the sakila Database
   4. Basic Select Examples
   5. SELECTing Specific Columns
   6. The WHERE Clause and Basic Operator Symbols
   7. Exercise: Using the WHERE clause to check for equality or inequality
   8. Exercise: Checking for NULL
   9. WHERE and ORDER BY
   10. Exercise: Using WHERE and ORDER BY Together
   11. Using Aliases
   12. Selecting Distinct Records
   13. Sorting Records
   14. Exercise: Sorting Results
   15. The LIMIT Clause
       1. Processing the First N or Last N Records
10. Using Expressions in MySQL Statements
    1. MySQL Expressions
       1. The IN Operator
    2. Operators in MySQL
       1. The LIKE Operator
    3. Concatenation
       1. The REGEXP Operator
    4. Arithmetic Operators
       1. Use of BINARY with Strings
    5. Operator Precedence
       1. The NOT Operator
    6. Comparison Operators
       1. Order of Evaluation
    7. The WHERE Clause and Operator Words
       1. Summary
    8. Exercise: More SELECTs with WHERE
    9. Using CASE
       1. Exercise :Using Control Flow Functions in a SELECT Statement
    10. Exercise: Working with CASE
    11. Logical Operators
        1. Data Conversion Functions
    12. Exercise: Writing SELECTs with Multiple Conditions
    13. Using MySQL Functions
        1. Numeric Functions
    14. Functions to Compare Data
        1. Current Date/Time
    15. Control Flow Functions
        1. Date Addition/Subtraction Functions
    16. String Functions
        1. Date Extraction Methods
    17. Exercise: Using String functions to find first word.
    18. Date/Time Functions
        1. Date Formatting Methods
    19. Exercise: Working with Dates and Strings
    20. Exercise: Working with Case and Dates
11. Inserting, Updating and Deleting Records in MySQL
    1. Inserting Data in a MySQL Database
       1. <set option> Alternative of INSERT
    2. Exercise: Inserting Records
    3. Using REPLACE Statement to Add Data
       1. ORDER BY and LIMIT Clauses in UPDATE
    4. Exercise: Replacing Records in sakila
    5. INSERT with ON DUPLICATE KEY UPDATE Syntax
       1. Some Advanced MySQL modifiers to DELETE statement
    6. Exercise: Replacing Records in sakila
    7. Updating Data in MySQL
       1. ORDER BY and LIMIT in DELETE Statement
    8. Using DELETE Statement in MySQL
       1. Updating Multiple Tables using Joins
    9. Exercise: Delete reservations
    10. Exercise: Delete reservations
    11. Using TRUNCATE Statement to Delete Data
        1. <FROM JOIN DELETE> Alternative
    12. Joining Tables in an UPDATE Statement
        1. <using join delete> Clause
    13. Exercise: using UPDATE Statements to Modify Data in multiple Joined Tables
    14. Joining Tables in a DELETE Statement
        1. Deleting from multiple tables
12. Data Validation in MySQL
    1. Data Validation
    2. MySQL SQL Modes
       1. Setting SQL mode
    3. Strict SQL Mode
    4. Special Combination Modes
    5. Data Validation for Temporal Types
       1. SHOW WARNINGS
13. Advanced Queries
    1. More on The SELECT Statement
       1. Full Outer Joins
    2. Using Variables in a SELECT Statement
       1. Creating Full Joins
    3. Creating Joins in Your SQL Statements
       1. Creating Basic Joins
    4. Inner Joins and Cross Joins
       1. Creating Straight Joins
    5. Exercise: Multi-table Joins
    6. Outer Joins
       1. Creating Natural Joins
    7. Exercise: Left Outer Join
    8. Unions
       1. UNION ALL
    9. Exercise: Using Unions
    10. Creating Views
    11. Dropping Views
        1. SubQuery Variants
    12. Benefits of Views
        1. Nesting JOINS
    13. Exercise: Creating a View
    14. The SELECT Statement Options
        1. SubSELECT Limitations
    15. Subqueries
        1. SubSelects as Join
    16. Exercise: Subqueries
14. Exporting and Importing Data in MySQL
    1. EXPORTING: Data Out of a Table
    2. Use Field and Line Separators
    3. Dump a Row to a File
    4. CTAS: Creating a New Table Using a ResultSet
    5. Copying Data into an Existing Table
       1. General Loading Rules
    6. Using the LOAD DATA to Import Data
       1. More Loading Examples
    7. Exercise: Exporting and Importing Data
    8. Using the mysqlimport Utility to Import Data
       1. Using Separators with mysqlimport Utility
    9. Exercise: Importing Data via mysqlimport
15. Installing MySQL
    1. Installing MySQL
    2. Operating System Selection
    3. Downloading and Installing MySQL On Windows
       1. Running the MySQL Configuration Wizard
    4. Installing MySQL On Linux and Unix
    5. Installing MySQL On Mac
    6. Verifying a Downloaded File
    7. Verifying a MySQL Installation
    8. Launching the MySQL Server
    9. Installing From Source Files
16. Setting up MySQL
    1. Versions of the MySQL Server
    2. Mysql Directory Structure
    3. Securing the MySQL Server: Set root Password
    4. Delete Anonymous Accounts
    5. Creating an Account for Basic Use
    6. General Information using MySQL Monitor
    7. Configuring MySQL
17. Configuring MySQL and Specifying Options
    1. Configuration Options
       1. Setting the Default Storage Engine
    2. Viewing MySQL's Configuration Parameters
       1. Executing SQL Commands Automatically
    3. Some Examples of Option Usage
       1. Logging Potentially Nonoptimal Queries
    4. Rules for Specifying Options
    5. Exercise: Using Options
    6. Setting Options in Configuration Files
    7. Exercise: Using Options in Files
    8. Configuration Templates
    9. Location of Configuration Files
    10. Options in Environment/System Variables
    11. Main Configuration Groups
    12. Key Configuration Options
    13. Client Configuration Options
    14. Server Configuration Options
    15. Engine-Specific Configuration Options
    16. Buffer and Cache Allocation Configuration Options
    17. Exercise: Review Options in Pre-configured Files
18. Securing MySQL Installation
    1. First Things First
       1. Controlling Connections
    2. Network
       1. Application Access
    3. Securing the mysqld Daemon
       1. One-Way Encryption
    4. Files, Directories, and Processes
       1. Signing Your Data
    5. Securing Accounts
       1. Two-Way Encryption
    6. Exercise: Limit connections with direct update
    7. Data Storage and Encryption
       1. Don't Store Sensitive Data
19. Managing Users in MySQL
    1. Managing Security and Access Control
    2. The Two Stages of Access Control
    3. Granting Privileges
    4. Revoking Privileges
    5. Managing Users
       1. Adding Users to MySQL Access Control
    6. Exercise: Managing Users
    7. MySQL Privilege System
    8. Privilege Scope Levels - Where Is Access Information Stored?
    9. The user Table - Global Privilege Scope
    10. Exercise: Checking Users table
    11. Database Privilege Scope - The db Table
        1. The host Table
    12. Exercise: Checking Users table
    13. Table Privilege Scope - The tables_priv Table
    14. Exercise: Managing Users' Table level permissions
    15. Column Privilege Scope - The columns_priv Table
    16. The Routine scope: The procs_priv Table
    17. Tools for Setting the Access Privileges
        1. Insert into user Table
    18. Refreshing Grant Caches
        1. Using SHOW GRANTS
    19. Reviewing User Privileges
        1. Listing Conflicts
    20. Exercise: Managing Users for Specific Tables
    21. The GRANT OPTION Privilege
        1. Grant to All Users from a Host
20. Administering MySQL
    1. View Information
    2. Using MySQLadmin Tool
    3. Flush Caches and Settings
    4. View and Kill Threads
    5. Exercise: View and Kill Threads
    6. Server Management
    7. MySQL Administrator User Interface
21. Data Backup and Restore in MySQL
    1. Managing Backup and Recovery
       1. Backing up all Databases
    2. Backing up or Exporting Databases Using mysqldump
       1. Reloading Your Database
    3. Using mysqldump
       1. Restoring Data Directly From a Binary Log
    4. mysqldump - Options
       1. Selective Changes from Log Files
    5. Backing up a Single Database
       1. Restoring Binary Log Data From a Text File
    6. Contents of the Backup SQL Script
       1. Enabling and Disabling Binary Logging
    7. Exercise: Backing up a Database
    8. Exercise: Backing up select tables from a Database
    9. Restoring Your Database
       1. mysqlhotcopy - A Database Backup Program
    10. Updating the Restored Database From Binary Log Files
        1. Stop Background Processes - Forced Recovery
    11. Exercise: Restoring a Database From a Backup File
    12. Recovering Corrupt MyISAM Tables
        1. Recovery using Checkpoints
    13. Backing Up and Recovering an InnoDB Database
        1. InnoDB Hot Backup

Introduction to MySQL Development and Administration Training Course Materials

In addition to a comprehensive set of materials, including course notes and all the programming examples, each student will also receive a one-year subscription to Webucator's online reference library, which contains hundreds of the most current electronic technology books - a $149.95 per student value.

Introduction to MySQL Development and Administration Training Technical Requirements

1. MySQL 5.0
   • Download
2. MySQL GUI Tools
   • Download

Class Files for Introduction to MySQL Development and Administration Training

1. Download the class files.
2. After downloading the files, click here for further instructions.