What is view (database)?

From the user's point of view, a view (database) can view the data in a special angle. From database theory, a view (database) is a virtual table, which consists of a stored query accessible composed of the result set of a query. From inside of the database system, the view composes with the data of one or more tables. From external database system, the view is just like a table, and it can be operated as a table, such as query, insert, modify or delete and so on.

But unlike ordinary tables (base tables) in a relational database, a view does not form part of the physical schema: it is a dynamic, virtual table computed or collated from data in the database. Changing the data in a table will be shown in subsequent invocations of the view.

A view can also be considered as a stored query. For two reasons: one is security reason, view can hidden data, such as: social insurance funds table, it can be only display the name, address, but no social security and wages, etc. Another reason is easier understand and use the complex queries.

Views also have these advantages over tables:
Views can represent a subset of the data contained in a table;
Views can join and simplify multiple tables into a single virtual table;
Views can act as aggregated tables, where the database engine aggregates data (sum, average etc) and presents the calculated results as part of the data;
Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents;
Views can limit the degree of exposure of a table or tables to the outer world.

Just as rows in a base table lack any defined ordering, rows available through a view do not appear with any default sorting. A view is a relational table, and the relational model defines a table as a set of rows. Since sets are not ordered - by definition - the rows in a view are not ordered, either. Therefore, an ORDER BY clause in the view definition is meaningless. The SQL standard does not allow an ORDER BY clause in a subselect in a CREATE VIEW statement, just as it is not allowed in a CREATE TABLE statement. However, sorted data can be obtained from a view, in the same way as any other table - as part of a query statement.

SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Readers also visit these:

Related Questions: