Quick introduction to MySQL views

Suppose we have table called names with two columns, first and last. A simple SELECT gives this, for example:

+-------+---------+
| first | last    |
+-------+---------+
| John  | Citizen |
| Jane  | Smith   |
+-------+---------+

Using CONCAT we can combine the first and last names together:

SELECT CONCAT(first,' ',last) FROM names;

which gives, for example:

+------------------------+
| CONCAT(first,' ',last) |
+------------------------+
| John Citizen           |
| Jane Smith             |
+------------------------+

If we frequently need to do such queries, we can create a view:

CREATE VIEW full_names AS SELECT CONCAT(first,' ',last) FROM names;

This creates a virtual table called full_names (it's called a virtual table because it's defined in terms of another table through a query). The view can which can be used in the following way:

SELECT * FROM full_names;

which gives:

+------------------------+
| CONCAT(first,' ',last) |
+------------------------+
| John Citizen           |
| Jane Smith             |
+------------------------+

We therefore no longer need to perform the SELECT statement using CONCAT.

To obtain information about a view:

SHOW CREATE VIEW view_name;

A view can be deleted in the following way:

DROP VIEW view_name;

We'll now consider a slightly more complicated example. Consider two tables, books:

+--------------------------------+--------------+------+
| title                          | author       | id   |
+--------------------------------+--------------+------+
| Elementary Mechanics           | Bob Smith    |    1 |
| Popular Science                | Reg Hammer   |    2 |
| Beginner's Guide to Mechanics  | R. G. Wesley |    3 |
| Advanced Statistical Mechanics | O. R. Ange   |    4 |
+--------------------------------+--------------+------+

and publishers:

+---------------+------+
| publisher     | id   |
+---------------+------+
| Smith & Smith |    1 |
| Davidson's    |    2 |
| Davidson's    |    3 |
| Smith & Smith |    4 |
+---------------+------+

We can use a join to obtain a list of book titles, authors and publishers:

SELECT title, author, publisher FROM books, publishers WHERE books.id = publishers.id;

which gives:

+--------------------------------+--------------+---------------+
| title                          | author       | publisher     |
+--------------------------------+--------------+---------------+
| Elementary Mechanics           | Bob Smith    | Smith & Smith |
| Popular Science                | Reg Hammer   | Davidson's    |
| Beginner's Guide to Mechanics  | R. G. Wesley | Davidson's    |
| Advanced Statistical Mechanics | O. R. Ange   | Smith & Smith |
+--------------------------------+--------------+---------------+

Instead of performing this join whenever we want to obtain this data, we could create a view:

CREATE VIEW bookshop AS SELECT title, author, publisher FROM books, publishers WHERE books.id = publishers.id;

Now we can just do this:

SELECT * FROM bookshop;

which gives the same set of results:

+--------------------------------+--------------+---------------+
| title                          | author       | publisher     |
+--------------------------------+--------------+---------------+
| Elementary Mechanics           | Bob Smith    | Smith & Smith |
| Popular Science                | Reg Hammer   | Davidson's    |
| Beginner's Guide to Mechanics  | R. G. Wesley | Davidson's    |
| Advanced Statistical Mechanics | O. R. Ange   | Smith & Smith |
+--------------------------------+--------------+---------------+

As expected, we can use a WHERE clause in the normal way:

SELECT * from bookshop WHERE publisher = "Davidson's";

which would give:

+-------------------------------+--------------+------------+
| title                         | author       | publisher  |
+-------------------------------+--------------+------------+
| Popular Science               | Reg Hammer   | Davidson's |
| Beginner's Guide to Mechanics | R. G. Wesley | Davidson's |
+-------------------------------+--------------+------------+

It's important to note that if the data in the original tables changes, the data in the view changes as well. Views can of course be much more complicated, and include calculations and aggregate functions.

It should be clear how useful views are. One of which is that users can perform simple SELECT queries on a view, rather than having to perform joins on multiple tables. A user can even be given permission only to have access to a view and not the underlying tables.