Recipe 3.12. 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 Section 3.5, 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) AS sender,     -> CONCAT(dstuser,'@',dsthost) AS recipient,     -> size FROM mail;             

To access the view contents, refer to it like any other table. You can select some or all of its columns, add a WHERE clause to restrict which rows to retrieve, use ORDER BY to sort the rows, and so forth. For example:

mysql> SELECT date_sent, sender, size FROM mail_view     -> WHERE size > 100000 ORDER BY size; +--------------+---------------+---------+ | date_sent    | sender        | size    | +--------------+---------------+---------+ | May 12, 2006 | tricia@mars   |  194925 | | May 15, 2006 | gene@mars     |  998532 | | May 14, 2006 | tricia@saturn | 2394482 | +--------------+---------------+---------+ 




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net