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 the privileges of the current user, see Query user privileges. If you do not have this privilege, contact your administrator. For more information about how to grant user privileges, see Modify user privileges.

Syntax

You can use SELECT statements 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 following table describes the parameters:

Execution order of SELECT keywords

When the keywords WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT are used together, there are specific restrictions on the order. The execution order of these keywords is as follows:

1. Execute FROM to find the required table.

2. Execute WHERE to specify the constraint conditions.

3. Execute GROUP BY to group or aggregate each retrieved record. If GROUP BY is not executed, all records are considered a group.

4. Execute HAVING to filter the grouped results.

5. Execute SELECT.

6. Execute DISTINCT to remove duplicate rows.

7. Execute ORDER BY to sort the results in ascending or descending order based on the conditions.

8. Execute LIMIT to limit the number of records.

Create a test table and add test data

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 'OrderID',
     user_id BIGINT NOT NULL COMMENT 'CustomerID',
     user_name VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'CustomerName',
     fruit_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'OrderAmount',
     order_year SMALLINT NOT NULL COMMENT 'OrderYear'
   ) COMMENT 'OrderTable';
   

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 organize the columns effectively 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 manually list all the fields by using the statement SELECT id, name, gender, age, score, enrollment_date FROM student;.

Example 1: Query the data of all rows in the student table, and return the data from all of the columns in each row.

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 from 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: Use the following SQL statement to select data from the student table for columns id, name, age, and age+5, and specify the alias age_plus_5 for the calculated column age+5.

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

When you query data that meets specific conditions, you can filter data by adding a WHERE clause to the SELECT statement. The WHERE clause can contain one or more conditions, which are used to filter the data, ensuring that only the data meeting the WHERE conditions is returned. Depending on specific requirements, you can use the query conditions flexibly to filter and retrieve the desired data.

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

The commonly used query conditions in the WHERE clause are shown in the table below.

For more information about query condition operators, 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 and return the data of user_id and COUNT(order_id).

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 and return the data of user_id, order_year, and COUNT(order_id).

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 user_id and COUNT(order_id).

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 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 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 employ functions within the ORDER BY clause to sort the query results. These functions can be applied to the columns or expressions being sorted, enabling more intricate and sophisticated 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 the WHERE clause

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 clauses

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 for limiting rows with the LIMIT clause can be either of the following:

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 m rows of records after skipping n rows

Example 24: Query the three rows of the id and name columns after the third row in the student table.

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 row with the maximum value

Example 25: To retrieve the row with the maximum score value in the student table, you can sort data in the table by score in descending order and then retrieve the first row.

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 after skipping n rows

Example 26: Query the three rows of the id and name columns after the fifth row in the student table.

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 syntax, see SELECT.

• For more information about query condition operators, see Comparison operators.

• For more information about subqueries, see Subqueries.

• For more information about query optimization, see Query rewrite overview.