MySQL 5 Development (Part 2)

Introduction
Welcome
What the Course Will Cover
System Requirements
Brief Review of Part 1
    
Configuration
Startup Options
Configuration File
Important Configuration Options

Manipulating Data
Data Manipulation Basics
Warnings and Errors
SQL Modes
Changing Syntax Interpretation
Elevating Warnings to Errors
Interpreting Error Messages
Note Warnings
System Error Warnings

Inserting Data
Inserting Data
Inserting Data Archaic Syntax
INSERT Demonstrations
Default Values on Insert
AUTO_INCREMENT on Insert
AUTO_INCREMENT Issues
LAST_INSERT_ID Function
Common INSERT Errors

Deleting Data
Deleting Data
DELETE with WHERE
DELETE with LIMIT
DELETE with ORDER BY
DELETE Result Messages

Updating Data
Updating Data
UPDATE with WHERE
UPDATE with LIMIT
UPDATE with ORDER BY
UPDATE Result Messages

Other MySQL DML Commands
REPLACE Command
REPLACE Demo
ON DUPLICATE KEY UPDATE Option
ON DUPLICATE KEY UPDATE Demo
TRUNCATE Command
TRUNCATE Demonstration
Safe Updates Mode

Importing Data
Importing Data
Basic Import Demonstrations
Importing Non-Tab Formats
Skipping Header Lines on Import
Changing Import Column Order
Converting Data on Import pt. 1
Converting Data on Import pt. 2
Importing on the Shell Command Line

Deriving New Tables
New Tables from Existing Tables
CREATE TABLE Using SELECT
Including Other Features
CREATE TABLE Using LIKE
Populating Derived Tables
Temporary Tables
Altering Derived Tables

Multi-Table Queries
Addressing Multiple Tables
Column Name Conflicts
Resolving Column Name Conflicts
Table Name Conflicts
Resolving Table Name Conflicts

Subqueries
Subquery Basics
Shape of the Result
Guaranteeing a Single Row
Scalar Subqueries
Table Subqueries
Columnar Subqueries
Row Subqueries
EXISTS Subqueries pt. 1
EXISTS Subqueries pt. 2
Correlated vs Non-Correlated
Nesting to Multiple Levels
Building Queries in Layers
Addressing the Same Table Twice
When to Use Subqueries

Joins
The FROM Clause
Join Basics pt. 1
Join Basics pt. 2
Inner Join Syntax
Basic Inner Join Demos
Inner Join Aggregation
The ON Clause
The USING Option
Natural Joins
Outer Joins
Join Criteria Options
Multiple Table Joins
Self Joins
Problems Joins Cannot Solve

Views
What a View Is
Uses of Views
Basic Syntax of a View
View Column Names
Updatable Views
Insertable Views
View Consistency
View Viability
Altering or Dropping a View
View Metadata

Multiple Table DML Operations
Multiple Table INSERT
Multiple Table UPDATE
Multiple Table DELETE
Advantages and Limitations

Transactions and Locking
Transaction Basics
Transactions in MySQL
AUTOCOMMIT Mode
Transaction Commands
Savepoints
Isolation
Isolation Levels
Transaction Demos
Locking Basics
Locking in MySQL
Explicit Row Locking with InnoDB
Row Locking Demos
Explicit Table Locking
Table Locking Demos
Deadlock Resolution
Implicit Commits

Stored Routine Basics
Procedures and Functions
Why Use Stored Routines
Basic Create Function Syntax
Invoking a Function
Basic Create Procedure Syntax
Invoking a Procedure
Database Association
Stored Routine Metadata
Where They Are Stored
Dropping Stored Routines

Stored Routine Programming
Compound Statements
Compound Statement Demos
Procedure Parameter Types
IN Parameters
INOUT Parameters
OUT Parameters
Declaring Local Variables
Assigning Values to Variables
Variable Scope
Conditional Blocks
IF Block Demos
CASE Block Demos
Iterative Blocks
LOOP Block Demos
REPEAT Block Demos
WHILE Block Demos
The LEAVE Statement
The ITERATE Statement
More We Can DECLARE
Declaring Conditions
Declaring Error Handlers
Declaring Cursors
Limitations on Cursors

Triggers
Trigger Basics
Uses for Triggers
Trigger Syntax
Trigger Examples
Exploring Triggers
Trigger Metadata
Dropping Triggers
User Accounts for Triggers
Trigger Limitations

Events
Event Basics
Event Execution
Event Syntax
Schedule Types
User Accounts for Events
Event Metadata
Event Limitations

Indexes
Index Basics
Defining Indexes for a Table
Adding Indexes to a Table
Dropping Indexes from a Table
Compound Indexes
Prefix Length Indexes
Covering Indexes
FULLTEXT Indexes pt. 1
FULLTEXT Indexes pt. 2

Efficiency Checks
Efficiency Checks
PROCEDURE ANALYSE function
EXPLAIN Command pt. 1
EXPLAIN Command pt. 2
EXPLAIN Command pt. 3

Conclusion
What We Covered
Where to Go from Here

Credits
About the Author