本記事では、SQLステートメントを使用してOceanBaseデータベースで単一テーブルのクエリ操作を実行する方法について説明します。
前提条件
- OceanBaseデータベースのOracleモードテナントに接続されていること。データベースへの接続操作の詳細については、接続方法の概要を参照してください。
- 既に
SELECT権限を保有していること。現在のユーザー権限を確認するための操作情報については、ユーザー権限の確認を参照してください。この権限を持っていない場合は、管理者に連絡して権限を付与してもらってください。ユーザー権限の付与に関する操作情報については、直接権限付与を参照してください。
構文
SELECT ステートメントを使用してデータのクエリを実行してください。
SELECT ステートメントの単一テーブルクエリにおける一般的な構造は次のとおりです：
SELECT [ALL | DISTINCT] select_list FROM table_name
[ WHERE query_condition ]
[ GROUP BY group_by_expression ]
[ HAVING group_condition ]
[ ORDER BY column_list ][ASC | DESC]
[ Row_Limiting_Clause ]
column_list：
column_name[,column_name...]
パラメータの説明：
| パラメータ | 説明 | |
|---|---|---|
| select_list | 対象となる列のリスト。列名、式、集約関数などを指定できます。複数の列を指定する場合は、カンマ（,）で区切ります。 | |
| table_name | データを照会する対象のテーブル名。 | |
| WHERE query_condition | オプションのパラメータで、検索条件を指定するために使用します。条件に合致する行のみが返されます。 | |
| GROUP BY group_by_condition | オプションのパラメータで、指定された列に基づいて結果をグループ化します。集約関数と併用するのが一般的です。 | |
| HAVING group_condition | オプションのパラメータで、グループ化後の結果セットをフィルタリングし、条件を満たすグループのみを返します。 | |
| ORDER BY column_list | オプションのパラメータで、結果セットをソートします。1列または複数の列を指定してソートすることができます。 | |
| ASC | DESC | オプションパラメータで、ソートの順序を指定します。ASCは昇順（デフォルト）、DESCは降順を表します。 |
| LIMIT limit_clause | オプションのパラメータで、クエリで返す行数を制限し、ページネーションクエリ機能を実装します。オフセットと、返す行数または行のパーセンテージを指定できます。ORDER BY句を組み合わせてソート順序を指定することで、一貫性のある結果を得ることができます。 | |
| column_list | 検索する列を指定するパラメータで、単一の列または複数の列をカンマ（,）で区切って指定できます。 | |
| column_name | 検索する列名。 |
SELECTキーワードの実行順序の概要
WHERE、GROUP BY、HAVING、ORDER BY といったキーワードを同時に使用する場合、その順番には明確な制限があります。キーワードの実行順序は以下のとおりです：
FROMを実行してテーブルを検索します。WHEREを実行して制約条件を指定します。GROUP BYを実行して取得された各レコードをグループ化します。GROUP BYがなければ、全体が1つのグループとして扱われます。HAVINGを実行してグループ化された結果をフィルタリングします。SELECTを実行します。DISTINCTを実行して重複を削除します。ORDER BYを実行して結果を条件に基づいて昇順または降順にソートします。ROWNUMを実行して結果の表示件数を制限します。
注意
WHERE と HAVING の違いは、WHERE はグループ化前にデータをフィルタリングし、HAVING はグループ化後の結果をフィルタリングし、最終的にSQL全体のクエリ結果を返します。
テストテーブルの作成とテストデータの追加
テーブル
deptを作成します。CREATE TABLE dept( deptno NUMBER(2,0), dname VARCHAR(14), location VARCHAR(13), CONSTRAINT pk_dept PRIMARY KEY(deptno) );deptテーブルに6件のデータを追加します。INSERT INTO dept VALUES (20,'Finance','beijing'), (35,'Administration','hangzhou'), (40,'Development','xian'), (30,'Workshop','guangzhou'), (25,'Legal affairs','shanghai'), (45,'Office','suzhou');テーブル
empを作成します。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), age NUMBER(2,0), CONSTRAINT PK_emp PRIMARY KEY (empno), CONSTRAINT FK_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno) );empテーブルに6件のデータを挿入します。INSERT INTO emp VALUES (1369,'SMITH','CLERK',1902,DATE'1980-12-17',800.00,NULL,20,22), (1499,'ALLEN','SALESMAN',1698,DATE'1981-02-20',1600.00,300.00,35,22), (1566,'JONES','MANAGER',1839,DATE'1981-04-02',2975.00, NULL,40,22), (1698,'BLAKE' ,'MANAGER',1839,DATE'1981-05-01',2850.00,NULL ,30,33), (1788,'SCOTT','ANALYST',1566,DATE'1987-07-15',3000.00,NULL ,25,33), (1902,'FORD','ANALYST',1566,DATE'1981-12-05',3000.00, NULL,45,22);
基本クエリ
すべての列のクエリ
* は、テーブル内のすべてのフィールドを返すことを意味します。例：
obclient [SYS]> SELECT * FROM emp;
実行結果は次のとおりです：
+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | AGE |
+-------+---------+----------+------+-----------+------+------+--------+------+
| 1369 | SMITH | CLERK | 1902 | 17-DEC-80 | 800 | NULL | 20 | 22 |
| 1499 | ALLEN | SALESMAN | 1698 | 20-FEB-81 | 1600 | 300 | 35 | 22 |
| 1566 | JONES | MANAGER | 1839 | 02-APR-81 | 2975 | NULL | 40 | 22 |
| 1698 | BLAKE | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL | 30 | 33 |
| 1788 | SCOTT | ANALYST | 1566 | 15-JUL-87 | 3000 | NULL | 25 | 33 |
| 1902 | FORD | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL | 45 | 22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
6 rows in set
これは、次のものと等価です：
obclient [SYS]> SELECT empname,empno,job,mgr,hiredate,sal,comm,deptno FROM emp;
実行結果は次のとおりです：
+---------+-------+----------+------+-----------+------+------+--------+
| EMPNAME | EMPNO | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+---------+-------+----------+------+-----------+------+------+--------+
| SMITH | 1369 | CLERK | 1902 | 17-DEC-80 | 800 | NULL | 20 |
| ALLEN | 1499 | SALESMAN | 1698 | 20-FEB-81 | 1600 | 300 | 35 |
| JONES | 1566 | MANAGER | 1839 | 02-APR-81 | 2975 | NULL | 40 |
| BLAKE | 1698 | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL | 30 |
| SCOTT | 1788 | ANALYST | 1566 | 15-JUL-87 | 3000 | NULL | 25 |
| FORD | 1902 | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL | 45 |
+---------+-------+----------+------+-----------+------+------+--------+
6 rows in set
クエリでテーブルエイリアスを指定する
テーブル emp のデータを照会し、テーブルエイリアス t を指定します。
obclient [SYS]> SELECT t.* FROM (emp) t;
実行結果は次のとおりです：
+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | AGE |
+-------+---------+----------+------+-----------+------+------+--------+------+
| 1369 | SMITH | CLERK | 1902 | 17-DEC-80 | 800 | NULL | 20 | 22 |
| 1499 | ALLEN | SALESMAN | 1698 | 20-FEB-81 | 1600 | 300 | 35 | 22 |
| 1566 | JONES | MANAGER | 1839 | 02-APR-81 | 2975 | NULL | 40 | 22 |
| 1698 | BLAKE | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL | 30 | 33 |
| 1788 | SCOTT | ANALYST | 1566 | 15-JUL-87 | 3000 | NULL | 25 | 33 |
| 1902 | FORD | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL | 45 | 22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
6 rows in set
指定した列の取得
obclient [SYS]> SELECT empname,deptno FROM emp;
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH | 20 |
| ALLEN | 35 |
| JONES | 40 |
| BLAKE | 30 |
| SCOTT | 25 |
| FORD | 45 |
+---------+--------+
6 rows in set
クエリ時に列にエイリアスを設定する
obclient [SYS]> SELECT empname AS 従業員名、deptno AS 部署番号 FROM emp。
実行結果は次のとおりです：
+--------------+--------------+
| 従業員名 | 部署番号 |
+--------------+--------------+
| SMITH | 20 |
| ALLEN | 35 |
| JONES | 40 |
| BLAKE | 30 |
| SCOTT | 25 |
| FORD | 45 |
+--------------+--------------+
6 rows in set
クエリ時に重複する値を持つ行を除外する
obclient [SYS]> SELECT DISTINCT age FROM emp;
実行結果は次のとおりです：
+------+
| AGE |
+------+
| 22 |
| 33 |
+------+
2 rows in set
クエリ時に ROWNUM を使用して、返される行数を制限できる
obclient [SYS]> SELECT empname, deptno FROM emp WHERE ROWNUM <= 3;
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH | 20 |
| ALLEN | 35 |
| JONES | 40 |
+---------+--------+
3 rows in set
計算された値のクエリ
obclient [SYS]> SELECT empname, sal-100,job FROM emp;
実行結果は次のとおりです：
+---------+---------+----------+
| EMPNAME | SAL-100 | JOB |
+---------+---------+----------+
| SMITH | 700 | CLERK |
| ALLEN | 1500 | SALESMAN |
| JONES | 2875 | MANAGER |
| BLAKE | 2750 | MANAGER |
| SCOTT | 2900 | ANALYST |
| FORD | 2900 | ANALYST |
+---------+---------+----------+
6 rows in set
クエリ内で指定した列に関数を適用できます。以下の例では、job を小文字で表すように要求されています。詳細については、クエリ内での演算子と関数の使用を参照してください。
obclient [SYS]> SELECT empname, sal-100, LOWER(job) FROM emp;
実行結果は次のとおりです：
+---------+---------+------------+
| EMPNAME | SAL-100 | LOWER(JOB) |
+---------+---------+------------+
| SMITH | 700 | clerk |
| ALLEN | 1500 | salesman |
| JONES | 2875 | manager |
| BLAKE | 2750 | manager |
| SCOTT | 2900 | analyst |
| FORD | 2900 | analyst |
+---------+---------+------------+
6 rows in set
FROM関数を使ったクエリ
OceanBaseデータベースのOracleモードでは、SELECT * FROM FUNCTION(*); の構文を使用して、テーブル形式のデータを返す関数を呼び出すことができます。関数の戻り値はテーブル形式として扱われ、そのまま照会することが可能です。
SELECT * FROM FUNCTION(*); を使用する場合、以下の点に注意が必要です：
関数が返すデータ型はテーブル型でなければなりません。
テーブル形式関数が返す列の数およびデータ型は、呼び出し元が要求する列の数およびデータ型と完全に一致する必要があります。
テーブル形式関数が返す行数は、呼び出し元が要求する行数以上である必要があります。
関数のパラメータの数とデータ型は、関数定義と一致する必要があります。
より多くのカスタムタイプの情報については、CREATE TYPE および ユーザー定義サブタイプを参照してください。
関数を作成する方法の詳細については、CREATE FUNCTION および 関数の作成を参照してください。
例：
テーブル形式の型
emp_typeを定義します。obclient [SYS]> DELIMITER // obclient [SYS]> CREATE OR REPLACE TYPE emp_type AS OBJECT (id NUMBER); // Query OK, 0 rows affectedテーブル形式の型を含むセット型
emp_type_listを定義します。obclient [SYS]> CREATE OR REPLACE TYPE emp_type_list IS TABLE OF emp_type; // Query OK, 0 rows affectedテーブル形式を返す関数
get_emp_infoを作成します。obclient [SYS]> CREATE OR REPLACE FUNCTION get_emp_info RETURN emp_type_list PIPELINED IS CURSOR emp_list_cursor IS SELECT EMPNO FROM emp; v_emp_id_type emp_type; v_emp_id varchar2(5); BEGIN OPEN emp_list_cursor; LOOP FETCH emp_list_cursor INTO v_emp_id; EXIT WHEN emp_list_cursor%notfound; v_emp_id_type := emp_type(v_emp_id); PIPE ROW(v_emp_id_type); END LOOP; CLOSE emp_list_cursor; RETURN; END;// Query OK, 0 rows affected obclient [SYS]> DELIMITER ;関数が返される結果を照会します。
obclient [SYS]> SELECT * FROM get_emp_info();実行結果は次のとおりです：
+------+ | ID | +------+ | 1369 | | 1499 | | 1566 | | 1698 | | 1788 | | 1902 | +------+ 6 rows in set
データのフィルタリング
特定の条件を満たすデータを照会する場合、SELECT クエリステートメントに WHERE 句を追加してデータのフィルタリングを行うことができます。WHERE 句の後には、1つ以上の条件を含めることができ、これらの条件によってデータがフィルタリングされ、WHERE 条件を満たすデータのみが返されます。特定ニーズに応じて、柔軟にクエリ条件を活用することで、ターゲットデータをフィルタリングおよび検索できます。
WHERE 句を使用する際には、条件が正しく、適切な演算子を使用していることを確認する必要があります。
WHERE 句でよく使われるクエリ条件は以下のテーブルのとおりです。
| クエリ条件タイプ | 述語 |
|---|---|
| 比較クエリ | =，>，<，>=，<=，!=，<> |
| 論理クエリ（複数条件） | AND、OR、NOT |
| あいまいクエリ（文字列マッチング） | LIKE、NOT LIKE |
| 区間クエリ（範囲指定） | BETWEEN AND、NOT BETWEEN AND |
| 指定集合クエリ | IN、NOT IN |
| NULL値クエリ | IS NULL、IS NOT NULL |
比較条件クエリ
等しい（=）
指定した列の値が、特定の値と等しいデータを取得します。値が文字列タイプの場合は、シングルクォートまたはダブルクォートで囲む必要があります。
obclient [SYS]> SELECT empname, deptno FROM emp WHERE deptno = 30;
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| BLAKE | 30 |
+---------+--------+
1 row in set
obclient [SYS]> SELECT empname, deptno FROM emp WHERE empname = 'ALLEN';
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN | 35 |
+---------+--------+
1 row in set
不等号（<> と !=）
不等号には <> と != 2種類の書き方が含まれます。一般的なフォーマットは次のとおりです：
SELECT column_name [,column_name...] FROM table_name WHERE column_name <> const_value;
SELECT column_name [,column_name...] FROM table_name WHERE column_name != const_value;
例：
obclient> SELECT empname, deptno FROM emp WHERE deptno <> 30;
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH | 20 |
| ALLEN | 35 |
| JONES | 40 |
| SCOTT | 25 |
| FORD | 45 |
+---------+--------+
5 rows in set
obclient> SELECT empname, deptno FROM emp WHERE deptno != 30;
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH | 20 |
| ALLEN | 35 |
| JONES | 40 |
| SCOTT | 25 |
| FORD | 45 |
+---------+--------+
5 rows in set
大なり（>）および小なり（<）
大なり（>）および小なり（<）は、数値の大小を比較するために使用されます。文字を比較する場合、ASCIIコードに対応する値で比較し、文字の対応する位置を順番に比較します。一般的なフォーマットは次のとおりです：
SELECT column_name [,column_name...] FROM table_name WHERE column_name < const_value;
SELECT column_name [,column_name...] FROM table_name WHERE column_name > const_value;
説明
大なりイコール（\>=）および小なりイコール（\<=）も同様です。
例：
obclient> SELECT empname, deptno FROM emp WHERE deptno > 30;
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN | 35 |
| JONES | 40 |
| FORD | 45 |
+---------+--------+
3 rows in set
obclient> SELECT empname, deptno FROM emp WHERE deptno >= 30;
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN | 35 |
| JONES | 40 |
| BLAKE | 30 |
| FORD | 45 |
+---------+--------+
4 rows in set
obclient> SELECT empname, deptno FROM emp WHERE deptno < 30;
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH | 20 |
| SCOTT | 25 |
+---------+--------+
2 rows in set
obclient> SELECT empname, deptno FROM emp WHERE deptno <= 30;
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH | 20 |
| BLAKE | 30 |
| SCOTT | 25 |
+---------+--------+
3 rows in set
論理条件クエリ
論理クエリ演算子である（AND）および（OR）は、複数の条件によるクエリをサポートします。
かつ（AND）
AND の2つの条件を満たすデータを返します。一般的なフォーマットは次のとおりです：
SELECT column_name [,column_name...] FROM table_name WHERE
query_condition AND query_condition;
例：
obclient> SELECT empname, deptno, sal FROM emp WHERE deptno<=30 AND sal > 1000;
実行結果は次のとおりです：
+---------+--------+------+
| EMPNAME | DEPTNO | SAL |
+---------+--------+------+
| BLAKE | 30 | 2850 |
| SCOTT | 25 | 3000 |
+---------+--------+------+
2 rows in set
または（OR）
いずれかの一方の条件を満たすデータはすべて返されます。一般的なフォーマットは次のとおりです：
SELECT column_name [,column_name...] FROM table_name WHERE
query_condition OR query_condition;
例：
obclient> SELECT empname, deptno, sal FROM emp WHERE deptno <= 30 OR sal > 1000;
実行結果は次のとおりです：
+---------+--------+------+
| EMPNAME | DEPTNO | SAL |
+---------+--------+------+
| SMITH | 20 | 800 |
| ALLEN | 35 | 1600 |
| JONES | 40 | 2975 |
| BLAKE | 30 | 2850 |
| SCOTT | 25 | 3000 |
| FORD | 45 | 3000 |
+---------+--------+------+
6 rows in set
あいまいクエリ（LIKE）
LIKE 述語は、文字列のマッチングに使用できます。一般形式は次のとおりです：
[NOT] LIKE pattern
構文の意味は、指定されたプロパティの列の値が pattern とマッチするタプルを照会することです。pattern は完全な文字列である場合もあれば、ワイルドカード % と _ を含む場合もあります。その中で：
アンダースコア
_は任意の1文字にマッチします。パーセント記号
%は、値の中の0文字以上の任意の文字列にマッチします。パターン%はNULLとマッチしません。
説明
OceanBaseデータベースのOracleモードでは、以下の条件を満たす場合、like が = 比較に変更されます:
patternは%または_のワイルドカードを含まず、escapeも適用されていません。- フィールドタイプは
LOBではありません。Oracleモードでは、LOBタイプは=比較をサポートしていないため、変換できません。 patternは、固定長のcharまたはncharタイプではありません。
以下の例は、従業員名の最初の4文字が ALLE で、最後の1文字が任意の文字であるデータを照会するものです。
obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'ALLE_';
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN | 35 |
+---------+--------+
1 row in set
以下の例は、従業員名の最初の文字が A であるデータを照会するものです。
obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'A%';
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN | 35 |
+---------+--------+
1 row in set
区間クエリ（BETWEEN AND）
演算子 BETWEEN ... AND は、2つの値の間にあるデータを抽出します。これらの値は、数値、テキスト、または日付にすることができます。一般的なフォーマットは次のとおりです：
SELECT column_name [,column_name...] FROM table_name WHERE
[NOT] BETWEEN min_const_value AND max_const_value;
注意
区間クエリの2つの境界値の順序を入れ替えないでください。必ず「左側の値以上、かつ右側の値以下」という条件になります。
例：
obclient> SELECT * FROM emp WHERE sal BETWEEN 2000 AND 2999;
実行結果は次のとおりです：
+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | AGE |
+-------+---------+---------+------+-----------+------+------+--------+------+
| 1566 | JONES | MANAGER | 1839 | 02-APR-81 | 2975 | NULL | 40 | 22 |
| 1698 | BLAKE | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL | 30 | 33 |
+-------+---------+---------+------+-----------+------+------+--------+------+
2 rows in set
指定集合クエリ（IN）
IN 演算子は、WHERE 句で複数の値を指定でき、これらを1つの集合として扱います。IN は、指定された列のデータが集合内のいずれかの値と一致すれば、そのデータが返されることを意味します。NOT IN は、集合内のいずれの値とも一致しないデータを返すことを意味します。一般的なフォーマットは次のとおりです：
SELECT column_name [,column_name...] FROM table_name WHERE
column_name [NOT] IN (const_value,const_value,const_value...);
注意
[NOT] INの集合内の値のタイプは、すべて一致しているか、互換性がある必要があります。[NOT] IN集合内の値はワイルドカードをサポートしません。
例：
obclient> SELECT * FROM emp WHERE deptno IN (30,40,50,60);
実行結果は次のとおりです：
+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | AGE |
+-------+---------+---------+------+-----------+------+------+--------+------+
| 1566 | JONES | MANAGER | 1839 | 02-APR-81 | 2975 | NULL | 40 | 22 |
| 1698 | BLAKE | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL | 30 | 33 |
+-------+---------+---------+------+-----------+------+------+--------+------+
2 rows in set
NULL値専用クエリ（IS NULL / IS NOT NULL）
比較演算子、LIKE、BETWEEN AND、IN、NOT IN は、NULL 値に対するクエリ結果が正確ではないため、NULL値専用のクエリステートメント IS NULL および IS NOT NULL を使用することを推奨します。
IS NULL
IS NULL は、指定された列の値が NULL であるデータを照会するために使用されます。一般的なフォーマットは次のとおりです：
SELECT column_name [,column_name...] FROM table_name WHERE
column_name IS NULL;
例：
obclient> SELECT * FROM emp WHERE comm IS NULL;
実行結果は次のとおりです：
+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | AGE |
+-------+---------+---------+------+-----------+------+------+--------+------+
| 1369 | SMITH | CLERK | 1902 | 17-DEC-80 | 800 | NULL | 20 | 22 |
| 1566 | JONES | MANAGER | 1839 | 02-APR-81 | 2975 | NULL | 40 | 22 |
| 1698 | BLAKE | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL | 30 | 33 |
| 1788 | SCOTT | ANALYST | 1566 | 15-JUL-87 | 3000 | NULL | 25 | 33 |
| 1902 | FORD | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL | 45 | 22 |
+-------+---------+---------+------+-----------+------+------+--------+------+
5 rows in set
IS NOT NULL
IS NOT NULL は、指定された列の値が NOT NULL のデータを照会するために使用されます。一般的なフォーマットは次のとおりです：
SELECT column_name [,column_name...] FROM table_name WHERE
column_name IS NOT NULL;
例：
obclient> SELECT * FROM emp WHERE comm IS NOT NULL;
実行結果は次のとおりです：
+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | AGE |
+-------+---------+----------+------+-----------+------+------+--------+------+
| 1499 | ALLEN | SALESMAN | 1698 | 20-FEB-81 | 1600 | 300 | 35 | 22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
1 row in set
グループ化クエリ
ORDER BY句は、クエリ結果を1つ以上のプロパティ列に基づいて昇順（ASC）または降順（DESC）で並べ替えるために使用されます。デフォルトは昇順です。
GROUP BY は単一フィールドのグループ化と複数フィールドのグループ化をサポートしています。WHERE 句を使用すると、グループ化の前にデータをフィルタリングできます。HAVING 句を使用すると、グループ化後にデータをフィルタリングできます。ORDER BY 句を使用すると、グループ化後にデータをソートできます。
グループでよく使われる集約関数は以下の表のとおりです。
| 集約関数 | 機能 |
|---|---|
| MAX() | 指定された列の最大値を照会します。 |
| MIN() | 指定された列の最小値を照会します。 |
| COUNT() | クエリ結果の行数を集計します。 |
| SUM() | 指定された列の合計を返します。 |
| AVG() | 指定された列のデータの平均値を返します。 |
グループ化クエリ例
サンプルテーブル
fruit_orderを作成します。CREATE TABLE fruit_order( order_id Number(10,2), user_id Number(10,2), user_name VARCHAR2(16), fruit_price Number(10,2), order_year Date, PRIMARY KEY (order_id) );適切なテストデータを追加します。
INSERT INTO fruit_order(order_id,user_id,user_name,fruit_price,order_year) VALUES (1,1011,'張三',13.11,Date'2019-01-01'), (4,1011,'張三',22.21,Date'2020-01-01'), (6,1011,'張三',58.83,Date'2020-02-02'), (2,1022,'李四',23.34,Date'2019-02-02'), (3,1022,'李四',12.22,Date'2019-03-03'), (7,1022,'李四',14.66,Date'2021-03-03'), (8,1022,'李四',34.44,Date'2021-04-04'), (5,1033,'王五',51.55,Date'2020-05-05'), (9,1033,'王五',63.66,Date'2021-06-06');
単一フィールドによるグループ化クエリ
各顧客の注文数を照会し、顧客IDと注文数を出力します。
obclient [SYS]> SELECT user_id 顧客ID, COUNT(order_id) 注文数 FROM fruit_order GROUP BY user_id;
実行結果は次のとおりです：
+----------+--------------+
| 顧客ID | 注文数 |
+----------+--------------+
| 1011 | 3 |
| 1022 | 4 |
| 1033 | 2 |
+----------+--------------+
3 rows in set
複数フィールドによるグループ化クエリ
各顧客が毎年行った注文数を照会し、顧客ID、注文年、注文数を出力します。
obclient [SYS]> SELECT user_id 顧客ID, order_year 注文年, COUNT(order_id) 注文数 FROM fruit_order GROUP BY user_id,order_year;
実行結果は次のとおりです：
+----------+--------------+--------------+
| 顧客ID | 注文年 | 注文数 |
+----------+--------------+--------------+
| 1011 | 01-JAN-19 | 1 |
| 1022 | 02-FEB-19 | 1 |
| 1022 | 03-MAR-19 | 1 |
| 1011 | 01-JAN-20 | 1 |
| 1033 | 05-MAY-20 | 1 |
| 1011 | 02-FEB-20 | 1 |
| 1022 | 03-MAR-21 | 1 |
| 1022 | 04-APR-21 | 1 |
| 1033 | 06-JUN-21 | 1 |
+----------+--------------+--------------+
9 rows in set
グループ化前のフィルタリング
2020年の各顧客の注文数を照会し、顧客IDと注文数を出力します。
obclient [SYS]> SELECT user_id 顧客ID, COUNT(order_id) 注文数 FROM fruit_order t WHERE t.order_year = '01-JAN-20' GROUP BY user_id;
実行結果は次のとおりです：
+----------+--------------+
| 顧客ID | 注文数 |
+----------+--------------+
| 1011 | 1 |
+----------+--------------+
1 row in set
グループ化後のフィルタリング
2019年に1件以上の注文があった顧客を照会し、顧客IDと注文数を出力します。
obclient [SYS]> SELECT user_id 顧客ID, COUNT(order_id) 注文数 FROM fruit_order t WHERE t.order_year = '01-JAN-19' GROUP BY user_id HAVING COUNT(order_id) >= 1;
実行結果は次のとおりです：
+----------+--------------+
| 顧客ID | 注文数 |
+----------+--------------+
| 1011 | 1 |
+----------+--------------+
1 row in set
グループ化後のソート
各顧客の注文の最大金額を照会し、その顧客IDと最大金額を金額の降順で出力します。
obclient [SYS]> SELECT user_id 顧客ID, MAX(fruit_price) 最大金額 FROM fruit_order t GROUP BY user_id ORDER BY 最大金額 DESC;
実行結果は次のとおりです：
+----------+--------------+
| 顧客ID | 最大金額 |
+----------+--------------+
| 1033 | 63.66 |
| 1011 | 58.83 |
| 1022 | 34.44 |
+----------+--------------+
3 rows in set
グループ集計
GROUP BY CUBE 句を使用して、fruit_order テーブルを user_id でグループ化し、グループ化結果を集計します。
obclient [SYS]> SELECT user_id,SUM(FRUIT_PRICE) FROM fruit_order GROUP BY CUBE(user_id);
実行結果は次のとおりです：
+---------+------------------+
| USER_ID | SUM(FRUIT_PRICE) |
+---------+------------------+
| NULL | 294.02 |
| 1011 | 94.15 |
| 1022 | 84.66 |
| 1033 | 115.21 |
+---------+------------------+
4 rows in set
より多くのグループ化された情報については、SIMPLE SELECTを参照してください。
ソートクエリ
ORDER BY句は、クエリ結果を1つ以上のプロパティ列に基づいて昇順（ASC）または降順（DESC）で並び替えるために使用されます。デフォルトは昇順です。
2019年に1件以上の注文があった顧客を照会し、顧客IDと注文数を出力します。
obclient [SYS]> SELECT user_id,USER_NAME,SUM(FRUIT_PRICE) FROM fruit_order GROUP BY CUBE(user_id,USER_NAME);
実行結果は次のとおりです：
+---------+-----------+------------------+
| USER_ID | USER_NAME | SUM(FRUIT_PRICE) |
+---------+-----------+------------------+
| NULL | NULL | 294.02 |
| NULL | 張三 | 94.15 |
| NULL | 李四 | 84.66 |
| NULL | 王五 | 115.21 |
| 1011 | NULL | 94.15 |
| 1022 | NULL | 84.66 |
| 1033 | NULL | 115.21 |
| 1011 | 張三 | 94.15 |
| 1022 | 李四 | 84.66 |
| 1033 | 王五 | 115.21 |
+---------+-----------+------------------+
10 rows in set
単一フィールドソートクエリ
deptno を昇順で従業員情報を表示する
obclient [SYS]> SELECT empname, deptno FROM emp ORDER BY deptno;
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH | 20 |
| SCOTT | 25 |
| BLAKE | 30 |
| ALLEN | 35 |
| JONES | 40 |
| FORD | 45 |
+---------+--------+
6 rows in set
deptno を降順で従業員情報を表示する
obclient [SYS]> SELECT empname, deptno FROM emp ORDER BY deptno DESC;
実行結果は次のとおりです：
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| FORD | 45 |
| JONES | 40 |
| ALLEN | 35 |
| BLAKE | 30 |
| SCOTT | 25 |
| SMITH | 20 |
+---------+--------+
6 rows in set
複数フィールドソートクエリ
deptno を昇順で、sal を降順で、従業員情報を表示します。
obclient [SYS]> SELECT empname, deptno, sal FROM emp ORDER BY deptno ASC,sal DESC;
実行結果は次のとおりです：
+---------+--------+------+
| EMPNAME | DEPTNO | SAL |
+---------+--------+------+
| SMITH | 20 | 800 |
| SCOTT | 25 | 3000 |
| BLAKE | 30 | 2850 |
| ALLEN | 35 | 1600 |
| JONES | 40 | 2975 |
| FORD | 45 | 3000 |
+---------+--------+------+
6 rows in set
WHERE句の後にソートを行う
ORDER BY は WHERE の後に記述してソートを行うことができます。例：
obclient [SYS]> SELECT empname, deptno,sal, HIREDATE FROM emp WHERE sal>=1000 ORDER BY HIREDATE;
実行結果は次のとおりです：
+---------+--------+------+-----------+
| EMPNAME | DEPTNO | SAL | HIREDATE |
+---------+--------+------+-----------+
| ALLEN | 35 | 1600 | 20-FEB-81 |
| JONES | 40 | 2975 | 02-APR-81 |
| BLAKE | 30 | 2850 | 01-MAY-81 |
| FORD | 45 | 3000 | 05-DEC-81 |
| SCOTT | 25 | 3000 | 15-JUL-87 |
+---------+--------+------+-----------+
5 rows in set
NLSSORT関数を使用してソートを行なう
NLSSORT 関数を使用することで、中国語のピンイン順や画数順などでソートすることができます。
NLSSORT 関数に関する詳細情報は、NLSSORTを参照してください。
ピンイン順にソートする
obclient [SYS]> SELECT empname, deptno,sal FROM emp ORDER BY NLSSORT(empname,'NLS_SORT=UCA0900_SCHINESE_PINYIN');
実行結果は次のとおりです：
+---------+--------+------+
| EMPNAME | DEPTNO | SAL |
+---------+--------+------+
| ALLEN | 35 | 1600 |
| BLAKE | 30 | 2850 |
| FORD | 45 | 3000 |
| JONES | 40 | 2975 |
| SCOTT | 25 | 3000 |
| SMITH | 20 | 800 |
+---------+--------+------+
6 rows in set
クエリでRow_Limiting_Clauseを使用する
Row_Limiting_Clauseは、SELECT クエリの戻り行数を制限するために使用され、ページネーション処理によく利用されます。
N行以降のデータを照会する
例1
4行以降のデータを照会します。
obclient> SELECT * FROM tb OFFSET 4 ROW;
実行結果は次のとおりです：
+------+------+------+
| ID | NAME | NUM |
+------+------+------+
| 5 | b | 700 |
| 6 | a | 80 |
+------+------+------+
2 rows in set
例2
5行以降のデータを照会します。
obclient> SELECT * FROM tb OFFSET 5 ROWS;
実行結果は次のとおりです：
+------+------+------+
| ID | NAME | NUM |
+------+------+------+
| 6 | a | 80 |
+------+------+------+
1 row in set
idが最も低い上位3件のデータを照会する
obclient> SELECT * FROM tb ORDER BY id FETCH FIRST 3 ROWS ONLY;
実行結果は次のとおりです：
+------+------+------+
| ID | NAME | NUM |
+------+------+------+
| 1 | a | 100 |
| 2 | b | 200 |
| 3 | a | 50 |
+------+------+------+
3 rows in set
idが最も低い上位2件のデータを照会する
obclient> SELECT id, name FROM tb ORDER BY id FETCH NEXT 2 ROWS ONLY;
実行結果は次のとおりです：
+------+------+
| ID | NAME |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set
numの上位30%に該当するデータを照会する
obclient> SELECT id, name,num FROM tb ORDER BY num
FETCH FIRST 30 PERCENT ROWS ONLY;
実行結果は次のとおりです：
+------+------+------+
| ID | NAME | NUM |
+------+------+------+
| 3 | a | 50 |
+------+------+------+
1 row in set
numが最も低いデータのうち、上位30%のデータを取得し、かつ前の例で取得した最後のデータと同じ値を持つその他すべてのデータも照会する
obclient> SELECT id, name,num FROM tb ORDER BY num FETCH FIRST 30 PERCENT ROWS WITH TIES;
実行結果は次のとおりです：
+------+------+------+
| ID | NAME | NUM |
+------+------+------+
| 3 | a | 50 |
+------+------+------+
1 row in set
関連ドキュメント
SELECT構文に関する詳細情報は、SELECTを参照してください。サブクエリに関する詳細については、サブクエリを参照してください。
クエリステートメントの最適化に関する詳細については、クエリ・リライトの概要を参照してください。