MySQL - AND 運算子

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

sql= " SELECT column1, column2, ..., columnN FROM table_name 3 
[WHERE condition1 AND condition2 AND condition3 ...]";   
con.query(sql);

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

String sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME 
[WHERE CONDITION1 AND CONDITION2 AND CONDITION3...]";  
statement.executeQuery(sql);

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

and_query = "SELECT column1, column2, ... FROM table_name 
WHERE condition1 AND condition2 AND ..."
cursorObj.execute(and_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), 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', 2000.00),(2,'Khilan', 25, 'Delhi', 1500.00),(3,'kaushik', 23, 'Hyderabad', 2000.00),(4,'Chaital', 25, 'Mumbai', 6500.00),(5,'Hardik', 27, 'Vishakapatnam', 8500.00),(6, 'Komal',22, 'Vishakapatnam', 4500.00),(7, 'Muffy',24, 'Indore', 10000.00);"
  con.query(sql);

  //Using HAVING Clause
  sql = "SELECT * FROM CUSTOMERS WHERE ADDRESS = 'Vishakapatnam' AND AGE = 27;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});        

輸出

產生的輸出如下所示 -

Connected!
--------------------------
[
  {
    ID: 5,
    NAME: 'Hardik',
    AGE: 27,
    ADDRESS: 'Vishakapatnam',
    SALARY: '8500.00'
  }
]       

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Andoperator {
  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 ADDRESS = \"Vishakapatnam\" AND AGE = 27";
            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: 5, Name: Hardik, Age: 27, Address: Vishakapatnam, Salary: 8500.00          

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object 
cursorObj = connection.cursor()
# Query to retrieve tutorials
and_query = f"""SELECT * FROM CUSTOMERS 
WHERE ADDRESS = "Vishakapatnam" AND AGE = 27"""
cursorObj.execute(and_query)
# Fetching all rows that meet the criteria
filtered_rows = cursorObj.fetchall()
# Print the filtered rows
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()                                    

輸出

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

(5, 'Hardik', 27, 'Vishakapatnam', Decimal('10000.00'))