List:General Discussion« Previous MessageNext Message »
From:Steven Buehler Date:February 10 2009 12:47pm
Subject:RE: MySQL View
View as plain text  

> -----Original Message-----
> From: Jochem van Dieten [mailto:jochemd@stripped]
> Sent: Tuesday, February 10, 2009 5:10 AM
> To: mysql@stripped
> Subject: Re: MySQL View
> 
> On Mon, Feb 9, 2009 at 3:41 PM, Steven Buehler wrote:
> > Ok, I just saw a post about using view's in mysql.  I tried to look
> it up
> > and found how to use it, but my question is: what is a view and why
> would
> > you use it?
> 
> The problem with any definition of an object in a database is that
> there are multiple definitions. Usually on the one hand you have the
> definition from abstract relational theory, and on the other hand you
> have the definition from actual working databases. So I am not going
> to bother with a definition, I will try to explain how a view works
> internally inside database code.
> 
> The easiest way to understand a view is to consider a view as a macro
> that gets expanded during the execution of every query that references
> that view in its FROM. Lets take for example the view that your DBA
> has defined for you using:
> CREATE VIEW x AS SELECT * FROM y INNER JOIN z ON y.id = z.id;
> 
> Then you query that view with the query:
> SELECT a FROM x;
> 
> 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);
> 
> Notice that I have done nothing but replace x with its definition
> between parenthesis. And this results in a valid query that can be
> executed. And that is exactly what the database will do. It will do
> this substitution and then it will run the result of that substitution
> as if it were the query that you submitted.
> 
> 
> Obviously a bit more will go on behind the scenes to handle things
> like permissions and optimizations (especially if you get to databases
> that have more functionality then MySQL), but this is really all there
> is to it. A view is a simple macro that assigns an alias to a select
> statement, and when you reference that alias the select statement will
> get substituted back in.

Jochem,

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? 
Even if I reboot the computer, the view will still be there when it comes back up too?

Thanks
Steve


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