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:
  • ALL: returns all values of the specified fields, including duplicate values.
  • DISTINCT: removes duplicate fields and returns only the values of distinct fields.
  • DISTINCTROW: removes duplicate rows and returns only the values of distinct rows.
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.
  • You can use the WHERE clause together with relational operators to query the data that meets specified conditions. The format is column_name operator value [AND | OR] [column_name operator value]. For more information, see WHERE clause (where_condition).
  • You can use the WHERE clause together with the conditions of match query or match phrase query to perform full-text search. For more information, see Full-text search.
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 select_expr to specify the column that you want to query. When you use select_expr, take note of the following items:
  • 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 use groupby_condition to group the row data in the result set of a SELECT statement based on a specified condition. When you use groupby_condition, take note of the following items:
  • 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.

In most cases, the HAVING clause is used together with aggregate functions to filter data. 
SELECT username,SUM(orderprice) FROM orders GROUP BY username HAVING SUM(orderprice) < 500;

ORDER BY clause (order_condition)

You can use order_condition to sort the row data in the result set of a query based on the specified field and sorting method. When you use order_condition, take note of the following items:
  • 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;