My SQL for Developers - Classroom

MySQL for Developers

Introduction 

• Client / Server Concepts
• General MySQL Architecture
• Invoking Client Programs
• General Command Option Syntax
• Connection Parameter Options
• Using Option Files
• Selecting a Default Database
• Establishing A Connection with a GUI Client
• Server SQL Modes

The MySQL Client Program

• Using MySQL Interactively
• Statement Terminators
• The MySQL Prompts
• Using Editing Keys in MySQL
• Using Script Files with MySQ
• MySQL Output Formats
• Client Commands and SQL Statements
• Using Server-Side Help
• Using the -safe-updates Option

MySQL Query Browser

• Capabilities
• Using MySQL Query Browser
• Using the Query Window
• Entering Queries
• The Result Area
• The Script Editor
• Stored Routine Management
• Object and Information Browsers

The MySQL Table Editor

• Connection Management
• Using the Connection Dialog
• Editing Connection Profiles
• The Options Dialog

MySQL Connectors

• MySQL Client Interfaces
• MySQL Connector/ODBC
• MySQL Connector/J
• MySQL Connector/NET

Data Types

• Overview
• Numeric Data Types
• Integers
• Floating-Point
• Fixed-Point
• The Bit Data Type
• String Data Types
• Character Set Support
• Non-Binary String Data Types
• Char
• Varchar
• Text
• Binary String Data Types
• BINARY
• VARBINARY
• BLOB
• The ENUM and SET Data Types
• Temporal Data Types
• DATE
• TIME
• DATETIME
• TIMESTAMP
• Per-Connection Time Zone Support
• Column Attributes
• Numeric Column Attributes
• String Column Attributes
• General Column Attributes
• Using the AUTO-INCREMENT Column Attribute
• Handling Missing or Invalid Data Types
• Missing Values
• Invalid Values in Non-Strict Mode
• Invalid Values in Strict Mode
• Enabling Additional Input Data Restrictions
• Overriding Input Data Restrictions

Identifiers

• Syntax
• Case Sensitivity
• Using Qualified Names
• Using Reserved Words as Identifiers

Databases

• Properties
• Creating Databases
• Altering Databases
• Dropping Databases
• Obtaining Database Metadata

Tables and Indexes

• Table Properties
• Creating Tables
• Using and Explicit Definition
• Specifying the Storage Engine for a Table
• Creating Tables Based on Existing Tables
• Using TEMPORARY Tables
• Altering Tables
• Adding or Dropping Columns
• Modifying Existing Columns
• Renaming a Table
• Specifying Multiple Table Operations
• Dropping Tables
• Emptying Tables
• Indexes
• Types
• Creating
• Choosing an Indexing Algorithm
• Dropping Indexes
• Obtaining Table and Index Metadata

Querying For Data

• Using SELECT
• Specifying Which Columns to Retrieve
• Specifying Which Rows to Retrieve
• Aggregating Results
• Grouping Results
• Using UNION

SQL Expressions

• Components of SQL Expressions
• Numeric Expressions
• String Expressions
• Temporal Expressions
• NULL Values
• Functions in SQL Expressions
• Aggregate Functions
• Math Functions
• String Functions
• Temporal Functions
• NULL-Related Functions
• Comments
• Updating Data
• Update Operations
• INSERT Statement
• REPLACE Statement
• UPDATE Statement
• DELETE Statement
• TRUNCATE TABLE Statement
• Privileges For Update Statement

Joins

• Overview
• Inner Joins
• Outer Joins
• Resolving Name Clashes using Qualifiers and Aliases
• Multiple Table Update and Delete Statements

Sub-queries

• Types
• Sub-queries as Scalar Expressions
• Correlated Sub-queries
• Using ALL, ANY, SOME
• Using IN
• Using EXISTS
• Comparison Using Row Sub-queries
• Using Sub-queries in the FROM Clause
• Converting Sub-queries to Joins
• Using Sub-queries in Updates

Views

• Reasons to Use Views
• Creating Views
• Altering Views
• Dropping Views
• Checking Views
• Obtaining View Metadata
• Privileges Required for Views

•  

Importing and Exporting Data

• Import and Export Operations
• Using SQL
• Importing and Exporting from the Command Line

User Variables

• User Variable Syntax
• User Variable Properties

Prepared Statements

• Benefits
• Using Prepared Statements from the MySQL Client
• Preparing a Statement
• Executing a Prepared Statement
• Deallocating a Prepared Statement

Stored Procedures and Functions

• Benefits of Stored Routines
• Differences Between Stored Procedures and Functions
• The Namespace for Stored Routines
• Defining Stored Routines
• Altering Stored Routines
• Dropping Stored Routines
• Invoking Stored Routines
• Obtaining Stored Routine Metadata

Triggers

• Reasons to use a Trigger
• Trigger Concepts
• Creating a Trigger
• Trigger Restrictions
• Referring to Old and New Column Values
• Destroying a Trigger
• Trigger Privileges

Debugging MySQL Applications

• Interpreting MySQL Error Messages
• SHOW WARNINGS Statement
• SHOW ERRORS Statement
• The perror Utility

Basic Optimizations

• Overview of Optimization Principles
• Using Indexes for Optimization
• Query Enhancement
• Choosing Appropriate Storage Engines
• Normalization