MySQL for Developers with Developer Techniques Accelerated NEW

This accelerated offering will guide developers through advanced level MySQL Developer topics. In this intensive course, students will learn about the main products needed for MySQL Developers and will go from a basic to an advanced level understanding of topics, found in the MySQL for Developers and MySQL Developer Techniques, all in one course.

The MySQL for Developers course is the foundation course for developers planning on designing and implementing applications that make use of MySQL. This course covers essential SQL statements for data design, querying, and programming.

Students will also obtain the skills required by covering lessons in The Developer Techniques course, to create complex queries and efficient structures while improving the performance of their database applications. In addition, the MySQL developers will also be provided with additional query writing techniques that support creating reports that support end users along with skills required to create and utilize trees and hierarchical data structures.

This is an accelerated course, covering eight days' worth of content in only five days. Because of the extra content to be covered each day, the duration of classes may be slightly longer than usual.

A Live Virtual Class (LVC) is exclusively for registered students; unregistered individuals may not view an LVC at any time. Registered students must view the class from the country listed in the registration form. Unauthorized recording, copying, or transmission of LVC content may not be made.

Audience

• Developer
• Database Administrators
• Database Designers

Course Topics Introduction MySQL Client/Server Concepts

• MySQL General Architecture
• How MySQL Uses Disk Space
• How MySQL Uses Memory

MySQL Clients

• Invoking Client Programs
• Using Option Files
• The MySQL Client
• MySQL Query Browser
• MySQL Connectors
• Third-Party APIs

Querying for Table Data

• The SELECT Statement
• Aggregating Query Results
• Using UNION

Handling Errors and Warnings

• SQL Modes
• Handling Missing or Invalid Data Values
• Interpreting Error Messages

SQL Expressions

• SQL Comparisons
• Functions in SQL Expressions
• Comments in SQL Statements

Data Types

• Data Type Overview
• Numeric Data Types
• Character String Data Types
• Binary String Data Types
• Temporal Data Types
• NULLs

Obtaining Metadata

• Metadata Access Methods
• The INFORMATION_SCHEMA Database/Schema
• Using SHOW and DESCRIBE
• The mysqlshow Command

Databases

• Database Properties
• Good Design Practices
• Identifiers
• Creating Databases
• Altering Databases
• Dropping Databases

Tables

• Creating Tables
• Table Properties
• Column Options
• Creating Tables Based on Existing Tables
• Altering Tables
• Dropping Tables
• Foreign Keys

Manipulating Table Data

• The INSERT Statement
• The DELETE Statement
• The UPDATE Statement
• The REPLACE Statement
• INSERT with ON DUPLICATE KEY UPDATE
• The TRUNCATE TABLE Statement

Transactions

• What is a Transaction?
• Transaction Commands
• Isolation Levels
• Locking

Joins

• What is a Join?
• Joining Tables in SQL
• Basic Join Syntax
• Inner Joins
• Outer Joins
• Other Types of Joins
• Joins in UPDATE and DELETE statements

Subqueries

• Types of Subqueries
• Table Subquery Operators
• Correlated and Non-Correlated Subqueries
• Converting Subqueries to Joins

Views

• What Are Views?
• Creating Views
• Updatable Views
• Managing Views
• Obtaining View Metadata

Prepared Statements

• Why Use Prepared Statements?
• Using Prepared Statements from the mysql Client
• Preparing a Statement
• Executing a Prepared Statement
• Deallocating a Prepared Statement

Exporting and Importing Data

• Exporting and Importing Data
• Exporting and Importing Data Using SQL
• Exporting and Importing Data Using MySQL Client Programs
• Import Data with the SOURCE Command

Stored Routines

• What is a Stored Routine?
• Creating, Executing and Deleting Stored Routines
• Compound Statements
• Assign Variables
• Parameter Declarations
• Flow Control Statements
• Declare and Use Handlers
• Cursors

Improving Performance with Indexes

• Query Executions in MySQL
• Why Indexes?
• When MySQL Uses an Index
• Optimize Indexes

Improving Searches of Date and Text Fields

• Strings in Multiple Columns
• Case Sensitivity
• INET_ATON and INET_NTOA functions
• Searching Dates
• String Dates to SQL Dates
• Dates to Integer Values
• Dates to Strings
• Nonspecific Date Searches

Improving Inserts and Updates

• INSERT Process
• Storage Engine Specifics
• MySQL Extensions

Improving Calculations

• Aggregate Multiplication Functions
• Running Total Queries
• Avoiding Division by Zero
• Median Values
• Simulating RANK
• Solving Complex Problems
• Miscellaneous

Course Objectives

• Configure and Optimize MySQL
• Create and manage triggers
• Create user defined variables, prepared statements and stored routines
• Create views utilizing SELECT statements to produce virtual tables of specific data
• Debug MySQL applications
• Manage the structural characteristics of your databases and the tables within your databases
• Perform bulk data import and export operations
• Select the best data type for representing information in MySQL
• Understand the steps necessary to invoke MySQL client programs
• Write join expressions in your SQL statements to obtain information from multiple tables
• Having attended the MySQL for Beginners course or some experience with Relational Databases and SQL
• Create report queries
• Design and optimize queries
• Design data structures
• Improve the design of joins
• Improve the process of importing data