MySQL - MINUS 运算符

要通过 JavaScript 程序在 MySQL 中执行减号运算符，我们需要使用 mysql2 连接器提供的 query() 函数执行带有 JOIN 子句的 SQL 语句。

sql = "SELECT column_list FROM table_name1
LEFT JOIN table_name2 ON join_predecate
WHERE table_name2.column_name IS NULL";
con.query(sql);

要通过 Java 程序在 MySQL 中执行减号运算符，我们需要使用 JDBC type 4 驱动程序提供的 executeQuery() 函数执行带有 JOIN 子句的 SQL 语句。

String sql = "SELECT column_list FROM table_name1
LEFT JOIN table_name2 ON join_predecate
WHERE table_name2.column_name IS NULL";
statement.executeQuery(sql);

要通过 Python 程序在 MySQL 中执行减号运算符，我们需要使用 MySQL Connector/Python 提供的 execute() 函数执行带有 JOIN 子句的 SQL 语句。

query = "SELECT column_list FROM table_name1
LEFT JOIN table_name2 ON join_predecate
WHERE table_name2.column_name IS NULL"
cursorObj.execute(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);
 //创建 CUSTOMERS 表
  sql = "CREATE TABLE CUSTOMERS (CUST_ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(25) NOT NULL,SALARY DECIMAL(18, 2),PRIMARY KEY(CUST_ID));"
  con.query(sql);
 //插入记录
  sql = "INSERT INTO CUSTOMERS (CUST_ID,NAME,AGE,ADDRESS,SALARY) 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);
 //Creating ORDERS table
  sql = "CREATE TABLE ORDERS (OID INT NOT NULL,DATE VARCHAR(20) NOT NULL,CUST_ID INT NOT NULL,ADDRESS CHAR(25),AMOUNT INT NOT NULL);"
  con.query(sql);
  
  //插入记录
  sql = "INSERT INTO ORDERS(OID, DATE, CUST_ID, AMOUNT) VALUES(102, '2009-10-08 00:00:00', 3, 3000),(100, '2009-10-08 00:00:00', 3, 1500),(101, '2009-11-20 00:00:00' , 2, 1560),(103, '2008-05-20 00:00:00', 4, 2060);"
  con.query(sql);
 //Using MINUS Operator
  sql = "SELECT * FROM CUSTOMERS LEFT JOIN ORDERS USING (CUST_ID) WHERE ORDERS.CUST_ID IS NULL;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});         

输出

生成的输出如下 -

  Connected!
  --------------------------
[
  {
    CUST_ID: 1,
    NAME: 'Ramesh',
    AGE: 32,
    ADDRESS: null,
    SALARY: '2000.00',
    OID: null,
    DATE: null,
    AMOUNT: null
  },
  {
    CUST_ID: 5,
    NAME: 'Hardik',
    AGE: 27,
    ADDRESS: null,
    SALARY: '8500.00',
    OID: null,
    DATE: null,
    AMOUNT: null
  },
  {
    CUST_ID: 6,
    NAME: 'Komal',
    AGE: 22,
    ADDRESS: null,
    SALARY: '4500.00',
    OID: null,
    DATE: null,
    AMOUNT: null
  },
  {
    CUST_ID: 7,
    NAME: 'Muffy',
    AGE: 24,
    ADDRESS: null,
    SALARY: '10000.00',
    OID: null,
    DATE: null,
    AMOUNT: null
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class MinusOperator {
  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 = "SELECT * FROM CUSTOMERS LEFT JOIN ORDERS USING (CUST_ID) WHERE ORDERS.CUST_ID IS NULL";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("CUST_ID");
              String name = rs.getString("NAME");
              String age = rs.getString("AGE");
              String address = rs.getString("ADDRESS");
              String salary = rs.getString("SALARY");
              String oid = rs.getString("OID");
              String date = rs.getString("DATE");
              String address1 = rs.getString("ADDRESS");
              String amount = rs.getString("AMOUNT");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary + ", Oid: " + oid + ", Date: " + date + ", Address: " + address1 + ", Amount: " + amount);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}             

输出

获得的输出如下所示 -

Table records: 
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00, Oid: null, Date: null, Address: Ahmedabad, Amount: null
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00, Oid: null, Date: null, Address: Bhopal, Amount: null
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00, Oid: null, Date: null, Address: MP, Amount: null
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00, Oid: null, Date: null, Address: Indore, Amount: null   

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
query = f"""
SELECT * FROM CUSTOMERS
    LEFT JOIN ORDERS USING (CUST_ID)
    WHERE ORDERS.CUST_ID IS NULL;
"""
cursorObj.execute(query)
# 获取所有符合条件的行
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()                 

输出

以下是上述代码的输出 -

Table records:
CUST_ID 1, NAME Ramesh, ADDRESS , SALARY 2000.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
CUST_ID 5, NAME Hardik, ADDRESS , SALARY 8500.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
CUST_ID 6, NAME Komal, ADDRESS , SALARY 4500.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
CUST_ID 7, NAME Muffy, ADDRESS , SALARY 10000.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000