MySQL for Developers NEW

This course teaches you the knowledge, skills, tools and issues involved in writing applications with the MySQL database server. It forms the core of the MySQL Developer learning path.

Learn To:

• Use MySQL tools to connect to databases.
• Examine database structure and contents.
• Generate database structure diagrams.
• Execute complex queries.
• Manipulate numeric, text, spatial and temporal data.
• Execute complex subqueries and joins.

Tools Used by MySQL Development Professionals

This course examines the tools used by MySQL development professionals. Expert instructors reference real-world examples when illustrating how to generate complex queries, and consume them in your application.

Java & PHP Lab Work

You'll perform lab work with Java and PHP, connecting to MySQL databases and executing queries and stored routines. Enrolling in this course will simultaneously help you prepare for the Oracle Certified Professional, MySQL 5 Developer certification.

Audience

• Developer
• Database Administrators
• Database Designers

Course Topics Introduction

• MySQL Product
• MySQL Services and Support
• MySQL Resources
• Example Databases

MySQL Client/Server Concepts

• MySQL General Architecture
• Diskspace use
• Memory use

MySQL Clients

• Invoking Client Programs
• Using Option Files
• The MySQL Client
• MySQL Workbench
• MySQL Connectors
• Third Party APIs

Querying for Table Data

• The SELECT Statement
• Aggregating Query Results
• Using UNION
• Import and Export Data

Handling Errors and Warnings

• SQL Modes
• Handling Missing or Invalid Data Values
• Interpreting Error Messages
• Conditions and Signals
• Error Handling when using Connectors

Data Types

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

SQL Expressions

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

Obtaining Metadata

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

Database

• 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
• Isolations 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
• Updating Views
• Managing Views
• Obtaining View Metadata

Prepared Statements

• Why use Prepared Statements
• Using Prepared Statements for the mysql client
• Executing a Prepared Statement
• Deallocating a Prepared Statement
• Programming with Prepared Statements

Stored Routines

• What is a Stored Routine?
• Creating Stored Routines
• Compound Statements
• Assign Variables
• Parameter Declaration
• Execute Stored Routines
• Stored Routine Characteristics
• Examine Stored Routines

Triggers

• What are Triggers
• When to Use Triggers
• How to Delete Triggers
• Restrictions on Triggers

Optimization and Monitoring

• Overview of Optimization Principles
• Using Indexes for Optimization
• Using EXPLAIN to Analyze Queries
• Using Performance Schema to Analyze Performance
• Query rewriting techniques
• Optimizing Queries by Limiting Output
• Using Summary Tables
• Optimizing Updates

Conclusion
Course Objectives

• Configure, Monitor, and Optimize MySQL using Performance Schema and metadata queries
• Understand the steps necessary to invoke MySQL client programs
• Use the general-purpose mysql client program and the MySQL Workbench graphical user interface (GUI) to run queries, retrieve results, and generate diagrams
• Understand the steps necessary to invoke MySQL client programs; Use the general-purpose mysql client program and the MySQL Workbench graphical user interface (GUI) to run queries,
• Manage the structural characteristics of your databases and the tables within your databases
• Use the SELECT statement to retrieve information from database tables
• Use expressions in SQL statements to retrieve more detailed information
• Use SQL statements to modify the contents of database tables
• Write join expressions in your SQL statements to obtain information from multiple tables
• Use subqueries in your SQL statements
• Create views using SELECT statements to produce virtual tables of specific data
• Perform bulk data import and export operations
• Create user defined variables, prepared statements and stored routines
• Create and manage triggers
• Use the INFORMATION_SCHEMA database to access metadata
• Write and debug MySQL applications