VIEW in MySQL

MySQL VIEWS

The view is a virtual table in MySQL. It has no physical existence. It is created by joining one or more tables.

Syntax:

CREATE [OR REPLACE] VIEW name AS  
SELECT column_1, column_2, column_3,......  
FROM table 
WHERE view_conditions;

Parameters:
name: It is used to specify the name of the MySQL VIEW to be created.

Example:

CREATE VIEW item AS  
SELECT id, name
FROM items

Output:

Query OK, 0 rows affected &lt;0.10 sec&gt;

Explanation:
A view named “item” is created from the table “items”. Check the created VIEW, using the below query.
SELECT * FROM item;

Output:

To modify or update the definition of a VIEW in MySQL without dropping, the ALTER VIEW statement is used.

Syntax:

ALTER VIEW name AS  
SELECT column_1, column_2, column_3,......  
FROM table 
WHERE view_conditions;

Parameters:
name: It is used to specify the name of the MySQL VIEW to be modified.

Example:

ALTER VIEW item AS  
SELECT id, name, number
FROM items

Output:

Query OK, 0 rows affected &lt;0.03 sec&gt;

Explanation:
The view named “item” is updated in the above example. Check the updated VIEW, using the below query.
SELECT * FROM item;

Output:

The DROP VIEW statement is used to delete a MySQL VIEW.

Syntax:

DROP VIEW [IF EXISTS] name;

Parameters:
name: It is used to specify the name of the MySQL VIEW to be deleted.

Example:

DROP VIEW item;

Explanation:
The view named “item” will be completely deleted after the execution of the above query.