MySQL for Developers Training in New Zealand

Required Prerequisites:

• Knowledgable in using the mysql command-line client
• Knowledgable in relational database concepts and MySQL data types
• Knowledgable in executing basic DDL and DML queries using SQL
• Knowledgable in Joining tables

Suggested Prerequisites:

• Developing simple web applications using PHP, Java/JSTL, or
• Basic HTML
• Knowledge of JSON (javascript object notation)

Those delegates who are attending via either virtual or extended classroom will be required to provide either dual monitors or single monitor plus a tablet device.

• Secure your connections to the MySQL server
• Use prepared statements
• Guard against SQL injection
• Investigate and handle errors and warnings
• Create database-driven web applications
• Enable fast text search
• Respond to data changes and events
• Modify table data
• Use temporal and numeric functions in expressions
• Write queries that contain nested queries
• Safeguard concurrent queries by using transactions
• Find and fix poorly-performing queries
• Use NoSQL and JSON document stores
• Write stored programs
• Create MySQL client programs using Connectors
• Gain an understanding of the MySQL Cloud Service

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

NoSQL

• NoSQL in MySQL
• Memcached plugin for InnoDB
• Memcached application development
• The JSON native data type
• JSON functions
• Generated columns

Spatial Data

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

Conclusion