MySQL - INSERT INTO Select 语句

要通过 Node.js 程序将数据从另一个 MySQL 表插入到另一个 MySQL 表中，我们需要使用 mysql2 库的 query() 函数执行"INSERT INTO SELECT"语句，如下所示 -

sql = "INSERT INTO Agentdemo(ID, NAME, GENDER, AGE) SELECT ID, NAME, GENDER, AGE FROM Agent WHERE GENDER = 'Male'";
con.query(sql);

要通过 Java 程序将数据从另一个表插入到一个 MySQL 表中，我们需要使用 JDBC 函数 executeUpdate() 执行"INSERT INTO SELECT"语句，如下所示 -

String sql = "INSERT INTO Agentdemo(ID, NAME, GENDER, AGE) SELECT ID, NAME, GENDER, AGE FROM Agent WHERE GENDER = \"Male\"";
st.executeUpdate(sql);

要通过 Python 程序将数据从另一个表插入到一个 MySQL 表中，我们需要使用 MySQL Connector/Python 的 execute() 函数执行"INSERT INTO SELECT"语句，如下所示 -

sql = "INSERT INTO new_tutorials_tbl
(tutorial_id, tutorial_title, tutorial_author, submission_date)
SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl"
insert_into_select_query = sql
cursorObj.execute(insert_into_select_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 = "USE TUTORIALS"
  con.query(sql);
 sql = "CREATE TABLE Agent (ID int NOT NULL,NAME varchar(20) NOT NULL,GENDER varchar(20) NOT NULL,AGE int NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);
 sql = "INSERT INTO Agent VALUES (1,'Msd', 'Male', 21), (2,'Virat', 'Male', 23), (3,'Perry', 'Female', 24), (4,'Smiti', 'Female', 18), (5,'Rose', 'Female', 23);"
  con.query(sql);
 sql = "SELECT * FROM Agent;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
    console.log("-------------------------------------------------------");
  });
 sql = "CREATE TABLE Agentdemo(ID int NOT NULL,NAME varchar(50) NOT NULL,GENDER varchar(20) NOT NULL,AGE int NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);
 //将所需数据从一个表插入到另一个表
  sql = "INSERT INTO Agentdemo(ID, NAME, GENDER, AGE) SELECT ID, NAME, GENDER, AGE FROM Agent WHERE GENDER = 'Male'";
  con.query(sql);
 sql = "SELECT * FROM Agentdemo;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});        

输出

生成的输出如下 -

Connected!
----------------------------------------
[
  { ID: 1, NAME: 'Msd', GENDER: 'Male', AGE: 21 },
  { ID: 2, NAME: 'Virat', GENDER: 'Male', AGE: 23 },
  { ID: 3, NAME: 'Perry', GENDER: 'Female', AGE: 24 },
  { ID: 4, NAME: 'Smiti', GENDER: 'Female', AGE: 18 },
  { ID: 5, NAME: 'Rose', GENDER: 'Female', AGE: 23 }
]
-------------------------------------------------------
[
  { ID: 1, NAME: 'Msd', GENDER: 'Male', AGE: 21 },
  { ID: 2, NAME: 'Virat', GENDER: 'Male', AGE: 23 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class InsertIntoSelect {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost: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 = "SELECT * FROM Agent";
            rs = st.executeQuery(sql);
            System.out.println("Agent table records: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String gender = rs.getString("Gender");
              String age = rs.getString("Age");
              System.out.println("Id: " + id + ", Name: " + name + "Gender: " + gender + ", Age: " + age);
            }
            String sql1 = "SELECT * FROM Agentdemo";
            rs = st.executeQuery(sql1);
            System.out.println("Agentdemo table before after insert into select: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String gender = rs.getString("Gender");
              String age = rs.getString("Age");
              System.out.println("Id: " + id + ", Name: " + name + "Gender: " + gender + ", Age: " + age);
            }
            //now let use the insert into select query
            String sql2 = "INSERT INTO Agentdemo(ID, NAME, GENDER, AGE) SELECT ID, NAME, GENDER, AGE FROM Agent WHERE GENDER = \"Male\"";
            st.executeUpdate(sql2);
            String sql3 = "SELECT * FROM Agentdemo";
            rs = st.executeQuery(sql3);
            System.out.println("Agentdemo table after after insert into select: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String gender = rs.getString("Gender");
              String age = rs.getString("Age");
              System.out.println("Id: " + id + ", Name: " + name + "Gender: " + gender + ", Age: " + age);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}        

输出

获得的输出如下所示 -

Agent table records: 
Id: 1, Name: MsdGender: Male, Age: 21
Id: 2, Name: ViratGender: Male, Age: 23
Id: 3, Name: PerryGender: Female, Age: 24
Id: 4, Name: SmitiGender: Female, Age: 18
Id: 5, Name: RoseGender: Female, Age: 23
Agentdemo table before after insert into select: 
Agentdemo table after after insert into select: 
Id: 1, Name: MsdGender: Male, Age: 21
Id: 2, Name: ViratGender: Male, Age: 23

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
insert_into_select_query = "INSERT INTO new_tutorials_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date) SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl"
cursorObj.execute(insert_into_select_query)
connection.commit()
print("Data inserted into new table successfully.")
cursorObj.close()
connection.close()        

输出

以下是上述代码的输出 -

Data inserted into new table successfully.