Database Reference
In-Depth Information
Discussion
Suppose that you retrieve several values from the
mail
table, using expressions to cal‐
culate 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, 2014 | barb@saturn | tricia@mars | 58274 |
| May 12, 2014 | tricia@mars | gene@venus | 194925 |
| May 12, 2014 | phil@mars | phil@saturn | 1048 |
| May 12, 2014 | barb@saturn | tricia@venus | 271 |
…
If you must issue such a statement often, it's inconvenient to keep writing the expres‐
sions. To make the statement results easier to access, use a view, which is a virtual table
that contains no data. 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, 2014 | tricia@mars | 194925 |
| May 15, 2014 | gene@mars | 998532 |
| May 14, 2014 | tricia@saturn | 2394482 |
+--------------+---------------+---------+
Stored programs provide another way to encapsulate calculations (see
Recipe 9.2
).
