List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:February 10 2009 6:47pm
Subject:Re: MySQL View
View as plain text  
On Tue, Feb 10, 2009 at 1:47 PM, Steven Buehler wrote:
> From: Jochem van Dieten:
>> What the database will do for you behind the scenes is expand your
>> usage of the view. In effect, the database will replace "x" with its
>> definition. So your query SELECT a FROM x; gets expanded to:
>> SELECT a FROM (SELECT * FROM y INNER JOIN z ON y.id = z.id);
>
> Not sure about the other poster, but this helps explain it to me.  If I understand
> you correctly, if I have multiple tables with many columns in them, but have several
> queries that need to pull only a few columns from each and put them together, it is
> probably best to create a view to do this so that I don't have to keep running joins in my
> queries?

No. I am explicitly not saying how you should use views. I am just
telling you how they work.

But to give you some examples of how you could use views (I am still
not saying how you should use views):
1. Use views to replace repetitive elements in queries. If you have
lots of queries that perform the same join or filter, put it in a
view. That has no semantic value, but you save yourself some typing.
2. Use views to manage permissions. If people have only access to a
subset of the data, revoke their permissions on the table and define a
view that has exactly the data that they have access to. Then give
them permissions on the view.
3. Use views to define new schema elements that have meaning. If you
have a normalized schema an invoice may be spread over a dozen tables
(customer, invoice, invoiceline, item, price, shipping, payment,
account etc.). You can define a view with all the proper joins and
filters that groups that together so you get all the data at once.
(Some people may argue that this is the same as no. 1, but I think it
is an important distinction that the view represents an actual object:
an invoice as you print and send them.)
4. <your great view usage here>


>  Even if I reboot the computer, the view will still be there when it comes back up
> too?

Yes, views are persitent.

Jochem


-- 
Jochem van Dieten
http://jochem.vandieten.net/
Thread
MySQL ViewSteven Buehler9 Feb
  • Re: MySQL ViewBaron Schwartz9 Feb
    • RE: MySQL ViewSteven Buehler9 Feb
      • RE: MySQL ViewMartin Gainty9 Feb
        • RE: MySQL ViewSteven Buehler9 Feb
          • Re: MySQL ViewClaudio Nanni9 Feb
            • RE: MySQL ViewSteven Buehler10 Feb
  • Re: MySQL ViewJochem van Dieten10 Feb
    • RE: MySQL ViewSteven Buehler10 Feb
      • Re: MySQL ViewJochem van Dieten10 Feb