An Introduction to MySQL Views

MySQL views are essentially a way to package up SELECT statements into re-usable virtual tables whereby the data can be retrieved simply by referencing the view, rather than having to repeat the associated SELECT statement.

Views are most commonly used in conjunction with joins. In the previous chapter (Joining Tables in MySQL) we looked at using Joins to retrieve data from columns residing in multiple tables using a single SELECT statement. If you are not familiar with Joins we recommend you read the previous chapter before proceeding

The purpose of this chapter is to explore the concept of views in MySQL.

Views are created in MySQL using the CREATE VIEW statement. The syntax for creating a view is as follows:

CREATE VIEW tablename AS select statement

The most basic form of view involves data from a single table. We might, for example, have a standard SELECT statement used for formatting data from a table in a certain way. For example, the following select statement retrieves data with some additional formatting:

mysql> SELECT CONCAT(UPPER(supplier_name), ' ', supplier_address) FROM suppliers;
+-----------------------------------------------------+
| CONCAT(UPPER(supplier_name), ' ', supplier_address) |
+-----------------------------------------------------+
| MICROSOFT 1 Microsoft Way                           |
| APPLE, INC. 1 Infinate Loop                         |
| EASYTECH 100 Beltway Drive                          |
| WILDTECH 100 Hard Drive                             |
| HEWLETT PACKARD 100 Printer Expressway              |
+-----------------------------------------------------+
6 rows in set (0.00 sec)

If we wanted to repeat this selection we could simply enter the SELECT statement again. Now imagine that we could create a table that was based on the above select statement and simply refer to it by name like any other table without having to enter that lengthy SELECT statement. The good news is that we can do exactly this using views.

We could, for example, create a view called suppformat which is created using our previous SELECT statement:

CREATE VIEW suppformat AS 
SELECT CONCAT(UPPER(supplier_name), ' ', supplier_address) FROM suppliers;

We have now created a virtual table based on our SELECT statement which we can now simply reference in a SELECT statement:

mysql> SELECT * FROM suppformat;
+-----------------------------------------------------+
| CONCAT(UPPER(supplier_name), ' ', supplier_address) |
+-----------------------------------------------------+
| MICROSOFT 1 Microsoft Way                           |
| APPLE, INC. 1 Infinate Loop                         |
| EASYTECH 100 Beltway Drive                          |
| WILDTECH 100 Hard Drive                             |
| HEWLETT PACKARD 100 Printer Expressway              |
+-----------------------------------------------------+

This is a very basic use of views. Views become especially useful, however, when used in conjunction with joins.

In our chapter on Joining Tables in MySQL we created a join between two tables. In order to extract data from both tables we created a SELECT statement which joined the tables and matched the supplier_id in each table to find the matching rows. This allowed us to display products from the product table and the related supplier for each product from the suppliers table:

mysql> SELECT prod_code, prod_name, supplier_name, supplier_address FROM product, suppliers 
WHERE (product.supplier_id = suppliers.supplier_id);
+-----------+--------------------------+---------------+-------------------+
| prod_code | prod_name                | supplier_name | supplier_address  |
+-----------+--------------------------+---------------+-------------------+
|         4 | Microsoft 10-20 Keyboard | Microsoft     | 1 Microsoft Way   |
|         5 | Apple iPhone 8Gb         | Apple, Inc.   | 1 Infinate Loop   |
|         1 | CD-RW Model 4543         | EasyTech      | 100 Beltway Drive |
|         2 | EasyTech Mouse 7632      | EasyTech      | 100 Beltway Drive |
|         3 | WildTech 250Gb 1700      | WildTech      | 100 Hard Drive    |
+-----------+--------------------------+---------------+-------------------+
5 rows in set (0.00 sec)

As you can see from the above mysql output, we have retrieved data from both the suppliers table and the product table where the product supplier_id column matches the suppliers supplier_id column.

Ordinarily we would need to repeat the above SELECT statement each time we wanted to perform the join and retrieve the data. Instead, we can create a view called prodsupp that is a virtual table populated with the results of our previous join between the product and suppliers tables:

CREATE VIEW prodsupp AS SELECT prod_code, prod_name, supplier_name, supplier_address
FROM product, suppliers WHERE (product.supplier_id = suppliers.supplier_id);

This will create a view called prodsupp which consists of the table columns in our join. We can now, for example, treat the view as we would any regular table in a SELECT statement.

mysql> SELECT * FROM prodsupp;
+--------------------------+---------------+-------------------+
| prod_name                | supplier_name | supplier_address  |
+--------------------------+---------------+-------------------+
| Microsoft 10-20 Keyboard | Microsoft     | 1 Microsoft Way   |
| Apple iPhone 8Gb         | Apple, Inc.   | 1 Infinate Loop   |
| CD-RW Model 4543         | EasyTech      | 100 Beltway Drive |
| EasyTech Mouse 7632      | EasyTech      | 100 Beltway Drive |
| WildTech 250Gb 1700      | WildTech      | 100 Hard Drive    |
+--------------------------+---------------+-------------------+
5 rows in set (0.00 sec)

You will notice that this is identical the output from our original join SELECT statement.

We can also filter data based on our view just as we can with any table. For example to retrieve the data for a particular prod_code:

mysql> SELECT * FROM prodsupp WHERE prod_code=3;
+-----------+---------------------+---------------+------------------+
| prod_code | prod_name           | supplier_name | supplier_address |
+-----------+---------------------+---------------+------------------+
|         3 | WildTech 250Gb 1700 | WildTech      | 100 Hard Drive   |
+-----------+---------------------+---------------+------------------+
1 row in set (0.00 sec)

<google>ADSDAQBOX_FLOW</google> All views are the result of an underlying SELECT statement. Sometimes it can be useful to find out what the SELECT statement behind a view looks like. This information can be obtained using the following SQL syntax:

SHOW CREATE VIEW view name;

A pre-existing view may be deleted from a database using the following statement:

DROP VIEW view name

An existing view may be replaced with a new view using the same name via the CREATE OR REPLACE VIEW statement:

CREATE OR REPLACE VIEW view name AS select statement

<google>BUY_MYSQL_BOTTOM</google>