SQLスキルチェック - 初学者編（入門書を学習してゆく段階）

背景

私は1年前に少しSQLを使った業務に携わっていたのですが、そこからブランクがあり、再度こちらの業界に携わることになりました。そこで入社前にSQLの基本を確認したいと思い、データベースとSQLの業務スキルレベル 判別表の問題にチャレンジすることにしました。

ここでは自分なりの解答を記載しますが、何か疑問となる箇所や謝っている箇所があればコメントでご教授いただけますと幸いです。

チェック項目

CRUDとは

CRUD図とは、機能とデータに用いられるプロセス（作成：Create, 参照：Read, 更新：Create, 削除：Delete）をまとめた表のことです。（参考：CRUD図の読み方と書き方）

	顧客マスタ	商品マスタ	注文テーブル
顧客登録	C
顧客検索	R
顧客削除	RD
注文登録	R	R	C
注文照会	R	R	R
注文訂正	R	R	RU
注文取消	R	R	RD

論理名と物理名の違い

論理名は「人間向けの名前」で、物理名は「コンピュータ向けの名前」です。一般に、論理削除という場合はレコードをデータベース(DB)に残したまま削除したことにしておくという概念的なもので、物理削除は完全にDBから削除することです。例えば、ユーザーが一時的に退会した場合は論理削除でいつでも復元できる状態にし、1年以上ログインがなければ物理削除するという使い分けができます。（参考：SQLで理解するDBの物理削除と論理削除）

論理名	物理名
顧客マスタ	mst_customer
顧客名	name
年齢	age
性別	sex

プライマリキー (PK) とは

プライマリーキー (Primary Key) とは、あるテーブルの中で一意のデータを特定できるカラムのことです。例えば、社員テーブルにおいて山田太郎さんが同姓同名で2人いた場合、社員名で検索をかけても所望のレコードを取得することができません。そこでPKとして、社員番号などを付与しておくと名前の重複に関わらず一意のレコードを取得することができます。（参考：PRIMARY KEY制約）

社員番号	名前	部署	性別
1	山田太郎	営業部	男性
2	鈴木花子	経理部	女性
3	近藤次郎	開発部	男性
4	工藤桃子	営業部	女性
5	山田太郎	開発部	男性

DDL, DML, DCLの違い

DDL (Data Defenition Language) とは、データを定義するSQLの命令のことです。

DDL	説明
CREATE	新規テーブルやビューなどのDBオブジェクトを作成
DROP	既存のDBオブジェクトを削除
ALTER	既存のテーブルベースオブジェクトを削除
TRUNCATE	テーブル内のデータを全削除

DML (Data Manipulation Language) とは、データを操作するSQLの命令のことです。

DML	説明
SELECT	テーブルからレコードを抽出
INSERT	テーブルにレコードを新規登録
UPDATE	テーブルのレコードを更新
DELETE	テーブルのレコードを削除

DCL (Data Control Language) とは、データを制御するSQLの命令のことです。

DCL	説明
GRANT	テーブルに対する権限を付与
REVOKE	テーブルに対する権限を取消

（参考：DDLとDMLの違い）

WHERE句内でのNULLの指定

WHERE句でNULL値のレコードを取得する/しない場合、WHERE column_name IS [NOT] NULLを用います。(WHERE column_name = NULLとしてはいけないことに注意)

NULL値のレコード

SELECT *
FROM table_name
WHERE column1 IS NULL AND column2 IS NOT NULL

ORDER BY, LIMIT, OFFSETを使った絞り込み

まず前提として、ORDER BY column_name [ASC | DESC] [NULL FARST | NULL LAST]はカラムを指定してソートする演算子、LIMIT [開始位置,] 取得する行数 (MySQL) または LIMIT {取得する行数 | ALL} [OFFSET 開始位置] (PostgreSQL) は行数を絞る演算子です。（参考：SQLリファレンス）

それでは、以下で表題の件で注意すべき点などを紹介します。（参考：ORDER BYとLIMIT, OFFSETの組み合わせには注意しよう）

# 今回使用するテーブルを用意
CREATE TABLE test(id INT, col INT);
INSERT INTO test values(1,1);
INSERT INTO test values(2,1);
INSERT INTO test values(10,1);
INSERT INTO test values(3,1);

#1
SELECT + FROM test ORDER BY col LIMIT 2 OFFSET 0;
#2
SELECT + FROM test ORDER BY col LIMIT 2 OFFSET 2;

こちらが#1, #2のクエリ結果です。データを追加した順番ではなく、ランダムに取得されていることが分かります。このようにORDER BYを指定しなかった場合、結果の順序はランダム（ORDER BY RAND()）となってしまうのがMySQLの仕様です。（参考：MySQL）

#1	id	col		#2	id	col
	3	1			10	1
	2	1			3	1

SQLのデータ型

（参考：Snowflake Documentation, SQL Server 2008のデータ型とサイズについて, 浮動小数点数型と誤差, SQLのデータ型, 文字列型の使い分け）

数値型

種別	データ型	説明	byte
整数	tinyint	0~255の範囲の数値	1
整数	smallint	およそ-3万~+3万の範囲の数値	2
整数	int, integer	およそ-20億~+20億の範囲の数値	4
整数	bigint	およそ-900京~+900京の範囲の数値	8
小数(概数)	real, float(4)	以下の(*1)参照	4
小数(概数)	float(8), double [precision]	realに比べ処理が速く正確(*2)	8
小数(真数)	number, decimal, numeric	(有効桁数, 小数点以下桁数)を指定できる	5~17

(※1) 概数は、仮数(小数以下の数値)と指数部分に分ける表現で、仮数の桁数を越えてしまうと数が丸められ誤差が生じます。例えば、32bitで概数を表す場合「a: 符号部(1bit)」「b: 指数部(8bit)」「c: 仮数部(23bit)」で「$(-1)^{a}×2^{b-127}×1.c$」と表現されます。

(*2) 現在のコンピュータではdouble型に合わせて回路が組まれていることが多いため、float型よりもdouble型の方が計算速度が速い（かつ精度が良い）。

文字列型

種別	データ型	説明	byte
固定長文字列	char, character	以下の(*1)参照	1
固定長文字列	char(n), character(n)	n: 文字数(*2)	2n
可変長文字列	varchar(n)	8000byteまで(*3)	byte*2 +2
可変長文字列	text, string	2GBまで	byte*2 +2

(*1) charはchar(1)を意味します。固定長文字列の場合、n文字ピッタリでない限り末尾の空白の分だけサイズが大きくなります。そのため処理速度の観点からもcharではなく、varcharやtextが使われることが多いです。

(*2) char(n)のnは、DBの種類によっては文字列ではなくbyteを表すものもあります。なお、数字や半角記号などが1byte, ひらがなや漢字などの全角文字が2byteです。

(*3) 目安として、だいたい200文字以内ならvarchar型、それ以外の長い文字列ならtextを利用すると良いでしょう。

論理型

種別	データ型	説明	byte
論理	boolean	true, false	1

日付と時刻型

種別	データ型	説明	byte
日付	date	YYYY-MM-DD	3
時間	time	00:00:00.0000000～23:59:59.9999999	3~5
日付時間	datetime	YYYY-MM-DD hh:mm:dd[.mmm] (*1)	8
日付時間	datetime2	YYYY-MM-DD hh:mm:dd[.nnnnnnn]	6~8

(*1) datetimeの精度は3.33ミリ秒、datetime2の精度は100ナノ秒です。

3層アーキテクチャーとMVC

一般に、Webアプリケーションは「Webサーバー」「アプリケーションサーバー」「DBサーバー」の3種類からなり、中でもアプリケーションサーバーは「プレゼンテーション層・ビジネスロジック層・データアクセス層（DAO層）」に分けることができます。（参考：MVC、3 層アーキテクチャから設計を学び始めるための基礎知識, DBunitのインストール）

例えば、ユーザーが入力したキーワードに応じて画像を表示させる場合、それぞれの層は以下のような役割を果たします。

• プレゼンテーション層：入力されたキーワードをビジネスロジック層に渡したり、出力結果を表示したりします。ユーザーとやりとりを行う層です。
• ビジネスロジック層：プレゼンテーション層から渡された情報を分析し、必要な画像をDAO層に要求します。プログラムの論理が記述された層です。
• DAO層：ビジネスロジック層から渡された情報でDBを検索し、DB内の画像を取得します。DBとやりとりを行う層です。

MVCとは「Model」「View」「Controller」の頭文字をとったもので、プレゼンテーション層とビジネスロジック層での構成のことを指します。（DAO層には一切コードを書かないケースも存在します）

UPDATEとDELETE-INSERTの違い

UPDATEとDELETE-INSERTでは、（条件次第で）どちらも同じ結果を得ることができます。（参考：DELETE-INSERTとUPDATEの違いや注意点）

UPDATEでレコードを更新

UPDATE tab1 SET col2 = 'A' WHERE col1 = '00001';

DELETEでレコードを削除しINSERTで新たに挿入

DELETE FROM tab1 WHERE col1 = '00001';
INSERT INTO tab1 VALUES('00001', 'A');

UPDATEを使う場合

DELETEする必要がない限り、基本はUPDATEを使うことが多いです。なぜなら、DELETEではデータを削除するというリスクが大きいほか、DELETE-INSERTでは2回処理を実行しなければならず処理に時間がかかってしまう可能性もあるからです。

DELETE-INSERTを使う場合

レコードを一度削除した方が都合が良いというときにDELETE-INSERTが用いられます。例えば、日次バッチで前日のレコードを削除して、今日分のレコードのみテーブルに登録したいという際にはこれが有効です。

マスタ(テーブル)とは

マスタとは、システムの運用上変更が少ない基本的な情報が格納されたテーブルのことです。（参考：DBで使用する一般的な用語）

例えば、ECサイトの場合「商品テーブル（商品ID, 商品名, ブランド名など）」がマスタテーブルに該当し、「注文テーブル（顧客ID, 製品名, 購入金額, 決済方法など）」がトランザクションテーブルとなります。

正規化とは

正規化とは、データの重複をなくし扱いやすくするためにテーブル構造を整えることです。大きな1つのテーブルを重複なしで複数の小さなテーブルに分割するイメージになります。（参考：DBで使用する一般的な用語）

正規化の手順

1. 表内の1つのセルに1つの値のみが入っている状態にする。（i.e., セルの結合などを解除する）
2. 後から集計できる情報などを削ぎ落とす。（i.e., 1人あたり売上など）
3. ある変数に従属する変数郡などを独立した小テーブルとして分割する。（i.e., 会社IDと会社名, 社員IDと社員名と電話番号など）

正規化することで、データを一元管理し効率的に変更などを加えることができるため、基本的には正規化した方が良いと言われています。ただ、テーブル数が多くなることで検索SQLのパフォーマンスが悪くなることも懸念されるので、場合によってはあえて正規化を進めないということも考えられます。（参考：データベースの正規化(第1~第3正規形)）

JOINの種類

種類	説明
INNER JOIN	キーが一致するレコードのみ取得するJOIN（それ以外は切り捨て）
LEFT JOIN	左側テーブルを軸に右側をJOIN（キーが不一致のレコードはNULL）
RIGHT JOIN	右側テーブルを軸に左側をJOIN（キーが不一致のレコードはNULL）
FULL JOIN	全てのレコードをJOIN（キーが不一致のレコードはNULL）
CROSS JOIN	左側のテーブルを軸に右側の全組み合わせをJOIN

（参考：【新人教育 資料】第9章 SQLへの道〜結合編〜）

INNER JOIN（内部結合）

今回は、以下の2つのテーブルでJOINについて復習していきます。

users

id	name	sex
1	Tom	man
2	Anny	woman
3	John	man
4	Monica	woman

personal_info

user_id	birthday	age
1	20001010	22
2	19990202	23
5	19550505	66

INNER JOIN

SELECT a.id, a.name, b.age
FROM users a INNER JOIN personal_info b ON a.id = b.user_id

今回は各テーブルの名前を省略するために、users, personal_infoのテーブルをそれぞれa, bという別名で登録しました。上記のINNER JOINの結果を以下で示します。

id	name	age
1	Tom	22
2	Anny	23

LEFT JOIN（左外部結合）

LEFT JOIN

SELECT a.id, a.name, b.age
FROM users a LEFT JOIN personal_info b ON a.id = b.user_id

id	name	age
1	Tom	22
2	Anny	23
3	John	null
4	Monica	null

RIGHT JOIN（右外部結合）

RIGHT JOIN

SELECT a.id, a.name, b.age
FROM users a RIGHT JOIN personal_info b ON a.id = b.user_id

id	name	age
1	Tom	22
2	Anny	23
null	null	66

FULL JOIN（完全外部結合）

FULL JOIN

SELECT a.id, a.name, b.age
FROM users a FULL JOIN personal_info b ON a.id = b.user_id

id	name	age
1	Tom	22
2	Anny	23
3	John	null
4	Monica	null
null	null	66

CROSS JOIN（クロス結合）

CROSS JOIN

SELECT a.id, a.name, b.age
FROM users a CROSS JOIN personal_info b

id	name	age
1	Tom	22
1	Tom	23
1	Tom	66
2	Anny	22
2	Anny	23
2	Anny	66
3	John	22
3	John	23
3	John	66
4	Monica	null
4	Monica	null
4	Monica	null

トランザクションとは

トランザクションとは、1つ以上のSQL文（e.g., SELECT, INSET, UPDATE）を一塊の処理単位としたもので、一連の処理の流れを中断せずに制御することを目的としています。トランザクションが成功した場合には全ての処理を反映させ、失敗した場合には全ての変更を取り消すことができます、（参考：【SQL基礎】トランザクションとは）

トランザクションは「START TRANSACTION」または「BEGIN」が開始文となり、その次に一連の処理を記述し、最後に「COMMIT」または「ROLLBACK」で締めます。COMMITはトランザクション処理の確定、ROLLBACKは処理のキャンセルを意味します。

トランザクション

START TRANSACTION; # BEGINでも可
UPDATE users SET name = 'Tommy' WHERE id = 1;
INSERT INTO users VALUES (5, 'Bob', 'man');
COMMIT; # ROLLBACKでも可

WHERE句で使える演算子

SQLの演算子のほとんどはWHERE句で利用することができます。（参考：SELECT構文：WHEREで検索条件を設定する）

| >, < | 大きい, 小さい |
| >=, <= | 以上, 以下 |
| !=, <> | 等しくない |
| AND | 2つの条件のうち両方が真 |
| OR | 2つの条件のうちどちらか一方が真 |
| NOT | 式の結果を反転 |
| BETWEEN a AND b | 対象フィールドがa~bの範囲 |
| IN | 対象フィールドが複数の値のいずれかに一致 |
| LIKE | 対象フィールドが指定したパターンに一致 |
| IS [NOT] NULL | 対象フィールドがNULL(またはNULLでない) |

※ LIKEでワイルドカードを指定する際、アンダースコア(_)は任意の1文字、パーセント(%)は0個以上の連続した文字を意味します。

WHERE句で使える演算子

SELECT * FROM users WHERE (id >= 2 AND sex = 'woman') OR sex = 'man'

SELECT * FROM users WHERE NOT (id >= 2 AND sex = 'woman')

SELECT * FROM users WHERE id BETWEEN 2 AND 5

SELECT * FROM users WHERE name IN ('Tom', 'John', 'Monica')

SELECT * FROM users WHERE name LIKE 'To_' OR name LIKE 'Mo%'

WHERE句とHAVING句の違い

WHEREとHAVINGはどちらも条件を絞る演算子ですが、実行される順序が異なります。以下でSQLの実行順序を示します。（参考：SELECT - PostgreSQL8.1.9文書）

データの絞り込み（WHERE vs. HAVING）

# 集計前のデータを絞り込み
SELECT datetime, product_id, product_name, price
FROM sales
WHERE DATETRUNC('day', datetime) > '2022-04-01'

# 集計後のデータを絞り込み
SELECT product_id, product_name, SUM(price) as total_price
FROM sales
GROUP BY product_id
HAVING total_price >= 3000

DBにおけるインデックスとは

インデックスとは「探すレコードを識別する項目」「対象レコードの格納位置を示すポインタ」で構成された構造のことです。インデックスを設定することで、テーブルの上から順に1件ずつ探すのではなく、データの格納位置に直接アクセスすることで検索速度を上げることができます。（参考：データベース性能を向上させる「インデックス」を理解する）

SQLで利用される文字コード

文字コードとは「（人間用）文字」と「（コンピュータ用）文字に割り当てられた番号」の対応表のことです。（参考：Unicode (ユニコード)）

コンピューターは「0」「1」しか理解できないので、私たちが普段使っている文字を読み込ませる場合には全て数字に変換しなければいけません。そこで例えば、「あ：00000001、い：00000010、う：00000011、え：00000100、お：00000101」のような対応表を作ることで、人間とコンピューターがやりとりできるようにしています。

そんな文字コードですが、SQLでは以下の5つがよく利用されます。（参考：使用可能（有効）な文字コードの一覧を調べる, 文字コードについて（UTF-8/SHift_JIS/EUC-JP））

文字コード	説明
utf8	ユニコード（UTF-8）
sjis	Shift_JIS（文字化けが起こるため非推奨）
cp932	特殊文字に対応したShift_JIS
ujis	EUC-JP（文字化けが起こるため非推奨）
eucjpms	特殊文字に対応したEUC-JP

• ユニコード：世界中の文字に対応した文字コード。
• Shift_JIS：Microsoft社が開発した日本語用の文字コード。使い方によっては文字化けします。
• EUC-JP：UNIX系のOSで使われる日本語用の文字コード。テキストエディタには対応していない場合があります。

ODBCとは

ODBC (Open Database Connectivity) とは、Microsoft社が開発した「アプリケーションからDBにアクセスする仕組み」のことで、ソースコードで利用できるものや関数呼び出し時の挙動などが規定されています。（参考：今さら学ぶODBCの基礎）

ODBCのAPIを使うことで（言語を問わず）同じプログラムから、様々なDBに対して接続することができるようになります。(*1)

ODBCは以下の4つのコンポーネントで構成されており、ODBC APIはドライバーマネージャーへのリクエストやレスポンスの際に役目を果たします。

コンポーネント	説明
アプリケーション	処理を実行しODBC関数を呼び出してSQLの命令を送信し、結果を取得します。
ドライバーマネージャー	アプリケーションとドライバー間の通信を管理します。 アプリからの接続情報に基づいてドライバーの選択/読み込みなどを行います。
ドライバー	ODBC関数の呼び出しを処理し、データソースに対してSQLを実行します。
データソース	ユーザーがアクセスするデータやDBMS(*2)などで構成されます。

(*1) Javaの場合、JavaアプリケーションからDBを操作するAPIの仕様であるJDBCを使用します。
(*2) DBMS (Database Management System) とは、コンピュータ上に蓄積されたDBにアクセスし、データの検索や更新などを行うソフトウェアのことです。（情報を蓄積するDBとDBMSをまとめて、DBと呼ぶこともあります）

接続文字列とは

接続文字列とは、DBへの接続情報を記した文字列のことで、例えば以下のように記述されます。

Provider=SQLOLEDB; Initial Catalog=testServer; Data Source=testDB; User ID=testuser; Password=testpass;

TCP/IPとは

TCP（Transmission Control Protocol）とは「送信したデータが相手に届いたかを都度確認し、正確な通信を実現する規格」のことで、IP（Internet Protocol）は「通信先を指定するための数値IPアドレスを用いてネットワーク通信を行う規格」のことです。これらの通信プロトコルが標準的に利用されることで、機器やOSが異なっている場合でも通信を成立させることができます。（参考：TCP/IPとは？通信プロトコルの階層モデルを図解で解説, まるごとわかるルーティング入門）

名称	主な規格（プロトコル）	主な利用例
アプリケーション層	HTTP(S), SMTP, DNS	Webサイト閲覧, メール, ファイル転送, 名前解決
トランスポート層	TCP, UDPなど	データを適切なアプリケーションへ振り分け
インターネット層	IP, ARPなど	ルーティング, end-to-end通信
ネットワーク インターネット層	Ethernet	LAN

アプリケーション層

アプリケーションで扱うデータのフォーマットや手順を決め、「0/1」の信号データをユーザーが理解できる表現形式に変換します。HTTP(S)プロトコルはWebブラウザ、SMTPやPOP3はメール、DNSは名前解決（ドメインとIPアドレスの紐付け）に用いられます。

トランスポート層

TCPやUDPといったプロトコルで、データを適切なアプリケーションに振り分けます。私たちが普段スマホでアプリなどを利用する上で欠かせない存在です。

	TCP	UDP
特徴	確実性重視	速度重視
信頼性	高い	低い
利用速度	遅い	速い
利用例	Webサイト閲覧, メールなど	映像配信, Web会議など

インターネット層

ルーターを経由して異なるネットワークにIPバケット（データ）を転送する「ルーティング」や、ルーターを介さずに端末と端末で直接データを送信する「end-to-end通信」などが行われます。

ネットワークインターフェイス層

Ethernet(有線LAN)や無線LANなどのプロトコルで、同一ネットワーク内におけるデータ転送を実現します。例えば、同一ネットワーク内のサーバーやDBなどの通信プロトコルを定めることで、デジタルデータなどを電気信号に変換して伝送媒体で伝えることができます。

DBの種類

ここでのDBとは、行列形式のDBであるRDB (Relational Database) のことであり、階層型DB・ネットワーク型DB・NoSQLなどについては言及しないものとします。

DB名	説明
MySQL	Oracle社が管理するオープンソース型のDBMS（無料）。大容量データを高速に処理でき、検索エンジンやレンタルサーバーなどでも利用されています。
Oracle Database	Oracle社が管理する商用DB（有料）。高い堅牢性を持つため障害などにも強く、サポートも受けることができます。
Microsoft SQL Server	Microsoft社が提供するRDB（有料）。高い堅牢性をもち、Windows製品との親和性が高いです。
Microsoft Access	Office製品に含まれるRDB（有料）。他のDBに比べ操作は簡単ですが、複雑な処理や高速な処理には不向きです。
PostgreSQL	Unix系のOSにも対応するオープンソース型のRDB（無料）。ある程度複雑な処理にもでき、様々な機能や関数にも対応できるバランス型です。
SQLite	オープンソース型のDBMS（無料）。手軽な設定と軽い動作が特徴で、アプリケーションに組み込まれて運用されます。
Amazon Aurora	Amazon社が提供するRDB（有料）。処理能力はMySQLの5倍、PostgreSQLの3倍高く、ストレージも最大64TBまで拡張でき、DB保護のセキュリティも複数段階用意されています。

（参考：データベースの種類を解説！主要データベース12選）