Duration

3 days

Overview

The Introduction to MySQL course covers the basics of the SQL language as implemented by MySQL. The course is designed to give delegates practical experience in writing SQL statements using the MySQL client program and MySQL Workbench. The basic SQL statements, including the use of SQL functions and the basic table and view handling statements are introduced.

Skills Covered

Create and alter a MySQL database and query
Modify data held within such a database

Who Should Attend

A Basic knowledge of core java
Experience with any other web framework will be helpful.

Prerequisites

Web Application developer
Java Application developer

Course Modules

Module 1: Introduction to MySQL

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

Module 2: 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

Module 3: 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

Module 4: 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

Module 5: 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

Module 6: Subquires 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

Module 7: Numeric and Character Functions

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

Module 8: 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

Module 9: 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

Module 10: 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

Module 11: Managing Data

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

Module 12: Access Control

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

Module 13: Import and Export

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

Programme scope, delivery arrangements and suitability are confirmed during enquiry.

MySQL Basic