SQL基礎　~備忘録~

##はじめに
この記事はプログラミング初学者による備忘録用の記事であり、また、少しでも他の初学者のお役に立てればと思い書いています。

今回は、Laravelを使用しているとEloquentを多用してSQLが疎かになりがちなので、再度基礎から復習したことを、備忘録としてまとめておきたいと思います。

間違いなどがございましたら、ご指摘のほどよろしくお願い致します。


##SQLとは
SQL(Structured Query Language)は国際標準化機関であるISOによって標準化されているデータベース言語です。

データの検索だけでなく、データの登録や削除といったデータベース操作のほとんどをSQLで行なうことができます。

SQLは、基本的に下記4つの構成要素に分かれていると思っています。

1.テーブルの結合
2.計算
3.絞り込み
4.並び替え

各段階で、どのようにデータが操作されるかをイメージすることが重要になってきます。

また、SQLでは様々なワードが使用されますが、データを取得する際は基本的に``SELECT``、 ``FROM``、``WHERE``はどのクエリにも使用されるワードです。複雑なクエリもこの3つのワードをベースとして構成されています。

##SQLの種類
SQL文は、RDBMSに与える命令の種類により、次の3つに分類されます。

**・DDL（Data Definition Language）**
DDL（データ定義言語）は、データを格納する入れ物であるデータベースやテーブルなどを作成、削除します。
DDLに分類される命令は下記のとおりです。

``CREATE``：データベースやテーブルなどを作成する
``ALTER``：データベースやテーブルなどの構成を変更する
``DROP``：データベースやテーブルなどを削除する
 
**・DML（Data Manipulation Language）**
DML（データ操作言語）は、テーブルの行を検索、変更します。
DMLに分類される命令は下記のとおりです。

``SELECT``：テーブルから行を検索する
``INSERT``：テーブルに新規行を登録する
``UPDATE``：テーブルの行を更新する
``DELETE``：テーブルの行を削除する
 
**・DCL（Data Control Language）**
DCL（データ制御言語）は、データベースに対して行なった変更を確定、取り消します。そのほか、RDBMSのユーザがデータベースにあるもの（テーブルなど）を操作する権限の設定も行ないます。
DCLに分類される命令は下記のとおりです。

``COMMIT``：データベースに対して行なった変更を確定する
``ROLLBACK``：データベースに対して行なった変更を取り消す
``GRANT``：ユーザに操作の権限を与える
``REVOKE``：ユーザから操作の権限を奪う
##SQLを理解するためのコツ
存在する表(table)をきちんと把握し、その表(table)からどのようなデータを取得したいか、どのような新しいデータを作りたいか等をイメージすることだと思います。

##SQLの記述ルール
**・SQL文の最後に「;」をつける**
1つのデータベース操作は、1つのSQL文で記述します。文には当然、終わりを示す区切り文字が必要であり、SQLでは文の区切り文字としてセミコロン``;``を使います。

**・大文字・小文字は区別されない**
区別されないので、読みやすいように大文字と小文字を下記のように使い分けます。
・キーワード(予約語)は大文字
・そのほか（列名など）は小文字

ただし、テーブルに登録されているデータについては、大文字・小文字が区別されます。

**・文字列と日付はシングルクォーテーションで囲み、数字は囲まない**
``文字列``をSQL文の中に記述する場合には、'abc'というようにシングルクォーテーションで文字列を囲み、それが文字列であることを示します。

``日付``をSQL文の中に記述する場合には、文字列と同じくシングルクォーテーションで囲みます。

一方、``数字``をSQL文の中に記述する場合には、10というように数字だけを書きます。

**・単語は半角スペースか改行で区切る**
　SQL文では、単語と単語の間を``半角スペース``または``改行``で区切ります。
##SQLを書くコツ
``・予約語は大文字で記述する``

**予約語とは** SQLで予め使うことが決まっているワード
SQLの予約語を大文字にすることで、ワードが予約語なのか、テーブル名やカラム名、ユーザーが決めたワードなのかを瞬時に判断できるようになります。

```sql
-- 例
SELECT COUNT(name)
FROM members
WHERE age >= 20;
```

``・SQLの予約語の後は改行する``

**対象となる予約語(一部)**

・SELECT
・FROM
・WHERE
・ORDER BY
・GROUP BY


``・改行後のインデントを揃える``
構造を見やすくするために、インデントを揃えます。

```sql
-- 例
SELECT COUNT(name) AS name_count
FROM members
WHERE age >= 20;
```

``・1つのカラムを1行に収める``

1行に詰め過ぎると読み辛くなるので、長過ぎる場合は1カラムごとに行を分けます。

また、AND/ORを使用する場合、読みやすくするためにAND/ORは先頭に置くようにします。

```sql
-- 例
SELECT name,AVG(price),DATE(created_at) AS created_date,
FROM flowers
WHERE price >= 1000 
AND name = Marigold;
```

##SELECT文 実例
下記テーブルをベースとして実例をまとめておきます。

``members table``

|id|name|age|feature|
|:--|:--|:--|:--|
|1|bob|22|student|
|2|jin|17|student|
|3|vodka|39|office worker|

###データを取得する
**・全カラムを取得する [*(アスタリスク)]**

```sql
SELECT * 
FROM members;
```
**・対象テーブルからカラム名を指定してデータを取得する**

```sql
SELECT name,feature
FROM members;
```
###検索結果の加工

**・カラム名の変更 [AS]**

```sql
SELECT name AS '名前', feature AS '特徴'
FROM members;
```

**・重複なしで指定したデータを取得する [DISTINCT]**

```sql
SELECT DISTINCT(name)
FROM members;
```
**・データの並び替え [ORDER BY | ORDER BY DESC]**

```sql
SELECT * 
FROM members 
ORDER BY age;

SELECT * 
FROM members 
ORDER BY age DESC;
```

**・検索結果を足し合わせる[UNION]**

```sql
SELECT name , age
FROM members1;
UNION
SELECT name , age
FROM members2;
```

**・初めの検索結果から次の検索結果と重複するデータを取り除く[EXCEPT]**

```sql
SELECT name , age
FROM members1;
EXCEPT
SELECT name , age
FROM members2;
```

**・2つの検索結果で重複するデータを取得[INTERSECT]**

```sql
SELECT name , age
FROM members1;
INTERSECT
SELECT name , age
FROM members2;
```

###・条件指定でデータを取得

**・条件指定[where]**

```sql
SELECT name , age
FROM members 
WHERE age > 20;
```

###・複数条件指定

**[AND , OR , BETWEEN , IN , NOT IN , LIKE , NULL , IS NOT NULL , LIMIT]**

**・AND/OR**

```sql
-- AND
SELECT name ,age 
FROM members 
WHERE age > 20 AND name = bob;

-- OR
SELECT name ,age 
FROM members 
WHERE age > 20 OR name = bob;
```

**・BETWEEN**

```sql
SELECT name, age 
FROM members 
WHERE age BETWEEN 20 AND 40;
```
**・IN/NOT IN**
指定した要素を含むレコード(行)を取得 / 指定した要素を含んでいないレコード(行)を取得

```sql
-- IN
SELECT * 
FROM members 
WHERE age IN('bob','jin');

-- NOT IN
SELECT * 
FROM members 
WHERE age NOT IN('bob','jin');
```

``補足``
**NOT IN**で指定したリストやサブクエリーの値に``NULL``が存在すると、常に空の結果が返ってきてしまいます。従って、**NOT IN**で指定するカラムに``NULL``が入る可能性がある時は、WHERE句で``IS NOT NULL``を指定する必要があります。

**・LIKE**
LIKE句を使用すると、対象のレコード(行)に対して文字列検索を行うことができます。

検索対象となる文字列には複数の記述方式があります。

``完全一致``

```sql
WHERE name LIKE '田中'
```

``部分一致``
部分一致の場合、ワイルドカードを使用して部分一致検索を行うことができます。

|ワイルドカード文字|意味|
|:--|:--|
|%|0文字以上の任意文字列|
|_|1文字の任意文字列|

``例``

|id|name|
|:--|:--|
|1|田中一郎|
|2|田中二郎|
|3|鈴木一郎|
|4|田中元|

```sql
WHERE name LIKE '田中%';
-- 田中一郎と田中二郎が検索対象となる

WHERE name LIKE '%一郎';
-- 田中一郎と鈴木一郎が検索対象となる

WHERE name LIKE '田中_';
-- 田中元が検索対象となる
```
**・IS NULL/IS NOT NULL**
NULLかどうかをチェックする / NULL以外をチェックする

```sql
-- IS NULL
SELECT * 
FROM members 
WHERE age IS NULL;

-- IS NOT NULL
SELECT * 
FROM members 
WHERE age IS NOT NULL;
```
**・LIMIT**
SELECT 文でデータを取得する時に、取得するデータの行数の上限を設定することができます。

```sql
SELECT * 
FROM members 
LIMIT 2; 
```



##集計関数
集計関数には下記のようなものがあります。

``計数``
・COUNT : レコード数

``集計``
・MAX : 最大値
・MIN : 最小値
・SUM : 合計値
・AVG : 平均値

``使用例 [members table]``

|name|age|job|created_at|
|:--|:--|:--|:--|
|bob|24|office worker|2021-07-14|
|jin|16|student|2021-07-14|
|vodka|55|office worker|2021-07-15|
|curarpikt|17|hunter|2021-07-15|

**・日付ごとに会員登録されたユーザーの年齢の平均値と最大値を求める場合**

```sql
SELECT created_at AVG(age),MAX(age)
FROM members
GROUP BY created_at;
```

##GROUP BY
複数のデータごとに集計したいときに使用します

``注意点``
軸となるカラム名を全て``GROUP BY``で指定します

``使用例 [members table]``

|name|age|job|created_at|
|:--|:--|:--|:--|
|bob|24|office worker|2021-07-14|
|jin|16|student|2021-07-14|
|vodka|55|office worker|2021-07-15|

**・日付ごとに会員登録数を集計する場合**

```sql
SELECT created_at,COUNT(name)
FROM members 
GROUP BY created_at;
```

**・日付ごとに会員登録数を職別で集計する場合**

```sql
SELECT created_at, job COUNT(name)
FROM members
GROUP BY created_at, job;
```

##内部結合と外部結合

複数のテーブル(データ)を結合してデータを取得する場合に使用し、大きく内部結合と外部結合に区分されます。

``テーブルの結合とは``
文字通り、複数のテーブルを結合した上で対象となるデータを取得することです。
下記のように、ベースとなるusersテーブルと、usersテーブルのプライマリーキーをuser_idとして含むtravelsテーブルをまとめた上で、データを取得します。



``users table``

|id|name| 
|:--|:--| 
|1|bob|  
|2|kaito|
|3|jin|
|4|vodka|
|5|roy|

``travels table``

|id|user_id|country|
|:--|:--|:--|
|1|2|イギリス|
|2|5|ポーランド|
|3|1|イタリア|
|4|4|カナダ|
|5|2|アメリカ|
|6|6|日本|

**・userの渡航履歴を取得する場合(内部結合)**

```sql
SELECT *
FROM users AS u
INNER JOIN travels AS t ON u.id = t.user_id;
```
``結果``

|id|name|id|user_id|country|
|:--|:--|:--|:--|:--|
|1|bob|3|1|イタリア|
|2|kaito|1|2|イギリス|
|2|kaito|5|2|アメリカ|
|4|vodka|4|4|カナダ|
|5|roy|2|5|ポーランド|

``注意点``

**・複数テーブルを扱う場合、カラム名の前にテーブル名を記述する必要がある**

複数テーブルで同じカラム名がある場合、SQL側がどのテーブルのカラムか判断できなくなりエラーが発生するため、カラム名の前にテーブル名を指定することで区別するようにします。

**・内部結合を使う場合、条件に合わないデータは結果から削除される**

結果データで条件に合わないレコードが削除されないようにするためには、``INNER JOIN``(内部結合)ではなく``LEFT OUTER JOIN``等の**外部結合**を使用します。
外部結合の結果として、条件と合わないレコードにはデータを消さずにnullが代入される。このように対応することで、ベースとなるテーブルのデータが結果から削除されなくなります。

**・userの渡航履歴を取得する場合(LEFT OUTER JOIN外部結合)**

LEFT OUTER JOINの場合、左側のテーブルを基準としてデータを操作します。
下記コードの場合、``usersテーブル``が基準となります。

```sql
SELECT *
FROM users AS u
LEFT OUTER JOIN travels AS t ON u.id = t.user_id;
```
``結果``


|id|name|id|user_id|country|
|:--|:--|:--|:--|:--|
|1|bob|3|1|イタリア|
|2|kaito|1|2|イギリス|
|2|kaito|5|2|アメリカ|
|3|jin|null|null|null|
|4|vodka|4|4|カナダ|
|5|roy|2|5|ポーランド|

``注意点``
**・LEFT OUTER JOINでの外部結合を行う場合、条件に合わないデータをnullとして取得する**
内部結合のように、それぞれのテーブルの指定したカラムの値が一致するものを結合するのに加え、左側のテーブルにしか存在しないものに関しても、対となる右側のテーブルのデータをnullとして取得します。
また、左のusersテーブルが基準となっているので、並び順もusersテーブルのidがベースとなっています。

**・userの渡航履歴を取得する場合(RIGHT OUTER JOIN外部結合)**

RIGHT OUTER JOINの場合、右側のテーブルを基準としてデータを操作します。
下記コードの場合、``travelsテーブル``が基準となります。

```sql
SELECT *
FROM users AS u
RIGHT OUTER JOIN travels AS t ON u.id = t.user_id;
```

``結果``

|id|name|id|user_id|country|
|:--|:--|:--|:--|:--|
|2|kaito|1|2|イギリス|
|5|roy|2|5|ポーランド|
|1|bob|3|1|イタリア|
|4|vodka|4|4|カナダ|
|2|kaito|5|2|アメリカ|
|null|null|6|6|日本|


``注意点``
**・RIGHT OUTER JOINでの外部結合を行う場合、条件に合わないデータをnullとして取得する**
内部結合のように、それぞれのテーブルの指定したカラムの値が一致するものを結合するのに加え、右側のテーブルにしか存在しないものに関しても、対となる左側のテーブルのデータをnullとして取得します。
また、右のtravelsテーブルが基準となっているので、並び順もtravelsテーブルのidがベースとなっています。

``まとめ``

**・内部結合(INNER JOIN)**
結合すべき行が見つからなかった場合、その行は消滅します。

**・外部結合(LEFT OUTER JOIN , RIGHT OUTER JOIN)**
 結合すべき行が見つからない場合、その値がNULLとして生成され結合します。
左右いずれかの表を優先させたいときに``LEFT OUTER JOIN``か``RIGHT OUTER JOIN``を指定します。

``LEFT JOIN``を指定とすると、左側のテーブルすべての行が表示されます。

``RIGHT JOIN``とすると、右側のテーブルすべての行が表示されます。

従って、LEFT JOINおよびRIGHT JOINを使用するときは、テーブル名を左右どちらに記述するか注意する必要があります。


##CASE式(条件分岐)
条件を指定して集計したいときにCASE式を利用します。
集計用の分類単位で集計したいときに集約関数とGROUP BYを組み合わせることで恩恵を受けられます。
しかし、プログラム側で条件分岐を書いた方がシンプルになる場合もあるので、何でもCASE式で書くべきではないと思います。

``注意点``
・ELSEも忘れずに書く
・条件は排他的に書く
・真（true）がマッチ対象であり、偽（false）と不明（unknown）はマッチ対象ではない

また、CASE式には``単純CASE式``と``検索CASE式``があります。

``単純CASE式`` 等価条件が真であるかを評価し、クエリをシンプルにできます。
``検索CASE式`` 応用が効きやすく検索CASE式で単純CASE式の内容を表すこともできます。

``単純CASE式と検索CASE式``

```sql
-- 単純CASE式
SELECT 
 CASE
  WHEN 条件 THEN 値
  WHEN 条件 THEN 値
  ELSE 値　
 END
FROM テーブル名

-- 検索CASE式
SELECT 
 CASE
  WHEN カラム名 = 条件 THEN 値
  WHEN カラム名 = 条件 THEN 値
  ELSE 値　
 END
FROM テーブル名
```

``補足``
検索CASE式は、「=」以外にも、不等号やIN句でも表現できます。



```sql
CASE
    WHEN vegetable IN ('トマト') THEN '果菜'
    WHEN vegetable IN ('ゴボウ', 'ニンジン', 'カブ', 'サツマイモ', 'レンコン') THEN '根菜'
    WHEN vegetable IN ('ネギ') THEN '葉茎菜'
    ELSE NULL
END
```

**・商品のジャンルごとに合計値を算出する場合**

``items table``

|name|price|
|:--|:--|
|バット|25,000|
|タオル|2,000|
|スパイク|9,500|
|ゲーム機|45,000|
|化粧水|4,000|

``実行例``

```sql
SELECT 
 CASE 
  WHEN name IN('バット', 'スパイク') THEN 'スポーツ用品'
  WHEN name IN ('タオル', '化粧水') THEN '生活用品'
  WHEN name IN ('ゲーム機') THEN '娯楽用品'
  ELSE NULL
 END AS classification_name,
SUM(price)
FROM items
GROUP BY
 CASE WHEN name IN('バット', 'スパイク') THEN 'スポーツ用品'
 WHEN name IN ('タオル', '化粧水') THEN '生活用品'
 WHEN name IN ('ゲーム機') THEN '娯楽用品'
 ELSE NULL
END;
```
``結果``

|classification_name|price|
|:--|:--|
|スポーツ用品|34,500|
|生活用品|6,000|
|娯楽用品|45,000|

##参考文献
[達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ](https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6SQL%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-%E7%AC%AC2%E7%89%88-%E5%88%9D%E7%B4%9A%E8%80%85%E3%81%A7%E7%B5%82%E3%82%8F%E3%82%8A%E3%81%9F%E3%81%8F%E3%81%AA%E3%81%84%E3%81%82%E3%81%AA%E3%81%9F%E3%81%B8-%E3%83%9F%E3%83%83%E3%82%AF-ebook/dp/B07GB4CNKP/ref=pd_sbs_1/356-6974316-7565249?pd_rd_w=ixCvp&pf_rd_p=367c54b8-500b-4071-9b4d-65fe16192688&pf_rd_r=P7QPGCDSSKCCS0C188TQ&pd_rd_r=ebb98fae-1564-4f5e-9972-4c2d6e92b3de&pd_rd_wg=pDo7T&pd_rd_i=B07GB4CNKP&psc=1)
[CASE式のススメ（前編）](https://codezine.jp/article/detail/404)
[CASE式のススメ（後編）](https://codezine.jp/article/detail/405)
[ユーザーの年齢・性別と購買履歴を活用する「属性」×「行動」分析のSQL](https://codezine.jp/article/detail/10611)
[SQLで基本のデータ解析に挑戦する](https://codezine.jp/article/detail/10154)
[初心者向けの解説書でSQLとリレーショナルデータベースの基本を押さえよう](https://codezine.jp/article/detail/12216)