MySQL Basic

Course Description

Duration: 3 days – 21 hrs.

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.

Objectives

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

Audience

Anyone involved in administering and developing on a MySQL relational database management system (RDBMS). Basic familiarity with database concepts is helpful, but not essential.
Database Administrators
Database Designers
Administrator

Pre- requisites

Basic knowledge in MySQL will be helpful.
Experience with any other web framework will be helpful.
Basic knowledge in terminal commands

Course Content

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

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

Day 3

Session 8: DATABASES AND TABLES

Creating a database
Selecting a database
Obtaining database metadata
Table properties
Creating tables ▪ Autoincrement
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