MySQL - Drop View



A MySQL View is a virtual table which is generated from a predefined SQL query. It contains (all or selective) records from one or more database tables.

Views are not stored in a database physically, but they can still be dropped whenever not necessary. Even though they are used to see and modify the data in a database table, the data in that table remains unchanged when views are dropped.

The MySQL DROP VIEW Statement

The DROP VIEW statement in MySQL is used to delete an existing view, along with its definition and other information. Once the view is dropped, all the permissions for it will also be removed. We can also use this statement to drop indexed views.

Suppose a table is dropped using the DROP TABLE command and it has a view associated to it, this view must also be dropped explicitly using the DROP VIEW command.

NOTE

  • While trying to perform queries, the database engine checks all the objects referenced in that statement are valid and exist. So, if a view does not exist in the database, the DROP VIEW statement will throw an error.

  • To drop a table in a database, one must require ALTER permission on the said table and CONTROL permissions on the table schema.

Syntax

Following is the syntax of the DROP VIEW Statement −

DROP VIEW view_name;

Where, view_name is the name of the view to be deleted.

Example

Suppose we have created a table named CUSTOMERS using the following CREATE TABLE query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 2),
   PRIMARY KEY(ID)
);

Let us insert records in the above created table using the following INSERT query −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', '32', 'Ahmedabad', 2000),
(2, 'Khilan', '25', 'Delhi', 1500),
(3, 'Kaushik', '23', 'Kota', 2500),
(4, 'Chaitali', '26', 'Mumbai', 6500),
(5, 'Hardik','27', 'Bhopal', 8500),
(6, 'Komal', '22', 'MP', 9000),
(7, 'Muffy', '24', 'Indore', 5500);

Creating a View −

Now, let us create a view on this table using the CREATE VIEW statement as shown below −

CREATE VIEW testView AS SELECT * FROM CUSTOMERS;

You can verify the list of all the views using the following query −

SHOW FULL TABLES WHERE table_type = 'VIEW';

The view will be created as follows −

Tables_in_sample Table_type
testview VIEW

Dropping a View −

Following query drops the view created above −

DROP VIEW testView;

Verification

To verify if we have deleted the view or not, display the list of views using the query below −

SHOW FULL TABLES WHERE table_type = 'VIEW';

As the view is dropped, an empty set is returned.

Empty set (0.12 sec)

The IF EXISTS clause

If you try to drop a view that doesn't exist, an error will be generated. Let us see an example where we are dropping a view named NEW using the following query −

DROP VIEW NEW;

The following error is displayed (where 'tutorialspoint' is the database name) −

ERROR 1051 (42S02): Unknown table 'tutorialspoint.new'

However, if you use the IF EXISTS clause along with the DROP VIEW statement as shown below, the query will be ignored even if a VIEW with the given name does not exist.

DROP VIEW IF EXISTS NEW;

Deleting Rows from a View

Instead of removing an entire view, we can also drop selected rows of a view using the DELETE statement with a WHERE clause.

Syntax

Following is the syntax of the DELETE statement −

DELETE FROM view_name WHERE condition;

Example

In this example, let us first create a testView on the CUSTOMERS table using the following query −

CREATE VIEW testView AS SELECT * FROM CUSTOMERS;

Now, using the following query, you can delete a record from the testView created on the CUSTOMERS table. The changes made to the data in view will finally be reflected in the base table CUSTOMERS.

DELETE FROM testView WHERE Location = 'Indore';

The associated table CUSTOMERS will have the following records −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00

Dropping View Using Client Program

In addition to drop a view from the MySQL database using the MySQL query, we can also perform the another operation on a table using a client program.

Syntax

Following are the syntaxes of the Drop View from MySQL in various programming languages −

The MySQL PHP connector mysqli provides a function named query() to execute the DROP VIEW query in the MySQL database.

$sql="DROP VIEW view_name";
$mysqli->query($sql);

The MySQL NodeJS connector mysql2 provides a function named query() to execute the DROP VIEW query in the MySQL database.

sql="DROP VIEW view_name";
con.query(sql);

We can use the JDBC type 4 driver to communicate to MySQL using Java. It provides a function named execute() to execute the DROP VIEW query in the MySQL database.

String sql = "DROP VIEW view_name";
statement.execute(sql);

The MySQL Connector/Python provides a function named execute() to execute the DROP VIEW query in the MySQL database.

drop_view_query = "DROP VIEW view_name"
cursorObj.execute(drop_view_query);

Example

Following are the implementations of this operation in various programming languages −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error); exit(); } // printf('Connected successfully.
'); // drop a view; $sql = "DROP VIEW first_view"; if ($mysqli->query($sql)) { printf("View dropped successfully!.
"); } if ($mysqli->errno) { printf("View could not be dropped!.
", $mysqli->error); } $mysqli->close();

Output

The output obtained is as follows −

View dropped successfully!.
var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "create database TUTORIALS"
  con.query(sql);

  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE dispatches_data(ProductName VARCHAR(255),CustomerName VARCHAR(255),DispatchTimeStamp timestamp,Price INT,Location VARCHAR(255));"
  con.query(sql);

  sql = "insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad'),('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam'),('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada'),('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai'),('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');"
  con.query(sql);

  //Creating Views
  sql = "CREATE VIEW testView AS SELECT * FROM dispatches_data;"
  con.query(sql);

  sql = "CREATE VIEW sample AS SELECT ProductName, Price FROM dispatches_data;"
  con.query(sql);

  sql = "CREATE VIEW demo AS SELECT * FROM dispatches_data WHERE Price>3000;"
  con.query(sql);

  //Displaying list of all views
  sql = "SHOW FULL TABLES WHERE table_type = 'VIEW';"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Views before deleting:**");
    console.log(result);
    console.log("--------------------------");
  });

  //Dropping views
  sql = "DROP VIEW demo;"
  con.query(sql);

  sql = "DROP VIEW sample;"
  con.query(sql);

  sql = "DROP VIEW testview;"
  con.query(sql);

  //retrieve the list of views 
  sql = "SHOW FULL TABLES WHERE table_type = 'VIEW';"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Views after deleting:**");
    console.log(result);
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
**Views before deleting:**
[
  { Tables_in_tutorials: 'demo', Table_type: 'VIEW' },
  { Tables_in_tutorials: 'sample', Table_type: 'VIEW' },
  { Tables_in_tutorials: 'testview', Table_type: 'VIEW' }
]
--------------------------
**Views after deleting:**
[]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DropView {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Drop a View.....
         String sql = "DROP VIEW tutorial_view";
         statement.executeUpdate(sql);
         System.out.println("View dropped Successfully...!");
         ResultSet resultSet = statement.executeQuery("SELECT * FROM tutorial_view");
         while (resultSet.next()) {
            System.out.print(resultSet.getString(1));
            System.out.println();
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
View dropped Successfully...!
java.sql.SQLSyntaxErrorException: Table 'tutorials.tutorial_view' doesn't exist
Python Program
import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
drop_view_query = "DROP VIEW tutorial_view"
cursorObj.execute(drop_view_query)
connection.commit()
print("View dropped successfully.")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

View dropped successfully.
Advertisements