MySQL - 子查詢

要透過 JavaScript 程式使用子查詢提取資料，我們需要使用mysql2 庫的query()函式執行“SELECT”語句，如下所示：

sql = "SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
con.query(sql);

要透過 Java 程式使用子查詢提取資料，我們需要使用JDBC 函式executeQuery()執行“SELECT”語句，如下所示：

String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
statement.executeQuery(sql);

要透過 Python 程式使用子查詢提取資料，我們需要使用MySQL Connector/Python的execute()函式執行“SELECT”語句，如下所示：

sub_query = "SELECT SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS)"
cursorObj.execute(sql)

NodeJS program
var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
 con.query(sql, function(err, result){
    console.log("Subquery executed successfully...!");
    console.log("Table records: ")
    if (err) throw err;
    console.log(result);
    });
});  

輸出

獲得的輸出如下所示：

Subquery executed successfully...!
Table records:
[
  { NAME: 'Chaitali', AGE: 25, ADDRESS: 'Mumbai', SALARY: '6500.00' },
  { NAME: 'Hardik', AGE: 27, ADDRESS: 'Bhopal', SALARY: '8500.00' },
  { NAME: 'Komal', AGE: 22, ADDRESS: 'Hyderabad', SALARY: '4500.00' },
  { NAME: 'Muffy', AGE: 24, ADDRESS: 'Indore', SALARY: '10000.00' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SubQuery {
  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...!");
            //create table
            String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("id");
              String name = rs.getString("name");
              String age = rs.getString("age");
              String address = rs.getString("address");
              String salary = rs.getString("salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

輸出

獲得的輸出如下所示：

Table records: 
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: Hyderabad, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# Subquery to fetch the salaries of all customers whose ID is present in the same table
sub_query = f"""
SELECT SALARY FROM CUSTOMERS
WHERE ID IN
(SELECT ID FROM CUSTOMERS);
"""
cursorObj.execute(sub_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

輸出

獲得的輸出如下所示：

(Decimal('2000.00'),)
(Decimal('1500.00'),)
(Decimal('2000.00'),)
(Decimal('6500.00'),)
(Decimal('8500.00'),)
(Decimal('4500.00'),)
(Decimal('10000.00'),)