Difference between revisions of "An Introduction to MySQL Views"

Revision as of 20:46, 26 October 2007

MySQL views provide a way to gather data from multiple tables into a single virtual table. If you are familiar with the concept of Joins then you can think of a view as a pre-configured join (if you are not familiar with joins we recommend reading Joining Tables in MySQL before continuing with this chapter.

Joins and Views

In our chapter on MySQL joins 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_name, supplier_name, supplier_address FROM product, suppliers 
WHERE (product.supplier_id = suppliers.supplier_id);
+--------------------------+---------------+-------------------+
| 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)

Ordinarily we would need to repeat the above SELECT statement each time we wanted to perform the join and retrieve the data. 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 new is that we can do exactly this using views.

Creating a View

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

CREATE VIEW tablename AS select statement

For example, we can create a view called prodsupp which is a virtual table populated with the results of our join:

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

Now we have a view called prodsupp comprised of columns from both the product and supplier tables which we can treat as a 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)