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.
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.