Prerequisites
- Having attended the MySQL for Beginners course or some experience with Relational Databases and SQL
- !MySQL for Beginners (D61918)
Course Objectives
- Understand the steps necessary to invoke MySQL client programs
- Utilize the general-purpose mysql client program and the MySQL Query Browser graphical user interface (GUI) to run queries and retrieve results
- Select the best data type for representing information in MySQL
- Manage the structural characteristics of your databases and the tables within your databases
- Utilize the SELECT statement to retrieve information from database tables
- Utilize expressions in SQL statements to retrieve more detailed information
- Utilize SQL statements to modify the contents of database tables
- Write join expressions in your SQL statements to obtain information from multiple tables
- Utilize subqueries in your SQL statements
- Create views utilizing SELECT statements to produce virtual tables of specific data
- Perform bulk data import and export operations
- Create user defined variables, prepared statements and stored routines
- Create and manage triggers
- Use the INFORMATION_SCHEMA database to access metadata
- Debug MySQL applications
- Configure and Optimize MySQL
Course Content
Introduction
- MySQL Overview, Products and Services
- MySQL Enterprise Services
- Supported Operating Services
- MySQL Certification Program
- Training Curriculum Paths
- MySQL Website
- Installing MySQL and the World Database
MySQL Client/Server Concepts
- MySQL General Architecture
- How MySQL Uses Disk Space
- How MySQL Uses Memory
MySQL Clients
- Invoking Client Programs
- Using Option Files
- The MySQL Client
- MySQL Query Browser
- MySQL Connectors
- Third-Party APIs
Querying for Table Data
- The SELECT Statement
- Aggregating Query Results
- Using UNION
Handling Errors and Warnings
- SQL Modes
- Handling Missing or Invalid Data Values
- Interpreting Error Messages
SQL Expressions
- SQL Comparisons
- Functions in SQL Expressions
- Comments in SQL Statements
Data Types
- Data Type Overview
- Numeric Data Types
- Character String Data Types
- Binary String Data Types
- Temporal Data Types
- NULLs
Obtaining Metadata
- Metadata Access Methods
- The INFORMATION_SCHEMA Database/Schema
- Using SHOW and DESCRIBE
- The mysqlshow Command
Databases
- Database Properties
- Good Design Practices
- Identifiers
- Creating Databases
- Altering Databases
- Dropping Databases
Tables
- Creating Tables
- Table Properties
- Column Options
- Creating Tables Based on Existing Tables
- Altering Tables
- Dropping Tables
- Foreign Keys
Manipulating Table Data
- The INSERT Statement
- The DELETE Statement
- The UPDATE Statement
- The REPLACE Statement
- INSERT with ON DUPLICATE KEY UPDATE
- The TRUNCATE TABLE Statement
Transactions
- What is a Transaction?
- Transaction Commands
- Isolation Levels
- Locking
Joins
- What is a Join?
- Joining Tables in SQL
- Basic Join Syntax
- Inner Joins
- Outer Joins
- Other Types of Joins
- Joins in UPDATE and DELETE statements
Subqueries
- Types of Subqueries
- Table Subquery Operators
- Correlated and Non-Correlated Subqueries
- Converting Subqueries to Joins
Views
- What Are Views?
- Creating Views
- Updatable Views
- Managing Views
- Obtaining View Metadata
Prepared Statements
- Why Use Prepared Statements?
- Using Prepared Statements from the mysql Client
- Preparing a Statement
- Executing a Prepared Statement
- Deallocating a Prepared Statement
Exporting and Importing Data
- Exporting and Importing Data
- Exporting and Importing Data Using SQL
- Exporting and Importing Data Using MySQL Client Programs
- Import Data with the SOURCE Command
Stored Routines
- What is a Stored Routine?
- Creating, Executing and Deleting Stored Routines
- Compound Statements
- Assign Variables
- Parameter Declarations
- Flow Control Statements
- Declare and Use Handlers
- Cursors
Triggers
- What are Triggers?
- Delete Triggers
- Restrictions on Triggers
Storage Engines
- SQL Parser and Storage Engine Tiers
- Storage Engines and MySQL
- The MyISAM Storage Engine
- The InnoDB Storage Engine
- The MEMORY Storage Engine
- Other Storage Engines
Optimization
- Overview of Optimization Principles
- Using Indexes for Optimization
- Using EXPLAIN to Analyze Queries
- Query Rewriting Techniques
- Optimizing Queries by Limiting Output
- Using Summary Tables
- Optimizing Updates
- Choosing Appropriate Storage Engines TOPIC22 Conclusion