MySQL - 刪除檢視

MySQL NodeJS 聯結器 **mysql2** 提供了一個名為 **query()** 的函式，用於在 MySQL 資料庫中執行 DROP VIEW 查詢。

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

我們可以使用 **JDBC 型別 4** 驅動程式使用 Java 與 MySQL 通訊。它提供了一個名為 **execute()** 的函式，用於在 MySQL 資料庫中執行 DROP VIEW 查詢。

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

**MySQL Connector/Python** 提供了一個名為 **execute()** 的函式，用於在 MySQL 資料庫中執行 DROP VIEW 查詢。

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

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);
  });
});

輸出

生成的輸出如下：

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://: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);
      }
   }
}

輸出

獲得的輸出如下所示：

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()

輸出

以下是上述程式碼的輸出：

View dropped successfully.