By Bryan Young
Expert Author
Article Date: 2010-11-23
I recently needed to write a query that would take only certain rows from a table and run a query against those specific rows. I tried JOINs and sub-queries, but nothing quite worked. It was then that one of my more SQL-savvy coworkers introduced me to the idea of a VIEW.
A VIEW is a way to take a large table and distill it down to a subsection of the original data. You can then run queries against it like you would any other table. For instance, if you have a table full of purchases from a store, you can create a VIEW of all the purchases made in the last year.
mysql> CREATE VIEW purchases2009 AS SELECT * FROM purchases WHERE date BETWEEN UNIX_TIMESTAMP('2009-01-01') AND UNIX_TIMESTAMP('2010-12-31');
Now you can see how many purchases in 2009 were made with cash, or were over $100.
mysql> SELECT COUNT(*) FROM purchases2009 WHERE paymentmethod = 'cash';
mysql> SELECT COUNT(*) FROM purchases2009 WHERE total > 100;
It seems like this is a waste, when you could just query the original table and get the information you want, but do you really want to have to type out BETWEEN UNIX_TIMESTAMP... blah blah blah every time you need to know something about the previous year's purchases? I didn't think so. This provides a better way to keep your database organized.
Once a VIEW is created, it is dynamically linked to the table, so if you were to create a VIEW for 2010, you can continue to add purchases to the original table, and they will show up in the VIEW automatically. Not only this, but any values you insert into the VIEW will also be inserted into the parent table. VIEWs are also persistent. This means that they will not go away until you use the following command.
mysql> DROP VIEW purchases2009;
