SELECTの構文は比較的複雑ですが、このセクションではSIMPLE SELECT構文について説明します。
説明
このステートメントは、テーブルまたはビューからデータを照会するために使用されます。
構文
simple_select:
SELECT [ hint_options ] [ DISTINCT | UNIQUE | ALL] select_expr_list
FROM from_list
[WHERE condition]
[hierarchical_query_clause]
[GROUP BY group_expression_list
[{ROLLUP | CUBE | GROUPING SETS} group_expression_list]
[HAVING condition]
]
[ORDER BY order_expression_list]
[FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]
[row_limiting_clause ]
select_expr_list:
table_name.*
| table_alias_name.*
| expr [[AS] column_alias_name]
| sequence_name.{ CURRVAL|NEXTVAL }@dblink_name
from_list:
table_reference [, table_reference...]
url_external_table_references
table_reference:
simple_table
| joined_table
| pivot_clause
| unpivot_clause
| table_name@dblink_name
url_external_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>'
)
}
simple_table:
(table_factor [partition_option])[table_alias_name]
| (select_stmt) table_alias_name
| (table_reference_list)
joined_table:
table_reference [INNER] JOIN simple_table [join_condition]
| table_reference outer_join_type JOIN simple_table join_condition
partition_option:
PARTITION (partition_name_list)
partition_name_list:
partition_name [, partition_name...]
outer_join_type:
{LEFT | RIGHT | FULL} [OUTER]
join_condition:
ON expression
condition:
expression
group_expression_list:
group_expression [, group_expression...]
group_expression:
expression [ASC | DESC]
order_expression_list:
order_expression [, order_expression...]
order_expression:
expression [ASC | DESC]
row_limiting_clause:
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
pivot_clause：
PIVOT
(aggregate_function ( expr ) [[AS] alias ]
[, aggregate_function ( expr ) [[AS] alias ]... ]
pivot_for_clause
pivot_in_clause
)
pivot_for_clause：
FOR { column| ( column [, column... ]) }
pivot_in_clause
IN
( { { expr| ( expr [, expr...] ) } [ [ AS] alias]... }
[, { { expr| ( expr [, expr...] ) } [ [ AS] alias] ...} ]
)
unpivot_clause ：
UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
( { column | ( column [, column... ]) }
pivot_for_clause
unpivot_in_clause
)
unpivot_in_clause：
IN
( { column | ( column [, column... ]) }[ AS { literal | ( literal [, literal... ]) } ]
[, { column | ( column [, column... ] ) }[ AS {literal | ( literal [, literal... ]) } ]]
)
hierarchical_query_clause:
[START WITH start_expression] CONNECT BY [NOCYCLE]
{PRIOR child_expr = parent_expr
| parent_expr = PRIOR child_expr} [ORDER SIBLINGS BY ...]
パラメータの説明
| フィールド | 説明 | |
|---|---|---|
| hint_options | Hintオプションを指定します。オプションです。 | |
| DISTINCT | UNIQUE | ALL | クエリ対象のデータには、重複する行が含まれる場合があります。
|
|
| select_expr_list | データベースから照会する式または列名をカンマ（,）で区切って指定します。「*」はすべての列を表します。
|
|
| FROM table_references | データを選択するオブジェクトを指定します。 | |
| url_external_table_references | オプションで、URLを介して外部テーブルデータを直接読み取ります。現在のURL外部テーブルは2種類の構文をサポートしており、詳細については以下を参照してください：
|
|
| PARTITION(partition_list) | クエリ対象のテーブルのパーティション情報を指定します。例：partition(p0,p1...)。 |
|
| table_factor | テーブル名（ベーステーブル、更新可能なビュー）、特殊なサブクエリなどを指定し、直接クエリ関数をサポートします。 | |
| table_alias_name | データオブジェクトのエイリアスを指定します。 | |
| joined_table | 複数テーブルクエリの結合方法を指定します。
|
|
| ON expression | 複数テーブル結合の結合条件を指定します。 | |
| WHERE where_conditions | 筛選条件を指定します。クエリ結果には条件を満たすデータのみが含まれます。オプションです。where_conditionsは式です。 |
|
| hierarchical_query_clause | オプションで、階層クエリオプションを指定します。詳細については、以下のhierarchical_query_clauseを参照してください。 | |
| GROUP BY group_by_list | グループ化するフィールドを指定します。通常は集計関数と組み合わせて使用します。 説明 SELECT句の後ろの列に集計関数を使用していない場合、グループ化クエリでは、SELECT句の後ろの列はGROUP BY句に含まれていなければなりません。 |
|
| ROLLUP group_expression_list | Group Byのグループを統合し、統計値を生成します。 | |
| CUBE group_expression_list | 式リスト内のすべての項目の各パーミュレーションに基づいて生成されるグループで集計を実行し、GROUP BY のグループを統合して統計値を生成します。説明：
|
|
| GROUPING SETS group_expression_list | 1つのクエリで複数のデータグループを指定し、それぞれに統計値を生成して、指定されたグループの統計値を集計して表示します。GROUPING SETS では、単一のフィールドまたはフィールドリストを指定できます。 |
|
| HAVING search_confitions | グループ化後の各グループデータをフィルタリングします。HAVING 句は WHERE 句と似ていますが、HAVING 句では累積関数（SUM、AVG など）を使用できます。 |
|
| ORDER BY order_list | 結果セットを1列または複数の列に基づいて ASC または DESC で表示するように指定します。ASC または DESC を指定しない場合、デフォルトは ASC です。
|
|
| row_limiting_clause | クエリが返す行数を制限し、ページネーション機能を実現します。オフセットと、返す行数または行の割合を指定できます。ORDER BY 句と組み合わせてソート順序を指定することで、一貫した結果を得ることができます。 |
|
| OFFSET | ページネーションクエリの開始前にスキップする行数を指定します。offset は数値または数値として計算される式である必要があります。
|
|
| ROW | ROWS | 単一行または複数行を表し、行数に応じてキーワードを選択することで、意味の明確さを保証できます。 | |
| FETCH | 返す行数または行の割合を指定します。この句が指定されていない場合、offset+1 から始まるすべての行が返されます。 |
|
| FIRST | NEXT | 最初に返される行数または行の割合、または次に返される行数または行の割合を表します。 | |
| rowcount | percent PERCENT | rowcount を使用して返す行数を指定します。rowcount は数値または数値として計算される式でなければなりません。負の数を指定した場合、rowcount は 0 と見なされます。rowcount が rowcount+1 から始まる利用可能な行数より大きい場合、利用可能なすべての行が返されます。rowcount に小数部が含まれている場合、その小数部は切り捨てられます。rowcount が NULL の場合、0 行が返されます。percent PERCENT を使用して、返す指定された行の総数の割合を指定します。percent は数値または数値として計算される式でなければなりません。負の数を指定した場合、percent は 0 と見なされます。percent が NULL の場合、0 行が返されます。rowcount または percent が指定されていない場合、1 行が返されます。 |
|
| ONLY | WITH TIES | ONLY を指定して、指定された行数または行の割合を返します。WITH TIES を指定して、前回取得した行と同じソートキーを持つ他の行を返します。WITH TIES を使用する場合は、ORDER BY 句を指定する必要があります。ORDER BY 句が指定されていない場合、他の行は返されません。 |
|
| FOR UPDATE | オプション。クエリ結果のすべての行に排他的ロックをかけて、他のトランザクションによる並行変更を防ぐか、特定のトランザクション分離レベルでの並行読み取りを防ぎます。
|
|
| pivot_clause | 行を列に回転させる句。
注意
|
|
| aggregate_function | 集約関数を指定します。 | |
| expr | 計算結果が定数値となる式を指定します。 pivot_in_clause は定数式のみをサポートします。 |
|
| unpivot_clause | 列を行に回転させる句。
注意
|
|
| dblink_name | アクセスするデータベースリンク（つまり dblink）の名前を指定します。 | |
| sequence_name | dblinkを使用して、OceanBaseデータベースやOracleデータベースなどのリモートデータベース内のシーケンス値にアクセスします。これには、SEQUENCE オブジェクトの NEXTVAL および CURRVAL 値の計算が含まれます。 |
|
hierarchical_query_clause
階層クエリでは、SELECT ステートメントの列に特殊な擬似列（pseudo column）LEVEL を使用して、階層を示すことができます。これはノードの階層を表します。クエリの開始点から数えて、開始点は1となり、それ以降も同様に続きます。このフィールドは階層クエリでのみ使用できます。階層クエリにおける擬似列の詳細については、階層クエリの擬似列を参照してください。
START WITH start_expression：オプションで、階層クエリ内のルート行（Root Row）を指定します。CONNECT BY：親子関係をどのように決定するかを指定します。通常は等価式を使用しますが、他の式もサポートされています。NOCYCLE：このキーワードを指定すると、結果セットにサイクルが含まれていても返却可能になり、CONNECT_BY_ISCYCLE仮想列を使用してサイクルが発生した箇所を指定できます。指定しない場合、クライアントにエラーが返されます。PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr：PRIORは単項演算子であり、パラメータ内の列が親行（Parent Row）から来ていることを示します。単項の + および - と同じ優先順位を持ちます。ORDER SIBLINGS BY：同一階層の行間の並べ替え順序を指定します。
注意
階層クエリに FOR UPDATE 句が含まれている場合、以下の使用シナリオはサポートされません：
- サブクエリで
DISTINCTキーワードまたは集計が使用されている場合、FOR UPDATEと一緒に使用することはできません。 - 共通テーブル式（Common Table Expressions、CTE）を含むシナリオはサポートされていません。つまり、
WITH ... AS ...句を含むSELECTクエリはFOR UPDATEと同時に使用することはできません。
階層クエリの使用方法の詳細については、階層クエリを参照してください。
table_function
LOCATION句は外部テーブルファイルの保存パスを指定するために使用されます。通常、外部テーブルのデータファイルは個別のディレクトリに保存され、そのフォルダ内にはサブディレクトリを含むことができます。テーブル作成時に、外部テーブルはそのディレクトリ内のすべてのファイルを自動的に収集します。
- ローカルLOCATIONの形式は
LOCATION = '[file://] local_file_path'であり、ここでlocal_file_pathは相対パスまたは絶対パスにすることができます。相対パスを指定する場合、現在のディレクトリはOceanBaseデータベースのインストールディレクトリである必要があります。secure_file_privはOBServerノードがアクセス権を持つファイルパスを設定するために使用されます。local_file_pathはsecure_file_privパスのサブパスにすることができます。 - リモートLOCATIONの形式は
LOCATION = '{oss\|s3}://$ACCESS_ID:$ACCESS_KEY@$HOST：s3_region/remote_file_path'であり、ここで$ACCESS_ID、$ACCESS_KEY、$HOSTはOSS、COS、S3へのアクセス時に設定が必要なアクセス情報であり、s3_regionはS3を使用する際に選択するリージョン情報です。これらの機密性の高いアクセス情報は暗号化された形でデータベースのシステムテーブルに保存されます。
- ローカルLOCATIONの形式は
FORMAT句は、読み取りファイル形式に関連するプロパティを指定するために使用され、CSV/PARQUET/ORCの3種類のファイル形式をサポートしています。
- TYPE = 'CSV' の場合、以下のフィールドが含まれます：
LINE_DELIMITER：CSVファイルの行区切り文字を指定します。デフォルトはLINE_DELIMITER='\n'です。FIELD_DELIMITER：オプション。CSVファイルの列区切り文字を指定します。デフォルトはFIELD_DELIMITER='\t'です。ESCAPE：CSVファイルのエスケープ文字を指定します。1バイトのみとなります。デフォルトはESCAPE ='\'です。FIELD_OPTIONALLY_ENCLOSED_BY：オプション。CSVファイル内のフィールド値を囲む記号を指定します。デフォルトは空です。このオプションを使用すると、一部のタイプのフィールド（CHAR、VARCHAR、TEXT、JSONなど）にのみ囲み文字が付加されます。ENCODING：ファイルの文字セットエンコーディング形式を指定します。指定しない場合、デフォルト値はUTF8MB4です。NULL_IF：NULLとして扱う文字列を指定します。デフォルト値は空です。SKIP_HEADER：ファイルヘッダーをスキップし、スキップする行数を指定します。SKIP_BLANK_LINES：空白行をスキップするかどうかを指定します。デフォルト値はFALSEであり、空白行をスキップしないことを意味します。TRIM_SPACE：ファイル内のフィールドの先頭と末尾のスペースを削除するかどうかを指定します。デフォルト値はFALSEであり、ファイル内のフィールドの先頭と末尾のスペースを削除しないことを意味します。EMPTY_FIELD_AS_NULL：空の文字列をNULLとして扱うかどうかを指定します。デフォルト値はFALSEであり、空の文字列をNULLとして扱わないことを意味します。PARSE_HEADER:この構成を指定すると、CSVファイルの最初の行の情報を直接取得し、それを各列の列名として使用します。注意
PARSE_HEADERとSKIP_HEADERは同時に使用できません。意味上の競合が生じます。
- TYPE = 'PARQUET/ORC' の場合、他の追加フィールドはありません。
- TYPE = 'CSV' の場合、以下のフィールドが含まれます：
PATTERN句は、LOCATIONディレクトリ下のファイルをフィルタリングするために使用される正規パターン文字列を指定します。各LOCATIONディレクトリ下のファイルパスについて、このパターン文字列とマッチする場合、外部テーブルはそのファイルにアクセスし、そうでない場合はそのファイルをスキップします。このパラメータを指定しない場合、デフォルトではLOCATIONディレクトリ下のすべてのファイルにアクセスできます。
ODPS形式の場合、データの取得方法がファイルを介さず、意味のあるURLパスも存在しないため、table_functionの形式としてはsourceのみがサポートされています。
- TYPE = 'ODPS' には、以下のフィールドが含まれます：
ACCESSID：ODPSユーザーのIDを指定します。ACCESSKEY：ODPSユーザーのパスワードを指定します。ENDPOINT：ODPSサービスの接続アドレスを指定します。TUNNEL_ENDPOINT：Tunnelデータ転送サービスの接続アドレスを指定します。PROJECT_NAME：照会対象のテーブルが属するProjectを指定します。SCHEMA_NAME：オプション。照会対象のテーブルのSCHEMAを指定します。TABLE_NAME：照会対象のテーブル名を指定します。QUOTA_NAME：オプション。指定されたQuotaを使用するかどうかを選択します。COMPRESSION_CODE：オプション。データソースの圧縮形式を指定します。ZLIB、ZSTD、LZ4、ODPS_LZ4の4種類の圧縮形式をサポートしており、設定しない場合は圧縮が有効になりません。
location_url
FORMAT句は、読み取りファイルの形式に関連するプロパティを指定するために使用されます。CSV、PARQUET、ORC の3種類のファイル形式をサポートしています。そのうち、CSVファイルではparse_headerを設定してファイルのヘッダー行を解析するかどうかを指定し、TYPE を使用してエクスポートファイルの形式を指定する必要があります。ODPSのデータについては、source句を使用する必要があります。クエリ例は以下のとおりです：
SELECT * FROM FILES( location = '/data/', format (TYPE = 'csv', field_delimiter = ',', parse_header = true), pattern = 'datafiles$';
例
テーブル
tbl1からnameデータを読み取ります。obclient> CREATE TABLE tbl1 (id INT,name VARCHAR(10),num INT); Query OK, 0 rows affected obclient> INSERT INTO tbl1 VALUES (1, 'a',100),(2, 'b',200),(3, 'a',50); Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0 obclient> SELECT * FROM tbl1; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | | 2 | b | 200 | | 3 | a | 50 | +------+------+------+ 3 rows in set obclient> SELECT name FROM tbl1; +------+ | NAME | +------+ | a | | b | | a | +------+ 3 rows in setテーブル
tbl1からnameデータを読み取り、重複処理を行います。obclient> SELECT DISTINCT name FROM tbl1; +------+ | NAME | +------+ | a | | b | +------+ 2 rows in setテーブル
tbl1からid、name、numを照会し、num列を2で割って出力します。出力される列名はavgです。obclient> SELECT id, name, num/2 AS avg FROM tbl1; +------+------+------+ | ID | NAME | AVG | +------+------+------+ | 1 | a | 50 | | 2 | b | 100 | | 3 | a | 25 | +------+------+------+ 3 rows in setテーブル
tbl1からフィルター条件name = 'a'に基づいて、対応するid、name、numを出力します。obclient> SELECT id, name, num FROM tbl1 WHERE name = 'a'; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | | 3 | a | 50 | +------+------+------+ 2 rows in setテーブル
tbl1からnameを照会し、nameでグループ化してnumの合計を求め、numの合計が160より小さい行を照会して出力します。obclient> SELECT name,SUM(num) sum FROM tbl1 GROUP BY name HAVING SUM(num) < 160; +------+------+ | NAME | SUM | +------+------+ | a | 150 | +------+------+ 1 row in setテーブル
tbl1からid、name、numを照会し、numに基づいて照会結果を昇順で出力します。obclient> SELECT * FROM tbl1 ORDER BY num ASC; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 3 | a | 50 | | 1 | a | 100 | | 2 | b | 200 | +------+------+------+ 3 rows in setテーブル
tbl1からすべての列を照会し、nameに基づいて降順、numに基づいて昇順で照会結果を出力します。obclient> SELECT * FROM tbl1 ORDER BY name DESC,num ASC; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 2 | b | 200 | | 3 | a | 50 | | 1 | a | 100 | +------+------+------+ 3 rows in setテーブル
tbl1から指定されたidの行を照会し、FOR UPDATE句を使用して照会結果の行をロックします。/* セッション1でテーブルtbl1のid=1の行を照会してロックする */ obclient> SELECT * FROM tbl1 WHERE id=1 FOR UPDATE; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | +------+------+------+ 1 row in set /* セッション2でテーブルtbl1のid=1またはid=2の行を照会してロックする */ obclient> SELECT * FROM tbl1 WHERE id=1 or id=2 FOR UPDATE; OBE-30006: resource busy; acquire with WAIT timeout expired obclient> SELECT * FROM tbl1 WHERE id=1 or id=2 FOR UPDATE SKIP LOCKED; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 2 | b | 200 | +------+------+------+ 1 row in setテーブル
group_tbl1を作成し、データを挿入します。CUBEを含むGROUP BYクエリステートメントを実行します。obclient> CREATE TABLE group_tbl1 (group_id INT,job VARCHAR2(10),name VARCHAR2(10),salary INT); Query OK, 0 rows affected obclient> INSERT INTO group_tbl1 VALUES(10,'Coding','Bruce',1000), (10,'Programmer','Clair',1000), (20,'Coding','Jason',2000), (20,'Programmer','Joey',2000), (30,'Coding','Rebecca',3000), (30,'Programmer','Rex',3000); Query OK, 6 rows affected Records: 6 Duplicates: 0 Warnings: 0 obclient> SELECT * FROM group_tbl1; +----------+------------+---------+--------+ | GROUP_ID | JOB | NAME | SALARY | +----------+------------+---------+--------+ | 10 | Coding | Bruce | 1000 | | 10 | Programmer | Clair | 1000 | | 20 | Coding | Jason | 2000 | | 20 | Programmer | Joey | 2000 | | 30 | Coding | Rebecca | 3000 | | 30 | Programmer | Rex | 3000 | +----------+------------+---------+--------+ 6 rows in set obclient> SELECT group_id, salary, SUM(salary) FROM group_tbl1 GROUP BY CUBE (group_id, salary); +----------+--------+-------------+ | GROUP_ID | SALARY | SUM(SALARY) | +----------+--------+-------------+ | NULL | NULL | 12000 | | NULL | 1000 | 2000 | | NULL | 2000 | 4000 | | NULL | 3000 | 6000 | | 10 | NULL | 2000 | | 20 | NULL | 4000 | | 30 | NULL | 6000 | | 10 | 1000 | 2000 | | 20 | 2000 | 4000 | | 30 | 3000 | 6000 | +----------+--------+-------------+ 10 rows in setテーブル
tbl1からnameとnumでグループ化して照会し、各グループ内の数を集計します。obclient> SELECT name, num, COUNT(*) from tbl1 GROUP BY GROUPING SETS(name, num); +------+------+----------+ | NAME | NUM | COUNT(*) | +------+------+----------+ | a | NULL | 2 | | b | NULL | 1 | | NULL | 100 | 1 | | NULL | 200 | 1 | | NULL | 50 | 1 | +------+------+----------+ 5 rows in setテーブル
emp_phoneの行を列に変換し、その後列を行に変換します。obclient> CREATE TABLE emp(name VARCHAR2(50), num CHAR, phone VARCHAR2(50)); Query OK, 0 rows affected obclient> INSERT INTO emp VALUES('ZhangSan', '1', '1234-5678'),('ZhangSan', '2', '3219-6066'),('ZhangSan', '3', '5365-9583'); Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0 obclient> SELECT * FROM emp; +----------+------+-----------+ | NAME | NUM | PHONE | +----------+------+-----------+ | ZhangSan | 1 | 1234-5678 | | ZhangSan | 2 | 3219-6066 | | ZhangSan | 3 | 5365-9583 | +----------+------+-----------+ 3 rows in set /* テーブルempの行を列に変換する */ obclient> SELECT * FROM emp PIVOT(MAX(phone) FOR num IN (1 AS home, 2 AS office, 3 AS mobile)); +----------+-----------+-----------+-----------+ | NAME | HOME | OFFICE | MOBILE | +----------+-----------+-----------+-----------+ | ZhangSan | 1234-5678 | 3219-6066 | 5365-9583 | +----------+-----------+-----------+-----------+ 1 row in set /* テーブルempの列を行に変換する */ obclient> CREATE VIEW v_emp AS SELECT * FROM emp PIVOT(MAX(phone) FOR num IN (1 AS home, 2 AS office, 3 AS mobile)); Query OK, 0 rows affected obclient> SELECT * FROM v_emp UNPIVOT(phone FOR num IN (home AS 1, office AS 2, mobile AS 3)); +----------+-----+-----------+ | NAME | NUM | PHONE | +----------+-----+-----------+ | ZhangSan | 1 | 1234-5678 | | ZhangSan | 2 | 3219-6066 | | ZhangSan | 3 | 5365-9583 | +----------+-----+-----------+ 3 rows in setリモートデータベースのテーブルからデータを照会します。
/* リモートのOceanBaseデータベースにアクセスしてデータを照会する */ obclient> SELECT ID FROM tbl2@ob_dblink; +------+ | ID | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set obclient> SELECT * FROM tbl2@ob_dblink; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | | 2 | b | 200 | | 3 | a | 50 | +------+------+------+ 3 rows in set /* リモートのOracleデータベースにアクセスしてデータを照会する */ obclient> SELECT ID FROM tbl2@ora_dblink; +------+ | ID | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set obclient> SELECT * FROM tbl2@ora_dblink; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | | 2 | b | 200 | | 3 | a | 50 | +------+------+------+ 3 rows in set /* ローカルデータベースとリモートデータベースのデータを同時に照会する */ obclient> SELECT t4.col1,t5.col2 FROM tbl1 t4, tbl2@ob_dblink t5 WHERE t1.col3=t2.col3; /* 異なるリモートデータベースのデータを同時に照会する */ obclient> SELECT * FROM tbl2@ob_dblink t_remote1,tbl2@ora_dblink t_remote2 WHERE t_remote1.col1 = t_remote2.col1;ページネーションクエリの例
従業員番号
empnoが最も低い3人の従業員を照会します。obclient> CREATE TABLE emp( empno NUMBER(4,0), empname VARCHAR(10), job VARCHAR(9), mgr NUMBER(4,0), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2,0), CONSTRAINT PK_emp PRIMARY KEY (empno) ); Query OK, 0 rows affected obclient> INSERT INTO emp VALUES (1839,'KING','PRESIDENT',null,'17-DEC-81',5000,null,10) ,(1698,'BLAKE','MANAGER',1839,'01-MAY-81',2850,null,30) ,(1782,'CLARK', 'MANAGER', 1839, '09-JUN-81', 2450, null, 10) ,(1566,'JONES','MANAGER',1839, '02-APR-81',2975,null,40) ,(1788,'SCOTT','ANALYST',1566, '15-JUL-87',3000,null,20) ,(1902,'FORD','ANALYST',1566, '05-OCT-81',3000,null,40) ,(1369,'SMITH','CLERK',1902, '17-NOV-80',800,null,20) ,(1499,'ALLEN','SALESMAN',1698, '20-FEB-81',1600,300,30); Query OK, 8 rows affected Records: 8 Duplicates: 0 Warnings: 0 obclient> SELECT empno, empname FROM emp ORDER BY empno FETCH FIRST 3 ROWS ONLY; +-------+---------+ | EMPNO | EMPNAME | +-------+---------+ | 1369 | SMITH | | 1499 | ALLEN | | 1566 | JONES | +-------+---------+ 3 rows in set従業員番号
empnoが最も低い3人の従業員をさらに照会します。obclient> SELECT empno, empname FROM emp ORDER BY empno FETCH NEXT 3 ROWS ONLY;最低賃金の従業員のうち上位25％の従業員を照会します。
obclient> SELECT empno, empname sal FROM emp ORDER BY sal FETCH FIRST 25 PERCENT ROWS ONLY; +-------+-------+ | EMPNO | SAL | +-------+-------+ | 1499 | ALLEN | | 1698 | BLAKE | +-------+-------+ 2 rows in set最低賃金の従業員のうち上位25％の従業員、および前の例で取得した最後の従業員の給与と同じすべての他の従業員を照会します。
obclient> SELECT empno, empname sal FROM emp ORDER BY sal FETCH FIRST 25 PERCENT ROWS WITH TIES; +-------+-------+ | EMPNO | SAL | +-------+-------+ | 1499 | ALLEN | | 1698 | BLAKE | +-------+-------+ 2 rows in set
テーブル
t1のデータを照会し、テーブルエイリアスを指定します。obclient> CREATE TABLE t1 (c1 INT, c2 INT ); Query OK, 0 rows affected obclient> INSERT INTO t1 VALUES ('1','2'); Query OK, 1 row affected obclient> SELECT * FROM (t1) a; +------+------+ | C1 | C2 | +------+------+ | 1 | 2 | +------+------+ 1 row in setRANDOM()を使用してランダム数を生成し、FROM句の後に直接GENERATOR()関数を照会します。obclient> SELECT RANDOM(4) FROM GENERATOR(3); +---------------------+ | RANDOM(4) | +---------------------+ | 5267436225003336391 | | -851690886662571060 | | 1738617244330437274 | +---------------------+ 3 rows in setリモートデータベースのシーケンス値にアクセスします。
- ローカルOceanBaseデータベースにログインし、リモートOceanBaseデータベースのOracleテナントのdlinkを作成します。
obclient> CREATE DATABASE LINK seq_link CONNECT TO test@oracle IDENTIFIED BY test HOST '127.xxx.xxx.xxx:2828'; Query OK, 0 rows affected- リモートのOceanBaseデータベースで、シーケンス
my_seqを作成します。
obclient> CREATE SEQUENCE my_seq START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30; Query OK, 0 rows affected- ローカルからリモートOceanBaseデータベースのシーケンス値にアクセスします。
obclient> SELECT my_seq.NEXTVAL@seq_link FROM DUAL; +---------+ | NEXTVAL | +---------+ | 1 | +---------+ 1 row in set obclient> SELECT my_seq.CURRVAL@seq_link FROM DUAL; +---------+ | CURRVAL | +---------+ | 1 | +---------+ 1 row in set obclient> SELECT my_seq.NEXTVAL@seq_link FROM DUAL; +---------+ | NEXTVAL | +---------+ | 3 | +---------+ 1 row in set