Session 1: OBTAINING DATABASE METADATA
- What is metadata?
- The mysqlshow utility
- The show and describe commands
- Describing tables
- The information_schema
- Listing tables
- Listing columns
- Listing views
- Listing key_columns_usage
Session 2: DEBUGGING
- MySQL error messages
- The show statement
- Show errors
- Show count(*) errors
- Show warnings
- Show count(*) warnings
- Note messages
- The perror utility
Session 3: JOINS
- Overview of inner joins
- Cartesian product
- Inner joins with original syntax
- Non equi-join
- Using table aliases to avoid name clashes
- Inner Joins With ISO/ANSI Syntax
- Outer Joins
- Left outer joins
- Right outer joins
- Full outer joins
- Updating multiple tables simultaneously
- Updating rows in one table based on a condition in another
- Updating rows in one table reading data from another
- Deleting from multiple tables simultaneously
- Deleting rows in one table based on a condition in another
Session 4: SUBQUERIES
- Types of subquery
- Multiple-column subqueries
- Correlated subqueries
- Using the ANY, ALL and SOME operators
- Using the EXISTS operator
- Subqueries as scalar expressions
- Inline views
- Converting subqueries to joins
- Using subqueries in updates and deletes
Session 5: VIEWS
- Why views are used
- Creating views
- View creation restrictions
- View algorithms
- Updateable views
- Altering and dropping views
- Displaying information about views
- Privileges for views
Session 6: IMPORT AND EXPORT
- Exporting using SQL
- Privileges required to export data
- Importing using SQL
- Messages when loading data
- Privileges required to load data
- Exporting from the command line
- Mysqldump main options
- Importing from the command line
- Mysqlimport main options
Session 7: USER VARIABLES AND PREPARED STATEMENTS
- Creating User variables
- User variables in a select
- Prepared statements
- The prepare statement
- The execute statement
- The deallocate statement
Session 8: INTRODUCTION TO STORED ROUTINES
- Types of stored routines
- Benefits of stored routines
- Stored routine features
- Differences between procedures and functions
- Introduction to the Block
- Declaring variables and constants
- Assigning values to variables
- Definer rights and invoker rights
- Using SELECT in stored routines
- Altering and Dropping stored routines
- Obtaining stored routine metadata
- Stored routine privileges and execution security
Session 9: STORED ROUTINES - PROGRAM LOGIC
- The IF .. THEN .. ELSEIF construct
- The CASE statement
- The basic loop
- The while loop
- The repeat loop
- The iterate statement
- Nested loops
Session 10: STORED ROUTINES - EXCEPTION HANDLERS & CURSORS
- Dealing with errors using Exception handlers
- What is a cursor?
- Cursor operations
- Declaring cursors
- Opening and closing cursors
- Fetching rows
- Status checking
Session 11: PROCEDURES WITH PARAMETERS
- Creating procedures with parameters
- Calling Procedures With Parameters
Session 12: FUNCTIONS
- What is a function?
- The create function statement
- Executing functions
- Executing functions from code
- Executing functions from SQL statements
- The deterministic and sql clauses
Session 13: TRIGGERS
- Trigger creation
- Restrictions on triggers
- The create trigger statement
- Using the old and new qualifiers
- Managing triggers
- Destroying triggers
- Required privileges
Session 14: THE EVENT SCHEDULER
- Event scheduler concepts
- Event scheduler configuration
- Creating, altering and dropping events
- Event scheduler monitoring
- Events and privileges
Session 15: BASIC OPTIMIZATIONS
- Normalisation of data to third normal form
- Using indexes for optimization
- General query enhancement
- Using Explain to analyze queries
- Choosing an INNODB or MYISAM storage enginge
Session 16: MORE ABOUT INDEXES
Session 16: MORE ABOUT INDEXES