MySQL - 插入查詢

要在 Node.js 程式中將資料插入 MySQL 表，我們需要使用mysql2庫的query函式執行 INSERT 語句，如下所示：

sql = "INSERT INTO tutorials_tbl VALUES(1, 'Learn PHP', 'John Paul', NOW())";
con.query(sql)

要在 Java 程式中將資料插入 MySQL 表，我們需要使用JDBC函式executeUpdate()執行 INSERT 語句，如下所示：

String sql = "INSERT INTO tutorials_tbl VALUES (1, 'Learn PHP', 'John Paul', NOW());";
st.executeUpdate(sql);

要在 Python 程式中將資料插入 MySQL 表，我們需要使用MySQL Connector/Python的execute()函式執行 INSERT 語句，如下所示：

sql = "INSERT INTO tutorials_tbl VALUES (1, 'Learn PHP', 'John Paul', '2023-3-28')"
cursorObj.execute(sql)

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!");
//Creating a Database
sql = "CREATE DATABASE IF NOT EXISTS TUTORIALS"
con.query(sql);
//Selecting a Database
sql = "USE TUTORIALS"
con.query(sql);
//Creating a Table
sql = "CREATE TABLE IF NOT EXISTS tutorials_tbl(tutorial_id INT NOT NULL PRIMARY KEY, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE)";
con.query(sql);
//Inserting records into table
sql = "INSERT INTO tutorials_tbl(tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES(1, 'Learn PHP', 'John Paul', NOW()), (2, 'Learn MySQL', 'Abdul S', NOW()), (3, 'JAVA Tutorial', 'Sanjay', '2007-05-06'), (4, 'Python Tutorial', 'Sasha Lee', '2016-09-04'), (5, 'Hadoop Tutorial', 'Chris Welsh', NOW())";
 con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
   });
});

輸出

生成的輸出如下：

Connected!
-----------------------------------
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 5,
  insertId: 0,
  info: 'Records: 5  Duplicates: 0  Warnings: 3',
  serverStatus: 2,
  warningStatus: 3,
  changedRows: 0
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class InsertQuery {
   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 = "INSERT INTO tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES (1, 'Learn PHP', 'John Paul', NOW());";
        st.executeUpdate(sql);
        System.out.println("Record insered successfully...!");
        System.out.println("Table records: ");
        String sql1 = "SELECT * FROM tutorials_tbl";
        rs = st.executeQuery(sql1);
        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();
     }
   }
}

輸出

獲得的輸出如下所示：

Record insered successfully...!
Table records: 
Id: 1, Title: Learn PHP, Author: John Paul, 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'
)
table_name = 'tutorials_tbl'
new_tutorial_data = [
    (2, 'Learn MySQL', 'Abdul S', '2023-03-28'),
    (3, 'JAVA Tutorial', 'Sanjay', '2007-05-06'),
    (4, 'Python Tutorial', 'Sasha Lee', '2016-09-04'),
    (5, 'Hadoop Tutorial', 'Chris Welsh', '2023-03-28'),
    (6, 'R Tutorial', 'Vaishnav', '2011-11-04')
]
#Creating a cursor object 
cursorObj = connection.cursor()
cursorObj.execute("truncate table tutorials_tbl")
sql = "INSERT INTO tutorials_tbl VALUES (1, 'Learn PHP', 'John Paul', '2023-3-28')"
cursorObj.execute(sql)
insert_query = f'INSERT INTO {table_name} (tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES (%s, %s, %s, %s)'
cursorObj.executemany(insert_query, new_tutorial_data)
connection.commit()
print("Row inserted successfully.")
cursorObj.close()
connection.close()

輸出

以上程式碼的輸出如下：

Row inserted successfully.