MySQL Training Classes

Overview
Organizations use large SQL databases to store information about clients, products, or sales. However, that data is locked into those databases until it is accessed by a business intelligence tool such as Power BI, Tableau, or Crystal Reports. Analysts and Data Scientists can use SQL to select the data they need for additional analysis and reporting. This class, MySQL Querying: Fundamentals, provides an overview of the SQL statements that can be used to access that data. This course covers MySQL 8.0.

You will:
• Connect to the MySQL Server database and execute a simple query.
• Include a search condition in a simple query.
• Use various functions to perform calculations on data.
• Organize the data obtained from a query before it is displayed on-screen.
• Retrieve data from multiple tables.

Target Student
This course is intended for individuals with basic computer skills, familiar with concepts related to database structure and terminology, and who want to use SQL to query databases.

Prerequisites
Some familiarity with database terminology and structure is helpful.

Course Outline

How to retrieve data from a single table
An introduction to the SELECT statement
How to code the SELECT clause
How to code the WHERE clause
How to code the ORDER BY clause
How to code the LIMIT clause

How to retrieve data from two or more tables
How to work with inner joins
How to work with outer joins
Other skills for working with joins
How to work with unions
How to code summary queries
How to work with aggregate functions
How to group and summarize data
How to code aggregate window functions

How to code subqueries
An introduction to subqueries
How to code subqueries in the WHERE clause
How to use the ALL keyword
How to code subqueries in other clauses
How to work with complex queries
How to work with common table expressions

How to work with data types
The data types
How to convert data

How to use functions
How to work with string data
How to work with numeric data
How to work with date/time data
Other functions you should know about

Overview
MySQL® is the open source community's most popular Relational Database Management System (RDBMS) offering, and is a key part of LAMP – Linux™, Apache™, MySQL®, PHP/Perl/Python®. Many Fortune 500 companies adopt MySQL to reap the benefits of an open source, platform-independent RDMS, such as simplifying conversion from other platforms and lowering database Total Cost of Ownership by 90%. This class encourages the student to explore database fundamentals, as well as MySQL features. Students learn the basics of MySQL use and the programming of stored routines. This course covers MySQL 8.0.

Audience
Application and web developers.

Prerequisites
Prior experience installing software and programming in any language, such as HTML, is recommended but not required.

COURSE OUTLINE

An introduction to relational databases and SQL
An introduction to client/server systems
An introduction to the relational database model
An introduction to SQL and SQL-based systems
The SQL statements
How to use SQL from an application program

How to use MySQL Workbench and other development tools
An introduction to MySQL Workbench
How to use MySQL Workbench to run SQL statements
How to use the MySQL Reference Manual
How to use the MySQL Command Line Client

How to retrieve data from a single table
An introduction to the SELECT statement
How to code the SELECT clause
How to code the WHERE clause
How to code the ORDER BY clause
How to code the LIMIT clause

How to retrieve data from two or more tables
How to work with inner joins
How to work with outer joins
Other skills for working with joins
How to work with unions

How to insert, update, and delete data
How to create test tables
How to insert new rows
How to update existing rows
How to delete existing rows

How to code summary queries
How to work with aggregate functions
How to group and summarize data
How to code aggregate window functions

How to code subqueries
An introduction to subqueries
How to code subqueries in the WHERE clause
How to use the ALL keyword
How to code subqueries in other clauses
How to work with complex queries
How to work with common table expressions

How to work with data types
The data types
How to convert data

How to use functions
How to work with string data
How to work with numeric data
How to work with date/time data
Other functions you should know about

How to create databases, tables, and indexes
How to work with databases
How to work with tables
How to work with indexes
How to use MySQL Workbench
How to work with character sets and collations
How to work with storage engines

How to create views
An introduction to views
How to work with views

Language skills for writing stored programs
An introduction to stored programs
How to write procedural code

How to use transactions and locking
How to work with transactions
How to work with concurrency and locking

How to create stored procedures and functions

How to code stored procedures
How to code stored functions
How to use Workbench with procedures and functions

Overview
The new version 8.0 of MySQL added a significant number of administrative features to the MySQL database including improved error logging, password policies, privileges, accounts, and backup and recovery. Students will perform these administration functions using the MySQL workbench and command line utilities during the course of training. The course may be taken by itself as a one day course of training or as a four day course which includes the topics from this course and the topics from the three day Querying MySQL course.

Audience
Database administrators.

Prerequisites
Prior experience with MySQL Querying.

COURSE OUTLINE

How to design a database
How to use MySQL Workbench for database design
How to open an existing EER model
How to create a new EER model
How to work with an EER model
How to work with an EER diagram

How to create triggers and events
How to work with triggers
How to create a BEFORE trigger
How to use a trigger to enforce data consistency
How to create an AFTER trigger
How to view or drop triggers
How to work with events
How to turn the event scheduler on or off
How to create an event
How to view, alter, or drop events

An introduction to database administration
Database administration concepts
Database administrator responsibilities
Types of database files
Types of log files
How to monitor the server
How to view the server status
How to view and kill processes
How to view the status variables
How to view the system variables
How to configure the server
How to set system variables using MySQL Workbench
How to set system variables using a text editor
How to set system variables using the SET statement

How to secure a database
An introduction to user accounts
An introduction to SQL statements for user accounts
A summary of privileges
The four privilege levels
The grant tables in the mysql database
How to work with users and privileges
How to create, rename, and drop users
How to specify user account names
How to grant privileges
How to view privileges
How to revoke privileges
How to change passwords
A script that creates users
How to work with roles
How to create, manage, and drop roles
A script that creates users and roles
How to use MySQL Workbench
How to work with users and privileges
How to connect as a user for testing

How to back up and restore a database
Strategies for backing up and restoring a database
A backup strategy
A restore strategy
How to back up a database
How use mysqldump to back up a database
A SQL script file for a database backup
How to set advanced options for a database backup
How to restore a database
How to use a SQL script file to restore a full backup
How to execute statements in the binary log
How to import and export data
How to export data to a file
How to import data from a file
How to check and repair tables
How to use the CHECK TABLE statement
How to repair a MyISAM table
How to repair an InnoDB table
How to use the mysqlcheck program
How to use the myisamchk program