MySql View

How to use MySQL View

A View is a virtual table, which provides access to a subset of columns from one or more tables.It is a query stored as an object in the database which does not have own data. A view can derive its data from one or  more table called the base tables or underlying tables.

If we will check a scenario  we can get more clear idea about view. Like the database administrator wants to restrict access of data to different users. Users may access data according to their role and responsibility. In this situation view may be the solution for it.

Advantages of database view

  • View can be used to display table contents differently for different users, so that each user can access only the data pertaining to that user’s activities. This improves security by hiding information from the users that they should not be able to access or modify. It also reduce distraction because irrelevant columns are not displayed.
  • Views can assist with structure changes that need to be made to tables to accommodates certain applications. The view can preserve the appearance of the original tables structure to minimize disruption to other applications. If you split a table into two tables a view can be created with the name of the original table and defined to select data from the new tables such that view appears to have original tables.
  • A view can be used to perform a calculation and display its result A view defines that invokes aggregate functions can be used to display a summary.
  • A view can be used to select a restricted set of rows by means of an appropriate where clauses. Or to select only a subset of tables columns.
  • A view can be used for selecting data from multiple tables by using joins or union.

Disadvantages of database view

  • Performance: querying data from a database view can be slow especially if the view is created based on other views.
  • Tables dependency: you create a view based on underlying tables of the database. Whenever you change the structure of these tables that view associated with, you have to change the view as well.

Creating Views in MySQL

To create a new view in MySQL, you use the CREATE VIEW statement. The syntax of creating a view in MySQL is as follows:

Guidelines for creating Views

While creating views you should consider the following guidelines :

  • The name of the view must follow the rules for the identifiers and must not the the same as that on the table on which is based.
  • A view can be created only if there is a SELECT permission on its base table.
  • A view can not be derived its data from temporary table.
  • In a view ORDER BY cannot be used in the select statement.

 

To demonstrate a basic example of view, let’s start by creating a database that we can use for testing purposes.

https://downloads.mysql.com/docs/world-setup-en.a4.pdf

If we want to query country details with city name , you just need to execute a simple SELECT statement against the vwCountryCity  view as follows:

country_view

Restrictions at the Time of Modifying Data Through Views

 

View do not maintain a separate copy of the data, but only display the data present in the base tables.

  • You cannot modify data in a view if the modification affects more than one underlying tables. However you can modify data in a view if the modification affects only one tables at the time.
  • You cannot change column that is result of a calculation such as a computed column or an aggregate function.

WITH CHECK OPTION

If a view is updatable you use the WITH CHECK OPTION clause to place a constraint. This clause causes the condition in the where clause of the view definition to be checked when updates are attempted. In other word WITH CHECK OPTION ensure that you cannot update a row in such a way that the view no longer select it, and you cannot insert a row that the view will select .

The WITH CHECK OPTION clause in the view definition allows some modifications but disallow others.For example It’s possible to increase  the population values of any country in the view.

It also possible a population value can be decrease but only if it does not drop below the minimum value 100 million that is required by the views where clues.

Managing Views

Showing view definition

SHOW CREATE VIEW statement that displays the view’s definition.

Modifying views

In addition to create views you also need mo manage them. Management of view includes altering,dropping,or renaming a views.

Modifying views using ALTER VIEW statement

Removing views

Once a view created, you can remove it using the DROP VIEW statement. The following illustrates the syntax of the DROP VIEW statement:

For example, if you want to remove the vwCountryCity view, you can use the DROP VIEW statement as follows:

 

 

 

 

 

Rasmi Ranjan

A tech guy who loves to learn code. I'm open source enthusiast and a delicious coffee maker

Leave a Reply

Your email address will not be published. Required fields are marked *