Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/mysql/mysql-drop-view/ below:

MySQL - Drop View - GeeksforGeeks

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.

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.

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.

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 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:

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

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

view2:

DROP VIEW view_name;

DROP VIEW view1;

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.

DROP VIEW view_name;

DROP VIEW view2;

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.

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.

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.

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.

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.

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:

Output: This query will display the current records in the EMPLOYEE table, reflecting the changes made through the DELETE operation on the testView.

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4