データを検索する
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"
)