Syntax--Creating, Altering, and Dropping Views - MySQL

As we’ve already mentioned, a view is based on a SELECT query, and this query usually makes up most of the view’s definition. To define a new view, we use the CREATE VIEW statement, whose standard syntax is shown here. While most databases offer extensions to it, nearly all of them that feature views support the basic form shown here. As you can see, it’s relatively straightforward and shouldn’t be too difficult for you to understand just by looking at it:

CREATE [OR REPLACE] VIEW view (columns) AS select-query
(WITH CHECK OPTION);

The optional OR REPLACE clause following the CREATE keyword is used to tell MySQL to re-create the view even if it already exists. Otherwise you’ll get an error if you try to create a view with the same name as an existing one.

In the preceding definition, view is the name of the view, which is covered by the same rules that govern other MySQL identifiers. This may be followed optionally by a list of columns--note that these are simply column names, as the types and sizes of the columns are already defined either in the table from which they’re being retrieved or in the SELECT statement. Next comes the AS keyword, which marks the beginning of the select-query used to obtain the data that will serve as the view’s contents.

The optional WITH CHECK OPTION clause following the query is defined in the SQL standard for use in creating a view that permits updates of its underlying table. (Recall that such a view may refer to only one table, as explained in the previous section.) In MySQL, this option doesn’t do anything; it is simply ignored by the command parser.

In addition to CREATE OR REPLACE VIEW , MySQL allows you to update an existing view with an ALTER VIEW command, although this isn’t part of the SQL standard. (Oracle and SQL Server also support this command.) MySQL’s ALTER VIEW replaces the SELECT query currently used in defining the view with a new query. The syntax is as shown here:

ALTER VIEW view (columns) AS
new-select-query
(WITH CHECK OPTION);

As is the case with CREATE VIEW , the WITH CHECK OPTION clause is ignored in an ALTER VIEW ; it is permitted for compatibility with other databases and the SQL standard but doesn’t actually do anything in MySQL.

In order to retrieve the query that was used to define a view, we use a new variation on the SHOW CREATE command, as shown here:

SHOW CREATE VIEW view;

Dropping a view is also very simple:

DROP VIEW view;

The DROP VIEW statement is supported by all major databases that implement views. view stands for the name of the view to be dropped.

These few statements are all that are required to work with views. We’ll provide some demonstrations showing how to use them in the next section.