Course Content

Introduction to MySQL

• MySQL overview
• MySQL Enterprise Edition
• MySQL on the Web
• The MySQL community
• What is MySQL Cloud Service
• What it means to provision a MySQL Cloud Service instance
• How an instance of MySQL Cloud Service differs from having an on-premises instance
• How MySQL course content covers material that applies whether you run MySQL locally or in MySQL Cloud Service

Connectors and APIs

• Introducing MySQL Connectors
• PHP Connectors
• Connector/J
• Connector/Python
• Embedding MySQL in an application
• Integration with MySQL Fabric

Using Connectors

• PHP, Java, and Python Connectors
• Reusing connections
• Dealing with special characters and null values
• Storing and retrieving connection details
• Creating secure connections to the MySQL server

Prepared Statements

• Reasons for using prepared statements
• User-defined variables
• Preparing, executing, and deallocating prepared statements
• Using prepared statements with Connectors

Handling Errors and Warnings

• SQL modes
• Interpreting errors and warnings
• MySQL diagnostics
• Error and exception handling with Connectors

Building Database-Driven Web Applications

• Anatomy of a database-driven web application
• Different methods of displaying query results
• Web forms and processing requests
• Using hyperlinks for navigation
• Displaying query results across multiple pages
• Dynamic sorting of query results

Tables and Views

• Creating new tables from existing tables
• Temporary tables
• Using mysqldump
• Working with views

Working with Strings

• Overview of working with strings
• String functions
• Sorting and comparing strings
• Matching string patterns with regular expressions
• Using FULLTEXT search

Working with Numeric and Temporal Data

• Numeric expressions
• Temporal expressions
• Interval arithmetic
• Numeric functions
• Temporal functions

Subqueries

• Subquery: overview
• Scalar subqueries
• Row subqueries
• Table subqueries
• Correlated and non-correlated subqueries
• Subqueries in data modification statements

Modifying Table Data

• Using the INSERT statement
• Retrieving the ID of the last-inserted record
• Using the DELETE statement
• Using the UPDATE and REPLACE statements
• Using the TRUNCATE statement

Transactions

• Overview of transactions
• Transactional statements
• Using transactions within programs
• Consistency issues
• Isolation levels
• Locking

Query Optimization

• Optimization strategies
• The MySQL Query Optimizer
• Indexing
• Query analysis with the EXPLAIN statement
• Rewriting queries
• MySQL Enterprise Monitor

Stored Routines

• Creating stored routines
• Variables in stored routines
• Parameters in stored routines
• Managing stored routines in the database
• Using flow control statements and constructs
• Using cursors
• Limitations of stored routines

Triggers and Scheduled Events

• Triggers
• Scheduled Events

Reporting

• Aggregating data
• Summary tables
• Grouping data
• Crosstab reports
• Bar charts
• Decision tables

Spatial Data

• Spatial data support in MySQL
• The MySQL spatial data types
• Spatial data formats
• Using spatial indexes for analysis
• Useful spatial data functions

JSON Data

• Benefits and characteristics of JavaScript Object Notation (JSON)
• Native MySQL JSON data type
• JSON functions to create and manipulate JSON data
• Index JSON data with generated columns

Document Store

• MySQL implements NoSQL
• Document Store
• X DevAPI functions and chaining
• Using X DevAPI with PHP to access a document store
• Using X DevAPI with Connector/J to access a document store
• Using MySQL shell to access JSON
• Issue Python statements form MySQL Shell