MySQL Database

DATABASE PROGRAMMING WITH MySQL
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

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

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