Views are the stored queries which produce results on being invoked. A view act as the virtual table or the prepared SQL statement. Available in MySQL Server 5.0, the view help in retrieving the data faster because you don’t have to perform joins and incorporate functions in your queries. Just create views for the data you require and see the action being performed by itself whenever the  insert, update and delete function is performed in the table. Once created, views update the data automatically.

Example: Here I am going to create one table where I will store the data related to video rating and store rating calculation in view. This is done to reduce the complications of the user,by using view the user need not to perform aggregate function on the table to calculate the total rating. If you perform aggregate function in mysql then it will take too much time to calculations To avoid these complicated calculations we use views.

Step 1:  Create a Table

In this table four fields are there. First one is “id” it’s a auto increment. Second one is rating (Out of 5). “Name” is related to user name. “Video” id related to particular video.

CREATE TABLE IF NOT EXISTS `video` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rating` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`video_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

This table is related to video rating. We have to store video name and video id, rating here.

Step 2: Now insert data in this table

INSERT INTO `video` (`id`, `rating`, `name`, `video_id`) VALUES
(1, 3, 'Phuspinder Kaur', 1),
(2, 2, 'Lali', 2),
(3, 5, 'Sahlu', 1),
(4, 5, 'Archna', 2),
(5, 4, 'Gurpreet Kaur', 1),
(6, 5, 'Gini', 3);

When ever you want to calculate total rating related to one video, you must need to apply query every time like this: SELECT video_id, AVG( rating ) AS rating_avg, COUNT( video_id ) AS video_count FROM video GROUP BY video_id. To avoid this calculation we will create views in db.

Step 3:  With the help of this query, We are able to create view

CREATE VIEW rating_average AS SELECT video_id, AVG(rating) as rating_avg,
COUNT(video_id) as video_count FROM video GROUP BY video_id

Now when ever you will perform action on the video table then there is no need to make changes in view. View will perform automatic functions of  update, add, edit and delete.

To calculate ratings there is no need to fetch query from the table, the data will be fetched from the view automatically. This process is extremely convenient and fast because calculation queries are performed in mysql engines.

Advantages:

1) The data complexity can be covered by creating views:

2) Data protection is important. If there is a table that contains sensitive data in certain columns, one can hide it from certain groups of users. Details such as customer names, addresses, and their social security number. An Employee who is working on a lower level, such as shipping clerks can only view the customer name and address. Permissions can be granted to them without allowing them to view the underlying tables.

The necessary information can be made secure in a couple of ways:

a) A view can be created that allows reading of only certain columns. This can be better explained with an example of the salary column. You might not want that the salaries of managers or other personnel should be visible to all. This can remain hidden by partitioning a table vertically where only names are visible. This is done by specifying only the appropriate columns in the Create View statement.

b) Create a view is done so that only certain rows are visible to all. For instance, one has the view of all the department manager. In this the managers have access to only their department and can provide raises to employees that are in their department. This is done by providing a WHERE clause in the SELECT statement that creates a view and is referred as horizontal partitioning. It is accomplished by specifying only the appropriate columns in the CREATE VIEW.

3) Adding in some simple business rules can also be done. For an instance if one wishes to generate a list of all the customers that need to receive the fall catalog, in this you can create a view of customers who have previously bought the products during the fall or the list of new ones.
4) All the data that is used exports with BCP. If you are a user who uses the BCP to export your SQL Server data into text files, the this can be formatted through the views. As the BCP’s formatting ability is quite limited.

5) Customizing of the data can also be done with ease. If one wishes to display computed values or column names that are formatted differently than the base table columns it can be done by creating views.

Disadvantages:

1) Tables dependency: one can also create views based on the underlying tables of the database. Any changes that are made in the structure of the tables that view associated, the view also has to be changed each time.