MySQL - LIMIT 子句

要限制透過 Node.js 程式從表中返回的記錄數量，我們需要使用mysql2庫的query()函式執行帶有 'LIMIT' 子句的 'SELECT' 語句，如下所示：

sql = "SELECT * FROM CUSTOMERS LIMIT 4";
con.query(sql);  

要限制透過 Java 程式從表中返回的記錄數量，我們需要使用JDBC函式executeUpdate()執行帶有 'LIMIT' 子句的 'SELECT' 語句，如下所示：

String sql = "SELECT * FROM CUSTOMERS LIMIT 4";
statement.executeQuery(sql);

要限制透過 Python 程式從表中返回的記錄數量，我們需要使用MySQL Connector/Python 的execute()函式執行帶有 'LIMIT' 子句的 'SELECT' 語句，如下所示：

limit_query = "SELECT * FROM tutorials_tbl LIMIT {limit_value}"
cursorObj.execute(limit_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!");

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

  //Creating a 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, '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);

  sql = "SELECT * FROM CUSTOMERS LIMIT 4"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
  });
});          

輸出

生成的輸出如下：

Connected!
[
  {
    ID: 1,
    NAME: 'Ramesh',
    AGE: 32,
    ADDRESS: 'Ahmedabad',
    SALARY: '2000.00'
  },
  {
    ID: 2,
    NAME: 'Khilan',
    AGE: 25,
    ADDRESS: 'Delhi',
    SALARY: '1500.00'
  },
  {
    ID: 3,
    NAME: 'kaushik',
    AGE: 23,
    ADDRESS: 'Kota',
    SALARY: '2000.00'
  },
  {
    ID: 4,
    NAME: 'Chaitali',
    AGE: 25,
    ADDRESS: 'Mumbai',
    SALARY: '6500.00'
  }
]       

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class LimitQuery {
  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 LIMIT 4";
            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: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00 

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
limit_value = 3
limit_query = f"SELECT * FROM tutorials_tbl LIMIT {limit_value}"
cursorObj.execute(limit_query)
result = cursorObj.fetchall()
print(f"First {limit_value} rows from Tutorial Table:")
for row in result:
    print(row)
cursorObj.close()
connection.close()           

輸出

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

First 3 rows from Tutorial Table:
(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))