Introduction to MySQL Training Course

Course Contents - DAY 1

Course Introduction

• • Administration and course materials
• • Course structure and agenda
• • Delegate and trainer introductions

Session 1: DATABASE CONCEPTS

• • What is a database?
• • Database management systems
• • Tables,rows and columns
• • Indexes,primary keys,unique constraints and foreign keys
• • Client-Server architecture
• • Supported data types
• • Storage engines and table types
• • The Information_Schema and MySQL Databases
• • Exercise: Using a MySQL Database

Session 2: USING THE MySQL CLIENT

• • What is the MySQL client?
• • Getting started and Logging in
• • Selecting a database
• • Client commands
• • Entering and executing SQL statements
• • Retrieving previous commands
• • Creating,editing and executing SQL files
• • Redirecting output into a file
• • Command line execution of MySQL Scripts
• • Exercise: Using the MySQL Client

Session 3: BASIC SELECT

• • The SQL SELECT statement
• • Case sensitivity
• • Quotes
• • Statement terminator
• • Syntax conventions
• • The select clause
• • The FROM clause
• • Conditions and the WHERE clause
• • Logical operators
• • The ORDER BY clause
• • Column aliases
• • Arithmetic expressions
• • Precedence of operators
• • Exercises: Querying the sample database

Session 4: LIMIT,UNION AND AGGREGATE FUNCTIONS

• • The LIMIT clause
• • UNION
• • UNION ALL
• • Aggregate functions
• • The GROUP BY clause
• • Using Rollup with GROUP BY
• • The HAVING clause
• • Exercises: Using Limit,Union and Aggregate Functions

Introduction to MySQL Training Course

Course Contents - DAY 2

Session 5: SUBQUERIES AND JOINS

• • Subqueries
• • Cartesian products
• • Joins with original syntax
• • Table aliases
• • Natural joins
• • Join using
• • Join on
• • Multi-table joins
• • Exercises: Using Subqueries and Joins

Session 6: NUMERIC AND CHARACTER FUNCTIONS

• • Function types
• • Testing functions
• • Numeric functions
• • Character functions
• • Exercise: Using Numeric and Character Functions

Session 7: DATE,TIME AND OTHER FUNCTIONS

• • Date and time column types
• • Date and time formats
• • The date format function
• • Functions to return date time
• • Functions to extract components from date time
• • Date time arithmetic
• • Miscellaneous functions
• • Exercise: Using Date,Time and other functions

Introduction to MySQL Training Course

Course Contents - DAY 3

Session 8: DATABASES AND TABLES

• • Creating a database
• • Selecting a database
• • Obtaining database metadata
• • Table properties
• • Creating tables
• • Auto_increment
• • Show create table
• • Column operations
• • Constraint operations
• • Copying tables
• • Renaming tables
• • Changing engine for tables
• • Obtaining table metadata
• • Altering tables
• • Dropping tables
• • Temporary tables
• • Memory tables
• • Exercises: Maintaining databases and tables

Session 9: INDEXES AND VIEWS

• • What is an index?
• • Structure of an index
• • Creating an index
• • Reviewing indexes
• • Composite indexes
• • Altering a table to add an index
• • Dropping indexes
• • What is a view?
• • Creating views
• • View restrictions
• • Dropping views
• • Exercise: Maintaining indexes and views

Session 10: MANAGING DATA

• • Inserting rows
• • Replacing rows
• • Updating rows
• • Deleting rows
• • The truncate statement
• • The COMMIT and ROLLBACK commands
• • Savepoints
• • Implicit commits
• • Exercise: Managing data

Session 11: ACCESS CONTROL

• • Creating users
• • Renaming users
• • Dropping users
• • Granting privileges
• • Revoking privileges
• • Exercise: Creating users,granting and revoking

Session 12: IMPORT AND EXPORT

• • Exporting using SQL
• • Importing using SQL
• • Exporting from the command line
• • Importing from the command line
• • Exercise: Export and import data