MySQL for Developers Training Course

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: QUERYING FOR DATA

• • The SQL SELECT statement and MySQL differences
• • Advanced order by
• • ORDER BY and collation
• • ORDER BYy 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

MySQL for Developers Training Course

Course Contents - DAY 2

Session 6: 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 7: 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 8: 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

Session 9: OBTAINING DATABASE METADATA

• • What is metadata?
• • Using mysqlshow utility with MariaDB
• • 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 10: DEBUGGING

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

MySQL for Developers Training Course

Course Contents - DAY 3

Session 11: 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 12: SUBQUERIES

• • Types of subquery
• • Multiple-column subqueries
• • Correlated subqueries
• • Using the ANY,ALL and SOME operators
• • Using the EXISTS operator
• • Subqueries as scalar expressions
• • Derived Table
• • WITH Clause ( Common Table Expression - CTE )
• • Recursive CTEs
• • Using subqueries in updates and deletes
• • Exercises: Coding subqueriess

Session 13: 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 14: USING WINDOW FUNCTIONS

• • Description
• • Non-aggregate window functions
• • Using RANK,DENSE_RANK,ROW_NUMBER,NTILE
• • Window partition clause
• • Using LAG. LEAD,FIRST_VALUE,LAST_VALUE,NTH_VALUE
• • Aggregate window functions - SUM,AVG etc
• • Window frame clause
• • Exercises: Using Window Functions

Session 15: REGULAR EXPRESSION SUPPORT

• • Overview of Regular Expression
• • Regular Expression Notation
• • The REGEXP_LIKE operator
• • The REGEXP_INSTR function
• • The REGEXP_SUBSTR function
• • The REGEXP_INSTR function
• • The REGEXP_REPLACE function
• • Exercises: Regular Expression Support

MySQL for Developers Training Course

Course Contents - DAY 4

Session 16: 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 17: 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 18: 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 19: PROCEDURES WITH PARAMETERS

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

MySQL for Developers Training Course

Course Contents - DAY 5

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: 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 22: 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 23: 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 24: MORE ABOUT INDEXES

• • Indexes and joins
• • Exercises: Investigating indexes and joins