SQLにハマって勉強STOP中。〜応用情報技術者試験
先日、基本情報技術者試験に無事合格しまして、次なる目標として応用情報技術者試験に挑戦しようとマイペースに勉強中です。
ところでですが、SQLって難しくないですか？
正直触ったこともなしに、こんな時はこういうSQL文を書いて・・・と覚えるのは厳しいので、実際にリレーショナルデータベースに対してSQL文を実行しながら、学びたいなと探したところ・・・
以下サイトで、テーブル作成からSQL文を実行して、その後データ抽出やソートなどテキストなどで解説される内容を実験することが可能だと分かりました。
ただ暗記するより、実際に触ってみた方が身につきやすいし、理解も進みやすいのではないでしょうか。・・・と言い訳しつつ、SQLでの遊びにふけって勉強STOP中です。
オンプレであれ、クラウドであれリレーショナルデータベースは顧客管理や商品管理など、多くの企業で利用されるため知っておくに越したことはない知識だと思います！
1.操作方法
左上の部分で、テーブル定義やデータ作成のためのSQL文実行が可能で、VIEWの作成もできます。
なお、データベースの構造いわば設計図のようなものをスキーマといいます。
何かシステム開発案件とかで「スキーマをここで移行して・・・なんちゃらかんちゃら」みたいな話が出たら、ああ、データ構造のことを言っているのねぐらいに思えるだけで随分と差がつくかと思います。
一言でスキーマと言っても、今回触れるのは概念スキーマというデータ項目と各項目の関連性の部分の定義となりますので、興味がある方は3層スキーマについて調べてみてください。
濃い青色のBuild Schema※ボタンでデータベースを作成したら、右側でSELECT文などのSQL文が実行可能です。
※Schema＝スキーマです。
2.スキーマを構築する
まずは、左上に簡単なテーブルを作成して、いくつかデータを登録してみましょう。
テーブルは、エクセルで作成した表みたいなものです。
学校における学級と生徒、そして先生のデータベースを作成してみましょう。
(1)テーブルを作成する
まずは、学級テーブルから。左上の欄に以下のSQL文を記載します。
学級というテーブルには、3つの項目（属性ともいう）（列）が存在します。
・クラス（1組とか2組とか）
・担任
・教室（何階に教室があるか）
CREATE TABLE 学級
(
クラス INT NOT NULL,
担任 CHAR(20) NOT NULL,
教室 INT NOT NULL,
PRIMARY KEY(クラス)
);テーブル定義は、CREATE TABLE テーブル名で定義して、カッコ内に引数として属性と属性ごとの定義を記載していきます。
クラスと教室は数値（INT）で、担任は文字列なのでCHARとしています。（カッコ内はバイト数）
NOT NULLは、値が入っていないとダメよということです。
PRIMARY KEYは主キーと言って、その表の各行を一意に定義する項目です。（インデックスとなるもの）
次に、生徒テーブルを定義していきます。
CREATE TABLE 生徒
(
学籍番号 CHAR(10) NOT NULL,
氏名 CHAR(20) NOT NULL,
性別 CHAR(4) NOT NULL,
学級 INT NOT NULL,
住所 CHAR(20),
PRIMARY KEY(学籍番号),
FOREIGN KEY(学級) REFERENCES 学級(クラス)
);
生徒は必ずいずれかの学級（クラス）に所属するため、生徒テーブルの学級という項目で、外部の学級表を参照できるよう外部キー（FOREGIN KEY）を定義しています。
なお、項目はエンティティといい、外部キーや主キーのような項目感の関係性をリレーションといいます。
ER図という言葉を聞いたことがあるかと思いますが、データモデルを設計するにあたり、E（エンティティ）とR（リレーション）を図で整理したものとなります。
そして、定義されたテーブルに登録される値を持ったものをインスタンス（実体）といい、1行をレコードと言います。
（2）テーブルにレコードを追加する
テーブルへのレコード追加はSQL文のINSERT句で行います。
あまり沢山は用意できませんが、3つの学級と13人の生徒を用意してみましたので追加していきます。
・学級テーブルへの行追加
INSERT INTO 学級(クラス, 担任, 教室) VALUES
(1, '山田太郎', 1),
(2, '佐藤二郎', 1),
(3, '田中三子', 2);・生徒テーブルへの行追加
INSERT INTO 生徒(学籍番号,氏名,性別, 学級,住所) VALUES
('20230001' , '貝原健太', 'M',1 ,'東京都'),
('20230002' , '山上総司', 'M',2 , '神奈川県'),
('20230003', '武井颯太', 'M', 3, '東京都'),
('20230004', '上杉太', 'M',1, '埼玉県'),
('20230005', '若井佳代子','F' , 2, '東京都'),
('20230006', '桑原弥恵','F' , 1, '千葉県'),
('20230007', '林久美','F' , 3, '東京都'),
('20230008', '雪丸花子', 'F', 1 , '埼玉県'),
('20230009', '金子みお', 'F', 2, '東京都'),
('20230010', '安田隆', 'M', 2, '東京都'),
('20230011', '川幡太一','M' , 3, '山梨県'),
('20230012', '黒田裕美','F', 1, '神奈川県'),
('20230013', '木更泰斗','M', 2, '東京都');INSERT INTO テーブル名(属性) VALUES(値) で追加が可能です。
ちなみに複数レコードを追加したい場合、上のようにタプル（かっこで囲まれた塊）を複数記載することで実現できます。
なお、属性をINT(数値)とした場合は数字を記載すればOKですが、CHAR（文字列）とした場合は''で囲む必要がありますので注意です。
では、上に記載したものをコピペでいいのでSQL Fiddleの左上の欄に貼り付けたら、Build Schemaボタンを押下してみましょう。
緑の帯でSchema Readyと表示されれば、無事テーブルと、レコード追加が完了したことになります。
・ 左上に記載すべきコード全文
CREATE TABLE 学級
(
クラス INT NOT NULL,
担任 CHAR(20) NOT NULL,
教室 INT NOT NULL,
PRIMARY KEY(クラス)
);
CREATE TABLE 生徒
(
学籍番号 CHAR(10) NOT NULL,
氏名 CHAR(20) NOT NULL,
性別 CHAR(4) NOT NULL,
学級 INT NOT NULL,
住所 CHAR(20),
PRIMARY KEY(学籍番号),
FOREIGN KEY(学級) REFERENCES 学級(クラス)
);
INSERT INTO 学級(クラス, 担任, 教室) VALUES
(1, '山田太郎', 1),
(2, '佐藤二郎', 1),
(3, '田中三子', 2);
INSERT INTO 生徒(学籍番号,氏名,性別, 学級,住所) VALUES
('20230001' , '貝原健太', 'M',1 ,'東京都'),
('20230002' , '山上総司', 'M',2 , '神奈川県'),
('20230003', '武井颯太', 'M', 3, '東京都'),
('20230004', '上杉太', 'M',1, '埼玉県'),
('20230005', '若井佳代子','F' , 2, '東京都'),
('20230006', '桑原弥恵','F' , 1, '千葉県'),
('20230007', '林久美','F' , 3, '東京都'),
('20230008', '雪丸花子', 'F', 1 , '埼玉県'),
('20230009', '金子みお', 'F', 2, '東京都'),
('20230010', '安田隆', 'M', 2, '東京都'),
('20230011', '川幡太一','M' , 3, '山梨県'),
('20230012', '黒田裕美','F', 1, '神奈川県'),
('20230013', '木更泰斗','M', 2, '東京都');
・Build Schemaボタン押下後
・レコードが登録できたことを確認する。
では、SQL Fiddleの右の欄に以下のコードを記載してみましょう。
SELECT * FROM 学級;
SELECT * FROM 生徒;
3.データベースを利用してみる
(1) 射影（Projection）
テーブルから任意の属性（列）のみを抽出することを射影といいます。
射影には、SELECT句を使います。
例えば生徒の表から性別を抽出します。
SELECT 性別 FROM 生徒
ただし、ご覧の通り値が重複した状態で抽出されます。
重複を排除したい場合はDISTINCT句を使うことで対応できます。
SELECT DISTINCT 性別 FROM 生徒(2) 選択（Selection）
次に、テーブルからある一定条件を満たす行を抽出してみましょう。
先ほどのSELECT句にWHEREを足すことで、条件を指定できます。
例えば、学級1に所属する生徒を抽出してみましょう。
SELECT 学籍番号, 氏名, 学級 FROM 生徒 WHERE 学級 = 1;【抽出結果】
学籍番号 氏名 学級
20230001 貝原健太 1
20230004 上杉太 1
20230006 桑原弥恵 1
20230008 雪丸花子 1
20230012 黒田裕美 1(3) 集計関数を利用する
集計関数によって、テーブルのレコードを加工して利用することが可能です。
合計値を算出するSUMや、レコード数をカウントするCOUNT、平均値を算出するAVGなどがあります。
では、各学級に所属する生徒数を調べてみましょう。
SELECT 学級, COUNT(*) AS 生徒数 FROM 生徒 GROUP BY 学級;【抽出結果】
GROUP BYで学級ごとのレコード数をカウントして、その結果を生徒数という項目名として出力しています。
さらに、集計関数利用時に条件指定をすることも可能です。
先ほどのSQL文を少し改造して、3人以上生徒数がある学級のみを出力してみます。
SELECT 学級, COUNT(*) AS 生徒数 FROM 生徒 GROUP BY 学級 HAVING 生徒数 > 3 ;HAVING句を使うと、集計関数で算出した結果に対して条件を設定できるようになります。
(4) 学級テーブルと生徒テーブルを結合する
次に、生徒テーブルから生徒名を、学級テーブルから担任名をそれぞれ抽出して一つの表にします。
生徒テーブルにおける「学級」と、学級テーブルにおける「クラス」は同一なので、これを元に2表を結合して生徒テーブルから「氏名」を、学級テーブルから「担任」を抽出します。
SELECT X.氏名, Y.担任 FROM 生徒 X INNER JOIN 学級 Y ON X.学級 = Y.クラス; 【抽出結果】
(5) VIEWを定義する
学級テーブルや、生徒テーブルのように定義されたテーブルを実表といいます。これに対し、VIEWは仮想表といい、SELECT文で導出された表をデータ抽出等が行えるように仕組みです。
例えば、保有するデータベースからデータ抽出が可能なシステムにおいて、ユーザーがテーブル構造を把握して必要に応じてテーブル結合をしながらデータを抽出しようとするのは難しいです。
なので、テーブル構造を意識せずに、データ分析や利用に必要な項目を予めVIEWとして定義しておき、ユーザーはVIEW側で抽出を行えるようにするものです。
これも、SQL Fiddleで実験可能です。
例えば、男子生徒の学籍番号・氏名・学級・教室がユーザーにとって必要項目だったとします。教室のみ学級テーブルなので、先ほどの表を結合しつつ呼び出しを行うことでも実現はできます。
が、必要項目だけが用意されたVIEWが利用できることで利便性は高まるということです。
それならば初めから、そのようなテーブルにすればいいのでは？とお思いかもしれませんが、データベースは更新時に矛盾が生じないため重複を排除した形（正規形）で定義されるため、VIEWが有効となることもあります。
では、SQL FiddleでVIEWを作成してみましょう。
VIEWの定義は、左上の欄で行いますので、以下を追記してみてBuild Schemaボタンを押下してください。
CREATE VIEW 男子生徒と教室 AS
SELECT X.学籍番号, X.氏名, X.学級, Y.教室
FROM 生徒 X, 学級 Y
WHERE 性別 = 'M';その上で、作成したVIEWが利用できるか試してみてください。
SELECT * FROM 男子生徒と教室作成したVIEWの全データを抽出します。
色々と手を動かしていくと、暗記せずとも、こういう時はこういう書き方をするんだな・・・と理解が進むと思います！！