#この記事について
初学者による、SQL基礎学習内容のアウトプット・自身の振り返り用メモです。
楽しくメモするために、データが焼鳥です。
参考資料
#【データベースとは】
データベース
アプリケーションのデータを保存・蓄積するための一つの手段
大量のデータを蓄積し、必要な情報の抜き出し、更新が柔軟に行える
データベースの代表的な種類は下記3つ
・関係型：データを表で管理
・階層型：データを階層で管理
・ネットワーク型：データを網状に管理
関係型のデータベースが現在の主流
DBMS (Data Base Management System)
データベースを利用したいアプリケーションのために
データベース機能を提供するミドルウェア
データベースの仕組み
関係データベースは表でデータを管理
・表：table
・列：Column, Field
・行：Record, Row
必要に応じて複数のテーブルを作成することが可能
#【SQLとは】
SQL (Structured Query Language)
DBMSへ命令（クエリ）を伝える（書く）ためのデータベース言語
大文字と小文字を区別しない
-- クエリ
データベースからデータ取得するために送る命令
命令は下記言語によって構成される
-- データ定義言語 / DDL (Data Definition Language）
スキーマの定義や表の作成といった定義を担当
CREATE / DROP / ALTER / RENAME / TRUNCATE
-- データ操作言語 / DML (Data Manipulation Language)
データの抽出や挿入、更新や削除といった操作を担当
SELECT / INSERT / UPDATE / DELETE / MERGE
-- データ制御言語 / DCL (Data Control Language)
データベースへのアクセス権限付与／取消を担当
GRANTE / REVOKE
-- トランザクション制御言語 / TCL (Transaction Control Language)
COMMIT / ROLLBACK / SAVEPOINT / SET TRANSACTION
※この記事に記載が無いものも列挙しています。後々追記するかもしれません。
#【SQL基本】
取得
SELECT カラムA, カラムB
FROM テーブル名;
- SELECT
データベースから「どのカラムのデータを取得するか」を選ぶ
SELECT name, price
-- 複数のカラムから取得する場合はカンマで区切る
SELECT *
-- 全カラムのデータを取得する場合は＊を使用
- FROM
SELECTで選んだカラムがあるテーブルを指定する
SELECT name -- 2.カラム:nameのデータを取得
FROM yakitories -- 1.yakitoriテーブルの
挿入
INSERT INTO テーブル名(カラムA, カラムB...)
VALUES(値1, 値2...);
更新
UPDATE テーブル名
SET カラム名 = 値1, カラム名 = 値2
WHERE 条件; -- 必ず指定
削除
DELETE FROM テーブル名
WHERE 条件; -- 必ず指定
#【条件】
WHERE
特定のデータを取得するため「どのレコードを取得するか」を指定する
SELECT *
FROM yakitories
WHERE part = "皮";
LIKE演算子
%をワイルドカードとして扱い、ある文字を含むデータを取得する
前方一致 %gima
後方一致 negi%
SELECT *
FROM yakitories
WHERE name LIKE %gi%;
NOT演算子
「〇〇を含まないデータ」や「〇〇に一致しないデータ」のような条件で
データを取得したい場合は「否定」を意味する「NOT演算子」を使用
SELECT *
FROM yakitories
WEHRE NOT flavor LIKE "%sauce";
NULL
中身が何かわからないことを示す
IS NUMLL / IS NOT NULLでデータを取得 (「 = 」は使えない)
-- IS NULL (指定したカラムのデータがNULLであるものを取得)
SELECT * FROM yakitories
WHERE price IS NULL;
-- IS NOT NULL (指定したカラムのデータがNULLでないものを取得)
SELECT * FROM yakitories
WHERE price IS NULL;
AND演算子
「WHERE 条件１ AND 条件２」のようにすることで
条件１と条件２を「共に満たす」データを検索できる
SELECT * FROM yakitories
WHERE manu = "gizzard" -- 条件1
AND category = "chicken"; -- 条件2
OR演算子
「WHERE 条件１ OR 条件２」のようにすることで
条件１または条件２の「どちらかを満たす」データを検索できる
SELECT * FROM yakitories
WHERE category = "chicken" -- 条件1
OR category = "pork"; -- 条件2
ORDER BY
「（基準となる）並べ替えたいカラム名」と「並べ方」を指定して
クエリの末尾に記述することで、取得結果のデータを並び替える
-- ASC / 昇順
-- DESC / 降順
-- ORDER BY カラム名 並べ方;
SELECT *
FROM yakitories
ORDER BY price DESC;
-- WHEREと併用可能
SELECT *
FROM yakitories
WHERE category = "chicken"
ORDER BY price DESC;
LIMIT
最大で取得するデータ件数を指定する
クエリの末尾に記述することで、取得するデータの数を制限する
-- LIMIT データの件数;
SELECT *
FROM yakitories
LIMIT 5;
-- LIMITもORDER BYと同様にWHEREと併用できる
SELECT *
FROM yakitories
WHERE category = "beef"
LIMIT 5;
-- ORDER BYとLIMITの組み合わせ
2つを組み合わせて、注文数が多い上位5件を取得する、等が可能
SELECT *
FROM yakitories
ORDER BY number_of_orders DESC
LIMIT 5; -- LIMITを末尾にする
#【検索結果の加工】
DISTINCT
SELECTで取得するカラムに使用することで、重複を省いたデータを取得できる
-- DISTINCT(カラム名)
SELECT DISTINCT(price)
FROM yakitories;
四則演算・関数
-
- 足す
-
- 引く
-
- 掛ける
- / 割る
- round 四捨五入、四捨五入する桁の位置の指定も可能
- round(5.355) --5
- round(5.355, 1) --5.4
- floor 少数点以下切り捨て
- ceil 小数点以下切り上げ
- rand() 0以上1未満のランダムな数値を返してくれる
＜rand補足：抽選で使える＞
select * from yakitories order by rand() limit 1;
乱数で並び替えそのうち1つだけ取り出す
ランダムな値をもとに並び替え、一番上のレコードだけを抽出
抽選1名を選ぶなどの処理に使える
#【集計関数】
SUM関数
SELECTで取得するカラムに用いることで、合計を取得できる
-- SUM(カラム名)
SELECT SUM(price)
FROM yakitories;
-- WHEREとSUM関数
SUM関数はWHEREと併用できる
SELECT SUM(price) -- 2.検索したレコードの数値の合計を計算
FROM yakitories
WHERE menu = "chicken_wings"; -- 1.とり手羽のレコードを検索
AVG関数
SELECTで取得するカラムに使用することで、計算平均を取得できる
-- AVG(カラム名)
SELECT AVG(price)
FROM yakitories;
--WHEREとAVG関数
AVG関数はWHEREと併用することができる
SELECT AVG(price) -- 2.検索したレコードの数値の平均を計算
FROM yakitories
WHERE name = "yojij"; -- 1.yojijiのレコードを検索
COUNT関数
SELECTで指定したカラムのデータの合計数を取得できる
-- COUNT(カラム名)
SELECT COUNT(*)
FROM yakitories
-- COUNT関数とnull
COUNT関数でカラム名を指定した場合
nullになっているデータの数は計算されない
nullの数も含めてデータの数を計算したい場合は
COUNT関数で全てのカラム「*」を指定する
-- WHEREとCOUNT関数
COUNT関数はWHEREと併用することができる
SELECT COUNT(*) -- 2.検索したレコードの数を計算
FROM yakitories
WHERE category = "chicken"; -- 1.chickenのレコードを検索
MAX / MIN
SELECTで取得したカラムに使用
MAX(カラム名)：指定したカラムのデータの中から最大のデータを取得
MIN(カラム名)：指定したカラムのデータの中から最小のデータを取得
SELECT MAX(price)
FROM yakitories;
-- WHEREとMAX・MIN関数
MAX,MINはWHEREと併用できる
SELECT MAX(price) -- 2.検索したレコードの中の最大値を取得
FROM yakitories
WHERE category = "chichen"; -- 1.chickenのレコードを検索
#【グループ化】
GROUP BY
「GROUP BY カラム名」とすることで、指定したカラムで
完全に同一のデータを持つレコード同士が同じグループとなる
-- グループ化と集計
集計関数を取得するFROMの後ろに「GROUP BY カラム名」を追加する
集計関数により各グループごとにデータが集計される
SELECT SUM(price), purchased_at -- 2.集計する
FROM yakitories
GROUP BY purchased_at; -- 1.グループ化する
-- GROUP BYの注意点
GROUP BY使用時、SELECTで使えるのは
GROUP BYに指定しているカラム名と、集計関数のみ
-- 複数カラムのGROUP BYの書き方
GROUP BYは複数のカラム名を適用させることができ
その場合は、カラム名同士をコンマ(,)で繋げる
-- WHEREとGROUP BY
GROUP BYはWHEREとも併用することができ、その場合はWHEREの後に書く
WHEREはまず最初に、そのあとにGROUP BYと関数が実行される
SELECT 集計関数
FROM テーブル名
WHERE 条件
GROUP BY カラム名, カラム名; -- WHERE文の後に書く
HAVING
GROUP BYでグループ化したデータを更に絞り込みたい場合にはHAVINGを使用
「GROUP BY カラム名 HAVING 条件」のようにすることで
条件を満たすグループを取得することができる
GROUP BY カラム名
HAVING 条件;
--WHEREとHAVING
グループ化した後のデータを絞り込む際、WHEREではなくHAVINGを使うのは
SQLの各コマンドが以下の順番で実行されていくため
-- 関数の実行順
| 実行順 | 実行内容 | 命令 |
|---|---|---|
| 1 | 検索 | WHERE |
| 2 | グループ化 | GROUP BY |
| 3 | 集計関数 | COUNT / SUM / AVG / MAX / MIN |
| 4 | HAVING | HAVING |
-- WHEREとHAVINGの違い
WHEREはグループ化される前のテーブル全体を検索対象とするのに対し
HAVINGはGROUP BYによってグループ化されたデータを検索対象とする
-- HAVINGの注意点
HAVINGはグループ化された後のテーブルから検索するため
条件文で使うカラムは必ずグループ化されたテーブルのカラムを使う
SELECT SUM(price), purchased_at
FROM yakitories
GROUP BY purchased_at
HAVING SUM(price) > 5000; -- グループ化された後のデータのカラムを使う
#【サブクエリ】
サブクエリ
SQLでは、クエリの中に他のクエリ（サブクエリ）を入れられる
2つ以上のクエリを1つにまとめられる為、複雑なデータを取得する際に使用される
SELECT menu
FROM yakitories
WHERE price > ( -- ()で囲む
SELECT price # -- ぼんじりの価格を取得
FROM yakitories
WHERE menu = "bonjiri"
); -- ()内にセミコロン（;）は不要
-- サブクエリの実行順番
サブクエリが実行された後、外側にあるクエリが実行される
AS
ASを使うことでカラム名などに別名を定義することができる
「カラム名 AS "名前"」でカラム名に定義する名前を指定
SELECT number_of_orders AS "皮の注文数"
FROM yakitories
WHERE manu = "yakitori_skin";
#【複数テーブル】
複数のテーブルを紐づけるために、外部キーと主キーを使う
外部キーで他のテーブルにある主キーを指定することで
テーブル同士を紐付けることができる
- テーブルを紐づけるメリット
カラムを追加するのではなくテーブルに紐づけることでデータ管理がしやすくなる
- yakitoriesテーブル
| id | name | cotegory_id (外部キー) |
|---|---|---|
| 1 | thigh_meat | 1 |
| 2 | pork_rose | 2 |
| 3 | bonjiri | 1 |
| 4 | ox_tongue | 3 |
- categorysテーブル
| id | name (主キー) |
|---|---|
| 1 | chicken |
| 2 | pork |
| 3 | beef |
JOIN
JOINは複数のテーブルを1つに結合したいときに使う
ONで条件を指定して、テーブルAにテーブルBを結合する
結合したテーブルは1つのテーブルとしてデータを取得できる
SELECT *
FROM テーブルA
JOIN テーブルB
ON 結合条件
- 結合条件
「ON テーブル名.カラム名 = テーブル名.カラム名」で指定
SELECT *
FROM テーブルA
JOIN テーブルB
ON テーブルA.カラム名 = テーブル名B.カラム名
SELECT *
FROM yakitories
JOIN categorys
ON yakitories.cotegory_id = cotegorys.id;
- JOINの実行順序
JOINを含んだクエリでは、はじめにJOINが実行される
その次に、結合されたテーブルに対してSELECTが実行される
- 複数テーブルでのカラム指定
複数のテーブルに同じカラム名が存在するときは
「それぞれのテーブル名.カラム名」で指定しなければならない
SELECT yakitories.name, cotegorys.name
FROM yakitories
JOIN categorys
ON yakitories.cotegory_id = cotegorys.id;
- WHERE内でテーブル名を指定
「テーブル名.カラム名」を用いたカラム指定はWHERE内でも使える
SELECT *
FROM yakitories
JOIN categorys
ON yakitories.cotegory_id = cotegorys.id;
WHERE yakitries.name = "ox_tongue";
-- WHERE内でもテーブル名を指定することで取得するカラムを指定できる
LEFT JOIN
LEFT JOINを使うことで、FROMで指定したテーブルのレコードを全て取得する
外部キーがNULLのレコードもNULLのまま実行結果に表示される
SELECT *
FROM yakitories -- LEFT JOINを使うと元テーブルのレコードを全て取得
LEFT JOIN categorys
ON yakitories.category_id = categorys.id
- ●複数のテーブルのJOIN
JOINは1つのクエリで、複数回使用できる
JOINを複数回使用しても、FROMは1度だけ書けば大丈夫なので注意
#【全体の実行順序】
SQLは、取得するテーブルを形成してから検索を行うので
FROM・JOINが先に行われることを覚えておく
| 実行順 | 実行内容 | 命令 |
|---|---|---|
| 1 | テーブルの指定 | FROM |
| 2 | 結合 | ON / JOIN |
| 3 | 取得条件 | WHERE |
| 4 | グループ化 | GROUP BY |
| 5 | 関数 | CONUT / SUM / AVG / MAX / MIN |
| 6 | HAVING | HAVING |
| 7 | 検索 | SELECT / DISTINCT |
| 8 | 順序 | ORDER BY |
| 9 | LIMIT | LIMIT |
- NULLを含んだ場合の実行結果
JOINを使った結合は、FROMで指定したテーブルを基準に実行される
ただし外部キーがNULLのレコードは、実行結果に表示されない
#【データの追加】
INSERT
テーブルにレコードを挿入したいときはINSERTを使う
INSERTとは「挿入する」という意味
INSERT INTO テーブル名(カラム名1, カラム名2,...)
VALUE(値a1, 値a2,...)
INSERT INTO yakitories (id, name, category_id) # 順番通りに挿入
VALUES(4, "tori_wings", 1);
AUTO INCREMENT
多くの場合、idカラムにはAUTO INCREMENTという機能が使われる
AUTO INCREMENTとは「自動で増加する」といった意味で
データが作成されるときに自動で値が割り当てられる
INSERT INTO yakitories (□ name, category_id)
VALUES(□ "tori_wings", 1);
-- □ AUTO INCREMENTのカラムは省略できる
UPDATE
データを更新したいときにはUPDATEを使う
複数変更したいカラムがある場合、コンマ(,)で区切る
UPDATE yakitories
SET name = "pork_fillet", cotegory_id = 2 -- 指定カラムを新しい値に
WHERE id = 1; -- 更新するレコードをWHEREで指定
- UPDATEの注意点
WHEREで更新するレコードを指定しないと
カラム内の全データが更新されてしまうので注意
- UPDATEの実行前に
UPDATE実行後はデータを戻すことができない
実行前に一度SELECTを実行して、操作するデータを確認する習慣をつける
DELETE
レコードを削除するときはDELETEを使う
UPDATEと同様にクエリ実行後はレコードを元に戻すことができないので
SELECTでレコードを確認してから削除を行う
DELETE FROM yakitories
WHERE id = 2; -- 削除するレコードをWHEREで指定する
- DELETEの注意点
WHEREで削除するレコードを指定しないと
テーブル内の全レコードが削除されてしまう