MySQL - 臨時表

要透過 Node.js 程式在 MySQL 資料庫中建立臨時表，我們需要使用mysql2庫的query()函式執行建立臨時表語句，如下所示：

sql="CREATE temporary Table table_name(column_name, column_type, ...)";
con.query(sql);

要透過 Java 程式在 MySQL 資料庫中建立臨時表，我們需要使用JDBC函式executeUpdate()執行建立臨時表語句，如下所示：

String sql="CREATE temporary Table table_name(column_name, column_type, ...)";
statement.executeQuery(sql);

要透過 Python 程式在 MySQL 資料庫中建立臨時表，我們需要使用 MySQLConnector/Python的execute()函式執行建立臨時表語句，如下所示：

sql="CREATE temporary Table table_name(column_name, column_type, ...)";
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!");
  console.log("--------------------------");

  //Creating a Database
  sql = "CREATE DATABASE testdb"
  con.query(sql);

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

  //Creating table
  sql = "CREATE TEMPORARY TABLE SalesSummary (product_name VARCHAR(50) NOT NULL, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0);"
  con.query(sql);

  sql = "INSERT INTO SalesSummary(product_name, total_sales, avg_unit_price, total_units_sold)VALUES('cucumber', 100.25, 90, 2);"
  con.query(sql);

  sql = "SELECT * FROM SalesSummary;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**SalesSummary Table:**")
    console.log(result);
  });
});

輸出

產生的輸出如下：

Connected!
--------------------------
**SalesSummary Table:**
[
  {
    product_name: 'cucumber',
    total_sales: '100.25',
    avg_unit_price: '90.00',
    total_units_sold: 2
  }
]

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

public class TemporaryTable {
    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...!");

            //Create temporary table...!
            String sql = "CREATE TEMPORARY TABLE SalesSummary (product_name VARCHAR(50) NOT NULL, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 )";
            statement.executeUpdate(sql);
            System.out.println("Temporary table created successfully...!");

            ResultSet resultSet = statement.executeQuery("DESCRIBE SalesSummary");
            while(resultSet.next()) {
                System.out.print(resultSet.getNString(1));
                System.out.println();
            }
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

輸出

獲得的輸出如下所示：

Connected successfully...!
Temporary table created successfully...!
product_name
total_sales
avg_unit_price
total_units_sold

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'tutorials_tbl_temp'
#Creating a cursor object 
cursorObj = connection.cursor()
create_table_query = f"CREATE TEMPORARY TABLE {table_name} (NAME VARCHAR(50), ID INT)"
cursorObj.execute(create_table_query)
print(f"Temporary table '{table_name}' is created successfully.")
cursorObj.close()
connection.close()

輸出

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

Temporary table 'tutorials_tbl_temp' is created successfully.