この記事について

初学者による、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で削除するレコードを指定しないと
テーブル内の全レコードが削除されてしまう

SQL基礎を焼鳥で