Database Reference
In-Depth Information
■
There is no limit to the number of views that can be created.
■
To create views, you must have security access. This is usually granted
by the database administrator.
■
Views can be nested; that is, a view may be built using a query that
retrieves data from another view.
■
ORDER BY
may be used in a view, but it will be overridden if
ORDER
BY
is also used in the
SELECT
that retrieves data from the view.
■
Views cannot be indexed, nor can they have triggers or default values
associated with them.
■
Views can be used in conjunction with tables, for example, to create a
SELECT
statement, which joins a table and a view.
So now that you know what views are (and the rules and restrictions that gov-
ern them), let's look at view creation:
■
Views are created using the
CREATE VIEW
statement.
■
To view the statement used to create a view, use
SHOW CREATE
VIEW viewname;
.
■
To remove a view, the
DROP
statement is used. The syntax is simply
DROP VIEW viewname;
.
■
To update a view you may use the
DROP
statement and then the
CREATE
statement again, or just use
CREATE OR REPLACE VIEW
,
which creates the view if it does not exist and replaces it if it does.
One of the
most common uses of views is to hide complex SQL, and this often
involves joins. Look at the following statement:
▼
Input
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
