データベースの実装
データベースの作成
応用編3日目から応用編5日目までの間の話をもとに、ここではいよいよ実際にデータベース、およびテーブルを作成して、そこにデータを挿入してデータベースを完成させてみることします。このデータベースは、まず
まずは、データベースの作成から始めましょう。今回は、データベースをcompanyとし、そこにテーブルを作ることにします。まずは、以下のsql文を実行してみましょう。
Sampleex601.sql# データベースcompanyの生成 CREATE DATABASE company; # companyデータベースの使用 use company;
テーブルを作成する順序
続いて、テーブルの作成とデータの挿入に移ります。この際、大事なことは、外部キーを含むテーブルから先に作成していくことです。外部キー制約が入るテーブルは、対応するテーブルとデータがないと、次のようなエラーが発生し、データを作成することができません。（図6-1.)
図6-1.外部キーのテーブルがない場合、外部キー制約があるテーブルを作成する際に出るエラー |
そのため、sexをまず作成し、次に、employee、qualテーブルを作成し、最後にemp_qualを作成します。（図6-2.）
図6-2.テーブルの作成順序 |
sexテーブルの作成
ではまず、テーブルの作成をします。まずは、一番簡単なsexテーブルを作成しましょう。
Sampleex602.sql（sexテーブルの生成）
CREATE TABLE sex(
sex_id INTEGER NOT NULL PRIMARY KEY,
sex CHAR(2) NOT NULL
);
sex_idは、外部キーなので、一意である必要があるため、通常はユニーク(UNIQUE)制約が必要になりますが、主キー制約がついているので、その必要はありません。続いて、INSRT INTO文で、データを挿入します。
Sampleex603.sql（sexテーブルへデータ挿入）INSERT INTO sex VALUES(1,'男性'); INSERT INTO sex VALUES(2,'女性');
これにより、sexテーブルとそのデータが完成しました。結果は以下のようになります。（図6-3.）
図6-3.完成したsexテーブル |
employeeテーブル
続いて、employeeテーブルを作成します。
Sampleex604.sql（employeeテーブルの生成）CREATE TABLE employee( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, age INTEGER NOT NULL, sex_id INTEGER NOT NULL, salary INTEGER DEFAULT 324000 NOT NULL, FOREIGN KEY(sex_id) REFERENCES sex(sex_id) );
すでに説明したとおり、外部キーとして、sexテーブルのsex_idを持ちますので、これを最後に記述します。続いて、このテーブルにデータを挿入しましょう。
Sampleex605.sql（employeeテーブルへデータ挿入）INSERT INTO employee VALUES(2030,'山田太郎',30,1,324000); INSERT INTO employee VALUES(2031,'佐藤幸一',35,1,412000); INSERT INTO employee VALUES(2032,'大峰聡子',28,2,290000); INSERT INTO employee VALUES(2033,'桜井直子',43,2,452000);
これにより、employeeテーブルが完成し、データを挿入することもできました。（図6-4.)
図6-4.データを挿入したemployeeテーブル |
employeeテーブルは、これで完成というわけではありません。今後データが追加されたり、削除されたりする可能性があります。
qualテーブル
続いて、資格情報を格納するqualテーブルを作成します。
Sampleex606.sql（qualテーブルの生成）CREATE TABLE qual( qual_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, qual VARCHAR(20) NOT NULL );
qual_idは、主キーとなり、資格の種類を区別します。AUTO_INCREMENTが付いているので、資格名（qual）を追加すれば、自動的にqual_idが追加されます。これを利用して、データを追加してみましょう。
Sampleex607.sql（qualテーブルへデータの追加）
INSERT INTO qual (qual) VALUES('普通自動車第一種免許');
INSERT INTO qual (qual) VALUES('英語検定二級');
INSERT INTO qual (qual) VALUES('英語検定一級');
INSERT INTO qual (qual) VALUES('日商簿記二級');
INSERT INTO qual (qual) VALUES('日商簿記一級');
INSERT INTO qual (qual) VALUES('TOEIC(750以上)');
INSERT INTO qual (qual) VALUES('中小企業診断士');
これにより、qualテーブルの内容は以下のようになります。（図6-5.）
図6-5.データを挿入したqualテーブル |
AUTO_INCREMENTのために、qual_idが自動的に付与されていることが分かります。
emp_qualテーブル
最後に、社員の資格を記録したemp_qualテーブルを作成します。まずは、テーブルの作成をします。
Sampleex608.sql（emp_qualテーブルの生成）CREATE TABLE emp_qual( id INTEGER NOT NULL, qual_id INTEGER NOT NULL, FOREIGN KEY(id) REFERENCES employee(id), FOREIGN KEY(qual_id) REFERENCES qual(qual_id) );
このテーブルは、2つの外部キーを持ちます。それぞれのキーは、テーブルemployee、およびqualに対応しているため、そのため、それぞれのテーブルがすでに出来上がってから作る必要があります。続いて、ここにデータを挿入します。
Sampleex609.sql（emp_qualテーブルへデータの追加）INSERT INTO emp_qual VALUES (2030,1); INSERT INTO emp_qual VALUES (2030,5); INSERT INTO emp_qual VALUES (2030,7); INSERT INTO emp_qual VALUES (2031,5); INSERT INTO emp_qual VALUES (2031,6); INSERT INTO emp_qual VALUES (2033,7); INSERT INTO emp_qual VALUES (2033,1);
完成したデータは、以下のようになります。（図6-6.）
図6-6.データを挿入したemp_qualテーブル |
以上で、データが完成しました。次は、これらのデータを、様々な方法で検索してみましょう。
データの検索
作成したデータの確認
では、作成したテーブルを利用して、様々な検索を試してみましょう。
様々な検索方法
まずは、社員番号、名前、性別、給与を表示してみましょう。
(1)社員番号、名前、性別、給与を表示Sample610.sql
SELECT id,name,age,sex,salary FROM employee INNER JOIN sex USING(sex_id);
実行結果は以下のようになります。(図6-7.)
図6-7.社員番号、名前、性別、給与を表示 |
employeeテーブルには、性別はsex_idとして数値（1か2）としてしか記述されていないので、sexテーブルを、内部結合して、結果を表示しています。
続いて、中小企業診断士の資格を持つ社員の社員番号とIDを表示しましょう。
(2)中小企業診断士の資格を持つ社員の社員番号とIDを表示Sample611.sql
SELECT id,name FROM employee INNER JOIN emp_qual USING(id) INNER JOIN qual USING(qual_id) WHERE qual='中小企業診断士';
 |
これらはあくまでも一部の例ですが、完成したテーブルを必要に応じて結合していけば、さまざまな検索を行うことができます。
 |  |  |
