Before diving into any technicalities, I would like to share a little about “Views”. What is a VIEW, what is the benefit of it?

A view is a virtual or logical table composed of the result set of a query. Unlike ordinary tables (base tables) in a relational database, a view is not part of the physical schema: it is a dynamic, virtual table computed or collated from data in the database.

Views can provide advantages over tables;

• They can subset the data contained in a table
• They can join and simplify multiple tables into a single virtual table
• Views can act as aggregated tables, where aggregated data (sum, average etc.) are calculated and presented as part of the data
• Views can hide the complexity of data, for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
• Views take very little space to store; only the definition is stored, not a copy of all the data they present
• Depending on the SQL engine used, views can provide extra security.
• Views can limit the exposure to which a table or tables are exposed to the outer world

MySQL has added this feature in version 5.0.1. The basic operations on VIEW can be carried out through the following syntax:

mysql> CREATE VIEW v AS SELECT * FROM table1;

At the time of creation, the view definition is “frozen”. In simple words, any changes made to the table structure will not affect the VIEW result. For example, if you created a VIEW with 2 columns and added another column to the table structure, VIEW would be still giving you result with 2 columns. You may alter your VIEW by running the ALTER query:

mysql> ALTER VIEW v AS SELECT * FROM table1;

To drop a VIEW, run:

mysql> DROP VIEW v;

VIEW definitions are restricted to:

• The SELECT statement cannot contain a subquery in the FROM clause
• The SELECT statement cannot refer to system or user variables
• The SELECT statement cannot refer to prepared statement parameters
• Within a stored routine, the definition cannot refer to routine parameters or local variables
• Any table or view referred to in the definition must exist. However, after a view has been created, it is possible to drop a table or view that the definition refers to. In this case, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement
• The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view
• The tables named in the view definition must already exist
• You cannot associate a trigger with a view

The article is an extract from: Wikipedia, MySQL 5.0 Reference Manual

Rate this topic:

(2 votes, average: 4 out of 5)

 Loading ...