MYSQL FOR DEVELOPERS Course: SQL-4501
PROFESSIONAL TRAINING MADE EASY
Website: www.quorse.com
MYSQL FOR DEVELOPERS
•
Course: SQL-4501; Duration: 5 Days; Instructor-led
• •
WHAT YOU WILL LEARN The MySQL for Developers course is the foundation course for Developers planning on designing and implementing 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 • •
Developers who want to create applications using MySQL Students wishing to prepare for the MySQL 5.0 Developer Certification Exams
PREREQUISITES REQUIRED PREREQUISITES:
Have attended the MySQL for Beginners course or some experience with Relational Databases and SQL
SUGGESTED PREREQUISITES:
MySQL For Beginners (SQL-4401)
COURSES OBJECTIVES: At the completion of this course, you should be able to: • Understand the steps necessary to invoke MySQL client programs • Utilize the general-purpose MySQL client program and the MySQL Query Browser graphical user interface (GUI) to run queries • Select the best data type for representing information in MySQL • Manage the structural characteristics of your databases and 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
COURSE OUTLINE Module 1 - Introduction • • • • • • •
MySQL Overview, Products and Services MySQL Enterprise Services Supported Operating Services MySQL Certification Program Training Curriculum Paths MySQL Website Installing MySQL and the World Database
Module 2 – MySQL Client / Server Concepts • • •
MySQL General Architecture How MySQL uses Disk Space How MySQL uses Memory
Module 3 – MySQL Clients • • • • • •
Invoking Client Programs Using Option Files The MySQL Client MySQL Query Browser MySQL Connectors Third-Party APIs
Module 4 – Querying For Table Data • • •
The SELECR Statement Aggregating Query Results Using UNION
Module 5 – Handling Errors And Warnings • • •
SQL Modes Handling Missing or invalid Data Values Interpreting Error Messages
Module 6 – SQL Expressions • • •
SQL Comparisons Functions in SQL Expressions Comments in SQL Statements
Module 7 – Data Types • • • • • •
Data Type Overview Numeric Data Types Character String Data Types Binary String Data Types Temporal Data Types NULLs
SQL-4501 MySQL For Developers | Page 1 of 2
PROFESSIONAL TRAINING MADE EASY
Website: www.quorse.com
Module 8 – Obtaining Metadata
Module 16 – Prepared Statements
• • • •
• •
Why use Prepared Statements? Using Prepared Statements from the mysql Client
• • •
Preparing a Statement Executing a Prepared Statement De-allocating a Prepared Statement
Metadata Access Methods The INFORMATION_SCHEMA Database / Schema Using SHOW and DESCRIBE The mysqlshow Command
Module 9 – Databases • • • • • •
Database Properties Good Design Practices Identifiers Creating Databases Altering Databases Dropping Databases
Module 10 – Tables • • • • • • •
Creating Tables Table Properties Column Options Creating Tables based in Existing Tables Altering Tables Dropping Tables Foreign Keys
Module 11 – 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
Module 12 – Transaction • • • •
What is a Transaction? Transaction Commands Isolation Levels Locking
Module 13 – 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
Module 14 – Subqueries • • • •
Types of Subqueries Table Subquery Operators Correlated and Non-Correlated Subqueries Converting Subqueries to Joins
Module 17 – Exporting And Importing Data • • • •
Exporting and Importing Data Exporting ad Importing Data using SQL Exporting and Importing Data using MySQL Client Programs Import Data with the SOURCE Command
Module 18 – Stored Routines • • • • • • • • •
What is a Stored Routine? Creating, Executing and Deleting Stored Routines Compound Statements Assign Variables Parameter Variables Parameter Declarations Flow Control Statements Declare and use Handlers Cursors
Module 19 – Triggers • • •
What are Triggers? Delete Triggers Restrictions on Triggers
Module 20 – Storage Engines • • • • • •
SQL Parser and Storage Engine Tiers Storage Engines and MySQL The MyISAM Storage Engine The InnoDB Storage Engine The MEMORY Staorage Engine Other Storage Engines
Module 21 – Optimization • • • • • • • •
Overview of Optimization Principles Using Indexes for Optimization Using EXPLAIN to Analyze Queries Query Rewriting Techniques Optimizing Queries by Limiting Output Using Summary Tables Optimizing Updates Choosing Appropriate Storage Engines
Module 15 – Views • • • • •
What are Views? Creating Views Updatable Views Managing Views Obtaining View Metadata
SQL-4501 MySQL For Developers | Page 2 of 2
Website: www.quorse.com
MYSQL FOR DEVELOPERS
•
Course: SQL-4501; Duration: 5 Days; Instructor-led
• •
WHAT YOU WILL LEARN The MySQL for Developers course is the foundation course for Developers planning on designing and implementing 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 • •
Developers who want to create applications using MySQL Students wishing to prepare for the MySQL 5.0 Developer Certification Exams
PREREQUISITES REQUIRED PREREQUISITES:
Have attended the MySQL for Beginners course or some experience with Relational Databases and SQL
SUGGESTED PREREQUISITES:
MySQL For Beginners (SQL-4401)
COURSES OBJECTIVES: At the completion of this course, you should be able to: • Understand the steps necessary to invoke MySQL client programs • Utilize the general-purpose MySQL client program and the MySQL Query Browser graphical user interface (GUI) to run queries • Select the best data type for representing information in MySQL • Manage the structural characteristics of your databases and 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
COURSE OUTLINE Module 1 - Introduction • • • • • • •
MySQL Overview, Products and Services MySQL Enterprise Services Supported Operating Services MySQL Certification Program Training Curriculum Paths MySQL Website Installing MySQL and the World Database
Module 2 – MySQL Client / Server Concepts • • •
MySQL General Architecture How MySQL uses Disk Space How MySQL uses Memory
Module 3 – MySQL Clients • • • • • •
Invoking Client Programs Using Option Files The MySQL Client MySQL Query Browser MySQL Connectors Third-Party APIs
Module 4 – Querying For Table Data • • •
The SELECR Statement Aggregating Query Results Using UNION
Module 5 – Handling Errors And Warnings • • •
SQL Modes Handling Missing or invalid Data Values Interpreting Error Messages
Module 6 – SQL Expressions • • •
SQL Comparisons Functions in SQL Expressions Comments in SQL Statements
Module 7 – Data Types • • • • • •
Data Type Overview Numeric Data Types Character String Data Types Binary String Data Types Temporal Data Types NULLs
SQL-4501 MySQL For Developers | Page 1 of 2
PROFESSIONAL TRAINING MADE EASY
Website: www.quorse.com
Module 8 – Obtaining Metadata
Module 16 – Prepared Statements
• • • •
• •
Why use Prepared Statements? Using Prepared Statements from the mysql Client
• • •
Preparing a Statement Executing a Prepared Statement De-allocating a Prepared Statement
Metadata Access Methods The INFORMATION_SCHEMA Database / Schema Using SHOW and DESCRIBE The mysqlshow Command
Module 9 – Databases • • • • • •
Database Properties Good Design Practices Identifiers Creating Databases Altering Databases Dropping Databases
Module 10 – Tables • • • • • • •
Creating Tables Table Properties Column Options Creating Tables based in Existing Tables Altering Tables Dropping Tables Foreign Keys
Module 11 – 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
Module 12 – Transaction • • • •
What is a Transaction? Transaction Commands Isolation Levels Locking
Module 13 – 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
Module 14 – Subqueries • • • •
Types of Subqueries Table Subquery Operators Correlated and Non-Correlated Subqueries Converting Subqueries to Joins
Module 17 – Exporting And Importing Data • • • •
Exporting and Importing Data Exporting ad Importing Data using SQL Exporting and Importing Data using MySQL Client Programs Import Data with the SOURCE Command
Module 18 – Stored Routines • • • • • • • • •
What is a Stored Routine? Creating, Executing and Deleting Stored Routines Compound Statements Assign Variables Parameter Variables Parameter Declarations Flow Control Statements Declare and use Handlers Cursors
Module 19 – Triggers • • •
What are Triggers? Delete Triggers Restrictions on Triggers
Module 20 – Storage Engines • • • • • •
SQL Parser and Storage Engine Tiers Storage Engines and MySQL The MyISAM Storage Engine The InnoDB Storage Engine The MEMORY Staorage Engine Other Storage Engines
Module 21 – Optimization • • • • • • • •
Overview of Optimization Principles Using Indexes for Optimization Using EXPLAIN to Analyze Queries Query Rewriting Techniques Optimizing Queries by Limiting Output Using Summary Tables Optimizing Updates Choosing Appropriate Storage Engines
Module 15 – Views • • • • •
What are Views? Creating Views Updatable Views Managing Views Obtaining View Metadata
SQL-4501 MySQL For Developers | Page 2 of 2
