View is a virtual table created by joining one or more tables.
MySQL Create VIEW
VIEW is created by SELECT statements. To make a VIEW, SELECT statements are used to take data from the source table.
Syntax:
CREATE [OR REPLACE] VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];
Here, OR REPLACE is optional which is used when a VIEW is already exist.
Example:
CREATE VIEW trainer AS
SELECT course_name, course_trainer
FROM courses;
To see the created VIEW:
SELECT * FROM view_name;
MySQL Update VIEW
To modify or update the already created VIEW without dropping it, we have to use ALTER VIEW statement.
Syntax:
ALTER VIEW view_name AS
SELECT columns
FROM table
WHERE conditions;
Example:
ALTER VIEW trainer AS
SELECT course_name, course_trainer, course_id
FROM courses;
MySQL Drop VIEW
We can drop VIEW using DROP VIEW statement.
Syntax:
DROP VIEW [IF EXISTS] view_name;
Here, IF EXISTS is optional. If we do not specify this clause and the VIEW don’t exist, then the statement will return error.
Example:
DROP VIEW trainer;