SQL ~データベース操作5~

初学者です。SQLの知識が浅いため、まとめがてら学習し、記述している記事です。

- MySQL
- Ruby on Rails 6.0.0

# はじめに

ここではSQLというDBを操作する言語について書いています。

この記事は、続きの記事になります。
第一回目はこちらになりますので、よろしかったらどうぞ！

今回は、**複雑な条件にあったデータの所得ができるようになる**ことが目標です！

## 前提に
データベースの作成、テーブルやカラムはすでに作成してある、データは存在している、という前提で進めます。
また、Sequel Proを使用したデータ検索です。
**→ ここから先のコードは全てSequel Proのクエリに記述しますのでご注意ください！**

![sql.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1101585/e5aee527-7228-ea3a-04c2-bf5457abcbaf.png)



# 複雑な条件にあったデータを取得する
イメージとしては、

- いくつかのテーブルをまたいで検索する場合、そのテーブル同士を一つにまとめてしまう。
- その後に、特定の条件を指定して情報を引っ張り出す。

といった流れです！

前回は**userテーブル**を中心にデータを取得していましたが、今回は複雑にということで条件を追加してみて行きましょう。
今回は**アルバイトのシフト管理**という名目で行きます。
そのため、**usersテーブル**に合わせて、**shiftsテーブル**を例で用意します。

**usersテーブル**

|カラム名|内容|
|------|----|
|last_name|苗字|
|first_name|名前|
|age|年齢|
|prefecture|出身の都道府県|

**shiftsテーブル**

|カラム名|内容|
|------|----|
|date|日付|
|start_time|勤務開始時間|
|user_id|ユーザー(のid)  |

では早速本題に入りましょう！
複雑な検索をする前に知っておくべきことがあります。実は、**「特定の日にちに誰が何コマシフトに入ったか」**、**「特定の日にちにシフトに入った人」**などの条件で検索するとなると結構複雑になります。

この場合、まずSQLを実行して、その結果と見比べながら考えるのが良いと思います。

最初にデータを結合する**JOIN**について知りましょう！

## JOIN
**JOIN**を使用すると、**指定したテーブルのカラムに登録された値が一致するデータをもとに、各テーブルのレコードを結合**できます。

**JOIN**句は、**FROM**句の後に記述し、結合の対象となるテーブルを指定します。
また**ON**なるものも出てきます!
これは、結合する元となるカラムを、**ON**の後に**"="**を使って指定するためのものです。

```
FROM テーブル名1
JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2
```

実際に例を使った記述で見ていきます。
**「"shiftsテーブル"の"user_id"と、"usersテーブルのid"」が一致する行を結合させたい**、としますと、、

```
FROM shifts
JOIN users ON shifts.user_id = users.id
```

こちらのようになると思います。

ですがこちらの記述、もう少し改良できます！
各テーブルに別名を当てることができます。変数名をつけるみたいですね。

```
FROM shifts s
JOIN users u ON s.user_id = u.id
```

SQL文では、"="などは使用せず、そのテーブルの後ろにちょこっと書くだけでOKです！
一般的には、**テーブル名の頭文字**を指定するそうです。

上記のコマンドを実行すると、**usersテーブル**のデータが結合できました。 

ここまでで、画面には結合されたデータベース情報がバーっと出てます。
![sql1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1101585/c66e7103-1624-3ae8-4208-c82a74eccc52.png)
 
ここから検索するための手段を知りましょう！

## CONCAT
**CONCAT**は、複数の文字列を連結させる句です。
例えば、last_nameカラム(名字)とfirst_nameカラム(名前)を連結させて、
フルネームになるように取り出したい！なんて時に使えます。

記述は以下の通りです。

```
CONTACT(文字列1, 文字列2,....)
```

**※注意点**
この時、もし連結させた文字列の中に**NULL**が存在すると、結果まで**NULL**となります。

## GROUP BY
**GROUP BY**は、ある特定のデータごとに、平均値や総量などを集計するときに使う句です。
この時指定したカラムは**グループ化した**という風にいえます。(後のCOUNTにも繋がってきます)

```
GROUP BY カラム名
```

例えば、このカラム名に**user_id**を入れるとします。すると、user_idごとの平均値や総量を集計することができます！

## COUNT
**COUNT**は、グループ化されたデータに対して使用できる、データの総量をカウントするための句です。
カラムを指定すると、データの行数を取得します。

```
SELECT COUNT (カラム名)
```

**※注意点**
カラムの値が**NULL**だとエラーになります。

では具体的な記述をしてみます。
**2015-07-01に、誰が、何コマシフトに入ったか**という情報が欲しい時、どのように記述できるでしょうか？

```
SELECT
    CONCAT(last_name, first_name) "名前",
    COUNT(*) "コマ数"
FROM shifts s
JOIN users u ON s.user_id = u.id
WHERE date = "2015-07-01"
GROUP BY user_id
```

学んだことが凝縮されて出てきました。笑
順に一つずつ見ていくと本当に単純なことしか書いてませんので、確認していきます。

- SELECTで表示されるものを**名前(CONTACT使用)、「入ったコマ数（COUNT使用）」**で表示した
- WHEREを使って、**日にちの2015年7月1日**を指定した
- GROUP BYで各ユーザーごとに集計ができるように設定した

するとこのように、**「2015-07-01に、誰が、何コマシフトに入ったか」**がわかりました！

![sql3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1101585/78833171-5abd-2466-0289-6c95352c9947.png)

最後に余談となりますが、知っておくと便利なサブクエリについてもまとめていきます！

# サブクエリ
**サブクエリ**とは、ある検索結果を使って、別のSQL文を実行する仕組みのことです。

では**「2015-07-01にシフトに入ってない人」**を取得してみます。
そのためには、まず**「2015-07-01にシフトに入った人の一覧」**を取得します。

```
SELECT user_id
FROM shifts
WHERE date = "2015-07-01"
```

これで以下のような結果がかえってきました。

![qsql4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1101585/02ebfdc9-b229-eaee-f357-9e6f982c60eb.png)

赤枠部分が"2015-07-01"にシフトに入った人のリストです。いくつかidが重複しているのは複数回シフトに入ったためです。

ここから、上記のリストに入ってない人を引っ張り出すことができそうです。
**"usersテーブル"から、"id"がリストに含まれていない行のみ**取得することで実現します。

```
SELECT *
FROM users
WHERE id NOT IN (
　　SELECT user_id
   FROM shifts
   WHERE date = "2015-07-01"
)
```

上記のように記述します。ポイントは**IN**に**NOT**を加えることで、含まれていないことを指定できています。
(ポイントというほどでもないですが💧)

すると以下のような結果が出力されました！

![sql5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1101585/ccf718ce-83ff-ad41-d04d-32657bc70763.png)

# まとめ
ここまでお読みいただきありがとうございます！
正直まだまだSQLを理解しきれていませんし、至らない部分が多いです。

SQL文自体もまだまだ存在していて紹介しきれていないので、次は表形式でまとめられたらなと思います。