Creating Views : Create View « View

Creating Views : Create View « View « MySQL Tutorial

1.	Introduction
2.	Select Query
3.	Database
4.	Table
5.	Table Join
6.	Subquery
7.	Insert Update Delete
8.	Logic Operator
9.	View
10.	Data Types
11.	Procedure Function
12.	Cursor
13.	Trigger
14.	Date Time Functions
15.	Comparison Functions Operators
16.	Aggregate Functions
17.	Cast Functions Operators
18.	Control Flow Functions
19.	Encryption Compression Functions
20.	Information Functions
21.	Math Numeric Functions
22.	Miscellaneous Functions
23.	String Functions
24.	Regular Expressions
25.	Data Dictionary
26.	MySQL Utilities
27.	Privilege

Java

Java Tutorial

Java Source Code / Java Documentation

Illustrator Tutorials

GIMP Tutorials

C# / C Sharp

C# / CSharp Tutorial

C# / CSharp Open Source

SQL Server / T-SQL Tutorial

Oracle PL / SQL

Oracle PL/SQL Tutorial

Flash / Flex / ActionScript

VBA / Excel / Access / Word

XML

XML Tutorial

Microsoft Office PowerPoint 2007 Tutorial

Microsoft Office Excel 2007 Tutorial

Microsoft Office Word 2007 Tutorial

MySQL Tutorial » View » Create View

9. 1. 2. 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>

9. 1. Create View
	9. 1. 1.	A few things aren't allowed in a query that defines a view
	9. 1. 2.	Creating Views
	9. 1. 3.	Creating a View with Specified Column Names
	9. 1. 4.	Creating a View with Joined Tables
	9. 1. 5.	Adding ORDER BY to the Joined Table View
	9. 1. 6.	Using a GROUP BY Clause to Create a View
	9. 1. 7.	Using a GROUP BY Clause to Create a View with ALGORITHM = TEMPTABLE
	9. 1. 8.	Using HAVING with GROUP BY to Create a View
	9. 1. 9.	Creating a View with UNION
	9. 1. 10.	Creating a view by joining two tables

www.java2java.com | Contact Us

All other trademarks are property of their respective owners.