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.
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) 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 | +--------------+---------------+---------+
3.15.214.155