MySQL DROP VIEW

Summary: in this tutorial, you will learn how to use the MySQL DROP VIEW statement to delete a view from the database.

Introduction to the MySQL DROP VIEW statement

The DROP VIEW statement deletes a view completely from the database. Here’s the basic syntax of the DROP VIEW statement:

DROP VIEW [IF EXISTS] view_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the view that you want to drop after the DROP VIEW keywords. The optional IF EXISTS option conditionally removes the view only if it exists.

To remove multiple views in a single statement, you use the following syntax:

DROP VIEW [IF EXISTS] view_name1 [,view_name2]...;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify a list of comma-separated views after the DROP VIEW keywords.

If the list contains a view that doesn’t exist, the DROP VIEW statement will fail and won’t delete any view. However, if you use the IF EXISTS option, the DROP VIEW statement will generate a NOTE for each non-existing view.

Note that in MySQL 5.7 or earlier, the DROP VIEW returns an error if there is any non-existing view. However, it drops the views that exist.

MySQL DROP VIEW statement examples

Let’s take some examples of using the DROP VIEW statement.

1) MySQL DROP VIEW – drop a view example

This statement creates a view named customerPayments based on the customers and payments tables:

CREATE VIEW customerPayments 
AS
    SELECT 
        customerName, 
        SUM(amount) payment
    FROM
        customers
    INNER JOIN payments 
        USING (customerNumber)
    GROUP BY 
        customerName;Code language: SQL (Structured Query Language) (sql)

This example uses the DROP VIEW statement to drop the customerPayments view:

DROP VIEW IF EXISTS customerPayments;
Code language: SQL (Structured Query Language) (sql)

2) MySQL DROP VIEW – drop multiple views example

This statement creates a view named employeeOffices based on the employees and offices tables:

CREATE VIEW employeeOffices AS
    SELECT 
        firstName, lastName, addressLine1, city
    FROM
        employees
            INNER JOIN
        offices USING (officeCode);Code language: SQL (Structured Query Language) (sql)

The following statement uses the DROP VIEW statement to delete two views employeeOffices and eOffices:

DROP VIEW employeeOffices, eOffices;
Code language: SQL (Structured Query Language) (sql)

MySQL issued the following error:

Error Code: 1051. Unknown table 'classicmodels.eoffices'
Code language: SQL (Structured Query Language) (sql)

Let’s add the IF EXISTS option like this:

DROP VIEW IF EXISTS employeeOffices, eOffices;
Code language: SQL (Structured Query Language) (sql)

MySQL issued a warning instead:

1 warning(s): 1051 Unknown table 'classicmodels.eoffices'
Code language: SQL (Structured Query Language) (sql)

The employeeOffices view remains intact.

This statement creates a new view named productCatalogs based on the products and productLines tables:

CREATE VIEW productCatalogs AS
    SELECT 
        productLine, productName, msrp
    FROM
        products
            INNER JOIN
        productLines USING (productLine);
Code language: SQL (Structured Query Language) (sql)

The following example uses the DROP VIEW statement to delete the employeeOffices and productCatalogs views:

DROP VIEW employeeOffices, productCatalogs;
Code language: SQL (Structured Query Language) (sql)

MySQL deleted the views completely.

Summary

• Use the DROP VIEW statement to delete one or more views from a database.
• Use the IF EXISTS option to conditionally delete a view if it exists.