MySQL for Developers

• MySQL Overview, Products and Services
• MySQL Enterprise Services
• Supported operating systems and version compatibility

• Installing MySQL and the World Database

• MySQL General Architecture
• How MySQL uses Disk Space
• How MySQL uses Memory

• Invoking Client Programs
• Using Option Files
• The MySQL Client
• Current client tools: MySQL Shell and a compatible graphical SQL client
• MySQL Connectors
• Third-Party APIs

• The SELECT Statement
• Aggregating Query Results
• Using UNION

• SQL Modes
• Handling Missing or invalid Data Values
• Interpreting Error Messages

• SQL Comparisons
• Functions in SQL Expressions
• Comments in SQL Statements

• Data Type Overview
• Numeric Data Types
• Character String Data Types
• Binary String Data Types
• Temporal Data Types
• NULL values

• Metadata Access Methods
• The INFORMATION_SCHEMA Database / Schema
• Using SHOW and DESCRIBE
• The mysqlshow Command

• Database Properties
• Good Design Practices
• Identifiers
• Creating Databases
• Altering Databases
• Dropping Databases

• Creating Tables
• Table Properties
• Column Options
• Creating tables based on existing tables
• Altering Tables
• Dropping Tables
• Foreign Keys

• The INSERT Statement
• The DELETE Statement
• The UPDATE Statement
• The REPLACE Statement
• INSERT with ON DUPLICATE KEY UPDATE
• The TRUNCATE TABLE Statement

• What is a Transaction?
• Transaction Commands
• Isolation Levels
• Locking

• What is a Join?
• Joining Tables in SQL
• Basic Join Syntax
• Inner Joins
• Outer Joins
• Other Types of Joins
• Joins in UPDATE and DELETE Statements

• Types of Subqueries
• Table Subquery Operators
• Correlated and Non-Correlated Subqueries
• Converting Subqueries to Joins

• What are Views?
• Creating Views
• Updatable Views
• Managing Views
• Obtaining View Metadata

• Why use Prepared Statements?
• Using Prepared Statements from the mysql Client
• Preparing a Statement
• Executing a Prepared Statement
• De-allocating a Prepared Statement

• Exporting and Importing Data
• Exporting and Importing Data using SQL
• Exporting and Importing Data using MySQL Client Programs
• Import Data with the SOURCE Command

• What is a Stored Routine?
• Creating, Executing and Deleting Stored Routines
• Compound Statements
• Assign Variables
• Parameter Variables
• Parameter Declarations
• Flow Control Statements
• Declare and use Handlers
• Cursors

• What are Triggers?
• Creating and deleting triggers
• Restrictions on Triggers

• SQL Parser and Storage Engine Tiers
• Storage Engines and MySQL
• The MyISAM Storage Engine
• The InnoDB Storage Engine
• The MEMORY Storage Engine
• Other Storage Engines

• Overview of Optimization Principles
• Using Indexes for Optimization
• Using EXPLAIN to Analyze Queries
• Query Rewriting Techniques
• Optimizing Queries by Limiting Output
• Using Summary Tables
• Optimizing Updates
• Choosing Appropriate Storage Engines

• Aggregate and summarize data
• Analyze queries for optimization
• Choose between connectors for a given application
• Control transactions in SQL applications
• Create and store JSON documents
• Explain application development with NoSQL and XDevAPI
• Process data in JSON documents
• Store and process numeric data, spatial data, string data
• Use MySQL Shell to access document stores through X Protocol
• Use prepared statements

Select the connector and API for the application. Use parameter binding through the connector where appropriate; distinguish connector APIs from SQL PREPARE syntax. Practise transactions and JSON SQL operations, and demonstrate X DevAPI document access using a configured X Protocol environment.