MySQL - 更新视图

要通过 JavaScript 程序更新 MySQL 数据库中的视图，我们需要使用 mysql2 库的 query() 函数执行 UPDATE 或 ALTER 语句，如下所示 -

sql = "UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = 'Ramesh'";
con.query(sql);

要通过 Java 程序更新 MySQL 数据库中的视图，我们需要使用 JDBC 函数 executeUpdate() 执行 UPDATE 或 ALTER 语句，如下所示 -

String sql = "ALTER VIEW first_view AS select tutorial_id, tutorial_title, tutorial_author from tutorials_tbl where tutorial_id = 1";
st.execute(sql);

要通过 Python 程序更新 MySQL 数据库中的视图，我们需要使用 MySQL Connector/Python 的 execute() 函数执行 UPDATE 或 ALTER 语句，如下所示 -

update_view_query = "UPDATE tutorial_view SET tutorial_title = 'New Title' WHERE tutorial_id = 2"
cursorObj.execute(update_view_query)

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});
 //连接到 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 CUSTOMERS(ID int NOT NULL, NAME varchar(20) NOT NULL, AGE int NOT NULL, ADDRESS varchar(25), SALARY decimal(18, 2), PRIMARY KEY (ID) );"
  con.query(sql);
 sql = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (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, 'MP', 4500.00 ),(7, 'Muffy',24, 'Indore', 10000.00 );"
  con.query(sql);
 sql = "CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;"
  con.query(sql);
 //显示视图的内容
  sql = "SELECT * from CUSTOMERS_VIEW;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**CUSTOMERS_VIEW View:**");
    console.log(result);
    console.log("--------------------------");
  });
 sql = "UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = 'Ramesh'";
  con.query(sql);
 //检索基表
  sql = "SELECT * FROM CUSTOMERS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Base Table(CUSTOMERS):**");
    console.log(result);
  });
});  

输出

生成的输出如下 -

Connected!
--------------------------
**CUSTOMERS_VIEW View:**
[
  {ID: 1, NAME: 'Ramesh', AGE: 32, ADDRESS: 'Ahmedabad', SALARY: '2000.00'},
  {ID: 2, NAME: 'Khilan', AGE: 25, ADDRESS: 'Delhi', SALARY: '1500.00'},
  {ID: 3, NAME: 'kaushik', AGE: 23, ADDRESS: 'Kota', SALARY: '2000.00'},
  {ID: 4, NAME: 'Chaitali', AGE: 25, ADDRESS: 'Mumbai', SALARY: '6500.00'},
  {ID: 5, NAME: 'Hardik', AGE: 27, ADDRESS: 'Bhopal', SALARY: '8500.00'},
  {ID: 6, NAME: 'Komal', AGE: 22, ADDRESS: 'MP', SALARY: '4500.00' },
  {ID: 7, NAME: 'Muffy', AGE: 24, ADDRESS: 'Indore', SALARY: '10000.00'}
]
--------------------------
**Base Table(CUSTOMERS):**
[
  {ID: 1, NAME: 'Ramesh', AGE: 35, ADDRESS: 'Ahmedabad', SALARY: '2000.00'},
  {ID: 2, NAME: 'Khilan', AGE: 25, ADDRESS: 'Delhi', SALARY: '1500.00'},
  {ID: 3, NAME: 'kaushik', AGE: 23, ADDRESS: 'Kota', SALARY: '2000.00'},
  {ID: 4, NAME: 'Chaitali', AGE: 25, ADDRESS: 'Mumbai', SALARY: '6500.00'},
  {ID: 5, NAME: 'Hardik', AGE: 27, ADDRESS: 'Bhopal', SALARY: '8500.00'},
  {ID: 6, NAME: 'Komal', AGE: 22, ADDRESS: 'MP', SALARY: '4500.00' },
  {ID: 7, NAME: 'Muffy', AGE: 24, ADDRESS: 'Indore', SALARY: '10000.00'}
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class UpdateView {
   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...!");
        //Update Created View.....
         String sql = "ALTER VIEW first_view AS select tutorial_id, tutorial_title, tutorial_author from tutorials_tbl where tutorial_id = 1";
         statement.execute(sql);
         System.out.println("Created view updated Successfully...!");
         ResultSet resultSet = statement.executeQuery("SELECT * FROM first_view");
         while (resultSet.next()) {
            System.out.print(resultSet.getInt(1)+" "+ resultSet.getString(2)+ " "+ resultSet.getString(3));
            System.out.println();
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

输出

获得的输出如下所示 -

Connected successfully...!
Created view updated Successfully...!
1 Learn PHP John Paul

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
update_view_query = """
UPDATE tutorial_view
SET tutorial_title = 'New Title'
WHERE tutorial_id = 2
"""
cursorObj.execute(update_view_query)
connection.commit()
print("View updated successfully.")
cursorObj.close()
connection.close()               

输出

以下是上述代码的输出 -

View updated successfully.