Views

View Creation Syntax

#SYNTAX:
CREATE 
 [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name] 
AS
 [SELECT  statement]
  • MERGE - Mysql takes the incoming view query and combine it with the query. Ex. Combing the where clauses to form a single query. It then executes this query again the SELECT table. This is the most effecient way to use a view.
  • TEMPTABLE - This method is slower than MERGE. MySql creates a temporary table from the SELECT query. It then executes the incoming query against the temporary table. This is slower because mysql has to create a temporary table and them copy the result from the SELECT into this table. Finally, executing the incoming view query against this table.
  • UNDEFINED - This is the default algorithm when one isn't specified. Mysql will choose the best option between MERGE and TEMPTABLE.

Create View

Example 1:
CREATE VIEW youngsters AS
SELECT name FROM person WHERE age < 15;

Select from the view.

SELECT * 
FROM  `youngsters` 
WHERE name LIKE  '%m%'
Example 2: Calculated FIeld
CREATE VIEW TotalOrderForSale
   AS 
  SELECT orderId,
  SUM  (orderQuantity * individualPrice) total
  FROM Orders
  GROUP by orderId
  ORDER BY total DESC

Now we can run a simple query against the view.

//the orderID and total columns
Select * FROM TotalOrderForSale;

Limation: Subquery In Where Clause

The SELECT statement can contain a subquery in WHERE clause but not in the FROM clause

CREATE VIEW vwProducts  AS 
 SELECT productCode,
        productName,
        buyPrice 
 FROM products
 WHERE buyPrice > (
      SELECT AVG  (buyPrice)
      FROM  products
 )
 ORDER BY buyPrice DESC

Other Limitations Of SELECT

  • The SELECT statement cannot refer to any variable including local variable, user variable or session variable.
  • The SELECT statement cannot refer to the parameters of prepared statements.

Mysql Implementation Of Views

Views are implement in 2 ways in mysql

  • Mysql may create a temporary table using the underlying table. The view query is then executed against that.
  • Mysql may combine the view query with the underlying query. That query is then executed against the original table.

Views Backup

Mysql support backups of the view.

Each time a view is replaced or changed a backup is placed with the arc(archive) folder within your database folder.

The first backup file is view_name.frm-00001, the second view_name.frm-00002 and so on.

Caching View

The view queries will be cached if caching is enabled on the server.

Pros And Cons Of Views

Pros

  • Hides the underlying complex query
  • Provides security by preventing the user from access all the data.
  • Return computed columns as if they were regular columns.
  • Change the underlying query without affecting the view

Cons

  • Querying data from a view might be slow especially it queries another view.
  • Changing the underlying  tables affects the view.

Updateable Views

In MySql 5, views can be updated if the SELECT statement follows the following rules.

Reference only a single table - NO JOINS

  • Do not use subqueries
  • Do not use unions - NO UNIONS
  • Do not use aggregate, functions and computed columns (COUNT(), SUM(), etc)
  • Do not use DISTINCT
CREATE VIEW youngsters AS
SELECT name FROM person WHERE age < 15;

Update youngsters SET name = 'Brad' WHERE name = 'Mark';

Alter Views

#syntax:
ALTER
 [ALGORITHM =  {MERGE | TEMPTABLE | UNDEFINED}]
  VIEW [database_name].  [view_name]
   AS 
 [SELECT  statement]
#example usage:
ALTER VIEW youngsters AS 
SELECT name FROM person WHERE age < 14;

Drop View

#DROP VIEW [IF EXISTS] [database_name].[view_name]
DROP VIEW IF EXISTS youngsters;

SHOW VIEW CREATION STATEMENT

show the sql statements use to create the view.

#SHOW CREATE VIEW [database_name].[view_ name];
SHOW CREATE VIEW youngsters;

 

Cookbook Category: 

Add new comment