MySQL Fundamentals Part 2

Part 2 of the Fundamentals of MySQL series, covering stored procedures, user-defined functions, triggers and more.
Course info
Rating
(277)
Level
Intermediate
Updated
Jul 9, 2013
Duration
2h 43m
Table of contents
Introduction
Joins, Unions and Subqueries
MySQL Functions
MySQL Views
Stored Procedures and Stored Functions
Triggers and Events
Summary and Resources
Description
Course info
Rating
(277)
Level
Intermediate
Updated
Jul 9, 2013
Duration
2h 43m
Description

MySQL is a popular choice of database for use in web applications, and is a central component of the widely used LAMP open source web application software stack. In this course, which is part 2 of the Fundamentals of MySQL series, we explore more advanced topics such as stored procedures & user-defined functions, subqueries & joins, views and events & triggers.

About the author
About the author

Pinal Dave is a Pluralsight Developer Evangelist.

More from the author
Getting Started with MariaDB
Beginner
2h 23m
Nov 2, 2018
Monitoring MySQL with Performance Schema
Intermediate
1h 59m
Apr 28, 2017
More courses by Pinal Dave
Section Introduction Transcripts
Section Introduction Transcripts

Joins, Unions and Subqueries
Hi, this is Pinal Dave for Pluralsight. And in this module, we will understand Joins, Unions and Sub Queries. We need Joins when we are going to retrieve data from two or more tables. We will understand three major kind of Joins. Inner Join, Outer Join, and Cross Join. Once we understand these three major kind of Joins, we'll continue to understand other types of Joins like Equal Join, Natural Join and Self Join. Right after that we'll understand, how Union operators work with MySQL. We'll also understand difference between Union and Union all. Finally, we will understand, how to write Sub Queries. And what are the important operatives for Sub Queries? One of the most important question, before we end this module, you will also understand when to use Sub Queries and when to use Joins. It is crucial for us to understand the difference between Joins and Sub Queries.

MySQL Functions
Hi. This is Pinal Dave for Pluralsight, and in this module we will discuss about MySQL functions. A function is a special type of command word in the SQL command set. In simple words, functions are one word commands that written a single value. The written value of the function can be determined by input parameters, pass into functions. However, there are few of the function does not take any input parameter, but there still return a single value as the result set. For example, function to return current day time. In this module, we'll discuss about: String Functions, Numeric Functions, Date and Time Functions, Control Flow Functions, Cast Functions, Information Functions, and few of the Miscellaneous Functions. Before we end the module, we will cover Aggregate Functions, which are commonly known as Column Functions. There is also concept of Stored Functions or User Defined Functions. We will cover that in depth in the later module of Stored Procedure and Stored Function Module.

MySQL Views
Hi. This is Pinal Dave for Pluralsight and in this module we will discuss about MySQL Views. Select statement can use joins, subqueries, functions, and other business logic. In the real world, we often retrieve data from more than one table. Presenting very complex select query. If you are to use the query again, we have two options. First, seal the script as a SQL file, and second, creating the view. SQL scripts are stored separate from the database, whereas views are stored as a part of the database. Any object which is part of the database can be used programmatically. Because of the same reason, views can be used programmatically. In this module, we will discuss what view is, and what are the different advantages of the same. We will later cover restriction on updatable view and finally we will discuss about how check option works with views.

Stored Procedures and Stored Functions
Hi, this is Pinal Dave. And in this module, we will discuss about stored procedures and stored functions. There are four different types of stored programs in MySQL. Stored procedure, it can be called from any application having access to database. Stored function, it can be called from any SQL statement, and is similar to MySQL function. Trigger, it response to insert, update, and delete statement on any particular table event, it executes as a scheduled time. Stored Procedures and Stored Functions are referred as stored routines. Stored routines store procedure logic in a central location. They are very powerful, and hold many advantages. In this module, we will learn about what stored procedures and stored functions are, as well understand various advantages of them in detail.

Triggers and Events
Hi, this is Pinal Dave, for Pluralsight. And in this module, we will discuss about Triggers and Events. Stored Programs. As we have discussed in earlier module, there're four different kind of Stored Programs. Stored Procedure, Stored Function, Trigger, and Event. In previous module, we discussed about Stored Procedure and Stored Function and in this module, we will discuss about Trigger and Event. Triggers. Triggers are executed before or after an Insert Update or Delete Statement on a particular table. Events can be executed at scheduled time and it automatically performs any task connected to database. Let's understand them a little bit in more detail.