CREATE VIEW
Creates
a new view based on existing tables or views.
SYNTAX
| view_name |
Name
of the new view you want to create. |
| column_name |
Name
of a column in the view. |
| select_statement |
Select
statement that specifies the contents of the view. |
DESCRIPTION
The CREATE
VIEW command creates a new view based on existing tables or views. To
execute the CREATE VIEW command, you must have RESOURCE security privileges
and either be the owner of the base table or view, or have the SELECT
privilege for that table or view.
A view is
a virtual table based on existing tables or views. Views appear to users
like a real table with named columns and rows of data, but unlike a real
table the view is not stored permanently in the database. The data visible
through a view is not physically stored in the database, but is instead
stored in the original tables from which the view is derived. Views are
stored in the database as a definition and a user-defined view name. The
view definition is an SQL query that DBMaker uses to access data from
the original tables whenever you use a view.
You can
use a view to tailor the appearance of a database to provide each user
with a personalized view of the database, and provide security and restricted
access to data by allowing users to see only the data they are authorized
to see. Views also isolate users from changes to the underlying structure
of the database; they present a consistent image of the database even
if the underlying tables are changed.
Views can
simplify the organization of a database by joining or grouping related
data from several different tables and presenting it as a single table
in the way that is most natural for the user. You can also use views to
provide a subset of rows stored in the base table by having a condition
on the return results.
However,
views there are two disadvantages to using views instead of a real table:
the performance and restrictions on updates. Performance is not as good
for queries on a view as for queries directly on the source tables. This
is because the database must first retrieve the view definition, build
it into the original query, perform the query, and then display the results.
There are also update restrictions imposed by using views, since the database
may not be able to manage updates on complicated views.
The SELECT
statement that defines the view cannot contain ORDER BY or INTO clauses,
and cannot use the UNION operator. Currently DBMaker can update a view
if that view is based on a single table.
You can
specify a list of column names for a view. The number of column names
that you specify must match the number of columns in the SELECT statement.
If you do not specify a list of column names, the view inherits the column
names from the underlying tables.
View names
and column names have a maximum length of eighteen characters, and may
contain numbers, letters, the underscore character, and the symbols $
and #. The first character may not be a number.
EXAMPLES
The following
example creates a view named View1 on the Employees table.
CREATE VIEW View1 AS SELECT Name, Salary from Employee WHERE Salary > 50000
|
RELATED COMMANDS
<
CREATE TRIGGER | Contents
| DELETE >
|
