Using Views

So now that you know what views are (and the rules and restrictions that govern them), let's look at view creation:

• Views are created using the CREATE VIEW statement.

• To view the statement used to create a view, use SHOW CREATE VIEW viewname;.

• To remove a view, the DROP statement is used. The syntax is simply DROP VIEW viewname;.

• To update a view you may use the DROP statement and then the CREATE statement again, or just use CREATE OR REPLACE VIEW, which will create it if it does not exist and replace it if it does.

Using Views to Simplify Complex Joins

One of the most common uses of views is to hide complex SQL, and this often involves joins. Look at the following statement:

• Input

CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
  AND orderitems.order_num = orders.order_num;

• Analysis

This statement creates a view named productcustomers, which joins three tables to return a list of all customers who have ordered any product. If you were to SELECT * FROM productcustomers, you'd list every customer who ordered anything.

To retrieve a list of customers who ordered product TNT2, you can do the following:

• Input

SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

• Output

+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

• Analysis

This statement retrieves specific data from the view by issuing a WHERE clause. When MySQL processes the request, it adds the specified WHERE clause to any existing WHERE clauses in the view query so the data is filtered correctly.

As you can see, views can greatly simplify the use of complex SQL statements. Using views, you can write the underlying SQL once and then reuse it as needed.

Using Views to Reformat Retrieved Data

As mentioned previously, another common use of views is for reformatting retrieved data. The following SELECT statement (from Chapter 10, "Creating Calculated Fields") returns vendor name and location in a single combined calculated column:

• Input

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
       AS vend_title
FROM vendors
ORDER BY vend_name;

• Output

+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+

Now suppose that you regularly needed results in this format. Rather than perform the concatenation each time it was needed, you could create a view and use that instead. To turn this statement into a view, you can do the following:

• Input

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
       AS vend_title
FROM vendors
ORDER BY vend_name;

• Analysis

This statement creates a view using the exact same query as the previous SELECT statement. To retrieve the data to create all mailing labels, simply do the following:

• Input

SELECT *
FROM vendorlocations;

• Output

+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+

Using Views to Filter Unwanted Data

Views are also useful for applying common WHERE clauses. For example, you might want to define a customeremaillist view so it filters out customers without email addresses. To do this, you can use the following statement:

• Input

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

• Analysis

Obviously, when sending email to a mailing list you'd want to ignore users who have no email address. The WHERE clause here filters out those rows that have NULL values in the cust_email columns so they'll not be retrieved.

View customeremaillist can now be used for data retrieval just like any table.

• Input

SELECT *
FROM customeremaillist;

• Output

+---------+----------------+---------------------+
| cust_id | cust_name      | cust_email          |
+---------+----------------+---------------------+
|   10001 | Coyote Inc.    | ylee@coyote.com     |
|   10003 | Wascals        | rabbit@wascally.com |
|   10004 | Yosemite Place | sam@yosemite.com    |
+---------+----------------+---------------------+

Using Views with Calculated Fields

Views are exceptionally useful for simplifying the use of calculated fields. The following is a SELECT statement introduced in Chapter 10. It retrieves the order items for a specific order, calculating the expanded price for each item:

• Input

SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

• Output

+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 | 5.99       | 59.90          |
| ANV02   |        3 | 9.99       | 29.97          |
| TNT2    |        5 | 10.00      | 50.00          |
| FB      |        1 | 10.00      | 10.00          |
+---------+----------+------------+----------------+

To turn this into a view, do the following:

• Input

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems;

To retrieve the details for order 20005 (the previous output), do the following:

• Input

SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;

• Output

+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 | 5.99       | 59.90          |
| ANV02   |        3 | 9.99       | 29.97          |
| TNT2    |        5 | 10.00      | 50.00          |
| FB      |        1 | 10.00      | 10.00          |
+---------+----------+------------+----------------+

As you can see, views are easy to create and even easier to use. Used correctly, views can greatly simplify complex data manipulation.

Updating Views

All of the views thus far have been used with SELECT statements. But can view data be updated? The answer is that it depends.

As a rule, yes, views are updateable (that is, you can use INSERT, UPDATE, and DELETE on them). Updating a view updates the underlying table (the view, you will recall, has no data of its own); if you add or remove rows from a view you are actually removing them from the underlying table.

But not all views are updateable. Basically, if MySQL is unable to correctly ascertain the underlying data to be updated, updates (this includes inserts and deletes) are not allowed. In practice, this means that if any of the following are used you'll not be able to update the view:

• Grouping (using GROUP BY and HAVING)

• Joins

• Subqueries

• Unions

• Aggregate functions (Min(), Count(), Sum(), and so forth)

• DISTINCT

• Derived (calculated) columns

In other words, many of the examples used in this chapter would not be updateable. This might sound like a serious restriction, but in reality it isn't because views are primarily used for data retrieval anyway.