MySQL - IN 运算符

要通过 JavaScript 程序在 MySQL 表中使用 IN 运算符，我们需要使用 mysql2 连接器提供的名为 query() 的函数执行带有 IN 的 SQL 语句 -

sql= "SELECT columns FROM table_name
WHERE column_name IN (value1, value2, value3, ...)";
con.query(sql);

要通过 Java 程序在 MySQL 表中使用 IN 运算符，我们需要使用 JDBC type 4 驱动程序提供的名为 executeQuery() 的函数执行带有 IN 的 SQL 语句 -

String sql = "SELECT COLUMN(S) FROM TABLE_NAME
WHERE COLUMN_NAME IN(VALUE1, VALUE2, VALUE3,...)";
statement.executeQuery(sql);

要通过 Python 程序在 MySQL 表中使用 IN 运算符，我们需要使用 MySQL Connector/Python 提供的名为 execute() 的函数执行带有 IN 的 SQL 语句 -

in_query = "SELECT column1, column2, ... FROM table_name
WHERE column_name IN (value1, value2, value3, ...)"
cursorObj.execute(in_query);

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 = "create database TUTORIALS"
  con.query(sql);
 //选择数据库
  sql = "USE TUTORIALS"
  con.query(sql);
 //创建表
  sql = "CREATE TABLE IF NOT EXISTS employee_tbl(id INT NOT NULL, name VARCHAR(100) NOT NULL, work_date DATE, daily_typing_pages INT);"
  con.query(sql);
 //插入记录
  sql = "INSERT INTO employee_tbl(id, name, work_date, daily_typing_pages) VALUES(1, 'John', '2007-01-24', 250), (2, 'Ram', '2007-05-27', 220), (3, 'Jack', '2007-05-06', 170), (3, 'Jack', '2007-04-06', 100), (4, 'Jill', '2007-04-06', 220),(5, 'Zara', '2007-06-06', 300),(5, 'Zara', '2007-02-06', 350);"
  con.query(sql);
 //使用 IN 运算符
  sql = "SELECT * FROM employee_tbl WHERE daily_typing_pages IN ( 250, 220, 170 );"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});    

输出

生成的输出如下 -

Connected!
--------------------------
[
  {
    id: 1,
    name: 'John',
    work_date: 2007-01-23T18:30:00.000Z,
    daily_typing_pages: 250
  },
  {
    id: 2,
    name: 'Ram',
    work_date: 2007-05-26T18:30:00.000Z,
    daily_typing_pages: 220
  },
  {
    id: 3,
    name: 'Jack',
    work_date: 2007-05-05T18:30:00.000Z,
    daily_typing_pages: 170
  },
  {
    id: 4,
    name: 'Jill',
    work_date: 2007-04-05T18:30:00.000Z,
    daily_typing_pages: 220
  }
]      

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class InOperator {
    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...!");
            String sql = "CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID))";
            st.execute(sql);
            System.out.println("Table created successfully...!");
            //现在让我们插入一些记录
            String sql1 = "INSERT INTO CUSTOMERS 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 )";
            st.execute(sql1);
            System.out.println("Records inserted successfully....!");
            //显示记录
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Table records before update: ");
            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);
            }
            //让使用 IN 运算符和 where 子句来更新记录
            String sql3 = "UPDATE CUSTOMERS SET AGE = 30 WHERE AGE IN (25, 27)";
            st.executeUpdate(sql3);
            //显示更新后的表记录
            String sql4 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql4);
            System.out.println("Table records before update: ");
            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 created successfully...!
Records inserted successfully....!
Table records before update: 
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Addresss: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, 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
Table records before update: 
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 30, Addresss: Delhi, Salary: 1500.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
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象 
cursorObj = connection.cursor()
in_query = f"""SELECT * FROM customers
WHERE id IN (2, 4, 6)"""
cursorObj.execute(in_query)
# 获取符合条件的所有行
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()                                   

输出

以下是上述代码的输出 -

(2, 'Khilan', 25, 'Kerala', Decimal('8000.00'))
(4, 'Chaital', 25, 'Mumbai', Decimal('1200.00'))
(6, 'Komal', 29, 'Vishakapatnam', Decimal('7000.00'))