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 <0.10 sec>

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

Output:

ID NAME
1 Electronics
2 Sports
3 Fashion
4 Grocery

MySQL Update VIEW:

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 <0.03 sec>

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

Output:

ID NAME NUMBER
1 Electronics 10
2 Sports 15
3 Fashion 20
4 Grocery 30

MySQL DROP VIEW

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.

Please Share