MySQL SQL and Stored Procedures from Beginner to Advanced

MySQL SQL and Stored Procedures from Beginner to Advanced

What you’ll learn

Create, query and update databases
Use triggers, views and stored procedures
Understand transactions and database isolation levels.
Understand database users and security.

Requirements

You will need to download and install the free MySQL Server Community Edition and the free MySQL Workbench

Description

This course will take you from beginner in SQL and MySQL to an advanced level. Whether you’re a complete beginner with only basic computer knowledge, or a professional who already uses MySQL but wants to understand advanced features like transactions, user permissions, triggers and stored procedures, this course can help you.
Database skills are highly in demand in the I.T. industry; everything from websites to multiplayer games are likely to make use of some kind of database.
MySQL is a free, industrial-strength relational database, very widely used all over the world. In fact, the MySQL Community Server (which this course covers to an advanced level) is the world’s most popular open-source database, used by millions of websites.Master Advanced MySQL Features, Starting from the Ground Up
In this course you’ll begin by learning the basics of creating, querying and adding data to databases using SQL with MySQL. As the course progresses, we’ll move on to advanced features, vital for getting the most out of MySQL, including transactions, indexes, isolation levels, views, triggers and stored procedures.
You’ll learn how to create powerful and efficient databases using SQL, how to read and create EER diagrams, how to understand and implement table relationships like “many to many” and “one to many”, and even how to design databases visually using the free MySQL Workbench client tool.
By the end of the course you’ll understand MySQL’s powerful features to a greater level than probably most professional software developers, and you’ll have an understanding of SQL that will help you get to grips with any relational database. You’ll be able to create and query your own databases and use transactions, stored procedures, constraints, indices, views and other features to ensure your database is efficient and secure.

Overview

Section 1: Introduction

Lecture 1 Introducing MySQL

Section 2: Getting Started

Lecture 2 Installing MySQL and the Workbench

Lecture 3 Using the Command Line

Lecture 4 Creating Tables

Lecture 5 Multiple Columns, int and Data Types

Lecture 6 Null Values and Not Null

Lecture 7 Mysql Storage Engines and Configuration

Lecture 8 SQL Modes

Lecture 9 Deleting All the Data in a Table

Lecture 10 Primary Keys

Lecture 11 Auto Increment

Lecture 12 Narrowing Down Select Statements

Section 3: Importing and Exporting Data

Lecture 13 Exporting and Importing Data

Lecture 14 Exporting Using the Command Line

Lecture 15 Importing Using the Command Line

Section 4: MySQL Operators

Lecture 16 Some Test Data

Lecture 17 Comparison Operators

Lecture 18 Logical Operators

Lecture 19 018 Exclusive Or

Lecture 20 Exercises Part 1

Lecture 21 Solutions Part 1

Section 5: Update, Order and Limit

Lecture 22 Beginning Update Statements

Lecture 23 Order By

Lecture 24 Limiting Results

Section 6: MySQL Data Types

Lecture 25 Mysql Types

Lecture 26 Text Types

Lecture 27 Floating Point Types and Ints

Lecture 28 The Bit Type

Lecture 29 Bool

Lecture 30 Blobs

Lecture 31 Time, Date and Year

Lecture 32 Timestamp and Datetime

Lecture 33 Enumerations

Lecture 34 Brackets and Conditions Revisited

Lecture 35 Exercises 2

Lecture 36 Solutions 2

Section 7: Aggregate Functions and Grouping

Lecture 37 The distinct keyword

Lecture 38 Counting Distinct Values

Lecture 39 Aggregate Functions

Lecture 40 Arithmetic in MySQL

Lecture 41 The Health Survey Dataset

Lecture 42 Group By

Lecture 43 Having- Restricting Groups By Aggregate Functions

Lecture 44 Exercises 3

Lecture 45 Solutions 3

Section 8: Foreign Keys and Multiple Tables

Lecture 46 Naming Columns and Tables

Lecture 47 Foreign Keys

Lecture 48 ER Diagrams

Lecture 49 Joins and Cartesian Products

Lecture 50 Inner Joins

Lecture 51 Left and Right Outer Joins

Lecture 52 Joins on Multiple Tables

Lecture 53 Querying Chains of Tables

Lecture 54 One to Many and One to One

Lecture 55 Many to Many

Lecture 56 Joining Tables to Themselves

Lecture 57 Restrict Foreign Keys

Lecture 58 Cascade Foreign Keys

Lecture 59 Exercises 4

Lecture 60 Solutions 4

Section 9: Combining Queries

Lecture 61 Union and Union All

Lecture 62 Subqueries with “in”

Lecture 63 Inline Views

Section 10: Altering Schemas

Lecture 64 Adding Columns

Lecture 65 Adding Foreign Keys

Lecture 66 Adding Indexes

Lecture 67 Indexes on Multiple Columns

Section 11: Users and Privileges

Lecture 68 Creating Users

Lecture 69 Granting Privileges

Section 12: Views

Lecture 70 Creating a Simple View

Lecture 71 View Algorithms

Lecture 72 With Check Option

Section 13: Locks and Transactions

Lecture 73 Exclusive Table Locks

Lecture 74 Shared Table Locks

Lecture 75 Using Variables

Lecture 76 Setting Variables with Selects

Lecture 77 A Select-Update Example

Lecture 78 Fixing Select-Updates with Table Locks

Lecture 79 ACID

Lecture 80 A Simple Transaction

Lecture 81 Start Transaction

Lecture 82 InnoDB Row Locking and Isolation

Lecture 83 ACID Isolation Levels

Lecture 84 Serializable and Row vs Table Locking

Lecture 85 Demonstrating the Other Isolation Levels

Lecture 86 Rolling Back to Savepoints

Lecture 87 The Account Transfer Problem

Lecture 88 Select for Update

Lecture 89 Lock in Share Mode

Section 14: MySQL Functions

Lecture 90 String Functions

Lecture 91 Dates, Intervals and Queries

Lecture 92 Date Diff and Dayname

Lecture 93 Str_To_Date and Date_Format

Lecture 94 Control Flow Functions

Lecture 95 Casting

Lecture 96 Functions Exercises

Lecture 97 Functions Solution

Section 15: Stored Procedures

Lecture 98 Introducing Stored Procedures

Lecture 99 Changing the Delimiter

Lecture 100 Hello World

Lecture 101 Editing Stored Procedures 2

Lecture 102 Setting the Definer

Lecture 103 Procedure Permission

Lecture 104 Passing Parameters

Lecture 105 Multiple Parameters

Lecture 106 Select Into Variables

Lecture 107 Out Parameters

Lecture 108 Inout Parameters

Lecture 109 The If Statement

Lecture 110 Local Variables

Lecture 111 Implementing Account Withdrawal

Lecture 112 Transactional Withdrawal

Lecture 113 Errors and Warnings

Lecture 114 Handling Errors

Lecture 115 While Loops

Lecture 116 Labelled Loops

Lecture 117 Generating Random Data

Lecture 118 A Data Generating Procedure

Lecture 119 Cursors

Lecture 120 Fetching Cursor Data in a Loop

Lecture 121 The Case Expression

Lecture 122 Variable Scope and Multiple Cursors

Lecture 123 Exercises 6

Lecture 124 Solutions 6

Section 16: Triggers

Lecture 125 Introducing Triggers

Lecture 126 Validating with Triggers

Lecture 127 Triggers and Transactions

Lecture 128 Triggers Exercise

Lecture 129 Triggers Solution

Section 17: User-Defined Functions

Lecture 130 Functions vs Procedures

Lecture 131 User-Defined Functions

Section 18: Visual Design

Lecture 132 Designing Databases Visually

Lecture 133 Connecting with Java

Lecture 134 Retrieving Data with Java

Section 19: Final Exercise

Lecture 135 Final Exercise

Lecture 136 Conclusion

This course teaches you MySQL from the very beginning.,No prior knowledge is required, other than basic knowledge of your computer.,By the end of the course you will be able to use MySQL to an advanced level.

Course Information:

Udemy | English | 18h 17m | 5.75 GB
Created by: John Purcell

You Can See More Courses in the IT & Software >> Greetings from CourseDown.com