MySQL - Views

mysql

http://www.sitepoint.com/mysql-views/
http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-views/

A view is a database object that is defined in term of a SELECT statement that retrieve the data you want to produce. Views are virtual tables. Unlike tables that contain data, view simply contain queries that dynamically retrieve data when used. A view can be used to select data from regular tables (base tables) or other views. In some cases, a view is updatable and can be used with statements such as UPDATE, DELETE, or INSERT to modify the base tables.

Why use views:

  1. To reuse SQL statements.
  2. To simplify complex SQL operations. After the query is written, it can be used easily without having to know the details of the underlying query.
  3. To expose parts of a table instead of complete tables.
  4. To secure data. Users can be given access to specific subset of tables instead of entire tables.
  5. To change data formating and representation. Views can return data formatted and presented differently from their underlying tables.
  6. Access to data become simplified. A view can be used to perform a calculation and display its result. For example, a view definition that invokes aggregate functions can be used to display a summary. A view can be used to select a restricted set of rows by means of an appropriate WHERE clause, or to select only a subset of columns. A view can be used for selecting data from multiple tables by using a join or union.
  7. Views can be used to display table contents differently for different users, so that each user sees only the data pertaining to that user's activities. This improve security by hiding information from users that they should not be able to access or modify. It also reduce distraction because irrelevant columns are not displayed.
  8. If you need to change the structure of your tables to accommodate certain applications, a view can preserve the appearance of the original table to minimize disruption to other applications. For example, if you split a table into two new tables, a view can be created with the same name as the original table and defined to select data from the new tables. You still need to see if existing statements that update the original table will continue to work with the new view.

For the most part, after views are created, they can be used in the same way as tables. You can perform SELECT operations, filter and sort data, join views to other views or tables, and possibly even add and update data.

Because views contains no data, any retrieval needed to execute a query must be processed every time the view is used. If you create complex views with multiple joins and filters, or if you nest views, you may find that performance is dramatically degraded. Extensively test execution before deploying applications that use views.

Rules and Restriction on Views:

  1. You cannot create a TEMPORARY view
  2. You cannot associate a trigger with a view.
  3. The tables on which a view based on must already exist.
  4. Like tables, view must be uniquely named.
  5. Views can be nested.
  6. Views cannot be indexed, nor can they have trigger or default values associated with them.
  7. Views can be used in conjunction with tables
  8. The SELECT statement in a view definition cannot contain sub-queries in the FROM clause, references to TEMPORARY tables, references to user variables, references to procedure parameters (if the view definition occurs within a stored routine), references to prepared statement parameters.

Using Views:

  1. Views are created using the CREATE VIEW
  2. SHOW CREATE VIEW viewname
  3. To remove view: DROP VIEW viewname
  4. To modify a view: CREATE OR REPLACE VIEW
CREATE [OR REPLACE] [ALGORITHM = algorithm_type] VIEW view_name [(column_list)]
 AS select_statement
 [WITH [CASCADED | LOCAL] CHECK OPTION]

The OR REPLACE clause cause any existing view with the same name as the new one to be dropped prior to creation of the new view. The ALGORITHM clause specifies the processing algorithm to use when the view is invoked. The column_list provides names for the view columns to override the default names. When the WITH CHECK OPTION clause is included in the view definition, all data changes made to the view are checked to ensure that the new or updated rows satisfy the view defining condition. If the condition is not satisfied, the change is not accepted, either in the view or in the underlying base tables.

Views and tables share the same namespace, so CREATE VIEW results in an error if a table with the same name already exist. To create the view if it does not exist, or replace a view of the same name if it does exist, use the OR REPLACE clause. The OR REPLACE clause only works if the existing object is a view.

By default, the names of the column in a view are the same as the names of columns retrieved by the SELECT statement. To override the default view column name, include a column_list clause following the view name. If present, the column_list must contain one name per column selected by the view, with multiple names separated by commas. View column names must be unique. If the columns selected by a view do not satisfy this condition, a list of unique explicit column names resolves name clashes. For example, an attempt to define a view that selects columns with the same name from joined tables fails unless you rename at least one of the columns.

Explicit view column names makes it easier to use columns that are calculated from expressions. By default, the name for such a column is the expression, which makes it difficult to reference. Another way to provide names for view columns is by using column aliases in the SELECT statement.

When you use a SELECT statement that refers to a view, it is possible that the statement will contain clauses that are also present in the view definition. Sometimes the corresponding clauses are both used, sometimes one overrides the other, and sometimes the effect is undefined:

  1. A view definition can include a WHERE clause. If a statement that refers to the view includes its own WHERE clause, the conditions in both clauses are used (the conditions are combined with an AND operator).
  2. If a view definition includes an ORDER BY clause, it is used for sorting view results unless a statement that refers to a view includes its own ORDER BY clause. In that case, the view definition ORDER BY is ignored.
  3. For some options, such as HIGH_PRIORITY, the effect is undefined if they appear both in the statement that refers to the view and in the view definition. You can avoid ambiguity in such cases by omitting the option from the view definition and specifying it as necessary only when selecting from the view.

View Algorithms:

A MySQL-specific extension to the CREATE VIEW statement is the ALGORITHM clause, which specifies the algorithm used to process the view. It has this syntax:

ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}

For UNDEFINED, MySQL chooses the algorithm itself. This is the default if no ALGORITHM clause is present. For MERGE, MySQL processes a statement that refers to the view by merging parts of the view definition into the corresponding parts of the statement and executing the resulting merged statement. For TEMPTABLE, MySQL processes a statement that refers to the view by first retrieving the view contents into an intermediate temporary table, and then using the temporary table to finish executing the statement. If you specify TEMPTABLE, the view becomes non-updatable (that is, the view cannot be used to update the based tables).

The MERGE algorithm requires a one-to-one relationship between the rows in the view and the rows in the base table. The MERGE requirement for a one-to-one relationship is not satisfied if the view definition produces a view row from multiple base table rows, or use constructs such as DISTINCT, aggregate functions, a sub-query in the select list, GROUP BY or HAVING.

If you specify MERGE as the algorithm but the view definition contains any construct that prevents MERGE from being used, MySQL issues a warning and resets the algorithm to UNDEFINED. On the other hand, you might want to specify TEMPTABLE to influence how MySQL uses locking while it processes the view. Locks used for any underlying tables can be released after the temporary table has been created. This might reduce contention by allowing other clients to access the base tables while the temporary table is used to finish processing the view. Specifying TEMPTABLE as the algorithm make the view non-updatable.

CREATE VIEW vendorLocation AS SELECT Concat(RTrim(name), ' (', RTrim(country), ')' ) 
 AS title FROM vendor ORDER BY name;

INSERT / UPDATE / DELETE on a view:

You cannot update / insert / delete on a view if:

  1. The SQL that was used to create the view involves GROUP BY and HAVING
  2. The SQL that was used to create the view involves JOINS
  3. The SQL that was used to create the view involves sub-query
  4. The SQL that was used to create the view involves UNION
  5. The SQL that was used to create the view involves aggregate functions
  6. The SQL that was used to create the view involves DISTINCT
  7. The SQL that was used to create the view involves calculated column

The view columns that are to be updated must be simple column references, not expressions.

An updatable view might also be insertable (usable with INSERT) if the view columns consist only of simple table column references, not expression, and if any columns present in the base table but not named in the view or INSERT have default values.

If a view is updatable, you can use the WITH CHECK OPTION clause to place a constraint on allowable modifications. This clause cause the conditions in the WHERE clause of the view definition to be checked when updates are attempted:

  1. An UPDATE to an existing row is allowed only if the WHERE clause remain true for the resulting row.
  2. An INSERT is allowed only if the WHERE clause is true for the new row.

In other words, WITH CHECK OPTION ensures that you cannot update a row in such a way that the view no longer selects it, and that you cannot insert a row that the view will not select.

The WITH CHECK OPTION clause takes an optional keyword that controls the extent to which MySQL performs WHERE-checking for updates when a view is defined in terms of other views:

  1. For WITH LOCAL CHECK OPTION, the check applies only to the view's own WHERE clause.
  2. For WITH CASCADED CHECK OPTION, the view's WHERE clause is checked, as well as the WHERE clause for any underlying views. CASCADED is the default if neither CASCADED nor LOCAL is given.

WITH CHECK OPTION is allowed only for updatable views, and an error occurs if you use it for non-updatable view. This means that ALGORITHM = TEMPTABLE are mutually exclusive, because TEMPTABLE makes a view non-updatable.

Generally rule of thumb: only use view with SELECT. Don't INSERT / UPDATE / DELETE on view.

CREATE VIEW LargePop AS SELECT Name, Population FROM CountryPop WHERE Population >= 1000000;
ALTER VIEW LargePop AS SELECT Name, Population FROM CountryPop WHERE Population >= 1000000;
DROP VIEW [IF EXISTS] view_name [, view_name] ...;

IF EXISTS is a MySQL extension to standard SQL.

A view may become invalid if the base table has been dropped or altered. To check a view for problems of this nature, use CHECK TABLE.

The INFORMATION_SCHEMA database has a VIEWS table that contains view metadata. INFORMATION_SCHEMA also has a TABLES table that contains view metadata.

SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'CityView' AND TABLE_SCHEMA = 'world';
SHOW CREATE VIEW CityView\G;

Some statements in MySQL that were originally designed to display base table information have been extended to work with views:

DESCRIBE and SHOW COLUMNS
SHOW TABLE STATUS
SHOW TABLES

By default, SHOW TABLES list only the names of tables and views. MySQL 5 has a SHOW FULL TABLES variant that display a second column. The value in the column are BASE TABLE or VIEW to indicate what kind of object each name refers to.

Privileges:

To create a view, you must have CREATE VIEW privilege, and you must have sufficient privileges for accessing the base tables. For each column in the base table, you must have some privileges for accessing it (such as SELECT, INSERT, or UPDATE). For columns accessed elsewhere in the statement such as in a WHERE or GROUP BY clause, you must have SELECT privilege.

To use the OR REPLACE clause, or to alter a view with ALTER VIEW, you must have the DROP privilege for the view in addition to the privileges required to create the view.

The DROP VIEW statement requires the DROP privilege for the view.

To access existing views, the privileges required are much like those for tables.

Privileges for a view apply to the view, not to the base tables. Suppose that you have the UPDATE privilege for an updatable view. That enables you to update the underlying table by using the view, but not to update the base table directly. For that, you must have the UPDATE privilege for the table itself.

The SHOW CREATE VIEW statement requires the SELECT privilege for the view. If the view definition refers to tables for which you have no privileges, you must also have the SHOW VIEW privilege in addition to SELECT.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License