A view in a database is a virtual table whose content is defined by a query. Like a true table, a view consists of a set of named columns and data rows. However, a view does not exist as a stored set of data values in a database. The rows and columns of data in a view come from tables referenced in the query that defines the view and are dynamically generated when the view is referenced. This topic describes the concepts, benefits, characteristics, and usage of views.
Benefits
Clarity: A view shows the specific data that a user is interested in and the specific tasks that a user is responsible for. This enhances data security by allowing the user to see only the data defined in the view, rather than all data in the table.
Simplified operations: A view greatly simplifies user operations on data. When a view is defined as the result set of a complex query, you do not need to rewrite these complex query statements repeatedly. You only need to execute a simple view query statement. In addition, visual views hide complex join operations between tables from users.
Data customization: Views enable different users to see different or identical data sets in different ways. This feature is critical when your database is used by many users at different levels.
Data merging and splitting: In some cases, due to the large amount of data in a table, the table is usually horizontally or vertically divided in design. However, changes in the table structure have a negative impact on the application. By using views, you can maintain the original structural relationship, so that the outer pattern remains unchanged, and the original application can still load the data by using the view.
Security: Views can be used as a security mechanism. By using views, users can only view and modify the data that they can see. They cannot view or access other tables or databases. If a user wants to access the result set of a view, they must be granted access to the view. The permission to access the table referenced by the view and the setting of the view permission are independent from each other.
Security of views
The security feature of views prevents unauthorized users from viewing specific rows or columns, so that users can see only specific rows in the table. You can perform the following steps to implement this feature:
Add a column that indicates the username to the table.
Create a view in which users can see only rows with their corresponding usernames.
Authorize the view to the users.
Logical data independence
Views allow applications to be independent of database tables. If views are not used, applications depend on real tables. After views are introduced, applications depend on views but not on real tables. In other words, views separate applications from database tables. Views can separate programs from data in the following ways:
If the application is built on a database table and the database table changes, you can create views based on the table to mask table changes, so that the application can remain unchanged.
If the application is built on a database table and the application changes, you can create views based on the table to mask the application changes, so that the database table can remain unchanged.
If the application is built on views and the database table changes, you can modify the views of the table to mask the table changes, so that the application can remain unchanged.
If the application is built on a views and the application changes, you can modify the views of the table to mask the application changes, so that the database can remain unchanged.
Views allow you to customize the presentation of data for different users. The following list describes the benefits of views:
You can define frequently queried data as views to simplify operations. Database queries may use aggregate functions, display information about additional fields, and reference other tables. As a result, the SQL statements can be rather complex. To avoid frequently executing these queries, you can create a view to simplify the query process. To obtain the desired query result after the view is created, execute the
SELECT * FROM view_namestatement.You can only query and modify data displayed in the view. This improves data security. A view is a data set that is automatically updated based on changes in the base tables. As a virtual table, a view does not physically exist and displays only the result set of a query. Therefore, you can choose not to display sensitive fields of the base tables to users.
A view is logically independent of the data structure of the base tables. Views allow applications to be independent of database tables. If views are not used, applications depend on real tables. After views are introduced, applications depend on views but not on real tables. In other words, views separate applications from database tables.
Characteristics
Unlike tables, views do not occupy storage space or store data. A view is defined by a query that extracts or derives data from the base tables referenced by the view. Therefore, a view requires no storage other than storage for the query that defines the view in the data dictionary.
A view depends on its referenced objects. If you drop, update, or re-create a base table of a view, the database determines whether the new base table is acceptable to the view definition.