MySQL View

  • A view is nothing more than a SQL statement that is store in the database with an associate the name.
  • A view is actually a composition of a table in the form of a predefine SQL query.
  • A view can contain all rows of a table or select rows from a table.
  • A view can create from one or many tables which depends on the written SQL query to create a view.
  • Views, which are kind of virtual tables, allow users to do the following:
    1. Structure data in a way that users or classes of users find natural or intuitive.
    2. Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.
    3. Summarize data from various tables which can allow to generate reports.

Advantages of views:

  • Views provide data security .
  • Different users can view same data from different perspective in different ways at the same time.
  • Views cal also be used to include extra/additional information.

Creating Views:

Database views are create using the CREATE VIEW statement. Views can create from a single table, multiple tables, or another view. To create a view, a user must have the appropriate system privilege according to the specific implementation.

The basic CREATE VIEW syntax is as follows:

CREATE VIEW view_name AS SELECT column1, column2….. FROM table_name WHERE [condition];

Ex:

CREATE VIEW CUSTOMERS_VIEW AS SELECT name, age FROM CUSTOMERS;

You can query CUSTOMERS_VIEW in similar way as you query an actual table. Following is the example:

SELECT * FROM CUSTOMERS_VIEW;

Updating a View:

A view can update under certain conditions:

  • The SELECT clause may not contain the keyword DISTINCT.
  • The SELECT clause may not contain summary functions.
  • The SELECT clause may not contain set functions.
  • The SELECT clause may not contain set operators.
  • The SELECT clause may not contain an ORDER BY clause.
  • The FROM clause may not contain multiple tables.
  • The WHERE clause may not contain sub queries.
  • The query may not contain GROUP BY or HAVING.

NOTE:

So if a view satisfies all the above mention rules then you can update a view. Following is an example to update the age of Ramesh:

Ex:

UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name=’Ramesh’;

Deleting Rows into a View:

Rows of data can delete from a view. The same rules that apply to the UPDATE and INSERT commands apply to the DELETE command.

Following is an example to delete a record having AGE= 22.delete from customers_view where age = 22;

Dropping Views:

Obviously, where you have a view, you need a way to drop the view if it is no longer needed.

The syntax is very simple as given below:

DROP VIEW view_name;

Following is an example to drop CUSTOMERS_VIEW from CUSTOMERS table:

DROP VIEW CUSTOMERS_VIEW;