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で操作するデータを確認すると良いでしょう。
