MySQL for Developers NEW

Course Fees: $3650.00 excl. GST
Printed Manual: $0.00 excl. GST
Course Duration: 5 days
Course Manual

Sorry, no course dates found

The course examines the tools used by MySQL development professionals, and uses real-world examples in explaining how to generate complex queries, and consume them in your application.

Students perform lab work with Java and PHP, connecting to MySQL databases and executing queries and stored routines. In addition, it will prepare students for the Oracle Certified Professional, MySQL 5 Developer certification.


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
Database Administrators
Database Designers
Developer
Experience with having created tables and queries within relational Databases, and comfort with basic SQL statements and another MySQL for Beginners NEW
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

Understand the steps necessary to invoke MySQL client programs

Use the general-purpose mysql client program and the MySQL

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
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