- 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:
- Structure data in a way that users or classes of users find natural or intuitive.
- Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.
- 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.
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:
You can query CUSTOMERS_VIEW in similar way as you query an actual table. Following is the example:
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.
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:
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;
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:
Following is an example to drop CUSTOMERS_VIEW from CUSTOMERS table: