MariaDB Drop View

Summary: in this tutorial, you will learn how to use the MariaDB drop view statement to delete one or more views from a database.

Introduction to the MariaDB drop view statement

The drop view statement removes a view from a database. The following shows the syntax of the drop view statement:

drop view [if exists] view_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you need to specify the name of the view that you want to delete after the drop view keywords.

You can use the if exists clause to conditionally remove a view only if it exists. The if exists clause is optional.

To drop multiple views at once, you use the following form of the drop view statement:

drop view [if exists] view_name1, view_name2,...;
Code language: SQL (Structured Query Language) (sql)

In this form, the drop view statement will fail with an error if there is a non-existing view in the list. It will not drop any view in this case.

However, if you use the if exists clause, the drop view will remove only the views that exist. In addition, it will generate a warning for each non-existing view.

MariaDB drop view statement examples

Let’s take some examples of using the drop view statement.

A) Using MariaDB drop view to remove a view

First, create a view called country_official_languages based on the countries, country_languages, and languages tables:

create view country_official_languages
as
select 
    language, 
    name country
from 
    countries
inner join country_languages 
    using (country_id)
inner join languages 
    using (language_id)
where 
    country_languages.official = 1;
Code language: SQL (Structured Query Language) (sql)

Second, show all views in the current database by using the show full tables statement:

show full tables;
Code language: SQL (Structured Query Language) (sql)

Third, drop the country_official_languages view:

drop view country_official_languages;
Code language: SQL (Structured Query Language) (sql)

B) Using MariaDB drop view to remove multiple views

We’ll use the tables countries and country_stats from the sample database in the following example:

First, create two views stat_2017 and stat_2018:

create view stat_2017 as
select 
    name, 
    population, 
    gdp 
from countries
inner join country_stats 
    using (country_id) 
where year = 2017;

create view stat_2018 as
select 
    name, 
    population, 
    gdp 
from countries
inner join country_stats 
    using (country_id) 
where year = 2018;
Code language: SQL (Structured Query Language) (sql)

Second, drop two views: stat_2017, stat_2018, and a non-existing view stat_2016:

drop view 
    stat_2016, 
    stat_2017, 
    stat_2018;
Code language: SQL (Structured Query Language) (sql)

MariaDB issued the following error:

SQL Error (4092): Unknown VIEW: 'nation.stat_2016
Code language: SQL (Structured Query Language) (sql)

The views stat_2016 and stat_2016 are not deleted.

Third, drop the same views but use the if exists clause:

drop view if exists 
    stat_2016, 
    stat_2017, 
    stat_2018;
Code language: SQL (Structured Query Language) (sql)

The views stat_2017 and stat_2018 are removed. In addition, MariaDB issued a warning. To display the warning, you use the show warnings statement:

show warnings;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you learned how to use the MariaDB drop view statement to drop one or more views from a database.