MySQL for Developers

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

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

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

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

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