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
• Configuration files
• Precedence
• SQL modes
• 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
• Exercises:   Using the MySQL client program

Session 3: DATA TYPES

• Bit data type
• Numeric data types
• Using Auto_increment
• Character string data types
• Binary string data types
• Enum and Set data typesTemporal data types
• Timezone support
• 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 column 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

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
• Changing the table storage engine
• Multiple alterations
• Droping 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
• Connector/j
• Connector/odbc
• Connector/net