MySQL for Developers

Course Contents - DAY 1Course Introduction

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

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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

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

• 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

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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

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

• 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

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

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

• 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

• Indexes and joins
• Exercises: Investigating indexes and joins