MySQL Cookbook, 2nd Edition

Using Views to Simplify Table Access

Problem

You often retrieve values that are calculated from expressions and you want a simpler way to refer to those values than writing the expressions each time you need them.

Solution

Use a view defined such that its columns perform the desired calculations.

Discussion

In Combining Columns to Construct Composite Values, we retrieved several values from the mail table, using expressions to calculate most of them:

mysql>SELECT
    -> DATE_FORMAT(t,'%M %e, %Y') AS date_sent,
    -> CONCAT(srcuser,'@',srchost) AS sender,
    -> CONCAT(dstuser,'@',dsthost) AS recipient,
    -> size FROM mail;
+--------------+---------------+---------------+---------+
| date_sent    | sender        | recipient     | size    |
+--------------+---------------+---------------+---------+
| May 11, 2006 | barb@saturn   | tricia@mars   |   58274 |
| May 12, 2006 | tricia@mars   | gene@venus    |  194925 |
| May 12, 2006 | phil@mars     | phil@saturn   |    1048 |
| May 13, 2006 | barb@saturn   | tricia@venus  |     271 |
...

One problem with such a statement is that if you have to issue it often, it’s inconvenient to write the expressions repeatedly. You can make the statement results easier to access by using a view. A view is a virtual table that does not contain any data itself. Instead, it’s defined as the SELECT statement that retrieves the data of interest. The following view, mail_view, is equivalent to the SELECT statement just shown:

mysql>CREATE VIEW mail_view AS
    -> SELECT
    -> DATE_FORMAT(t,'%M %e, %Y') AS date_sent,
    -> CONCAT(srcuser,'@',srchost) ...