D61830GC30
MySQL for Developers
Summary
Duration Vendor Audience
5 Days Oracle Database Administrators, Developers, Web Administrators
Level Technology
Professional Oracle MySQL 5.6
Delivery Method Training Credits / Vouchers Instructor-led (Classroom) Yes
Introduction
This five day instructor led course teaches developers how to plan, design and implement applications using MySQL. Expert Oracle University instructors will teach you through realistic examples, interactive instruction and hands-on exercises using Java and PHP languages.
After taking this course, you will be able to write programs that take advantage of the power and flexibility of MySQL. You will have developed the skills to build a database that's optimal for your application, while creating fast and effective queries.
Prerequisites
Before attending this course, students must have:
• Basic knowledge of SQL
• Experience of Java, PHP or another programming or scripting language
• Experience of creating tables and queries in a relational database
Oracle requires and recommends that delegates have attended the following course prior to attending Oracle Database Program with PL/SQL.
• D61918GC30 MySQL for Beginners At Course completion
After completing this course, students will be able to:
• Use client programs and options.
• Access the database from program code.
• Build "NoSQL" applications.
• Write effective queries and stored routines.
• Inspect database metadata.
• Respond to database events with triggers.
• Handle errors and exceptions.
• Optimize SQL statements.
Course Content
Lesson 1: Introduction to MySQL This lesson describes the goals of the course and the classroom learning environment.
Lesson Topics:
• 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 Practice:
• There are no practices for this lesson.
Lesson 2: MySQL Client and Server Concepts
This lesson describes the nature and architecture of the MySQL
Server/Client model, communication protocols, connectors, API’s and storage engines.
Lesson Topics:
• Describe the MySQL client/server model
• Explain communication protocols
• Describe how the server supports storage engines
• Explain the basics of how MySQL uses memory and disk space
• Find respources for downloading MySQL products
• Start the MySQL client
• Use script files with MySQL
• Explain the basics of how MySQL uses databases
• Install and populate the world_innodb database Practice:
• Quiz – MySQL Client and Server concepts
• Review the classroom environment
• Create and populate the world_innodb database
Lesson 3: MySQL Clients This lesson explains the usage and configuration of MySQL client applications.
Lesson Topics:
• Invoke client programs in the MySQL client / server architecture
• Describe and use many features of the mysql client
• Modify client behaviour with options
• Use option files to configure clients
• Describe and use the MySQL Workbench
• Describe MySQL utilities Practice:
• Quiz – MySQL Clients and Options
• Launch and use the MySQL command line client
• Create an option files for MySQL applications
• Work with statement terminators and editing keys in MySQL command line client
• Use the MySQL command line client
interactively and in batch mode
• Use MySQL Workbench to run statements and explore databases
Lesson 4: MySQL Connectors and API’s
This lesson describes the available MySQL connectors and API’s and demonstrates how to connect to MySQL through Java and PHP.
Lesson Topics:
• Describe MySQL Connectors
• List common connectors that are available from Oracle and the MySQL community
• List reasons for writing custom MySQL programs
• Access the MySQL server by using Java and PHP
• Explain how a MySQL database can be embedded in a program
• Explain how MySQL supports NoSQL
• Describe InnoDB integration with memcached Practice:
• Quiz – MySQL clients and connectors
• Working with MySQL connectors
• Configuring the memcached plug-in for InnoDB
• Using the memcached plug-in for InnoDB
Lesson 5: Data Types
This lesson describes the available data types and their implementation within table design.
Lesson Topics:
• Describe the four major categories of data types
• Describe character sets
and collation
• Assign appropriate data types to table entities
• Describe the meaning and use of NULL and NOT NULL
• Explain the MySQL spatial data type extensions Practice:
• Quiz – Data Types
• Using numeric data types
• Use temporal data types
• Use string data types
• Use spatial data types
• Reviewing data types in the sakila database
Lesson 6: SQL Expressions This lesson explains the use of expressions in calculations, manipulations, combination of and commenting on data.
Lesson Topics:
• Use components of expressions
• Apply numeric, string, spatial, and temporal values in expressions
• Use pattern matching for string expressions
• Explain the properties of NULL values
• Employ functions in expressions
• Combine the result sets from several SELECT statements
• Write comments in SQL statements
Practice:
• Use SQL expressions
• Use SQL functions
• Use advanced SQL expressions
Lesson 7: Obtaining Metadata This lesson describes the nature and usage of metadata within a MySQL database.
Lesson Topics:
• List metadata access methods
• Recognise the structure of the
INFORMATION_SCHEMA database schema
• Use commands to view metadata
• List the differences between SHOW statements and INFRMATION_SCHEMA tables
Practice:
• Obtaining metadata
• Using the how and describe syntax
• Showing tables, databases and columns
Lesson 8: Databases This lesson explores the topics around databases creation such as normalisation and data file storage.
Lesson Topics:
• Describe the MySQL data directory
• Use best practices when designing a database structure
• Explain the process of normalisation
• Choose proper identifiers for databases
• Create a database
• Alter a database
• Drop a database Practice:
• Quiz – Normalisation
• Create a database
• Changing database character set and collation
• Removing a database
• Reviewing the configuration and normalisation of the sakila database Lesson 9: Tables
This lesson describes the creation and maintenance of database tables.
Lesson Topics:
• Assign appropriate table properties
• Assign appropriate column options
• Create a table
• Alter a table
• Display table information
• Rename a table
• Remove a table
• Assign and use foreign keys
Practice:
• Creating and viewing new tables
• Creating new tables from existing tables
• Adding, removing and modifying tables
• Removing tables from a database
• Setting foreign keys
• Additional practice
Lesson 10: Manipulating Table Data
This lesson describes the use of DML (Data Manipulation Language) to manage table data
Lesson Topics:
• Insert data in a table
• Delete data from a table
• Update data in a table
• Replace data in a table
• Truncate data from a table
Practice:
• Adding new data to a table
• Removing data from a table
• Modifying existing table row data
• Replace table data
• Avoid data insertion errors
• Empty out tables with the TRUNCATE statement
• Additional practice
Lesson 11: Transactions This lesson describes transactions within the MySQL database.
Lesson Topics:
• Use transaction commands to run multiple SQL statements concurrently
• Describe and use the ACID transaction rules
• Isolate one transaction from another
Practice:
• Using transactional control statements
• Quiz – Isolation levels and locking
• Working with transactions
Lesson 12: Joining Tables This lesson describes the different join types and their usage within the MySQL database.
Lesson Topics:
• Describe the concepts of joining tables
• Explain the construction and properties of the Cartesian product
• Use the syntax and application of different join types
• Use qualified column references and table aliases to avoid ambiguity
• List the different types of joins
• Execute inner and outer joins
• Perform a self-join
• Use multi-table UPDATE and DELETE statements Practice:
• Calculating the Cartesian product of two tables
• Create a Cartesian product by jointing two tables using a WHERE clause
• Obtaining information from specific columns in joined tables
• Using JOIN syntax
• Performing INNER JOINS
• Performing OUTER JOINS
• Updating and deleting data in joined tables
• Joining multiple tables
Lesson 13Table Subqueries This lesson describes the nature and usages of subqueries within a MySQL Database.
Lesson Topics:
• Write queries that contain nested queries
• Identify the appropriate type of subquery to use
• Use correct SQL syntax to create sunqueries
• Describe and use quantifiers for subquery comparisons
Practice:
• Quiz – Subqueries
• Placing subqueries in the SELECT and FROM clauses
• Placing subqueries in the WHERE clause
• Additional practice
Lesson 14: Views
This lesson describes the nature, creation and usage of MySQL database views.
Lesson Topics:
• Define views
• List the reasons for using views
• Create a view
• Check a view
• Alter or remove a view
• Set privileges for views Practice:
• Using the CREATE VIEW statement
• Using updatable views
• Checking views
• Obtaining view metadata
• Addition al practice
Lesson 15: Prepared Statements This lesson explores the nature of prepared statements and discusses the need and usage thereof.
Lesson Topics:
• List the reasons for using prepared statements
• Use prepared statements with the MySQL
command-line client
• Prepare, execute and deallocate prepared statements
• Use prepared statements in code with connectors Practice:
• Preparing statements in MySQL
• Programming with prepared statements
Lesson 16: Stored Routines This lesson explains the use of stored routines within the MySQL database environment.
Lesson Topics:
• Define a stored routine
• Differentiate between stored procedures and stored functions
• Create and execute stored routines
• Examine an existing stored routine
• Delete and existing stored routine
• Create stored routines with compound statements
• Assign variables in stored routines
• Create flow control statements
• Describe cursor usage and limitations
• List the limitations of stored routines Practice:
• Create a stored procedure
• Create a stored function
• Create stored routines
with compound statements
• Create a stored routine with parameter declarations
• Examine stored routines
• Delete and re-create a stored routine
• Create a stored routine with flow control statements
• Working with DECLARE CONDITION and DECLARE HANDLER statements
• Create a stored function to retrieve database metadata
• Additional practice
Lesson 17: Triggers
This lesson describes the nature and implementation of MySQL database triggers.
Lesson Topics:
• Describe triggers
• Identify when to use triggers
• Create new triggers
• Delete existing triggers
• Describe scheduling events
Practice:
• Create and drop triggers
• Additional practice
Lesson 18: Handling Errors and Warnings
This lesson describes the nature of MySQL database errors and warnings and demonstrates how to deal with them programmatically.
Lesson Topics:
• Set SQL modes to affect error output
• Handle missing or invalid data values
• Interpret error messages
• Use the SHOW WARINGS and SHOW ERRORS statements
• Examine MySQL diagnostic information
• Invoke the perror utility program
• Handle errors when coding with connectors Practice:
• Quiz – Handling errors
• Identifying errors and warnings with the MySQL client
• Handling MySQL errors when coding with connectors
Lesson 19: Optimisation and Monitoring
This lesson explores the functionality of the MySQL query optimiser and describes techniques that can be used to affect query performance.
Lesson Topics:
• Describe the role of the query optimiser
• Describe the strategies available for optimising queries
• Use the EXPLAIN statement to predict query performance
• Use indexes for optimisation
• Describe the role of MySQL Enterprise Monitor in query optimisation Practice:
• Quiz – Optimisation
• Create a table with indexes
• Altering the indexes in an existing table
• Remove an index from an existing table
• Using optimisation techniques
Lesson 20: Conclusion
This lesson provides a summary of all the concepts covered within the preceding lessons.
Lesson Topics:
• Course goals
• MySQL curriculum path
• MySQL resources
Associated Certifications & Exam This course prepares students to write Exam:
1Z0-882 MySQL 5.6 Developer Accredited to certification(s):
Oracle Certified Professional MySQL 5.6 Developer