| Course Code: |
RT 504 |
| Course Abstract: |
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 participant to explore database fundamentals, as well as MySQL features. Participants learn the basics of MySQL use and the programming of stored routines and triggers. Candidates also participate in database design discussions, perform administrative functions, learn about optimization and performance tuning, and explore various APIs. |
| Audience: |
This course is designed for individuals who are application and web developers, or systems administrators. |
| Duration: |
4 days |
| Learning Outcomes: |
Upon completion of this course, the participant will be able to:
> Describe MySQL’s benefits and features
> Configure the MySQL server
> Perform database design and normalization
> Work with the command-line mysql tool
> Set up and manage data and data schemas in MySQL
> Use SELECT to retrieve data from a database
> Combine data from multiple sources
> Utilize more complex SQL functionality
> Understand the MySQL storage engine types and their applicability
> Use some of the more common MySQL standalone tools
> Manage database resources and users
> Write and call stored procedures
> Optimize database storage for performance
> Connect MySQL to external programming interfaces |
| Course Topics: |
Introduction to Database Concepts and MySQL
Features of a Relational Database
Where does SQL Fit in?
Database Access
Why MySQL?
The History of MySQL
Installation, Configuration, and Upgrading
MySQL Software
MySQL Software Features
Preparing to Install MySQL
Available Client Software
After the Download
Configuring the Server
Starting the Server
The Initial User Accounts
Verifying Server Operation
Upgrading
Copying a Database between Architectures
Environment Variables
Database Design
Developing the Design of a Database
Database Entities
The Primary Key
Foreign Key Relationships
Data Models and Normalization
Second Normal Form (2NF)
Third Normal Form (3NF) and Beyond
Translating a Data Model into a Database Design
Using the mysql Command-Line Tool
Running the mysql Client
Customizing the mysql Prompt
mysql Commands
Using the Help Command
Some Useful mysql Options
Working with a Database
Examining Table Definitions
Other SHOW Options
DDL – Data Definition Language
DDL & DML Overview
Building Table Definitions
Identifiers
Column Definitions
Numeric Datatypes
ENUM and SET Types
Date and Time Datatypes
AUTO_INREMENT
UNIQUE Constraints
Primary Keys
Modifying Tables
Foreign Keys
Renaming and Dropping Tables
DML – Data Manipulation Language
DDL & DML Overview
Data Values: Numbers
Data Values: Strings
Working with NULL Values
Bulk Loading of Data
Bulk Data Format
Working with Special Values in Bulk Data
Adding New Table Rows with INSERT
Copying Rows
UPDATE
REPLACE
Removing Table Rows
Transactions
InnoDB: Using Transactional Processing
Locking Tables
Queries – The SELECT Statement
SELECT Syntax Summary
Choosing Data Sources and Destinations for SELECT
Presentation of Table Data with SELECT
Being Selective about Which Rows are Displayed
User-Defined Variables
Expressions and Functions
Control Flow Operators and Functions
Function Names
Comparison Operators and Functions
String Functions
Numeric Operators and Functions
Date and Time Functions
Forcing Data Interpretation
Miscellaneous Functions
Building a Result Set from Several Sources
UNION
Combining Data from Two Tables
Using WHERE to Choose Matching Rows
INNER JOIN
OUTER JOINs
Multiple Tables, Fields, Joins, and Ordering
SELECT * and USING Columns
MySQL Pattern Matching
Multipliers, Anchors, and Grouping
GROUP BY
Aggregates
Subqueries
Subquery Comparisons and Quantifiers
Other Subqueries
Subquery Alternatives and Restrictions
InnoDB Multi-Table Updates and Deletes
Building a VIEW
Updatable VIEWs
MySQL Storage Engines
Storage Engine Overview
Other Storage Engine Types
The Basics of Commonly Used Storage Engines
MyISAM Limits and Features
MyISAM Data File Format
InnoDB and Hardware Limitations
InnoDB Shared Tablespace Configuration
InnoDB Per-Table Tablespaces
InnoDB Data Management
MEMORY and FEDERATED
MERGE and ARCHIVE
Utilities
Client Overview
Specifying Options for Command-Line Clients
Client Option Files
Checking Tables with myisamchk and mysqlchk
Using myisamchk and mysqlchk for Repairs
mysqlshow and mysqlimport
Using mysqldump
The Query Browser
MySQL Query Browser: Deeper
MySQL Administrator: Basic Operations
MySQL Administrator: Monitoring the Server and User Administration
Third Party Tools
Administering a Database and Users
The Server-Side Programs
Starting the MySQL Server
Using SET for Server Options
Table Management
Server Log Files
mysqladmin
Backup and Restore
Miscellaneous Functions
User Account Management
Understanding User Privileges
User Account Rights Management
User Account Privileges
Managing Access to the Database Environment
Database Programmability
Stored Routines: Basic Concepts
Routine Creation and Use
Flow Control Statement
Writing Blocks of Code
Triggers
Stored Routines, Triggers, and the Binary Log
Table HANDLERs
Prepared Statements
Optimization and Performance Tuning
Hardware Limitations
Optimizing the MySQL Server’s Interaction with the External World
Adjusting the MySQL Server Configuration
Optimizing Your Database
Optimizing Queries
The Use of Indexes to Support Queries
Thinking about JOIN Queries
Query Sorts, Indexes, and Short-Circuiting
INSERT, UPDATE, DELETE, and Table Locks
Some General Optimizations
Optimizations Specific to MyISAM
Optimizations Specific to InnoDB
MySQL Programming Interfaces
Database Application Architectures
Connecting MySQL to ODBC
Connecting MySQL to MS/Office and MS/Access
Connecting to MySQL from Perl
Programming Perl to MySQL
Connecting to MySQL from PHP
Programming PHP to MySQL |
| Prerequisites: |
Prior experience installing software and programming in any language, such as HTML, is recommended but not required. |