CREATE VIEW

Use this MySQL 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.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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]

Explanation

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. See the online documentation for features that, if used in a view, prevent it from being updatable.

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 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 a name of an existing view fails, but the OR REPLACE parameter overwrites a view with the same name if it exists and creates 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 that creates it, but another user can be specified with the DEFINER clause. This clause is available as of version 5.1.2 of MySQL. This same version introduced the related SQL SECURITY clause. It instructs MySQL to authorize access to the view based on the privileges of either the user account of the view's creator (DEFINER, the default) or the user account of the user who is querying the view (INVOKER). This can help to prevent some users from accessing restricted views.

The ALGORITHM parameter selects one of the two types of algorithmic method to use for processing a view: MERGE or TEMPTABLE. TEMPTABLE prevents a view from being updatable. The default of UNDEFINED leaves the choice to MySQL.

The WITH CHECK OPTION clause restricts updates to rows in which the WHERE clause of the underlying SELECT statement returns true. For a view that is based on another view, if you include the LOCAL keyword, this restriction will be limited to the view in which it's given and not the underlying view. Conversely, if you use the default choice of CASCADED, the WHERE clauses of underlying views will be considered as well.

If the mysqld server is started with the --updatable_views_with_limit option, updates that contain a LIMIT clause can update views only if the views contain all of the columns that are part of the primary keys of the underlying tables. If set to the default value of 1, a warning only is returned and updates are not restricted.

Examples

Here is an example of how you can use this MySQL statement:

CREATE DEFINER = 'cjcollier'@'localhost'
SQL SECURITY INVOKER
VIEW student_directory(ID, Name, Telephone) 
AS SELECT student_id, 
CONCAT(name_first, SPACE(1), name_last), phone_home
FROM students;

This MySQL statement creates a view that contains each student's identification number, the student's first and last name concatenated together with a space in between, and the student's home telephone. To retrieve this data, enter the following SQL statement:

SELECT * FROM student_directory 
WHERE Name LIKE '%Tears';

+-----------+-------------------+-----------+
| ID        | Name              | Telephone |
+-----------+-------------------+-----------+
| 433342000 | Christina Tears   | 4883831   |  
+-----------+-------------------+-----------+

To save space in the output, the query included a WHERE clause to retrieve a student with the last name of Tears. Notice that the column names are the ones named by the MySQL statement, CREATE VIEW, not the underlying tables on which the view is based. This view will be available for all users who have SELECT privileges for the database in which it was created.

By default, a view is created in the default database at the time that the CREATE VIEW statement is entered. To create a view in a different database, simply add the database name and a dot as a separator in front of the view name in the CREATE VIEW statement.

To delete a view from a database, use the DROP VIEW statement. To see a list of existing views for the current database, run SHOW FULL TABLES WHERE Table_type='VIEW';