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

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

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

問題2

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

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

問題3

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

なお、作成した際に使用した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
備考1 Note1 text
備考2 Note2 text
登録日 RegistDate timestamp now() NOT NULL
廃棄日 DiscardDate timestamp