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"
});
 //连接到 MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
 //选择数据库
  sql = "USE TUTORIALS"
  con.query(sql);
 //创建一个表
  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);
 //插入记录
  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://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 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
#建立连接
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))