MySQL Introduction

Course Overview

Prerequisites

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

Target Audience for this Course

• • Anyone who needs to access and work with a MySQL Database.

Skills Gained from this Training

• • 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

Course Content

• DATABASE CONCEPTS

  What is a database?
  Database management systems
  Tables, rows and columns
  Indexes, primary keys, unique constraints and foreign keys
  Client-Server architecture
  Supported data types
  Storage engines and table types
  The Information_Schema and MySQL Databases

• USING THE MySQL CLIENT

  What is the MySQL client?
  Getting started and Logging in
  Selecting a database
  Client commands
  Entering and executing SQL statements
  Retrieving previous commands
  Creating, editing and executing SQL files
  Redirecting output into a file
  Command line execution of MySQL Scripts

• BASIC SELECT

  The SQL SELECT statement
  Case sensitivity
  Quotes
  Statement terminator
  Syntax conventions
  The select clause
  The FROM clause
  Conditions and the WHERE clause
  Logical operators
  The ORDER BY clause
  Column aliases
  Arithmetic expressions
  Precedence of operators

• LIMIT, UNION AND AGGREGATE FUNCTIONS

  The LIMIT clause
  UNION
  UNION ALL
  Aggregate functions
  The GROUP BY clause
  Using Rollup with GROUP BY
  The HAVING clause

• SUBQUERIES AND JOINS

  Subqueries
  Cartesian products
  Joins with original syntax
  Table aliases
  Natural joins
  Join using
  Join on
  Multi-table joins

• NUMERIC AND CHARACTER FUNCTIONS

  Function types
  Testing functions
  Numeric functions
  Character functions

• DATE, TIME AND OTHER FUNCTIONS

  Date and time column types
  Date and time formats
  The date format function
  Functions to return date time
  Functions to extract components from date time
  Date time arithmetic
  Miscellaneous functions

• DATABASES AND TABLES

  Creating a database
  Selecting a database
  Creating tables
  Auto_increment
  Show create table
  Column operations
  Constraint operations
  Copying tables
  Renaming tables
  Changing engine for tables
  Dropping tables
  Temporary tables

• INDEXES AND VIEWS

  What is an index?
  Creating an index
  Reviewing indexes
  Dropping indexes
  What is a view?
  Creating views
  View restrictions
  Dropping views

• MANAGING DATA

  Inserting rows
  Replacing rows
  Updating rows
  Deleting rows
  The truncate statement
  The COMMIT and ROLLBACK commands
  Savepoints
  Implicit commits

• ACCESS CONTROL

  Creating users
  Renaming users
  Dropping users
  Granting privileges
  Revoking privileges

• IMPORT AND EXPORT

  Exporting using SQL
  Importing using SQL
  Exporting from the command line
  Importing from the command line

Included with this course

• • Comprehensive course materials and exercises