Views in MySQL Tutorial: Create, Join & Drop with Examples

Details

What are views?

VIEWS are virtual tables that do not store any data of their own but display data stored in other tables. In other words, VIEWS are nothing but SQL Queries. A view can contain all or a few rows from a table. A MySQL view can show data from one table or many tables.

Views syntax

Let's now look at the basic syntax used to create a view in MySQL. 

CREATE VIEW `view_name` AS SELECT statement;

WHERE

Let's now create our first view using the "myflixdb" we will create a simple view that restricts the columns seen in the members table.

Suppose authorization requirements  state that the accounts department can only see member's number , name and gender from the member's table. To achieve this you can create a VIEW - 

CREATE VIEW `accounts_v_members` AS SELECT `membership_number`,`full_names`,`gender` FROM `members`;

Executing the above script in MySQL workbench against the myflixdb and expanding the views node in the database explorer gives us the following results.

Views in MySQL

Note the accounts_v_members object is now visible in the database views objects. Let's now execute a SELECT statement that selects all the fields from the view. 

SELECT * FROM `accounts_v_members`;

Executing the above script in MySQL workbench against myflixdb gives us the following results shown below. 



membership_numberfull_namesgender

 
1Janet JonesFemale

 
2Janet Smith JonesFemale

 
3Robert PhilMale

 
4Gloria WilliamsFemale

 
5Leonard HofstadterMale

 
6Sheldon CooperMale

 
7Rajesh KoothrappaliMale

 
8Leslie WinkleMale

 
9Howard WolowitzMale

Only the authorized columns for accounts department have been returned. Other details found in the members table have been hidden .

If we want to see the SQL statements that make up a particular view, we can use the script shown below to do that. 

SHOW CREATE VIEW `accounts_v_members`;

Executing the above script gives you the view name and the SQL SELECT statements used to create the view.

Joins and views

Let's now look at a fairly complex example which involves multiple tables and uses joins.

We will package the JOIN created that gets information from three (3) tables namely members, movies and movie rentals. Below is the script that helps us to achieve that. 

CREATE VIEW `general_v_movie_rentals` AS SELECT mb.`membership_number`,mb.`full_names`,mo.`title`,mr.`transaction_date`,mr.`return_date` FROM `movierentals` AS mr INNER JOIN `members` AS mb ON mr.`membership_number` = mb.`membership_number` INNER JOIN `movies` AS mo ON mr.`movie_id` = mo.`movie_id`;

Executing the above scripts creates the view named general_v_movie_rentals in our myflixdb

Let's now select all the fields from a table named general_v_movie_rentals.

SELECT * FROM `general_v_movie_rentals`;

Executing the above script in MySQL workbench against the myflixdb gives us the following results shown below. 



membership_numberfull_namestitletransaction_datereturn_date

 
1Janet JonesPirates of the Caribean 420-06-201228-06-2012

 
1Janet JonesForgetting Sarah Marshal22-06-201225-06-2012

 
3Robert PhilForgetting Sarah Marshal22-06-201225-06-2012

 
2Janet Smith JonesForgetting Sarah Marshal21-06-201224-06-2012

 
3Robert PhilX-Men23-06-201228-06-2012

Note we didn't have to write the complex JOIN query to get information about members, movies and movie rental details. We simply used the view in a regular SELECT statement as any other ordinary table. The view can be called from anywhere in the application system running on top of the myflixdb.

Dropping views

The DROP command can be used to delete a view from the database that is no longer required. The basic syntax to drop a view is as follows. 

DROP VIEW ` general_v_movie_rentals `;

Why use views?

You may want to use views primarily for following 3 reasons

Summary

 

YOU MIGHT LIKE: