O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

MySQL 5 Development (Part 2)

Video Description

This MySQL 5 Development Part 2 course continues the introduction to MySQL we began with Part 1. But in Part 2, we now begin working with commands that are used to change the contents of the tables in our database. We also explore various ways of building more complex queries, including command options that allow us to address more than one table in a single command. While this is still an introductory course, we are now exploring tools that let us handle more complex operations and build more sophistication into our MySQL database systems. The topics we cover in Part 2 include inserting/updating/deleting data, subqueries, joins, transactions, table and row locking, stored procedures and functions, triggers and events, indexes, and more. We have tried to keep the examples in the lessons as simple as possible to illustrate them most effectively, but the further study exercises are more realistic as to how these tools are used in practice. To begin learning today, simply click on the movie links.

Table of Contents

  1. Introduction
    1. Welcome 00:05:44
    2. What the Course Will Cover 00:04:47
    3. System Requirements 00:03:20
    4. Brief Review of Part 1 00:02:15
  2. Configuration
    1. Startup Options 00:06:02
    2. Configuration File 00:05:30
    3. Important Configuration Options 00:06:00
  3. Manipulating Data
    1. Data Manipulation Basics 00:03:16
    2. Warnings and Errors 00:07:39
    3. SQL Modes 00:06:40
    4. Changing Syntax Interpretation 00:06:56
    5. Elevating Warnings to Errors 00:07:02
    6. Interpreting Error Messages 00:05:45
    7. Note Warnings 00:04:24
    8. System Error Warnings 00:05:24
  4. Inserting Data
    1. Inserting Data 00:06:19
    2. Inserting Data Archaic Syntax 00:04:29
    3. INSERT Demonstrations 00:07:39
    4. Default Values on Insert 00:05:01
    5. AUTO_INCREMENT on Insert 00:05:13
    6. AUTO_INCREMENT Issues 00:06:06
    7. LAST_INSERT_ID Function 00:07:04
    8. Common INSERT Errors 00:04:47
  5. Deleting Data
    1. Deleting Data 00:06:08
    2. DELETE with WHERE 00:06:25
    3. DELETE with LIMIT 00:06:43
    4. DELETE with ORDER BY 00:04:01
    5. DELETE Result Messages 00:03:03
  6. Updating Data
    1. Updating Data 00:07:48
    2. UPDATE with WHERE 00:05:02
    3. UPDATE with LIMIT 00:07:09
    4. UPDATE with ORDER BY 00:03:36
    5. UPDATE Result Messages 00:05:25
  7. Other MySQL DML Commands
    1. REPLACE Command 00:05:02
    2. REPLACE Demo 00:05:54
    3. ON DUPLICATE KEY UPDATE Option 00:05:15
    4. ON DUPLICATE KEY UPDATE Demo 00:05:22
    5. TRUNCATE Command 00:03:48
    6. TRUNCATE Demonstration 00:05:56
    7. Safe Updates Mode 00:07:14
  8. Importing Data
    1. Importing Data 00:07:29
    2. Basic Import Demonstrations 00:06:35
    3. Importing Non-Tab Formats 00:06:58
    4. Skipping Header Lines on Import 00:04:13
    5. Changing Import Column Order 00:07:14
    6. Converting Data on Import pt. 1 00:04:35
    7. Converting Data on Import pt. 2 00:04:47
    8. Importing on the Shell Command Line 00:06:33
  9. Deriving New Tables
    1. New Tables from Existing Tables 00:06:40
    2. CREATE TABLE Using SELECT 00:06:09
    3. Including Other Features 00:06:41
    4. CREATE TABLE Using LIKE 00:04:12
    5. Populating Derived Tables 00:03:24
    6. Temporary Tables 00:06:48
    7. Altering Derived Tables 00:05:54
  10. Multi-Table Queries
    1. Addressing Multiple Tables 00:08:04
    2. Column Name Conflicts 00:04:35
    3. Resolving Column Name Conflicts 00:04:13
    4. Table Name Conflicts 00:05:03
    5. Resolving Table Name Conflicts 00:06:31
  11. Subqueries
    1. Subquery Basics 00:06:16
    2. Shape of the Result 00:07:49
    3. Guaranteeing a Single Row 00:05:25
    4. Scalar Subqueries 00:04:01
    5. Table Subqueries 00:04:14
    6. Columnar Subqueries 00:05:40
    7. Row Subqueries 00:03:40
    8. EXISTS Subqueries pt. 1 00:03:24
    9. EXISTS Subqueries pt. 2 00:03:51
    10. Correlated vs Non-Correlated 00:04:35
    11. Nesting to Multiple Levels 00:02:57
    12. Building Queries in Layers 00:05:03
    13. Addressing the Same Table Twice 00:05:18
    14. When to Use Subqueries 00:04:12
  12. Joins
    1. The FROM Clause 00:03:38
    2. Join Basics pt. 1 00:07:55
    3. Join Basics pt. 2 00:06:11
    4. Inner Join Syntax 00:07:03
    5. Basic Inner Join Demos 00:05:59
    6. Inner Join Aggregation 00:06:02
    7. The ON Clause 00:05:09
    8. The USING Option 00:05:51
    9. Natural Joins 00:03:51
    10. Outer Joins 00:04:51
    11. Join Criteria Options 00:04:12
    12. Multiple Table Joins 00:06:24
    13. Self Joins 00:07:02
    14. Problems Joins Cannot Solve 00:03:59
  13. Views
    1. What a View Is 00:04:13
    2. Uses of Views 00:04:52
    3. Basic Syntax of a View 00:06:05
    4. View Column Names 00:05:41
    5. Updatable Views 00:05:23
    6. Insertable Views 00:05:22
    7. View Consistency 00:03:19
    8. View Viability 00:03:07
    9. Altering or Dropping a View 00:05:08
    10. View Metadata 00:07:46
  14. Multiple Table DML Operations
    1. Multiple Table INSERT 00:06:52
    2. Multiple Table UPDATE 00:05:52
    3. Multiple Table DELETE 00:05:48
    4. Advantages and Limitations 00:06:40
  15. Transactions and Locking
    1. Transaction Basics 00:07:05
    2. Transactions in MySQL 00:04:23
    3. AUTOCOMMIT Mode 00:06:50
    4. Transaction Commands 00:07:28
    5. Savepoints 00:05:08
    6. Isolation 00:06:41
    7. Isolation Levels 00:08:06
    8. Transaction Demos 00:05:43
    9. Locking Basics 00:06:45
    10. Locking in MySQL 00:06:26
    11. Explicit Row Locking with InnoDB 00:07:20
    12. Row Locking Demos 00:07:12
    13. Explicit Table Locking 00:08:29
    14. Table Locking Demos 00:06:08
    15. Deadlock Resolution 00:04:51
    16. Implicit Commits 00:07:11
  16. Stored Routine Basics
    1. Procedures and Functions 00:05:53
    2. Why Use Stored Routines 00:07:27
    3. Basic Create Function Syntax 00:06:40
    4. Invoking a Function 00:04:23
    5. Basic Create Procedure Syntax 00:04:50
    6. Invoking a Procedure 00:04:09
    7. Database Association 00:05:02
    8. Stored Routine Metadata 00:07:47
    9. Where They Are Stored 00:06:07
    10. Dropping Stored Routines 00:05:28
  17. Stored Routine Programming
    1. Compound Statements 00:06:55
    2. Compound Statement Demos 00:05:09
    3. Procedure Parameter Types 00:06:08
    4. IN Parameters 00:04:47
    5. INOUT Parameters 00:04:29
    6. OUT Parameters 00:06:48
    7. Declaring Local Variables 00:04:38
    8. Assigning Values to Variables 00:05:45
    9. Variable Scope 00:07:05
    10. Conditional Blocks 00:06:38
    11. IF Block Demos 00:06:07
    12. CASE Block Demos 00:06:54
    13. Iterative Blocks 00:06:19
    14. LOOP Block Demos 00:05:36
    15. REPEAT Block Demos 00:07:15
    16. WHILE Block Demos 00:03:47
    17. The LEAVE Statement 00:06:41
    18. The ITERATE Statement 00:08:03
    19. More We Can DECLARE 00:07:48
    20. Declaring Conditions 00:02:45
    21. Declaring Error Handlers 00:06:08
    22. Declaring Cursors 00:07:20
    23. Limitations on Cursors 00:08:05
  18. Triggers
    1. Trigger Basics 00:05:31
    2. Uses for Triggers 00:06:15
    3. Trigger Syntax 00:06:01
    4. Trigger Examples 00:06:39
    5. Exploring Triggers 00:05:45
    6. Trigger Metadata 00:06:02
    7. Dropping Triggers 00:06:26
    8. User Accounts for Triggers 00:04:35
    9. Trigger Limitations 00:07:31
  19. Events
    1. Event Basics 00:07:01
    2. Event Execution 00:06:26
    3. Event Syntax 00:06:12
    4. Schedule Types 00:07:32
    5. User Accounts for Events 00:05:54
    6. Event Metadata 00:06:48
    7. Event Limitations 00:04:49
  20. Indexes
    1. Index Basics 00:06:36
    2. Defining Indexes for a Table 00:05:44
    3. Adding Indexes to a Table 00:05:59
    4. Dropping Indexes from a Table 00:03:35
    5. Compound Indexes 00:03:38
    6. Prefix Length Indexes 00:04:17
    7. Covering Indexes 00:04:03
    8. FULLTEXT Indexes pt. 1 00:06:15
    9. FULLTEXT Indexes pt. 2 00:05:01
  21. Efficiency Checks
    1. Efficiency Checks 00:04:42
    2. PROCEDURE ANALYSE function 00:06:52
    3. EXPLAIN Command pt. 1 00:06:14
    4. EXPLAIN Command pt. 2 00:04:37
    5. EXPLAIN Command pt. 3 00:05:08
  22. Conclusion
    1. What We Covered 00:03:54
    2. Where to Go from Here 00:03:14
  23. Credits
    1. About the Author 00:06:58