mysql

Khóa học miễn phí MySQL – Limit Clause nhận dự án làm có lương

Posted on by user

MySQL – Limit

Table of content

MySQL Limit Clause

The LIMIT clause in MySQL can be used to specify the number of records to return. This clause is mostly used when dealing with tables that have thousands of records. It accepts one or two arguments (offset or count). The values of both arguments should be either be positive integers or zero.

The offset of the first row starts from 0, not from 1 and the count of the first row starts from 1. Let us understand it better using the following picture:

Mysql-Limit

Assume the name of the above table is students. If we execute the above-mentioned query, we will get the output as Mahika, Aarohi, and Nikhil.

Syntax

Following is the generic syntax of MySQL Limit clause −

SELECT column1, column2, ... FROM table_name
LIMIT number;

Where, the LIMIT clause specifies the maximum number of rows from the table to return.

Example

The following example demonstrates the usage of the MySQL Limit query.

First of all, let us create a table named CUSTOMERS using the following query −

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)
);

Here, we are inserting 7 records into the above-created table using the following INSERT INTO statement −

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, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to fetch all the records present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
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 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Now, we are selecting the first four records from the CUSTOMERS table using the MySQL LIMIT clause in conjunct with SELECT statement −

SELECT * FROM CUSTOMERS LIMIT 4;

Output

As we can see the output below, it returned the first four rows from the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00

Example

In the following query, we are selecting rows from the CUSTOMERS table starting from the third row (offset 2) from then four rows −

SELECT * FROM CUSTOMERS LIMIT 2,4;

Output

When we execute the above query, the output is obtained as follows −

ID NAME AGE ADDRESS SALARY
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00

LIMIT with WHERE Clause

In MySQL, we can use the LIMIT clause along with the WHERE clause in a SELECT statement to specify the number of rows returned from the query based on the conditions.

Syntax

Following is the generic syntax −

SELECT column1, column2, ... FROM table_name
WHERE condition
LIMIT number;

Example

In the query below, we are selecting the first two rows from the CUSTOMERS table where the AGE is greater than 21 −

SELECT * FROM CUSTOMERS WHERE AGE > 21 LIMIT 2;

Output

On executing the above query, the output is displayed as follows −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00

Example

In the following query, we are selecting the next 3 records from the CUSTOMERS table starting from the 2nd record (off set) where the value of the AGE column is greater than 21:

SELECT * FROM CUSTOMERS WHERE AGE > 21 LIMIT 1,3;

Output

The output for the above query is produced as given below −

ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00

LIMIT with ORDER BY clause

The ORDER BY clause will sort the rows of a column in the specified order (ASC or DESC). In MySQL, we can use the LIMIT clause along with the ORDER BY clause to limit the number of rows returned in the sorted result set.

Syntax

Following is the syntax of LIMIT clause with WHERE clause in MySQL −

SELECT column1, column2, ... FROM table_name
ORDER BY column_name [ASC|DESC]
LIMIT number;

Example

In the below query, we are fetching all the records from the CUSTOMERS table and sorting the SALARY column in descending order. Then we are fetching 5 rows from the sorted result set.

SELECT * FROM CUSTOMERS
ORDER BY SALARY DESC
LIMIT 5;

Output

If we compile and run the above query, the result is produced as follows −

ID NAME AGE ADDRESS SALARY
7 Muffy 24 Indore 10000.00
5 Hardik 27 Bhopal 8500.00
4 Chaitali 25 Mumbai 6500.00
6 Komal 22 Hyderabad 4500.00
1 Ramesh 32 Ahmedabad 2000.00

Example

Here, we are selecting all the records from the table and sorting the SALARY column in ascending order. Then we are fetching rows from the sorted result set starting from the second row (offset 1) from then three rows −

SELECT * FROM CUSTOMERS
ORDER BY SALARY ASC
LIMIT 1,3;

Output

On executing the given query, the output is displayed as follows −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
3 Kaushik 23 Kota 2000.00
6 Komal 22 Hyderabad 4500.00

Limit Clause Using a Client Program

In addition to limiting the number of records returned from a table with a MySQL query, we can also use a client program to perform the LIMIT operation.

Syntax

Following are the syntaxes of this operation in various programming languages −

To limit the number of records returned from a table through a PHP program, we need to execute the ”SELECT” statement with ”LIMIT” clause using the mysqli function query() as follows −

$sql = "SELECT * FROM tutorials_tbl WHERE tutorial_title = ''Java Tutorial'' LIMIT 3";
$mysqli->query($sql);

To limit the number of records returned from a table through a Node.js program, we need to execute the ”SELECT” statement with ”LIMIT” clause using the query() function of the mysql2 library as follows −

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

To limit the number of records returned from a table through a Java program, we need to execute the ”SELECT” statement with ”LIMIT” clause using the JDBC function executeUpdate() as follows −

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

To limit the number of records returned from a table through a Python program, we need to execute the ”SELECT” statement with ”LIMIT” clause using the execute() function of the MySQL Connector/Python as follows −

limit_query = "SELECT * FROM tutorials_tbl LIMIT {limit_value}"
cursorObj.execute(limit_query)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT * FROM tutorials_tbl WHERE tutorial_title = ''Java Tutorial'' LIMIT 3";
if($result = $mysqli->query($sql)){
   printf("SELECT LIMIT statement executed successfully..!  ");
   printf("Records are(limit 3): ");
   while($row = mysqli_fetch_row($result)){
      print_r ($row);
   }
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

SELECT LIMIT statement executed successfully..!  Records are(limit 3): Array
(
    [0] => 1
    [1] => Java Tutorial
    [2] => new_author
    [3] =>
)
Array
(
    [0] => 3
    [1] => Java Tutorial
    [2] => newauther1
    [3] => 2023-12-20
)
Array
(
    [0] => 4
    [1] => Java Tutorial
    [2] => newauther2
    [3] => 2022-06-10
)  

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);
  });
});

Output

The output produced is as follows −

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();
    }
  }
}



Output


The output obtained is as shown below −


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()           



Output


Following is the output of the above code −


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))














	


 


Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương:   Khóa học lập trình nhận lương tại trung tâm Toidayhoc


	
	


	

	

		

			

				

									

			

			

				

					user