Views and More in MySQL 5.0 (
Page 1 of 6 )
MySQL 5.0 supports a range of features that earlier versions of MySQL do not support. This article, the fourth in a series, explains views and other new features. It is excerpted from chapter eight of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress, ISBN: 1590593324).
Taking Derived Tables to the Next Level—Views
Unnamed views became possible in MySQL 4.1 with the inclusion of subselects that allow for the use of derived tables. Beginning with version 5.0.1, MySQL includes support for named views, usually referred to simply as “views.” Before we demonstrate how these are implemented in MySQL, let’s talk first a little bit about exactly what views are and how they will prove useful to MySQL database administrators and developers when it’s possible to deploy MySQL 5.0 in a production setting.
Views are also sometimes known as virtual tables, because they’re defined in terms of (other) tables through the use of queries. (We’ll see exactly how a view is defined in the upcoming “Syntax—Creating, Altering, and Dropping Views” section.) A view can be thought of as a “window” into a table, or perhaps a viewport that shows us a selected portion of a table at any given time, not unlike what’s shown in Figure 8-1.
However, a view is not merely a convenient container for a subset of records from a table. For one thing, a view is a “live” or dynamic snapshot of table data; when the data in the underlying table changes, so does that in the view. For another, we can construct views that aren’t merely subsets of table data; for example, we can perform calculations on the data, or obtain aggregate information for analysis purposes.
Figure 8-1. A view can be thought of as a "window" into a table.
Finally, in some cases, a view can be active as well as passive—in other words, when the view is updated, so is the data in the table upon which the view is based. It’s possible for this to happen so long as the SELECT statement used in defining the view meets the following conditions:
- Data is selected from one and only one table (in other words, you can’t perform an update on a view that was defined using a join).
- The statement is not a SELECT DISTINCT query.
- The statement does not contain any GROUP BY or HAVING clauses, and does not use any aggregate functions.
- No UNION operators are used in the query.
CAUTION A bug in MySQL 5.0.1 prevents the use of UNION queries in view definitions. According to bugs.mysql.com , this will be fixed in the next release.
There are some additional restrictions on how views may be used to update data in underlying tables. We’ll discuss some of these shortly, in the “Syntax— Creating, Altering, and Dropping Views” section, which follows the one you’re reading right now.
NOTE If the definition of the underlying table changes, the change may not carry over to views derived from that table. For example, if we add a new column to a table that serves as the basis for a view, the new column does not get added to the view. This is true not only of MySQL (at least in version 5.0.1), but also with many other data-bases, including Oracle.
Conversely, if we’re not concerned with using views to update data, then there are few restrictions on how they may be defined. You can use practically any legal SELECT query as the basis for a view, and you’re not confined to selecting data from a single table. Nor do you necessarily have to select data from any tables at all.
It’s also quite possible to select data from one view in defining another view; generally this isn’t a recommended practice, but it can be and is done with databases that support views, the most notable exception to this being PostgreSQL. (MySQL does support nested views, as we’ll see shortly.) The only major prohibition in the ANSI standard is that SELECT statements used to define views may not contain an ORDER BY clause, but, we’ll see, MySQL ignores this restriction.
CAUTION Don’t confuse the terms “virtual tables” and “derived tables.” Both are defined using queries (subqueries in the case of derived tables) and both depend upon database tables for their existence. However, a derived table has no name and is transitory; it exists only for the lifetime of a query, and there is no way to recall it once the query has been completed, save by rerunning the query. A virtual table (or view) is a named entity that, once created, persists until it’s dropped, and that can be accessed at any time following its creation.
/ 20 
