This topic describes how to perform a single-table query in OceanBase AI Database using SQL statements.
Prerequisites
- You are connected to an OceanBase AI Database tenant.
- You have the
SELECTprivilege. For more information about checking user privileges, see View user privileges. If you do not have this privilege, contact your administrator to grant it. For more information about granting privileges to users, see Grant privileges directly.
Syntax
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:
Parameter |
Description |
|
|---|---|---|
| select_list | The list of columns to retrieve, which can be column names, expressions, aggregate functions, etc. Multiple columns can be separated with commas. | |
| table_name | The name of the table from which data is to be retrieved. | |
| WHERE query_condition | Optional. Specifies the search conditions. Only rows that meet the conditions are returned. | |
| GROUP BY group_by_condition | Optional. Specifies the column by which to group the results. This clause is often used together with aggregate functions. | |
| HAVING group_condition | Optional. Filters the grouped result set and returns only the groups that meet the specified conditions. | |
| ORDER BY column_list | Optional. Specifies the columns to use for sorting the result set. | |
| ASC | DESC | Optional. Specifies the sorting order. ASC indicates ascending order (default), and DESC indicates descending order. |
| LIMIT limit_clause | Optional. Specifies the number of rows to return in the result set. | |
| column_list | The parameter used to specify the columns to retrieve. You can specify one or more columns, separated by commas. | |
| column_name | The name of the column to retrieve. |
Overview of the execution order of SELECT keywords
When keywords such as WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT are used together, their sequence is strictly defined. The execution order is as follows:
Execute
FROMto find the table.Execute
WHEREto specify the constraints.Execute
GROUP BYto group (aggregate) each record fetched. If there is noGROUP BY, the entire result set acts as one group.Execute
HAVINGto filter the results of the groups.Execute
SELECT.Execute
DISTINCTto remove duplicates.Execute
ORDER BYto sort the results in ascending or descending order based on the conditions.Execute
LIMITto limit the number of results.
Notice
The difference between WHERE and HAVING is that WHERE filters data before grouping, while HAVING filters the results after grouping to finally return the query result of the entire SQL statement.
Create a test table and add test data
Create the
studenttable.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
studenttable.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 the
fruit_ordertable.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' ) COMMENT 'Order table';Insert 10 rows of data into the
fruit_ordertable.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 an 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.
Note
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 the data in all rows of the student table.
SELECT id, name, gender, age, score, enrollment_date, notes
FROM student;
or
SELECT * FROM student;
The query 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
Query data in specified columns by column name in a table.
Example 2: Use the following SQL statement to query all rows of data in the student table and return the data in the id and name columns for each row.
SELECT id, name
FROM student;
The query 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 computed values and specify column aliases
Perform computations on specified columns in a query.
Example 3: Use the following SQL statement to select data from the student table for four columns: id, name, age, and age+5. Specify an alias age_plus_5 for the computed column age+5.
SELECT id, name, age, age+5 AS age_plus_5
FROM student;
The query 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
Note
For more information about how to use operators and functions in queries to process data in specified columns, see the section under Use operators and functions in queries.
Data filtering
When you want to query data that meets specific conditions, you can add a WHERE clause to the SELECT statement for data filtering. The WHERE clause can contain one or more conditions that filter the data. Only data that meets the WHERE conditions is returned. You can flexibly use query conditions to filter and retrieve target data according to your specific needs.
When using the WHERE clause, ensure the conditions are correct and appropriate operators are used.
The following table lists common query conditions used in the WHERE clause.
Query condition type |
Operator |
|---|---|
| Comparison query | =,>,<,>=,<=,!=,<> |
| Logical query (multiple conditions) | AND,OR,NOT |
| Fuzzy query (character matching) | LIKE,NOT LIKE |
| Range query (specified range) | BETWEEN AND,NOT BETWEEN AND |
| Specified set query | IN,NOT IN |
| NULL value query | IS NULL,IS NOT NULL |
For more information about query condition operators, see Comparison operators.
Comparative condition queries
Equal to
Equal to (=): Queries data in the specified column that equals the target value. If the value is of string type, 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 equals 1, and return the data in the id, name, and gender columns for these rows.
SELECT id, name, gender
FROM student
WHERE gender = 1;
The query 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" includes two notations: <> and !=.
Example 5: Use the following SQL statement to query all rows in the student table where the gender column does not equal 1, and return the data in the id, name, and gender columns for these rows.
SELECT id, name, gender
FROM student
WHERE gender <> 1;
The query 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 numerical values in size. When comparing characters, they are compared based on their ASCII code values, comparing each corresponding character position in sequence.
Note
Greater than or equal to (\>=) and less than or equal to (\<=) are similar.
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 in the id, name, and score columns for these rows.
SELECT id, name, score
FROM student
WHERE score < 90;
The query 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 condition query
The logical operators AND and OR support queries with multiple conditions.
AND
The AND keyword is used to combine multiple conditions. Data is returned only if all conditions are met simultaneously.
Example 7: Use the following SQL statement to query all rows in the student table where gender equals 1 and score is less than or equal to 90, and return the data in the id, name, gender, and score columns for these rows.
SELECT id, name, gender, score
FROM student
WHERE gender = 1 AND score <= 90;
The query 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 is returned if any one of the conditions is met.
Example 8: Use the following SQL statement to query all rows in the student table where gender equals 1 or score is less than 90, and return the data in the id, name, gender, and score columns for these rows.
SELECT id, name, gender, score
FROM student
WHERE gender = 1 OR score < 90;
The query 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 can be used for fuzzy string matching.
This syntax matches the value in the corresponding column against pattern. pattern can be a complete string or contain the wildcard characters % and _. Specifically:
The underscore (
_) matches any single character.The percent sign (
%) matches zero or more characters in the value.%cannot matchNULL.
Notice
When the database character set is ASCII, two _ characters are required for one Chinese character; 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 name contains am, and return the data in the id and name columns for these rows.
SELECT id, name
FROM student
WHERE name LIKE '%am%';
The query result is as follows:
+----+----------+
| id | name |
+----+----------+
| 2 | William |
| 4 | James |
| 6 | Benjamin |
+----+----------+
3 rows in set
Range query
The BETWEEN ... AND operator retrieves data that falls between two values. These values can be numeric, text, or date.
Note
The two boundary values in a range query must not be swapped. The value 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 value is between 85 and 90, and return the data in the id, name, and score columns for these rows.
SELECT id, name, score
FROM student
WHERE score BETWEEN 85 AND 90;
The query 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
Specified set query
The IN operator specifies multiple values in the WHERE clause, which form a set. IN returns any data in the specified column that matches any value in the set. NOT IN returns only data that does not match any value in the set.
Note
- The value types in the
[NOT] INset must be consistent or compatible with each other. - Values in the
[NOT] INset do not support wildcards.
Example 11: Use the following SQL statement to query all rows in the student table where the id value is in the set (1, 3, 5, 7), and return the data in the id and name columns for these rows.
SELECT id, name
FROM student
WHERE id IN (1,3,5,7);
The query result is as follows:
+----+--------+
| id | name |
+----+--------+
| 1 | Emma |
| 3 | Olivia |
| 5 | Sophia |
| 7 | Ava |
+----+--------+
4 rows in set
Queries for NULL values
Since comparison operators, LIKE, BETWEEN AND, IN, and NOT IN yield inaccurate results when querying for NULL values, it is recommended to use the dedicated NULL value queries IS NULL and IS NOT NULL. Additionally, you can use the safe equality operator (<=>), which can evaluate both regular numbers and NULL values.
IS NULL
The IS NULL condition is used to query for data where the value in the specified column is NULL.
Example 12: Use the following SQL statement to query all rows in the student table where the notes column is NULL, and return the data in the id, name, score, and notes columns for these rows.
SELECT id, name, score, notes
FROM student
WHERE notes IS NULL;
The query 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 data where the value of a specified column is not NULL.
Example 13: Use the following SQL statement to query all rows in the student table where the notes column is not null, and return the data in the id, name, score, and notes columns for those rows.
SELECT id, name, score, notes
FROM student
WHERE notes IS NOT NULL;
The query 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
In SQL queries, you can use the GROUP BY clause to group the query results. GROUP BY supports grouping by a single field or multiple fields. Before grouping, you can use the WHERE clause to filter data. After grouping, you can use the HAVING clause to filter data and the ORDER BY clause to sort data.
Considerations for data grouping:
- When using the
GROUP BYclause, the columns in theSELECTstatement must be either columns in theGROUP BYclause or aggregate functions. - When using the
HAVINGclause, theHAVINGcondition filters the grouped results, not the original data.
Grouping by a single field
Example 14: Query the number of orders per 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 query result is as follows:
+---------+-----------------+
| user_id | COUNT(order_id) |
+---------+-----------------+
| 1011 | 3 |
| 1022 | 4 |
| 1033 | 2 |
| 1034 | 1 |
+---------+-----------------+
4 rows in set
Grouping by multiple fields
Example 15: Query the number of orders per customer per year in fruit_order 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 query 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 per customer for 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 query result is as follows:
+---------+-----------------+
| user_id | COUNT(order_id) |
+---------+-----------------+
| 1011 | 2 |
| 1033 | 1 |
+---------+-----------------+
2 rows in set
Filtering after grouping
Note
When a query contains HAVING, it first obtains the SQL query result without the HAVING clause, then filters out the matching data based on the HAVING condition on this result, and finally returns these data. Therefore, aggregate functions can be used after HAVING, and these aggregate functions do not have to be the same as those after SELECT.
Example 17: Query customers with more than 1 order in 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 query result is as follows:
+---------+-----------------+
| user_id | COUNT(order_id) |
+---------+-----------------+
| 1022 | 2 |
+---------+-----------------+
1 row in set
Sorting after grouping
Example 18: Query the maximum amount for each customer's orders, and then output user_id and MAX(fruit_price) in descending order of the maximum amount.
SELECT user_id, MAX(fruit_price)
FROM fruit_order t
GROUP BY user_id
ORDER BY MAX(fruit_price) DESC;
The query 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 type of query that performs aggregate operations on data and returns a summary of the results. It can perform aggregate operations such as statistics, counting, summing, averaging, finding the maximum value, and finding the minimum value on a set of data. Aggregate queries are typically used with the GROUP BY clause to group the data and apply aggregate operations to each group. The GROUP BY clause groups the data by specified columns, and then aggregate functions are applied to each group to generate a result set.
Common aggregate functions used in grouping are shown in the following table.
Aggregate function |
Description |
|---|---|
| MAX() | Returns the maximum value in the specified column. |
| MIN() | Returns the minimum value in the specified column. |
| COUNT() | Returns the number of rows in the query result. |
| SUM() | Returns the sum of the values in the specified column. |
| AVG() | Returns the average value of the data in the specified column. |
For more information about using aggregate functions in queries, see Use aggregate functions in queries.
Data sorting
Data sorting is an operation that sorts the query results according to specified columns or expressions, and it can rearrange data in ascending order (ASC) or descending order (DESC). In SQL queries, you can use the ORDER BY clause to specify the sorting method. The ORDER BY clause supports sorting by a single field, multiple fields, by alias, and by function. Multiple fields are separated by commas. When performing a sorting query, if the ASC or DESC keyword is not added, the query result is sorted in ascending order by default.
Using the ORDER BY clause to sort a result set is a resource-consuming operation, especially for large datasets. When necessary, it is recommended to use indexes to optimize the sorting operation. Ensure the correct columns and sorting order are specified.
Sorting by a single field
Example 19: Display student information in ascending order of score in the student table.
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: Display student information in descending order of score in the student table.
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
Sorting by multiple fields
Example 21: Display student information in the student table in descending order by enrollment_date and ascending order by score.
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
Sorting using functions in the ORDER BY clause
In the ORDER BY clause, you can use functions to sort the query results. Functions can be applied to the columns or expressions to be sorted for more complex sorting.
Example 22: Display student information in the student table in descending order by DAY(enrollment_date) and ascending order by score.
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
Sorting after filtering data
You can use the WHERE clause to filter data before sorting.
Example 23: Query students with a score greater than 85 from the student table and display the student information in ascending order by DAY(enrollment_date).
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 a result set
In an SQL query, you can use the LIMIT clause to limit the number of rows returned in the result set.
The syntax of format 1 of the LIMIT clause for limiting the number of rows is as follows:
LIMIT [offset,] row_count
The syntax of format 2 of the LIMIT clause for limiting the number of rows is as follows:
LIMIT row_count OFFSET offset
The parameters are described as follows:
offset: The offset, which is the number of rows to skip. In format 1,offsetis optional and defaults to 0, meaning 0 rows are skipped. The value range foroffsetis [0,+∞).row_count: The number of rows to return. In format 1, ifoffsetis not specified, the first row is used by default. The value range forrow_countis [0,+∞).
Note
The values for offset and row_count are subject to the following restrictions:
- Expressions cannot be used.
- They must be exact numbers and cannot be negative.
Get the first m rows
Example 24: Query the first 5 records of columns id and name from 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
Get the record with the maximum value
Example 25: To get the record with the highest score from the student table, you can first sort by score in descending order and then select 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
Get the m rows after skipping n rows
Note
When skipping n rows of records, if the number of remaining data rows is less than m, the query result will include all the remaining data.
Example 26: Query three rows of data starting from the fifth row in the id and name columns of 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
Paginated query
In SQL queries, you can use the LIMIT clause to implement paginated queries.
The usage format for the LIMIT clause in paginated queries is as follows:
LIMIT (page_no - 1) * page_size, page_size;
The parameters are described as follows:
page_no: Indicates which page to retrieve, starting from 1. The range is [1,+∞).page_size: Indicates how many records to display per page. The range is [1,+∞). For example, page_no = 5, page_size = 10 means to retrieve 10 data records on the 5th page.
Example 27: In the student table, display 2 data records per page, and retrieve the data on the 1st, 2nd, and 3rd pages in sequence.
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
SELECTstatement, 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 Rule-based query rewriting.