SQL基礎を焼鳥で

#この記事について
初学者による、SQL基礎学習内容のアウトプット・自身の振り返り用メモです。
楽しくメモするために、データが焼鳥です。

**参考資料**

* [Progate SQL学習コース](https://prog-8.com/languages/sql)

* [キタミ式イラストIT塾 基本情報技術者 令和02年 Kindle版](https://www.amazon.co.jp/%E3%82%AD%E3%82%BF%E3%83%9F%E5%BC%8F%E3%82%A4%E3%83%A9%E3%82%B9%E3%83%88IT%E5%A1%BE-%E5%9F%BA%E6%9C%AC%E6%83%85%E5%A0%B1%E6%8A%80%E8%A1%93%E8%80%85-%E4%BB%A4%E5%92%8C02%E5%B9%B4-%E3%81%8D%E3%81%9F%E3%81%BF-%E3%82%8A%E3%82%85%E3%81%86%E3%81%98-ebook/dp/B082DT76Z8/ref=sr_1_1?__mk_ja_JP=%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A&crid=3JMDJMOJXZU52&dchild=1&keywords=%E3%82%AD%E3%82%BF%E3%83%9F%E5%BC%8F%E3%82%A4%E3%83%A9%E3%82%B9%E3%83%88it%E5%A1%BE+%E5%9F%BA%E6%9C%AC%E6%83%85%E5%A0%B1%E6%8A%80%E8%A1%93%E8%80%85+%E4%BB%A4%E5%92%8C02%E5%B9%B4+%E6%83%85%E5%A0%B1%E5%87%A6%E7%90%86%E6%8A%80%E8%A1%93%E8%80%85%E8%A9%A6%E9%A8%93&qid=1607565323&sprefix=%E3%82%AD%E3%82%BF%E3%83%9F%2Caps%2C116&sr=8-1)


#【データベースとは】
### データベース
アプリケーションのデータを保存・蓄積するための一つの手段
大量のデータを蓄積し、必要な情報の抜き出し、更新が柔軟に行える
データベースの代表的な種類は下記3つ
・関係型：データを表で管理
・階層型：データを階層で管理
・ネットワーク型：データを網状に管理
関係型のデータベースが現在の主流

### DBMS (Data Base Management System)
データベースを利用したいアプリケーションのために
データベース機能を提供するミドルウェア



### データベースの仕組み
関係データベースは表でデータを管理
・表：table
・列：Column, Field
・行：Record, Row
必要に応じて複数のテーブルを作成することが可能

#【SQLとは】

### SQL (Structured Query Language)
DBMSへ命令（クエリ）を伝える（書く）ためのデータベース言語
大文字と小文字を区別しない

**-- クエリ**
データベースからデータ取得するために送る命令
命令は下記言語によって構成される

**-- データ定義言語 / DDL** (Data Definition Language）
スキーマの定義や表の作成といった定義を担当
CREATE / DROP / ALTER / RENAME / TRUNCATE

**-- データ操作言語 / DML** (Data Manipulation Language)
データの抽出や挿入、更新や削除といった操作を担当
SELECT / INSERT / UPDATE / DELETE / MERGE

**-- データ制御言語 / DCL** (Data Control Language)
データベースへのアクセス権限付与／取消を担当
GRANTE / REVOKE

**-- トランザクション制御言語 / TCL** (Transaction Control Language)
COMMIT / ROLLBACK / SAVEPOINT / SET TRANSACTION

※この記事に記載が無いものも列挙しています。後々追記するかもしれません。

#【SQL基本】

### 取得

```sql
SELECT カラムA, カラムB
FROM テーブル名;
```
**- SELECT**
データベースから「どのカラムのデータを取得するか」を選ぶ

```sql
SELECT name, price
-- 複数のカラムから取得する場合はカンマで区切る
SELECT *
-- 全カラムのデータを取得する場合は＊を使用
```
**- FROM**
SELECTで選んだカラムがあるテーブルを指定する

```sql
SELECT name -- 2.カラム:nameのデータを取得
FROM yakitories -- 1.yakitoriテーブルの
```

### 挿入
```sql
INSERT INTO テーブル名(カラムA, カラムB...)
VALUES(値1, 値2...);
```

### 更新
```sql
UPDATE テーブル名
SET カラム名 = 値1, カラム名 = 値2
WHERE 条件; -- 必ず指定
```

### 削除
```sql
DELETE FROM テーブル名
WHERE 条件; -- 必ず指定
```

#【条件】

### WHERE
特定のデータを取得するため「どのレコードを取得するか」を指定する

```sql
SELECT *
FROM yakitories
WHERE part = "皮";
```

### LIKE演算子
%をワイルドカードとして扱い、ある文字を含むデータを取得する
前方一致　%gima
後方一致　negi%

```sql
SELECT *
FROM yakitories
WHERE name LIKE %gi%;
```

### NOT演算子
「〇〇を含まないデータ」や「〇〇に一致しないデータ」のような条件で
データを取得したい場合は「否定」を意味する「NOT演算子」を使用

```sql
SELECT *
FROM yakitories
WEHRE NOT flavor LIKE "%sauce";
```

### NULL
中身が何かわからないことを示す
IS NUMLL / IS NOT NULLでデータを取得 (「 = 」は使えない)

```sql
-- IS NULL (指定したカラムのデータがNULLであるものを取得)
SELECT * FROM yakitories
WHERE price IS NULL;

-- IS NOT NULL (指定したカラムのデータがNULLでないものを取得)
SELECT * FROM yakitories
WHERE price IS NULL;
```

### AND演算子
「WHERE 条件１ AND 条件２」のようにすることで
条件１と条件２を「共に満たす」データを検索できる

```sql
SELECT * FROM yakitories
WHERE manu = "gizzard" -- 条件1
AND category = "chicken"; -- 条件2
```

### OR演算子
「WHERE 条件１ OR 条件２」のようにすることで
条件１または条件２の「どちらかを満たす」データを検索できる

```sql
SELECT * FROM yakitories
WHERE category = "chicken" -- 条件1
OR category = "pork"; -- 条件2
```

### ORDER BY
「（基準となる）並べ替えたいカラム名」と「並べ方」を指定して
クエリの末尾に記述することで、取得結果のデータを並び替える
**-- ASC / 昇順**
**-- DESC / 降順**

```sql
-- ORDER BY カラム名 並べ方;
SELECT *
FROM yakitories
ORDER BY price DESC;

-- WHEREと併用可能
SELECT *
FROM yakitories
WHERE category = "chicken"
ORDER BY price DESC;
```

### LIMIT
最大で取得するデータ件数を指定する
クエリの末尾に記述することで、取得するデータの数を制限する

```sql
-- LIMIT データの件数;
SELECT *
FROM yakitories
LIMIT 5;

-- LIMITもORDER BYと同様にWHEREと併用できる
SELECT *
FROM yakitories
WHERE category = "beef"
LIMIT 5;
```

**-- ORDER BYとLIMITの組み合わせ**
2つを組み合わせて、注文数が多い上位5件を取得する、等が可能

```sql
SELECT *
FROM yakitories
ORDER BY number_of_orders DESC
LIMIT 5; -- LIMITを末尾にする
```

#【検索結果の加工】
### DISTINCT
SELECTで取得するカラムに使用することで、重複を省いたデータを取得できる

```sql
-- DISTINCT(カラム名)
SELECT DISTINCT(price)
FROM yakitories;
```

### 四則演算・関数
* + 足す
* - 引く
* * 掛ける
* / 割る
* round 四捨五入、四捨五入する桁の位置の指定も可能
* round(5.355) --5
* round(5.355, 1) --5.4
* floor 少数点以下切り捨て
* ceil 小数点以下切り上げ
* rand() 0以上1未満のランダムな数値を返してくれる

＜rand補足：抽選で使える＞
select * from yakitories order by rand() limit 1;
乱数で並び替えそのうち1つだけ取り出す
ランダムな値をもとに並び替え、一番上のレコードだけを抽出
抽選1名を選ぶなどの処理に使える

#【集計関数】

### SUM関数
SELECTで取得するカラムに用いることで、合計を取得できる

```sql
-- SUM(カラム名)

SELECT SUM(price)
FROM yakitories;
```

**-- WHEREとSUM関数**
SUM関数はWHEREと併用できる

```sql
SELECT SUM(price) -- 2.検索したレコードの数値の合計を計算
FROM yakitories
WHERE menu = "chicken_wings"; -- 1.とり手羽のレコードを検索
```
### AVG関数
SELECTで取得するカラムに使用することで、計算平均を取得できる

```sql
-- AVG(カラム名)

SELECT AVG(price)
FROM yakitories;
```

**--WHEREとAVG関数**
AVG関数はWHEREと併用することができる

```sql
SELECT AVG(price) -- 2.検索したレコードの数値の平均を計算
FROM yakitories
WHERE name = "yojij"; -- 1.yojijiのレコードを検索
```

### COUNT関数
SELECTで指定したカラムのデータの合計数を取得できる

```sql
-- COUNT(カラム名)

SELECT COUNT(*)
FROM yakitories
```

**-- COUNT関数とnull**
COUNT関数でカラム名を指定した場合
nullになっているデータの数は計算されない

nullの数も含めてデータの数を計算したい場合は
COUNT関数で全てのカラム「*」を指定する


**-- WHEREとCOUNT関数**
COUNT関数はWHEREと併用することができる

```sql
SELECT COUNT(*) -- 2.検索したレコードの数を計算
FROM yakitories
WHERE category = "chicken"; -- 1.chickenのレコードを検索
```

### MAX / MIN
SELECTで取得したカラムに使用
MAX(カラム名)：指定したカラムのデータの中から最大のデータを取得
MIN(カラム名)：指定したカラムのデータの中から最小のデータを取得

```sql
SELECT MAX(price)
FROM yakitories;
```

**-- WHEREとMAX・MIN関数**
MAX,MINはWHEREと併用できる

```sql
SELECT MAX(price) -- 2.検索したレコードの中の最大値を取得
FROM yakitories
WHERE category = "chichen"; -- 1.chickenのレコードを検索
```

#【グループ化】

### GROUP BY
「GROUP BY カラム名」とすることで、指定したカラムで
完全に同一のデータを持つレコード同士が同じグループとなる



**-- グループ化と集計**
集計関数を取得するFROMの後ろに「GROUP BY カラム名」を追加する
集計関数により各グループごとにデータが集計される

```sql
SELECT SUM(price), purchased_at -- 2.集計する
FROM yakitories
GROUP BY purchased_at; -- 1.グループ化する
```

**-- GROUP BYの注意点**
GROUP BY使用時、SELECTで使えるのは
GROUP BYに指定しているカラム名と、集計関数のみ

**-- 複数カラムのGROUP BYの書き方**
GROUP BYは複数のカラム名を適用させることができ
その場合は、カラム名同士をコンマ(,)で繋げる

**-- WHEREとGROUP BY**
GROUP BYはWHEREとも併用することができ、その場合はWHEREの後に書く
WHEREはまず最初に、そのあとにGROUP BYと関数が実行される

```sql
SELECT 集計関数
FROM テーブル名
WHERE 条件
GROUP BY カラム名, カラム名; -- WHERE文の後に書く
```

### HAVING
GROUP BYでグループ化したデータを更に絞り込みたい場合にはHAVINGを使用
「GROUP BY カラム名 HAVING 条件」のようにすることで
条件を満たすグループを取得することができる

```sql
GROUP BY カラム名
HAVING 条件;
```

**--WHEREとHAVING**
グループ化した後のデータを絞り込む際、WHEREではなくHAVINGを使うのは
SQLの各コマンドが以下の順番で実行されていくため

**-- 関数の実行順**

| 実行順 | 実行内容 | 命令 |
|:-:|:-:|:-:|
| 1 | 検索 | WHERE |
| 2 | グループ化 | GROUP BY |
| 3 | 集計関数 | COUNT / SUM / AVG / MAX / MIN |
| 4 | HAVING | HAVING |


**-- WHEREとHAVINGの違い**
WHEREはグループ化される前のテーブル全体を検索対象とするのに対し
HAVINGはGROUP BYによってグループ化されたデータを検索対象とする

**-- HAVINGの注意点**
HAVINGはグループ化された後のテーブルから検索するため
条件文で使うカラムは必ずグループ化されたテーブルのカラムを使う

```sql
SELECT SUM(price), purchased_at
FROM yakitories
GROUP BY purchased_at
HAVING SUM(price) > 5000; -- グループ化された後のデータのカラムを使う
```

#【サブクエリ】
### サブクエリ
SQLでは、クエリの中に他のクエリ（サブクエリ）を入れられる
2つ以上のクエリを1つにまとめられる為、複雑なデータを取得する際に使用される

```sql
SELECT menu
FROM yakitories
WHERE price > ( -- ()で囲む
  SELECT price # --　ぼんじりの価格を取得
  FROM yakitories
  WHERE menu = "bonjiri"
);　-- ()内にセミコロン（;）は不要
```

**-- サブクエリの実行順番**
サブクエリが実行された後、外側にあるクエリが実行される

### AS
ASを使うことでカラム名などに別名を定義することができる
「カラム名 AS "名前"」でカラム名に定義する名前を指定

```sql
SELECT number_of_orders AS "皮の注文数"
FROM yakitories
WHERE manu = "yakitori_skin";
```

#【複数テーブル】
複数のテーブルを紐づけるために、外部キーと主キーを使う
外部キーで他のテーブルにある主キーを指定することで
テーブル同士を紐付けることができる

**- テーブルを紐づけるメリット**
カラムを追加するのではなくテーブルに紐づけることでデータ管理がしやすくなる

- yakitoriesテーブル

| id | name | cotegory_id (外部キー) |
|:-:|:-:|:-:|
| 1 | thigh_meat | 1 |
| 2 | pork_rose | 2 |
| 3 | bonjiri | 1 |
| 4 | ox_tongue | 3 |

- categorysテーブル

| id | name (主キー) |
|:-:|:-:|
| 1 | chicken |
| 2 | pork  |
| 3 | beef  |



### JOIN
JOINは複数のテーブルを1つに結合したいときに使う
ONで条件を指定して、テーブルAにテーブルBを結合する
結合したテーブルは1つのテーブルとしてデータを取得できる

```sql
SELECT *
FROM テーブルA
JOIN テーブルB
ON 結合条件
```

**- 結合条件**
「ON テーブル名.カラム名 = テーブル名.カラム名」で指定

```sql
SELECT *
FROM テーブルA
JOIN テーブルB
ON テーブルA.カラム名 = テーブル名B.カラム名

SELECT *
FROM yakitories
JOIN categorys
ON yakitories.cotegory_id = cotegorys.id;
```

**- JOINの実行順序**
JOINを含んだクエリでは、はじめにJOINが実行される
その次に、結合されたテーブルに対してSELECTが実行される

**- 複数テーブルでのカラム指定**
複数のテーブルに同じカラム名が存在するときは
「それぞれのテーブル名.カラム名」で指定しなければならない

```sql
SELECT yakitories.name, cotegorys.name
FROM yakitories
JOIN categorys
ON yakitories.cotegory_id = cotegorys.id;
```

**- WHERE内でテーブル名を指定**
「テーブル名.カラム名」を用いたカラム指定はWHERE内でも使える

```sql
SELECT *
FROM yakitories
JOIN categorys
ON yakitories.cotegory_id = cotegorys.id;
WHERE yakitries.name = "ox_tongue";
-- WHERE内でもテーブル名を指定することで取得するカラムを指定できる
```

### LEFT JOIN
LEFT JOINを使うことで、FROMで指定したテーブルのレコードを全て取得する
外部キーがNULLのレコードもNULLのまま実行結果に表示される

```sql
SELECT *
FROM yakitories -- LEFT JOINを使うと元テーブルのレコードを全て取得
LEFT JOIN categorys
ON yakitories.category_id = categorys.id
```

**- ●複数のテーブルのJOIN**
JOINは1つのクエリで、複数回使用できる
JOINを複数回使用しても、FROMは1度だけ書けば大丈夫なので注意

#【全体の実行順序】
SQLは、取得するテーブルを形成してから検索を行うので
FROM・JOINが先に行われることを覚えておく

| 実行順 | 実行内容 | 命令 |
|:-:|:-:|:-:|
| 1 | テーブルの指定 | FROM |
| 2 | 結合 | ON / JOIN |
| 3 | 取得条件 | WHERE |
| 4 | グループ化 | GROUP BY |
| 5 | 関数 | CONUT / SUM / AVG / MAX / MIN |
| 6 | HAVING | HAVING |
| 7 | 検索 | SELECT / DISTINCT |
| 8 | 順序 | ORDER BY |
| 9 | LIMIT | LIMIT |

**- NULLを含んだ場合の実行結果**
JOINを使った結合は、FROMで指定したテーブルを基準に実行される
ただし外部キーがNULLのレコードは、実行結果に表示されない

#【データの追加】

### INSERT
テーブルにレコードを挿入したいときはINSERTを使う
INSERTとは「挿入する」という意味

```sql
INSERT INTO テーブル名(カラム名1, カラム名2,...)
VALUE(値a1, 値a2,...)

INSERT INTO yakitories (id, name, category_id) # 順番通りに挿入
VALUES(4, "tori_wings", 1);
```

### AUTO INCREMENT
多くの場合、idカラムにはAUTO INCREMENTという機能が使われる
AUTO INCREMENTとは「自動で増加する」といった意味で
データが作成されるときに自動で値が割り当てられる

```sql
INSERT INTO yakitories (□ name, category_id)
VALUES(□ "tori_wings", 1);
-- □ AUTO INCREMENTのカラムは省略できる
```

### UPDATE
データを更新したいときにはUPDATEを使う
複数変更したいカラムがある場合、コンマ(,)で区切る

```sql
UPDATE yakitories
SET name = "pork_fillet", cotegory_id = 2 -- 指定カラムを新しい値に
WHERE id = 1; -- 更新するレコードをWHEREで指定
```

**- UPDATEの注意点**
WHEREで更新するレコードを指定しないと
カラム内の全データが更新されてしまうので注意

**- UPDATEの実行前に**
UPDATE実行後はデータを戻すことができない
実行前に一度SELECTを実行して、操作するデータを確認する習慣をつける

### DELETE
レコードを削除するときはDELETEを使う
UPDATEと同様にクエリ実行後はレコードを元に戻すことができないので
SELECTでレコードを確認してから削除を行う

```sql
DELETE FROM yakitories
WHERE id = 2; -- 削除するレコードをWHEREで指定する
```

**- DELETEの注意点**
WHEREで削除するレコードを指定しないと
テーブル内の全レコードが削除されてしまう