SQL 教育用資料

テーブルの作成

はじめに

SQLを憶えるためにDBサーバーを立てるのもあれなので、お手軽に使える sqlite で学習します。 cygwin の setup から sqlite3 を探してインストールしてください。

課題

SQLite とはどういうデータベースなのか wikipedia 等で調べて簡潔に説明できるようにしてください。

データベース作成と接続

sqlite3 を使用して接続します。
データベースはファイルそのものなので、適当に名前を付けて作成します。

$ sqlite3 example001.sqlite3
SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
sqlite> .exit

$

.exit でDBから抜けることができます。 .help でコマンドの説明が出てくるので個別にチェックしておいてください。

テーブルの作り方

データを入れるための箱を作成します。

ここから使うのはSQL構文になります。
テーブルを作るには

CREATE TABLE を使います。

では、以下のようなテーブルを作成する手順を追いかけてみましょう。

EmployeeMaster(社員マスタ)

名称	列名	データ型	デフォルト値	制約
社員CD	EmployeeCode	int		PRIMARY、NOT NULL
名前	EmployeeName	text		NOT NULL
かな	EmployeeKana	text
性別	EmployeeSex	int
ステータス	EmployeeStatus	int	1	NOT NULL

演習用のテーブルについての言い訳

ぐだぐだなテーブル定義ですが、演習用だからです。 いろいろと思うことについてはDB設計の専門書を参考にしてください。 今回はあくまでも使うということになれるための物であるという認識でいてください。

では、作成してみましょう。

sqlite> .tables
sqlite> CREATE TABLE EmployeeMaster (
EmployeeCode int NOT NULL,
EmployeeSurname text NOT NULL,
EmployeeName text NOT NULL,
EmployeeSex int,
EmployeeStatus int DEFAULT 1 NOT NULL,
primary key(EmployeeCode)
);
sqlite>

作成されたことを確認してみましょう。

sqlite> .tables
EmployeeMaster
sqlite> .schema EmployeeMaster
CREATE TABLE EmployeeMaster (
EmployeeCode int NOT NULL,
EmployeeSurname text NOT NULL,
EmployeeName text NOT NULL,
EmployeeSex int,
EmployeeStatus int DEFAULT 1 NOT NULL,
primary key(EmployeeCode)
);
sqlite> .exit

ファイルに書いたSQL文を実行

テーブルを作ることは数回しかないかも知れないけれど、ミスがあったときに最初から書くとさぁ大変。
というわけで、ファイルに書いたSQL文を実行して登録する方法を学びます。

では、テキストエディタを使用して、下記の内容のファイルを、c://cygwin64/home/ユーザー名/sqlfile に作成してください。 cygwin上のvi を使って作成しても構いません。その場合は、DBのファイルと同じ階層に作成しましょう。 取り合えず vi で作成する手順

$ mkdir sqlfile
$ cd sqlfile
$ vi employeemaster.sql

CREATE TABLE EmployeeMaster (
 EmployeeCode int NOT NULL,
 EmployeeSurname text NOT NULL,
 EmployeeName text NOT NULL,
 EmployeeSex int,
 EmployeeStatus int DEFAULT 1 NOT NULL,
 PRIMARY KEY (EmployeeCode)
);

$ ls
employeemaster.sql
$

では、ファイルから作成してみましょう。

$ sqlite3 example001.sqlite3
SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
sqlite> .read employeemaster.sql
Error: near line 1: table EmployeeMaster already exists
sqlite>

同じ社員マスタを作るSQL文を作っています。そのため、実行すると、すでにあるテーブルだからエラーですという意味のメッセージ table EmployeeMaster already exists が出ます。

これでは意味が無いので、作成済みのテーブルを削除します。
この作業を『テーブルをドロップする』なんていうこともあります。

sqlite> drop table EmployeeMaster;
sqlite> .tables
sqlite>

さて、準備が出来たので、テーブルを今度はファイルの内容から登録してみます。

sqlite> .read employeemaster.sql
sqlite> .schema EmployeeMaster
CREATE TABLE EmployeeMaster (
 EmployeeCode int NOT NULL,
 EmployeeSurname text NOT NULL,
 EmployeeName text NOT NULL,
 EmployeeSex int,
 EmployeeStatus int DEFAULT 1 NOT NULL,
 PRIMARY KEY (EmployeeCode)
);
sqlite> .exit

という感じですね。
エラーの場合は、不思議な状態に陥るので、 Ctrl+D で抜けてください。

テーブルの作成の演習

問題１

以下の型について調べてください。

1. char
2. varchar
3. text
4. integer
5. numeric
6. timestamp
7. date

問題２

以下の制約について調べてください。

1. NOT NULL(非NULL制約)
2. UNIQUE(一意性制約)
3. PRIMARY KEY(プライマリキー)

問題３

以下の定義に沿ったテーブルを作成してください。

なお、作成した際に使用したSQL文はファイルに残すなどして確認できるようにしてください。

MaterielDivMaster(資材区分マスタ)

名称	列名	データ型	デフォルト値	制約
資材区分コード	MaterielDivCode	int		PRIMARY、NOT NULL
資材区分名	MaterielDivName	text		NOT NULL
資材区分備考	MaterielDivNote	text

MaterielMaster(資材マスタ)

名称	列名	データ型	デフォルト値	制約
資材コード	MaterielCode	int		PRIMARY
資材区分コード	MaterielDivCode	int		NOT NULL
資材ステータス	MaterielStatus	int	1	NOT NULL
メーカー	MaterielMaker	text	N/A	NOT NULL
型番	ModelNo	text		NOT NULL
製造番号	ProductNo	text	N/A	NOT NULL
シリアル番号	SerialNo	text	N/A	NOT NULL
備考１	Note1	text
備考２	Note2	text
登録日	RegistDate	timestamp	now()	NOT NULL
廃棄日	DiscardDate	timestamp