MySQL: VIEW
This MySQL tutorial explains how to create, update, and drop VIEWS in MySQL with syntax and examples.
What is a VIEW in MySQL?
In MySQL, a VIEW is not a physical table, but rather, it is in essence a virtual table created by a query joining one or more tables.
Create VIEW
Syntax
The syntax for the CREATE VIEW statement in MySQL is:
CREATE [OR REPLACE] VIEW view_name AS SELECT columns FROM tables [WHERE conditions];
- OR REPLACE
- Optional. If you do not specify this clause and the VIEW already exists, the CREATE VIEW statement will return an error.
- view_name
- The name of the VIEW that you wish to create in MySQL.
- WHERE conditions
- Optional. The conditions that must be met for the records to be included in the VIEW.
Example
Here is an example of how to use the CREATE VIEW statement to create a view in MySQL:
CREATE VIEW hardware_suppliers AS SELECT supplier_id, supplier_name FROM suppliers WHERE category_type = 'Hardware';
This CREATE VIEW example would create a virtual table based on the result set of the SELECT statement. You can now query the MySQL VIEW as follows:
SELECT * FROM hardware_suppliers;
Update VIEW
You can modify the definition of a VIEW in MySQL without dropping it by using the ALTER VIEW statement.
Syntax
The syntax for the ALTER VIEW statement in MySQL is:
ALTER VIEW view_name AS SELECT columns FROM table WHERE conditions;
Example
Here is an example of how you would use the ALTER VIEW statement in MySQL:
ALTER VIEW hardware_suppliers AS SELECT supplier_id, supplier_name, address, city FROM suppliers WHERE category_type = 'Hardware';
This ALTER VIEW example in MySQL would update the definition of the VIEW called hardware_suppliers without dropping it. In this example, we are adding the address and city columns to the VIEW.
Drop VIEW
Once a VIEW has been created in MySQL, you can drop it with the DROP VIEW statement.
Syntax
The syntax for the DROP VIEW statement in MySQL is:
DROP VIEW [IF EXISTS] view_name;
- view_name
- The name of the view that you wish to drop.
- IF EXISTS
- Optional. If you do not specify this clause and the VIEW does not exist, the DROP VIEW statement will return an error.
Example
Here is an example of how to use the DROP VIEW statement in MySQL:
DROP VIEW hardware_suppliers;
This DROP VIEW example would drop/delete the MySQL VIEW called hardware_suppliers.
Advertisements