MySQL Development Training (4 days)

MySQL Development Training Overview

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.

MySQL Development Training Course Goals

• Learn data retrieval using SELECT statement.
• Troubleshoot typical warnings and errors.
• Change or add data.
• Underdstand MySQL data validation.
• Delete data from tables.
• Generate aggregated query data using various criteria.
• Connect data from multiple table rows using various types of JOIN constructs.
• Use several different types of sub-queries.
• Extensive coverage of MySQL Functions and expressions.
• Use expressions in SQL statements for more functional and flexible retrieval.
• Learn to export and import data.
• Understand MySQL storage engines, transactions and features of the common engines.
• Create views to reuse SELECT statements.
• 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.

MySQL Development Training Course Prerequisites

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.

MySQL Development Training Course Outline

1. Basic Select Statements
   1. Basic SQL Syntax Rules
      1. Comments in SQL
      2. Whitespace and Semi-colons
      3. Case Sensitivity
   2. SELECT Basics
   3. Introduction to the sakila Database
   4. Basic Select Examples
      1. SELECTing All Columns in All Rows
   5. SELECTing Specific Columns
   6. The WHERE Clause and Basic Operator Symbols
      1. Checking for NULL
   7. WHERE and ORDER BY
   8. Using Aliases
   9. Selecting Distinct Records
   10. Sorting Records
   11. The LIMIT Clause
       1. Processing the First N or Last N Records
2. Using Expressions in MySQL Statements
   1. MySQL Expressions
      1. Basic Elements of MySQL Expressions
   2. Operators in MySQL
      1. Calculated Fields
   3. Concatenation
   4. Arithmetic Operators
   5. Operator Precedence
   6. Comparison Operators
   7. The WHERE Clause and Operator Words
      1. The BETWEEN Operator
      2. The IN Operator
      3. The LIKE Operator
      4. The REGEXP Operator
      5. Use of BINARY with Strings
      6. The NOT Operator
   8. Using CASE
   9. Logical Operators
      1. Order of Evaluation
      2. Summary
   10. Using MySQL Functions
   11. Functions to Compare Data
   12. Control Flow Functions
       1. Exercise :Using Control Flow Functions in a SELECT Statement
       2. Data Conversion Functions
   13. String Functions
       1. Numeric Functions
   14. Date/Time Functions
       1. Current Date/Time
       2. Date Addition/Subtraction Functions
       3. Date Extraction Methods
       4. Date Formatting Methods
3. Inserting, Updating and Deleting Records in MySQL
   1. Inserting Data in a MySQL Database
      1. <VALUES option> of INSERT
      2. Add Multiple Rows
      3. Maintaining Relationships
      4. <set option> Alternative of INSERT
   2. Using REPLACE Statement to Add Data
   3. INSERT with ON DUPLICATE KEY UPDATE Syntax
   4. Updating Data in MySQL
      1. ORDER BY and LIMIT Clauses in UPDATE
   5. Using DELETE Statement in MySQL
      1. Some Advanced MySQL modifiers to DELETE statement
      2. ORDER BY and LIMIT in DELETE Statement
   6. Using TRUNCATE Statement to Delete Data
   7. Joining Tables in an UPDATE Statement
      1. Updating Multiple Tables using Joins
   8. Joining Tables in a DELETE Statement
      1. <FROM JOIN DELETE> Alternative
      2. <using join delete> Clause
      3. Deleting from multiple tables
4. Data Validation in MySQL
   1. Data Validation
   2. MySQL SQL Modes
      1. Setting SQL mode
      2. Some important SQL Modes
   3. Strict SQL Mode
      1. Strict Mode in Default Values
   4. Special Combination Modes
   5. Data Validation for Temporal Types
      1. SHOW WARNINGS
5. Advanced Queries
   1. More on The SELECT Statement
   2. Using Variables in a SELECT Statement
   3. Creating Joins in Your SQL Statements
   4. Inner Joins and Cross Joins
      1. Multi-table Joins
   5. Outer Joins
      1. Left Joins
      2. Right Joins
      3. Full Outer Joins
      4. Creating Full Joins
      5. Creating Basic Joins
      6. Creating Straight Joins
      7. Creating Natural Joins
   6. Unions
      1. UNION ALL
      2. UNION Rules
   7. Creating Views
   8. Dropping Views
   9. Benefits of Views
   10. The SELECT Statement Options
   11. Subqueries
       1. SubQuery Variants
       2. Nesting JOINS
       3. SubSELECT Limitations
       4. SubSelects as Join
6. Aggregate Functions and Grouping
   1. Grouping Data using GROUP BY
   2. Aggregate Functions
      1. Using Conditions in Grouping
   3. GROUP_CONCAT: Aggregate Functions
   4. Multiple Column GROUPING
   5. GROUP BY WITH ROLLUP
   6. Filtering Aggregates using HAVING Clause
   7. Miscellaneous Grouping Concepts and General Constraints
      1. Finding Top-N or Bottom-N Entities
      2. Order of Clauses
      3. Grouping Rules
7. Programming with MySQL
   1. Stored procedures
      1. Why Stored Procedures?
      2. SP Implementation
   2. Creating Routines
   3. Specification Options
   4. Deleting Routines
   5. Changing Routines
   6. Listing Existing Routines
   7. Determining the Code of an SP
   8. Programming Routines - Inside the Routines
      1. Encapsulation of Commands (BEGIN-END)
   9. Using Variables
      1. Variable Assignment
   10. General Programming Syntax Rules
   11. Invoking Procedures
   12. Parameters to Procedures
   13. Function Parameters
   14. Results of Procedures (SELECT)
   15. Function Results
   16. Invoking Functions
   17. IF-THEN-ELSE Branching
   18. Branching Using CASE
   19. REPEAT-UNTIL Loop
   20. LOOP
       1. LEAVE and ITERATE
       2. Security
   21. Using MySQL Query Browser
   22. Error Handling via Handlers
       1. Named Conditions
   23. Triggers
       1. Creating a Trigger
8. Managing Transactions in MySQL
   1. Managing Transactions
   2. Introducing Transactions
   3. Working Without Transactions
   4. Transactional Databases
   5. Performing a Basic Transaction
      1. The START TRANSACTION Statement
      2. The COMMIT Statement
      3. The ROLLBACK Statement
   6. Adding Savepoints to Your Transaction
   7. The SAVEPOINT Statement
      1. The ROLLBACK TO SAVEPOINT Statement
   8. Data Problems in a Transaction
      1. Dirty Reads
      2. Nonrepeatable Reads
      3. Phantom Reads
   9. Transaction Isolation Levels
      1. Setting the Isolation Level
   10. Locking Nontransactional Tables
       1. The LOCK TABLES Statement
       2. The UNLOCK TABLES Statement
   11. Setting the Autocommit Mode
   12. Statements that Automatically Commit Transactions
   13. SELECT ... LOCK IN SHARE MODE
   14. Locking via SELECT ... FOR UPDATE
9. Exporting and Importing Data in MySQL
   1. EXPORTING: Data Out of a Table
   2. Use Field and Line Separators
   3. Dump a Row to a File
   4. CTAS: Creating a New Table Using a ResultSet
   5. Copying Data into an Existing Table
   6. Using the LOAD DATA to Import Data
      1. General Loading Rules
      2. More Loading Examples
   7. Using the mysqlimport Utility to Import Data
      1. Using Separators with mysqlimport Utility

MySQL Development Training Course Materials

In addition to a comprehensive set of materials, including course notes and all the programming examples, each student will also receive a one-year subscription to Webucator's online reference library, which contains hundreds of the most current electronic technology books - a $149.95 per student value.

MySQL Development Training Technical Requirements

1. MySQL 5.0
   • Download
2. MySQL GUI Tools
   • Download

Class Files for MySQL Development Training

1. Download the class files.
2. After downloading the files, click here for further instructions.