> -----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
