Detailed Course Outline
Course Contents - DAY 1
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
- MySQL Utilities
- 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
- Spatial Datatypes
- 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
Course Contents - DAY 2
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
- MySQL connectors
- Oracle and community conectors
- Connecting to MySQL server using Java and PHP connectors
- MySQL and NoSQL
- Innodb integration with memcached
Course Contents - DAY 3
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
Course Contents - DAY 4
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
- Using prepared statements in code, with connectors
- 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
Course Contents - DAY 5
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: 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
- Using MySQL Enterprise Monitor in query optimization
- Exercises: Making use of basic optimizations
Session 25: MORE ABOUT INDEXES
- Indexes and joins
- Exercises: Investigating indexes and joins