MariaDB: VIEW

This MariaDB tutorial explains how to create, update, and drop VIEWS in MariaDB with syntax and examples.

What is a VIEW in MariaDB?

In MariaDB, 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 MariaDB 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 MariaDB.

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 MariaDB:

CREATE VIEW great_sites AS
  SELECT site_id, site_name, server_name
  FROM sites
  WHERE site_name = 'AODBA.com';

This CREATE VIEW example would create a virtual table based on the result set of the SELECT statement. You can now query the MariaDB VIEW as follows:

SELECT *
FROM great_sites;

Update VIEW

You can modify the definition of a VIEW in MariaDB without dropping it by using the ALTER VIEW statement.

Syntax

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

ALTER VIEW great_sites AS
  SELECT site_id, site_name, server_name
  FROM sites
  WHERE site_name = 'AODBA.com'
  OR site_name = 'mySite.com'
  OR site_name = 'data.com';

This ALTER VIEW example in MariaDB would update the definition of the VIEW called great_sites without dropping it. In this example, we are changing the WHERE clause of the VIEW.

Drop VIEW

Once a VIEW has been created in MariaDB, you can drop it with the DROP VIEW statement.

Syntax

The syntax for the DROP VIEW statement in MariaDB 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 MariaDB:

DROP VIEW great_sites;

This DROP VIEW example would drop/delete the MariaDB VIEW called great_sites.