A view is a pseudo-table. In SQL, you can reference a view just like you reference a table in a SELECT statement. You can also use some views with INSERT, UPDATE, and DELETE statements.
Views are a tool of convenience. For example, if you find yourself frequently performing the same join, you might find it more convenient to create a view to use in your queries instead.
Creating a View
You create a view with the CREATE VIEW command. The following example creates a simple viewevery column from the customer_contacts table is included, but a WHERE clause restricts the rows returned to only those with the given customer_code.
mysql> CREATE VIEW custview AS -> SELECT * FROM customer_contacts -> WHERE customer_code = 'SCICORP'; Query OK, 0 rows affected (0.04 sec)
The command executes silently if there is no error. An error in the SELECT statement of a CREATE VIEW command produces the same error you would get if you had executed the query directly.
After you have created a view, you can perform a SELECT statement using it, as follows:
mysql> SELECT first_name, last_name -> FROM custview -> ORDER BY last_name; +------------+-----------+ | first_name | last_name | +------------+-----------+ | Marie | Curie | | Charles | Darwin | | Albert | Einstein | | Benjamin | Franklin | +------------+-----------+ 4 rows in set (0.01 sec)
The output contains only the rows that were filtered in the CREATE VIEW statement. Other rows from the customer_contacts table cannot be accessed using this view.
The following example creates a view that includes a table join on the customers and customer_contacts table. This time, the CREATE VIEW statement also specifies a list of columns and includes an expression.
mysql> CREATE VIEW customer_details AS -> SELECT customers.customer_code, name, email, -> concat(last_name, ', ', first_name) AS full_name -> FROM customers, customer_contacts -> WHERE customers.customer_code = customer_contacts.customer_code; Query OK, 0 rows affected (0.00 sec)
An alias is required for the CONCAT() expression, to give that column a name in the view. Showing the columns in this view reveals that there is indeed a column named full_name:
mysql> DESCRIBE customer_details; +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | customer_code | varchar(10) | NO | | | | | name | varchar(40) | NO | | | | | email | text | YES | | NULL | | | full_name | varchar(62) | NO | | | | +---------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
Go ahead and select values from this columnyou will see that the concatenated full name is returned for each value in the table.
mysql> SELECT full_name -> FROM customer_details; +---------------------+ | full_name | +---------------------+ | Lincoln, Abraham | | Nixon, Richard | ... | Britten, Benjamin | | Lennon, John | +---------------------+ 11 rows in set (0.00 sec)
Privileges and Views
To create views, you must have the CREATE VIEW privilege. This is a separate privilege from CREATE, which enables you to create tables and indexes. If a user account was granted ALL PRIVILEGES, it includes CREATE VIEW.
To assign the CREATE VIEW privilege to a user, use a statement like the following:
mysql> GRANT CREATE VIEW ON mysql10.* TO chris@localhost; Query OK, 0 rows affected (0.00 sec)
You must also have the SELECT privilege on all the tables that are referenced in the SELECT statement of a CREATE VIEW statement. If you cannot query the tables yourself, you cannot create a new view that uses them.
You can, however, grant the SELECT privilege on a view you created to another user without giving that user the SELECT privilege on each table in the view. For instance, a system user could have access to the customer_details view without being able to query either customers or 190customer_contacts directly.
Some views are updateable, but you cannot always use a view in an UPDATE, INSERT, or DELETE statement. Several features of the underlying query that would cause a view to be nonupdateable.
If the view contains a join, you can usually perform a SELECT only on the view. MySQL cannot insert into two tables simultaneously, even with the relationship between the tables specified in the join condition.
You can insert into a view that uses a join only if it is an inner-join and also only if all the columns you insert into are from the same table.
You cannot insert into any view that uses the UNION or UNION ALL operator.
A view cannot be updated, even if it is based on only one table, if its query contains any of the following features:
Finding Information About Views
You can use the SHOW CREATE VIEW command to see the underlying query text for a view. The output might not be the query you entered verbatimfor example, a SELECT * will be expanded to select the individual fields, and several optional attributes of the CREATE VIEW command will be present. Any formatting you used when entering the CREATE VIEW statement will be lost.
The output of SHOW CREATE VIEW is designed to give you a single command that can be run on any MySQL server to reproduce that view. The following shows this for the customer_details view:
mysql> SHOW CREATE VIEW customer_details -> \G *************************** 1. row *************************** View: customer_details Create View: CREATE ALGORITHM=UNDEFINED DEFINER= `root`@`localhost` SQL SECURITY DEFINER VIEW `customer_details` AS select `customers`. `customer_code` AS `customer_code`,`customers`. `name` AS `name`,`customer_contacts`.`email` AS `email`,concat(`customer_contacts`.`last_name`, _latin1', ',`customer_contacts`.`first_name`) AS `concat(last_name, ', ', first_name)` from (`customers` join `customer_contacts`) where (`customers`.`customer_code` = `customer_contacts`.`customer_code`) 1 row in set (0.00 sec)
The output from the previous example included an optional attribute in the CREATE VIEW statement: ALGORITHM=UNDEFINED. This attribute is a MySQL-specific extension to the SQL language used to instruct MySQL on how to process a view. When this is UNDEFINEDwhich will be the case unless you explicitly give an algorithmMySQL chooses the algorithm.
The MERGE algorithm is usually the most efficient way for a view to be processed. The underlying SQL is merged into the SQL statement, and the query is executed in one go. Consider the following query using the custview view:
SELECT first_name, last_name FROM custview ORDER BY last_name;
If the custview view is executed using the MERGE algorithm, the query is actually executed as if you entered the following:
SELECT first_name, last_name FROM (SELECT * FROM customer_contacts WHERE customer_code = 'SCICORP') ORDER BY last_name
However, if custview uses the TEMPTABLE algorithm, the process would be as if you had executed the following steps:
CREATE TEMPORARY TABLE tempview AS SELECT * FROM customer_contacts WHERE customer_code = 'SCICORP'; SELCET first_name, last_name FROM tempview ORDER BY last_name; DROP TEMPORARY TABLE tempview;
The TEMPTABLE algorithm must be used if the query contains an aggregate function, a GROUP BY clause, the DISTINCT keyword, or a UNION.
Altering and Dropping Views
To change the definition of a view, use the ALTER VIEW command. The syntax is just the same as CREATE VIEW, but the existing view is replaced with the new definition. The following example changes the definition of custview to use a different customer_code in the filter:
mysql> ALTER VIEW custview AS -> SELECT * FROM customer_contacts -> WHERE customer_code = 'MUSGRP'; Query OK, 0 rows affected (0.00 sec)
To drop a view, simply use DROP VIEW as follows:
mysql> DROP VIEW custview; Query OK, 0 rows affected (0.00 sec)