Understanding Views

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.

MariaDB> 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:

MariaDB> 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.

Views Are Not Tables

It is important to understand that the previous statement does not create a copy of the customer_contacts table using a snapshot of the data. Whenever a view is accessed, the underlying query is executed to generate the values in the pseudo-table.

Therefore, if new records are inserted into customer_contacts for this customer, they will also appear in custview.

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.

MariaDB> 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:

MariaDB> 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)

Column Aliases

Where you use a derived column in a view, you must give the column an alias; otherwise, you will not be able to select its values by name. The name assigned if you do not give an alias will be based on the expression used to calculate the column.

You must also use aliases if the SELECT statement in a view will return two columns with the same name. MariaDB returns an error if the column names in a view are not unique.

Go ahead and select values from this columnyou will see that the concatenated full name is returned for each value in the table.

MariaDB> SELECT full_name
 -> FROM customer_details;
+---------------------+
| full_name |
+---------------------+
| Lincoln, Abraham |
| Nixon, Richard |
... 
| Britten, Benjamin |
| Lennon, John |
+---------------------+
11 rows in set (0.00 sec)

View Naming

The name of a view must be unique across both views and tables. A view cannot have the same name as a table in the same database.

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:

MariaDB> GRANT CREATE VIEW ON mysql10.* TO chris@localhost;
Query OK, 0 rows affected (0.00 sec)

Using Views

If you have upgraded to MariaDB 5.0.1 or higher from an earlier version, you will need to upgrade the grant tables using the mysql_fix privilege_tables command to assign the CREATE VIEW privilege. Refer to the release notes with your version of MariaDB for more information.

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.

Updating Views

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. MariaDB 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:

The DISTINCT keyword
A GROUP BY clause
Any aggregate function, such as SUM()
A column derived from any function, expression, or subquery
A correlated subquery in the WHERE clause
The view's algorithm is TEMPTABLEsee the section "View Algorithms" later in this lesson for more information.

Cascaded Views

It follows that if a view selects data from another nonupdateable view. It, too, will be nonupdateable.

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 MariaDB server to reproduce that view. The following shows this for the customer_details view:

MariaDB> 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)

Show Create View Output

In some versions of MariaDB, the column headings in the output from SHOW CREATE VIEW were labeled Table and Create Table so don't be confused if you see this instead. The headings were changed to View and Create View in MariaDB 5.0.11.

Showing Views

Because views behave just like tables, the SHOW TABLES command will show all your views as well as your tables.

View Algorithms

The output from the previous example included an optional attribute in the CREATE VIEW statement: ALGORITHM=UNDEFINED. This attribute is a MariaDB-specific extension to the SQL language used to instruct MariaDB on how to process a view. When this is UNDEFINEDwhich will be the case unless you explicitly give an algorithmMariaDB 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:

MariaDB> 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:

MariaDB> DROP VIEW custview;
Query OK, 0 rows affected (0.00 sec)