MySQL & SQL for Beginners: From Basics to Stored Procedures

Introducing MySQL

Source Code

Installing MySQL and the Workbench

Using the Command Line

Creating Tables

Multiple Columns_ int and Data Types

Null Values and Not Null

Mysql Storage Engines and Configuration

SQL Modes

Deleting All the Data in a Table

Primary Keys

Auto Increment

Narrowing Down Select Statements

Exporting and Importing Data

Exporting Using the Command Line

Importing Using the Command Line

Some Test Data

Comparison Operators

Logical Operators

Exclusive Or

Exercises Part 1

Solutions Part 1

Beginning Update Statements

Order By

Limiting Results

MySQL Types

Text Types

Floating Point Types and Integers

Bool

Blobs - Binary Data

The Bit Type

Time_ Date and Year

Timestamp and Datetime

Enumerations

Brackets and Conditions Revisited

Data Types Exercises

Data Types Solutions to Exercises

The Distinct Keyword

Counting Distinct Values

Aggregate Functions

Arithmetic in MySQL

The Health Survey Dataset

Group By

Having- Restricting Groups By Aggregate Functions

Exercise

Solutions

Naming Columns and Tables

Foreign Keys

ER Diagrams

Joins and Cartesian Products

Inner Joins

Left and Right Outer Joins

Joins on Multiple Tables

Querying Chains of Tables

One to Many and One to One

Many to Many

Joining Tables to Themselves

Restrict Foreign Keys

Cascade Foreign Keys

Exercises

Solutions

Union and Union All

Subqueries with in

Inline Views

Adding Columns

Adding Foreign Keys

Adding Indexes

Indexes on Multiple Columns

Creating Users

Granting Privileges

Creating a Simple View

View Algorithms

With Check Option

Exclusive Table Locks

Shared Table Locks

Using Variables

Setting Variables with Selects

A Select-Update Example

Fixing Select-Updates with Table Locks

ACID

A Simple Transaction

Start Transaction

InnoDB Row Locking and Isolation

ACID Isolation Levels

Serializable and Row vs Table Locking

Demonstrating the Other Isolation Levels

Rolling Back to Savepoints

The Account Transfer Problem

Select for Update

Lock in Share Mode

String Functions

Dates, Intervals and Queries

Date Diff and Dayname

Str_To_Date and Date_Format

Control Flow Functions

Casting

Functions Exercises

Functions Solution

Introducing Stored Procedures

Changing the Delimiter

Hello World

Editing Stored Procedures

Setting the Definer

Procedure Permissions

Passing Parameters

Multiple Parameters

Select Into Variables

Out Parameters

Inout Parameters

The If Statement

Local Variables

Implementing Account Withdrawal

Transactional Withdrawal

Errors and Warnings

Error Handlers

While Loops

Labelled Loops

Generating Random Data

A Data Generating Procedure

Cursors

Fetching Cursor Data in a Loop

The Case Expression

Variable Scope and Multiple Cursors

Procedures Exercises

Procedures Solution

Introducing Triggers

Triggers and Validation

Triggers and Transactions

Triggers Exercise

Triggers Exercise Solution

Functions vs Procedures

User-Defined Functions

Designing Databases Visually

Final Exercise

Conclusion

Connecting With Java

Retrieving Data with Java