【Oracle SQL】基礎

## 概要
- SQLの基礎について
- 一部Oracle SQLのみのものもあるかも

## テーブル作成

```sql
CREATE TABLE テーブル名 (
  カラム名 データ型 (制約),
  カラム名 データ型 (制約),
  ...
);
```

- 指定したデータ型のカラムを持つテーブルを作成
- 必要に応じて制約を付与

### データ型

| データ型 | 意味 |
|:-|:-|
| CHAR  | 固定長の文字列  |
| VARCHAR2  | 可変長の文字列  |
| NUMBER  | 整数  |
| BINARY_FLOAT  | 32ビットの浮動小数点  |
| DATE  | 年月日<br>yyyy-mm-ddの形式で記述  |
| BLOB  | バイナリデータ  |

- `CHAR`や`VARCHAR2`、`NUMBER`の後には`()`で長さを指定する
- `CHAR(10)`の場合は10バイトの文字列を扱う
- `CHAR`と`VARCHAR2`は、固定長か可変長かが異なる
    - 固定長の場合は、`()`で指定したバイト数を常に確保する
    - 可変長の場合は、実際の文字列分のバイト数を確保する
    ※`()`で指定したバイト数以上は確保不可
- Oracleで`DATE`型のカラムの値をINSERTする場合は、`VALUES(値1, 値2, DATE 'yyyy-mm-dd', 値4, ...)`という形で記述する
(`date`を先頭につける必要がある)

### 制約

| 制約 | 意味 |
|:-|:-|
| NOT NULL  | 値が空欄はNG  |
| PRIMARY KEY  | 主キー  |
| FOREIGN KEY (カラム名) REFERENCES テーブル名(カラム名)  | 外部キー  |

### テーブル定義の確認
```sql
DESC テーブル名;
```

- テーブルに登録されているのカラムの定義を表示
- `DESC`は`DESCRIBE`でも可

## テーブル削除
```sql
DROP TABLE テーブル名;
```

- `テーブル名`で指定したテーブルを削除する
- 制約があって削除できない場合は`CASCADE CONSTRAINTS`を付けて制約ごと削除可能
- 削除すると**リサイクル・ビン**というゴミ箱に入る
    - リサイクル・ビンからも削除したい場合は、テーブル名の後に`PURGE`を付ける

## レコード登録
```sql
INSERT INTO テーブル名(カラム1, カラム2, ...) VALUES(値1, 値2, ...);
```

- 新規レコードを作成
- カラムに対応する値をそれぞれ指定する
- `NOT NULL`がついているカラムは必ず指定する
- 指定をしていないカラムは`NULL`になる
- カラム名はそのまま記述するかダブルクォーテーションで囲む
※シングルクォーテーションでは囲まない
- 値は、型がvarcharなどの文字列の場合はシングルクォーテーション囲み、numberなどの数値の場合はそのまま記述
- `テーブル名(カラム1, カラム2, ...)`を省略する場合は全てのカラムの値を指定する必要あり

## レコード更新
```sql
UPDATE テーブル名 SET カラム1 = 値1, カラム2 = 値2, ... WHERE 条件;
```

- 条件に当てはまるレコードのカラムを、それぞれ指定した値に更新する

## レコード削除
```sql
DELETE FROM テーブル名 WHERE 条件;
```

- 条件に当てはまるレコードを削除
- **`WHERE`句を指定しないと全件削除となるため注意**

## データ抽出
```sql
SELECT * FROM テーブル名;
```

- テーブルにある全てのカラムを抽出

```sql
SELECT カラム名, カラム名 FROM テーブル名;
```

- 指定したカラムのみを抽出
- 指定したカラムに対して`+`、`-`、`*`、`/`などの演算子を使用することも可能
    - 日付に対して演算を行うことも可能
- NULL値に対して演算を行うと結果はNULLになる
- テーブル名に`DUAL`を指定すると、ダミーのテーブルで結果を得ることが可能

```sql
SELECT カラム名 AS 別カラム名 FROM テーブル名;
```

- カラム名に別名を付けて抽出
- `AS`は省略可能
- 別カラム名にスペースや記号を入れたい場合は別カラム名全体を`""`で囲む
**※`''`ではないので注意**

```sql
SELECT カラム名1 || カラム名2 || カラム名3 FROM テーブル名;
```

- カラムを結合して表示
- `||`でカラムではなく文字列を結合する場合は`''`で囲む

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

- カラムの中で重複した値を持つ行を削除して抽出
- `DISTINCT`は`NULL`に対しても有効（`NULL`が1行にまとめられる）

## 抽出条件
```sql
SELECT カラム名 FROM テーブル名 WHERE 条件式
```

- 条件式に従ってテーブルからカラムを抽出
- 条件式は下記のような形式で記述する

| 条件式 | 意味 |
|:-|:-|
| カラム名 = 100  | カラムの値が100に等しいデータ<BR>文字列の場合は`''`で囲む  |
| カラム名 >= 1000  | カラムの値が1000以上  |
| カラム名 <= 1000  | カラムの値が1000以下  |
| カラム名 <> 1000<br>カラム名 != 1000  | カラムの値が1000ではない  |
| カラム名 BETWEEEN 1000 AND 2000  | カラムの値が1000以上、2000以下  |
| カラム名 IN (100, 200, 300)  | カラムの値が100, 200, 300  |
| カラム名 LIKE 'J%'  | カラムの値が`J`で始まる<BR>`%`は何文字でも可(ワイルドカード)<BR>データが数字の場合でも`''`で囲む必要あり  |
| カラム名 LIKE '_LEAK'  | カラムの値の1文字目が不明で、2文字目以降が`LEAK`となっているデータ<BR>`_`は不明な文字数分必要<BR>データが数字の場合でも`''`で囲む必要あり  |
| カラム名 IS NULL  | カラムの値が`NULL`  |
| カラム名 IS NOT NULL  | カラムの値が`NULL`でない  |
| 条件1 AND 条件2  | 条件1と条件2どちらも満たす  |
| 条件1 OR 条件2  | 条件1か条件2のいずれかを満たす  |

- `AND`と`OR`両方を条件式に記述している場合、`AND`が優先して評価される

## 置換変数
```sql
WHERE カラム名 = &変数名;
```

- 条件を変数化し都度置き換えることが可能
- SQL文を実行すると、変数名にあたる部分の値を選択するよう画面に表示される
- 入力した値が`&変数名`に置き換わり、SQLが実行される

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3757184/fae5e55f-b4ea-1b38-7586-3192cd166525.png)

## ソート（並び替え）
```sql
ORDER BY カラム1, カラム2 ASC;
```

- カラム1、カラム2の順でソート
- `ASC`を付けると昇順にソートされるが、標準で昇順となっているため省略可
- NULLは一番最後に表示される
    - `NULLS FIRST`を付けるとNULLを最初に表示可能

```sql
ORDER BY カラム1, カラム2 DESC;
```

- カラム2、カラム1の順でソート（降順）
- NULLは一番最初に表示される
    - `NULLS LAST`を付けるとNULLを最後に表示可能

## 文字列操作関数

| SQL文 | 実行結果 | 例 |
|:-|:-|:-|
| LOWER(カラム名) | 大文字を小文字に変換 | ORACLE ⇒ oracle |
| UPPER(カラム名) | 小文字を大文字に変換 | oracle ⇒ ORACLE |
| INITCAP(カラム名) | 先頭文字を大文字、以降を小文字に変換 | ORACLE MASTER ⇒ Oracle Master |
| SUBSTR(カラム名, 値1, 値2)<BR>※値2は省略可 | 文字列の値1番目から値2個分までを抽出 | SUBSTR('ORACLE', 2, 4)⇒ RACL |
| LENGTH(文字列) | 文字数を返す | LENGTH('おらくる') ⇒ 4 |
| LENGTHB(文字列) | バイト数を返す | LENGTHB('おらくる') ⇒ 12 |
| INSTR(カラム名, 文字列, 値1, 値2)<BR>※値1, 値2は省略可 | 文字列が出現する場所を返す。値1は開始場所、値2は文字列が出てくる回数 | INSTR('ABCDEABC', 'C', 3,2) ⇒ 8<BR>INSTR('ABCDEABC', 'C') ⇒ 3 |
| LPAD(カラム名, 値1, 文字) | 値1の長さになるまで文字を左に埋める | LPAD('ORACLE', 10 '*') ⇒ ****ORACLE |
| RPAD(カラム名, 値1, 文字) | 値1の長さになるまで文字を右に埋める | RPAD('ORACLE', 10 '*') ⇒ ORACLE**** |
| TRIM (文字列) | 前後の空白を除く | TRIM(' ORA CLE ') ⇒ORA CLE |
| REPLACE(文字列, 文字1, 文字2) | 文字1を文字2に置換する | REPLACE('AABBCC','B', 'D') ⇒ AADDCC |

## 数値関数

| SQL文 | 実行結果 | 例 |
|:-|:-|:-|
| ROUND(数字1, 数字2)<BR>※数字2は省略可 | 数字1の小数点以下を数字2で指定した桁で四捨五入<br>数字2を0にすると整数になる | ROUND(55.56,1)⇒ 55.6<BR>ROUND(55.56) ⇒ 56 |
| TRUNC(数字1, 数字2)<BR>※数字2は省略可 | 数字1の小数点以下を数字2で指定した桁で切捨て | TRUNC(55.56,1) ⇒55.5<BR>TRUNC(55.56) ⇒ 55 |
| MOD(数字1, 数字2) | 数字1を数字2で割った余り | MOD(55,3) ⇒ 1 |
| CEIL(数字) | 数字の小数点以下を切上 | CEIL(55.56) ⇒ 56 |
| FLOOR(数字) | 数字の小数点以下を切捨 | FLOOR(55.56)⇒ 55 |
| POWER(数字1, 数字2) | 数字1を数字2で累乗した値を返す | POWER(3, 2) ⇒3の2乗⇒ 9 |
| SQRT(数字) | 数字の平方根を返す | SQRT(4) ⇒ 4の平方根⇒ 2 |

## 集約関数 / 集計関数
### 集約関数
| SQL文 | 実行結果 |
|:-|:-|
| GROUP BY カラム1, カラム2, カラム3, ...  | 指定したカラム内で重複する値ごとに集約を行う<BR>NULLも1つのグループとして集約される<BR>**`GROUP BY`を使用した場合、`SELECT`句では`GROUP BY`で指定したカラムのみを使用可能**  |
| HAVING フィルタリング条件  | `GROUP BY`句によって集約されたデータに対してフィルタリングをかける<BR>基本的に`WHERE`句と同じ書き方が可能  |


### 集計関数
| SQL文 | 実行結果 |
|:-|:-|
| COUNT(カラム)  | 指定したカラムに一致するカラムの件数を出力  |
| SUM(カラム)  | 指定したカラムに一致するカラムの値の合計を出力  |
| AVG(カラム)  | 指定したカラムに一致するカラムの値の平均を出力  |
| MAX(カラム), MIN(カラム)  | 指定したカラムの中の最大値、最小値を出力  |

- 集計関数は集約関数を使っている場合のみに使用可能
- 集計関数は`WHERE`句では使用不可（詳細は関数の実行順序を参照）

## NULL関連
### NVL関数
```sql
SELECT VNL(カラム, 値) FROM テーブル名 WHERE 条件式;
```

- 第1引数のカラムがNULLだった場合、第2引数の値を返す
- 第1引数のカラムがNULLでない場合、そのままカラムの値を返す
- 第1引数にはカラムの値や演算処理などを指定可能
- Oracleのみで使用可能
※MySQLやPostgreSQLなど他のDBMSでは後述の`COALESCE関数`を使用

#### 例
- 下記のようなテーブルで入社日(JOIN_DAY)が`NULL`の場合は`2000/1/1`と表示する

| ID   | NAME   | AGE | JOIN_DAY  | JOB_NAME  | 
| ---- | ------ | --- | --------- | --------- | 
| 0001 | Johnny | 23  | 2020/8/25 | salesman  | 
| 0002 | Tom    | 15  | 2019/4/1  | engineer  | 
| 0003 | Jack   | 24  |           | salesman  | 
| 0004 | Andrew | 32  | 2015/6/28 | president | 

```sql
SELECT ID, NAME, VNL(JOIN_DAY, '2000/1/1') AS 入社日 FROM EMPLOYEE;
```

- 上記を実行するとJackの入社日が`2000/1/1`となる

### COALESCE関数
```sql
SELECT COALESCE(式1, 式2, ...) FROM テーブル名 WHERE 条件式;
```

- 引数の式を左から順に評価し、最初に見つかったNULL以外の値を返す

## 日付の計算, 日付関数

| SQL文 | 実行結果 |
|:-|:-|
| SYSDATE  | 実行した日付  |
| 日付 + 数値  | 日付に数値分の日数が足される  |
| 日付 - 数値  | 日付から数値分の日数が引かれる  |
| 日付 + 日付  | 不可  |

<BR>

| SQL文 | 実行結果 |
|:-|:-|
| ADD_MONTHS(日付, 値)  | 値の月だけ日付を加算<BR>値が負の数の場合は減算  |
| LAST_MANTHS(日付)  | 日付の月末の日を返す  |
| NEXT_DAY(日付, 曜日)  | 日付で指定した日の次の曜日の日を返す  |
| MONTHS_BETWEEN(日付1, 日付2)  | 日付1と日付2の間の月数を返す  |

- 日付については`YY-MM-DD`という形式で記述する

## 型変換関数
| SQL文 | 実行結果 |
|:-|:-|
| TO_CAHR(数値)  | 数値を文字列に変換  |
| TO_CAHR(数値, 'L999')  | 数値を文字列に変換し、先頭に`¥`を付ける  |
| TO_CAHR(数値, '999.0')  | 数値を文字列に変換し、数値が整数の場合は小数点以下を表示する  |
| TO_CAHR(数値, '999,999,999')  | 数値を文字列に変換し、3桁区切りに`,`を追加する  |
| TO_NUMBER(文字)  | 文字もしくは文字列を数値に変換  |
| TO_DATE(文字列)  | 文字列を日付(YY-MM-DD)に変換  |
| 'YYYY-MM-DD'  | 年を西暦で表示  |
| 'HH24:MI:SS'  | 時刻を表示<BR>`HH24`は24時間表記で、`HH`は12時間表記  |

## 表の結合
### 内部結合
- 2つの表のうち、結合条件に一致するもののみを抽出する結合

```sql
SELECT カラム1, カラム2, ... 
FROM テーブル1, テーブル2
WHERE テーブル1.カラム1 = テーブル2.カラム1;
```

- テーブル1のカラム1の値とテーブル2のカラム1の値を比較
- 等しいフィールドがある場合、テーブル1と2を結合しカラム1、カラム2を抽出する
- 結合するテーブルにおいてカラム名の重複がある場合は、`テーブル名.カラム名`と表記する
- `WHERE`句を記述せずに表を結合してしまう(結合条件を記述しない)と、データベースに負荷をかけてしまうため避ける（直積）

```sql
SELECT カラム1, カラム2, ...
FROM テーブル1 (INNER) JOIN テーブル2
ON テーブル1.カラム1 = テーブル2.カラム1;
```

- 上記の`WHERE`句を用いた結合と同義
- `INNER`は省略可能
- `ON`句にはほかの表から値を参照する際に、**参照する列と参照される列の関係**を記述（結合条件）
- 参照する列と参照される列はE-R図から判断する

#### (補足)テーブルに別名を付ける
```sql
FROM テーブル1 A, テーブル2 B;
```

- テーブルに別名を付ける
- 上記ではテーブル1にA、テーブル2にBという別名を付けている
- A.カラム名と表記できるだけでテーブル名を変更しているわけではない

### 外部結合（左側一致）
- 2つの表のうち結合条件の左側一致するもののみを抽出する結合
- 割と使う

```sql
SELECT カラム1, カラム2, ...
FROM テーブル1 LEFT JOIN テーブル2
ON テーブル1.カラム1 = テーブル2.カラム1;
```

### 外部結合（右側一致）
- 2つの表のうち結合条件の右側一致するもののみを抽出する結合
- あまり使うことはない

```sql
SELECT カラム1, カラム2, ...
FROM テーブル1 RIGHT JOIN テーブル2
ON テーブル1.カラム1 = テーブル2.カラム1;
```

### 外部結合（全て）
- 2つの表のうち結合条件の一致するもの全てのみを抽出する結合
- 左側一致と右側一致を合わせた結合

```sql
SELECT カラム1, カラム2, ...
FROM テーブル1 FULL OUTER JOIN テーブル2
ON テーブル1.カラム1 = テーブル2.カラム1;
```

**(参考) 左側一致と右側一致の違いについて**

https://qiita.com/ysda/items/0d473e644409e45965d7

## 条件分岐
- 条件分岐を行う
- 単純CASE式と検索CASE式の2種類がある

### 単純CASE式
```sql
CASE 条件分岐判断対象のカラム(複数可)
  WHEN '値1' THEN '結果1'
  WHEN '値2' THEN '結果2'
  ...
  ELSE '結果n'
END
-- もしくは
CASE 
  WHEN 条件分岐判断対象のカラム IN '値1' THEN '結果1'
  WHEN 条件分岐判断対象のカラム IN '値2' THEN '結果2'
  ...
  ELSE '結果n'
END
```
- 等価式がTRUEかFALSEかを判断する際に使用
- どの条件にも当てはまらない場合は`ELSE`に記載した処理を実行

### 検索CASE式
```sql
CASE
  WHEN 条件分岐判断対象のカラム 条件式 THEN '結果1'
  WHEN 条件分岐判断対象のカラム 条件式 THEN '結果2'
  ...
  ELSE '結果n'
END
```

- 単純にTRUEかFALSEでは判断できない条件の際に使用
- どの条件にも当てはまらない場合は`ELSE`に記載した処理を実行
- 条件式の部分では比較条件など(=, BETWEEN, NULL, NOT, AND)を使用可能

### CASEとSUMの特殊な使用
- CASEとSUMを併用することで、複数行のレコードを1行で集計することが可能

```sql
SELECT 
    SUM (CASE WHEN カラム = 値1 THEN 1 END), 
    SUM (CASE WHEN カラム = 値2 THEN 1 END), 
    SUM (CASE WHEN カラム = 値3 THEN 1 END), 
    ...
FROM テーブル;
```

- 集計対象のカラムが値1の場合、値2の場合、値3の場合、のように値ごとにカラムを作成
- 各SUM関数の中では、当てはまる値が見つかった場合はビットフラグを立て、最終的にそのフラグの数をSUM関数でカウントしている

#### 例
- 下記のテーブルにおいて各血液型の人数を1行で集計したい場合を考える
※A, B, O, AB, 不明の4列を作成するイメージ

| ID  | NAME         | BLOODTYPE | 
| --- | ------------ | --------- | 
| 1   | 大山　加奈子 |           | 
| 2   | 栗原　江見   | B         | 
| 3   | 中田　秀樹   | A         | 
| 4   | 斧　真一     | O         | 
| 5   | 中村　瞬次   | A         | 
| 6   | 末長　信吾   | A         | 
| 7   | 高橋　直美   | O         | 
| 8   | 渋江　洋子   | AB        | 
| 9   | 高橋　幸雄   | O         | 

- 下記の通りSQL文を記述
- A型のユーザが見つかったら2行目のSUM関数内のビットを立てる
- B型以降も同様

```sql
select
	sum (case when bloodtype = 'A' then 1 end) A型, 
	sum (case when bloodtype = 'B' then 1 end) B型, 
	sum (case when bloodtype = 'O' then 1 end) O型, 
	sum (case when bloodtype = 'AB' then 1 end) AB型, 
	sum (case when bloodtype is null then 1 end) 血液型不明
from テーブル;
```
- 実行結果は下記の通り
- 各血液型の人数を1行にまとめることができた

| A型 | B型 | O型 | AB型 | 血液型不明 | 
| --- | --- | --- | ---- | ---------- | 
| 3   | 1   | 3  | 1    | 1          | 

## 副問い合わせ（サブクエリ）
```sql
SELECT カラム1 FROM テーブル2 WHERE (SELECT カラム2 FROM テーブル2 WHERE 条件式)
```

- `SELECT`文の中にさらに`SELECT`文を使用したSQL
- 前者の`SELECT`文を主問い合わせ、後者を副問い合わせ(サブクエリ)という
- 副問い合わせは主問い合わせより先に実行される
- `FROM`句で副問い合わせを実施することも可能

### EXISTS
```sql
SELECT カラム FROM テーブル WHERE EXISTS (副問い合わせ)
```

- 表に特定の行が存在するかを確認する副問い合わせ
- `WHERE`句の中で使用し、`EXISTS`や`NOT EXISTS`の後ろに副問い合わせがくる

## 集合演算子
- 2つ以上のSQLの抽出結果を1つの結果として抽出する処理
(ANDやORのようなイメージ)

```sql
SELECT カラム1 FROM テーブル1
UNION
SELECT カラム2 FROM テーブル2
```

| SQL文 | 実行結果 |
|:-|:-|
| UNION  | 2つ以上のSQLの抽出結果を連結し重複した行を削除して結合した結果を出力<br>(＝同じ行が2つ以上あっても1行しか抽出されない)  |
| UNION ALL  | 2つ以上のSQLの抽出結果を連結し重複した行も含めて結合した結果を出力<br>(＝同じ行が2つ以上あった場合は全て抽出される)  |

- 抽出結果のカラム名は1つ目のSQL文で指定したカラム名となる
- `ORDER BY`句を使用する場合は一番最後に記述し、1つ目のSQL文のカラム名を指定する必要がある