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 = 'TechOnTheNet.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 = 'TechOnTheNet.com' OR site_name = 'CheckYourMath.com' OR site_name = 'BigActivities.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.
Advertisements