EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

MySQL Advanced

Course outline

3 days
Discuss this programme →

Duration

3 days

Overview

This course will take the participants from beginner to an advanced level in MySQL. Even if the participant is a complete beginner with only basic MYSQL knowledge but wants to understand advanced features like transactions, user permissions, triggers and stored procedures, this course can help. Database skills are highly in demand in the I.T. industry. Everything from websites to multiplayer games is likely to make use of some kind of database. MySQL is a free, industrial-strength relational database, very widely used all over the world. In fact, the MySQL Community Server is the world's most popular open-source database, used by millions of websites.

Target Participants

  • This course teaches you MySQL from the very beginning.
  • No prior knowledge is required, other than basic knowledge of your computer.
  • By the end of the course you will be able to use MySQL to an advanced level.

Program Prerequisites

The course assumes knowledge of Database

Course Objectives

Participants will learn how to:-

  • Create, query and update databases
  • Understand transactions and database isolation levels
  • Use triggers, views and stored procedures
  • Understand database users and security

Introduction

  • Introducing MySQL
  • Installing MySQL and the Workbench
  • Using the Command Line
  • Creating Tables
  • Multiple Columns, int and Data Types
  • Null Values and Not Null
  • Mysql Storage Engines and Configuration
  • SQL Modes
  • Deleting All the Data in a Table
  • Primary Keys
  • Auto Increment
  • Narrowing Down Select Statements
  • Importing and Exporting Data
  • Exporting and Importing Data
  • Exporting Using the Command Line
  • Importing Using the Command Line
  • MySQL Operators
  • Some Test Data
  • Comparison Operators
  • Logical Operators
  • 018 Exclusive Or
  • Update, Order and Limit
  • Beginning Update Statements
  • Order By
  • Limiting Results
  • MySQL Data Types
  • Mysql Types
  • Text Types
  • Floating Point Types and Ints
  • The Bit Type
  • Bool
  • Blobs
  • Time, Date and Year
  • Timestamp and Datetime
  • Enumerations
  • Brackets and Conditions Revisited
  • Aggregate Functions and Grouping
  • The distinct keyword
  • Counting Distinct Values
  • Aggregate Functions
  • Arithmetic in MySQL
  • The Health Survey Dataset
  • Group By
  • Having- Restricting Groups By Aggregate Functions
  • Foreign Keys and Multiple Tables
  • Naming Columns and Tables
  • Foreign Keys
  • ER Diagrams
  • Joins and Cartesian Products
  • Inner Joins
  • Left and Right Outer Joins
  • Joins on Multiple Tables
  • Querying Chains of Tables
  • One to Many and One to One
  • Many to Many
  • Joining Tables to Themselves
  • Restrict Foreign Keys
  • Cascade Foreign Keys
  • Combining Queries
  • Union and Union All
  • Subqueries with "in"
  • Inline Views
  • Altering Schemas
  • Adding Columns
  • Adding Foreign Keys
  • Adding Indexes
  • Indexes on Multiple Columns
  • Users and Privileges
  • Creating Users
  • Granting Privileges
  • Views
  • Creating a Simple View
  • View Algorithms
  • With Check Option
  • Locks and Transactions
  • Exclusive Table Locks
  • Shared Table Locks
  • Using Variables
  • Setting Variables with Selects
  • A Select-Update Example
  • Fixing Select-Updates with Table Locks
  • ACID
  • A Simple Transaction
  • Start Transaction
  • InnoDB Row Locking and Isolation
  • ACID Isolation Levels
  • Serializable and Row vs Table Locking
  • Demonstrating the Other Isolation Levels
  • Rolling Back to Savepoints
  • The Account Transfer Problem
  • Select for Update
  • Lock in Share Mode
  • MySQL Functions
  • String Functions
  • Dates, Intervals and Queries
  • Date Diff and Dayname
  • Str_To_Date and Date_Format
  • Control Flow Functions
  • Casting
  • Functions Exercises
  • Functions Solution
  • Stored Procedures
  • Introducing Stored Procedures
  • Changing the Delimiter
  • Hello World
  • Setting the Definer
  • Procedure Permission
  • Passing Parameters
  • Multiple Parameters
  • Select Into Variables
  • Out Parameters
  • Inout Parameters
  • The If Statement
  • Local Variables
  • Implementing Account Withdrawal
  • Transactional Withdrawal
  • Errors and Warnings
  • Handling Errors
  • While Loops
  • Labelled Loops
  • Generating Random Data
  • A Data Generating Procedure
  • Cursors
  • Fetching Cursor Data in a Loop
  • The Case Expression
  • Variable Scope and Multiple Cursors
  • Triggers
  • Introducing Triggers
  • Validating with Triggers
  • Triggers and Transactions
  • Triggers Exercise
  • Triggers Solution
  • User-Defined Functions
  • Functions vs Procedures
  • User-Defined Functions
  • Visual Design
  • Designing Databases Visually
  • Connecting with PHP
  • Retrieving Data with PHP
  • Final Exercise
  • Conclusion

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