Blog

In this guide, we will explain 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 is:

CREATE [OR REPLACE] VIEW view_name AS
  SELECT columns
  FROM tables
  [WHERE conditions];

OR REPLACEOptional. If you do not specify this clause and the VIEW already exists, the CREATE VIEW statement will return an error.view_nameThe name of the VIEW that you wish to create. WHERE conditions optional. The conditions 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 :

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 VIEWS statement.

Syntax

The syntax for the ALTER VIEW statement 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 :

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 VIEWS statement.

Syntax

The syntax for the DROP VIEW statement is:

DROP VIEW [IF EXISTS] view_name;

view_nameThe name of the view that you wish to drop.IF EXISTSOptional. 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 :

DROP VIEW hardware_suppliers;

This DROP VIEW example would drop/delete the MySQL VIEW called hardware_suppliers.

Learn More : Click Here