MySQL Development Training

This MySQL Training course teaches how to develop database applications on MySQL, using various Database Manipulation Language (DML) statements, MySQL Stored Procedures, Triggers, Views, and Information Schema. Students will be able to use what they learn in this class to create complex queries and reports, even aggregate results.

Experience in the following areas is required:

-Be able to create user defined variables, prepared statements and stored procedures
-Have experience with simple SQL queries
-Have experience with complex queries like joins and subqueries
-Be able to debug MySQL applications
-Have experience with INFORMATION_SCHEMA
-Some knowledge of database concepts.

Experience in the following areas would be beneficial.

-Some knowledge of database modeling.

Basic SQL Syntax Rules
- Comments in SQL
- Whitespace and Semi-colons
- Case Sensitivity
SELECT Basics
Introduction to the sakila Database
Basic Select Examples
- SELECTing All Columns in All Rows
SELECTing Specific Columns
The WHERE Clause and Basic Operator Symbols
- Checking for NULL
WHERE and ORDER BY
Using Aliases
Selecting Distinct Records
Sorting Records
The LIMIT Clause
- Processing the First N or Last N Records

MySQL Expressions
- Basic Elements of MySQL Expressions
Operators in MySQL
- Calculated Fields
Concatenation
Arithmetic Operators
Operator Precedence
Comparison Operators
The WHERE Clause and Operator Words
- The BETWEEN Operator
- The IN Operator
- The LIKE Operator
- The REGEXP Operator
- Use of BINARY with Strings
- The NOT Operator
Using CASE
Logical Operators
- Order of Evaluation
- Summary
Using MySQL Functions
Functions to Compare Data
Control Flow Functions
- Exercise :Using Control Flow Functions in a SELECT Statement
- Data Conversion Functions
String Functions
- Numeric Functions
Date/Time Functions
- Current Date/Time
- Date Addition/Subtraction Functions
- Date Extraction Methods
- Date Formatting Methods

Inserting Data in a MySQL Database
- of INSERT
- Add Multiple Rows
- Maintaining Relationships
- Alternative of INSERT
Using REPLACE Statement to Add Data
INSERT with ON DUPLICATE KEY UPDATE Syntax
Updating Data in MySQL
- ORDER BY and LIMIT Clauses in UPDATE
Using DELETE Statement in MySQL
- Some Advanced MySQL modifiers to DELETE statement
- ORDER BY and LIMIT in DELETE Statement
Using TRUNCATE Statement to Delete Data
Joining Tables in an UPDATE Statement
- Updating Multiple Tables using Joins
Joining Tables in a DELETE Statement
- Alternative
- Clause
- Deleting from multiple tables

Data Validation
MySQL SQL Modes
- Setting SQL mode
- Some important SQL Modes
Strict SQL Mode
- Strict Mode in Default Values
Special Combination Modes
Data Validation for Temporal Types
- SHOW WARNINGS

More on The SELECT Statement
Using Variables in a SELECT Statement
Creating Joins in Your SQL Statements
Inner Joins and Cross Joins
- Multi-table Joins
Outer Joins
- Left Joins
- Right Joins
- Full Outer Joins
- Creating Full Joins
- Creating Basic Joins
- Creating Straight Joins
- Creating Natural Joins
Unions
- UNION ALL
- UNION Rules
Creating Views
Dropping Views
Benefits of Views
The SELECT Statement Options
Subqueries
- SubQuery Variants
- Nesting JOINS
- SubSELECT Limitations
- SubSelects as Join

Grouping Data using GROUP BY
Aggregate Functions
- Using Conditions in Grouping
GROUP_CONCAT: Aggregate Functions
Multiple Column GROUPING
GROUP BY WITH ROLLUP
Filtering Aggregates using HAVING Clause
Miscellaneous Grouping Concepts and General Constraints
- Finding Top-N or Bottom-N Entities
- Order of Clauses
- Grouping Rules

Stored procedures
- Why Stored Procedures?
- SP Implementation
Creating Routines
Specification Options
Deleting Routines
Changing Routines
Listing Existing Routines
Determining the Code of an SP
Programming Routines - Inside the Routines
- Encapsulation of Commands (BEGIN-END)
Using Variables
- Variable Assignment
General Programming Syntax Rules
Invoking Procedures
Parameters to Procedures
Function Parameters
Results of Procedures (SELECT)
Function Results
Invoking Functions
IF-THEN-ELSE Branching
Branching Using CASE
REPEAT-UNTIL Loop
LOOP
- LEAVE and ITERATE
- Security
Using MySQL Query Browser
Error Handling via Handlers
- Named Conditions
Triggers
- Creating a Trigger

Managing Transactions
Introducing Transactions
Working Without Transactions
Transactional Databases
Performing a Basic Transaction
- The START TRANSACTION Statement
- The COMMIT Statement
- The ROLLBACK Statement
Adding Savepoints to Your Transaction
The SAVEPOINT Statement
- The ROLLBACK TO SAVEPOINT Statement
Data Problems in a Transaction
- Dirty Reads
- Nonrepeatable Reads
- Phantom Reads
Transaction Isolation Levels
- Setting the Isolation Level
Locking Nontransactional Tables
- The LOCK TABLES Statement
- The UNLOCK TABLES Statement
Setting the Autocommit Mode
Statements that Automatically Commit Transactions
SELECT ... LOCK IN SHARE MODE
Locking via SELECT ... FOR UPDATE

EXPORTING: Data Out of a Table
Use Field and Line Separators
Dump a Row to a File
CTAS: Creating a New Table Using a ResultSet
Copying Data into an Existing Table
Using the LOAD DATA to Import Data
- General Loading Rules
- More Loading Examples
Using the mysqlimport Utility to Import Data
- Using Separators with mysqlimport Utility