Views in MySQL

In this part of the MySQL tutorial, we will mention views.

View definition

A view is a specific look on data from one or more tables. It can arrange data in some specific order, highlight or hide some data. A view consists of a stored query accessible as a virtual table composed of the result set of a query. Unlike ordinary tables a view does not form part of the physical schema. It is a dynamic, virtual table computed or collated from data in the database.

A view is a pseudo table. It is a stored query which looks like a table. And it can be referenced like a table.

Views can restrict users to specific rows or columns and thus enhance security. They can be used to join columns from multiple tables, so that they look like a single table. They can be used to provide aggregate information.

There are several restrictions that apply to views. Here are some of them:

• The SELECT statement cannot contain a subquery
• The SELECT statement cannot refer to system or user variables
• Any table or view referred to in the definition must exist
• A temporary VIEW cannot be created
• A VIEW cannot be associated with a trigger

Creating, modifying and dropping a View

In the next example, we create a simple view. We use CREATE VIEW syntax to create a view.

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+ 

This is our data, upon which we create the view.

mysql> CREATE VIEW CheapCars AS 
    -> SELECT Name FROM Cars WHERE Cost<25000;

We create a view CheapCars. These are cars which cost under 25000.

mysql> SELECT * FROM CheapCars;
+------------+
| Name       |
+------------+
| Skoda      |
| Citroen    |
| Volkswagen |
+------------+

A view is a database object than can be queried. There are three cars which are considered to be cheap.

mysql> ALTER VIEW CheapCars AS SELECT Name FROM Cars
    -> WHERE Cost<30000;

mysql> SELECT * FROM CheapCars;
+------------+
| Name       |
+------------+
| Skoda      |
| Volvo      |
| Citroen    |
| Volkswagen |
+------------+

We can redefine a view. Say we now consider a car to be cheap if it costs under 30000. We use the ALTER VIEW statement to modify our view.

What happens to a view if we delete a table, from which the data is selected?

mysql> DROP TABLE Cars;

mysql> SELECT * FROM CheapCars;
ERROR 1356 (HY000): View 'mydb.CheapCars' references invalid table(s) 
or column(s) or function(s) or definer/invoker of view lack rights to use them

Querying the view we receive the above error.

mysql> SOURCE cars.sql

mysql> SELECT * FROM CheapCars;
+------------+
| Name       |
+------------+
| Skoda      |
| Citroen    |
| Volkswagen |
+------------+

When we recreate the table the view works again.

mysql> DROP VIEW CheapCars;

Finally, a view is deleted with the DROP VIEW syntax.

Finding views

We will mention several ways how to find views in MySQL database.

mysql> SHOW FULL TABLES;
+----------------+------------+
| Tables_in_mydb | Table_type |
+----------------+------------+
| AA             | BASE TABLE |
...
| Chars          | BASE TABLE |
| CheapCars      | VIEW       |
| Customers      | BASE TABLE |
| Dates          | BASE TABLE |
| Decimals       | BASE TABLE |
| FavoriteCars   | VIEW       |
...

We can list all tables in a database with a SHOW FULL TABLES statement. In the Table_type column we can see, whether it is a table or a view.

mysql> SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES;
+---------------------------------------+-------------+
| TABLE_NAME                            | TABLE_TYPE  |
+---------------------------------------+-------------+
| CHARACTER_SETS                        | SYSTEM VIEW |
| COLLATIONS                            | SYSTEM VIEW |
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW |
| COLUMNS                               | SYSTEM VIEW |
| COLUMN_PRIVILEGES                     | SYSTEM VIEW |
| ENGINES                               | SYSTEM VIEW |
...
| Chars                                 | BASE TABLE  |
| CheapCars                             | VIEW        |
| Customers                             | BASE TABLE  |
| Dates                                 | BASE TABLE  |
| Decimals                              | BASE TABLE  |
| FavoriteCars                          | VIEW        |
...

In the information_schema database there is a TABLES table. The TABLE_NAME and TABLE_TYPE columns give us information about table names and their types.

mysql> SELECT TABLE_NAME FROM information_schema.VIEWS;
+--------------+
| TABLE_NAME   |
+--------------+
| CheapCars    |
| FavoriteCars |
+--------------+

This is the most straightforward way to find views. We query the VIEWS table of the information_schema database.

Creating a view with a UNION

The UNION operator is used to combine result-sets of two or more SELECT statements. Each select must have the same number of columns.

mysql> CREATE VIEW FavoriteCars AS
    -> SELECT * FROM Cars WHERE Id=7
    -> UNION SELECT * FROM Cars WHERE Id=4
    -> UNION SELECT * FROM Cars WHERE Id=5;

We create a view called FavoriteCars. In this view, we have three rows which are considered to be favourite. There are three SELECT statements combined with a UNION operator.

mysql> SELECT * FROM FavoriteCars;
+----+---------+--------+
| Id | Name    | Cost   |
+----+---------+--------+
|  7 | Hummer  |  41400 |
|  4 | Volvo   |  29000 |
|  5 | Bentley | 350000 |
+----+---------+--------+

This is a SELECT from the view.