Introduction to MySQL

Course Description

This two-day hands-on course provides a comprehensive introduction to MySQL. Students will also learn database concepts.

Course Objectives

Upon successful completion of this course, students will be able to:

• Design, create and maintain MySQL databases
• Search and display data in an MySQL database
• Interact with MySQL via the command line interface
• Protect the MySQL database
• Construct efficient SQL queries incorporating MySQL extensions
• Optimize database performance and enhance reliability

Course Benefits

Students will quickly learn how to efficiently use MySQL commands in an interactive environment on Linux.

Who Should Attend

This course is valuable for anyone who is new to the MySQL environment.

Prerequisite

This course assumes students have no previous knowledge of MySQL. Basic computer experience at the user level is expected.

Method Of Instruction

Lecture, demonstrations, questions and answers, and numerous hands-on exercises.

Hands-on Exercises

Throughout this course, students perform a series of extensive hands-on exercises including:

• Creating a database with tables.
• Inserting data into a database.
• Copy, delete, move and display data in tables.
• Writing complex queries to extract data from a MySQL database.
• Importing data from external sources.
• Exporting data to files.
• Employing the MySQL command line interface to manipulate data.
• Structuring data in Third Normal Form.
• Finding strings in text.
• Use advance MySQL features and functions in building complex queries.

Course Outline

Chapter 1: Getting Started

• What is MySQL
• Why use MySQL
• How MySQL works
• The MySQL Server
• Structured Query Langauge
• Database Structure
• Building A SQL Statment
• Operators
• Rows are records; Columns are fields
• Syntax
• Quoting
• Connecting to the Database
• Users and passwords

Chapter 2: Data Structures

• Data types
• Creating a table
• Inserting data

Chapter 3: Changing Data

• Updating Records
• Deleting Records
• Query Records
• Operators
• Dates
• The DISTINCT qualifier
• Hierarchy of Conditions
• Group By
• Order By
• The HAVING Condition
• LIMIT

Chapter 4: Manipulating Structures

• Adding a Column to a Table
• Modifying a Table
• Copying Tables
• DROP
• Exporting and Importing Data

Chapter 5: Join

• What is a Join
• Cross Join
• Inner Join
• Left Join
• Adding a WHERE Clause
• The USING Clause
• Joining Several Tables
• UPDATE and DELETE Joins

Chapter 6: Administration

• Basic Permissions
• Changing Permission
• Logging In
• Creating a Database
• Granting Privileges
• Revoking Privileges
• Backing Up Data
• mysqlhotcopy
• mysqldump
• Restoring a Dump
• Optimizing a dump
• Using mysqldump to copy databases
• Remote Client Connection
• Batch Commands

Chapter 7: Normalization

• What is Normalization
• Relational Databases
• Keys
• Functional Dependency
• Stages of Normalization

Chapter 8: Advanced Queries

• AS
• ALTER
• Multiple Joins to One Table
• UNION Joins
• Creating a Temporary Table
• Using FULLTEXT Searches

Chapter 9: Intrinsic Functions

• Date Functions
• Getting the Current Date and Time
• Changing Date Values
• Extraction Functions
• String Functions
• Extracting Text from a String
• Finding Text in a String
• Transforming Strings
• Numeric Functions
• Summary Functions
• Control Functions

Chapter 10: Securing MySQL

• Removing Permissions from a Database
• Removing Permissions from a User
• Flushing Priviledges