MySQL for Beginners

This lesson describes the goals of the course and the classroom learning environment.

Lessons:

• Describe the course goals

• Explain the origin and status of the MySQL product

• List the available MySQL products and professional services

• List the currently supported operating systems

• Describe how to access MySQL information and services on Oracle and MySQL websites

• List all the available MySQL courses

• Describe the Oracle Certification Program for MySQL

• Obtain MySQL online documentation and example databases

• This practice covers reviewing the MySQL and Oracle websites that contain information about MySQL products and services

This lesson describes the nature and architecture of the MySQL Server/Client model, communication protocols and connectors.

Lessons:

• Explain the MySQL client/server model

• Identify the components of a LAMP stack, and their roles

• Explain how MySQL works with connectors

• Start the MySQL server and the mysql client

• Install the MySQL server

• Use command-line editing methods

• Install the world_innodb example database

• Use a text file to log your mysql client session

• Install and Start the MySQL Server

• Describe the communication protocols used to interact with the MySQL server

• Use the Keyboard Editing and Tee Commands

• Install the world_innodb Database

This lesson explains the concepts of relational databases and the use of data definition and data manipulation language.

Lessons:

• Define a relational database (and RDBMS)

• Describe the structure of an RDBMS database

• Identify the differences between databases and spreadsheets

• Explain the use of SQL and MySQL with relational databases

• Define and use data definition language (DDL)

• Lab:

• Define and use data manipulation language (DML)

• Quiz–Database Basics

• Identify the Structure of a Table

This lesson explores the concepts of database modelling and the implementation of referential integrity and database normalisation.

Lessons:

• Explain database modelling

• Explain how to use keys to identify a row

• Explain how to use foreign keys to achieve referential integrity

• Define and perform normalization

• Quiz–Database Design

• Prepare a design plan for a new database

• Evaluate a Database

• View a database structure

• Create a Structure Diagram

• Evaluate a database design

This lesson describes the available data types and their implementation within table design.

Lessons:

• Explain the use of data types in database design

• Explain the usage of NULL

• List the four MySQL data type categories

• Choose the correct data types for columns

• Quiz–Data Types

• Describe the special considerations for using data types

• Explain the Use of Data Types

This lesson explains the statements used for creating databases and database tables.

Lessons:

• Use the CREATE DATABASE statement

• Use the CREATE TABLE statement to add tables to a database

• Use the SHOW CREATE TABLE statement

• Describe and set column and table options

• Describe and use table indexing

• Use the SHOW INDEX statement

• Display Table Creation Information

• Describe table constraints

• Create a Database

This lesson describes how to retrieve data from a database with the SELECT statement.

Lessons:

• Retrieve database data by using the SELECT statement

• Use the SELECT statement clauses: FROM, DISTINCT, WHERE, ORDER BY, LIMIT

• Troubleshoot problems using warning and error messages

• Describe and use SQL Modes to change interpretation of SQL syntax

• Perform Basic Queries

• Use the MySQL Workbench GUI tool to submit queries

• Perform Basic Queries Using MySQL Workbench

• Perform Basic Queries on the Pets Database

This lesson describes how to manipulate databases and database tables.

Lessons:

• Use the DROP DATABASE statement to delete a database

• Modify table columns

• Discuss the issues related to dropping a database

• Add a new table to a database by using SELECT

• Remove a Database

• Add and remove indexes and constraints

• Create a New Table and Remove a Table

• Delete an entire table

• Alter Table Columns

• Add and remove columns of a table

• Modify Table Indexes and Constraints

This lesson describes how to manipulate table data through the use of INSERT, UPDATE, REPLACE and DELETE statements.

Lessons:

• Delete and modify table data

• Add new data to a table by using the INSERT and REPLACE statements

• Insert and Replace Table Row Data

• Modify data in a table by using the UPDATE statement

• Modify and Delete Table Row Data

• Remove table rows by using the DELETE statement

• Manipulate Table Row Data in the “Pets” Database

This lesson describes how to use built-in functions within SQL statements.

Lessons:

• Use built-in functions in MySQL

• Use aggregate functions with the SELECT statement

• Describe and use string functions

• Describe and use temporal functions

• Quiz – Function Basics

• Describe and use numeric functions

• Use Built-In, String, and Temporal Functions

• Describe and use control flow functions

• Use Numeric and Control Flow Functions

• Use Aggregate Functions

This lesson explains the use of join statements to obtain information from multiple tables.

Lessons:

• Explain the concept of a join

• Use the JOIN keyword to query multiple tables

• Perform Inner and Outer Joins

• Execute outer and inner joins

• Create Queries Requiring Joins

• Additional Optional Practice

This lesson describes the different types of subqueries and their usages.

Lessons:

• Recognize the different types of subqueries

• Use multiple methods for performing nested queries (subqueries)

• Perform Different Types of Queries

• Explain and perform the process of converting subqueries to joins

• Perform Several Advanced Subqueries

This lesson describes how to use MySQL’s graphical user interface tool.

Lessons:

• Explain the primary features of the MySQL Workbench GUI tool

• Use the MySQL Workbench tool

• Create a Data Model Using MySQL Workbench

• Explain the primary features of the MySQL Enterprise Monitor GUI tool

• Create a Server Instance Using MySQL Workbench

• View the MySQL Enterprise Monitor Demonstrations

• Create a Model for the Pets Database Using MySQL Workbench

This lesson provides supplemental information on various MySQL storage engines.

Lessons:

• Explain the general purpose of storage engines in MySQL

• Display and set the storage engine

• Describe the key features of the InnoDB storage engine

• Create views of tables with specific data

• Explain the basics of transactions and transaction statements

• Describe and use ACID transactions

• Display Storage Engine Information

• Define and use the INFORMATION_SCHEMA database for obtaining metadata

• Display and Create Views

• Describe the PERFORMANCE_SCHEM A database

• Obtain Metadata

• Explain the key features of MySQL Enterprise Backup

• Create a Backup of MySQL Databases

This lesson provides a summary of all the concepts covered within the preceding lessons.

Lessons:

• Describe the features and benefits of MySQL

• Explain the basics of relational databases

• Describe MySQL connectors, and their major features and differences

• Explain the SQL and MySQL languages

• Describe data/column types with regard to efficient database design

• View a database design structure and content

• Create a database design by using an efficient structure

• Extract basic database information by using the SELECT command

• Troubleshoot and identify typical syntax warnings and errors