MySQL Training: MySQL Development Training

MySQL Development Training Outline

• Basic Select Statements
• 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
• Using Expressions in MySQL Statements
• 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, Updating and Deleting Records in MySQL
• Inserting Data in a MySQL Database
• <VALUES option> of INSERT
• Add Multiple Rows
• Maintaining Relationships
• <set option> 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
• <FROM JOIN DELETE> Alternative
• <using join delete> Clause
• Deleting from multiple tables
• Data Validation in MySQL
• 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
• Advanced Queries
• 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
• Aggregate Functions and Grouping
• Grouping Data using GROUP BY
• Aggregate Functions
• Using Conditions in Grouping
• GROUP_CONCAT: Aggregate Functions
• Multiple Column GROUPING
• GROUP BY WITH RulLUP
• Filtering Aggregates using HAVING Clause
• Miscellaneous Grouping Concepts and General Constraints
• Finding Top-N or Bottom-N Entities
• Order of Clauses
• Grouping Rules
• Programming with MySQL
• 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 in MySQL
• 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 and Importing Data in MySQL
• 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
• Conclusion