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://127.0.0.1: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))