MySQL - SELECT 查询

要通过 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"
});
 //连接到 MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
 //创建数据库
  sql = "CREATE DATABASE IF NOT EXISTS TUTORIALS"
  con.query(sql);
 //选择数据库
  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);
  //将记录插入表中
  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://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 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
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'tutorials_tbl'
#创建游标对象
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))