Course Code: 1184

MySQL for Developers

Class Dates:
1/6/2021
Length:
5 Days
Cost:
$2495
Class Time:
Technology:
Database
Delivery:
Instructor-Led Training, Virtual Instructor-Led Training

Overview

  • Course Overview
  • This MySQL for Developers training 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

    Learn 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.

  • Audience
  • - Web Administrator
    - Developer
    - Database Administrators

Prerequisites

Course Details

  • Introduction
  • MySQL Overview, Products, Services
  • MySQL Services and Support
  • Supported Operating Services
  • MySQL Certification Program
  • Training Curriculum Paths
  • MySQL Documentation Resources
  • Client and Server Concepts
  • The MySQL client/server model
  • Communication protocols
  • Storage engines
  • How MySQL uses memory and disk space
  • The mysql command line client
  • Using SQL scripts
  • How MySQL uses databases
  • Installing and populating the class database
  • MySQL Clients
  • Invoking client programs
  • Features of the mysql client
  • Modifying client behavior with options
  • Configuring client behaviour with option files
  • MySQL Workbench
  • MySQL Utilities
  • MySQL Connectors and APIs
  • MySQL connectors
  • Oracle and community connectors
  • Why write custom programs?
  • Connecting to MySQL server by using Java and PHP
  • Embedding a MySQL database in a program
  • MySQL and NoSQL
  • InnoDB integration with memcached
  • Data Types
  • The major categories of data types
  • Character sets and collation
  • Choosing the correct data type
  • NULL and NOT NULL
  • The spatial data type extensions
  • SQL Expressions
  • Components of expressions
  • Applying numeric, string, spatial, and temporal values in expressions
  • Pattern matching for string expressions
  • The properties of NULL values
  • Functions in expressions
  • Combining result sets from multiple queries
  • Comments in SQL statements
  • Obtaining Metadata
  • Metadata access methods
  • The INFORMATION_SCHEMA database
  • Metadata commands
  • SHOW statements
  • Databases
  • The MySQL data directory
  • Database design best practices
  • Normalization
  • Choosing identifiers for database entities
  • Creating a database
  • Modifying a database
  • Removing a database
  • Tables
  • Table Properties
  • Column options
  • Creating a table
  • Modifying a table
  • Displaying table information
  • Renaming a table
  • Removing a table
  • Foreign Keys
  • Manipulating Table Data
  • Inserting data into a table
  • Deleting data from a table
  • Updating data in a table
  • Replacing data in a table
  • Truncating table data
  • Transactions
  • Using transactions for concurrent updates
  • The ACID transaction rules
  • Isolating transactions
  • Joining Tables
  • Table join concepts
  • The different join methods
  • Cartesian joins
  • Outer and inner joins
  • Joining a table to itself
  • Column references and table aliases
  • Multi-table UPDATE and DELETE statements
  • Subqueries
  • Nesting queries
  • Correct positioning of subqueries
  • Using the appropriate type of subquery
  • Using the correct SQL syntax to create subqueries
  • Quantifiers for subquery comparisons
  • Views
  • What are views?
  • Reasons for using views
  • Creating a view
  • Checking a view
  • Changing and removing a view
  • Setting view privileges
  • Prepared Statements
  • Reasons for using prepared statements
  • Using prepared statements from the mysql command-line client
  • Preparing, executing, and deallocating prepared statements
  • Using prepared statements in code, with Connectors
  • Stored Routines
  • Stored procedures and stored functions
  • Creating and executing stored routines
  • Examining an existing stored routine
  • Deleting an existing stored routine
  • Assigning variables in stored routines
  • Flow control statements
  • Cursor usage and limitations
  • Limitations of stored routines and stored functions
  • Triggers
  • What are triggers?
  • When to use triggers
  • Creating triggers
  • Deleting triggers
  • Scheduling events
  • Handling Errors and Warnings
  • The effect of SQL modes on error output
  • Dealing with missing or invalid data values
  • Interpreting error messages
  • Using the SHOW WARNINGS and SHOW ERRORS statements
  • MySQL diagnostic information
  • The perror utility program
  • Handling errors when coding with Connectors
  • Optimization
  • The role of the query optimizer
  • Strategies available for optimizing queries
  • Using the EXPLAIN statement to predict query performance
  • Using indexes for optimization
  • Describing the role of MySQL Enterprise Monitor in query optimization