テーブルの作成方法
テーブルの作成方法について説明します。
テーブルの作成
ここではテーブルを作成及びデータの追加・更新・削除のSQLを説明します。
以下のECサイトの顧客テーブルを作成することにします。
| No | 項目名（論理） | 項目名（物理） | 型 | 制約 |
|---|---|---|---|---|
| 1 | 顧客番号 | CUST_NO | INT | 主キー |
| 2 | 氏名 | CUST_NAME | VARCHAR | 必須 |
| 3 | 住所 | CUST_ADDR | VARCHAR | 必須 |
| 4 | 電話番号 | CUST_TEL | VARCHAR | |
| 5 | 入会日時 | JOIN_DATE | DATE |
テーブルを作成する際は以下のSQL文を実行します。
構文の詳細は公式サイトを参照ください。
【SQL文】
-- テーブル
CREATE TABLE MST_CUSTOMER
(
CUST_NO INT NOT NULL, -- 顧客番号
CUST_NAME VARCHAR(30) NOT NULL, -- 氏名
CUST_ADDR VARCHAR(100) NOT NULL, -- 住所
CUST_TEL VARCHAR(20), -- 電話番号
JOIN_DATE DATE -- 入会日時
)
;
-- プライマリーキー
ALTER TABLE MST_CUSTOMER
ADD CONSTRAINT PK_MST_CUSTOMER PRIMARY KEY (CUST_NO)
;
これでテーブルが作成されました。
次にデータを追加してみましょう。
以下のSQL文を実行ます。
【SQL文】
INSERT INTO MST_CUSTOMER
VALUES(0001, '佐藤 一郎', '東京都中央区１－１', '090-3000-1234', CURRENT_DATE);
INSERT INTO MST_CUSTOMER
VALUES(0002, '鈴木 和夫', '東京都港区１１', '090-9080-4567', CURRENT_DATE);
INSERT INTO MST_CUSTOMER
VALUES(0003, '高橋 花子', '東京都葛飾区５－１－４', '090-0300-8901', CURRENT_DATE);
INSERT INTO MST_CUSTOMER
VALUES(0004, '田中 翔太', '埼玉県さいたま市３１', '090-3000-0453', CURRENT_DATE);
INSERT INTO MST_CUSTOMER
VALUES(0005, '渡辺 明日香', '神奈川県横浜市１００', '090-0100-1010', CURRENT_DATE);
データが登録されたか以下のSELECT文でデータを抽出してみます。
SELECT * FROM MST_CUSTOMER ;
実行結果は以下になります。データが追加されています。
CUST_NO CUST_NAME CUST_ADDR CUST_TEL JOIN_DATE
------------- ----------------------------------- ---------------------------------------------------------- ----------------------- ------------
1 佐藤 一郎 東京都中央区１－１ 090-3000-1234 2019-08-31
2 鈴木 和夫 東京都港区１１ 090-9080-4567 2019-08-31
3 高橋 花子 東京都葛飾区５－１－４ 090-0300-8901 2019-08-31
4 田中 翔太 埼玉県さいたま市３１ 090-3000-0453 2019-08-31
5 渡辺 明日香 神奈川県横浜市１００ 090-0100-1010 2019-08-31
コメントの追加
テーブル、項目に対しコメントを追加することができます。
カタログに登録され、ちょっと便利です。
構文は以下の通りです。
COMMENT ON DBオブジェクト DBオブジェクト名 IS 'コメント文字列'
-- コメント COMMENT ON TABLE MST_CUSTOMER IS '顧客テーブル'; COMMENT ON COLUMN MST_CUSTOMER.CUST_NO IS '顧客番号'; COMMENT ON COLUMN MST_CUSTOMER.CUST_NAME IS '氏名'; COMMENT ON COLUMN MST_CUSTOMER.CUST_ADDR IS '住所'; COMMENT ON COLUMN MST_CUSTOMER.CUST_TEL IS '電話番号'; COMMENT ON COLUMN MST_CUSTOMER.JOIN_DATE IS '入会日時';
実行後カタログをみてみます。
SELECT NAME, REMARKS FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = 'MST_CUSTOMER' ORDER BY COLNO ;
実行結果は以下になります。カタログにコメントが追加されています。
NAME REMARKS ---------------------------------------------------------- ---------------------------------------------------------- CUST_NO 顧客番号 CUST_NAME 氏名 CUST_ADDR 住所 CUST_TEL 電話番号 JOIN_DATE 入会日時
これを利用して以下のSQLを実行するとCREATE TABLE文が生成できます。
※制約は対応していません。プレーンな表のみです。
-- CEATE TABLE 生成SQL
SELECT CASE WHEN COL.COLNO = 0 THEN
'CREATE TABLE ' || TBL.NAME ||
' -- ' || TBL.REMARKS || CHR(13) || CHR(10) || '(' || CHR(13) || CHR(10)
ELSE
''
END ||
' ' || COL.NAME ||
REPEAT(' ', 20 - (LENGTH(COL.NAME))
) ||
COL.COLTYPE ||
CASE WHEN COL.COLTYPE IN ('CHAR', 'VARCHAR', 'LONG VARCHAR')
THEN '(' || COL.LENGTH || ')'
ELSE ''
END ||
CASE WHEN TBL.COLCOUNT > COL.COLNO + 1 THEN ',' ELSE '' END ||
REPEAT(' ', 20 - (CASE WHEN COL.COLTYPE IN ('CHAR', 'VARCHAR', 'LONG VARCHAR') THEN
LENGTH(TO_CHAR(COL.LENGTH)) + 2
ELSE 0
END +
CASE WHEN TBL.COLCOUNT > COL.COLNO + 1 THEN
1
ELSE
0
END
)
) ||
'-- ' || COL.REMARKS ||
CASE WHEN TBL.COLCOUNT = COL.COLNO + 1 THEN CHR(13) || CHR(10) || ');' ELSE '' END
FROM SYSIBM.SYSTABLES TBL
JOIN SYSIBM.SYSCOLUMNS COL
ON COL.TBNAME = TBL.NAME
WHERE TBNAME = 'MST_CUSTOMER'
ORDER BY COLNO
;
CREATE TABLE文が出力されました。まあ、コメントはなくても良いという話はありますが。。。
CREATE TABLE MST_CUSTOMER -- 顧客テーブル
(
CUST_NO INTEGER ,
-- 顧客番号
CUST_NAME VARCHAR (30),
-- 氏名
CUST_ADDR VARCHAR (100),
-- 住所
CUST_TEL VARCHAR (20),
-- 電話番号
JOIN_DATE DATE
-- 入会日時
);
おまけに、SELECT文を生成するSQLも記します。
-- SELECT 生成SQL
SELECT CASE WHEN COL.COLNO = 0 THEN
'SELECT '
ELSE
' '
END ||
COL.NAME ||
CASE WHEN TBL.COLCOUNT = COL.COLNO + 1 THEN
CHR(13) || CHR(10) || ' FROM ' || TBL.NAME || ';'
ELSE
','
END
FROM SYSIBM.SYSTABLES TBL
JOIN SYSIBM.SYSCOLUMNS COL
ON COL.TBNAME = TBL.NAME
WHERE TBNAME = 'MST_CUSTOMER'
ORDER BY COLNO
;