- Introduction
- Client and Server Concepts
- MySQL Clients
- MySQL Connectors and APIs
- Data Types
- SQL Expressions
- Obtaining Metadata
- Databases
- Tables
- Manipulating Table Data
- Transactions
- Joining Tables
- Subqueries
- Views
- Prepared Statements
- Stored Routines
- Triggers
- Handling Errors and Warnings
- Optimization
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