SQLまとめ

# はじめに
業務上でよく使うSQLをまとめます。
今回の記事に書くSQLは、RDBMSの中では基本的に以下のものに対応しています。
(例外的に対応していない可能性もありますのでご了承ください)
* DB2
* PostgreSQL
* MySQL

参考書は、「[SQL-第2版-ゼロからはじめるデータベース操作](https://www.amazon.co.jp/SQL-%E7%AC%AC2%E7%89%88-%E3%82%BC%E3%83%AD%E3%81%8B%E3%82%89%E3%81%AF%E3%81%98%E3%82%81%E3%82%8B%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E6%93%8D%E4%BD%9C-%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9F%E3%83%B3%E3%82%B0%E5%AD%A6%E7%BF%92%E3%82%B7%E3%83%AA%E3%83%BC%E3%82%BA-%E3%83%9F%E3%83%83%E3%82%AF/dp/4798144452)」を活用させていただきました。

# SELECT文
#### <font color="RoyalBlue">基本的なSELECT文</font>

すべての列
```
SELECT * FROM <テーブル名>;
````
列を指定
```
SELECT <列名>, <列名> FROM <テーブル名>;
```


#### <font color="RoyalBlue">列に別名を付ける</font>
```
SELECT <列名> AS <別名>, <列名> AS <別名> FROM <テーブル名>;
```
(実行例)
```
SELECT shohin_id AS "商品ID",
       shohin_mei AS "商品名",
       shiire_tanka AS "仕入単価"
  FROM Shohin;
```
(出力例)
```
 商品ID |     商品名     | 仕入単価
--------+----------------+----------
 0001   | Tシャツ        |      500
 0002   | 穴あけパンチ    |      320
```
#### <font color="RoyalBlue">定数の出力</font>

```
SELECT <定数> AS <表示名>, <定数> AS <表示名> FROM <テーブル名>;
```
(実行例)
```
SELECT shohin_id AS "商品ID",
       38 AS kazu,
       '2009-02-24' AS hizuke
  FROM Shohin;
```
(出力例)
```
 商品ID |   kazu  |    hizuke
--------+---------+---------------
 0001   |    38   |  2009-02-24
 0002   |    38   |  2009-02-24
```


#### <font color="RoyalBlue">結果から重複行を省く</font>

対象カラムの重複を省いた形で出力
```
SELECT DISTINCT <対象列名> FROM <テーブル名>;
```
※NULLも1種類のデータとして扱われる
※DISTINCTキーワードは先頭の列名の前にしか書けない

#### <font color="RoyalBlue">WHERE句による行の選択</font>

```
SELECT <列名>, <列名> FROM <テーブル名> WHERE <条件式>;
```

#### <font color="RoyalBlue">コメントの書き方</font>
１行コメント

```
-- コメントです
```

複数行コメント

```
/*複数行
コメントです*/
```

# 演算子について
#### <font color="RoyalBlue">算術演算子</font>
```
SELECT <列名>, <対象列名> * 2 AS "<表示名>"　FROM <テーブル名>;
```

(出力例)
```
 列名   |   表示名    
--------+------------
 0001   |  対象列×2   
 0002   |  対象列×2   
```

| 意味 | 記号 |
|:-:|:-:|
| 足し算  | +  |
| 引き算  | -  |
| 掛け算  | *  |
| 割り算  | /  |

※通常の計算式と同じようにカッコ`()`も使える
※NULLを含んだ演算は問答無用でNULLになる

#### <font color="RoyalBlue">比較演算子</font>

WHERE句の条件式には比較演算子が必要

| 意味 | 記号 |
|:-:|:-:|
| 等しい  | =  |
| 等しくない  | <>  |
| 以上  | >=  |
| より大きい  | >  |
| 以下  | <=  |
| より小さい  | <  |

※文字列型の順序の原則は辞書順。数値の大小順序と混合してはいけない。
※NULLは演算子に使用できない(レコードが取得できない)ため、IS NULL演算子、IS NOT NULL演算子を使う。

#### <font color="RoyalBlue">論理演算子</font>

NOT演算子

```
SELECT <列名>, <列名> FROM <テーブル名> WHERE NOT <条件式>;
```

AND演算子、OR演算子

```
SELECT <列名>, <列名> FROM <テーブル名> WHERE <条件式> AND <条件式> OR <条件式>;
```
※条件を強くしたいときは()を付ける

# 集約と並べ替え
### 集約関数(集合関数)

* COUNT：レコードを数える
* SUM：数値列のデータを合計する
* AVG：数津列のデータを平均する
* MAX：任意の列データの最大値を求める
* MIN：任意の列データの最小値を求める

#### <font color="RoyalBlue">COUNT関数</font>

全行を数える
```
COUNT * FROM <テーブル名>;
````
指定した列のNULLを除外して行数を数える
```
SELECT COUNT (<列名>) FROM <テーブル名>;
```

#### <font color="RoyalBlue">SUM関数</font>

指定した列の合計を求める
```
SELECT SUM (<列名>) FROM <テーブル名>;
```

※「COUNT(*)」以外の集約関数はNULLを除外する

#### <font color="RoyalBlue">AVG関数</font>

指定した列の平均値を求める
```
SELECT AVG (<列名>) FROM <テーブル名>;
```

#### <font color="RoyalBlue">MAX関数・MIN関数</font>

最大値を求める

```
SELECT MAX (<列名>) FROM <テーブル名>;
```

最小値を求める

```
SELECT MIN (<列名>) FROM <テーブル名>;
```

#### <font color="RoyalBlue">重複値を除外して集約関数を使う</font>

DISTINCTキーワードを使って、値の重複値を除いて行数を数える

```
SELECT COUNT (DISTINCT <列名>) FROM <テーブル名>;
```

### テーブルをグループに切り分ける

#### <font color="RoyalBlue">GROUP BY句</font>

集約

```
SELECT <列名1>, <列名2> FROM <テーブル名> GROUP BY <列名1>, <列名2>;
```

指定した列の分類ごとに行数を数える

```
SELECT <列名>, COUNT(*) FROM <テーブル名> GROUP BY <列名>;
```

(出力例)
```
 列名        |  count    
-------------+------------
 グループA   |    3  
 グループB   |    2   
 グループC   |    4 
```

**句の記述順は不変『SELECT→FROM→(WHERE)→GROUP BY』**

※集約キーにNULLが含まれる場合、結果にも「不明」行(空行)として現われる
※集約関数を書ける場所はSELECT句とHAVING句(とORDER BY句)だけ

### 集約した結果に条件を指定する

#### <font color="RoyalBlue">HAVING句</font>
集約したグループに対する条件指定

```
SELECT <列名1>, <列名2> 
FROM <テーブル名> 
GROUP BY <列名1>, <列名2>
HAVING <条件式>;
```

**記述順序『SELECT→FROM→WHERE→GROUP BY→HAVING』**

HAVING句にかける要素
* 定数
* 集約関数
* GROUP BY句で指定した列名(集約キー)

※HAVING句はグループに対する条件指定であるため、集約キーに対する条件はHAVING句ではなくWHERE句に書く方がよい(処理速度も速い)

### 検索結果を並べ替える

#### <font color="RoyalBlue">ORDER BY句</font>

```
SELECT <列名>, <列名> 
FROM <テーブル名>
ORDER BY <並べ替えの基準となる列1>, <並べ替えの基準となる列2>;
```

| 設定値 | 結果 |
|:-:|:-:|
| デフォルト  | 昇順  |
| ASC  | 昇順  |
| DESC  | 降順  |

**句の記述順序『SELECT→FROM→WHERE→GROUP BY→HAVING→ORDER BY』**

※NULLは先頭または末尾にまとめて表示される
※ORDER BY句では別名での指定が許されている(GROUP BY句では別名は使えない)

# データの更新
#### <font color="RoyalBlue">INSERT文</font>

テーブルにデータを1行登録する
```
INSERT INTO  <テーブル名> (列1, 列2, 列3...) VALUES (値1, 値2, 値3...);
```
* テーブル名の後の列リストはテーブルの全行に対してINSERTを行る場合、省力可能
* 列リストと値リストの列数が不一致だとエラーになる
* 原則として1回の実行で1行を挿入する
(1回の実行で複数行をINSERTする機能は**複数行INSERT**と呼ぶ)

テーブルにデータを複数行登録する(複数行INSERT)
```
INSERT INTO  <テーブル名> VALUES 
    (値1, 値2, 値3...),
    (値1, 値2, 値3...),
    (値1, 値2, 値3...)...;
    
```

#### <font color="RoyalBlue">他のテーブルからデータをコピーする</font>

テーブルAのデータをテーブルBにコピー

```
INSERT INTO  <テーブルB> (値1, 値2, 値3...)
SELECT (値1, 値2, 値3...) FROM <テーブルA>;
```

INSERT文内のSELECT文では、WHERE句やGROUP BY句など、どんなSQL構文も使うことができる

#### <font color="RoyalBlue">DELETE文</font>

DROP TABLE → テーブルごとすべて削除
DELETE → テーブル自体は残して行のみをすべて削除

テーブルは残したまま、すべての行を削除する

```
DELETE FROM <テーブル名>;
```
一部の行だけを削除する探索型DELETE

```
DELETE FROM <テーブル名>WHERE <条件>;
```

※必ずテーブルを全行削除する場合**TRUNCATE**を使う方が高速

```
TRUNCATE <テーブル名>;
```

#### <font color="RoyalBlue">UPDATE文</font>

テーブルのデータを変更する

```
UPDATE <テーブル名> SET <列名> = <式>;
```
一部の行だけを変更する探索型UPDATE

```
UPDATE <テーブル名> SET <列名> = <式>
    WHERE <条件>;
```

※列をNULLで更新することを**NULLクリア**と呼ぶ
　(NOT NULL制約や主キー制約の付いていない列に限る)

 # トランザクション

 トランザクションとは「**データベースに対する一つ以上の更新をまとめて呼ぶときの名称**」である

 DBMSのトランザクションの制約「**ACID特性**」

 * 原子性(Atomicity)
 * 一貫性(Consistency)
 * 独立性(Isolation)
 * 永続性(Durability)

```
トランザクション開始文;

DML文①;
DML文②;
DML文③;
・
・
・

トランザクション終了文(COMMIT または ROLLBACK);
```

DML文にはINSERT文、DELETE文、UPDATE文があてはまる
※SQL Server、PostgreSQL、MySQLなどはデフォルト設定が自動コミットモードになってる

#### <font color="RoyalBlue">**トランザクション開始文**</font>
DBMSによって違いがある

* SQL Server、PostgreSQL
`BEGIN TRANSACTION`
* MySQL
`START TRANSACTION`

#### <font color="RoyalBlue">トランザクション終了文</font>

**COMMIT** -処理の確定(上書き保存)

**ROLLBACK** -処理の取り消し(保存せずに終了)

# 述語
述語とは、戻り値が真理値(TRUE/FALSE/UNKNOWN)になる関数のことです。
[述語例]
* LIKE
* BETWEEN
* IS NULL, IS NOT NULL
* IN


#### <font color="RoyalBlue">LIKE述語 </font> -文字列の部分一致検索

部分一致には以下の3種類がある

* 前方一致
* 中間一致
* 後方一致

前方一致
```
SELECT * FROM <テーブル名> WHERE <列名> LIKE '<文字列>%';
```
中間一致
```
SELECT * FROM <テーブル名> WHERE <列名> LIKE '%<文字列>%';
```
後方一致
```
SELECT * FROM <テーブル名> WHERE <列名> LIKE '%<文字列>';
```

※ `%`は文字数がわかっている場合`_`に置き換えることができる
　(`_`は任意の一文字を意味する)

#### <font color="RoyalBlue">BETWEEN述語 </font> -範囲検索

(実行例)
販売単価が100～1000円の商品を選択
```
SELECT shohin_mei, hanbai_tanka FROM Shohin
    WHERE hanbai_tanka BETWEEN 100 AND 1000;
```
(出力例)
```
 shohin_mei | hanbai_tanka    
------------+-------------
 Tシャツ    |  1000   
 フォーク   |  500  
```
↑ BETWEENで記載する場合、両端の値(100, 1000)も含まれる

#### <font color="RoyalBlue">IS NULL、IS NOT NULL </font> -NULLか非NULLかの判定

(実行例)
仕入単価がNULLの商品を選択
```
SELECT shohin_mei, shiire_tanka FROM Shohin
    WHERE shiire_tanka IS NULL;
```
(出力例)
```
 shohin_mei |  shiire_tanka    
------------+--------------
 フォーク   |     
 スプーン   |    
```

(実行例)
仕入単価がNULL以外の商品を選択
```
SELECT shohin_mei, shiire_tanka FROM Shohin
    WHERE shiire_tanka IS NOT NULL;
```

#### <font color="RoyalBlue">IN述語 </font> -ORの便利な省略形

(実行例)
ORで複数の仕入単価を指定して検索
```
SELECT shohin_mei, shiire_tanka FROM Shohin
    WHERE shiire_tanka = 320
       OR shiire_tanka = 500
       OR shiire_tanka = 5000;
```
(出力例)
```
 shohin_mei   |  shiire_tanka    
--------------+--------------
 Tシャツ      |  500   
 ボールペン   |  320  
 圧力鍋       |  5000  
```

(実行例)
INで複数の仕入単価を指定して検索(実行結果は上記と同じ)
```
SELECT shohin_mei, shiire_tanka FROM Shohin
    WHERE shiire_tanka IN (320, 500, 5000);
```

反対に指定した値以外を選択したい場合は**NOT IN**を使う

※INやNOT INはNULLを選択することはできない
※INやNOT INの後にサブクエリの記載も可能

# 集合演算
### テーブルの足し算と引き算
#### <font color="RoyalBlue">UNION </font> -テーブルの足し算

UNIONによるテーブルの足し算
```
SELECT <列名>, <列名>... FROM <テーブルA>;
UNION
SELECT <列名>, <列名>... FROM <テーブルB>;
```

※演算対象となるレコードの列数が同じであることと、足し算の対象となるレコードの列のデータ型が一致している必要がある
※UNIONに限らず集合演算子は、通常は重複行が削除される
#### <font color="RoyalBlue">ALLオプション </font> -重複行を残す

UNIONによるテーブルの足し算で重複行を排除しない
```
SELECT <列名>, <列名>... FROM <テーブルA>;
UNION ALL
SELECT <列名>, <列名>... FROM <テーブルB>;
```
#### <font color="RoyalBlue">INTERSECT </font> -テーブルの共通部分の選択

INTERSECTによるテーブル共通部分の選択
```
SELECT <列名>, <列名>... FROM <テーブルA>;
INTERSECT
SELECT <列名>, <列名>... FROM <テーブルB>;
```
#### <font color="RoyalBlue">EXCEPT </font> -レコードの足し算

EXCEPTによるレコードの引き算
```
SELECT <列名>, <列名>... FROM <テーブルA>;
EXCEPT
SELECT <列名>, <列名>... FROM <テーブルB>;
```

### 結合
結合(JOIN)とは別のテーブルから列を持ってきて列を増やす集合演算です。

#### <font color="RoyalBlue">INNER JOIN</font> -内部結合

(実行例)
```
SELECT TS.tenpo_id, TS.tenpo_mei, TS.shohin_id, S.shohin_mei, S.hanbai_tanka
  FROM TenpoShohin AS TS INNER JOIN Shohin AS S
    ON TS.shohin_id = S.shohin_id
ORDER BY tenpo_id;
```
* 結合を行うときは、FROM句に複数のテーブルを記述する
* 内部結合ではON句は必須。記述場所はFROMとWHEREの間

ONによるテーブル足し算(和集合)のイメージ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1834935/2e26b3bf-7fd0-b5e5-fb60-743641fe8e51.png)

* 結合を使った場合のSELECT句の列は、すべての<テーブルの別名>.<列名>の形式で書く
#### <font color="RoyalBlue">OUTER JOIN</font> -外部結合

(実行例)
```
SELECT TS.tenpo_id, TS.tenpo_mei, S.shohin_id, S.shohin_mei, S.hanbai_tanka
  FROM TenpoShohin TS RIGHT OUTER JOIN Shohin S
    ON TS.shohin_id = S.shohin_id
ORDER BY tenpo_id;
```

* 外部結合のポイントは、片方のテーブル情報がすべて出力されること
* LEFTを使った場合はFROM句の左側に書いたテーブルをマスタとし、RIGHTを使った場合は右側のテーブルをマスタとして使う
 　(LEFTとRIGHTはどちらを使ってもテーブルの記載を入れ替えれば結果は同じになる)
#### <font color="RoyalBlue">CROSS JOIN</font> -クロス結合
クロス結合は、2つのテーブルのレコードについて、すべての組み合わせを作る結合 方法(結果は2つのテーブル行数を掛け算した数になる)

(実行例)
```
SELECT TS.tenpo_id, TS.tenpo_mei, TS.shohin_id, S.shohin_mei
  FROM TenpoShohin AS TS CROSS JOIN Shohin AS S;
```

※クロス結合の場合、ON句は指定することができない