You can execute the SELECT statement to query data in a table.
Usage notes
The execution priority of clauses in the SELECT statement is WHERE > GROUP BY > HAVING > ORDER BY > LIMIT and OFFSET.
Syntax
SELECT
[ALL | DISTINCT | DISTINCTROW]
select_expr [, select_expr] ...
[FROM table_references]
[WHERE where_condition]
[GROUP BY groupby_condition]
[HAVING having_condition]
[ORDER BY order_condition]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
Parameters
Parameter | Required | Description |
---|---|---|
ALL | DISTINCT | DISTINCTROW | No | Specifies whether to remove duplicate fields. Default value: ALL. Valid values:
|
select_expr | Yes | The name or expression of the column in the column_name[, column_name][, column_exp],.. format. For more information, see Column expression (select_expr).
|
table_references | Yes | The information about the table whose data you want to query. The value of this parameter
can be a table name or a SELECT statement in the table_name | select_statement format. For more information, see Table information (table_references).
|
where_condition | No | The WHERE clause that can be used together with different conditions to implement
specific features.
|
groupby_condition | No | The GROUP BY clause that can be used together with aggregate functions. The format
is column_name . For more information, see GROUP BY clause (groupby_condition).
|
having_condition | No | The HAVING clause that can be used together with aggregate functions. The format is
aggregate_function(column_name) operator value . For more information, see HAVING clause (having_condition).
|
order_condition | No | The ORDER BY clause in the column_name [ASC | DESC][,column_name [ASC | DESC],...] format. For more information, see ORDER BY clause (order_condition).
|
row_count | No | The maximum number of rows to return in the query. |
offset | No | The data that is skipped in the query. Default value: 0. |
Column expression (select_expr)
- You can use the wildcard character (*) to query all columns. You can also use the
WHERE clause to specify the query condition.
SELECT * FROM orders;
The following example shows how to use the WHERE clause to specify a query condition:SELECT * FROM orders WHERE orderprice >= 100;
- You can use the column name to specify the column that you want to query.
SELECT username FROM orders;
Table information (table_references)
You can use table_references to specify the table whose data you want to query.
SELECT orderprice FROM orders;
WHERE clause (where_condition)
You can use where_condition to query data that meets the specified conditions. When you use where_condition, take note of the following items:
- You can use simple expressions that are constructed by using operators such as arithmetic
operators or relational operators.
SELECT * FROM orders WHERE username = 'lily'; SELECT * FROM orders WHERE orderprice >= 100;
- You can use combined expressions that are constructed by using logical operators.
SELECT * FROM orders WHERE username = 'lily' AND orderprice >= 100;
For more information about operators, see SQL operators.
GROUP BY clause (groupby_condition)
- You can group row data by field.
SELECT username FROM orders GROUP BY username;
- You can use aggregate functions on grouped columns.
SELECT username,COUNT(*) FROM orders GROUP BY username;
- You must add the columns that do not use aggregate functions in the SELECT statement
to the GROUP BY clause.
SELECT username,orderprice FROM orders GROUP BY username,orderprice;
For more information about aggregate functions, see Aggregate functions.
HAVING clause (having_condition)
You can use having_condition to filter the row data that you grouped in the result sets that are obtained by using the WHERE and GROUP BY clauses. The row data that you grouped is filtered based on specified conditions.
SELECT username,SUM(orderprice) FROM orders GROUP BY username HAVING SUM(orderprice) < 500;
ORDER BY clause (order_condition)
- You can use the ASC or DESC keyword to specify the sorting method. By default, the
data is sorted in ascending order (ASC).
SELECT * FROM orders ORDER BY orderprice DESC LIMIT 10;
- You can specify multiple fields based on which you want to sort data.
SELECT * FROM orders ORDER BY username ASC,orderprice DESC LIMIT 10;
- You can use LIMIT to limit the number of rows to return.
SELECT * FROM orders ORDER BY orderprice LIMIT 10;