通过 Node.js 程序从另一个数据库表派生表，我们需要使用 mysql2 库的 query() 函数执行 SELECT 语句，如下所示 −

sql ="SELECT column_name(s) FROM (subquery) AS derived_table_name";
con.query(sql);

通过 Java 程序从另一个数据库表派生表，我们需要使用 JDBC 函数 executeUpdate() 执行 SELECT 语句，如下所示 −

String sql="SELECT col_1, col_2 FROM table_name WHERE col_name IN (SELECT col_name FROM table_name)";
statement.executeQuery(sql);

通过 Python 程序从另一个数据库表派生表，我们需要使用 MySQL Connector/Python 的 execute() 函数执行 SELECT 语句，如下所示 −

sql="SELECT col_1, col_2 FROM table_name WHERE col_name IN (SELECT col_name FROM table_name)";
cursorObj.execute(sql);

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 SAMPLE(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(25),SALARY DECIMAL(18, 2),PRIMARY KEY (ID));"
  con.query(sql);

  sql = "INSERT INTO SAMPLE 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 = "SELECT ID, NAME, SALARY FROM (SELECT * FROM SAMPLE) AS DERIVED_SAMPLE;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

输出

产生的输出如下所示 −

Connected!
--------------------------
[
  { ID: 1, NAME: 'Ramesh', SALARY: '2000.00' },
  { ID: 2, NAME: 'Khilan', SALARY: '1500.00' },
  { ID: 3, NAME: 'kaushik', SALARY: '2000.00' },
  { ID: 4, NAME: 'Chaitali', SALARY: '6500.00' },
  { ID: 5, NAME: 'Hardik', SALARY: '8500.00' },
  { ID: 6, NAME: 'Komal', SALARY: '4500.00' },
  { ID: 7, NAME: 'Muffy', SALARY: '10000.00' }
]

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

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

        //锁定表....
        String sql = "SELECT tutorial_title, tutorial_author FROM tutorials_tbl WHERE tutorial_id IN (SELECT tutorial_id FROM tutorials_tbl)";
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println("Table derived successfully...!");
        while (resultSet.next()) {
            System.out.print(resultSet.getString(1)+ " " +resultSet.getString(2));
            System.out.println();
        }

        connection.close();
    } catch (Exception e) {
        System.out.println(e);
    }
}
}

输出

获得的输出如下所示 −

Connected successfully...!
Table derived successfully...!
Learn PHP John Paul
Learn MySQL Abdul S
JAVA Tutorial Sanjay
Python Tutorial Sasha Lee
Hadoop Tutorial Chris Welsh

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'tutorials_tbl'
# 主查询，包含派生表（子查询）
main_query = """
SELECT d.tutorial_id, d.tutorial_title, d.tutorial_author, d.submission_date
FROM (
    SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
    FROM tutorials_tbl
    WHERE submission_date >= '2023-01-01'
) AS d
WHERE d.tutorial_author LIKE '%Paul%'
"""
cursorObj = connection.cursor()
cursorObj.execute(main_query)
result = cursorObj.fetchall()
print("Derived Table Result:")
for row in result:
    print(f"| {row[0]:<11} | {row[1]:<15} | {row[2]:<15} | {row[3]:<15} |")
cursorObj.close()
connection.close()

输出

以上代码的输出如下 −

Derived Table Result:
| 1           | Learn PHP       | John Paul       | <15 |