Single-table queries

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

Prerequisites

• You have connected to a MySQL tenant of OceanBase Database.
• You have the SELECT privilege. For more information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Grant direct privileges.

Syntax

You can use the SELECT statement to query data.

The general structure 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...]

The parameters are described as follows:

Execution order of keywords in the SELECT statement

If you use the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT keywords in a query, these clauses are executed in 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. Execute LIMIT to limit the number of records.

Create a test table and add test data to the table

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 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 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 result of calculation.

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

The 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 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 commonly used query conditions specified by the WHERE clause.

For more information about operators in query conditions, see Comparison operators.

Queries with comparison operators

Equal to (=)

This operator queries data from the specified column that is equal to the target value. If the value is of string type, it needs to be enclosed in single or double quotes.

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

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

The 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 (<> and !=)

This operator includes two different expressions: <> and !=.

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

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

The 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 in the student table where score values are less than 90, and return the data from the id, name, and score columns in these rows.

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

The 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

The AND keyword is used to combine multiple conditions, ensuring that only the data satisfying all conditions will be returned.

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

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

The result is as follows:

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

OR

The OR keyword is used to link multiple conditions, returning data that satisfies any of the conditions.

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

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

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

The predicate LIKE can be used for string matching.

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. The pattern % cannot match NULL.

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

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

The result is as follows:

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

Range queries (BETWEEN AND)

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

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

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

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

The IN operator is used to specify multiple values as a set in a WHERE clause. It returns data from the specified column that matches any value in the set. On the other hand, the NOT IN operator returns data from the specified column that does not match any value in the set.

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

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

The result is as follows:

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

NULL value queries

Due to the inaccurate results obtained when using comparison operators, LIKE, BETWEEN AND, IN, and NOT IN to query for NULL values, we recommend that you use the dedicated query statements IS NULL and IS NOT NULL for NULL value queries. Additionally, the safe equal operator (<=>) can be used to compare both normal values and NULL values.

IS NULL

The IS NULL condition is used to query the data where the specified column's value is NULL.

Example 12: Query all rows in the student table where notes is NULL, and return the data from the id, name, score, and notes columns in these rows.

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

The 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

The IS NOT NULL condition is used to query the data where the specified column's value is not NULL.

Example 13: Query all rows in the student table where the notes is not NULL, and return the data from the id, name, score, and notes columns in these rows.

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

The 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

GROUP BY queries

In SQL queries, the GROUP BY clause is used to group the query results. GROUP BY supports grouping by a single field or multiple fields. Before you perform data grouping, you can filter the data using the WHERE clause and then filter the data using the HAVING clause. You can also use the ORDER BY clause to sort the 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 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 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

Filter data 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 result is as follows:

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

Filter data after grouping

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

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 result is as follows:

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

Sort data after grouping

Example 18: Query the maximum order amount of each customer and return the data of user_id and MAX(fruit_price) sorted by 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 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 is a method used to perform aggregation operations on data and return summary results. It can be used to perform statistical operations such as counting, summing, averaging, finding the maximum and minimum values, and other aggregation operations on a set of data. Aggregate queries are typically used with the GROUP BY clause to group data and perform aggregation operations on each group. The GROUP BY clause groups the data based on specified columns, and then aggregate functions are applied to each group, generating a result set.

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

For more information about aggregate queries, see Use aggregate functions in 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. In SQL queries, the ORDER BY clause is used to specify the sorting method. The ORDER BY clause supports sorting by a single field, sorting by multiple fields, sorting by aliases, and sorting by functions, with multiple fields separated by commas. When performing a sorting query, if the ASC or DESC keywords are not added, the default query result is sorted in ascending order.

Using the ORDER BY clause to sort the result set is a resource-intensive operation, especially for large datasets. When necessary, we recommend that you use indexes to optimize the sorting operation. Ensure that the correct columns and sorting order are specified.

Sorting by 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 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 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 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. These 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 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 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

In SQL queries, the LIMIT clause can be used to limit the number of rows returned in the result set.

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. The value range is [0,+∞). In the first format, offset is optional and its default value is 0, indicating that zero rows are skipped.

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

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 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 result is as follows:

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

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

Example 26: Retrieve the three rows after 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 result is as follows:

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

Paging queries

In SQL queries, the LIMIT clause can be used to implement paging queries.

The format for paging queries with the LIMIT clause is as follows:

LIMIT (page_no - 1) * page_size, page_size;

where

• page_no specifies the page number that starts from 1. 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: In 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 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 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 result is as follows:

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

References

• For more information about the SELECT statement, see SELECT.

• For more information about operators in query conditions, see Comparison operators.

• For more information about subqueries, see Subqueries.

• For more information about query optimization, see Overview.