The From Clause and SQL Queries - Views
(Page 3 of 4 )
A view is a query that is stored in the data dictionary. It looks and acts like a table, but there is no data associated with a view (this is why I call it a virtual table). When you issue a query against a view, your query is merged with the view definition to create a final query to be executed.
To demonstrate, here’s a view definition that queries theemployeetable and includes a call to a built-in function:
CREATE VIEW employee_vw AS
SELECT emp_id, fname, lname,
YEAR(start_date) start_year
FROM employee;
After the view has been created, no additional data is created: theselectstatement is simply stored by the server for future use. Now that the view exists, you can issue queries against it, as in:
SELECT emp_id, start_year
FROM employee_vw;
emp_id start_year
-------- ----------
1 2001
2 2002
3 2000
4 2002
5 2003
6 2004
7 2004
8 2002
9 2002
10 2002
11 2000
12 2003
13 2000
14 2002
15 2003
16 2001
17 2002
18 2002
Views are created for various reasons, including to hide columns from users and to simplify complex database designs.
Views are not supported in MySQL until Version 5.0.1. They are, however, a heavily used feature of the other database servers, so, for those readers planning to work with MySQL, you should keep them in mind.
Because Version 4.1.11 of MySQL does not include views, I intentionally left out the mysql> prompt in the previous query along with the usual result-set formatting. I use this same convention in several other chapters in the book when I show a SQL feature not yet implemented by MySQL.
Next: Table Links >>
More Database Articles Articles
More By O'Reilly Media
|
 This article is excerpted from chapter three of the book Learning SQL, written by Alan Beaulieu (O'Reilly; ISBN: 0596007272). Check it out today at your favorite bookstore. Buy this book now.
|
|
