5,821
Views are like snapshots of queries. You can save any query as a view, even complex queries with joins and subqueries, and MySQL will save the results of that query in a format that can be searched (and in some cases, modified) just like a table. But unlike tables, you never have to worry about updating the data in your view; MySQL handles all of the details for you!
For the purposes of an example, let's pretend you built a database for Wally's Widget Works. The good folks at Wally's decided that they needed to keep track of the different kinds of widget that they produce. To make it easier to sort the different widgets, they have decided to give each widget one or more 'tags' that describes some aspect of that widget's function or purpose.
So you start out with a database that looks something like this:
- Data_Widgets (`widgetid` serial, `desc` varchar)
- Data_Tags (`tagid` serial, `desc` varchar)
- Map_WidgetTag (`widgetid` bigint, `tagid` bigint)
Since each Widget can have more than one tag, and each tag could be associated with more than one Widget, we have a database design professor's favorite topic of lecture: a many-to-many relationship.
Let's suppose we wanted to find all the Widgets that matched the tag, "lifesupport".
Expand|Select|Wrap|Line Numbers
- SELECT `Data_Widgets`.* FROM (`Data_Tags` LEFT JOIN `Map_WidgetTag` USING(`tagid`) LEFT JOIN `Data_Widgets` USING(`widgetid`)) WHERE `Data_Tags`.`desc` = 'lifesupport' ORDER BY `desc` ASC;
Expand|Select|Wrap|Line Numbers
- SELECT `Data_Tags`.* FROM (`Data_Widgets` LEFT JOIN `Map_WidgetTag` USING(`widgetid`) LEFT JOIN `Data_Tags` USING(`tagid`)) WHERE `Data_Widgets`.`widgetid` = '15806' ORDER BY `desc` ASC;
(well, you get the idea; there's a lot of queries, and they're all really similar)
Now, let's say that the BOSS comes around and says, "Hey, that looks really good, but we don't need to worry about widgets 0-999. Those belong to the accounting department, and they have their own system."
So off you go to add a "AND `widgetid` > 999" to *every* *single* *one* *of* *your* *queries*.
And you also missed one, which caused a great deal of embarrassment at the company picnic the following month.
There's got to be a better way.
And there is! Enter views.
Expand|Select|Wrap|Line Numbers
- CREATE VIEW `View_WidgetTags` AS SELECT `Data_Widgets`.`widgetid`, `Data_Widgets`.`desc` AS `widgetdesc`, `Data_Tags`.`tagid`, `Data_Tags`.`desc` AS `tagdesc` FROM (`Data_Widgets` LEFT JOIN `Map_WidgetTag` USING(`widgetid`) LEFT JOIN `Data_Tags` USING(`tagid`)) WHERE `Data_Widgets`.`widgetid` > 999;
Expand|Select|Wrap|Line Numbers
- SELECT `widgetid`, `widgetdesc` FROM `View_WidgetTags` WHERE `tagdesc` = 'lifesupport' ORDER BY `widgetdesc` ASC;
Expand|Select|Wrap|Line Numbers
- SELECT `tagid`, `tagdesc` FROM `View_WidgetTags` WHERE `widgetid` = '15806' ORDER BY `tagdesc` ASC;
Expand|Select|Wrap|Line Numbers
- SELECT * FROM `View_WidgetTags` WHERE `widgetdesc` LIKE 'Green%' ORDER BY `widgetdesc`, `tagdesc` ASC;
And when the BOSS stops by your cube and says, "Hey, Murray from accounting says that they're giving us their widgets, so we need to include them in our searches again," you smile and walk over to your terminal and work your magic:
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE VIEW `View_WidgetTags` AS SELECT `Data_Widgets`.`widgetid`, `Data_Widgets`.`desc` AS `widgetdesc`, `Data_Tags`.`tagid`, `Data_Tags`.`desc` AS `tagdesc` FROM (`Data_Widgets` LEFT JOIN `Map_WidgetTag` USING(`widgetid`) LEFT JOIN `Data_Tags` USING(`tagid`));
For more information on views (especially the quirks when it comes to UPDATEing or DELETEing data in a view), check out the MySQL manual page here:
http://dev.mysql.com/doc/refman/5.0/en/create-view.html
