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.
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
Conclusion
· Course Overview
· MySQL Curriculum
· Course Evaluation
· Thank You!
· Q&A Session