Course Code: 1156

MySQL Administration and Development

Class Dates:
1/25/2021
12/28/2020
3/1/2021
Length:
4 Days
Cost:
$1995.00
Class Time:
Technology:
Database
Delivery:
Instructor-Led Training, Virtual Instructor-Led Training

Overview

  • Course Overview
  • This 4-day class encourages the student to explore database fundamentals, as well as MySQL features. Students learn the basics of MySQL use and the programming of stored routines and triggers.

  • Audience
  • Application and web developers, or system administrators.

Prerequisites

  • Prior experience installing software and programming in any language, such as HTML, is recommended but not required.

Course Details

  • Course Introduction
  • Course Objectives
  • Course Overview
  • Using the Workbook
  • Suggested References
  • 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
  • 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 & DML Overview
  • Building Table Definitions
  • Identifiers
  • Column Definitions
  • Numeric Datatypes
  • ENUM and SET Types
  • Date and Time Datatypes
  • AUTO_INREMENT
  • UNIQUE Constraints
  • Primary Keys
  • Modifying Tables
  • Foreign Keys
  • DML – Data Manipulation Language
  • DDL & 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
  • 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
  • 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
  • The MySQL Workbench – General
  • MySQL Workbench - Execution
  • MySQL Administration via the Workbench
  • Data Modeling with the Workbench
  • SQL Development
  • 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 Statements
  • Writing Blocks of Code, Triggers
  • Stored Routines, Triggers, and the Binary Log
  • Table HANDLERs
  • Prepared Statements
  • Optimization and Performance Tuning
  • Handware Limitations
  • Optimizing the MySQL Server's Interaction with the External World
  • Adjusting the MySQL Server Configuration
  • Optimizing Your Database, Table Partitioning, Optimizing Queries
  • The Use of Indexes to Support Queries,
  • Thinkng 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