MySQL – Drop View

MySQL is a powerful open-source relational database management system that is widely used for building scalable and high-performance databases. Developed by MySQL AB, which is currently owned by Oracle Corporation, MySQL has been around since 1995.

It is known for its robust, easy-to-use, and reliable features, as well as its quick processing speeds. MySQL is particularly popular among dynamic web applications and is often used in conjunction with server-side programming languages like PHP and Python. In this article, you will learn about how to DROP a VIEW in MySQL. You will learn how the DROP VIEW along with some examples.

MySQL DROP VIEW Statement

In relational database management systems (RDBMS) like MySQL, a VIEW is a virtual table interactive with data generated from one or more underlying tables through either a defined query. Unlike a regular table, the VIEW as a query doesn’t store the data itself. Instead, it creates a result set when someone queries it. For dropping a VIEW in MYSQL the view should be already existing.

Syntax:

DROP VIEW view_name;

• DROP VIEW: This is the SQL keyword indicating that you want to drop (delete) a view from the database.
• view_name: This is the name of the view you want to drop. In your case, view_name should be replaced with the actual name of the view you wish to delete.

Examples of MySQL DROP VIEW Statement

Let’s take an example of the EMPLOYEE table having EMP_ID, NAME, AGE, and SALARY as columns.

CREATE TABLE EMPLOYEE (
    EMP_ID INT PRIMARY KEY,
    NAME VARCHAR(50),
    AGE INT,
    SALARY INT
);

Insert the data on it:

INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, SALARY) VALUES
(1, 'Sahil', 21, 15000),
(2, 'Alen', 22, 13000),
(3, 'John', 22, 14000),
(4, 'Alex', 20, 13000),
(5, 'Mathew', 22, 14000),
(6, 'Sia', 21, 15000),
(7, 'David', 22, 16000),
(8, 'Tim', 21, 14000),
(9, 'Leo', 20, 15000),
(10, 'Tom', 21, 16000);

EMPLOYEE Table:

EMPLOYEE TABLE

Let’s first CREATE 2 VIEWS from the EMPLOYEE Table.

Query:

CREATE VIEW view1 AS
  SELECT EMP_ID, SALARY
  FROM EMPLOYEE


CREATE VIEW view2 AS
SELECT EMP_ID, AGE, SALARY
FROM EMPLOYEE
WHERE SALARY=14000;

Output: view1

view1

view2:

view2

Examples of MySQL drop view statement

Example 1: Drop view1 using the Drop View statement

Syntax:

DROP VIEW view_name;

Query:

DROP VIEW view1;

Output:

Dropped Successful

Explanation: Here we are dropping a view1 using the DROP VIEW statement. The view1 had 10 rows present in it and after dropping it all 10 rows were deleted from the view and also the view got dropped.

Example 2: Drop view2 using the Drop View statement

Syntax:

DROP VIEW view_name;

Query:

DROP VIEW view2;

Output:

Dropped Successful

Explanation: Here we are dropping a view2 using the DROP VIEW statement. The view2 had 3 rows present in it and after dropping it all 3 rows were deleted from the view and also the view got dropped.

Conclusion

In Conclusion, the DROP VIEW statement in MySQL does not only provide an easier and more effective way for removing VIEWS from the database schematic; it also ensures lower maintenance. Through this sentence, users will be able to set up and control their database structure with ease which is a critical aspect of having organised and efficient database management. When it comes to running misleading queries or changing the data layout, DROP VIEW allows users to structure their databases by offering accuracy and simplicity.