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) ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.