MariaDB Crash Course - Ben Forta [68]
| 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 e-mail 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 e-mail to a mailing list you want to ignore users who have no e-mail address. The WHERE clause here filters out those rows that have NULL values in the cust_email columns so they are not 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 |
+---------+----------------+---------------------+
* * *
Note: WHERE Clauses and WHERE Clauses
If a WHERE clause is used when retrieving data from the view, the two sets of clauses (the one in the view and the one passed to it) are combined automatically.
* * *
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
+-----------+---------+----------+------------+----------------+
| order_num | prod_id | quantity | item_price | expanded_price |
+-----------+---------+----------+------------+----------------+
| 20005 | ANV01 | 10 | 5.99 | 59.90 |
| 20005 | ANV02 | 3 | 9.99 | 29.97 |
| 20005 | TNT2 | 5 | 10.00 | 50.00 |
| 20005 | 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 the views thus far have been used with SELECT statements. But can view data be updated? 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 MariaDB cannot 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