Single-Table Query

This topic describes how to perform single-table queries in seekdb by using SQL statements.

Prerequisites

You have connected to seekdb.
You have the SELECT privilege. For more information about how to view the current user privileges, see View user privileges. If you do not have the privilege, contact the administrator to grant the privilege. For more information about how to directly grant a privilege, see Directly grant a privilege.

Syntax

You can use the SELECT statement to query data.

The general syntax of the SELECT statement for querying a single table 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...]

Parameter description:

Parameter	Description
select_list	The list of columns to be retrieved. It can contain column names, expressions, or aggregate functions. You can separate multiple columns with commas.
table_name	The name of the table from which to retrieve data.
WHERE query_condition	An optional parameter that specifies the retrieval conditions. Only rows that meet the conditions will be returned.
GROUP BY group_by_condition	An optional parameter that specifies the columns to group the results by. It is typically used with aggregate functions.
HAVING group_condition	An optional parameter that filters the grouped result set and returns only the groups that meet the conditions.
ORDER BY column_list	An optional parameter that specifies the columns to sort the result set by. You can specify one or more columns for sorting.
ASC	DESC
LIMIT limit_clause	An optional parameter that specifies the maximum number of rows to return.
column_list	The list of columns to be retrieved. You can specify one or more columns, separated by commas.
column_name	The name of the column to be retrieved.

Execution order of the SELECT keyword

When you use the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT keywords together, their execution order is fixed. The execution order is as follows:

Execute the FROM clause to find the table.
Execute the WHERE clause to specify the constraint conditions.
Execute the GROUP BY clause to group each record (aggregate) that is retrieved. If the GROUP BY clause is not specified, all records are grouped into one group.
Execute the HAVING clause to filter the grouped result set.
Execute the SELECT clause.
Execute the DISTINCT clause to remove duplicates.
Execute the ORDER BY clause to sort the result set in ascending or descending order based on the specified conditions.
Execute the LIMIT clause to limit the number of result rows.

tip

The WHERE and HAVING clauses are different in that the WHERE clause filters data before grouping, while the HAVING clause filters the grouped result set and returns the entire SQL query result.

Create a test table and add test data

Create a student table.

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

Insert 10 rows of data 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');

Create a fruit_order table.

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 'Order Year'
) COMMENT 'Order Table';

Insert 10 rows of data 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 using SELECT, it is recommended to use meaningful column aliases and appropriate column order to improve the readability of the result set and better organize and understand the query results.

Query all columns

You can use the SELECT * FROM student; statement to query all student information.
You can also manually list all fields and use the SELECT id,name,gender,age,score,enrollment_date FROM student; statement to query all student information.

info

Although using * can quickly list all fields, manually listing all fields is better for query performance, code readability, and maintainability.

Example 1: Use the following SQL statement to query all rows in the student table.

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

SELECT * FROM student;

The result set 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

Query specified columns by column name.

Example 2: Use the following SQL statement to query all rows in the student table and return the id and name columns of each row.

SELECT id, name
FROM student;

The result set 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 specified column aliases

You can perform data calculations on specified columns in a query.

Example 3: Use the following SQL statement to select the id, name, age, and age+5 columns from the student table 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 set 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

info

For more information about how to use operators and functions in queries to process data on specified columns, see Use operators and functions in queries below.

Data filtering

To query data that meets specific conditions, you can add a WHERE clause in the SELECT query statement to filter the data. The WHERE clause can contain one or more conditions that are used to filter the data. Only data that meets the WHERE conditions will be returned. You can flexibly use query conditions to filter and retrieve the target data based on specific requirements.

When using the WHERE clause, ensure that the conditions are correct and the appropriate operators are used.

The following table describes the commonly used query conditions in the WHERE clause.

Query condition type	Predicate
Comparison query	`=`, `>`, `<`, `>=`, `<=`, `!=`, `<>`
Logical query (multiple conditions)	`AND`, `OR`, `NOT`
Fuzzy query (character matching)	`LIKE`, `NOT LIKE`
Range query (determine range)	`BETWEEN AND`, `NOT BETWEEN AND`
Set query	`IN`, `NOT IN`
NULL value query	`IS NULL`, `IS NOT NULL`

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

Comparison conditions

Equal to

Equal to (=): Queries data in the specified column that is equal to the target value. If the value is a string, it must be enclosed in single or double quotes.

Example 4: Use the following SQL statement to query all rows in the student table where the gender column is equal to 1, and return the data from the id, name, and gender columns.

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

Not equal to can be written as <> or !=.

Example 5: Use the following SQL statement to query all rows in the student table where the gender column is not equal to 1, and return the data from the id, name, and gender columns.

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

Greater than (>) and less than (<) compare numeric values. If comparing characters, they are compared based on their ASCII values, character by character.

info

Greater than or equal to (>=) and less than or equal to (<=) work similarly.

Example 6: Use the following SQL statement to query all rows in the student table where the score column is less than 90, and return the data from the id, name, and score columns.

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

Logical conditions

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

AND

The AND keyword is used to combine multiple conditions. Only data that meets all conditions will be returned.

Example 7: Use the following SQL statement to query all rows in the student table where the gender column is equal to 1 and the score column is less than or equal to 90, and return the data from the id, name, gender, and score columns.

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 connect multiple conditions. Data that meets any of the conditions will be returned.

Example 8: Use the following SQL statement to query all rows in the student table where the gender column is equal to 1 or the score column is less than 90, and return the data from the id, name, gender, and score columns.

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 query

The LIKE predicate is used for fuzzy matching of strings.

The syntax means to find data in the corresponding column that matches the pattern. The pattern can be a complete string or contain wildcards % and _. Specifically:

The underscore (_) matches any single character.
The percent sign (%) matches zero or more characters in the value. % cannot match NULL.

tip

When the database character set is ASCII, one Chinese character requires two _ characters. When the character set is GBK, only one _ character is needed.

Example 9: Use the following SQL statement to query all rows in the student table where the name column contains am, and return the data from the id and name columns.

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 query

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

tip

The two boundary values in a range query cannot be swapped. The values must be greater than or equal to the left value and less than or equal to the right value.

Example 10: Use the following SQL statement to query all rows in the student table where the score column is between 85 and 90, and return the data from the id, name, and score columns.

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

Set query

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

tip

The values in the [NOT] IN set must be of the same type or compatible types.
The [NOT] IN set does not support wildcards.

Example 11: Use the following SQL statement to query all rows in the student table where the id column is in the set (1, 3, 5, 7), and return the data from the id and name columns.

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 query

Comparison operators, LIKE, BETWEEN AND, IN, and NOT IN do not accurately query NULL values. Therefore, it is recommended to use the IS NULL and IS NOT NULL statements for NULL value queries. Additionally, the safe equal operator (<=>) can be used to compare both regular values and NULL values.

IS NULL

The IS NULL condition queries data where the specified column value is NULL.

Example 12: Use the following SQL statement to query all rows in the student table where the notes column is empty, and return the data from the id, name, score, and notes columns.

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 queries data where the specified column value is not NULL.

Example 13: Use the following SQL statement to query all rows in the student table where the notes column is not empty, and return the data from the id, name, score, and notes columns.

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

Grouping data

In SQL queries, you can use the GROUP BY clause to group the query results. GROUP BY supports grouping by a single field and grouping by multiple fields. Before grouping, you can use the WHERE clause to filter the data, the HAVING clause to filter the data after grouping, and the ORDER BY clause to sort the data after grouping.

Considerations:

When you use the GROUP BY clause, the columns in the SELECT statement must be the columns in the GROUP BY clause or the columns of the aggregate functions.
When you use the HAVING clause, the HAVING condition filters the grouped results, not the original data.

Single-field grouping

Example 14: Query the number of orders for each customer in the fruit_order table, and output user_id and COUNT(order_id).

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

The returned result is as follows:

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

Multi-field grouping

Example 15: Query the number of orders for each customer in the fruit_order table, and output 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 returned 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 for each customer in the year 2020, and output 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 returned result is as follows:

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

Filtering after grouping

info

If the query contains the HAVING clause, the SQL query result without the HAVING clause is obtained first. Then, the HAVING condition is used to filter the data from this result. Finally, the filtered data is returned. Therefore, you can use aggregate functions after HAVING, and the aggregate functions do not need to be the same as those after SELECT.

Example 17: Query the customers who placed more than one order in the year 2019, and output 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 returned 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 for each customer, and output user_id and MAX(fruit_price) in descending order of the maximum order amount.

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

The returned 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

Aggregation queries

An aggregation query is a query that aggregates data and returns a summary. It can perform statistical operations such as counting, summing, averaging, and finding the maximum and minimum values on a set of data. Aggregation queries are usually used with the GROUP BY clause to group data and perform aggregation operations on each group. The GROUP BY clause groups data by the specified columns, and then the aggregate functions are applied to each group to generate a result set.

The following table describes the commonly used aggregate functions in grouping.

Aggregate function	Description
MAX()	Queries the maximum value of the specified column.
MIN()	Queries the minimum value of the specified column.
COUNT()	Counts the number of rows in the query result.
SUM()	Returns the sum of the specified column.
AVG()	Returns the average value of the specified column.

For more information about how to use aggregate functions in queries, see Use aggregate functions in queries.

Sort data

You can sort query results by a specified column or expression in ascending (ASC) or descending (DESC) order. In an SQL query, you can use the ORDER BY clause to specify the sorting method. The ORDER BY clause supports single-column sorting, multi-column sorting, sorting by alias, and sorting by function. You can use commas to separate multiple columns in the ORDER BY clause. If you do not specify ASC or DESC, the query results are sorted in ascending order by default.

Sorting query results by using the ORDER BY clause consumes resources, especially for large datasets. We recommend that you use indexes to optimize sorting operations. Make sure that you specify the correct columns and sorting order.

Query data by using single-column sorting

Example 19: Query the student table and display student information in ascending order of the score column.

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

The query 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 the student table and display student information in descending order of the score column.

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

The query 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

Query data by using multi-column sorting

Example 21: Query the student table and display student information in descending order of the enrollment_date column and in ascending order of the score column.

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

The query 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

Sort data by using a function in the ORDER BY clause

In the ORDER BY clause, you can use a function to sort query results. You can apply the function to the column or expression that you want to sort, so that you can sort data in a more complex manner.

Example 22: Query the student table and display student information in ascending order of the DAY(enrollment_date) function and in ascending order of the score column.

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

The query 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

Sort data after filtering

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

Example 23: Query the student table and display student information in ascending order of the DAY(enrollment_date) function, where the score column is greater than 85.

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

The query 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 an SQL query, you can use the LIMIT clause to limit the number of rows in the result set.

The LIMIT clause can be used in the following format to limit the number of rows:

LIMIT [offset,] row_count

The LIMIT clause can be used in the following format to limit the number of rows:

LIMIT row_count OFFSET offset

Parameters:

offset: specifies the offset, that is, the number of rows to skip. In the first format, offset is optional. The default value is 0, which means that no rows are skipped. The value of offset ranges from 0 to +∞.
row_count: specifies the number of rows to return. In the first format, if you do not specify offset, the default value is 0, which means that the first row is returned. The value of row_count ranges from 0 to +∞.

tip

The values of offset and row_count must meet the following requirements:

They cannot be expressions.
They must be positive integers.

Retrieve the first m records

Example 24: Query the first five records of the id and name columns in the student table.

SELECT id, name
FROM student
LIMIT 5;

The query 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: To retrieve the record with the maximum value of the score column in the student table, you can sort the records in descending order of the score column and then retrieve the first record.

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

The query result is as follows:

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

Retrieve m records after skipping n records

info

If the number of remaining records after skipping n records is less than m, all remaining records are returned.

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

SELECT id, name
FROM student
LIMIT 3 OFFSET 5;

The query result is as follows:

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

Pagination

In an SQL query, you can use the LIMIT clause to implement pagination.

The LIMIT clause can be used in the following format to implement pagination:

LIMIT (page_no - 1) * page_size, page_size;

Parameters:

page_no: specifies the page number. The value starts from 1 and ranges from 1 to +∞.
page_size: specifies the number of records to return per page. The value ranges from 1 to +∞. For example, if page_no is 5 and page_size is 10, the query returns 10 records on page 5.

Example 27: In the student table, return two records per page and query the data on page 1, page 2, and page 3.

Page 1:

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

The query 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 query 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 query 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 query condition operators, see Comparison operators.
For more information about subqueries, see Subqueries.
For more information about query statement optimization, see Query rewriting overview.

Prerequisites​

Syntax​

Execution order of the SELECT keyword​

Create a test table and add test data​

Basic queries​

Query all columns​

Query specified columns​

Query calculated values and specified column aliases​

Data filtering​

Comparison conditions​

Equal to​

Not equal to​

Greater than and less than​

Logical conditions​

AND​

OR​

Fuzzy query​

Range query​

Set query​

NULL value query​

IS NULL​

IS NOT NULL​

Grouping data​

Single-field grouping​

Multi-field grouping​

Filtering before grouping​

Filtering after grouping​

Sorting after grouping​

Aggregation queries​

Sort data​

Query data by using single-column sorting​

Query data by using multi-column sorting​

Sort data by using a function in the ORDER BY clause​

Sort data after filtering​

LIMIT clause​

Limit the number of rows in the result set​

Retrieve the first m records​

Retrieve the record with the maximum value​

Retrieve m records after skipping n records​

Pagination​

References​

Contents

Prerequisites

Syntax

Execution order of the SELECT keyword

Create a test table and add test data

Basic queries

Query all columns

Query specified columns

Query calculated values and specified column aliases

Data filtering

Comparison conditions

Equal to

Not equal to

Greater than and less than

Logical conditions

AND

OR

Fuzzy query

Range query

Set query

NULL value query

IS NULL

IS NOT NULL

Grouping data

Single-field grouping

Multi-field grouping

Filtering before grouping

Filtering after grouping

Sorting after grouping

Aggregation queries

Sort data

Query data by using single-column sorting

Query data by using multi-column sorting

Sort data by using a function in the ORDER BY clause

Sort data after filtering

LIMIT clause

Limit the number of rows in the result set

Retrieve the first m records

Retrieve the record with the maximum value

Retrieve m records after skipping n records

Pagination

References