This instructor-led course is designed for students planning on developing applications that make use of MySQL 5.0. This course covers essential SQL statements for data design, querying, and programming. In addition, it will prepare students for the MySQL Developer certification.
Audience Profile
Anyone who needs to administer, monitor and support MySQL databases and services
At Course Completion
After completing this course, you should be able to:
Describe the MySQL client/server architecture
Understand the steps necessary to invoke MySQL client programs
Utilize the general-purpose mysql client program to run queries and retrieve results
Perform queries and analysis of data utilizing the MySQL Query Browser graphical user interface (GUI)
Explain the MySQL connectors that provide connectivity for client programs
Select the best data type for representing information in MySQL
Manage the structural characteristics of your databases
Manage the structural characteristics of the tables within your databases
Utilize the SELECT statement to retrieve information from database tables
Utilize expressions in SQL statements to retrieve more detailed information
Utilize SQL statements to modify the contents of database tables
Write join expressions in your SQL statements to obtain information from multiple tables
Utilize subqueries in your SQL statements
Create views utilizing 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
Debug MySQL applications
Configure and Optimize MySQL
Prerequisites
Having attended the MySQL 5.0 for Beginners course or some experience with Relational Databases and SQL.
Course Outline
Module 1: MySQL Client/Server Concepts
Lessons
MySQL General Architectures
How MySQL Uses Disk Space
How MySQL Uses Memorys
Module: MySQL Clients
Lessons
Invoking Client Programs
Using Option Files
The MySQL Client
MySQL Query Browser
MySQL Connectors
Third-Party API's
Module 3: Querying for Table Data
Lessons
The SELECT Statement
Aggregating Query Results
Using UNION
Module 4: SQL Expressions
Lessons
SQL Comparisons
Functions in SQL Expressions
Comments in SQL Statements
Module 5: Data Types
Lessons
Data Type Overview
Numeric Data Types
Character String Data Types
Binary String Data Types
Temporal Data Types
NULLs
Module 6: Handling Errors and Warnings
Lessons
SQL Modes
Handling Missing or Invalid Data Values
Interpreting Error Messages
Module 7: Obtaining Metadata
Lessons
Metadata Access Methods
The INFORMATION_SCHEMA
Using SHOW and DESCRIBE
The mysqlshow Command
Module 8: Databases
Lessons
Database Properties
Good Design Practices
Identifiers
Creating Databases
Altering Databases
Dropping Databases
Module 9: Tables
Lessons
Creating Tables
Table Properties
Column Options
Creating Tables Based on Existing Tables
Altering Tables
Dropping Tables
Foreign Keys
Module 10: Manipulating Table Data
Lessons
The INSERT Statement
The DELETE Statement
The UPDATE Statement
The REPLACE Statement
INSERT with ON DUPLICATE KEY UPDATE
The TRUNCATE TABLE Statement
Module 11: Transactions
Lessons
What is a Transaction?
Transaction Commands
Isolation Levels
Locking
Module 12: Joins
Lessons
What is a Join?
Inner Joins
Outer Joins
Cross Joins
Using Qualifiers and Aliases
Multi-Table UPDATE and DELETE Statements
Module 13: Subqueries
Lessons
Subquery Overview
Categories of Subqueries
Subqueries Types
Placing Subqueries
Other Subquery Uses
Module 14: Views
Lessons
What Are Views?
Creating Views
Updatable Views
Managing Views
Obtaining View Metadata
Module 15: Prepared Statements
Lessons
Why Use Prepared Statements?
Using Prepared Statements from the mysql Client
Preparing a Statement
Executing a Prepared Statement
Deallocating a Prepared Statement
Module 16: Exporting and Importing Data
Lessons
Exporting and Importing Data
Exporting and Importing Data Using SQL
Exporting and Importing Data Using MySQL Client Programs
