公開日:2018/5/31 0:00:00

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(社員マスタ)

名称列名データ型デフォルト値制約
社員CDEmployeeCodeintPRIMARY、NOT NULL
名前EmployeeNametextNOT NULL
かなEmployeeKanatext
性別EmployeeSexint
ステータスEmployeeStatusint1NOT 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(資材区分マスタ)

名称列名データ型デフォルト値制約
資材区分コードMaterielDivCodeintPRIMARY、NOT NULL
資材区分名MaterielDivNametextNOT NULL
資材区分備考MaterielDivNotetext

MaterielMaster(資材マスタ)

名称列名データ型デフォルト値制約
資材コードMaterielCodeintPRIMARY
資材区分コードMaterielDivCodeintNOT NULL
資材ステータスMaterielStatusint1NOT NULL
メーカーMaterielMakertextN/ANOT NULL
型番ModelNotextNOT NULL
製造番号ProductNotextN/ANOT NULL
シリアル番号SerialNotextN/ANOT NULL
備考1Note1text
備考2Note2text
登録日RegistDatetimestampnow()NOT NULL
廃棄日DiscardDatetimestamp