MySQL in a Nutshell, 2nd Edition

Name

CREATE VIEW

Synopsis

CREATE 
  [OR REPLACE] 
  [ALGORITHM = {MERGE|TEMPTABLE|UNDEFINED}] 
  [DEFINER = {'user'@'host'|CURRENT_USER}]
  [SQL SECURITY {DEFINER|INVOKER}]
VIEW view [(column,  . . . )] 
AS SELECT... 
[WITH [CASCADED|LOCAL] CHECK OPTION]

Use this statement to create a view, which is a preset query stored in a database. In certain situations, a view can be useful for improved security. Views are available as of version 5.0.2 of MySQL.

The contents of a view are based on the SELECT statement given in the AS clause. Users can subsequently issue queries and updates to the view in place of a table; updates ultimately change the data in the tables that underlie the views.

The name of the view cannot be the same as a table in the database, because they share the same tablespace. A view can be based on other views, rather than directly based on a table. To label the column headings for the view’s results set, column names may be given in a comma-separated list in parentheses after the view name. This SQL statement is available as of version 5.0.1 of MySQL.

A few parameters may appear between the CREATE and VIEW keywords. By default, attempts to create a view with the name of an existing view will fail, but the OR REPLACE parameter will overwrite a view with the same name if it exists and will create a new view otherwise. Also by default, the view’s definer (used to determine access rights to the columns of the view) is the user who creates it, but another user can be specified with the DEFINER ...