Single-table queries

This topic describes how to use SQL statements to perform single-table queries in OceanBase Cloud.

Prerequisites

• You have connected to a MySQL-compatible tenant of OceanBase Cloud.
• You have the SELECT privilege. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Manage tenant accounts.

Syntax

Use the SELECT statement to query data.

The general syntax of a single-table query using the SELECT statement is as follows:

SELECT [ALL | DISTINCT | UNIQUE | SQL_CALC_FOUND_ROWS] select_list
FROM table_name
[ WHERE query_condition ]
[ GROUP BY group_by_condition ]
[ HAVING group_condition ]
[ ORDER BY column_list ][ASC | DESC]
[ LIMIT limit_clause ]

column_list:
 column_name[,column_name...]

Parameters

Execution order of keywords in the SELECT statement

If you use the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT clauses in a query, these clauses are executed in strict accordance with the following sequence:

1. The FROM clause is executed to find the required table.

2. The WHERE clause is executed to specify conditions.

3. The GROUP BY clause is executed to group or aggregate records. If GROUP BY is not executed, all records are considered a group.

4. The HAVING clause is executed to filter the grouped results.

5. The SELECT clause is executed.

6. The DISTINCT clause is executed to remove duplicate rows.

7. The ORDER BY clause is executed to sort the results in ascending or descending order.

8. The LIMIT clause is executed to limit the number of records.

Create test tables and add test data to the tables

1. Create a table named student.

   CREATE TABLE student (
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(20) NOT NULL,
     gender TINYINT NOT NULL,
     age INT NOT NULL,
     score FLOAT NOT NULL,
     enrollment_date DATE NOT NULL,
     notes VARCHAR(50)
   );
   

2. Insert 10 records into the student table.

   INSERT INTO student (name, gender, age, score, enrollment_date, notes)
     VALUES ('Emma', 0, 20, 85.0, '2021-09-01',NULL),
     ('William', 1, 21, 90.5, '2021-09-02','B'),
     ('Olivia', 0, 19, 95.5, '2021-09-03','A'),
     ('James', 1, 20, 87.5, '2021-09-03',NULL),
     ('Sophia', 0, 20, 91.5, '2021-09-05','B'),
     ('Benjamin', 1, 21, 96.5, '2021-09-01','A'),
     ('Ava', 0, 22, 89.5, '2021-09-06',NULL),
     ('Michael', 1, 18, 93.5, '2021-09-08','B'),
     ('Charlotte', 1, 19, 88.0, '2021-09-06',NULL),
     ('Ethan', 1, 20, 92.0, '2021-09-01','B');
   

3. Create a table named fruit_order.

   CREATE TABLE fruit_order(
     order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Order ID',
     user_id BIGINT NOT NULL COMMENT 'Customer ID',
     user_name VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'Customer name',
     fruit_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Order amount',
     order_year SMALLINT NOT NULL COMMENT 'Year of order placement'
   ) COMMENT 'Order table';
   

4. Insert 10 records into the fruit_order table.

   INSERT INTO fruit_order(user_id, user_name,fruit_price,order_year)
     VALUES (1011,'A1',13.11,'2019'),
     (1011,'A1',22.21,'2020'),
     (1011,'A1',58.83,'2020'),
     (1022,'B2',23.34,'2019'),
     (1022,'B2',12.22,'2019'),
     (1022,'B2',14.66,'2021'),
     (1022,'B2',34.44,'2021'),
     (1033,'C3',51.55,'2020'),
     (1033,'C3',63.66,'2021'),
     (1034,'D4',53.62,'2021');
   

Basic queries

When you use SELECT, we recommend that you use meaningful column aliases and properly organize the columns to improve the readability and organization of the result set, making it easier to understand the query results.

Query all columns

• You can execute the SELECT * FROM student; statement to query all student information.

• You can also execute the SELECT id,name,gender,age,score,enrollment_date FROM student; statement to query all student information.

Example 1: Query the data of all rows in the student table.

SELECT id, name, gender, age, score, enrollment_date, notes
FROM student;

or

SELECT * FROM student;

The return result is as follows:

+----+-----------+--------+-----+-------+-----------------+-------+
| id | name      | gender | age | score | enrollment_date | notes |
+----+-----------+--------+-----+-------+-----------------+-------+
|  1 | Emma      |      0 |  20 |    85 | 2021-09-01      | NULL  |
|  2 | William   |      1 |  21 |  90.5 | 2021-09-02      | B     |
|  3 | Olivia    |      0 |  19 |  95.5 | 2021-09-03      | A     |
|  4 | James     |      1 |  20 |  87.5 | 2021-09-03      | NULL  |
|  5 | Sophia    |      0 |  20 |  91.5 | 2021-09-05      | B     |
|  6 | Benjamin  |      1 |  21 |  96.5 | 2021-09-01      | A     |
|  7 | Ava       |      0 |  22 |  89.5 | 2021-09-06      | NULL  |
|  8 | Michael   |      1 |  18 |  93.5 | 2021-09-08      | B     |
|  9 | Charlotte |      1 |  19 |    88 | 2021-09-06      | NULL  |
| 10 | Ethan     |      1 |  20 |    92 | 2021-09-01      | B     |
+----+-----------+--------+-----+-------+-----------------+-------+
10 rows in set

Query specified columns

You can query data in specified columns based on column names.

Example 2: Query the data of all rows in the student table and return the data of the id and name columns in each row.

SELECT id, name
FROM student;

The return result is as follows:

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Emma      |
|  2 | William   |
|  3 | Olivia    |
|  4 | James     |
|  5 | Sophia    |
|  6 | Benjamin  |
|  7 | Ava       |
|  8 | Michael   |
|  9 | Charlotte |
| 10 | Ethan     |
+----+-----------+
10 rows in set

Query calculated values and specify column aliases

You can calculate the data of specified columns in a query.

Example 3: Select data from the id, name, age, and age+5 columns of the student table, and specify the alias age_plus_5 for the age+5 column that stores the calculation result.

SELECT id, name, age, age+5 AS age_plus_5
FROM student;

The return result is as follows:

+----+-----------+-----+------------+
| id | name      | age | age_plus_5 |
+----+-----------+-----+------------+
|  1 | Emma      |  20 |         25 |
|  2 | William   |  21 |         26 |
|  3 | Olivia    |  19 |         24 |
|  4 | James     |  20 |         25 |
|  5 | Sophia    |  20 |         25 |
|  6 | Benjamin  |  21 |         26 |
|  7 | Ava       |  22 |         27 |
|  8 | Michael   |  18 |         23 |
|  9 | Charlotte |  19 |         24 |
| 10 | Ethan     |  20 |         25 |
+----+-----------+-----+------------+
10 rows in set

Data filtering

You can add a WHERE clause to the SELECT statement to query data that meets the specified conditions. The WHERE clause can contain one or more conditions for filtering data. Only data that meets the WHERE conditions will be returned. You can flexibly use query conditions based on specific requirements to filter and retrieve target data.

When you use the WHERE clause, make sure that the conditions are correct and appropriate operators are used.

The following table lists general query conditions specified by the WHERE clause.

Queries with comparison conditions

Equal to

The equal to operator (=) returns data equal to the target value in the specified column. If the value is a string, it needs to be enclosed in single quotation marks (' ') or double quotation marks (" ").

Example 4: Query all rows where gender values are equal to 1 in the student table, and return the data of the id, name, and gender columns in the rows.

SELECT id, name, gender
FROM student
WHERE gender = 1;    

The return result is as follows:

+----+-----------+--------+
| id | name      | gender |
+----+-----------+--------+
|  2 | William   |      1 |
|  4 | James     |      1 |
|  6 | Benjamin  |      1 |
|  8 | Michael   |      1 |
|  9 | Charlotte |      1 |
| 10 | Ethan     |      1 |
+----+-----------+--------+
6 rows in set

Not equal to

The not equal to operator includes two expressions: <> and !=.

Example 5: Query all rows where gender values are not equal to 1 in the student table, and return the data of the id, name, and gender columns in the rows.

SELECT id, name, gender
FROM student
WHERE gender <> 1;

The return result is as follows:

+----+--------+--------+
| id | name   | gender |
+----+--------+--------+
|  1 | Emma   |      0 |
|  3 | Olivia |      0 |
|  5 | Sophia |      0 |
|  7 | Ava    |      0 |
+----+--------+--------+
4 rows in set

Greater than and less than

The greater than operator (>) and the less than operator (<) compare numbers based on their values. If characters are compared, they are converted into their respective ASCII codes, and then the ASCII codes are compared from left to right.

Example 6: Query all rows where score values are less than 90 in the student table, and return the data of the id, name, and score columns in the rows.

SELECT id, name, score
FROM student
WHERE score < 90;

The return result is as follows:

+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Emma      |    85 |
|  4 | James     |  87.5 |
|  7 | Ava       |  89.5 |
|  9 | Charlotte |    88 |
+----+-----------+-------+
4 rows in set

Queries with logical conditions

Logical query operators AND and OR support queries with multiple conditions.

AND

You can use the AND keyword to combine multiple conditions. Only data that meets all the conditions will be returned.

Example 7: Query all rows where gender values are equal to 1 and score values are less than or equal to 90 in the student table, and return the data of the id, name, gender, and score columns in the rows.

SELECT id, name, gender, score
FROM student
WHERE gender = 1 AND score <= 90;

The return result is as follows:

+----+-----------+--------+-------+
| id | name      | gender | score |
+----+-----------+--------+-------+
|  4 | James     |      1 |  87.5 |
|  9 | Charlotte |      1 |    88 |
+----+-----------+--------+-------+
2 rows in set

OR

You can use the OR keyword to concatenate multiple conditions. Data that meets any one of the conditions will be returned.

Example 8: Query all rows where gender values are equal to 1 or score values are less than 90 in the student table, and return the data of the id, name, gender, and score columns in the rows.

SELECT id, name, gender, score
FROM student
WHERE gender = 1 OR score < 90;

The return result is as follows:

+----+-----------+--------+-------+
| id | name      | gender | score |
+----+-----------+--------+-------+
|  1 | Emma      |      0 |    85 |
|  2 | William   |      1 |  90.5 |
|  4 | James     |      1 |  87.5 |
|  6 | Benjamin  |      1 |  96.5 |
|  7 | Ava       |      0 |  89.5 |
|  8 | Michael   |      1 |  93.5 |
|  9 | Charlotte |      1 |    88 |
| 10 | Ethan     |      1 |    92 |
+----+-----------+--------+-------+
8 rows in set

Fuzzy queries

You can use the LIKE predicate for fuzzy matching of strings.

The syntax means finding data that matches the corresponding column value with the pattern. The pattern can be a complete string or contain wildcards % and _, where:

• The underscore (_) exactly matches any character in the value.

• The percent sign (%) matches zero or multiple characters in the value, but cannot match NULL.

Example 9: Query all rows where name values contain am in the student table, and return the data of the id and name columns in the rows.

SELECT id, name
FROM student
WHERE name LIKE '%am%';

The return result is as follows:

+----+----------+
| id | name     |
+----+----------+
|  2 | William  |
|  4 | James    |
|  6 | Benjamin |
+----+----------+
3 rows in set

Range queries

The BETWEEN AND operator selects data between two values. These values can be numerals, literals, or dates.

Example 10: Query all rows where score values range from 85 to 90 in the student table, and return the data of the id, name, and score columns in the rows.

SELECT id, name, score
FROM student
WHERE score BETWEEN 85 AND 90;

The return result is as follows:

+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Emma      |    85 |
|  4 | James     |  87.5 |
|  7 | Ava       |  89.5 |
|  9 | Charlotte |    88 |
+----+-----------+-------+
4 rows in set

Queries with a specified set

The IN operator specifies multiple values in a WHERE clause. The values can be treated as a set. The IN operator returns data, in the specified column, that matches any value in the set. The NOT IN operator returns data, in the specified column, that does not match any value in the set.

Example 11: Query all rows where id values belong to the set of (1, 3, 5, 7) in the student table, and return the data of the id and name columns in the rows.

SELECT id, name
FROM student
WHERE id IN (1,3,5,7);

The return result is as follows:

+----+--------+
| id | name   |
+----+--------+
|  1 | Emma   |
|  3 | Olivia |
|  5 | Sophia |
|  7 | Ava    |
+----+--------+
4 rows in set

NULL value queries

Since you cannot obtain accurate results when you use comparison operators, LIKE, BETWEEN AND, IN, and NOT IN to query NULL values, we recommend that you use the dedicated query conditions IS NULL and IS NOT NULL for NULL value queries. You can also use the NULL-safe equal to operator (<=>) to compare both NULL values and normal numerals.

IS NULL

You can use the IS NULL condition to query the data where the value of the specified column is NULL.

Example 12: Query all rows whose notes values are NULL in the student table, and return the data of the id, name, score, and notes columns in the rows.

SELECT id, name, score, notes
FROM student
WHERE notes IS NULL;

The return result is as follows:

+----+-----------+-------+-------+
| id | name      | score | notes |
+----+-----------+-------+-------+
|  1 | Emma      |    85 | NULL  |
|  4 | James     |  87.5 | NULL  |
|  7 | Ava       |  89.5 | NULL  |
|  9 | Charlotte |    88 | NULL  |
+----+-----------+-------+-------+
4 rows in set

IS NOT NULL

You can use the IS NOT NULL condition to query the data where the value of the specified column is not NULL.

Example 13: Query all rows whose notes values are not NULL in the student table, and return the data of the id, name, score, and notes columns in the rows.

SELECT id, name, score, notes
FROM student
WHERE notes IS NOT NULL;

The return result is as follows:

+----+----------+-------+-------+
| id | name     | score | notes |
+----+----------+-------+-------+
|  2 | William  |  90.5 | B     |
|  3 | Olivia   |  95.5 | A     |
|  5 | Sophia   |  91.5 | B     |
|  6 | Benjamin |  96.5 | A     |
|  8 | Michael  |  93.5 | B     |
| 10 | Ethan    |    92 | B     |
+----+----------+-------+-------+
6 rows in set

Data grouping

You can use the GROUP BY clause to group the query results in SQL queries. GROUP BY supports grouping by a single field or multiple fields. You can also use the WHERE clause to filter data before grouping, use the HAVING clause to filter data after grouping, and use the ORDER BY clause to sort data after grouping.

Considerations

• When you use the GROUP BY clause, make sure that the columns in the SELECT statement are either included in the GROUP BY clause or used as aggregate functions.
• When you use the HAVING clause, make sure that the conditions are applied to the grouped results, not the original data.

GROUP BY query based on a single field

Example 14: Query the number of orders placed by each customer in the fruit_order table, and return the data of the user_id and COUNT(order_id) columns.

SELECT user_id, COUNT(order_id)
FROM fruit_order
GROUP BY user_id;

The return result is as follows:

+---------+-----------------+
| user_id | COUNT(order_id) |
+---------+-----------------+
|    1011 |               3 |
|    1022 |               4 |
|    1033 |               2 |
|    1034 |               1 |
+---------+-----------------+
4 rows in set

GROUP BY query based on multiple fields

Example 15: Query the number of orders placed by each customer each year in the fruit_order table, and return the data of the user_id, order_year, and COUNT(order_id) columns.

SELECT user_id, order_year, COUNT(order_id)
FROM fruit_order
GROUP BY user_id,order_year;

The return result is as follows:

+---------+------------+-----------------+
| user_id | order_year | COUNT(order_id) |
+---------+------------+-----------------+
|    1011 |       2019 |               1 |
|    1011 |       2020 |               2 |
|    1022 |       2019 |               2 |
|    1022 |       2021 |               2 |
|    1033 |       2020 |               1 |
|    1033 |       2021 |               1 |
|    1034 |       2021 |               1 |
+---------+------------+-----------------+
7 rows in set

Filtering before grouping

Example 16: Query the number of orders placed by each customer in 2020, and return the data of the user_id and COUNT(order_id) columns.

SELECT user_id, COUNT(order_id)
FROM fruit_order t
WHERE t.order_year = 2020
GROUP BY user_id;

The return result is as follows:

+---------+-----------------+
| user_id | COUNT(order_id) |
+---------+-----------------+
|    1011 |               2 |
|    1033 |               1 |
+---------+-----------------+
2 rows in set

Filtering after grouping

Example 17: Query customers who placed two or more orders in 2019, and return the data of the user_id and COUNT(order_id) columns.

SELECT user_id, COUNT(order_id)
FROM fruit_order t
WHERE t.order_year = 2019
GROUP BY user_id
HAVING COUNT(order_id) >= 2;

The return result is as follows:

+---------+-----------------+
| user_id | COUNT(order_id) |
+---------+-----------------+
|    1022 |               2 |
+---------+-----------------+
1 row in set

Sorting after grouping

Example 18: Query the maximum order amount of each customer, and return the data of the user_id and MAX(fruit_price) columns sorted by the maximum order amount in descending order.

SELECT user_id, MAX(fruit_price)
FROM fruit_order t
GROUP BY user_id
ORDER BY MAX(fruit_price) DESC;

The return result is as follows:

+---------+------------------+
| user_id | MAX(fruit_price) |
+---------+------------------+
|    1033 |            63.66 |
|    1011 |            58.83 |
|    1034 |            53.62 |
|    1022 |            34.44 |
+---------+------------------+
4 rows in set

Aggregate queries

An aggregate query aggregates data and returns a summary of results. It can collect statistics on, count, or calculate the sum, average value, maximum value, or minimum value of a set of data. An aggregate query is usually used with the GROUP BY clause to group data and aggregate each group of data. The GROUP BY clause groups data based on specified columns. Then an aggregate function is applied to each group to generate a result set.

The following table lists frequently-used aggregate functions in GROUP BY queries.

Data sorting

Data sorting is an operation that arranges the query results based on specified columns or expressions, allowing data to be rearranged in either ascending (ASC) or descending (DESC) order. You can use the ORDER BY clause to specify the sorting method in SQL queries. ORDER BY supports sorting by a single field, multiple fields, an alias, or a function. Separate multiple fields with commas (,). If the ASC or DESC keyword is not added in the ORDER BY clause, the query results are sorted in ascending order by default.

The ORDER BY clause consumes a large amount of resources, especially when you use it to sort large datasets. We recommend that you use indexes to optimize the sorting operation when necessary. Make sure that the correct columns and sorting order are specified.

Sorting by a single field

Example 19: Query student information in the student table, and return the information sorted by score in ascending order.

SELECT id, name, score
FROM student
ORDER BY score;

The return result is as follows:

+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Emma      |    85 |
|  4 | James     |  87.5 |
|  9 | Charlotte |    88 |
|  7 | Ava       |  89.5 |
|  2 | William   |  90.5 |
|  5 | Sophia    |  91.5 |
| 10 | Ethan     |    92 |
|  8 | Michael   |  93.5 |
|  3 | Olivia    |  95.5 |
|  6 | Benjamin  |  96.5 |
+----+-----------+-------+
10 rows in set

Example 20: Query student information in the student table, and return the information sorted by score in descending order.

SELECT id, name, score
FROM student
ORDER BY score DESC;

The return result is as follows:

+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  6 | Benjamin  |  96.5 |
|  3 | Olivia    |  95.5 |
|  8 | Michael   |  93.5 |
| 10 | Ethan     |    92 |
|  5 | Sophia    |  91.5 |
|  2 | William   |  90.5 |
|  7 | Ava       |  89.5 |
|  9 | Charlotte |    88 |
|  4 | James     |  87.5 |
|  1 | Emma      |    85 |
+----+-----------+-------+
10 rows in set

Sorting by multiple fields

Example 21: Query student information in the student table, and return the information sorted by enrollment_date in descending order and by score in ascending order.

SELECT id, name, score, enrollment_date
FROM student
ORDER BY enrollment_date DESC,score ASC;

The return result is as follows:

+----+-----------+-------+-----------------+
| id | name      | score | enrollment_date |
+----+-----------+-------+-----------------+
|  8 | Michael   |  93.5 | 2021-09-08      |
|  9 | Charlotte |    88 | 2021-09-06      |
|  7 | Ava       |  89.5 | 2021-09-06      |
|  5 | Sophia    |  91.5 | 2021-09-05      |
|  4 | James     |  87.5 | 2021-09-03      |
|  3 | Olivia    |  95.5 | 2021-09-03      |
|  2 | William   |  90.5 | 2021-09-02      |
|  1 | Emma      |    85 | 2021-09-01      |
| 10 | Ethan     |    92 | 2021-09-01      |
|  6 | Benjamin  |  96.5 | 2021-09-01      |
+----+-----------+-------+-----------------+
10 rows in set

Sorting by functions

You can use functions in the ORDER BY clause to sort query results. The functions can be applied to specified columns or expressions to support complex data sorting.

Example 22: Query student information in the student table, and return the information sorted by DAY(enrollment_date) in descending order and by score in ascending order.

SELECT id, name, score, enrollment_date
FROM student
ORDER BY DAY(enrollment_date) DESC,score ASC;

The return result is as follows:

+----+-----------+-------+-----------------+
| id | name      | score | enrollment_date |
+----+-----------+-------+-----------------+
|  8 | Michael   |  93.5 | 2021-09-08      |
|  9 | Charlotte |    88 | 2021-09-06      |
|  7 | Ava       |  89.5 | 2021-09-06      |
|  5 | Sophia    |  91.5 | 2021-09-05      |
|  4 | James     |  87.5 | 2021-09-03      |
|  3 | Olivia    |  95.5 | 2021-09-03      |
|  2 | William   |  90.5 | 2021-09-02      |
|  1 | Emma      |    85 | 2021-09-01      |
| 10 | Ethan     |    92 | 2021-09-01      |
|  6 | Benjamin  |  96.5 | 2021-09-01      |
+----+-----------+-------+-----------------+
10 rows in set

Sorting after data filtering

You can use the WHERE clause to filter data before sorting.

Example 23: Query information about students whose score values are greater than 85 in the student table, and return the information sorted by DAY(enrollment_date) in ascending order.

SELECT id, name, score, DAY(enrollment_date)
FROM student
WHERE score > 85
ORDER BY DAY(enrollment_date) ASC;

The return result is as follows:

+----+-----------+-------+----------------------+
| id | name      | score | DAY(enrollment_date) |
+----+-----------+-------+----------------------+
|  6 | Benjamin  |  96.5 |                    1 |
| 10 | Ethan     |    92 |                    1 |
|  2 | William   |  90.5 |                    2 |
|  3 | Olivia    |  95.5 |                    3 |
|  4 | James     |  87.5 |                    3 |
|  5 | Sophia    |  91.5 |                    5 |
|  7 | Ava       |  89.5 |                    6 |
|  9 | Charlotte |    88 |                    6 |
|  8 | Michael   |  93.5 |                    8 |
+----+-----------+-------+----------------------+
9 rows in set

LIMIT clause

Limit the number of rows in the result set

You can use the LIMIT clause to limit the number of rows returned in the result set for an SQL query.

The format of the LIMIT clause to limit the number of rows is as follows:

LIMIT [offset,] row_count

or

LIMIT row_count OFFSET offset

where

• offset specifies the number of rows to skip. In the first format, offset is optional, and its default value is 0, indicating that zero rows are to be skipped. The value range is [0, +∞).

• row_count specifies the number of rows to be returned. In the first format, if offset is not specified, data is returned starting from the first row by default. The value range is [0, +∞).

Retrieve the first m rows of records

Example 24: Retrieve the first five rows of the student table, and return the data of the id and name columns.

SELECT id, name
FROM student
LIMIT 5;

The return result is as follows:

+----+---------+
| id | name    |
+----+---------+
|  1 | Emma    |
|  2 | William |
|  3 | Olivia  |
|  4 | James   |
|  5 | Sophia  |
+----+---------+
5 rows in set

Retrieve the record with the maximum value

Example 25: Sort data in the student table by score in descending order, and retrieve the first row to obtain the record with the maximum score value.

SELECT id, name, score
FROM student
ORDER BY score DESC
LIMIT 1;

The return result is as follows:

+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  6 | Benjamin |  96.5 |
+----+----------+-------+
1 row in set

Obtain the m rows of records following the n skipped rows of records

Example 26: Retrieve the three rows following the fifth row in the student table, and return the data of the id and name columns.

SELECT id, name
FROM student
LIMIT 3 OFFSET 5;

The return result is as follows:

+----+----------+
| id | name     |
+----+----------+
|  6 | Benjamin |
|  7 | Ava      |
|  8 | Michael  |
+----+----------+
3 rows in set

Pagination queries

You can use the LIMIT clause to implement pagination queries.

The format of the LIMIT clause for pagination is as follows:

LIMIT (page_no - 1) * page_size, page_size;

where

• page_no specifies the page number. The value range is [1, +∞).

• page_size specifies the number of records per page. The value range is [1, +∞). For example, if page_no is set to 5 and page_size is set to 10, the 10 records on page 5 are retrieved.

Example 27: For the student table, set page_size to 2 to retrieve data of page 1, page 2, and page 3.

Page 1:

SELECT id, name
FROM student
ORDER BY id
LIMIT 0,2;

The return result is as follows:

+----+---------+
| id | name    |
+----+---------+
|  1 | Emma    |
|  2 | William |
+----+---------+
2 rows in set

Page 2:

SELECT id, name
FROM student
ORDER BY id
LIMIT 2,2;

The return result is as follows:

+----+--------+
| id | name   |
+----+--------+
|  3 | Olivia |
|  4 | James  |
+----+--------+
2 rows in set

Page 3:

SELECT id, name
FROM student
ORDER BY id
LIMIT 4,2;

The return result is as follows:

+----+----------+
| id | name     |
+----+----------+
|  5 | Sophia   |
|  6 | Benjamin |
+----+----------+
2 rows in set