MySQL - 更新查詢

要透過 Node.js 程式更新 MySQL 資料庫中表的記錄，我們需要使用mysql2庫的query()函式執行UPDATE語句，如下所示：

sql = "UPDATE table_name SET column1 = value1,
   column2 = value2, ... WHERE condition";
con.query(sql);

要透過 Java 程式更新 MySQL 資料庫中表的記錄，我們需要使用JDBC函式executeUpdate()執行UPDATE語句，如下所示：

String sql="UPDATE table_name SET field1 = new-value1,
   field2 = new-value2 [WHERE Clause]";
statement.executeUpdate(sql);

要透過 Python 程式更新 MySQL 資料庫中表的記錄，我們需要使用 MySQL Connector/Python 的execute()函式執行UPDATE語句，如下所示：

update_query = "UPDATE table_name SET field1 = new-value1,
   field2 = new-value2 [WHERE Clause]"
cursorObj.execute(update_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!");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Updating a single record in the table
  sql = "UPDATE tutorials_tbl SET tutorial_title = 'Learning Java' WHERE tutorial_id = 3;"
  con.query(sql);

  //Selecting records from table
  sql = "SELECT * FROM tutorials_tbl"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
  });
});

輸出

產生的輸出如下：

Connected!
[
  {
    tutorial_id: 1,
    tutorial_title: 'Learn PHP',
    tutorial_author: 'John Paul',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 2,
    tutorial_title: 'Learn MySQL',
    tutorial_author: 'Abdul S',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 3,
    tutorial_title: 'Learning Java',
    tutorial_author: 'Sanjay',
    submission_date: 2007-05-05T18:30:00.000Z
  },
  {
    tutorial_id: 4,
    tutorial_title: 'Python Tutorial',
    tutorial_author: 'Sasha Lee',
    submission_date: 2016-09-03T18:30:00.000Z
  },
  {
    tutorial_id: 5,
    tutorial_title: 'Hadoop Tutorial',
    tutorial_author: 'Chris Welsh',
    submission_date: 2023-07-25T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class UpdateQuery {
	public static void main(String[] args) {
		String url = "jdbc:mysql://:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "UPDATE tutorials_tbl SET tutorial_title = 'Learning Java' WHERE tutorial_id = 3";
            st.executeUpdate(sql);
            System.out.println("Update query executed successfully..!");
            String sql1 = "SELECT * FROM tutorials_tbl";
            rs = st.executeQuery(sql1);
            System.out.println("Table records: ");
            while(rs.next()) {
            	String tutorial_id = rs.getString("tutorial_id");
            	String tutorial_title = rs.getString("tutorial_title");
            	String tutorial_author = rs.getString("tutorial_author");
            	String submission_date = rs.getString("submission_date");
            	System.out.println("Id: " + tutorial_id + ", Title: " + tutorial_title + ", Author: " +  tutorial_author + ", Submission_date: " + submission_date);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

輸出

獲得的輸出如下所示：

Update query executed successfully..!
Table records: 
Id: 1, Title: Learn PHP, Author: John Paul, Submission_date: 2023-08-08
Id: 2, Title: Angular Java, Author: Abdul S, Submission_date: 2023-08-08
Id: 3, Title: Learning Java, Author: Sanjay, Submission_date: 2007-05-06
Id: 4, Title: Python Tutorial, Author: Sasha Lee, Submission_date: 2016-09-04
Id: 5, Title: Hadoop Tutorial, Author: Chris Welsh, Submission_date: 2023-08-08

import mysql.connector
import datetime
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
#Creating a cursor object 
cursorObj = connection.cursor()
update_query = "UPDATE tutorials_tbl SET tutorial_title = 'Learning Java' WHERE tutorial_id = 3"
cursorObj.execute(update_query)
connection.commit()
print("Row updated successfully.")
cursorObj.close()
connection.close()

輸出

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

Row updated successfully.