473,549 Members | 2,753 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Views and Why We Love Them

5,821 Recognized Expert Expert
Today I'd like to talk about a new feature introduced in MySQL 5: Views.

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, "lifesuppor t".

Expand|Select|Wrap|Line Numbers
  1. 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;
Oh, and a little further down the page, we want to get all the tags for widget #15806:

Expand|Select|Wrap|Line Numbers
  1. 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;
Oh, and a little further, we need all the tags that are associated with any widget whose description starts with 'Green':

(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
  1. 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;
Going back to our exmples, to find all "lifesuppor t" widgets:

Expand|Select|Wrap|Line Numbers
  1. SELECT `widgetid`, `widgetdesc` FROM `View_WidgetTags` WHERE `tagdesc` = 'lifesupport' ORDER BY `widgetdesc` ASC;
And to find all tags for widget #15806:

Expand|Select|Wrap|Line Numbers
  1. SELECT `tagid`, `tagdesc` FROM `View_WidgetTags` WHERE `widgetid` = '15806' ORDER BY `tagdesc` ASC;
And incidentally:

Expand|Select|Wrap|Line Numbers
  1.     SELECT * FROM `View_WidgetTags` WHERE `widgetdesc` LIKE 'Green%' ORDER BY `widgetdesc`, `tagdesc` ASC;
Remember that the view is a hybrid query/table; you define it like a query, but you access it like a table. When you go to SELECT data from your view, MySQL has already compiled the data that matches the query you used to define the view, so your SELECT query will execute even faster!

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
  1. 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`));
And instantly, without having to change a single query in your PHP code (or what-have-you), *all* your queries return the previously-forbidden widgets!

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:
May 6 '07 #1
1 9576
18 New Member
How is this better than creating a table based on a select query?

CREATE TABLE `View_WidgetTag s` SELECT `Data_Widgets`. `widgetid`, `Data_Widgets`. `desc` AS `widgetdesc`, `Data_Tags`.`ta gid`, `Data_Tags`.`de sc` AS `tagdesc` FROM (`Data_Widgets` LEFT JOIN `Map_WidgetTag` USING(`widgetid `) LEFT JOIN `Data_Tags` USING(`tagid`)) WHERE `Data_Widgets`. `widgetid` > 999;

And then running queries on that table?


Answered my own question with a simple test. I tried updating the table which the VIEW selects from and the VIEW's result is updated also.

The CREATE VIEW FROM QUERY "caches" the QUERY not the SQL Result Set. So if the Original table in the QUERY is updated, your VIEW table is updated, and yet you can treat a VIEW just like a table, set permissions etc...

very cool.
Jun 9 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

by: asj | last post by:
awhile back, eBay decided to switch from a Microsoft/.NET/Windows architecture on the backend to a J2EE one, which might explain why their java backend will handle up to 1 BILLION page views a day! the funny thing was eBay was one of the major case studies for .NET at the beginning, when there was still some hype about it. interesting post...
by: Mike N. | last post by:
Hello: I am new to T-SQL programing, and relativly new to SQL statements in general, although I have a good understanding of database theory. I'm a little confused as to the fundamental differences between a view and a query, or rather, when it is more appropriate to use one vs. the other. It seems to me that most select queries can be...
by: dbtoo_dbtoo | last post by:
One of the databases has 50 views and when I do a db2look I only get schema for 40 of them. If I select from the sysviews, I can see all 50 (the text column contains schema for all 10 (missing) views). What's going on here? Why can't or how can I get the schema for these 10 views. Aix 5.1 V7.2 EE FP9 Thanks.
by: Bruce | last post by:
I have several user defined functions which are referenced in triggers and views. For software upgrades, I need to be able to drop the triggers and views which reference these user defined functions, then drop and recreate the user defined functions, then recreate all the views and triggers. I don't imiagine I'm the first person who needed...
by: VB6 User | last post by:
Hi all devies! Many (.NUT, .NOT or whatever), APIs, VB6, Views & Questions Your can not call APIs directly in .NET, only via P/Invoke. There are some things that cannot be done in .NET, and that requires APIs. Earlier people
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test Environments. What is the purpose of a view if I can just copy the vode from a view and put it into a stored procedure? Should I be...
by: rod.weir | last post by:
Fellow database developers, I would like to draw on your experience with views. I have a database that includes many views. Sometimes, views contains other views, and those views in turn may contain views. In fact, I have some views in my database that are a product of nested views of up to 6 levels deep! The reason we did this was. ...
by: Peter | last post by:
People are telling me it is bad to put select * from <atable> in a view. I better should list all fields of the table inside the definition of the view. I dont know exactly why but some say: A select * from makes sql server does a table scan.
by: Gary | last post by:
Hello guys! Bear with me, I am a newbie. She is the Data Warehouse manager. She has about 50 users to use the Oracle database from M$ Access via ODBC connection. All those users have only SELECT privileges on certain tables. I built all the roles and users for them and they work fine. Then she asked "Why do YOU let them see all those...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.