基本的なSQL構文一覧（CRUD、テーブル結合、ウィンドウ関数、実行計画、実行順序、チューニングなど）

2023年12月18日月曜日

DB・テーブルの作成や定義変更、制約についてはこちらで書きました。

INSERT　登録

テーブルにレコードを挿入する。

カラム名を指定せずに登録されている順に値を入れていく。

INSERT INTO　テーブル名　VALUES （値1,値2,...）;

カラム名を指定して値を入れる。

INSERT INTO　テーブル名　VALUES （値1,値2,...）;

SELECT　検索・検索条件系

テーブルからレコードを取得する。

テーブル名のidが1のデータを全カラム取得する。

SELECT * FROM テーブル名 WHERE id = '1';

ORDER BY

特定のカラムを昇順・降順で並べる。

テーブル名のidが1のデータを全カラム取得し、カラム1を昇順（小さいもの順）で表示する。

SELECT * FROM テーブル名 WHERE id = '1'　ORDER BY カラム1 ASC;

末尾のASCで昇順、DESCを入力すると降順で表示できます。どちらも記入しないと昇順で表示されます。

DISTINCT

指定したカラムの重複を省いて表示

カラム1が被ってるものは省いて、全カラム1を表示する

SELECT DISTINCT　カラム1 FROM テーブル名;

LIMIT,OFFSET

指定した行数だけ取り出す

テーブル名のレコードを3行取り出す。

SELECT * FROM テーブル名　LIMIT 3;

テーブル名のレコードを1行飛ばして3行取り出す。

SELECT * FROM テーブル名　LIMIT 1,3;

SELECT * FROM テーブル名　LIMIT 3　OFFSET 1;

ROWS ONLY

Oracleの場合、LIMITは使えないので、下記のように書きます。

SELECT * FROM テーブル名
ORDER BY カラム名
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY;

Oracle 12c 以降では、OFFSET 句を省略しても動作します。

SELECT * FROM テーブル名
ORDER BY カラム名
FETCH FIRST 3 ROWS ONLY;

IS NULL, IS NOT NULL

NULLを条件に指定する。

idがNULLのデータを取得する。

SELECT * FROM テーブル名　WHERE id IS NULL;

idがNULLでないデータを取得する。

SELECT * FROM テーブル名　WHERE id IS NOT NULL;

NULLの評価と注意点

NULLは「IS NULL」「IS NOT NULL」以外の「＝」や「比較演算子」で使うと結果がNULLになります。

条件式	結果
`NULL = NULL`	NULL
`NULL <> 0`	NULL
`NULL IS NULL`	true

また、「IN、NOT IN」「ANY, ALL」などの対象にNULLが1件でもあると結果もNULLになってしまいます。

・AND条件

	TRUE	NULL	FALSE
TRUE	TRUE	NULL	FALSE
NULL	NULL	NULL	FALSE
FALSE	FALSE	FALSE	FALSE

・OR条件

	TRUE	NULL	FALSE
TRUE	TRUE	TRUE	TRUE
NULL	TRUE	NULL	NULL
FALSE	TRUE	NULL	FALSE

BETWEEN, NOT BETWEEN

範囲内を指定してレコードを取り出す。

idが3以上7以下のデータを取り出す

SELECT * FROM テーブル名　WHERE id BETWEEN 3 AND 7;

idが3未満、または7より大きいデータを取り出す

SELECT * FROM テーブル名　WHERE id NOT BETWEEN 3 AND 7;

LIKE, NOT LIKE

曖昧検索、パターンを指定する。

カラム1が「渡辺」で始まるデータを取得する

SELECT * FROM テーブル名　WHERE カラム1 LIKE '渡辺％';

カラム1が「太郎」で終わるデータを取得する

SELECT * FROM テーブル名　WHERE カラム1 LIKE '％太郎';

カラム1に「渡」が含まれるデータを取得する

SELECT * FROM テーブル名　WHERE カラム1 LIKE '％渡％';

カラム1が「あ〇」（2文字）のデータを取得する

SELECT * FROM テーブル名　WHERE カラム1 LIKE 'あ_';

IN, NOT IN, ANY, ALL

複数の条件を比べてデータを取得する。

カラム1が1,4,8のいずれかのデータを取得する。

SELECT * FROM テーブル名　WHERE カラム1 IN （'1', '4', '8');

カラム1が1,4,8でないデータを取得する。

SELECT * FROM テーブル名　WHERE カラム1 NOT IN （'1', '4', '8');

カラム1が1,4,8のいずれかと比較演算子で比べて真のデータを取得する。

SELECT * FROM テーブル名　WHERE カラム1　比較演算子 ANY （'1', '4', '8');

カラム1が1,4,8の全てと比較演算子で比べて真のデータを取得する。

SELECT * FROM テーブル名　WHERE カラム1　比較演算子 ALL  （'1', '4', '8');

CASE

条件に応じて表示する値を変える。

カラム1が値1なら表示値1、値2なら表示値2、それ以外なら表示値3を取得する。

SELECT
CASE　カラム1
	WHEN　'値1' THEN '表示値1'
	WHEN　'値2' THEN '表示値2'
	ELSE '表示値3'
END
FROM テーブル名;

カラム1が値1なら表示値1、NULLなら表示値2を取得する。

SELECT
CASE　
	WHEN　カラム1 = '値1' THEN '表示値1'
	WHEN　カラム1 IS NULL THEN '表示値2'
END
FROM テーブル名;

UNION, UNION ALL

和集合を取得する

テーブル1とテーブル2の和集合の結果を表示する。(重複する行は1つにまとめる)

SELECT　* FROM テーブル1
UNION
SELECT　* FROM テーブル2;

テーブル1とテーブル2の和集合の結果を表示する。（重複する行は重複したまま取り出す）

SELECT　* FROM テーブル1
UNION　ALL
SELECT　* FROM テーブル2;

EXCEPT, MINUS

差集合を取得する

テーブル1からテーブル2を除いた差集合を表示する。

SELECT　* FROM テーブル1
EXCEPT
SELECT　* FROM テーブル2;

SELECT　* FROM テーブル1
MINUS
SELECT　* FROM テーブル2;

INTERSECT

積集合を取得する

テーブル1とテーブル2の重複する積集合を表示する。

SELECT　* FROM テーブル1
INTERSECT
SELECT　* FROM テーブル2;

GROUP BY

特定の列で行をグループ化し、グループごとの集計を求める。

カラム1ごとに行をグループ化し、各グループの行数（COUNT）を計算する。

SELECT カラム1, COUNT(*)　FROM テーブル1　GROUP BY column1;

HAVING

グループ化したものに条件を指定する。

カラム1の値が2回以上現れるグループを抽出する。

SELECT カラム1, COUNT(*)　FROM テーブル1　GROUP BY column1　HAVING COUNT(*) > 1;

EXISTS, NOT EXISTS

サブクエリの結果でも共通する結果を返す。

EXISTS：積集合を取得する

サブクエリの結果に共通するテーブル1の結果を取得する

SELECT *　FROM テーブル1WHERE
EXISTS (
  SELECT 1
  FROM テーブル2
  WHERE テーブル2.id = テーブル1.id
    AND テーブル2.location = 'Japan'
);

NOT EXISTS：差集合を取得する

サブクエリの結果に共通しないテーブル1の結果を取得する

SELECT *　FROM テーブル1WHERE
NOT EXISTS (
  SELECT 1
  FROM テーブル2
  WHERE テーブル2.id = テーブル1.id
    AND テーブル2.location = 'Japan'
);

UPDATE　更新

テーブルのレコードを更新する。

テーブル名内のidが1のデータに、カラム1を〇〇にカラム2を△△に更新する

UPDATE　テーブル名　SET カラム1 = 〇〇, カラム2 = '△△'　WHERE id = '1';

DELETE　削除

テーブルのレコードを削除する。

テーブル名内のidが1のデータを削除する

DELETE FROM　テーブル名　WHERE id = '1';

TRUNCATE　削除

テーブルのレコードを削除する。

テーブル名内のidが1のデータを削除する

DELETE FROM　テーブル名　WHERE id = '1';

結合系

例で使用するテーブル

・テーブル1

id	name
1	Alice
2	Bob
3	Charlie
4	David

・テーブル2

id	amount
1	1000
2	2000
3	3000

INNER JOIN, JOIN(内部結合)

テーブル間で結合し、結合条件を満たす行のみが結果に表示されます。

SELECT テーブル1.id, テーブル2.name
FROM テーブル1
INNER JOIN テーブル2 ON テーブル2.id = テーブル1.id;

id	name	amount
1	Alice	1000
2	Bob	2000
3	Charlie	3000

テーブル2に無いidが4の要素は取得できない。

LEFT JOIN, RIGHT JOIN(外部結合)

テーブル間で結合し、左側または右側のテーブルのすべての行を取得する。存在しない場合は NULL で埋める。

SELECT テーブル1.id, テーブル2.name
FROM テーブル1
LEFT JOIN テーブル2 ON テーブル2.id = テーブル1.id;

id	name	amount
1	Alice	1000
2	Bob	2000
3	Charlie	3000
4	David	null

テーブル2に無かったidが4の要素も取得できる。

CROSS JOIN(交差結合)

全ての組み合わせを取得する。

SELECT テーブル1.id, テーブル2.name
FROM テーブル1
CROSS JOIN テーブル2 ON テーブル2.id = テーブル1.id;

交差結合は下記のような古い書き方もあります。

SELECT *
FROM table1, table2
WHERE （条件）

WITH

一時的な結果を格納し、その結果セットにクエリを実行できる。

WITH句内のSELECT結果をテーブルAで一時的に格納し、その結果とテーブル1を結合しています。

WITH テーブルA AS (
  SELECT id, MAX(amount) AS max_amount
  FROM テーブル2
  GROUP BY id
)
SELECT テーブル1.*, テーブルA.max_amount
FROM テーブル1
JOIN テーブルA ON テーブルA.id = テーブル1.id;

ウィンドウ関数、Window Function

OVER() 及び PARTITION BY は、Windows関数（Window Function）を使用する際に使われるSQLです。
これらを使うことで、クエリの結果に範囲を指定して集計やランク付けを行うことができます。

OVER, PARTITION BY

OVER()句は、集計関数やランク関数などのウィンドウ関数をどの範囲で行うかを定義します。

PARTITION BYは、特定の列でデータを区切ることができ、列ごとに集計などを行えます。

部署毎の給料の合計値を表示する。

SELECT
  名前,
  給料,
  部署,
  SUM(給料) OVER(PARTITION BY 部署) AS 合計値
FROM テーブル1;

名前	給料	部署	合計値	備考
太郎	1000	営業	4000	太郎の給料 + 真由の給料
真由	3000	営業	4000	太郎の給料 + 真由の給料
一郎	2000	事務	6000	一郎の給料 + エマの給料
エマ	4000	事務	6000	一郎の給料 + エマの給料

主なウィンドウ関数

・関数例

関数	説明
SUM()	範囲内の合計を計算する。
AVG()	範囲内の平均を計算する。
MIN()	範囲内の最小値を計算する。
MAX()	範囲内の最大値を計算する。
ROW_NUMBER()	各行に対して一意の番号を付与する。
RANK()	各行に対して順位を付与する。同じ値が複数ある場合、同じ順位が付与される。
DENSE_RANK()	各行に対して密な順位を付与する。同じ値が複数あっても順位が飛び飛びにならない。
NTILE(n)	結果をn個に分けてその番号内で付与する。
LEAD()	現在の行から指定されたオフセットだけ後の行の値を取得する。第2引数を入力するとオフセットを指定第3引数はNULLの時の代わりの値を指定できる。
LAG()	現在の行から指定されたオフセットだけ前の行の値を取得する。第2引数を入力するとオフセットを指定第3引数はNULLの時の代わりの値を指定できる。
FIRST_VALUE()	グループ内の最初の行の値を取得する。
LAST_VALUE()	グループ内の最後の行の値を取得する。

・データ例

データ	ROW_NUMBER()	RANK()	DENSE_RANK()	NTILE(2)	LEAD()	LAG()
1000	1	1	1	1	1000	NULL
1000	2	1	1	1	2000	1000
2000	3	3	2	1	4000	1000
4000	4	4	3	2	4000	2000
4000	5	4	3	2	NULL	4000

PARTITION BY内にORDER BYを入れた場合

PARTITION BY内にORDER BYを入れた場合、結果の各行に、その行までの特定部署内の給料の合計が表示されます。

SELECT
  名前,
  給料,
  部署,
  SUM(給料) OVER(PARTITION BY 部署 ORDER BY 名前) AS 合計値
  FROM テーブル1;

名前	給料	部署	合計値	備考
太郎	1000	営業	1000	太郎の給料
真由	3000	営業	4000	太郎の給料 + 真由の給料
一郎	2000	事務	2000	一郎の給料
エマ	4000	事務	6000	一郎の給料 + エマの給料

各行の「合計値」は、その行の部署までの給料の合計を示しています。行が部署ごとに並べ替えられており、各部署内では名前の昇順で並べ替えられています。

ROWS BETWEEN

集計する対象の行(フレーム)を指定できます。

・データ例

id	value
1	10
2	20
3	30
4	40
5	50

・SQL

ROWS BETWEEN 句は次のような形式を取ります

ウィンドウ関数 OVER (PARTITION BY カラム1 ORDER BY カラム2
ROWS BETWEEN 集計開始位置 AND 集計終了位置)

SELECT
  id,
  value,
  SUM(value) OVER (ORDER BY id
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS rolling_sum
FROM データ例;

id	value	rolling_sum	備考
1	10	60	10 + 20 + 30
2	20	90	10 + 20 + 30 + 40
3	30	150	10 + 20 + 30 + 40 + 50
4	40	140	20 + 30 + 40 + 50
5	50	120	30 + 40 + 50

「ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING」により、各行を中心として前後2行分（計5行）の範囲で合計が計算されます。

集計開始位置と集計終了位置は数値を入力して指定もできますが、基本的には下記の式を使って指定することが多いです。

式	説明
UNBOUNDED PRECEDING	一番頭の行
n PRECEDING	現在の行の値より n 行前
CURRENT ROW	現在の行
n FOLLOWING	現在の行の値より n 行後ろ
UNBOUNDED FOLLOWING	一番最後の行

効率化系

インデックス INDEX

INDEXを作成すると、特に検索やソートの操作が頻繁に行われる場合に、SELECTクエリのパフォーマンスが向上します。
一方で、INDEXを作成することでテーブルの容量が増加します。

INDEXの作成

CREATE INDEX インデックス名 ON テーブル名 (カラム名);

上記で作成したテーブル名のカラム名をSELECTする速度が早くなる。

SELECT * FROM テーブル名 WHERE カラム名 = 'AAA';

INDEXの表示

-- テーブルのINDEX一覧を表示
SHOW INDEX FROM テーブル名;
-- 特定のINDEXの詳細情報を表示
SHOW CREATE INDEX INDEX名 ON テーブル名;

INDEXの削除

DROP INDEX INDEX名 ON テーブル名;

関数の結果にインデックス

-- 給与の合計に対する検索処理が早くなる。
CREATE INDEX idx_salary_sum ON employees (SUM(salary));

関数の結果にインデックス

-- 給与の合計に対する検索処理が早くなる。
CREATE INDEX idx_salary_sum ON employees (SUM(salary));

複数のカラムへのINDEX作成

CREATE INDEX インデックス名 ON テーブル名 (カラム名1, カラム名2, ...);

SELECT * FROM テーブル名 WHERE カラム名1 = 'John' AND カラム名2 = 'Doe';

ユニークなINDEX作成

CREATE UNIQUE INDEX idx_unique_column ON table_name (column_name);

ユニークINDEXが存在する場合、同じ値を持つ行を挿入しようとするとエラーが発生します。

VIEW

VIEW（ビュー）は、データベース内のテーブルや他のビューから抽出された仮想のテーブルを作成できます。

VIEWの作成

CREATE VIEW テーブル名_view AS
SELECT t1.カラム1, t1.カラム2, t2.カラム3
FROM テーブル1 t1
JOIN テーブル2 t2 ON t1.共通カラム名 = t2.共通カラム名;

VIEWの利用

SELECT * FROM テーブル名_view WHERE カラム1 = 'value';

VIEWは仮想の表であるため、元となるテーブルのデータを変更するとVIEWもそれに基づいて変更されます。

VIEWはデータベースの設計とクエリの簡素化に役立つ概念であり、データの抽象化やセキュリティの向上などさまざまな目的で使用されます。

EXPLAIN, EXPLAIN ANALYZE 実行計画

実行計画などSQL文について調査できる。

EXPLAIN:

SQL文の先頭に「EXPLAIN」を追加すると、SQL文は実行せず、そのSQL文の情報を表示します。（テーブルの読み取り方法、結合方法、使用されるインデックスなど）

EXPLAIN SELECT * FROM テーブル名 WHERE condition;

EXPLAIN ANALYZE:

SQL文の先頭に「EXPLAIN ANALYZE」を追加すると、SQL文を実行しつつ、そのSQL文の実行にかかる時間や各ステップの実行統計などが表示されます。

EXPLAIN ANALYZE　SELECT * FROM テーブル名 WHERE condition;

パーティショニング

テーブルに大量のレコードが存在する場合に、レコードを領域ごとに分けて格納できる機能。

RANGE PARTITION（範囲パーティション）:

範囲パーティショニングでは、指定した範囲の値を持つ行を同じパーティションに格納します。通常、日付や数値の範囲などを使用します。

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN (2010),
    PARTITION p3 VALUES LESS THAN (2020),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

salesテーブルはsale_dateの年ごとにパーティション化されています。

LIST PARTITION（リストパーティション）:

リストパーティショニングでは、指定したリストの値に基づいて行をパーティションに分割します。

CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(50),
    department_id INT
) PARTITION BY LIST (department_id) (
    PARTITION p_eng VALUES IN (1, 3, 5),
    PARTITION p_sales VALUES IN (2, 4, 6),
    PARTITION p_other VALUES IN (7, 8, 9)
);

この例では、employeesテーブルはdepartment_idの値に基づいてパーティション化されています。

HASH PARTITION（ハッシュパーティション）:

ハッシュパーティショニングでは、指定された数のパーティションに均等にデータを分散します。

CREATE TABLE sensor_data (
    sensor_id INT,
    sensor_value INT
) PARTITION BY HASH(sensor_id) PARTITIONS 4;

この例では、sensor_dataテーブルはsensor_idに基づいて4つのパーティションに分割されます。

KEY PARTITION（キーパーティション）:

キーパーティショニングは、複数の列を使用してパーティションを作成します。

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE
) PARTITION BY KEY (customer_id, order_date) PARTITIONS 10;

この例では、ordersテーブルはcustomer_idとorder_dateに基づいて10のパーティションに分割されます。

SUB PARTITION（サブパーティション）:

サブパーティショニングは、範囲パーティションやリストパーティションのサブセット内でさらにパーティションを作成します。

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(sale_date)) SUBPARTITION BY HASH(MONTH(sale_date)) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN (2010),
    PARTITION p3 VALUES LESS THAN (2020),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

この例では、salesテーブルは年ごとにパーティション化され、各年は月ごとにサブパーティション化されています。

PARTITIONで分けたデータをSELECTする

-- 1990年のデータを取得
SELECT * FROM sales PARTITION (p0);

-- パーティションキーが3のデータを取得
SELECT * FROM sensor_data PARTITION (3);

-- パーティションキーが (123, '2023-01-15') のデータを取得
SELECT * FROM orders PARTITION (123, '2023-01-15');

SQLの優先順位・実行順序

SQL文は以下の順で実行されます。
効率的で正確なクエリを構築するために知っておきましょう。

FROM 句: データの取得元となるテーブルが指定されます。
JOIN 句: 複数のテーブルが結合されます。JOIN条件に基づいて行が結合されます。
WHERE 句: 行のフィルタリングが行われます。条件に合致する行のみが結果に含まれます。
GROUP BY 句: 列の値に基づいて行をグループ化します。
HAVING 句: GROUP BYによってグループ化された結果に対して条件を適用します。
SELECT 句: 結果として取得する列が指定されます。
ORDER BY 句: 結果セットの順序を指定します。通常、最後に実行されます。
LIMIT 句/OFFSET 句: 結果セットの制限やオフセットがあれば、それが適用されます。

SQLチューニング

SQLのパフォーマンスを向上させるための一連の手法を指します。以下に、主要な概念を用いて説明します。

ソフトパースとハードパース

SQL文が実行される際、まずパース（解析）を行います。パースにはソフトパースとハードパースの2種類があります。

ソフトパース：SQL文が以前にパースされ、その結果が共有プールにキャッシュされている場合、再度パースを行わずにキャッシュされた結果を再利用します。
ハードパース：SQL文が初めて実行される場合、SQL文をパースし、その結果や実行計画を共有プールにキャッシュします。

ハードパースはCPUリソースを多く消費するため、可能な限りソフトパースを利用することが推奨されます。これを実現するためには、バインド変数を使用することが有効です。

バインド変数

バインド変数は、SQL文の一部をパラメータ化するために使用されます。バインド変数を使用すると、同じSQL文でも異なる値を使用して実行することができます。

SELECT * FROM employees WHERE employee_id = :bind_variable;

これにより、SQL文が再利用され、ソフトパースが可能になります。

実行計画

実行計画は、SQL文をどのように実行するかを示すものです。
実行計画は、コストベースオプティマイザ（CBO）によって、各操作の最も効率的なアクセスパスを選択します。

フルスキャンとインデックススキャン

フルスキャンとインデックススキャンは、データベースがテーブルのデータにアクセスする方法を指します。

フルスキャン：テーブルの全ての行をスキャンします。
大量のデータを処理する場合や、テーブルの大部分をスキャンする必要がある場合に効率的です。
インデックススキャン：インデックスを使用してテーブルの特定の行にアクセスします。
少数の行を取得する場合や、特定の値を持つ行を高速に検索する場合に効率的です。

ネステッドループ結合、ハッシュ結合、ソートマージ結合

これらは、2つ以上のテーブルを結合する方法を指します。

ネステッドループ結合：一方のテーブル（外部表）の各行に対して、もう一方のテーブル（内部表）をスキャンします。
ハッシュ結合：結合条件に等価条件（=）が指定され、大量のレコードを結合する場合に有効です。
ソートマージ結合：両方の表を結合条件列でソートし、結果をマージします。