MySQL - 選擇查詢

要透過 Node.js 程式從 MySQL 表中獲取資料，我們需要使用 **mysql2** 庫的 **query()** 函式執行 SELECT 語句，如下所示：

sql="SELECT field1, field2,...fieldN FROM table_name";
con.query(sql);

要透過 Java 程式從 MySQL 表中獲取資料，我們需要使用 **JDBC** 函式 **executeUpdate()** 執行 SELECT 語句，如下所示：

String sql="SELECT COLUMN_NAME1, COLUMN_NAME2,... FROM TABLE_NAME";
statement.executeQuery(sql);

要透過 Python 程式從 MySQL 表中獲取資料，我們需要使用 MySQL **Connector/Python** 的 **execute()** 函式執行 SELECT 語句，如下所示：

select_query = "SELECT COLUMN1, COLUMN2,.. FROM TABLE_NAME";
cursorObj.execute(select_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!");

  //Creating a Database
  sql = "CREATE DATABASE IF NOT EXISTS TUTORIALS"
  con.query(sql);

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating a Table
  sql = "CREATE TABLE IF NOT EXISTS tutorials_tbl(tutorial_id INT NOT NULL PRIMARY KEY, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE)"
  con.query(sql);
  //Inserting records into table
  sql = "INSERT INTO tutorials_tbl(tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES(1, 'Learn PHP', 'John Paul', NOW()), (2, 'Learn MySQL', 'Abdul S', NOW()), (3, 'JAVA Tutorial', 'Sanjay', '2007-05-06'), (4, 'Python Tutorial', 'Sasha Lee', '2016-09-04'), (5, 'Hadoop Tutorial', 'Chris Welsh', NOW())"
  con.query(sql);

  //Selecting all the records from table
  sql = "SELECT * FROM tutorials_tbl"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
  });
});

輸出

產生的輸出如下：

Connected!
[
  {
    tutorial_id: 1,
    tutorial_title: 'Learn PHP',
    tutorial_author: 'John Paul',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 2,
    tutorial_title: 'Learn MySQL',
    tutorial_author: 'Abdul S',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 3,
    tutorial_title: 'JAVA Tutorial',
    tutorial_author: 'Sanjay',
    submission_date: 2007-05-05T18:30:00.000Z
  },
  {
    tutorial_id: 4,
    tutorial_title: 'Python Tutorial',
    tutorial_author: 'Sasha Lee',
    submission_date: 2016-09-03T18:30:00.000Z
  },
  {
    tutorial_id: 5,
    tutorial_title: 'Hadoop Tutorial',
    tutorial_author: 'Chris Welsh',
    submission_date: 2023-07-25T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SelectQuery {
	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";
            rs = st.executeQuery(sql);
            System.out.println("Select query executed successfully..!");
            System.out.println("Table records: ");
            while(rs.next()) {
            	String tutorial_id = rs.getString("tutorial_id");
            	String tutorial_title = rs.getString("tutorial_title");
            	String tutorial_author = rs.getString("tutorial_author");
            	String submission_date = rs.getString("submission_date");
            	System.out.println("Id: " + tutorial_id + ", Title: " + tutorial_title + ", Author: " +  tutorial_author + ", Submission_date: " + submission_date);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

輸出

獲得的輸出如下所示：

Select query executed successfully..!
Table records: 
Id: 1, Title: Learn PHP, Author: John Paul, Submission_date: 2023-08-08

import mysql.connector
import datetime
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'tutorials_tbl'
#Creating a cursor object 
cursorObj = connection.cursor()
select_query = f"SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM {table_name}"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Tutorial Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

輸出

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

Derived Table Result:
Tutorial Table Data:
(1, 'Learn PHP', 'John Paul', datetime.date(2023, 3, 28))
(2, 'Learn MySQL', 'Abdul S', datetime.date(2023, 3, 28))
(3, 'JAVA Tutorial', 'Sanjay', datetime.date(2007, 5, 6))
(4, 'Python Tutorial', 'Sasha Lee', datetime.date(2016, 9, 4))
(5, 'Hadoop Tutorial', 'Chris Welsh', datetime.date(2023, 3, 28))
(6, 'R Tutorial', 'Vaishnav', datetime.date(2011, 11, 4))