MySQL for Developers

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

- Using advanced features of the MySQL Client
- Using advanced data types
- Managing the structure of databases and tables
- Managing and using indexes
- Writing complex SQL query statements
- Using advanced SQL expressions
- Using advanced SQL functions
- Performing advanced Insert, Update, Delete, Replace and Truncate Operations
- Using user variable syntax and properties
- Importing and exporting data from within MySQL
- Importing and exporting data from the command line
- Performing complex joins to access multiple tables
- Performing complex subqueries
- Creating, managing and using views
- Using MySQL Connectors
- MySQL and NoSQL integration
- Using prepared statements
- Creating and using stored routines
- Creating and using triggers
- Obtaining database metadata
- Optimizing queries
- Working with the main storage engines
- Debugging MySQL applications

MySQL Developers who have a basic understanding of a MySQL database and SQL commands as covered on the Introduction to MySQL course.

A working knowledge of MySQL is required. This can be gained by attendance on the Introduction to MySQL course.

- Using advanced features of the MySQL Client
- Using advanced data types
- Managing the structure of databases and tables
- Managing and using indexes
- Writing complex SQL query statements
- Using advanced SQL expressions
- Using advanced SQL functions
- Performing advanced Insert, Update, Delete, Replace and Truncate Operations
- Using user variable syntax and properties
- Importing and exporting data from within MySQL
- Importing and exporting data from the command line
- Performing complex joins to access multiple tables
- Performing complex subqueries
- Creating, managing and using views
- Using MySQL Connectors
- MySQL and NoSQL integration
- Using prepared statements
- Creating and using stored routines
- Creating and using triggers
- Obtaining database metadata
- Optimizing queries
- Working with the main storage engines
- Debugging MySQL applications