MySQL for Developers

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