SQL基礎 ~備忘録~

はじめに

この記事はプログラミング初学者による備忘録用の記事であり、また、少しでも他の初学者のお役に立てればと思い書いています。

今回は、Laravelを使用しているとEloquentを多用してSQLが疎かになりがちなので、再度基礎から復習したことを、備忘録としてまとめておきたいと思います。

間違いなどがございましたら、ご指摘のほどよろしくお願い致します。

SQLとは

SQL(Structured Query Language)は国際標準化機関であるISOによって標準化されているデータベース言語です。

データの検索だけでなく、データの登録や削除といったデータベース操作のほとんどをSQLで行なうことができます。

SQLは、基本的に下記4つの構成要素に分かれていると思っています。

1.テーブルの結合
2.計算
3.絞り込み
4.並び替え

各段階で、どのようにデータが操作されるかをイメージすることが重要になってきます。

また、SQLでは様々なワードが使用されますが、データを取得する際は基本的にSELECT、 FROM、WHEREはどのクエリにも使用されるワードです。複雑なクエリもこの3つのワードをベースとして構成されています。

SQLの種類

SQL文は、RDBMSに与える命令の種類により、次の3つに分類されます。

・DDL（Data Definition Language）
DDL（データ定義言語）は、データを格納する入れ物であるデータベースやテーブルなどを作成、削除します。
DDLに分類される命令は下記のとおりです。

CREATE：データベースやテーブルなどを作成する
ALTER：データベースやテーブルなどの構成を変更する
DROP：データベースやテーブルなどを削除する

・DML（Data Manipulation Language）
DML（データ操作言語）は、テーブルの行を検索、変更します。
DMLに分類される命令は下記のとおりです。

SELECT：テーブルから行を検索する
INSERT：テーブルに新規行を登録する
UPDATE：テーブルの行を更新する
DELETE：テーブルの行を削除する

・DCL（Data Control Language）
DCL（データ制御言語）は、データベースに対して行なった変更を確定、取り消します。そのほか、RDBMSのユーザがデータベースにあるもの（テーブルなど）を操作する権限の設定も行ないます。
DCLに分類される命令は下記のとおりです。

COMMIT：データベースに対して行なった変更を確定する
ROLLBACK：データベースに対して行なった変更を取り消す
GRANT：ユーザに操作の権限を与える
REVOKE：ユーザから操作の権限を奪う

SQLを理解するためのコツ

存在する表(table)をきちんと把握し、その表(table)からどのようなデータを取得したいか、どのような新しいデータを作りたいか等をイメージすることだと思います。

SQLの記述ルール

・SQL文の最後に「;」をつける
1つのデータベース操作は、1つのSQL文で記述します。文には当然、終わりを示す区切り文字が必要であり、SQLでは文の区切り文字としてセミコロン;を使います。

・大文字・小文字は区別されない
区別されないので、読みやすいように大文字と小文字を下記のように使い分けます。
・キーワード(予約語)は大文字
・そのほか（列名など）は小文字

ただし、テーブルに登録されているデータについては、大文字・小文字が区別されます。

・文字列と日付はシングルクォーテーションで囲み、数字は囲まない
文字列をSQL文の中に記述する場合には、'abc'というようにシングルクォーテーションで文字列を囲み、それが文字列であることを示します。

日付をSQL文の中に記述する場合には、文字列と同じくシングルクォーテーションで囲みます。

一方、数字をSQL文の中に記述する場合には、10というように数字だけを書きます。

・単語は半角スペースか改行で区切る
　SQL文では、単語と単語の間を半角スペースまたは改行で区切ります。

SQLを書くコツ

・予約語は大文字で記述する

予約語とは SQLで予め使うことが決まっているワード
SQLの予約語を大文字にすることで、ワードが予約語なのか、テーブル名やカラム名、ユーザーが決めたワードなのかを瞬時に判断できるようになります。

-- 例
SELECT COUNT(name)
FROM members
WHERE age >= 20;

・SQLの予約語の後は改行する

対象となる予約語(一部)

・SELECT
・FROM
・WHERE
・ORDER BY
・GROUP BY

・改行後のインデントを揃える
構造を見やすくするために、インデントを揃えます。

-- 例
SELECT COUNT(name) AS name_count
FROM members
WHERE age >= 20;

・1つのカラムを1行に収める

1行に詰め過ぎると読み辛くなるので、長過ぎる場合は1カラムごとに行を分けます。

また、AND/ORを使用する場合、読みやすくするためにAND/ORは先頭に置くようにします。

-- 例
SELECT name,AVG(price),DATE(created_at) AS created_date,
FROM flowers
WHERE price >= 1000 
AND name = Marigold;

SELECT文 実例

下記テーブルをベースとして実例をまとめておきます。

members table

id	name	age	feature
1	bob	22	student
2	jin	17	student
3	vodka	39	office worker

データを取得する

・全カラムを取得する [*(アスタリスク)]

SELECT * 
FROM members;

・対象テーブルからカラム名を指定してデータを取得する

SELECT name,feature
FROM members;

検索結果の加工

・カラム名の変更 [AS]

SELECT name AS '名前', feature AS '特徴'
FROM members;

・重複なしで指定したデータを取得する [DISTINCT]

SELECT DISTINCT(name)
FROM members;

・データの並び替え [ORDER BY | ORDER BY DESC]

SELECT * 
FROM members 
ORDER BY age;

SELECT * 
FROM members 
ORDER BY age DESC;

・検索結果を足し合わせる[UNION]

SELECT name , age
FROM members1;
UNION
SELECT name , age
FROM members2;

・初めの検索結果から次の検索結果と重複するデータを取り除く[EXCEPT]

SELECT name , age
FROM members1;
EXCEPT
SELECT name , age
FROM members2;

・2つの検索結果で重複するデータを取得[INTERSECT]

SELECT name , age
FROM members1;
INTERSECT
SELECT name , age
FROM members2;

・条件指定でデータを取得

・条件指定[where]

SELECT name , age
FROM members 
WHERE age > 20;

・複数条件指定

[AND , OR , BETWEEN , IN , NOT IN , LIKE , NULL , IS NOT NULL , LIMIT]

・AND/OR

-- AND
SELECT name ,age 
FROM members 
WHERE age > 20 AND name = bob;

-- OR
SELECT name ,age 
FROM members 
WHERE age > 20 OR name = bob;

・BETWEEN

SELECT name, age 
FROM members 
WHERE age BETWEEN 20 AND 40;

・IN/NOT IN
指定した要素を含むレコード(行)を取得 / 指定した要素を含んでいないレコード(行)を取得

-- IN
SELECT * 
FROM members 
WHERE age IN('bob','jin');

-- NOT IN
SELECT * 
FROM members 
WHERE age NOT IN('bob','jin');

補足
NOT INで指定したリストやサブクエリーの値にNULLが存在すると、常に空の結果が返ってきてしまいます。従って、NOT INで指定するカラムにNULLが入る可能性がある時は、WHERE句でIS NOT NULLを指定する必要があります。

・LIKE
LIKE句を使用すると、対象のレコード(行)に対して文字列検索を行うことができます。

検索対象となる文字列には複数の記述方式があります。

完全一致

WHERE name LIKE '田中'

部分一致
部分一致の場合、ワイルドカードを使用して部分一致検索を行うことができます。

ワイルドカード文字	意味
%	0文字以上の任意文字列
_	1文字の任意文字列

例

id	name
1	田中一郎
2	田中二郎
3	鈴木一郎
4	田中元

WHERE name LIKE '田中%';
-- 田中一郎と田中二郎が検索対象となる

WHERE name LIKE '%一郎';
-- 田中一郎と鈴木一郎が検索対象となる

WHERE name LIKE '田中_';
-- 田中元が検索対象となる

・IS NULL/IS NOT NULL
NULLかどうかをチェックする / NULL以外をチェックする

-- IS NULL
SELECT * 
FROM members 
WHERE age IS NULL;

-- IS NOT NULL
SELECT * 
FROM members 
WHERE age IS NOT NULL;

・LIMIT
SELECT 文でデータを取得する時に、取得するデータの行数の上限を設定することができます。

SELECT * 
FROM members 
LIMIT 2; 

集計関数

集計関数には下記のようなものがあります。

計数
・COUNT : レコード数

集計
・MAX : 最大値
・MIN : 最小値
・SUM : 合計値
・AVG : 平均値

使用例 [members table]

name	age	job	created_at
bob	24	office worker	2021-07-14
jin	16	student	2021-07-14
vodka	55	office worker	2021-07-15
curarpikt	17	hunter	2021-07-15

・日付ごとに会員登録されたユーザーの年齢の平均値と最大値を求める場合

SELECT created_at AVG(age),MAX(age)
FROM members
GROUP BY created_at;

GROUP BY

複数のデータごとに集計したいときに使用します

注意点
軸となるカラム名を全てGROUP BYで指定します

使用例 [members table]

name	age	job	created_at
bob	24	office worker	2021-07-14
jin	16	student	2021-07-14
vodka	55	office worker	2021-07-15

・日付ごとに会員登録数を集計する場合

SELECT created_at,COUNT(name)
FROM members 
GROUP BY created_at;

・日付ごとに会員登録数を職別で集計する場合

SELECT created_at, job COUNT(name)
FROM members
GROUP BY created_at, job;

内部結合と外部結合

複数のテーブル(データ)を結合してデータを取得する場合に使用し、大きく内部結合と外部結合に区分されます。

テーブルの結合とは
文字通り、複数のテーブルを結合した上で対象となるデータを取得することです。
下記のように、ベースとなるusersテーブルと、usersテーブルのプライマリーキーをuser_idとして含むtravelsテーブルをまとめた上で、データを取得します。

users table

id	name
1	bob
2	kaito
3	jin
4	vodka
5	roy

travels table

id	user_id	country
1	2	イギリス
2	5	ポーランド
3	1	イタリア
4	4	カナダ
5	2	アメリカ
6	6	日本

・userの渡航履歴を取得する場合(内部結合)

SELECT *
FROM users AS u
INNER JOIN travels AS t ON u.id = t.user_id;

結果

id	name	id	user_id	country
1	bob	3	1	イタリア
2	kaito	1	2	イギリス
2	kaito	5	2	アメリカ
4	vodka	4	4	カナダ
5	roy	2	5	ポーランド

注意点

・複数テーブルを扱う場合、カラム名の前にテーブル名を記述する必要がある

複数テーブルで同じカラム名がある場合、SQL側がどのテーブルのカラムか判断できなくなりエラーが発生するため、カラム名の前にテーブル名を指定することで区別するようにします。

・内部結合を使う場合、条件に合わないデータは結果から削除される

結果データで条件に合わないレコードが削除されないようにするためには、INNER JOIN(内部結合)ではなくLEFT OUTER JOIN等の外部結合を使用します。
外部結合の結果として、条件と合わないレコードにはデータを消さずにnullが代入される。このように対応することで、ベースとなるテーブルのデータが結果から削除されなくなります。

・userの渡航履歴を取得する場合(LEFT OUTER JOIN外部結合)

LEFT OUTER JOINの場合、左側のテーブルを基準としてデータを操作します。
下記コードの場合、usersテーブルが基準となります。

SELECT *
FROM users AS u
LEFT OUTER JOIN travels AS t ON u.id = t.user_id;

結果

id	name	id	user_id	country
1	bob	3	1	イタリア
2	kaito	1	2	イギリス
2	kaito	5	2	アメリカ
3	jin	null	null	null
4	vodka	4	4	カナダ
5	roy	2	5	ポーランド

注意点
・LEFT OUTER JOINでの外部結合を行う場合、条件に合わないデータをnullとして取得する
内部結合のように、それぞれのテーブルの指定したカラムの値が一致するものを結合するのに加え、左側のテーブルにしか存在しないものに関しても、対となる右側のテーブルのデータをnullとして取得します。
また、左のusersテーブルが基準となっているので、並び順もusersテーブルのidがベースとなっています。

・userの渡航履歴を取得する場合(RIGHT OUTER JOIN外部結合)

RIGHT OUTER JOINの場合、右側のテーブルを基準としてデータを操作します。
下記コードの場合、travelsテーブルが基準となります。

SELECT *
FROM users AS u
RIGHT OUTER JOIN travels AS t ON u.id = t.user_id;

結果

id	name	id	user_id	country
2	kaito	1	2	イギリス
5	roy	2	5	ポーランド
1	bob	3	1	イタリア
4	vodka	4	4	カナダ
2	kaito	5	2	アメリカ
null	null	6	6	日本

注意点
・RIGHT OUTER JOINでの外部結合を行う場合、条件に合わないデータをnullとして取得する
内部結合のように、それぞれのテーブルの指定したカラムの値が一致するものを結合するのに加え、右側のテーブルにしか存在しないものに関しても、対となる左側のテーブルのデータをnullとして取得します。
また、右のtravelsテーブルが基準となっているので、並び順もtravelsテーブルのidがベースとなっています。

まとめ

・内部結合(INNER JOIN)
結合すべき行が見つからなかった場合、その行は消滅します。

・外部結合(LEFT OUTER JOIN , RIGHT OUTER JOIN)
結合すべき行が見つからない場合、その値がNULLとして生成され結合します。
左右いずれかの表を優先させたいときにLEFT OUTER JOINかRIGHT OUTER JOINを指定します。

LEFT JOINを指定とすると、左側のテーブルすべての行が表示されます。

RIGHT JOINとすると、右側のテーブルすべての行が表示されます。

従って、LEFT JOINおよびRIGHT JOINを使用するときは、テーブル名を左右どちらに記述するか注意する必要があります。

CASE式(条件分岐)

条件を指定して集計したいときにCASE式を利用します。
集計用の分類単位で集計したいときに集約関数とGROUP BYを組み合わせることで恩恵を受けられます。
しかし、プログラム側で条件分岐を書いた方がシンプルになる場合もあるので、何でもCASE式で書くべきではないと思います。

注意点
・ELSEも忘れずに書く
・条件は排他的に書く
・真（true）がマッチ対象であり、偽（false）と不明（unknown）はマッチ対象ではない

また、CASE式には単純CASE式と検索CASE式があります。

単純CASE式 等価条件が真であるかを評価し、クエリをシンプルにできます。
検索CASE式 応用が効きやすく検索CASE式で単純CASE式の内容を表すこともできます。

単純CASE式と検索CASE式

-- 単純CASE式
SELECT 
 CASE
  WHEN 条件 THEN 値
  WHEN 条件 THEN 値
  ELSE 値　
 END
FROM テーブル名

-- 検索CASE式
SELECT 
 CASE
  WHEN カラム名 = 条件 THEN 値
  WHEN カラム名 = 条件 THEN 値
  ELSE 値　
 END
FROM テーブル名

補足
検索CASE式は、「=」以外にも、不等号やIN句でも表現できます。

CASE
    WHEN vegetable IN ('トマト') THEN '果菜'
    WHEN vegetable IN ('ゴボウ', 'ニンジン', 'カブ', 'サツマイモ', 'レンコン') THEN '根菜'
    WHEN vegetable IN ('ネギ') THEN '葉茎菜'
    ELSE NULL
END

・商品のジャンルごとに合計値を算出する場合

items table

name	price
バット	25,000
タオル	2,000
スパイク	9,500
ゲーム機	45,000
化粧水	4,000

実行例

SELECT 
 CASE 
  WHEN name IN('バット', 'スパイク') THEN 'スポーツ用品'
  WHEN name IN ('タオル', '化粧水') THEN '生活用品'
  WHEN name IN ('ゲーム機') THEN '娯楽用品'
  ELSE NULL
 END AS classification_name,
SUM(price)
FROM items
GROUP BY
 CASE WHEN name IN('バット', 'スパイク') THEN 'スポーツ用品'
 WHEN name IN ('タオル', '化粧水') THEN '生活用品'
 WHEN name IN ('ゲーム機') THEN '娯楽用品'
 ELSE NULL
END;

結果

classification_name	price
スポーツ用品	34,500
生活用品	6,000
娯楽用品	45,000

参考文献

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ
CASE式のススメ（前編）
CASE式のススメ（後編）
ユーザーの年齢・性別と購買履歴を活用する「属性」×「行動」分析のSQL
SQLで基本のデータ解析に挑戦する
初心者向けの解説書でSQLとリレーショナルデータベースの基本を押さえよう