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"
});
//连接到 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://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...!");
            //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
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# 子查询获取 ID 存在于同一个表中的所有客户的工资
sub_query = f"""
SELECT SALARY FROM CUSTOMERS
WHERE ID IN
(SELECT ID FROM CUSTOMERS);
"""
cursorObj.execute(sub_query)
# 获取所有符合条件的行
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'),)