Sequel ProでSQL文を実行しデータを検索する方法

### データを検索する
---
usersテーブル

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


- WHERE句

 WHERE句は取得するレコードの条件を指定し、その条件が正のレコードを取得します。（WHERE句はSELECT句、FROM句と合わせてSQL文を実行します。）以下を実行。

```
SELECT *
FROM users
WHERE last_name = "佐藤"
```
実行結果：usersテーブルからlast_nameが"佐藤"さんのレコードが取得できました。


- LIKE句

 LIKE句とは、WHERE句と併用して使うことで「文字列検索」を行うことができる句です。

```
WHERE 《列名》 LIKE '検索文字'
```
曖昧検索として%というワイルドカードも使用できます。下記を実行。

```
SELECT *
FROM users
WHERE prefecture
LIKE '%都'
```

実行結果：usersテーブルからprefectureが"東京都"のレコードが取得できました。

- AND演算子
　AND演算子は複数の条件を指定して式を作り、その条件が全て正のレコードを取得します。下記を実行。

```
SELECT *
FROM users
WHERE age <= 30 AND prefecture = "神奈川県"
```
実行結果：usersテーブルからageが３０以下かつprefectureが神奈川県のレコードが取得できました。

- OR演算子
　OR演算子は複数の条件を指定して式を作り、その条件のうちどれか1つ以上が正となれば式は正となり、レコードを取得します。下記を実行。

```
SELECT *
RFOM users
WHERE age <=25 OR prefecture = "東京都"
```
実行結果：usersテーブルからageが２５以下もしくは、prefectureが東京都であるレコードを取得できました。

- NOT演算子
　NOT演算子は1つの条件を指定して式を作り、その条件に当てはまらないものを取得します。下記を実行。

```
SELECT *
FROM users
WHERE NOT prefecture = "東京都"
```
実行結果：prefectureが東京都ではないレコードを取得できました。

- BETWEEN演算子
　BETWEEN演算子は1つのカラムに対し上限と下限を指定して式を作り、カラムの値がその範囲に含まれるとき、その式は正になり、レコードを取得します。下記を実行。

```
【例】 WHERE 《カラム名》 BETWEEN 《下限》 AND 《上限》

SELECT *
FROM users
WHERE age BETWEEN 21 AND 24
```
実行結果：usersテーブルからageが21~24の範囲に含まれるレコードが取得できました。

- IN演算子
 IN演算子は***1つのカラムに対してリストを指定***して、カラムの値がそのリストに含まれるときその式は正になり、レコードを取得します。下記を実行。

```
SELECT *
FROM users
WHERE prefecture IN ("東京都", "神奈川県")
```
実行結果：usersテーブルからprefectureが"東京都"もしくは"神奈川県"であるレコードが取得できました。


### 複雑な条件に合ったデータを取得
---

shiftsテーブル

|カラム名         |内容                        |
|---------------|---------------------------|
|date           |シフトの日付                  |
|start_time     |シフトの開始時間               |
|user_id        |このシフトが属するユーザーのid    |


- JOIN句

　JOIN句は、指定したテーブルのカラムに登録された値が一致するデータを元に、各テーブルのレコードを結合できます。JOIN句はFROM句のあとに記述し、結合対象となるテーブルを指定します。そして、結合する元となるカラムを、ONの後に =（イコール）を用いて指定します。shiftsテーブルとusersテーブルを結合させる場合、下記のようになります。

```
SELECT *
FROM shifts
JOIN users ON shifts.user_id = users.id
```
実行結果：shiftsテーブルのuser_idと、usersテーブルのidが一致するレコードを結合させることができました。

このとき、ONの後にshiftsやusersとテーブル名を再度書くのは冗長です。各テーブルに別名を付け、その名前で扱うのが良いです。各テーブルの頭文字を取ってあげるのが一般的です。下記を実行。

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

- CONCAT関数

CONCAT関数は、複数の文字列を連結できる関数です。

- GROUP BY句

　GROUP BY句は、ある特定のデータごとに、平均値や総量などを集計するときに用いる句です。例えばカラム名にuser_idが入れば、user_idがグループ化されます。すると、user_idごとの平均値や総量を集計できます。

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

- COUNT関数

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

```
SELECT COUNT(カラム名) #　カラムの値が「NULL」だとエラーになるので注意。ただし、SELECT COUNT(*)と記述すると「行の値がすべてNULL」であるレコードも取得します。
```

上記を使って**「特定の日にちに誰が何コマシフトに入ったか」**を検索します。下記を実行。

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

上記で行ったこと：

- SELECTで表示されるものを「名前(CONCATを使用)」「入ったコマ数(COUNTを使用)」の表示しました
- WHEREを用いて特定の日にちを指定しました
- GROUP BYで各ユーザーごとに集計ができるようにしました


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

**「特定の日にち("2021-5-31")にシフトに入っていない人」**を検索する場合の工程を見てみます。下記。

まず、"2021-5-31"にシフトに入っている人の一覧を取得します。

```
SELECT user_id
FROM shifts
WHERE date = "2021-5-31"
```

次に、上記を実行して取得したリストを使って、usersテーブルからidがリストに含まれていないレコードのみを取得するSQL文を記述します。WHERE句で、NOT INを使用して下記のように記述できそうです。

```
SELECT *
FROM users
WHERE id NOT IN (リスト)
```
（リスト）という部分に、先ほど取得した"2021-5-31"にシフトに入っている人のリストを取得するSQL文を埋め込みます。下記を実行。

```
SELECT *
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM shifts
    WHERE date = "2021-5-31"
)
```