SQL入門 – 総まとめ【SELECT, INSERT, UPDATE, DELETE】
SQL。
データベースを操作するための言語ですが、どんなアプリケーションを作っていても必要な言語になり、学習して絶対に損は無いです。
Webアプリケーションでも、PHPのフレームワークやRubyのフレームワークを使うと、SQLを直接触る機会は減りますが、
蓄積したデータの分析を行ったり、フレームワークのメソッドだけでは痒い所に手が届かなかったりします。
今回は、そのSQLの入門として、基礎をまとめていきます。
大事な所は⭐️を付けました。
基本
- SQLは大文字と小文字を区別しない
- セミコロンで文を終える
コメントアウト
- 単一行:
-- コメント
- 複数行:
/* コメント */
⭐️ データベースからデータを取得する
SELECT
でどのカラム(縦)からデータを取得するか選択します。
1 | SELECT カラム名 |
複数のカラムを取得
カラム名をカンマで区切る
1 | SELECT カラム名, カラム名 |
全てのカラムを取得するには*
を使います
1 | SELECT * |
⭐️ テーブルを選択
FROM
を用いてテーブル名を選択します。
SELECTにおいて必須の文です。
1 2 | SELECT カラム名 FROM テーブル名 |
⭐️ 特定のデータを取得する
WHERE
でどのレコード(横)を取得するか選択できます。
条件:category = 〇〇、created_at >= “2020-04-08” (categoryが〇〇、作成日が2020-04-08以前 など)
1 | WHERE 条件 |
ある文字を含むデータを取得する
WHEREの直後に、カラム名 LIKE
(〜のようなの意味)で文字列を指定します。
1 | WHERE カラム名 LIKE 文字列 |
ワイルドカード
LIKE演算子を用いる際に覚えておく必要があるのが「ワイルドカード」です。
LIKE演算子では%
をワイルドカードとして扱います。
焼肉を含む文字列(焼肉、焼肉定食、カルビ焼肉、大盛り焼肉定食 にマッチする)
1 | "%焼肉%" |
前方一致(焼肉、焼肉定食 にマッチする)
1 | "焼肉%" |
後方一致(焼肉、カルビ焼肉 にマッチする)
1 | "%焼肉" |
ある文字が含まない、一致しないデータを取得する
WHEREの直後にNOT
演算子をおくことで、その条件を満たさないデータを取得できます。
(焼肉が含まれないレコード)
1 | WHERE NOT カラム名 LIKE "%焼肉%" |
値がnullのカラムを取得する nullでは無いカラムを取得する
nullのデータは、IS NULL
で取得できます。
1 | WHERE カラム名 IS NULL |
nullでは無いデータは、IS NOT NULL
で取得できます。
1 | WHERE カラム名 IS NOT NULL |
⭐️ 複数の条件を指定する
条件① かつ 条件② → AND
演算子
1 | WHERE 条件① AND 条件② |
条件① または 条件② → OR
演算子
1 | WHERE 条件① OR 条件② |
⭐️ 取得したデータを並び替える
クエリの末尾にORDER BY
で並び替えができます。
1 | ORDER BY カラム名 並べ方 |
- 昇順:ASC (1, 2, 3)
- 降順:DESC (3, 2, 1)
⭐️ 取得する最大件数を指定する
クエリの末尾にLIMIT
で最大で何件取得するかを指定します。
※ ORDER BYとLIMITの組み合わせはLIMITを末尾にする必要があります。
1 2 | ORDER BY カラム名 並べ方 LIMIT 件数 |
⭐️ 重複データを取り除く
DISTINCT
は、SELECTで取得するカラムに使用することで、重複を省いたデータを取得できます。
1 | SELECT DISTINCT カラム名 |
データに計算を加える
SELECT文のカラム名に直接式を加えます。
1 | SELECT カラム名 * 1.08 |
合計値を求める
1 | SELECT SUM(カラム名) |
平均値を求める
1 | SELECT AVG(カラム名) |
⭐️ カラムの数をカウントする
COUNT
関数ではnullのカラムはカウントされません。
1 | SELECT COUNT(カラム名) |
nullの数も含めてデータの数を計算したい場合は、COUNT
関数で * (全てのカラム)を指定します。
1 | SELECT COUNT(*) |
データの中で最大・最小値を求める
1 | SELECT MAX(カラム名) |
1 | SELECT MIN(カラム名) |
⭐️ データをグループ化する
FROMの後ろにGROUP BY
を用いると、指定したカラムで完全に同一のデータを持つレコードどうしが同じグループとなります。
※ 集計関数をグループごとに取りたい場合に使用します。
※ WHEREで検索した後に、GROUP BYでグループ化を行います。
カラム②でグループ化し、カラム①の合計を求める
1 2 3 4 | SELECT SUM(カラム名①), カラム名② FROM テーブル名 WHERE 条件 GROUP BY カラム名② |
複数カラムでグループ化する
GROUP BY
でカンマでカラム名をつなげると、カラム② かつ カラム③でグループ化できます。
1 2 3 | SELECT SUM(カラム名①), カラム名②, カラム名③ FROM テーブル名 GROUP BY カラム名②, カラム名③ |
⭐️ グループ化したデータをさらに絞り込む
GROUP BYの後に、HAVING
を用いると、さらに絞り込むことができます。
※ 条件文で使うカラムは必ずグループ化されたテーブルのカラムを使います
1 2 | GROUP BY カラム名 HAVING 条件 |
クエリの実行順
WHERE → GROUP BY → COUNT, SUMなどの関数 → HAVING
⭐️ WHEREとHAVINGの違い
実行順序によって、WHEREとHAVINGは検索対象に違いがあります。
WHEREはグループ化される前のテーブル全体を検索対象とするのに対し、
HAVINGはGROUP BYによってグループ化されたデータを検索対象とします。
⭐️ 一度に複数のクエリで複雑なデータを取得する(サブクエリ)
()
で囲むことによってサブクエリを使用できます。
()内のクエリはセミコロンは不要です。クエリの最後だけに書きます。
シャツの値段より高い商品の名前を取得
1 2 3 4 5 6 7 | SELECT name FROM items WHERE price > ( SELECT price FROM items WHERE name = "シャツ" ); |
サブクエリの実行順番
サブクエリを含むクエリの場合、サブクエリが実行された後、外側にあるクエリが実行されます。
取得したカラム名を変更する
AS
を使うことでカラム名などに別名を定義することができます。
1 | SELECT price AS "値段" |
⭐️ 他のテーブルと紐付ける
JOIN
は複数のテーブルを1つに結合したいときに使います。ON
で条件を指定して、テーブルAにテーブルBを結合します。
結合条件:ON テーブル名A.カラム名 = テーブル名B.カラム名
など
1 2 3 4 5 | SELECT * FROM テーブル名A JOIN テーブル名B ON 結合条件 GROUP BY テーブル名A.カラム名 |
⭐️ クエリの実行順序
FROM → ON, JOIN → WHERE → GROUP BY → COUNT, SUMなど → HAVING → SELECT, DISTINCT → ORDER BY → LIMIT
テーブルが紐づく仕組み
テーブルを紐づけるために、外部キーと主キーを使います。
外部キーで他のテーブルにある主キーを指定することで、テーブル同士を紐付けることができます。
テーブルを紐づけるメリット
複数テーブルを使わない場合は複数修正が発生するが、分けると修正が1つで済む。
結合時の複数テーブルでのカラムの指定方法
SELECTやWHEREなどで使います。
SELECT テーブル名A.カラム名, テーブル名B.カラム名
外部キーがNULLのレコードも取得する
LEFT JOIN
を使うことで、FROMで指定したテーブルのレコードを全て取得します。
外部キーがNULLのレコードもNULLのまま実行結果に表示されます。
⭐️ 3つ以上のテーブルの結合
JOIN
は1つのクエリで、複数回使用できます。
1 2 3 4 5 6 | SELECT * FROM テーブル名A JOIN テーブル名B ON テーブル名A.カラム名1 = テーブル名B.カラム名 LEFT JOIN テーブル名C ON テーブル名A.カラム名2 = テーブル名C.カラム名 |
⭐️ データを追加する
テーブルにレコードを挿入したいときはINSERT INTO
を使います。
INSERT INTO の後にテーブル名とカラム名を指定して、VALUES
で対応した順番通りに値を指定します。
1 2 | INSERT INTO テーブル名(カラム名1, カラム名2) VALUES(値1, 値2) |
オートインクリメント
多くの場合、idカラムにはAUTO INCREMENT(自動増加)が使われ、データ作成時に自動で割り当てられます。
INSERT文にAUTO INCREMENTカラムは省略できます。
⭐️ データを更新する
データを更新したいときにはUPDATE
を使います。
更新するレコードをWHEREで選択します。SET
で値を更新しますが、複数変更したい時はカンマ区切りで複数指定できます。
1 2 3 | UPDATE テーブル名 SET カラム名=値1, カラム名=値2 WHERE 条件 |
⭐️ データを削除する
レコードを削除するときは DELETE
を使います。
削除したいレコードをWHEREで選択します。
1 2 | DELEATE FROM テーブル名 WHERE 条件 |
⭐️ UPDATE、DELETEの注意点
WHEREで更新するレコードを指定しないと、条件が無いので、カラム内の全データが更新されてしまいます。
実行後はデータを元に戻すことができません。実行前にSELECTで操作するデータを確認すると良いでしょう。