Database Reference
In-Depth Information
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 ascer-
tain 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.
 
 
Search WWH ::




Custom Search