The Create View statement creates a new view. A view is analogous to a named query. The view's query can refer to any combination of remote and local tables as well as other views. Views are read-only; they cannot be updated.
Syntax
CREATE VIEW view_name[(view_column,...)] AS
SELECT ... FROM ... [WHERE Expression]
[ORDER BY order_expression [, ...]]
[LIMIT limit [OFFSET offset]];
where:
view_name
specifies the name of the view.
view_column
specifies the column associated with the view. Multiple column names must be separated by commas.
The other commands used for Create View are the same as those used for Select (see "Select").
Notes
A view can be thought of as a virtual table. A Select statement is stored in the database; however, the data accessible through a view is not stored in the database. The result set of the Select statement forms the virtual table returned by the view. You can use this virtual table by referring to the view name in SQL statements the same way you refer to a table. A view is used to perform any or all of these functions:
Restrict a user to specific rows in a table.
Restrict a user to specific columns.
Join columns from multiple tables so that they function like a single table.
Aggregate information instead of supplying details. For example, the sum of a column, or the maximum or minimum value from a column can be presented.
Views are created by defining the Select statement that retrieves the data to be presented by the view.
The Select statement in a View definition must return columns with distinct names. If the names of two columns in the Select statement are the same, use a column alias to distinguish between them. Alternatively, you can define a list of new columns for a view.
Example A
This example creates a view named myOpportunities that selects data from three database tables to present a virtual table of data.
CREATE VIEW myOpportunities AS
SELECT a.name AS AccountName,
o.name AS OpportunityName,
o.amount AS Amount,
o.description AS Description
FROM Opportunity o INNER JOIN Account a
ON o.AccountId = a.id
INNER JOIN User u
ON o.OwnerId = u.id
WHERE u.name = 'MyName'
AND o.isClosed = 'false'
ORDER BY Amount desc
You can then refer to the myOpportunities view in statements just as you would refer to a table. For example:
SELECT * FROM myOpportunities;
Example B
The myOpportunities view contains a detailed description for each opportunity, which may not be needed when only a summary is required. A view can be built that selects only specific myOpportunities columns as shown in the following example:
CREATE VIEW myOpps_NoDesc as
SELECT AccountName,
OpportunityName,
Amount
FROM myOpportunities
The view selects the name column from both the opportunity and account tables. These columns are assigned the alias OpportunityName and AccountName, respectively.
A view can be thought of as a virtual table. A Select statement is stored in the database; however, the data accessible through a view is not stored in the database. The result set of the Select statement forms the virtual table returned by the view. You can use this virtual table by referring to the view name in SQL statements the same way you refer to a table. A view is used to perform any or all of these functions: