| MS01 - MySQL Workshop |
|---|
| Duration | 2 Days (customisation) |
| Availability |
|
Course Description
This training course teaches the delegate all SQL statements required to read, manipulate and join MySQL tables. It is aimed at those who have little or no previous SQL experience.
Pre-requisites
A working knowledge of the host environment is advantageous but not essential.
Objectives
The aim of this course is to provide the delegate with the necessary skills to perform simple and complex queries using either the command prompt or the MySQL Query Browser.
On completion of this course the student will be able to:
- understand how tables are defined
- set up a MySQL test environment, using correctly defined tables, views and indexes
- understand how tables are defined
- describe the data types available when defining MySQL columns
- describe the importance of an Index for certain queries
- understand how views are defined
- write SELECT, UPDATE, DELETE and INSERT SQL statements
- join tables together
- use inner joins and outer joins
- write non-correlated and correlated subqueries
- use MySQL functions and the CASE statement
Environment
Development will be performed using MySQL (Version 4 or 5).
For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.
Course Details
- INTRODUCTION TO MySQL
- The Relational Model
- Data Representation
- The MySQL Environment
- Data Definition Language
- The MySQL Table
- Column Types
- INTEGER
- BIGINT
- TINYINT
- SMALLINT
- MEDIUMINT
- DECIMAL
- NUMERIC
- CHAR
- VARCHAR
- DATE
- DATE formats
- TIME
- TIMESTAMP
- DATETIME
- Null Values
- Default Values
- Indexes
- Index Columns
- DATA DEFINITION LANGUAGE
- The Structure of MySQL Objects
- Connecting to and Disconnecting from the Server
- Entering Queries
- Using the Command Prompt
- Using the Query Browser
- Schemas
- Creating and Using a Database
- Creating a Table
- Loading Data
- Column Definition
- Null Values
- User Defined Default Values
- Views (Version 5)
- Read Only Views
- Views - With Check Option
- Views Based Upon Other Views
- Creating a View of Two Tables
- Synonyms and Aliases
- Indexes
- Non-Unique Indexes
- Index Definition
- Index Design Considerations
- The Alter Statements
- The Drop Statements
- DATA MANIPULATION LANGUAGE
- SQL - Structured Query Language
- MySQL Environments
- SQL Features
- SQL Query Results
- The SELECT Statement
- The 'As' Clause
- Column concatenation
- Expressions
- Functions
- Special Registers
- The WHERE Clause
- Special Operators
- NOT Operand
- IN Operand
- LIKE Operand
- BETWEEN Operand
- Statements Using Nulls
- Column Functions
- Using 'Distinct'
- Multiple Column IN Predicate
- Multiple Column Subselect
- Multiple Column Basic Predicate
- The GROUP BY Clause
- The HAVING Clause
- The ORDER BY Clause
- Special Registers
- Current Date
- Current Time
- Current Timestamp
- User Keyword
- The UPDATE Statement
- Update with Subselect
- The DELETE Statement
- Self-Referencing UPDATE / DELETE
- The INSERT Statement
- The Mass Insert Statement
- Functions and Operators
- Operators
- Control Flow Functions
- The Case Statement
- String Functions
- Numeric Functions
- Date and Time Functions
- Full-Text Search Functions Date, Time and Timestamp Functions
- JOINS
- Cartesian Joins
- Inner Joins
- Outer Joins
- Nested Table Expression
- The UNION Statement
- Fullselects and Subselects
- Union Example in a View
- Union Example in a Table Specification
- Union Example within a Where Clause
- Union Example within an Insert or Update
- Subqueries
- Subqueries Using in
- Exists
- The 'All' Subquery
- The 'Any' Or 'Some' Subquery
- DO Statement
- TRUNCATE Statement
|
|
Course Format
The course contains many practical exercises to ensure familiarity with the product. Students write many queries to read, join and manipulate MySQL data.
The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.
| 
