MySQL

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

  • 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
  • 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
  • General MySQL Architecture
  • Invoking Client Programs
  • Server SQL Modes
  • Identifier Syntax
  • Case Sensitivity
  • Using Qualified Names
  • Reserved Words as Identifiers
  • Database Properties
  • Creating Databases
  • Altering Databases
  • Dropping Databases
  • Obtaining Database Metatdata
  • Table Properties
  • Creating Tables
  • Altering Tables
  • Emptying Tables
  • Dropping TablesIndexes
  • Indexes
  • Dropping Indexes
  • Obtaining Table and Index Metadata
  • Using SELECT to Retrieve Data
  • Specifying Which Columns to Retrieve
  • Specifying Which Rows to Retrieve
  • Aggregating Results
  • Grouping Results
  • Using UNION
  • Components of SQL Expressions
  • Numeric Expressions
  • String Expressions
  • Temporal Expressions
  • NULL Values
  • Functions in SQL Expressions
  • Functions in SQL Expressions
  • Comments in SQL Statements
  • Update Operations
  • The INSERT Statement
  • The REPLACE Statement
  • The UPDATE Statement
  • The DELETE and TRUNCATE TABLE Statements
  • Privileges Required for Update Statements
  • Overview
  • Writing Inner Joins
  • Writing Outer Joins
  • Resolving Name Clashes Using Qualifiers and Aliases
  • 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
  • Reasons to Use Views
  • Creating Views
  • Altering Views
  • Dropping Views
  • Checking Views
  • Displaying Information About Views
  • Privileges for Views
  • Import and Export Operations
  • Importing and Exporting Using SQL
  • Importing and Exporting Data from the Command Line
  • User Variable Syntax
  • User Variable Properties
  • Benefits of Prepared Statements
  • Using Prepared Statements from the mysql Client
  • Preparing a Statement
  • Executing a Prepared Statement
  • Deallocating a Prepared Statement
  • 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
  • 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
  • Overview of Metadata Access Methods
  • Using INFORMATION_SCHEMA to Obtain Metadata
  • Using SHOW and DESCRIBE to Obtain Metadata
  • Using mysqlshow to Obtain Metadata
  • Interpreting Error Messages
  • The SHOW WARNINGS Statement
  • The SHOW ERRORS Statement
  • The perror Utility
  • Overview of Optimization Principles
  • Using Indexes for Optimization
  • General Query Enhancement
  • Choosing Appropriate Storage Engines
  • Normalization
  • Client/Server Overview
  • Communication Protocols
  • The SQL Parser and Storage Engine Tiers
  • How MySQL Uses Disk Space
  • How MySQL Uses Memory
  • 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
  • Overview of Administrative Clients
  • mysql
  • mysqladmin
  • mysqlimport
  • mysqldump
  • Client Program Limitations
  • Performance Issues
  • Choosing Data Types for Character Columns