Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: CLIENT/SERVER CONCEPTS

• MySQL client/server architecture
• Server modes
• Using client programs
• Logging in options
• Configuration files
• Precedence of logging in options
• Exercises:   Using client/server

Session 2: THE MYSQL CLIENT PROGRAM

• Using MySQL interactively
• The MySQL prompts
• Client commands and SQL statements
• Editing
• Selecting a database
• Help
• Safe updates
• Using script files
• Using a source file
• Redirecting output into a file
• Command line execution
• Mysql output formats
• Overriding the defaults
• Html and xml output
• Exercises:   Using the MySQL client program

Session 3: DATA TYPES

• Bit data type
• Numeric data types
• Auto_increment
• Character string data types
• Character sets and collation
• Binary string data types
• Enum and Set data types
• Temporal data types
• Timezone support
• Handling Missing Or Invalid Data Values
• SQL_MODE options
• Exercises:   Using data types

Session 4: IDENTIFIERS

• Using Quotes with identifier naming
• Case sensitivity in Identifier naming
• Qualifying columns with table and database names
• Using reserved words as identifiers
• Function names
• Exercises:   Using identifiers

Session 5: DATABASES

• Database properties
• Creating a database
• Selecting a database
• Altering databases
• Dropping databases
• Obtaining database metadata
• The SHOW command
• The INFORMATION_SCHEMA database
• The SHOW CREATE command
• Exercises:   Using databases

Session 6: TABLES AND INDEXES

• Table properties
• Creating tables
• Create table using Select or Like
• Temporary tables and memory tables
• Altering tables
• Adding columns
• Changing column widths and types
• Renaming columns
• Dropping columns
• Adding constraints
• Dropping constraints
• Renaming tables
• Change the table storage engine
• Multiple alterations
• Dropping tables
• Emptying tables
• Obtaining table metadata
• Show create table
• The information_schema
• Index introduction
• Structure of a mysql index
• Creating and dropping indexes
• Creating an index
• Altering a table to add an index
• Specifying index type
• Dropping indexes
• Obtaining Index Metadata
• Exercises:   Creating, altering and dropping tables/indexes

Session 7: QUERYING FOR DATA

• The SQL select statement and MySQL differences
• Advanced order by
• Order by and collation
• Order by with enum datatype
• Order by with Set datatype
• Ordering with distinct and group by
• Special features of union
• Limit and order by clauses
• Group By clause
• Group_concat
• Using Rollup in a Group By clause
• Exercises:   Querying for data

Session 8: SQL EXPRESSIONS AND FUNCTIONS

• Components of expressions
• Nulls
• Numeric expressions
• String expressions
• Temporal expressions
• Comparison functions
• Flow control functions
• Numeric functions
• String functions
• Temporal functions
• Exercises:   Using expressions and functions

Session 9: UPDATING DATA

• Update operations and privileges
• Inserting rows
• Insert using a set clause
• Inserting duplicate values
• Replacing rows
• Updating rows
• Update using the order by and limit clauses
• Deleting rows
• The delete and truncate statements
• Exercise:   Inserting, updating, replacing and deleting data

Session 10: CONNECTORS

• MySQL client interfaces
• Connector/j
• Connector/odbc
• Connector/net

Session 11: 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
• Exercises:   Obtaining database metadata

Session 12: DEBUGGING

• Mysql error messages
• The show statement
• Show errors
• Show count(*) errors
• Show warnings
• Show count(*) warnings
• Note messages
• The perror utility
• Exercises:   Debugging

Session 13: 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
• Exercises:   Coding joins

Session 14: 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
• Exercises:   Coding subqueriess

Session 15: 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
• Exercises:   Using views

Session 16: 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
• Exercises:   Importing and exporting

Session 17: USER VARIABLES AND PREPARED STATEMENTS

• Creating User variables
• User variables in a select
• Prepared statements
• The prepare statement
• The execute statement
• The deallocate statement
• Exercises:   Using variables and prepared statements

Session 18: 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
• Exercises:   Writing simple stored routines

Session 19: 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
• Exercises:   Writing stored routines with program logic

Session 20: STORED ROUTINES - EXCEPTION HANDLERS & CURSORS

• Dealing with errors using Exception handlers
• Cursors
• What is a cursor?
• Cursor operations
• Declaring cursors
• Opening and closing cursors
• Fetching rows
• Status checking
• Exercises:   Writing stored routines with program logic

Session 21: PROCEDURES WITH PARAMETERS

• Creating procedures with parameters
• Calling Procedures With Parameters
• Exercises:   Writing stored routines with parameters

Session 22: 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
• Exercises:   Writing functions

Session 23: TRIGGERS

• Trigger creation
• Restrictions on triggers
• The create trigger statement
• Using the old and new qualifiers
• Managing triggers
• Destroying triggers
• Required privileges
• Exercises:   Writing triggers

Session 24: THE EVENT SCHEDULER

• Event scheduler concepts
• Event scheduler configuration
• Creating, altering and dropping events
• Event scheduler monitoring
• Events and privileges
• Exercises:   Creating and using events

Session 25: 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 engine
• Exercises:   Making use of basic optimizations

Session 26: MORE ABOUT INDEXES

• Indexes and joins
• Exercises:   Investigating indexes and joins