Day 1
Introduction to MySQL
- 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