MySQL - ANY 運算子

要透過 Node.js 程式在 MySQL 表上執行 ANY 運算子，我們需要使用 mysql2 庫的 query() 函式執行包含 ANY 運算子的 SELECT 語句，如下所示：

sql= " SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name operator ANY (subquery)";  
con.query(sql);

要透過 Java 程式在 MySQL 表上執行 ANY 運算子，我們需要使用 JDBC 函式 executeUpdate() 執行包含 ANY 運算子的 SELECT 語句，如下所示：

String sql = "SELECT column_name1, column_name2, ... FROM table_name 
WHERE column_name operator ANY (subquery)";
statement.executeQuery(sql);

要透過 Python 程式在 MySQL 表上執行 ANY 運算子，我們需要使用 MySQL Connector/Python 的 execute() 函式執行包含 ANY 運算子的 SELECT 語句，如下所示：

any_query = SELECT column1, column2, ... FROM table_name 
WHERE column_name comparison_operator ANY (subquery);
cursorObj.execute(any_query);

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 TUTORIALS"
   con.query(sql);

   //Select database
   sql = "USE TUTORIALS"
   con.query(sql);

   //Creating table
   sql = "CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(25) NOT NULL,SALARY DECIMAL(18, 2),PRIMARY KEY(ID));"
   con.query(sql);

   //Inserting Records
   sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,'Ramesh', 32, 'Hyderabad',4000.00),(2,'Khilan', 25, 'Kerala', 8000.00),(3,'kaushik', 23, 'Hyderabad', 11000.00),(4,'Chaital', 25, 'Mumbai', 1200.00),(5,'Hardik', 27, 'Vishakapatnam', 10000.00),(6, 'Komal',29, 'Vishakapatnam', 7000.00),(7, 'Muffy',24, 'Delhi', 10000.00);"
   con.query(sql);

   //Using ANY Operator
   sql = "SELECT * FROM CUSTOMERS WHERE SALARY > ANY (SELECT SALARY FROM CUSTOMERS WHERE AGE = 29);"
   con.query(sql, function(err, result){
      if (err) throw err
      console.log(result)
   });
});      

輸出

生成的輸出如下所示：

Connected!
--------------------------
[
  {
    ID: 2,
    NAME: 'Khilan',
    AGE: 25,
    ADDRESS: 'Kerala',
    SALARY: '8000.00'
  },
  {
    ID: 3,
    NAME: 'kaushik',
    AGE: 23,
    ADDRESS: 'Hyderabad',
    SALARY: '11000.00'
  },
  {
    ID: 5,
    NAME: 'Hardik',
    AGE: 27,
    ADDRESS: 'Vishakapatnam',
    SALARY: '10000.00'
  },
  {
    ID: 7,
    NAME: 'Muffy',
    AGE: 24,
    ADDRESS: 'Delhi',
    SALARY: '10000.00'
  }
]      

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class AnyOperator {
  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...!");
            String sql = "SELECT * FROM CUSTOMERS WHERE SALARY > ANY (SELECT SALARY FROM CUSTOMERS WHERE AGE = 30)";
            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 + ", Addresss: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}                                    

輸出

獲得的輸出如下所示：

Table records: 
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 30, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 30, Addresss: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Addresss: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Addresss: 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 = """SELECT SALARY FROM CUSTOMERS WHERE AGE = 29"""
any_query = f"""SELECT * FROM CUSTOMERS
WHERE SALARY > ANY ({subquery})"""
cursorObj.execute(any_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
   print(row)
cursorObj.close()
connection.close()                                 

輸出

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

(2, 'Khilan', 25, 'Kerala', Decimal('8000.00'))
(3, 'kaushik', 23, 'Hyderabad', Decimal('11000.00'))
(5, 'Hardik', 27, 'Vishakapatnam', Decimal('10000.00'))
(7, 'Muffy', 24, 'Delhi', Decimal('10000.00'))