In this article, we are going to learn about the fundamentals of MySQL Views. In this article, I am going to cover the following topics:

1. What are Database Views and the benefits of using them
2. Create, Update, and Delete a view using the queries and MySQL workbench wizard
3. Understand the concept of the MySQL Updatable views

The database views are the virtual tables that are generated by the query output. The views are considered as an object, and it can be queried using the SELECT statement. The View does not store the physical data on the database. When we run a SELECT statement on a database view, it executes the query and populates the data from the underlying tables used to create a view.

Benefits of using a MySQL view

1. The database view helps to simplify the complex business logic written in the SQL queries. Instead of executing the same complex query multiple times, you can create a view from it. This View can be referenced by using a simple SELECT query
2. The views add an extra layer of security. If you want your application not to access the base tables, you can create a view that refers to the Table you want to use. Suppose your application is using the customer table. You do not want to show the customer’s SSN details then you can create a view that populates the customer’s general information (Name, Address, contact details) grant access to the View only

MySQL View processing algorithm

The MySQL CREATE VIEW syntax has an optional clause ‘ALGORITHM.‘ This clause specifies how the views are going to be processed. The MySQL database views can be created using three algorithms.

1. MERGE algorithm
2. TEMP TABLE algorithm
3. UNDEFINED

MERGE Processing algorithm

To understand the MERGE processing algorithm, I have created a view named vw_customer using the customer table. The create view statement is the following:

Suppose to populate the data from the view; we are executing the following query:

When we create a MySQL view using the MERGE algorithm, first, it converts the view vw_customer to the customer table. Secondly, it converts the star (*) into the list of the columns(store_id, f_name, l_name). These column names correspond to the actual columns of the underlying tables (store_id, first_name, last_name), and lastly, it applies the WHERE clause.

The resulting query that executes on the database is the following:

• Note: When we use the MERGE algorithm in MySQL View, and if MySQL database engine is unable to process it, MySQL creates a view using ALGORITHM=UNDEFINED and generates a warning.

TEMPTABLE Processing algorithm

The TEMPTABLE algorithm is simple and easy to understand. When we create a database view using the TEMPTABLE algorithm, MySQL performs the following steps

1. It creates a temporary table to store the output generated by the SELECT statement that is used in the view definition
2. Executes the SELECT statement to insert the data into the View

UNDEFINED

When we create a view without specifying any algorithm type, MySQL uses the UNDEFINED algorithm. MySQL always chooses the MERGE algorithm over the TEMPTABLE because the performance of the views created with the MERGE algorithm is higher than the views created using the TEMPTABLE algorithm.

Create a database View

The syntax to create a database view is the following:

In the syntax:

1. The Name of the View is specified after the CREATE VIEW keyword. If you are creating a view with the specific processing algorithm, you must specify the ALGORITHM keyword between the CREATE and VIEW keyword
2. The ALGORITHM keyword is used to specify the processing algorithm of the database view
3. The select statement is followed by the AS keyword

Now, let us create a database view. Suppose you want to populate the list of the films whose actor is MATTHEW. To populate the list, the query should be written as follows:

Now, to create a view using the above query, the query should be written as follows:

The above query creates a database view named vw_moviesByMatthewJohanssan in the sakila database. You can use the SELECT statement to populate the result of the View. The following query is used to populate the data from the View.

You can also use the WHERE clause while querying a database view. The following query populates the film title with ‘R’ ratings from the vw_moviesByMatthewJohanssan.

As you can see in the above image, the list of movies with R ratings is populated from the view vw_moviesByMatthewJohanssan.

Alter a database View

We can change the definition of the MySQL view using CREATE OR REPLACE View statement. Suppose we want to add a rental duration column in a view, then the query should be written as follows:

As you can see in the above image, the new column RentalDuration has been added.

MySQL Updatable View

The MySQL views are Updatable, meaning you can execute UPDATE and DELETE queries on the database view. When we execute a DELETE and UPDATE query on the database view, the underlying tables are also updated. The queries that are used to create an updatable view must not have the following:

1. Any aggregate function, e.g., MIN, MAX, AVG, SUM, and COUNT
2. Subqueries in SELECT and WHERE clause
3. UNION or UNION ALL
4. Outer Join or Left Joins
5. HAVING and GROUP BY Clause

Moreover, the updatable View cannot refer to the non-updatable View, and you cannot update the View that has been created using the TEMPTABLE algorithm. The updatable views must be created using a table that has a primary key column.

First, let us create a simple database view named vw_actor using the actor table.

You can run the following query to verify that the view is updatable or not.

Run the below command to check to view the changes made in the record.

Let us delete the same record from the vw_actor. Run the following statement:

As you can see, the record has been deleted.

Manage views using MySQL Workbench

To create a view using MySQL workbench, we are creating a view named vw_films using the film table of the sakila database. First, Expand Sakila schema Right-click on Views Click on Create View.

A New View pan opens Enter the following query in the New View pane Click on Apply.

Screenshot:

A dialog box Apply SQL Script to Database opens. In the dialog box, you can review the query that is used to create a vw_films view. You can choose the view processing algorithm from the Algorithm drop-down box. You can choose the type of the lock from the Lock Type drop-down box the chosen lock will be placed on the tables that are used to create a view. Click on Apply.

The script will be executed successfully.

Screenshot:

Screenshot of the MySQL Workbench Navigator:

As you can see that a new database view has been created under the Views folder in MySQL workbench.

Drop a View

To drop the View, you can use the DROP VIEW statement. The syntax is the following:

To drop the vw_films View, execute the following query:

To drop the View using MySQL Workbench, expand Views Right-click on vw_films Click on the Delete View.

On the Drop View confirmation dialog box, click on Drop Now.

The View will be deleted.

Summary

In this article, we learned the basics of MySQL views. I have covered the following topics.

1. Database Views and the benefits of using them.
2. Create, Update, and Delete a view using the queries and MySQL workbench wizard.
3. Understand the concept of the MySQL Updatable views.

In the next article, we are going to learn about MySQL functions.

Table of contents

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022