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);
Output of the EMPLOYEE table:
| EMP_ID | NAME | AGE | SALARY |
|---|
| 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 |
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
view1view2:
view2Examples 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 SuccessfulExplanation: 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 SuccessfulExplanation: 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.
Using the IF EXISTS Clause
When attempting to drop a view that doesn’t exist, MySQL normally throws an error. To prevent this error and ensure smooth execution, you can employ the IF EXISTS clause. If the view exists, it is dropped; if it doesn't, the statement is silently ignored.
Example
Suppose we want to drop a view named 'NEW_1'. Without IF EXISTS, MySQL would produce an error if 'NEW_1' doesn't exist:
DROP VIEW NEW_1;
Output:
ERROR 1051 (42S02)
To handle this scenario gracefully and avoid errors, use IF EXISTS:
DROP VIEW IF EXISTS NEW_1;
Output: If 'NEW_1' exists, it is dropped; otherwise, no action is taken, and no error is reported.
Deleting Rows from a View
In MySQL, you can delete specific rows from a view using the DELETE statement with a WHERE clause. This operation affects the underlying base table from which the view is created, reflecting the changes accordingly.
Syntax
The syntax for deleting rows from a view is similar to deleting rows from a table:
DELETE FROM view_name WHERE condition;
- DELETE FROM: This SQL clause indicates that you want to delete rows from a table or view.
- view_name: Replace this with the name of the view from which you want to delete rows.
- WHERE condition: Optional clause that specifies the conditions for rows to be deleted.
Example
Let's explain this with an example using a view called testView created on the EMPLOYEE table. Suppose testView contains certain records based on a predefined query:
CREATE VIEW testView AS
SELECT * FROM EMPLOYEE
WHERE SALARY > 14000;
Now, to delete rows from testView where the AGE is greater than 21:
DELETE FROM testView WHERE AGE > 21;
After performing the deletion operation, the resulting data in 'testView' (and indirectly in the EMPLOYEE table) will be:
Explanation: This statement deletes all records from 'testView' where the AGE of employees is greater than 21. The changes made to testView will be reflected in the base table EMPLOYEE.
Verification
After executing the DELETE statement, you can verify the updated records in the underlying table EMPLOYEE:
SELECT * FROM EMPLOYEE;
After performing the deletion operation, the resulting data in testView (and indirectly in the EMPLOYEE table) will be:
| EMP_ID | NAME | AGE | SALARY |
|---|
| 1 | Sahil | 21 | 15000 |
| 2 | Alen | 22 | 13000 |
| 3 | John | 22 | 14000 |
| 4 | Alex | 20 | 13000 |
| 5 | Mathew | 22 | 14000 |
| 6 | Sia | 21 | 15000 |
| 8 | Tim | 21 | 14000 |
| 9 | Leo | 20 | 15000 |
| 10 | Tom | 21 | 16000 |
Output: This query will display the current records in the EMPLOYEE table, reflecting the changes made through the DELETE operation on the testView.
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.
Similar Reads
SQL - DROP View
SQL Views provide a powerful way to simplify complex queries and present data in a more understandable format. However, there may be times when we need to remove a view from our database schema. In SQL, deleting a view is straightforward using the DROP VIEW command. In this article, we will explain
5 min read
PL/SQL DROP VIEW
Views serve as an important tool for simplifying complex queries, enhancing data security, and streamlining database management. They provide a virtual layer that presents data from underlying tables in a structured format. However, views like other database objects need to be managed effectively to
3 min read
SQLite DROP VIEW
SQLite is an embedded database that doesn't use a database like Oracle in the background to operate. It is written in C language and is used by developers who embed a lightweight database over the existing application, browser, or embedded systems. The main features of SQLite are that it is a tiny,
4 min read
MySQL | DROP USER
In MySQL, managing user accounts is essential for database security and proper access control. The DROP USER statement is a powerful tool used by database administrators to delete user accounts that are no longer required. This command ensures that all privileges assigned to the user are revoked, pr
3 min read
MYSQL View
MySQL is an open-source RDBMS, i.e. Relational Database Management System which is maintained by Oracle. MySQL has support for major operating systems like Windows, MacOS, Linux, etc. MySQL makes it easy for users to interact with your relational databases, which store data in the form of tables. Yo
11 min read
MySQL DROP Trigger
In MySQL, triggers automatically perform actions when events like INSERT, UPDATE, or DELETE occur on a table However, there are situations where a trigger may not be necessary and its logic may need to be updated. In such cases, MySQL provides the DROP TRIGGER statement to delete an existing trigger
4 min read
PL/SQL VIEW
In Oracle PL/SQL, views are a powerful way to manage data access and simplify complex queries. A view is essentially a virtual table that presents data from one or more tables using a stored query. Unlike physical tables, views do not store the data themselves; they dynamically retrieve data based o
4 min read
MariaDB Drop View
MariaDB is an open-source relational database management system that is based on SQL(Structured query language). It is an improved version of MySQL and has various features, security, and performance when compared to MySQL. This database is open source with a strong community that can be trusted in
4 min read
SQL DROP TABLE
The DROP TABLE command in SQL is a powerful and essential tool used to permanently delete a table from a database, along with all of its data, structure, and associated constraints such as indexes, triggers, and permissions. When executed, this command removes the table and all its contents, making
4 min read
PL/SQL CREATE VIEW
PL/SQL CREATE VIEW is a statement used to create a virtual table based on the result of a query. Views in PL/SQL allow users to access and manipulate data stored in one or more underlying tables as if it were a single table. In this article, We will learn about the PL/SQL CREATE VIEW by understandin
3 min read