MySQL View or MySQL Virtual Table

MySQL View or MySQL Virtual Table

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;  
Post a Comment