本記事では、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
注意
データベースの文字セットがASCIIの場合、1つの漢字には2つの _ が必要です。文字セットがGBKの場合は、1つの _ だけが必要です。
区間クエリ（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 句で複数の値を指定し、それらを集合として扱うことができます。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,'Zhang San',13.11,Date'2019-01-01'), (4,1011,'Zhang San',22.21,Date'2020-01-01'), (6,1011,'Zhang San',58.83,Date'2020-02-02'), (2,1022,'Li Si',23.34,Date'2019-02-02'), (3,1022,'Li Si',12.22,Date'2019-03-03'), (7,1022,'Li Si',14.66,Date'2021-03-03'), (8,1022,'Li Si',34.44,Date'2021-04-04'), (5,1033,'Wang Wu',51.55,Date'2020-05-05'), (9,1033,'Wang Wu',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を参照してください。サブクエリの詳細については、サブクエリを参照してください。
クエリ文の最適化の詳細については、クエリのリライトの概要を参照してください。