• No results found

D61830GC30. MySQL for Developers. Summary. Introduction. Prerequisites. At Course completion After completing this course, students will be able to:

N/A
N/A
Protected

Academic year: 2021

Share "D61830GC30. MySQL for Developers. Summary. Introduction. Prerequisites. At Course completion After completing this course, students will be able to:"

Copied!
5
0
0

Loading.... (view fulltext now)

Full text

(1)

 

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.

 

(2)

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

(3)

• 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

(4)

 

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

(5)

• 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

References

Related documents

Suppose first that there is no exogenous switching cost, and (21) holds. Let c be the unit production cost for each brand. If ) competition drives price down to marginal cost,

The present work The Waves That Heal is a brief account of Lakhovsky's theories and results obtained by the application of his methods together with a number of observations made

5 DNA quantification in alginate beads, equine collagen, gelatin, porcine collagen and chitosan constructs after 35 days of chondrogenic culture, and in control media (equine

1/2 cup green onion, finely sliced 1 tablespoon truffle or olive oil 1 teaspoon fresh dill, minced (optional) 1 teaspoon salt.. Blanch almonds by pinching them out at the

You cannot catch COVID-19 from the vaccine but it is possible to have caught COVID-19 and not realise you have the symptoms until after your vaccination appointment. The

(Specifically, the threat of having one’s systems become part of a botnet. There is also the threat of being at- tacked by a botnet, but this is a different, and arguably

Survey™ from Scalable Software is a cost management application that provides detailed, comprehensive true usage information for the desktop software, hardware and

Bu ikircikli yaklaşım; kültür mücadelesi içinde pa­ ramparça olan burjuva toplumunun özaydmlanmasım doğru güzergâha sokabilir. Post-seküler toplum dinin mitos