Creating Views

Creating a view requires a view name and a SQL statement:

Once the view is created, you can query it in the same way as any other table.

The CREATE statement:


CREATE [OR REPLACE] [<algorithm attributes>] VIEW [database.]< name> [(<columns>)]
AS <SELECT statement> [<check options>]

When creating a view, you can use the OR REPLACE syntax to replace an existing view.

The algorithm attributes have some control over how MySQL executes the query.

These attributes are MERGE, TEMPTABLE, and UNDEFINED.

A MERGE algorithm attempts to combine the incoming SQL statement with the SELECT statement that defines the view and create one SQL statement to process.

MERGE isn't allowed in a non-one-to-one relationship with records in the underlying tables.

Non-one-to-one relationship is created by using aggregation functions (SUM(), MIN(), MAX(), and so on) or by using the DISTINCT, GROUP BY, HAVING, and UNION keywords.

In instances where the MERGE algorithm isn't allowed, the database switches the ALGORITHM value to UNDEFINED.


CREATE ALGORITHM = MERGE VIEW myView AS
   SELECT view_id, first_name FROM employee WHERE id = 1;

SELECT name FROM myView WHERE view_id = 1;

With a MERGE algorithm, MySQL combines the query with the SELECT statement in the view definition to come up with a single query to execute:


SELECT name FROM employee WHERE view_id = 1 AND id = 1;

The TEMPTABLE algorithm forces a view to load the data from the underlying tables into a temporary table.

The incoming statement is executed against the temporary table.

Moving the data to a temporary table means the underlying tables can be released from any locks.

The TEMPTABLE option means the least amount of lock time for the view's underlying tables.

Views that reference only literal values are required to use a temporary table.

Views that use temporary tables are not updatable.

Setting the view algorithm to UNDEFINED tells the query parser to make the choice between the MERGE and TEMPTABLE algorithms.

The parser will use MERGE unless a condition forces it to use a temporary table.

UNDEFINED is the default, and it will be used if you omit the ALGORITHM keyword.

UNDEFINED is also used if the view specifies MERGE but can be processed only by using a temporary table.

Quote from www.mysql.com


mysql>
mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,'Jason',    'Martin',  '19960725',  '20060725', 1234.56, 'Toronto',  'Programmer');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,'Alison',   'Mathews',  '19760321', '19860221', 6661.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,'James',    'Smith',    '19781212', '19900315', 6544.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,'Celia',    'Rice',     '19821024', '19990421', 2344.78, 'Vancouver','Manager');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,'Robert',   'Black',    '19840115', '19980808', 2334.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,'Linda',    'Green',    '19870730', '19960104', 4322.78,'New York',  'Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,'David',    'Larry',    '19901231', '19980212', 7897.78,'New York',  'Manager');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,'James',    'Cat',     '19960917',  '20020415', 1232.78,'Vancouver', 'Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> CREATE VIEW myView AS
    ->    SELECT id, first_name FROM employee WHERE id = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> SELECT * FROM myView;
+------+------------+
| id   | first_name |
+------+------------+
|    1 | Jason      |
+------+------------+
1 row in set (0.02 sec)

mysql>
mysql>
mysql> drop view myView;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>

Creating Views : Create View : View MySQL TUTORIALS

MySQL TUTORIALS » View » Create View »

Leave a Comment / Note

MySQL TUTORIALS