SELECT

Description

This statement is used to query data from one or more tables.

This section mainly introduces the general SELECT syntax. For more information about other SELECT statements, see:

Permissions

To execute the SELECT statement, the current user must have the SELECT permission. For more information about the permissions of seekdb, see Permission classification of seekdb.

Syntax

select_stmt:
    SELECT [hint_options]
        [{DISTINCT | UNIQUE} | ALL]
        [SQL_CALC_FOUND_ROWS]
        [STRAIGHT_JOIN]
        select_expr_list
        [FROM from_list]
        [WHERE where_condition]
        [GROUP BY group_by_condition_list [WITH ROLLUP]
         | GROUP BY [group_by_condition_list] group_by_summary_option (expression_list)]
        [HAVING having_condition]
        [window_clause]
        [ORDER BY order_by_condition_list]
        [LIMIT limit_clause]
        [lock_option];

select_expr_list:
    select_expr [, select_expr ...]

select_expr:
    *
    | table_name.{* | column_name}
    | table_alias_name.{* | column_name}
    | expr [[AS] column_alias_name]

from_list:
    DUAL
    | table_references
    | { location_url | table_function }

location_url:
  '<string>'
  (
  {FORMAT = (
      TYPE = 'CSV',
      LINE_DELIMITER = '<string>' | <expr>,
      FIELD_DELIMITER = '<string>' | <expr>,
      ESCAPE = '<character>' | <expr>,
      FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
      ENCODING = 'charset',
      NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
      SKIP_HEADER = <int>,
      SKIP_BLANK_LINES = { TRUE | FALSE },
      PARSE_HEADER = { TRUE | FALSE },
      TRIM_SPACE = { TRUE | FALSE },
      EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
    )
   | FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
  }
  [PATTERN = '<regex_pattern>']
  )

table_function:
  {
  FILES (
    LOCATION = '<string>',
    {
      FORMAT = (
        TYPE = 'CSV',
        LINE_DELIMITER = '<string>' | <expr>,
        FIELD_DELIMITER = '<string>' | <expr>,
        ESCAPE = '<character>' | <expr>,
        FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
        ENCODING = 'charset',
        NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
        SKIP_HEADER = <int>,
        SKIP_BLANK_LINES = { TRUE | FALSE },
        PARSE_HEADER = { TRUE | FALSE },
        TRIM_SPACE = { TRUE | FALSE },
        EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
      )
      | FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
    },
    [PATTERN = '<regex_pattern>']
  )
  | SOURCE (
      TYPE = 'ODPS',
      ACCESSID = '<string>',
      ACCESSKEY = '<string>',
      ENDPOINT = '<string>',
      TUNNEL_ENDPOINT = '<string>',
      PROJECT_NAME = '<string>',
      SCHEMA_NAME = '<string>',
      TABLE_NAME = '<string>',
      QUOTA_NAME = '<string>',
      COMPRESSION_CODE = '<string>'
    )
  }

where_condition:
    expression

group_by_condition_list:
    group_by_condition [, group_by_condition ...]

group_by_condition:
    expression [ASC | DESC]

group_by_summary_option:
    GROUPING SETS
    | ROLLUP
    | CUBE

expression_list:
    expression [, expression ...]

having_condition:
    expression

order_by_condition_list:
    order_by_condition [, order_by_condition ...]

order_by_condition:
    expression [ASC | DESC]

limit_clause:
    [offset,] row_count
    | row_count OFFSET offset

lock_option:
    FOR UPDATE [opt_for_update_wait]
    | LOCK IN SHARE MODE

opt_for_update_wait:
    WAIT {decimal | intnum}
    | NOWAIT
    | NO_WAIT
    | SKIP LOCKED

Parameter description

Parameter	Description
select_stmt	The SQL statement part that specifies which data to retrieve from the database.
hint_options	Optional. Specifies the `hint` option. For more information about hints, see Optimizer Hint.
{DISTINCT \| UNIQUE} \| ALL	Optional. Specifies whether to return duplicate rows in the result set. `DISTINCT`/`UNIQUE`: Indicates that the result set does not contain duplicate rows. `ALL`: Indicates that all rows, including duplicates, are returned. The default value is `ALL`.
SQL_CACHE \| SQL_NO_CACHE	Optional. Specifies whether to cache the query result. `SQL_CACHE`: Indicates that the query result is cached. `SQL_NO_CACHE`: Indicates that the query result is not cached.
SQL_CALC_FOUND_ROWS	Optional. Records the total number of rows returned in a query with the `LIMIT` clause. You can then call the `FOUND_ROWS()` function to obtain the total number of rows.
STRAIGHT_JOIN	Optional. Forces the optimizer to join tables in the order specified in the `FROM` clause. This command does not affect the execution result.:::tip seekdb does not fully support the `STRAIGHT_JOIN` syntax in MySQL. If the `STRAIGHT_JOIN` clause specifies the join order of tables, seekdb executes the clause. If the `STRAIGHT_JOIN` clause cannot uniquely determine the join order, seekdb ignores the clause. :::
select_expr_list	The list of columns or expressions to display in the query result. For more information about the columns or expressions, see select_expr.
column_name	The name of a column.
FROM from_list	Optional. Specifies the data source for the query. The data source can be a table, a view, or a subquery. For more information about the data source, see from_list.
{ table_function \| location_url }	Optional. The current URL external table supports two syntax forms. For more information, see the following sections: table_function syntax. location_url syntax.
WHERE where_condition	Optional. Specifies the filtering condition for the query. For more information, see where_condition.
GROUP BY group_by_condition_list	Optional. Groups the result set by the specified columns. This clause is typically used with aggregate functions. For more information, see group_by_condition.
WITH ROLLUP	Optional. Aggregates each group to represent a higher-level aggregation (also known as super-aggregation) and generates additional rows.
GROUP BY [group_by_condition_list] group_by_summary_option (expression_list)	Optional. Performs advanced grouping and aggregation operations. `group_by_condition_list`: Optional. Specifies the columns or expressions by which to group. For more information, see group_by_condition. When you explicitly specify `group_by_condition_list`, these columns are considered fixed grouping conditions. They are combined with subsequent `group_by_summary_option` to form the final grouping logic. For example, `GROUP BY col1, GROUPING SETS ((col2), (col3))` is equivalent to `GROUP BY GROUPING SETS ((col1, col2), (col1, col3))`. If you do not specify `group_by_condition_list`, all grouping logic is determined by `group_by_summary_option`. In other words, the grouping conditions are entirely based on the columns or expressions in `expression_list`. `group_by_summary_option`: Specifies the advanced aggregation option. For more information, see group_by_summary_option. `expression_list`: Specifies the list of one or more column names, aliases, or expressions. This list is used to define the aggregation dimensions. :::info seekdb supports advanced grouping and aggregation operations. :::
HAVING having_condition	Optional. Filters the result set after grouping. The `HAVING` clause is similar to the `WHERE` clause, but it can use aggregate functions such as `SUM` and `AVG`. For more information, see having_condition.
window_clause	Optional. Specifies the window definition for the analysis function (also known as window function in some databases). For more information about the `window_clause` syntax, see WINDOW clause.
ORDER BY order_by_condition_list	Optional. Sorts the result set. You can specify one or more columns for sorting. For more information, see order_by_condition.
LIMIT limit_clause	Optional. Specifies the maximum number of rows to return. For more information, see limit_clause.
lock_option	Optional. Locks the query result. For more information, see lock_option.

select_expr

select_expr：specifies the columns or expressions in the query result. Multiple expressions or column names are separated by commas (,). The value can be as follows:

*: specifies all columns.

Here is an example:

Create tables tbl1 and tbl2.

CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT)
  PARTITION BY HASH(col1)
  PARTITIONS 5;

CREATE TABLE tbl2(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);

Insert test data into tbl1.

INSERT INTO tbl1 VALUES(1, 'A1', 1),(2, 'A2', 2),(3, 'A3', 3);

INSERT INTO tbl2 VALUES(1, 'A1', 1),(2, 'A2', 22),(3, 'A3', 33);

Query data from tbl1.

SELECT * FROM tbl1;

The return result is as follows:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | A1   |    1 |
|    2 | A2   |    2 |
|    3 | A3   |    3 |
+------+------+------+
3 rows in set (0.022 sec)

table_name.{* | column_name}: specifies all columns or a specific column in the specified table.
- table_name.*: specifies all columns in the specified table.
- table_name.column_name: specifies a specific column in the specified table.
  
  Here is an example:
  
  Query the col1 column from tbl1.
```
SELECT tbl1.col1 FROM tbl1;
```
  The return result is as follows:
```
+------+
| col1 |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.025 sec)
```
table_alias_name.{* | column_name}: similar to the previous format, but the column is selected using the table alias.
expr [[AS] column_alias_name]: specifies an expression and allows you to assign an alias to the selected expression as the column name.
- expr: specifies a column name, function, or calculation expression.
  - expr AS column_alias_name/expr column_alias_name: specifies the expression and assigns the alias column_alias_name to it.
Here is an example:

Query the col1 column from tbl1, calculate a new column col1_add with the value col1+1, and calculate a new column col3_mul with the value col3*2.
```
SELECT col1, col1+1 AS col1_add, col3*2 col3_mul FROM tbl1;
```
The return result is as follows:
```
+------+----------+----------+
| col1 | col1_add | col3_mul |
+------+----------+----------+
|    1 |        2 |        2 |
|    2 |        3 |        4 |
|    3 |        4 |        6 |
+------+----------+----------+
3 rows in set (0.027 sec)
```

from_list

DUAL: specifies a virtual table name. It is typically used to perform calculations or execute functions without an actual table.

Here is an example:

SELECT 1+1, SYSDATE() FROM DUAL;

The return result is as follows:

+------+---------------------+
| 1+1  | SYSDATE()           |
+------+---------------------+
|    2 | 2024-08-28 15:20:59 |
+------+---------------------+
1 row in set (0.009 sec)

table_references: specifies a list of table references. Multiple table references are separated by commas (,). It specifies the list of data source tables for the query, which can be a combination of one or more tables. For more information about the syntax of table_references, see JOIN clause.

where_condition

expression: specifies the condition expression used to filter the rows to be updated.

Here is an example:

Query all rows from tbl1 that satisfy the conditions col1 > 1 and col2 = 'A3'.

SELECT * FROM tbl1
  WHERE col1 > 1
  AND col2 = 'A3';

The return result is as follows:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    3 | A3   |    3 |
+------+------+------+
1 row in set (0.003 sec)

table_function

The LOCATION clause specifies the path where the external table files are stored. Usually, the data files of an external table are stored in a separate directory, which can contain subdirectories. When you create an external table, the system automatically collects all files in the directory.
- The format of the local LOCATION clause is LOCATION = '[file://] local_file_path', where local_file_path can be a relative or absolute path. If you specify a relative path, the current directory must be the installation directory of seekdb. The secure_file_priv clause specifies the file path that the OBServer node has permission to access. local_file_path must be a subpath of the secure_file_priv path.
- The format of the remote LOCATION clause is:
  - LOCATION = '{oss\|s3}://$ACCESS_ID:$ACCESS_KEY@$HOST：s3_region/remote_file_path', where $ACCESS_ID, $ACCESS_KEY, and $HOST are the access information required to access Alibaba Cloud OSS, AWS S3, and object storage compatible with the S3 protocol, and s3_region is the region information selected when using S3. These sensitive access information are stored in the system tables of the database in an encrypted manner.
  - LOCATION = hdfs://localhost:port/PATH, where localhost specifies the address of HDFS, port specifies the port number of HDFS, and PATH specifies the directory path in HDFS.
    - With Kerberos authentication: LOCATION = 'hdfs://localhost:port/user?principal=xxx&keytab=xxx&krb5conf=xxx&configs=xxx'. Where:
      - principal: specifies the user for login and authentication.
      - keytab: specifies the path of the key file for user authentication.
      - krb5conf: specifies the path of the description file for the Kerberos environment.
      - configs: specifies additional HDFS configuration items. By default, this parameter is empty. However, in a Kerberos environment, this parameter usually has a value and needs to be configured. For example, dfs.data.transfer.protection=authentication,privacy specifies the data transmission protection level as authentication and privacy.
The FORMAT clause specifies properties related to the file format. Supported file formats include CSV, PARQUET, and ORC.
- TYPE = 'CSV' specifies the following fields:
  - LINE_DELIMITER: specifies the line delimiter of the CSV file. The default value is LINE_DELIMITER='\n'.
  - FIELD_DELIMITER: an optional field that specifies the column delimiter of the CSV file. The default value is FIELD_DELIMITER='\t'.
  - ESCAPE: specifies the escape character of the CSV file. It can be only one byte. The default value is ESCAPE ='\'.
  - FIELD_OPTIONALLY_ENCLOSED_BY: an optional field that specifies the symbol for enclosing field values in the CSV file. The default value is empty. If you use this option, only fields of specific types (such as CHAR, VARCHAR, TEXT, and JSON) are enclosed.
  - ENCODING: specifies the character set encoding format of the file. If you do not specify this parameter, the default value is UTF8MB4.
  - NULL_IF: specifies the string to be treated as NULL. The default value is empty.
  - SKIP_HEADER: specifies the number of header rows to skip.
  - SKIP_BLANK_LINES: specifies whether to skip blank lines. The default value is FALSE, indicating that blank lines are not skipped.
  - TRIM_SPACE: specifies whether to remove leading and trailing spaces in fields of the file. The default value is FALSE, indicating that leading and trailing spaces in fields of the file are not removed.
  - EMPTY_FIELD_AS_NULL: specifies whether to treat empty strings as NULL. The default value is FALSE, indicating that empty strings are not treated as NULL.
  - PARSE_HEADER: specifies that the first row of the CSV file is directly obtained as the column names of each column.
    Notice
    PARSE_HEADER cannot be used with SKIP_HEADER, as they have conflicting semantics.
- TYPE = 'PARQUET/ORC' does not specify any additional fields.
The PATTERN clause specifies a regular expression pattern for filtering files in the LOCATION directory. For each file path in the LOCATION directory, if the file path matches the pattern, the external table accesses the file; otherwise, it skips the file. If you do not specify this parameter, the external table can access all files in the LOCATION directory by default.

For the ODPS format, data is not obtained through files, and there is no meaningful URL path. Therefore, only the source form of the table_function is supported.

TYPE = 'ODPS' specifies the following fields:
- ACCESSID: specifies the ID of the ODPS user.
- ACCESSKEY: specifies the password of the ODPS user.
- ENDPOINT: specifies the connection address of the ODPS service.
- TUNNEL_ENDPOINT: specifies the connection address of the Tunnel data transmission service.
- PROJECT_NAME: specifies the project where the queried table is located.
- SCHEMA_NAME: an optional field that specifies the schema of the queried table.
- TABLE_NAME: specifies the name of the queried table.
- QUOTA_NAME: an optional field that specifies whether to use the specified quota.
- COMPRESSION_CODE: an optional field that specifies the compression format of the data source. Supported compression formats include ZLIB, ZSTD, LZ4, and ODPS_LZ4. If you do not specify this parameter, compression is not enabled.

Here is an example:

Read data in the ODPS format.

SELECT * FROM
source (
  type = 'ODPS',
  accessid = '$ODPS_ACCESSID',
  accesskey = '$ODPS_ACCESSKEY',
  endpoint= '$ODPS_ENDPOINT',
  project_name = 'example_project',
  schema_name = '',
  table_name = 'example_table',
  quota_name = '',
  compression_code = ''
);

location_url

The FORMAT clause specifies properties related to the file format. Supported file formats include CSV, PARQUET, and ORC:
- For CSV files: configure parse_header=true/false to specify whether to parse the first row as column headers (default is false). Additionally, use TYPE=CSV to declare the file format (required when exporting).
- For PARQUET and ORC files: the file structure is automatically recognized, and no additional configuration for the header row is required.

Here is an example:

SELECT * FROM
FILES( location = '/data/',
      format (TYPE = 'csv', field_delimiter = ',', parse_header = true),
      pattern = 'datafiles$';

group_by_condition

expression [ASC | DESC]: specifies the grouping condition expression. Adding ASC or DESC controls the order of groups in the result set, specifying whether to sort them in ascending or descending order.

Here is an example:

Query col1 and col2 from table tbl1, group by col2, and sum col3, then output the result.

SELECT col1, col2, SUM(col3)
  FROM tbl1
  GROUP BY col2 DESC;

The result is as follows:

+------+------+-----------+
| col1 | col2 | SUM(col3) |
+------+------+-----------+
|    3 | A3   |         3 |
|    2 | A2   |         2 |
|    1 | A1   |         1 |
+------+------+-----------+
3 rows in set (0.017 sec)

group_by_summary_option

info

seekdb supports advanced grouping and summarizing operations.

GROUPING SETS: specifies a custom grouping method, allowing you to define multiple grouping combinations. It has the following characteristics:

You can flexibly define the grouping combinations as needed.
The result set includes only the grouping combinations specified by you.

info

GROUP BY GROUPING SETS ((col1), (col2), (col3)) is equivalent to GROUP BY GROUPING SETS (col1, col2, col3).

Here is an example:

SELECT col1, col2, col3, COUNT(*)
FROM tbl1
GROUP BY GROUPING SETS ((col1), (col2), (col3));

The returned result is as follows:

+------+------+------+----------+
| col1 | col2 | col3 | COUNT(*) |
+------+------+------+----------+
|    1 | NULL | NULL |        1 |
|    2 | NULL | NULL |        1 |
|    3 | NULL | NULL |        1 |
| NULL | A1   | NULL |        1 |
| NULL | A2   | NULL |        1 |
| NULL | A3   | NULL |        1 |
| NULL | NULL |    1 |        1 |
| NULL | NULL |    2 |        1 |
| NULL | NULL |    3 |        1 |
+------+------+------+----------+
9 rows in set (0.016 sec)

ROLLUP: specifies a hierarchical summarizing method. It reduces the number of grouping columns in the order of the grouping columns and generates multi-level summarizing results. It has the following characteristics:

It reduces the grouping columns in the order of the columns in expression_list until only the total row remains.
The result set includes:
- The detailed data of each group.
- The sub-summarized data of each group.
- The total row.

ROLLUP can be considered a special case of GROUPING SETS. For example:

GROUP BY ROLLUP(col1, col2, col3)

is equivalent to

GROUP BY GROUPING SETS ((col1, col2, col3), (col1, col2), (col1), ())

ROLLUP can also be used with GROUPING SETS. For example:

GROUP BY GROUPING SETS((col2), (col3), ROLLUP(col1, col2, col3))

is equivalent to

GROUP BY GROUPING SETS((col2), (col3), (col1, col2, col3), (col1, col2), (col1), ())

Here is an example:

SELECT col1, col2, col3, COUNT(*)
FROM tbl1
GROUP BY ROLLUP (col1, col2, col3);

The returned result is as follows:

+------+------+------+----------+
| col1 | col2 | col3 | COUNT(*) |
+------+------+------+----------+
|    1 | A1   |    1 |        1 |
|    1 | A1   | NULL |        1 |
|    1 | NULL | NULL |        1 |
|    2 | A2   |    2 |        1 |
|    2 | A2   | NULL |        1 |
|    2 | NULL | NULL |        1 |
|    3 | A3   |    3 |        1 |
|    3 | A3   | NULL |        1 |
|    3 | NULL | NULL |        1 |
| NULL | NULL | NULL |        3 |
+------+------+------+----------+
10 rows in set (0.109 sec)

CUBE: specifies a multi-dimensional summarizing method. It generates all possible grouping combinations. It has the following characteristics:

The detailed data of each group.
The summarized data of all possible grouping combinations.
The total row.

CUBE can be considered a special case of GROUPING SETS. For example:

GROUP BY CUBE(col1, col2, col3)

is equivalent to

GROUP BY GROUPING SETS ((col1, col2, col3), (col1, col2), (col1, col3), (col2, col3), (col1), (col2), (col3), ())

CUBE can also be used with GROUPING SETS. For example:

GROUP BY col1, CUBE(col2, col3), GROUPING SETS((col4), (col5))

is equivalent to

GROUP BY GROUPING SETS(
  (col1, col2, col3, col4), (col1, col2, col3, col5),
  (col1, col2, col4), (col1, col2, col5),
  (col1, col3, col4), (col1, col3, col5),
  (col1, col4), (col1, col5))

Here is an example:

SELECT col1, col2, col3, COUNT(*)
FROM tbl1
GROUP BY CUBE (col1, col2, col3);

The returned result is as follows:

+------+------+------+----------+
| col1 | col2 | col3 | COUNT(*) |
+------+------+------+----------+
| NULL | NULL | NULL |        3 |
| NULL | NULL |    1 |        1 |
| NULL | NULL |    2 |        1 |
| NULL | NULL |    3 |        1 |
| NULL | A1   | NULL |        1 |
| NULL | A2   | NULL |        1 |
| NULL | A3   | NULL |        1 |
| NULL | A1   |    1 |        1 |
| NULL | A2   |    2 |        1 |
| NULL | A3   |    3 |        1 |
|    1 | NULL | NULL |        1 |
|    2 | NULL | NULL |        1 |
|    3 | NULL | NULL |        1 |
|    1 | NULL |    1 |        1 |
|    2 | NULL |    2 |        1 |
|    3 | NULL |    3 |        1 |
|    1 | A1   | NULL |        1 |
|    2 | A2   | NULL |        1 |
|    3 | A3   | NULL |        1 |
|    1 | A1   |    1 |        1 |
|    2 | A2   |    2 |        1 |
|    3 | A3   |    3 |        1 |
+------+------+------+----------+
22 rows in set (0.113 sec)

having_condition

expression: specifies the condition expression for filtering the grouped results.

Here is an example:

Query the col1 and col2 columns from the tbl1 table, group the results by col2, and calculate the sum of col3. Return only the rows where the sum of col3 is less than 3.

SELECT col1, col2, SUM(col3)
  FROM tbl1
  GROUP BY col2
  HAVING SUM(col3) < 3;

The returned result is as follows:

+------+------+-----------+
| col1 | col2 | SUM(col3) |
+------+------+-----------+
|    1 | A1   |         1 |
|    2 | A2   |         2 |
+------+------+-----------+
2 rows in set (0.010 sec)

order_by_condition

expression [ASC | DESC]: specifies the condition expression for sorting the result set.

ASC | DESC: an optional parameter that specifies the sorting order. ASC indicates ascending order (default), and DESC indicates descending order.

Here is an example:

Query the data from the tbl1 table and output the query results in descending order (DESC) based on the col3 column.

SELECT * FROM tbl1
  ORDER BY col3 DESC;

The returned result is as follows:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    3 | A3   |    3 |
|    2 | A2   |    2 |
|    1 | A1   |    1 |
+------+------+------+
3 rows in set (0.012 sec)

limit_clause

[offset,] row_count: the parameter values must be integer constants. The following table describes the parameters.
- offset: an optional parameter that specifies the number of rows to skip from the start of the result set (offset). The default offset is 0.
- row_count: specifies the number of rows to return. If you specify only the row_count parameter, the first row_count rows of the result set are returned.
row_count OFFSET offset: specifies to skip offset rows from the start of the result set and return the next row_count rows.

Here is an example:

Query the data from the tbl1 table, force the query to start from the second row, and return two rows of data from the tbl1 table.

SELECT * FROM tbl1
  LIMIT 1, 2;

The returned result is as follows:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    2 | A2   |    2 |
|    3 | A3   |    3 |
+------+------+------+
2 rows in set (0.011 sec)

lock_option

FOR UPDATE [opt_for_update_wait]：This option specifies that exclusive locks are placed on all rows of the query result set to prevent concurrent modifications by other transactions or concurrent reads at certain transaction isolation levels. For more information about exclusive locks, see Lock query results with SELECT FOR UPDATE.
- opt_for_update_wait is an optional parameter that specifies the behavior for acquiring locks. The options are as follows:
  - WAIT {decimal | intnum}: Specifies the amount of time to wait for other transactions to release resources. If the time limit is exceeded, the resource cannot be obtained. The unit is seconds.
  - NOWAIT/NO_WAIT: Specifies not to wait for other transactions to release resources and to directly return that the resource cannot be obtained.
  - SKIP LOCKED: Specifies not to wait for row locks. The query is executed immediately, and locked rows are removed from the result set.
Here are some examples:
- You can use SELECT ... FOR UPDATE to add row-level locks to a table. If the LIMIT 1 clause is used, the optimizer will push the operator down to the table scan step, and only the rows returned by LIMIT will be locked.
```
SELECT * FROM tbl1
  LIMIT 1
  FOR UPDATE;
```
- If you use the ORDER BY clause to sort the query results, the results will be sorted first, and then LIMIT 1 will be executed. In this case, all selected rows will be locked.
```
SELECT * FROM tbl1
  ORDER BY col1
  LIMIT 1
  FOR UPDATE;
```
LOCK IN SHARE MODE: This option specifies that shared locks are placed on the data during the query to prevent other transactions from writing to the data, but allows other transactions to read the data. For more information about shared locks, see Lock query results with LOCK IN SHARE MODE.

Examples

Example of a simple table query

Create a table named test_tbl1.

CREATE TABLE test_tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);

Insert test data into the test_tbl1 table.

INSERT INTO test_tbl1 VALUES (1, 'A1', 10),(2, 'A2', 15),(3, 'A1', 8);

Query the col2 column in the test_tbl1 table and remove duplicates.

SELECT DISTINCT col2 FROM test_tbl1;

The returned result is as follows:

+------+
| col2 |
+------+
| A1   |
| A2   |
+------+
2 rows in set (0.014 sec)

Example of querying data by using dblink

Query the number_t table in the remote test database.

SELECT * FROM number_t@ob_dblink;

The returned result is as follows:

+--------+-------------+---------+---------------------+--------------------------------+
| c_int  | c_bigint    | c_float |    c_double         |   c_decimal                    |
+--------+-------------+---------+---------------------+--------------------------------+
| 214748 | 92233720368 | 3.40282 | 1.7976931348623157  | 123456789123456789.1234567890  |
+--------+-------------+---------+---------------------+--------------------------------+
1 row in set (0.013 sec)

Query the number_t table in the remote test database and perform a JOIN operation with the local number_t table.

SELECT a.c_decimal, b.c_double
  FROM number_t a, number_t@ob_dblink b
  WHERE a.c_int = b.c_int;

The returned result is as follows:

+--------------------------------+---------------------+
|   c_decimal                    |    c_double         |
+---------------------+--------------------------------+
| 123456789123456789.1234567890  |  1.7976931348623157 |
+---------------------+--------------------------------+
1 row in set (0.017 sec)

Query the datetime_t table in the remote mysql database.

SELECT * FROM mysql.datetime_t@ob_dblink;

The returned result is as follows:

+-------------+------------+---------+----------------------+----------------------+
| c_date      | c_time     | c_year  | c_datetime           |  c_timestamp         |
+-------------+------------+---------+-----------------------+---------------------+
|  2023-04-13 | 12:12:12   |  2078   | 2100-11-01 12:12:13  | 2100-12-01 21:14:15  |
+-------------+------------+---------+----------------------+----------------------+
1 row in set (0.015 sec)

References

Single-table query

Description​

Permissions​

Syntax​

Parameter description​

select_expr​

from_list​

where_condition​

table_function​

Notice

location_url​

group_by_condition​

group_by_summary_option​

having_condition​

order_by_condition​

limit_clause​

lock_option​

Examples​

Example of a simple table query​

Example of querying data by using dblink​

References​

Contents

Description

Permissions

Syntax

Parameter description

select_expr

from_list

where_condition

table_function

location_url

group_by_condition

group_by_summary_option

having_condition

order_by_condition

limit_clause

lock_option

Examples

Example of a simple table query

Example of querying data by using dblink

References