MySQL for Developers

This lesson describes the goals of the course and the classroom learning environment.

Lessons:

• Describe the course goals

• Explain the origin and status of the MySQL product

• List the available MySQL products and professional services

• List the currently supported operating systems

• Describe how to access MySQL information and services on Oracle and MySQL websites

• List all the available MySQL courses

• Describe the Oracle Certification Program for MySQL

• Obtain MySQL online documentation and example databases

This lesson describes the nature and architecture of the MySQL Server/Client model, communication protocols, connectors, API’s and storage engines.

Lessons:

• Describe the MySQL client/server model

• Explain the basics of how MySQL uses memory and disk space

• Describe how the server supports storage engines

• Install and populate the world_innodb database

• Find respources for downloading MySQL products

• Start the MySQL client

• Quiz – MySQL Client and Server concepts

• Use script files with MySQL

• Review the classroom environment

• Explain the basics of how MySQL uses databases

• Create and populate the world_innodb database

• Explain communication protocols

This lesson explains the usage and configuration of MySQL client applications.

Lessons:

• Invoke client programs in the MySQL client / server architecture

• Describe and use many features of the mysql client

• Quiz – MySQL Clients and Options

• Modify client behaviour with options

• Launch and use the MySQL command line client

• Use option files to configure client

• Create an option files for MySQL applications

• Describe and use the MySQL Workbench

• Work with statement terminators and editing keys in MySQL command line client

• Describe MySQL utilities

• Use the MySQL command line client interactively and in batch mode

• Use MySQL Workbench to run statements and explore databases

This lesson describes the available MySQL connectors and API’s and demonstrates how to connect to MySQL through Java and PHP.

Lessons:

• Describe MySQL Connectors

• Describe InnoDB integration with memcached

• List common connectors that are available from Oracle and the MySQL community

• List reasons for writing custom MySQL programs

• Quiz – MySQL clients and connectors

• Access the MySQL server by using Java and PHP

• Working with MySQL connectors

• Explain how a MySQL database can be embedded in a program

• Configuring the memcached plug-in for InnoDB

• Explain how MySQL supports NoSQL

• Using the memcached plug-in for InnoDB

This lesson describes the available data types and their implementation within table design.

Lessons:

• Describe the four major categories of data types

• Describe character sets and collation

• Quiz – Data Types

• Assign appropriate data types to table entities

• Using numeric data types

• Describe the meaning and use of NULL and NOT NUL

• Use temporal data types

• Explain the MySQL spatial data type extensions

• Use string data types

• Reviewing data types in the sakila database

• Use spatial data types

This lesson explains the use of expressions in calculations, manipulations, combination of and commenting on data.

Lessons:

• Use components of expressions

• Combine the result sets from several SELECT statements

• Apply numeric, string, spatial, and temporal values in expressions

• Write comments in SQL statements

• Use pattern matching for string expressions

• Explain the properties of NULL values

• Use SQL expressions

• Employ functions in expressions

• Use SQL functions

• Use advanced SQL expressions

This lesson describes the nature and usage of metadata within a MySQL database.

Lessons:

• List metadata access methods

• Recognise the structure of the INFORMATION_SCHEMA database schema

• Obtaining metadata

• Use commands to view metadata

• Using the how and describe syntax

• List the differences between SHOW statements and INFRMATION_SCHEMA tables

• Showing tables, databases and columns

This lesson explores the topics around databases creation such as normalisation and data file storage.

Lessons:

• Describe the MySQL data directory

• Drop a database

• Use best practices when designing a database structure

• Explain the process of normalisation

• Quiz – Normalisation

• Choose proper identifiers for databases

• Create a database

• Create a database

• Changing database character set and collation

• Alter a database

• Removing a database

• Reviewing the configuration and normalisation of the sakila database

This lesson describes the creation and maintenance of database tables.

Lessons:

• Assign appropriate table properties

• Assign appropriate column options

• Creating and viewing new tables

• Create a table

• Creating new tables from existing tables

• Alter a table

• Adding, removing and modifying tables

• Display table information

• Removing tables from a database

• Rename a table

• Setting foreign keys

• Remove a table

• Additional practice

• Assign and use foreign keys

This lesson describes the use of DML (Data Manipulation Language) to manage table data.

Lessons:

• Insert data in a table

• Lab:

• Delete data from a table

• Adding new data to a table

• Update data in a table

• Removing data from a table

• Replace data in a table

• Modifying existing table row data

• Truncate data from a table

• Replace table data

• Avoid data insertion errors

• Empty out tables with the TRUNCATE statement

• Additional practice

This lesson describes transactions within the MySQL database.

Lessons:

• Use transaction commands to run multiple SQL statements concurrently

• Describe and use the ACID transaction rules

• Using transactional control statements

• Isolate one transaction from another

• Quiz – Isolation levels and locking

• Working with transactions

This lesson describes the different join types and their usage within the MySQL database.

Lessons:

• Describe the concepts of joining tables

• Explain the construction and properties of the Cartesian product

• Calculating the Cartesian product of two tables

• Use the syntax and application of different join types

• Create a Cartesian product by jointing two tables using a WHERE clause

• Use qualified column references and table aliases to avoid ambiguity

• Obtaining information from specific columns in joined tables

• List the different types of joins

• Using JOIN syntax

• Execute inner and outer joins

• Performing INNER JOINS

• Perform a self-join

• Performing OUTER JOINS

• Use multi-table UPDATE and DELETE statements

• Updating and deleting data in joined tables

• Joining multiple tables

This lesson describes the nature and usages of subqueries within a MySQL Database.

Lessons:

• Write queries that contain nested queries

• Identify the appropriate type of subquery to use

• Quiz – Subqueries

• Use correct SQL syntax to create sunqueries

• Placing subqueries in the SELECT and FROM clauses

• Describe and use quantifiers for subquery comparisons

• Placing subqueries in the WHERE clause

• Additional practice

This lesson describes the nature, creation and usage of MySQL database views.

Lessons:

• Define views

• List the reasons for using views

• Using the CREATE VIEW statement

• Create a view

• Using updatable views

• Check a view

• Checking views

• Alter or remove a view

• Obtaining view metadata

• Set privileges for views

• Addition al practice

This lesson explores the nature of prepared statements and discusses the need and usage thereof.

Lessons:

• List the reasons for using prepared statements

• Use prepared statements in code with connectors

• Use prepared statements with the MySQL commandline client

• Prepare, execute and deallocate prepared statements

• Preparing statements in MySQL

• Programming with prepared statements

This lesson explains the use of stored routines within the MySQL database environment.

Lessons:

• Define a stored routine

• Differentiate between stored procedures and stored functions

• Create a stored procedure

• Create and execute stored routines

• Create a stored function

• Examine an existing stored routine

• Create stored routines with compound statements

• Delete and existing stored routine

• Create a stored routine with parameter declarations

• Create stored routines with compound statements

• Examine stored routines

• Assign variables in stored routines

• Delete and re-create a stored routine

• Create flow control statements

• Create a stored routine with flow control statements

• Describe cursor usage and limitations

• Working with DECLARE CONDITION and DECLARE HANDLER statements

• List the limitations of stored routines

• Create a stored function to retrieve database metadata

• Additional practice

This lesson describes the nature and implementation of MySQL database triggers.

Lessons:

• Describe triggers

• Describe scheduling events

• Identify when to use triggers

• Create new triggers

• Create and drop triggers

• Delete existing triggers

• Additional practice

This lesson describes the nature of MySQL database errors and warnings and demonstrates how to deal with them programmatically.

Lessons:

• Set SQL modes to affect error output

• Handle missing or invalid data values

• Handle errors when coding with connectors

• Interpret error messages

• Lab:

• Use the SHOW WARINGS and SHOW ERRORS statements

• Quiz – Handling errors

• Examine MySQL diagnostic information

• Identifying errors and warnings with the MySQL client

• Invoke the perror utility program

• Handling MySQL errors when coding with connectors

This lesson explores the functionality of the MySQL query optimiser and describes techniques that can be used to affect query performance.

Lessons:

• Describe the role of the query optimiser

• Lab:

• Describe the strategies available for optimising queries

• Quiz – Optimisation

• Use the EXPLAIN statement to predict query performance

• Create a table with indexes

• Use indexes for optimisation

• Altering the indexes in an existing table

• Describe the role of MySQL Enterprise Monitor in query optimisation

• Remove an index from an existing table

• Using optimisation techniques

This lesson provides a summary of all the concepts covered within the preceding lessons.

Lessons:

• Course goals

• MySQL curriculum path

• MySQL resources