EncartaLabs

MySQL

( Duration: 4 Days )

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

By attending MySQL workshop, Participants will learn:

  • Using client programs to access a MySQL database
  • Creating, altering and dropping a MySQL database
  • Writing SQL statements to select data from a MySQL database
  • Writing joins and subqueries
  • Using SQL expressions
  • Using SQL aggregate functions and scalar functions
  • Inserting, updating, deleting and replacing rows
  • Creating and altering tables, indexes and views
  • Using transactions
  • Granting and revoking access privileges on tables and views
  • Exporting and importing data

There are no formal pre-requisites, although an understanding of databases and exposure to information technology in general would be useful.

MySQL class is suitable for anyone who needs to access and work with a MySQL Database.

COURSE AGENDA

1

Client/Server Concepts

  • General MySQL Architecture
  • Invoking Client Programs
  • Server SQL Modes
2

MySQL Client Program

  • Using mysql Interactively
  • Statement Terminators
  • The mysql Prompts
  • Editing Keys in mysql
  • Using Script Files with MySQL
  • mysql Output Formats
  • Client Commands and SQL Statements
  • Using Server-Side Help
  • Using the -safe-updates Option
3

Data Types

  • Numeric Data Types
  • The BIT Data Type
  • String Data Types
  • Temporal Data Types
  • Column Attributes
  • Using the AUTO_INCREMENT Column Attribute
  • Handling Missing or Invalid Data Values
4

Identifiers

  • Identifier Syntax
  • Case Sensitivity
  • Using Qualified Names
  • Reserved Words as Identifiers
5

Databases

  • Database Properties
  • Creating Databases
  • Altering Databases
  • Dropping Databases
  • Obtaining Database Metatdata
6

Tables and Indexes

  • Table Properties
  • Creating Tables
  • Altering Tables
  • Emptying Tables
  • Dropping TablesIndexes
  • Indexes
  • Dropping Indexes
  • Obtaining Table and Index Metadata
7

Querying for Data

  • Using SELECT to Retrieve Data
  • Specifying Which Columns to Retrieve
  • Specifying Which Rows to Retrieve
  • Aggregating Results
  • Grouping Results
  • Using UNION
8

SQL Expressions

  • Components of SQL Expressions
  • Numeric Expressions
  • String Expressions
  • Temporal Expressions
  • NULL Values
  • Functions in SQL Expressions
  • Functions in SQL Expressions
  • Comments in SQL Statements
9

Updating Data

  • Update Operations
  • The INSERT Statement
  • The REPLACE Statement
  • The UPDATE Statement
  • The DELETE and TRUNCATE TABLE Statements
  • Privileges Required for Update Statements
10

Joins

  • Overview
  • Writing Inner Joins
  • Writing Outer Joins
  • Resolving Name Clashes Using Qualifiers and Aliases
  • Multiple-Table UPDATE and DELETE Statements
11

Multiple-Table UPDATE and DELETE Statements

  • Types of Subqueries
  • Subqueries as Scalar Expressions
  • Correlated Subqueries
  • Comparing Subquery Results to Outer Query Columns
  • Comparison Using Row Subqueries
  • Using Subqueries in the FROM Clause
  • Converting Subqueries to Joins
  • Using Subqueries in Updates
12

Views

  • Reasons to Use Views
  • Creating Views
  • Altering Views
  • Dropping Views
  • Checking Views
  • Displaying Information About Views
  • Privileges for Views
13

Importing and Exporting Data

  • Import and Export Operations
  • Importing and Exporting Using SQL
  • Importing and Exporting Data from the Command Line
14

User Variables

  • User Variable Syntax
  • User Variable Properties
15

Prepared Statements

  • Benefits of Prepared Statements
  • Using Prepared Statements from the mysql Client
  • Preparing a Statement
  • Executing a Prepared Statement
  • Deallocating a Prepared Statement
16

Stored Routines

  • Benefits of Stored Routines
  • Differences Between Stored Procedures and Functions
  • The Namespace for Stored Routines
  • Defining Stored Routines
  • Creating Stored Routines
  • Altering Stored Routines
  • Dropping Stored Routines
  • Invoking Stored Routines
  • Obtaining Stored Routine Metadata
  • Stored Routine Privileges and Execution Security
17

Triggers

  • Reasons to Use Triggers
  • Trigger Concepts
  • Creating a Trigger
  • Restrictions on Triggers
  • Referring to Old and New Column Values
  • Destroying a Trigger
  • Privileges Required for Triggers
18

Obtaining Database Metadata

  • Overview of Metadata Access Methods
  • Using INFORMATION_SCHEMA to Obtain Metadata
  • Using SHOW and DESCRIBE to Obtain Metadata
  • Using mysqlshow to Obtain Metadata
19

Debugging MySQL Applications

  • Interpreting Error Messages
  • The SHOW WARNINGS Statement
  • The SHOW ERRORS Statement
  • The perror Utility
20

Basic Optimizations

  • Overview of Optimization Principles
  • Using Indexes for Optimization
  • General Query Enhancement
  • Choosing Appropriate Storage Engines
  • Normalization
21

MySQL Architecture

  • Client/Server Overview
  • Communication Protocols
  • The SQL Parser and Storage Engine Tiers
  • How MySQL Uses Disk Space
  • How MySQL Uses Memory
22

Starting, Stopping, and Configuring MySQL

  • Types of MySQL Distributions
  • Starting and Stopping MySQL Server on Windows
  • Starting and Stopping MySQL Server on Unix
  • Runtime MySQL Configuration
  • Log and Status Files
  • Loading Time Zone Tables
  • Security-Related Configuration
  • Setting the Default SQL Mode
  • Upgrading MySQL
23

Client Programs for DBA Work

  • Overview of Administrative Clients
  • mysql
  • mysqladmin
  • mysqlimport
  • mysqldump
  • Client Program Limitations
24

Character Set Support

  • Performance Issues
  • Choosing Data Types for Character Columns

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 4,000 Modules on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting http://encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top