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