The Sixwish project

SQL - Education

テーブルの作成

データベースにテーブルを追加する方法を学びます。
この演習は、Linuxにログインしたユーザーで行なってください。
また、使用するデータベースは指定されたものを使用するようにしてください。

使用するデータベースが判らない場合は、管理者に問い合わせてください。

データベースへ接続

psqlを使用して接続します。
今回はLinuxのユーザー名と同じユーザー(Role)を作成、データベースもあるので、いろいろな指定を省いて接続できます。

[lain@sixwish ~]$ psql
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

lain=> \q
[lain@sixwish ~]$

次からは、このpsqlで接続した場所で作業を行なうことになります。

テーブルの作り方

PostgreSQLのマニュアルの中では『データ定義』となっているのがミソなのですが……
それはさておき、データを入れるための箱を作成します。

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

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

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

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

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

って、言い訳になったかな?

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

[lain@sixwish ~]$ psql
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

lain=> \d
No relations found.
lain=> CREATE TABLE EmployeeMaster (
lain(> EmployeeCode int NOT NULL,
lain(> EmployeeSurname text NOT NULL,
lain(> EmployeeName text NOT NULL,
lain(> EmployeeSex int,
lain(> EmployeeStatus int DEFAULT 1 NOT NULL,
lain(> primary key(EmployeeCode)
lain(> );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "employeemaster_pkey" for table "employeemaster"
CREATE TABLE
lain=> \d
          List of relations
 Schema |      Name      | Type  | Owner
--------+------------+-------+-------
 public | employeemaster | table | lain
(1 row)
lain=> \d employeemaster
                                         Table "public.employeemaster"
     Column      |  Type   |     Modifiers
-----------------+---------+--------------------
 employeecode    | integer | not null
 employeesurname | text    | not null
 employeename    | text    | not null
 employeesex     | integer |
 employeestatus  | integer | not null default 1
Indexes:
    "employeemaster_pkey" PRIMARY KEY, btree (employeecode)

lain=> \q
[lain@sixwish ~]$

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

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

では、viを使用して、下記の内容のファイルを作成してください。

[lain@sixwish ~]$ mkdir sqlfile
[lain@sixwish ~]$ cd sqlfile
[lain@sixwish 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)
);
[lain@sixwish sqlfile]$ ls
employeemaster.sql
[lain@sixwish sqlfile]$

さて、今回も同じ社員マスタを作るSQL文を作っています。
このまま実行すると、すでにあるテーブルだからエラーですという意味のメッセージ(ERROR: relation "employeemaster" already exists)が出ます。

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

[lain@sixwish sqlfile]$ psql
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

lain=> \d
          List of relations
 Schema |      Name      | Type  | Owner
--------+------------+-------+-------
 public | employeemaster | table | lain
(1 row)
lain=> DROP TABLE employeemaster ;
DROP TABLE
lain=> \d
No relations found.
lain=> \q
[lain@sixwish sqlfile]$

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

[lain@sixwish sqlfile]$ psql -f employeemaster.sql lain
psql:employeemaster.sql:8: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "employeemaster_pkey" for table "employeemaster"
CREATE TABLE
[lain@sixwish sqlfile]$

という感じですね。
エラーの場合は、ERROR:と出てくる(psql:employeemaster.sql:行番号: ERROR: 内容)ので、ファイルの中身を修正してください。

あとは、先ほどと同じようにpsqlで入って、テーブルが出来ていることを確認してください。

演習

問題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