MySQL - WHERE 子句

要透過 Node.js 程式從表中獲取選擇性記錄，我們需要使用 **mysql2** 庫的 **query()** 函式執行 **SELECT** 語句，如下所示 -

sql= "SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2....." ;
Con.query(sql);

要透過 Java 程式從表中獲取選擇性記錄，我們需要使用 **JDBC** 函式 **executeUpdate()** 執行 **SELECT** 語句，如下所示 -

String sql = "SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2....";
statement.executeQuery(sql);

要透過 Python 程式從表中獲取選擇性記錄，我們需要使用 MySQL **Connector/Python** 的 **execute()** 函式執行 **SELECT** 語句，如下所示 -

where_clause_query = SELECT column1, column2, ... 
   FROM table_name WHERE condition;
cursorObj.execute(where_clause_query)

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.
');
$sql = "SELECT * FROM tutorials_tbl where tutorial_author = 'Sanjay'";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Id: %s, Title: %s, Author: %s, Date: %d 
", 
         $row["tutorial_id"], 
         $row["tutorial_title"], 
         $row["tutorial_author"],
         $row["submission_date"]);               
   }
} else {
   printf('No record found.
');
}
mysqli_free_result($result);
$mysqli->close();

輸出

產生的輸出如下所示 -

Connected!
--------------------------
[
  {
    tutorial_id: 3,
    tutorial_title: 'JAVA Tutorial',
    tutorial_author: 'Sanjay',
    submission_date: 2007-05-20T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class WhereClause {
	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 tutorials_tbl WHERE tutorial_author = 'Sanjay'";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()){
            	String Id = rs.getString("tutorial_id");
            	String Title = rs.getString("tutorial_title");
            	String Author = rs.getString("tutorial_author");
            	String Date = rs.getString("submission_date");
            	System.out.println("Id: " + Id + ", Title: " + Title + ", Author: " + Author + ", Submission-date: " + Date);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

輸出

獲得的輸出如下所示 -

Table records: 
Id: 3, Title: Learning Java, Author: Sanjay, Submission-date: 2007-05-06

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
where_clause_query = """SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl
WHERE tutorial_author = 'John Paul'
"""
cursorObj.execute(where_clause_query)
# Fetch all the matching rows
matching_rows = cursorObj.fetchall()
# Printing the matching rows
for row in matching_rows:
    print(row)
cursorObj.close()
connection.close()

輸出

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

(1, 'Learn PHP', 'John Paul', datetime.date(2023, 3, 28))